CODEWITHSUNDEEP
c#sql-servert-sqlstored-proceduresparameters
shucode
shucode

Reputation: 55

C# TSQL Stored Procedure don't pass parameter?

I don't know if this query is worth asking or not but it would good to understand what does and doesn't work and why.

I have a table like this called tbl_Person_Details:

ID | Name | Likes
-----------------
0  | Jon  | Fish
1  | Doey | Coding

Code behind like this (example shows two functions but there could be more):

public void updatePerson()
{
    string sID = GridView1.SelectedDataKey[0].ToString();

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    con.Open();

    SqlCommand comm = new SqlCommand("spUpdatePersonBasics", con);
    comm.CommandType = CommandType.StoredProcedure;

    comm.Parameters.Add(new SqlParameter("@Name", tbName.Text.ToString()));
    comm.Parameters.Add(new SqlParameter("@ID", sID));

    object tmp = comm.ExecuteScalar();
    con.Close();

    lblStatus.Text = "<img src=\"img/icons/green-tick.gif\" /> <strong>>Person Details have been updated!</strong>";
}

public void updatePersonLikes()
{
    string sID = GridView1.SelectedDataKey[0].ToString();

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    con.Open();

    SqlCommand comm = new SqlCommand("spUpdatePersonBasics", con);
    comm.CommandType = CommandType.StoredProcedure;

    comm.Parameters.Add(new SqlParameter("@Likes", tbName.Text.ToString()));
    comm.Parameters.Add(new SqlParameter("@ID", sID));

    object tmp = comm.ExecuteScalar();
    con.Close();

    lblStatus.Text = "<img src=\"img/icons/green-tick.gif\" /> <strong>>Person likes have been updated!</strong>";
}

Stored procedure:

ALTER PROCEDURE [dbo].[spUpdatePersonBasics]
    @ID int, 
    @Name varchar(50),
    @Likes varchar(50)
AS
    UPDATE tbl_Person_Details
    SET Name = @Name,
        Likes = @Likes
    WHERE ID = @ID

    SELECT CAST(scope_identity() AS int)

Issue:

As we can see all fine and dandy so far. However upon debugging we can have multiple scenarios which cause issues. These are below:

  1. On my code behind functions I have to declare all parameters even at times when I only want to update one parameter.
  2. If we add a new column for instance called 'Title' and the front-end HTML has a dropdown list for it. We can update our code behind by adding one more line to our stored procedure and one line parameter to any of the functions. But because now the stored procedure will have a new parameter then in code behind functions where we are not updating the 'Title' we will get a SQL Exception error because we are not supplying data via this given function back to the stored procedure.

I think the design here is bad but I think the only possible solution to me is to have multiple stored procedures i.e. one for each function. Any ideas if this is the best way of doing it and is it robust?

What I actually need is not to update some columns if they already exist. For instance I wouldn't want to update the Name of the row when I am updating the Likes.

Upvotes: 1

Views: 679

Answers (1)

marc_s
marc_s

Reputation: 754348

You could just pass in NULL for either one of the two values, if you don't want to update it, and then adapt your stored procedure to this:

ALTER PROCEDURE [dbo].[spUpdatePersonBasics]
    @ID int, 
    @Name varchar(50),
    @Likes varchar(50)
AS
    UPDATE tbl_Person_Details
    SET Name = ISNULL(@Name, Name)
        Likes = ISNULL(@Likes, Likes)
    WHERE ID = @ID

So if you pass in @Name = NULL, then the UPDATE would basically update Name to the value of Name - a "non-update", really.

To pass in NULL from C#, use DBNull.Value:

comm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = DBNull.Value;

I choose to use the .Add() method, and I choose to explicitly specify the SqlDbType for the parameter (including the length, for string-based parameters).

If you want to pass the parameter - just use this:

comm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = tbName.Text;

Since the .Text property already is a string, there's really no point in calling .ToString() on it ....

Upvotes: 2

Related Questions