ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call addProduct.  The stored procedure takes the following input parameters.

1.  First you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure and output the result from the SqlDataReader

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "addProduct";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
  
            SqlParameter productName = new SqlParameter("@ProductName", "Teh");
            productName.SqlDbType = SqlDbType.NVarChar;
            productName.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(productName);

            SqlParameter supplerID = new SqlParameter("@SupplierID", 1);
            supplerID.SqlDbType = SqlDbType.Int;
            supplerID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(supplerID);

            SqlParameter categoryID = new SqlParameter("@CategoryID", 1);
            categoryID.SqlDbType = SqlDbType.Int;
            categoryID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(categoryID);

            SqlParameter quantityPerUnit = new SqlParameter("@QuantityPerUnit", "20 boxes of 12 oz.");
            quantityPerUnit.SqlDbType = SqlDbType.NVarChar;
            quantityPerUnit.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(quantityPerUnit);

            SqlParameter unitPrice = new SqlParameter("@UnitPrice", 12.99);
            unitPrice.SqlDbType = SqlDbType.Money;
            unitPrice.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitPrice);

            SqlParameter unitsInStock = new SqlParameter("@UnitsInStock", 6);
            unitsInStock.SqlDbType = SqlDbType.SmallInt;
            unitsInStock.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitsInStock);

            SqlParameter reorderLevel = new SqlParameter("@ReorderLevel", 2);
            reorderLevel.SqlDbType = SqlDbType.SmallInt;
            reorderLevel.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(reorderLevel);

            SqlParameter discontinued = new SqlParameter("@Discontinued", false);
            discontinued.SqlDbType = SqlDbType.Bit;
            discontinued.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(discontinued);

            int rowsAffected = cmd.ExecuteNonQuery();

            Response.Write(rowsAffected);
    }

In the above code you add the parameters required by the addProduct stored procedure. You specify the name, type, and value. Then add it to command object's parameters list. Then you execute the ExecuteNonQuery() method because you are not get a resultset back or a scalar value. The ExecuteNonQuery() method returns an int value, usually the rows that were affected value.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8