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
Mua vé tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ tháng nào rẻ nhất
mua vé máy bay từ mỹ về vn
giá vé máy bay từ canada về Việt Nam
lịch bay từ nhật về việt nam hôm nay
vé máy bay vietjet từ hàn quốc về việt nam
Vé máy bay từ Đài Loan về VN
chuyến bay chuyên gia về việt nam