Using Stored Procedures in C#

Question

I am working on a database application in C#. I had a requirement to display data on a table and I am now done with that. But my business logic is hardcoded to my code. Now I want to go ahead and use Stored procedures with my code. What are the modifications that I need to do. A simple list of steps would be enough :)

SqlConnection myConnection = new SqlConnection("user id=dbblabla;" + 
   "password=1234;server=localhost\\SQLEXPRESS;" + 
   "Trusted_Connection=yes;" + 
   "database=myDB; " + 
   "connection timeout=30");

try
{
 myConnection.Open();
} catch (SqlException excep){
 Console.WriteLine(excep.StackTrace);
}

String selectionQuery = "SELECT * FROM myTable";
SqlDataAdapter myAdapter = new SqlDataAdapter(selectionQuery,myConnection);

DataSet ds = new DataSet();
myAdapter.Fill(ds,"AllInfo");

dataGridSearchOutput.DataSource = ds.Tables["AllInfo"].DefaultView;

I started from creating a new SQL command but I am not sure I am using the correct way.

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.StoredProcedure;

Answer 1

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.StoredProcedure;

This will not work correctly, because you are not using a store procedure, the above command should be called like this

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.Text;

If you are calling a stored procedure, you would call it like this:

SqlCommand newCommand = new SqlCommand("spYourProcedure");
newCommand.CommandType = CommandType.StoredProcedure;

See here in the MSDN for more details, it will also go into using parameters

Answer 2

Stored-Procedure

CREATE PROCEDURE addemp
     @eno int,
     @ename varchar(50),
     @edate datetime
AS
  declare @p int

  select @p=count(*) from emp
      where eno=@eno
  if @p=0
     begin
       insert into emp
         values (@eno,@ename,@edate)
     end        
    RETURN

C# code

    SqlConnection cn = new SqlConnection(@"conn_str");
    SqlCommand cmd = new SqlCommand("addemp", cn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@eno", 10);
    cmd.Parameters.AddWithValue("@ename", "Mr.Xyz");
    cmd.Parameters.AddWithValue("@edate", DateTime.Parse("1-1-2002"));

    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();

Answer 3

This is not exactly an answer to your question, but in your case I would not bother with turning your queries into stored procedures, as it is potentially a lot of extra work with no real benefits. In days of yore, stored procedures in SQL Server were faster than so-called ad hoc SQL queries, because SQL Server could calculate and cache the execution plans for your queries. In modern versions of SQL Server, however, the database will cache the execution plan for each query after it has been run once, so after the initial execution of the ad hoc query it will be executed just as fast as a stored procedure.

Also, one real and major advantage of keeping your queries in your application code is that the queries will be under source control and versionable (assuming you're using source control, of course). It is possible to version stored procedures in SQL Server, but this is more work and people rarely do it.

Finally, moving your queries to stored procedures will mean rewriting substantial portions of your application code, since stored procedures are not accessed in the same way as ad hoc SQL queries. It might have made sense to write your application using stored procedures at the beginning, but it makes a lot less sense to convert your application to using stored procedures after you've already written it to use ad hoc SQL queries.

Answer 4

You just need to specify the stored procedure name to the SqlCommand constructor and set the CommandType to CommandType.StoredProcedure. Also it is a good practice to wrap disposable objects in using block. SqlConnection, SqlCommand and SqlAdapter are disposable objects. Here is a modified code.

string connectionString = "user id=dbblabla;" + 
                        "password=1234;server=localhost\\SQLEXPRESS;" + 
                        "Trusted_Connection=yes;" + 
                        "database=myDB; " + 
                        "connection timeout=30";
DataSet ds = new DataSet();
using(SqlConnection myConnection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("yourprocedure", myConnection))
{
    SqlDataAdapter myAdapter = new SqlDataAdapter();
    myAdapter.SelectCommand = command;
    myAdapter.Fill(ds,"AllInfo");
}
dataGridSearchOutput.DataSource = ds.Tables["AllInfo"].DefaultView;

Answer 5

stored procedure

CREATE PROCEDURE  procedure 

AS
BEGIN

    SET NOCOUNT ON;
    SELECT field1,field2 from tbl
END
GO

code

using(SqlConnection con = new SqlConnection("user id=dbblabla;password=1234;server=localhost\\SQLEXPRESS; database=myDB; connection timeout=30"))
        {
            SqlCommand cmd = new SqlCommand("procedure",con);
            cmd.CommandType= System.Data.CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridSearchOutput.DataSource = dt;           

        }

why i used using is check this link

Answer 6

For what it's worth, you'll want to go a long way beyond simply wrapping your stored procedures in the codebehind. For some insight into how far, check out this article:

Code on the Road: ExampleCode != ProductionCode

Ad-hoc SQL is one of the simplest to fix, yet most prevalent forms of code duplication that we come across as developers. Even just a few minutes of thinking about how to organize your database interaction will pay off in spades in the form of maintainability and development pace.

Answer 7

I don’t understand why people forget they are in an object oriented language when it comes to the data access code. You should not be writing the same code over and over again. First, you should have a base class that is used in all of your projects. This base class should take care of the connection string, logging exceptions, etc. I wrote one years ago and haven’t changed it much since.

Look at the following for samples that would be in this base class:

protected SqlCommand GetNewCmd()
{
    SqlCommand objCmd = new SqlCommand();
    objCmd.Connection = new SqlConnection(this.ConnString);
    objCmd.CommandType = CommandType.StoredProcedure;
    return objCmd;

}

protected SqlCommand GetNewCmd(string CmdText)
{
    SqlCommand objCmd = new SqlCommand(CmdText, 
                             new SqlConnection(this.ConnString));
    objCmd.CommandType = CommandType.StoredProcedure;
    return objCmd;
}

protected DataTable GetTable(SqlCommand objCmd)
{
    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter();

    try
    {
        da.SelectCommand = objCmd;
        da.Fill(dt);

        dt.DefaultView.AllowNew = false;
    }
    catch (Exception ex)
    {
        LogException(ex);
        throw;
    }
    finally
    {
        Close(objCmd);
        da.Dispose();
        da = null;

    }

    return dt;

}

We have GetTable(), GetDataSet(), ExecuteScalarInt(), ExecuteScalarGuid(), etc. and a bunch of overloads for each.

So these are in my base class and I inherit from this class to do my project specific work. But now this is greatly simplified like these examples:

public DataTable GetStages(int id)
{
    SqlCommand cmd = GetNewCmd("dbo.GetStages");
    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
    return GetTable(cmd);
}

public void DeleteStage(int id)
{
    SqlCommand cmd = GetNewCmd("dbo.DeleteStage");
    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
    ExecuteNonQuery(cmd);
}

As a side benefit, I can script out this code based on the stored proc. Here is the code to do that. So it saves me a lot of typing especially when there are a lot of parameters.

Finally, using other object oriented techniques I get my code down to the following:

GridView1.DataSource = cApp.DB.GetStages(id);
GridView1.DataBind();

(Most of the time there is a business object in the middle of that, but you get the idea.) All the connection string, exception logging, etc is encapsulated in that 1 line.

Another side benefit is that your base class can get way more involved then you ever would if you are copying code every time. For example, we log exceptions to a database table. Only if that fails does it then log it to a text file. This extensive logic is fine if it lives in one place in the base class, but its not something you are going to want to copy and paste all over your project.

This also make it easy to incorporated some best practices such as opening the connection as late as possible and closing it as soon as possible.