c#中如何执行存储过程

8/3/2015来源:C#应用人气:1253

c#中如何执行存储过程

  1 using System;  2 using System.Collections.Generic;  3 using System.ComponentModel;  4 using System.Data;  5 using System.Drawing;  6 using System.Linq;  7 using System.Text;  8 using System.Threading.Tasks;  9 using System.Windows.Forms; 10  11 namespace 使用存储过程 12 { 13     using System.Data.SqlClient; 14     public partial class Form1 : Form 15     { 16         public Form1() 17         { 18             InitializeComponent(); 19         } 20         string connStr = "Data Source=.;Initial Catalog=MySchoolMoreData;Integrated Security=True"; 21  22         #region 调用没有参数的存储过程 +void btnNoPARAMAS_Click(object sender, EventArgs e) 23         /// <summary> 24         /// 调用没有参数的存储过程 25         /// </summary> 26         /// <param name="sender"></param> 27         /// <param name="e"></param> 28         PRivate void btnNoPARAMAS_Click(object sender, EventArgs e) 29         { 30             //SqlDataAdapter da = new SqlDataAdapter("select * from Student", connStr); 31             SqlDataAdapter da = new SqlDataAdapter("usp_getAllStuInfo", connStr); 32             DataTable dt = new DataTable(); 33             da.Fill(dt); 34             this.dgvList.DataSource = dt; 35         }  36         #endregion 37  38         #region 调用有输入参数的存储过程 +void btnHasparamas_Click(object sender, EventArgs e) 39         /// <summary> 40         /// 调用有输入参数的存储过程 41         /// </summary> 42         /// <param name="sender"></param> 43         /// <param name="e"></param> 44         private void btnHasParamas_Click(object sender, EventArgs e) 45         { 46             SqlDataAdapter da = new SqlDataAdapter("usp_getStuInfoBySexAndCname", connStr); 47             //1.如果传入了存储过程,必须告诉服务器按存储过程进行处理,否则就会按sql语句进行处理 48             da.SelectCommand.CommandType = CommandType.StoredProcedure; 49             //2.创建存储过程中所需要的参数,注意:名称必须与存储过程的参数名称对应 50             SqlParameter[] ps = {  51                                 new SqlParameter("@cname",this.cboClass.Text),//Text获取显示在下拉列表控件中的文本值 52                                 new SqlParameter("@Sex",rdoMale.Checked?"男":"女") 53                                 }; 54             //3.将参数传递给服务器使用 55             da.SelectCommand.Parameters.AddRange(ps); 56             DataTable dt = new DataTable(); 57             da.Fill(dt); 58             this.dgvList.DataSource = dt; 59         }  60         #endregion 61  62         #region 加载班级下拉列表数据和Dgv控件的分页数据 +void Form1_Load(object sender, EventArgs e) 63         /// <summary> 64         /// 加载班级下拉列表数据和Dgv控件的分页数据 65         /// </summary> 66         /// <param name="sender"></param> 67         /// <param name="e"></param> 68         private void Form1_Load(object sender, EventArgs e) 69         { 70             #region 加载下拉列表数据 71             SqlDataAdapter da = new SqlDataAdapter("select classid ,classname from classes where classid<@num", connStr); 72             SqlParameter p = new SqlParameter("@num", 15); 73             SqlParameter p2 = new SqlParameter("@num2", 150); 74             da.SelectCommand.Parameters.Add(p2); 75             da.SelectCommand.Parameters.Add(p); 76             DataTable dt = new DataTable(); 77             da.Fill(dt); 78             this.cboClass.DisplayMember = "classname"; 79             this.cboClass.ValueMember = "classid"; 80             this.cboClass.DataSource = dt; 81             #endregion 82  83             LoadDgvData(); 84         }  85         #endregion 86  87         int pageIndex = 1; //当前页索引 88         //int pageCount = 5; 89  90         #region 调用带输出参数和返回值的存储过程 +void btnOutput_Click(object sender, EventArgs e) 91         /// <summary> 92         /// 调用带输出参数和返回值的存储过程 93         /// </summary> 94         /// <param name="sender"></param> 95         /// <param name="e"></param> 96         private void btnOutput_Click(object sender, EventArgs e) 97         { 98             SqlDataAdapter da = new SqlDataAdapter("usp_GetCountByCnameAndSex", connStr); 99             //1.如果传入了存储过程,必须告诉服务器按存储过程进行处理,否则就会按sql语句进行处理100             da.SelectCommand.CommandType = CommandType.StoredProcedure;101             //2.创建存储过程中所需要的参数,注意:名称必须与存储过程的参数名称对应102             SqlParameter[] ps = { 103                                 new SqlParameter("@cname",this.cboClass.Text),//Text获取显示在下拉列表控件中的文本值104                                 new SqlParameter("@Sex",rdoMale.Checked?"男":"女"),105                                 //创建输出参数的时候,没有必要赋值106                                 //创建一个输出参数,服务器最终将输出参数的值返回到这个参数对象的Value属性中107                                 new SqlParameter("@totalCount",100),108                                 new SqlParameter("@cnt",SqlDbType.Int),109                                 new SqlParameter("@result",SqlDbType.Int)110                                 };111             //3.一定要修改输出参数的方向,否则服务器会将所有参数当成输入参数进行处理,需要客户端传入值,如果没有就报错112             ps[0].Direction = ParameterDirection.Input;//默认就是input,不设置也没有关系113             //ps[2].Direction = ParameterDirection.Output;//设置参数的方向为输出参数114             //指定方向,是向服务器发送返回对应类型输出参数或者返回值的请求115             ps[3].Direction = ParameterDirection.Output;116             ps[4].Direction = ParameterDirection.ReturnValue;117             //3.将参数传递给服务器使用118             da.SelectCommand.Parameters.AddRange(ps);119             DataTable dt = new DataTable();120             da.Fill(dt);121             this.dgvList.DataSource = dt;122             this.lblMsg.Text = "总人数是:" + ps[2].Value + ",指定性别的人数是:" + ps[3].Value + ",返回值是:" + ps[4].Value;123         } 124         #endregion125 126         #region 下一页 + void btnNext_Click(object sender, EventArgs e)127         /// <summary>128         /// 下一页129         /// </summary>130         /// <param name="sender"></param>131         /// <param name="e"></param>132         private void btnNext_Click(object sender, EventArgs e)133         {134             if (pageIndex.ToString() == System.Configuration.ConfigurationManager.AppSettings["totalPageCount"])135             {136                 MessageBox.Show("没有下一页了");137                 return;138             }139             pageIndex++;140             LoadDgvData();141         } 142         #endregion143 144         #region 获取分页数据 +void LoadDgvData()145         /// <summary>146         /// 获取分页数据147         /// </summary>148         private void LoadDgvData()149         {150             string count = System.Configuration.ConfigurationManager.AppSettings["pageCount"];151             SqlParameter[] ps ={152                                 new SqlParameter("@pageIndex",pageIndex),153                                 new SqlParameter("@pageCount",count),154                                 new SqlParameter("@totalPageCount",SqlDbType.Int)155                                };156             ps[2].Direction = ParameterDirection.Output;//修改参数的方法为输出参数--发送请求157             this.dgvList.DataSource = SqlHelper.ExecuteTable("usp_getPageData", CommandType.StoredProcedure, ps);158             System.Configuration.ConfigurationManager.AppSettings["totalPageCount"] = ps[2].Value.ToString();159         } 160         #endregion161 162         #region 上一页 +void btnPre_Click(object sender, EventArgs e)163         /// <summary>164         /// 上一页165         /// </summary>166         /// <param name="sender"></param>167         /// <param name="e"></param>168         private void btnPre_Click(object sender, EventArgs e)169         {170             if (pageIndex == 1)171             {172                 MessageBox.Show("没有上一页了");173                 return;174             }175             pageIndex--;176             LoadDgvData();177         } 178         #endregion179     }180 }