一、下面讲C#如何调用写好的存储过程,下面是对数据操作封装几个函数
//执行对表的增删改操作的sql语句
public static int ExecuteCommand(string text)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(text, conn))
{
int i = cmd.ExecuteNonQuery();
return i;
}
}
}
public static int ExecStoredProcedure(string procName, params SqlParameter[] parameters)
{
int rtn = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
SqlTransaction st = conn.BeginTransaction();
cmd.Transaction = st;
try
{
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
rtn= cmd.ExecuteNonQuery();
st.Commit();
return rtn;
}
catch(SqlException sqlex)
{
st.Rollback();
throw sqlex;
}
}
}
}
public static int ExecuteStoredProcedure(string procName,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
}
二、推荐用第三种方式,用到了事务处理,若只要有一条不通过全部回滚,避免脏数据的产生。也可以在存储过程中使用事务
protected void Button1_Click(object sender, EventArgs e)
{
string mainName = "圆梦组";
string detailName="刘能|赵本山|沈燕|欧阳锋";
string detailAge="23|32|18|19";
int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
}
protected void Button2_Click(object sender, EventArgs e)
{
string mainName = "起航组2";
string detailName="段毅2|乔峰2|杨过2|李莫愁2";
string detailAge="18|28|jj|35";
SqlParameter[] sps = new SqlParameter[] {
new SqlParameter("@mainName",mainName),
new SqlParameter("@detailNameStr",detailName),
new SqlParameter("@detailAgeStr",detailAge)
};
try
{
int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void Button3_Click(object sender, EventArgs e)
{
string mainName = "起航组2";
string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
string detailAge = "18|28|jj|35";
SqlParameter[] sps = new SqlParameter[] {
new SqlParameter("@mainName",mainName),
new SqlParameter("@detailNameStr",detailName),
new SqlParameter("@detailAgeStr",detailAge)
};
try
{
int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string mainName = "圆梦组";
string detailName="刘能|赵本山|沈燕|欧阳锋";
string detailAge="23|32|18|19";
int rtn=DBHelper.ExecuteCommand(string.Format("exec dbo.Proc_TestBatchMainDetailIns '{0}','{1}','{2}'", mainName, detailName,detailAge));
}
protected void Button2_Click(object sender, EventArgs e)
{
string mainName = "起航组2";
string detailName="段毅2|乔峰2|杨过2|李莫愁2";
string detailAge="18|28|jj|35";
SqlParameter[] sps = new SqlParameter[] {
new SqlParameter("@mainName",mainName),
new SqlParameter("@detailNameStr",detailName),
new SqlParameter("@detailAgeStr",detailAge)
};
try
{
int rtn = DBHelper.ExecuteStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void Button3_Click(object sender, EventArgs e)
{
string mainName = "起航组2";
string detailName = "段毅2|乔峰2|杨过2|李莫愁2";
string detailAge = "18|28|jj|35";
SqlParameter[] sps = new SqlParameter[] {
new SqlParameter("@mainName",mainName),
new SqlParameter("@detailNameStr",detailName),
new SqlParameter("@detailAgeStr",detailAge)
};
try
{
int rtn = DBHelper.ExecStoredProcedure("dbo.Proc_TestBatchMainDetailIns", sps);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}