C#操作数据库,分页、执行存储过程等 [一] - ADO.NET入门之中
先在MSSQL创建一个分页存储过程:
use wentest
if exists(select * from sys.sysobjects where type='p' and name='myfy_one')
drop procedure myfy_one
go
create procedure myfy_one
@size int, --每页大小
@number int --分页编码
with encryption --加密
as
begin
--declare @sql varchar(256)
declare @did datetime
declare @curr int
set @curr = @size * (@number -1) +1
select top (@curr) @did = datet from adonet order by datet desc
select top (@size) * from adonet where datet<= @did order by datet desc
end
C#里执行存储过程的语句,输入参数MS要同存储过程里的参数一样。
private void button5_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "myfy_one"; //存储过程名
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.CreateParameter();
param.Direction = ParameterDirection.Input; //输入参数
param.ParameterName = "@size";
param.DbType = DbType.Int32;
param.Value = 100;
SqlParameter param1 = cmd.CreateParameter();
param1.Direction = ParameterDirection.Input;
param1.ParameterName = "@number";
param1.DbType = DbType.Int32;
param1.Value = 3;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param1);
SqlDataReader sread = cmd.ExecuteReader();
while (sread.Read())
{
ListViewItem lv = new ListViewItem(sread.GetString(0));
lv.SubItems.Add(sread.GetString(1));
lv.SubItems.Add(sread.GetString(2));
lv.SubItems.Add(sread.GetDateTime(3).ToString());
listView1.Items.Add(lv);
}
sread.Close();
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}
计数的存储过程
use wentest
if exists(select * from sys.sysobjects where type='p' and name='sall')
drop procedure sall
go
create procedure sall
@allnum int output
with encryption --加密
as
select @allnum= COUNT(*) from adonet
带输出参数的存储
private void button6_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("sall",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@allnum",SqlDbType.Int,4);
cmd.Parameters["@allnum"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
MessageBox.Show(cmd.Parameters["@allnum"].Value.ToString());
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}