Skip to content

How To Create a Stored Procedure using C#, SQL Server 2008, Visual Studio 2008

July 24, 2008

Microsoft’s Visual Studio 2008 is amazing. It truly is an integrated development environment, because EVERYTHING is integrated. I just learnt about stored procedures today and how creating stored procedures is so easy in Visual Studio. Here’s the steps that you need.

  1. In Visual Studio, create a solution if you haven’t, and to that solution, add a project of the type “SQL Server Project”
  2. Next, right click on that project (not the solution) and add an item of the type “Stored Procedure”, and choose “Stored Procedure” as the sub-type that we want.
  3. You should see the stored procedure added to your project as a file. I’m using C#, so it’s added as a new C# file, with some template code added already.
  4. Try adding this code chunk:

    SqlPipe p = SqlContext.Pipe;
    p.Send(“Hello!”);

  5. Now we can test out our new stored procedure by right-clicking on the project and selecting “Deploy”. This will create a DLL file and copy it over the SQL Server.
  6. Open up SQL Server Management Studio and click on the button titled “Create a New Query’. You should see a screen pop up on the right.
  7. Enter this code:

    USE [YourDatabaseName]
    GO
    EXEC [dbo].[WhatYouNamedYourStoredProcedure]
    GO

  8. Now right-click in that code space and select “Execute”. If you experience this error: “Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option”, then execute the following query first:

    sp_configure ‘clr enabled’, 1
    go
    reconfigure
    go

  9. You should see the text “Hello!” appearing in your output window 🙂
  10. To do something that’s actually useful, we can add in the following code to our stored procedure, which essentially selects all records in our database and outputs them:

    SqlConnection conn = new SqlConnection(“Context Connection=true”);
    SqlCommand cmd = new SqlCommand(@”SELECT * FROM YourTable”, conn);

    conn.Open();

    SqlDataReader rdr = cmd.ExecuteReader();
    SqlContext.Pipe.Send(rdr);

    rdr.Close();
    conn.Close();

  11. Done! You have just created and run your first stored procedure. Wasn’t that easy?

Related Posts.