Input on a stored sql procedure using C#

Question

In sql I normally execute my procedure using

exec dbo.usp_FCS 'TIMV','serial'

And I tried something somewhat the same in c# but it seems I got this wrong

   using (SqlConnection connection = new SqlConnection("Data Source=;Initial Catalog=;User ID=;Password="))
            {
                using (SqlCommand cmd = new SqlCommand("usp_FCS_GetUnitInfo_Takaya" + "'" + MachineName + " ','serial' " , connection))
                {
                        try
                    {
                           
                            connection.Open();
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                    }

                        catch (SqlException ex)
                    {
                        label6.Visible = true;
                        label6.Text = string.Format("Failed to Access Database!\r\n\r\nError: {0}", ex.Message);
                        return;
                    }
                }
            }

My question is,how can I give those 2 inputs 'TIMV' and 'serial' of my stored procedure using c#?

Edit:

I tried something like this:

 using (SqlCommand cmd = new SqlCommand("usp_FCS_GetUnitInfo_Takaya"  , connection))
                {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = MachineName;
                        cmd.Parameters.Add("@p2", SqlDbType.VarChar).Value = "serial";
                        try
                    {    my code...

And it is still not working

Possible duplicate of [Call a stored procedure with parameter in c#](http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp) — Jakub Jankowski, Oct 13 '16 at 09:11

Zohar Peled · Answer 1 · 2016-10-13T09:31:07.900

1

You use the Parameters collection of the SqlCommand class to send parameters to a stored procedure.

Suppose your parameter names are @p1 and @p2 (Please, for your sake, don't use names like this ever) - your c# code would look like this:

using (var cmd = new SqlCommand("usp_FCS_GetUnitInfo_Takaya", connection))
{
    cmd..CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = MachineName;
    cmd.Parameters.Add("@21", SqlDbType.VarChar).Value = "serial";

    try
    {
       // rest of your code goes here....

Note: use the SqlDbType value that fits the parameters data type.

edited Oct 13 '16 at 09:31

answered Oct 13 '16 at 09:15

Zohar Peled

79,642
10
69
121

Do I also need to add any assembly reference? – John Pietrar Oct 13 '16 at 09:21
Error CS1061 'int' does not contain a definition for 'Value' and no extension method 'Value' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?) – John Pietrar Oct 13 '16 at 09:23

Steve · Accepted Answer · 2016-10-13T09:42:46.223

1

The most correct way to add a parameter to an SqlCommand is through the Add method that allows you to specify the datatype of the parameter and, in case of strings and decimals, the size and the precision of these values. In that way the Database Engine Optimizer can store your query for reuse and be a lot faster the second time you call it. In your case I would write

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@mname", SqlDbType.NVarChar, 20).Value = MachineName;
cmd.Parameters.Add("@serial", SqlDbType.NVarChar, 20).Value = "serial";

This assumes that your stored procedure receives two parameters named EXACTLY @mname and @serial, the type of the parameters is NVarChar and the length expected is 20 char. To give a more precise answer we need to see at least the first lines of the sp.

In your code above also the execution of the command is missing. Just creating the command does nothing until you execute it. Given the presence of an SqlDataAdapter I think you want to fill a DataSet or a DataTable and use this object as DataSource of your grid. Something like this

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
yourDataGrid.DataSource = dt;

And if this is an ASP.NET app, also the DataBind call

yourDataGrid.DataBind();

edited Oct 13 '16 at 09:42

answered Oct 13 '16 at 09:30

Steve

213,761
22
232
286

I must ask why did this work?I mean i just added a data grid and filled the grid and somehow it worked? what gives? – John Pietrar Oct 13 '16 at 12:42
The important points are: Exact datatype,size and name for the parameters. The CommandType = CommandType.StoredProcedure is fundamentale otherwise the CommandText is interpreted as query (select, update...) Of course filling the DataTable (da.Fill(dt)) is mandatory to show anything on the grid – Steve Oct 13 '16 at 12:49
Let me explain what I have.I did not use your example,I used `AddWithValue`(I know its not ok but I'm trying to prove a point) .I have 2 procedures If i try to run the second one without running the first one the second one will trow an error.I tried to make it work every way possible, to add the parameters and nothing worked until I used the DataTable and I can't understand why? – John Pietrar Oct 13 '16 at 13:01
Uhm, difficult to say without seeing the new code. I suggest to post a new question with details about this problem. So many more people could see your problem and try to solve it – Steve Oct 13 '16 at 13:03

this.hart · Answer 3 · 2016-10-13T09:34:13.977

Try this:

   DataSet ds = new DataSet("dts");
        using (SqlConnection conn = new SqlConnection
          ("Data Source=;Initial  Catalog=;User ID=;Password="))
        {
            try
            {
    SqlCommand sqlComm = new  SqlCommand("usp_FCS_GetUnitInfo_Takaya",conn);
                sqlComm.Parameters.AddWithValue("@p1", MachineName);
                sqlComm.Parameters.AddWithValue("@p2", "serial");
                sqlComm.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = sqlComm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                label6.Visible = true;
                label6.Text = string.Format
              ("Failed to Access  Database!\r\n\r\nError: {0}", ex.Message);
                return;
            }

Read [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Zohar Peled, Oct 13 '16 at 09:32

Input on a stored sql procedure using C#

Edit:

3 Answers3