CODEWITHSUNDEEP
c#sql-servert-sqlstored-procedures
John Pietrar
John Pietrar

Reputation: 543

Input on a stored sql procedure using C#

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

Upvotes: 1

Views: 168

Answers (3)

Steve
Steve

Reputation: 216313

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();

Upvotes: 1

this.hart
this.hart

Reputation: 328

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;
            }

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

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.

Upvotes: 1

Related Questions