存储过程就是在数据库中写好的函数,C#通过调用存储过程来获得数据,可以在一定程度上提高数据库的安全性(将一些重要的数据封装了起来),那么如何在C#中调用存储过程呢?
一、存储过程
环境如下:
1、数据库Itcast2014中包含表TblStudent,表中数据如下:
2、 存储过程的名称是usp_separatepage,功能是将表中的数据分页显示,通过给定每页的行数和查询的页码,显示当前页的行。
3、第一个参数是查询的行数,第二个参数是需要查询的页码,第三个参数是返回值,返回表中总共的行数,第三个参数是返回值,返回表中总共的页数。
4、存储过程具体代码如下:
CREATE proc [dbo].[usp_separatepage]
@itemperpage int=7, --每页多少条
@currentpage int=1, --当前的页数
@itemcount int output, --总共多少条
@pagecount int output --总共多少页
as
begin
select t.tsid,t.tsname,tsaddress,t.tsphone,t.tsage,t.tsbirthday,t.tscardid
from (select *,rn=row_number() over(order by tsid asc) from TblStudent) as t
where t.rn between (@currentpage-1)*@itemperpage+1 and @currentpage*@itemperpage --分页的语句
set @itemcount=(select count(1) from TblStudent)
set @pagecount=CEILING((@itemcount*1.00)/@itemperpage)
end
二、第一种方法
环境如下:
1、创建一个控制台应用程序
2、在当前main方法所在类下写一个方法
3、第一种方法中使用的sql命令可以直接在数据库中使用,与其他的select语句同样的执行方法
4、给定参数时,返回值无须指定value,但是要指定direction
5、获得返回值的方法就是通过参数数组,给定下标获取。
6、方法如下:
/// <summary>
/// 执行带参数和返回值的存储过程的第一种方法,直接使用exec并指定参数运行
/// </summary>
/// <param name="page">选择要查看的页码</param>
/// <returns></returns>
private static DataTable GetData(int page)
{
DataTable dt = new DataTable();
string conStr = "server=.;database=Itcast2014;integrated security=true;";
//注意执行存储过程的语法,前面要加exec,后面的参数要指定,前面两个参数是输入的参数,后面两个是返回值
string cmdTxt = "exec usp_separatepage @itemperpage,@currentpage,@itemcount=@ic output,@pagecount=@pc output";
using (SqlDataAdapter adapter = new SqlDataAdapter(cmdTxt, conStr))
{
//指定需要的参数,第三个参数和第四个参数是返回值,无需指定value,但是要指定方向参数
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@itemperpage",SqlDbType.Int) {Value=7 },
new SqlParameter("@currentpage",SqlDbType.Int) {Value=page },
new SqlParameter("@ic",SqlDbType.Int) {Direction=ParameterDirection.Output },
new SqlParameter("@pc",SqlDbType.Int) {Direction=ParameterDirection.Output }
};
adapter.SelectCommand.Parameters.AddRange(paras);
adapter.Fill(dt);
//直接通过参数数组获得我们需要的返回值
int rowcount = Convert.ToInt32(paras[2].Value);
int pagecount = Convert.ToInt32(paras[3].Value);
}
return dt;
}
三、第二种方法
环境同第一种方法。
第二种方法与第一种方法有三点不同:
1、sql命令语句只给了一个存储过程的名称
2、在执行前,将命令类型设置成了存储过程
3、参数的名称需要和存储过程中的名称一致
/// <summary>
/// 执行带参数和返回值的存储过程的第二种方法,直接提交存储过程的名称
/// </summary>
/// <param name="page">选择要查看的页码</param>
/// <returns></returns>
private static DataTable GetData2(int page)
{
DataTable dt = new DataTable();
string conStr = "server=.;database=Itcast2014;integrated security=true;";
//命令参数直接给一个存储过程的名称
string cmdTxt = "usp_separatepage";
using (SqlDataAdapter adapter=new SqlDataAdapter(cmdTxt,conStr))
{
//*******************将命令类型设置成存储过程*********************
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
//指定需要的参数,第三个参数和第四个参数是返回值,无需指定value,但是要指定方向参数,参数的名称要和存储过程中相同
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@itemperpage",SqlDbType.Int) {Value=7 },
new SqlParameter("@currentpage",SqlDbType.Int) {Value=page },
new SqlParameter("@itemcount",SqlDbType.Int) {Direction=ParameterDirection.Output },
new SqlParameter("@pagecount",SqlDbType.Int) {Direction=ParameterDirection.Output }
};
adapter.SelectCommand.Parameters.AddRange(paras);
adapter.Fill(dt);
//直接通过参数数组获得我们需要的返回值
int rowcount = Convert.ToInt32(paras[2].Value);
int pagecount = Convert.ToInt32(paras[3].Value);
}
return dt;
}
四、main函数
main函数中主要是如何访问表格DataTable的每行数据。
static void Main(string[] args)
{
DataTable table = GetData2(2);
//遍历表格的所有行
for (int i = 0; i < table.Rows.Count; i++)
{
//遍历表格的所有列
for (int j = 0; j < table.Columns.Count; j++)
{
Console.Write(table.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.ReadKey();
}