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();

            }
        }

转载于:https://www.cnblogs.com/onepc/archive/2011/10/28/2227604.html