How to use SQL-Server Stored Procedures?

Question

over the past week or so I've been building an ASP site that connects to a Sql-Server 2008 database. I've never used Stored procedures and I was wondering if anyone could give me some guidance on how to create and how to use them within an ASP method. I'm trying to make the code of the website as simple and elegant as possible. Here's the code I'm trying to change into a stored procedure:

    private void ExecuteInsert(string name, string type)
{
    SqlConnection conn = new SqlConnection(GetConnectionStringHM());
    string sql = "INSERT INTO tblSoftwareTitles (SoftwareName, SoftwareType) VALUES " 
        +"(@SoftwareName,@SoftwareSystemType)";

    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlParameter[] param = new SqlParameter[2];
        //param[0] = new SqlParameter("@SoftwareID);
        param[0] = new SqlParameter("@SoftwareName", SqlDbType.NVarChar, 200);
        param[1] = new SqlParameter("@SoftwareType", SqlDbType.Int);

        param[0].Value = name;
        param[1].Value = type;

        for (int i= 0; i < param.Length; i++)
        {
            cmd.Parameters.Add(param[i]);
        }
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        string msg ="Insert Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }

    finally
    {
        conn.Close();
    }

    }

This is just a Simple insert that takes two parameters from an entry form and inserts them into the database. Any Help with this would be much appreciated as I feel it would be a useful thing to know later on down the line. Thanks in advance!

Answer 1

You should look in to MSDN basics: http://msdn.microsoft.com/en-us/library/bb896274.aspx

You don't need to complicate things using for loop.

try
  {
     sqlConnection = new SqlConnection(dbConnectionString);
     SqlCommand command = new SqlCommand(sql, sqlConnection);
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = SoftwareNameHere;
     command.Parameters.Add("@SoftwareType",  SqlDbType.Int).Value = SoftwareTypeHere;
     sqlConnection.Open();
     return command.ExecuteNonQuery();
  }
catch (SqlException ex)
  {
     Console.WriteLine("SQL Error" + ex.Message.ToString());
     return 0;
  }
finally
  {
     conn.Close(); 
  }

If you are using .NET 3.5 or above, you can use the USING code block which takes care of the disposal of your resources. I am not entirely sure, but from what I remember this was introduced with .NET 3.5 to replace Try/Finally code block (which required developers to dispose the resources like connection object manually through code).

using (SqlConnection con = new SqlConnection { dbConnectionString })
{
    con.Open();
    try
    {   
        using (SqlCommand command = new SqlCommand { CommandType = CommandType.StoredProcedure, Connection = con, CommandTimeout = 300, CommandText = "sp_Test" })      
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = SoftwareNameHere;
            command.Parameters.Add("@SoftwareType",  SqlDbType.Int).Value = SoftwareTypeHere;

            command.ExecuteNonQuery();
        }
    }
    catch(SqlException ex)
    {
        //ex.ToString message here;
    }
}

Answer 2

The answer you're looking for is at this SO post...

https://stackoverflow.com/a/4561443/1246574

The one thing I would improve upon for the accepted answer in that post, is the example in that answer doesn't use any USING statements. It would be better to have the connection and command within USING statements so they are automatically disposed.

Another approach would be to use the Microsoft Enterprise Library for interacting with your SQL Server DB. I think it's easier than using plain old SqlConnection and SqlCommand.

Answer 3

Since your code already uses parameters, you are 90% of the way there. All you have to do is:

1. Put the insert statement into a stored procedure, keeping the same parameter definitions as the dynamic statement.
2. Change CommandType.Text to CommandType.StoredProcedure
3. Change the command text to be just the name of the stored procedure.

Answer 4

Having done some reading on the subject I've come across some useful articles that really do question my need for stored procedures.

This article gives a good Viewpoint on how stored procedures can be more of a hassle than a help and are quite clunky and tedious in terms of coding, debugging ad error reporting.

http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

And this article (linked in the one above) gives a good explanation on parametrized, explaining why they are necessary to reduce the risk of sql injections and also raises the point that these parametrized queries are cached in a similar way to procedures, giving them comparable performance gains

http://www.uberasp.net/getarticle.aspx?id=46

I feel that In my situation keeping parametrized sql Queries coded into my ASP pages will be the smartest move as these pages will be stored on a server and accessed by clients. I imagine if this were an application installed on several client machines hard coding the SQL wouldn't be a desirable option and so Stored procedures would be the best way to go (to my knowledge)

A follow Up to Stored procedures verses Parametrized sql can be found here with different links to each side of the argument if those are interested.

http://www.codinghorror.com/blog/2005/05/stored-procedures-vs-ad-hoc-sql.html

Hope this little answer helps out anyone else considering using stored procedures over parametrized SQL and vice-versa