ADO.net用存储过程来代替sql语句的操作

 

namespace 执行语句存储过程
{
    public static  class SqlHelper
    {
        static readonly string constr = ConfigurationManager.ConnectionStrings["Myconstr"].ConnectionString;

        public static int ExecuteNoneQuery(string sql,CommandType cmdType,params SqlParameter[] p ) 
        {
            using (SqlConnection conn=new SqlConnection(constr))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    if (p!=null)
                    {
                        cmd.Parameters.AddRange(p);
                    }
                    cmd.CommandType = cmdType;
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }       
        }
        public static int ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] p) {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (p != null)
                    {
                        cmd.Parameters.AddRange(p);
                    }
                    cmd.CommandType = cmdType;
                    conn.Open();
                    return (int)cmd.ExecuteScalar();
                }
            }  
        }

        public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] p)
        {
            SqlConnection conn = new SqlConnection(constr);
            using (SqlCommand cmd=new SqlCommand(sql,conn))
            {
                if (p!=null)
                {
                    cmd.Parameters.AddRange(p);
                }
                cmd.CommandType = cmdType;
                try
                {
                    conn.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch 
                {
                    conn.Close();
                    conn.Dispose();
                    throw;
                }
            }
        }
    }
}

 

 

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn转账_Click(object sender, EventArgs e)
        {
            string from = txtBankFrom.Text;
            string To = txtBankTo.Text;
            double money = Double.Parse(txtMoney.Text);
            string sql="usp_transfer";
            SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@from", SqlDbType.NVarChar) { Value = from }, new SqlParameter("@to", SqlDbType.NVarChar) { Value = To }, new SqlParameter("@money", SqlDbType.Int) { Value = money }, new SqlParameter("@state", SqlDbType.Bit) { Direction = ParameterDirection.Output } };
            int num=SqlHelper.ExecuteNoneQuery(sql,CommandType.StoredProcedure,sp);
            if (num>0)
            {
                MessageBox.Show("成功转账");
            }
        }
    }

select * from bank
go
alter proc usp_transfer
@from char(4),
@to char(4),
@money money,
@state bit output
as
begin
    
    declare @banlance money,@sum int=0
    
    --.查询@from中的余额
    set @banlance=(select balance from bank where bank.cId=@from )
    --2检查余额是否充足
    if @banlance-@money>10 begin
    
    --3.开始事务进行转账
    begin transaction
        --减钱
        update bank set balance=bank.balance-@money where bank.cId=@from
        set @sum=@sum+@@error
        
        --
        update bank set balance=bank.balance+@money where bank.cId=@to
        set @sum=@sum+@@error
        
        if @sum=0 begin
        set @state=1
        commit transaction
        end
        
        else    
        begin
        set @state=0
        rollback transaction
        end
    end
    else
    begin
    set @state=0
    end
end

 DECLARE @ok bit
exec usp_transfer @from='0001',@to='0002',@money=1000,@state=@ok output

 select @ok

namespace 执行语句存储过程
{
    public partial class Form1 : Form
    {
        int pageIndex;
        int pageNum;
        int pageCount;
        public Form1()
        {
            InitializeComponent();
            pageIndex = 1;
            pageNum = 8;
            pageCount = 0;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadData();
        }

        private void LoadData()
        {
            List<Model> ls = new List<Model>();
            string sql = "usp_row";
            SqlParameter[] sp = new SqlParameter[] { 
                        new SqlParameter("@pagenum",SqlDbType.Int){Value=pageNum},
                        new SqlParameter("@pageindex",SqlDbType.Int){Value=pageIndex},
                        new SqlParameter("@recordcount",SqlDbType.Int){Direction=ParameterDirection.Output},
                        new SqlParameter("@pagecount",SqlDbType.Int){ Direction=ParameterDirection.Output}};
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.StoredProcedure, sp))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Model m = new Model();
                        m.CustomerID = reader.GetString(0);
                        m.CompanyName = reader.GetString(1);
                        m.City = reader.GetString(2);
                        m.Address = reader.GetString(3);
                        m.Phone = reader.GetString(4);
                        ls.Add(m);
                    }
                }
            }
            this.dataGridView1.DataSource = ls;
            label1.Text = "当前共" + sp[2].Value + "条," + "分" + sp[3].Value + "页";
            pageCount =(int)sp[2].Value;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (pageIndex > 1)
            {
                pageIndex--;

            }
            else {
                pageIndex = 1;
            }
            LoadData();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (pageIndex <pageCount)
            {
                pageIndex++;

            }
            else
            {
                pageIndex = 1;
            }
            LoadData();
        }
    }
}

 class Model
    {
        //                        t.CustomerID,
        //                        t.CompanyName,
        //                        t.City,
        //                        t.[Address],
        //                        t.Phone
        public string  CustomerID { set; get; }
        public string CompanyName { set; get; }
        public string City { set; get; }
        public string Address { set; get; }
        public string Phone { set; get; }

 

select * from Customers
--参数：
    --每页大小pagesize
    --当前用户要查看第几页
    --总页数
    --总条数
    go
alter proc  usp_row
@pagenum int,
@pageindex int,
@recordcount int output,
@pagecount int output
     as
     begin
        --1.分页查询语句
        select 
        t.CustomerID,
        t.CompanyName,
        t.City,
        t.[Address],
        t.Phone

        from(select *,rn=ROW_NUMBER()over(Order by CustomerId asc) from Customers) as t
        where  t.rn between @pagenum*(@pageindex-1)+1 and @pagenum*@pageindex
        --2.返回总条数
        set @recordcount=(select COUNT(*) from Customers)
        --3.计算总页数，并返回
        set @pagecount=CEILING(@recordcount*1.0/@pagenum)
    end
    declare @num int
    set @num=3
    declare @num2 int
    set @num2=2
    exec usp_row @pagenum=4,@pageindex=3,@recordcount=@num output,@pagecount=@num2 output
    select @num
    select @num2