目的:通过C#.NET调用SQL Server数据库中定义的存储过程。
示例一:
存储过程名:sp_login
输入参数1:@username nvarchar(50)
输入参数2:@password nvarchar(50)
输出参数:@nickname nvarchar(50)
返回值:@retval int
using (SqlConnection sqlConn = new SqlConnection("Data Source=(local);Initial Catalog=EG;User ID=user;Password=123456"))
{
sqlConn.Open();
SqlCommand sqlCommand = new SqlCommand("sp_login", sqlConn);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@username", System.Data.SqlDbType.NVarChar));
sqlCommand.Parameters["@username"].Value = "user01";
sqlCommand.Parameters.Add(new SqlParameter("@password", System.Data.SqlDbType.NVarChar));
sqlCommand.Parameters["@password"].Value = "password";
sqlCommand.Parameters.Add(new SqlParameter("@nickname", System.Data.SqlDbType.NVarChar, 50));// 注意:对于输出参数,必须指定参数的尺寸
sqlCommand.Parameters["@nickname"].Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(new SqlParameter("@retval", System.Data.SqlDbType.Int));
sqlCommand.Parameters["@retval"].Direction = ParameterDirection.ReturnValue;
sqlCommand.ExecuteNonQuery(); // 开始执行存储过程
// 读取返回值及输出参数的值
int retval = (int)sqlCommand.Parameters["@retval"].Value;
string nickname = (string)sqlCommand.Parameters["@nickname"].Value;
}
示例二:
存储过程名:sp_report
输入参数1:@max int
输入参数2:@min int
输出一张表
using (SqlConnection sqlConn = new SqlConnection("Data Source=(local);Initial Catalog=EG;User ID=user;Password=123456"))
{
sqlConn.Open();
SqlCommand sqlCommand = new SqlCommand("sp_report", sqlConn);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@max", System.Data.SqlDbType.Int));
sqlCommand.Parameters["@max"].Value = 10;
sqlCommand.Parameters.Add(new SqlParameter("@min", System.Data.SqlDbType.Int));
sqlCommand.Parameters["@min"].Value = 0;
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataTable table = new DataTable("Report");
adapter.Fill(table);
// 读取返回的表
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
// table.Rows[i][j]
}
}
}