Wednesday, February 11, 2015

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:

No comments:

Post a Comment