存储过程（Stored Procedure）即用来存储数据表操作的一个过程，是把对数据表操作的方法存储到一起的一个对象，是存储在数据库中。
优点：1.降低网络传输数据量：通过存储过程的名称和参数传递即可调用执行，不用传输sql。
2.执行效率高：sqlserver会事先将存储过程编译成可执行的二进制代码，运行存储过程时，无需再次编译。
3.封装性：也可叫模块式编程，将实现某种功能的多条sql封装到一个对象中，可多次重复调用，可移植性强。
4.安全：可针对不同的权限的用户使用不同的存储过程。
下面讲C#如何调用写好的存储过程，下面是对数据操作封装几个函数
1 //执行对表的增删改操作的sql语句
2 public static int ExecuteCommand(string text)
3 {
4 using (SqlConnection conn = new SqlConnection(connectionString))
5 {
6 conn.Open();
7 using (SqlCommand cmd = new SqlCommand(text, conn))
8 {
9 int i = cmd.ExecuteNonQuery();
10 return i;
11 }
12 }
13 }
14
15 public static int ExecStoredProcedure(string procName, params SqlParameter[] parameters)
16 {
17 int rtn = 0;
18 using (SqlConnection conn = new SqlConnection(connectionString))
19 {
20 conn.Open();
21 using (SqlCommand cmd = conn.CreateCommand())
22 {
23 SqlTransaction st = conn.BeginTransaction();
24 cmd.Transaction = st;
25 try
26 {
27 cmd.CommandText = procName;
28 cmd.CommandType = CommandType.StoredProcedure;
29 cmd.Parameters.AddRange(parameters);
30 rtn= cmd.ExecuteNonQuery();
31 st.Commit();
32 return rtn;
33 }
34 catch(SqlException sqlex)
35 {
36 st.Rollback();
37 throw sqlex;
38 }
39 }
40 }
41 }
42 public static int ExecuteStoredProcedure(string procName,
43 params SqlParameter[] parameters)
44 {
45 using (SqlConnection conn = new SqlConnection(connectionString))
46 {
47 conn.Open();
48 using (SqlCommand cmd = conn.CreateCommand())
49 {
50 cmd.CommandText = procName;
51 cmd.CommandType = CommandType.StoredProcedure;
52 cmd.Parameters.AddRange(parameters);
53 return cmd.ExecuteNonQuery();
54 }
55 }
56
57 }
推荐用第三种方式，用到了事务处理，若只要有一条不通过全部回滚，避免脏数据的产生。也可以在存储过程中使用事务，后续会继续更新...
1 protected void Button1_Click(object sender, EventArgs e)
2 {
3 string mainName = "圆梦组";
4 string detailName="刘能|赵本山|沈燕|欧阳锋";
5 string detailAge="23|32|18|19";
6 int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
7 }
8 protected void Button2_Click(object sender, EventArgs e)
9 {
10 string mainName = "起航组2";
11 string detailName="段毅2|乔峰2|杨过2|李莫愁2";
12 string detailAge="18|28|jj|35";
13 SqlParameter[] sps = new SqlParameter[] {
14 new SqlParameter("@mainName",mainName),
15 new SqlParameter("@detailNameStr",detailName),
16 new SqlParameter("@detailAgeStr",detailAge)
17 };
18 try
19 {
20 int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
21 }
22 catch (Exception ex)
23 {
24 Response.Write(ex.Message);
25 }
26
27 }
28 protected void Button3_Click(object sender, EventArgs e)
29 {
30 string mainName = "起航组2";
31 string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
32 string detailAge = "18|28|jj|35";
33 SqlParameter[] sps = new SqlParameter[] {
34 new SqlParameter("@mainName",mainName),
35 new SqlParameter("@detailNameStr",detailName),
36 new SqlParameter("@detailAgeStr",detailAge)
37 };
38 try
39 {
40 int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
41 }
42 catch (Exception ex)
43 {
44 Response.Write(ex.Message);
45 }
46 }
protected void Button1_Click(object sender, EventArgs e)
2 {
3 string mainName = "圆梦组";
4 string detailName="刘能|赵本山|沈燕|欧阳锋";
5 string detailAge="23|32|18|19";
6 int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
7 }
8 protected void Button2_Click(object sender, EventArgs e)
9 {
10 string mainName = "起航组2";
11 string detailName="段毅2|乔峰2|杨过2|李莫愁2";
12 string detailAge="18|28|jj|35";
13 SqlParameter[] sps = new SqlParameter[] {
14 new SqlParameter("@mainName",mainName),
15 new SqlParameter("@detailNameStr",detailName),
16 new SqlParameter("@detailAgeStr",detailAge)
17 };
18 try
19 {
20 int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
21 }
22 catch (Exception ex)
23 {
24 Response.Write(ex.Message);
25 }
26
27 }
28 protected void Button3_Click(object sender, EventArgs e)
29 {
30 string mainName = "起航组2";
31 string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
32 string detailAge = "18|28|jj|35";
33 SqlParameter[] sps = new SqlParameter[] {
34 new SqlParameter("@mainName",mainName),
35 new SqlParameter("@detailNameStr",detailName),
36 new SqlParameter("@detailAgeStr",detailAge)
37 };
38 try
39 {
40 int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
41 }
42 catch (Exception ex)
43 {
44 Response.Write(ex.Message);
45 }
46 }