ASP.NET 中调用SQL Server 存储过程- -
存储过程是存储在服务器上的一组预编译的SQL语句,它是一组为了完成特定功能的SQL语句集。存储过程具有对数据库立即访问的功能,信息处理极为迅速。另外,存储过程在服务器端运行,独立于ASP.NET程序,便于修改。使用存储过程可以减少数据库操作语句在网络中的传输。
一、在数据库中创建存储过程
下面创建了一个存储过程,用于向名为“Student”的表中插入一条记录:
CREATE PROCEDURE StuProcedure
@number char(10),
@name char(10),
@sex char(2),
@age int
as
insert into Student(学号,姓名,性别,年龄) values (@number, @name, @sex, @age)
return
GO
二、APS.NET中调用存储过程
SqlConnection stuConn = null;
string dataSource = "Data Source = localhost;";
string initialCatalog = "Initial Catalog = TestDatabase;";
string userId = "User ID = sa;";
string password = "Password =;";
string connectionStr = dataSource + initialCatalog + userId + password;
try
{
stuConn = new SqlConnection(connectionStr);
stuConn.Open();
SqlCommand stuCmd = new SqlCommand("StuProcedure",stuConn);
stuCmd.Connection = stuConn;
stuCmd.CommandType = CommandType.StoredProcedure;
stuCmd.Parameters.Add( "@number", SqlDbType.Char, 10, "学号" );
stuCmd.Parameters.Add( "@name", SqlDbType.Char, 10, "姓名" );
stuCmd.Parameters.Add( "@sex", SqlDbType.Char, 2, "性别" );
stuCmd.Parameters.Add( "@age", SqlDbType.Int, 4, "年龄" );
stuCmd.Parameters[0].Value = "10000001";
stuCmd.Parameters[1].Value = "ZHANG";
stuCmd.Parameters[2].Value = "男";
stuCmd.Parameters[3].Value = 25;
stuCmd.ExecuteNonQuery();
}
catch(Exception ex)
{
}
finally
{
if(stuConn != null)
{
stuConn.Close();
stuConn = null;
}
}