我想传入一个语句去执行多个操作,我不知道传入SQL的格式怎么写更合理些。
例如:
string strSQL="update a set a.total=b.total from tablea a, tableb b where a.id=b.id delete from tableb"
SqlHelper.ExecuteNonQuery(DataConn,CommandType.Text,strSQL);上面的strSQL实际上是由"update a set a.total=b.total from tablea a, tableb b where a.id=b.id"和"delete from tableb"组成,
请问可以直接象上面一样写在一起吗?有什么更合理的写法吗?
例如:
string strSQL="update a set a.total=b.total from tablea a, tableb b where a.id=b.id delete from tableb"
SqlHelper.ExecuteNonQuery(DataConn,CommandType.Text,strSQL);上面的strSQL实际上是由"update a set a.total=b.total from tablea a, tableb b where a.id=b.id"和"delete from tableb"组成,
请问可以直接象上面一样写在一起吗?有什么更合理的写法吗?
解决方案 »
- 启动进程时为什么没有窗口?
- 写一个sql server的存储过程
- DataGrid中,数据更新的问题
- url中传递包含%变量的问题
- DataGrid中如何添加数据
- 高分求助,Asp.net跟Asp共存的问题!!
- dropdownlist的默认值问题!
- c# 做 UBB编辑器
- aspx提交后如何定位页面位置
- jQuery.Easyui 菜单如何控制显示还是不显示?比如权限,某用户登录后,能看到哪些菜单,不能看到哪些菜单,怎么搞?
- jimu8130(虚荣心发作了,想得到msmvp称号发疯了) ( )请进来,,, DG可以绑定了,,,可是要怎么把修改的值返回并保存到数据库哦
- 网站编译后的错误!!!急...
先update然后delete,而且应该用到事务
/// 更新公司简介
/// </summary>
/// <param name="str_ContentType">内容类别</param>
/// <param name="str_Content">公司简介内容</param>
/// <returns>True :更新成功 False 更新失败</returns>
public bool b_updateCompanyIntroduce(string str_ContentType,string str_Content)
{
//首先先删除,指定的记录
System.Data.SqlClient.SqlConnection sql_conn = new SqlConnection(SqlHelper.ConnectionString);
sql_conn.Open();
SqlTransaction trans = sql_conn.BeginTransaction(); try
{ string strSQL = "delete from CN_Content where ContentType=@ContentType"; SqlParameter[] param_1 = new SqlParameter[1];
param_1[0] = new SqlParameter("@ContentType", SqlDbType.NVarChar, 50);
param_1[0].Value = str_ContentType; Common.SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQL, param_1);
//然后新增 SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@Title", SqlDbType.NVarChar, 50); string str_SelectContentType = "";
switch (str_ContentType)
{
case "1":
str_SelectContentType = "公司简介";
break;
case "2":
str_SelectContentType = "公司景观";
break;
case "3":
str_SelectContentType = "生产实力";
break;
case "4":
str_SelectContentType = "品质控制";
break;
case "5":
str_SelectContentType = "工艺技术";
break;
case "20":
str_SelectContentType = "广聘英才";
break; } param[0].Value = str_SelectContentType; param[1] = new SqlParameter("@Content", SqlDbType.NVarChar, 20000);
param[1].Value = str_Content; param[2] = new SqlParameter("@ContentType", SqlDbType.NVarChar, 50);
param[2].Value = str_ContentType;
string strSQL_insert = "insert into CN_Content(Title,Content,ContentType) values (@Title,@Content,@ContentType)";
Common.SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQL_insert, param); trans.Commit();
}
catch
{
trans.Rollback();
sql_conn.Close();
return false;
}
sql_conn.Close();
return true;
}
string strSQL="update a set a.total=b.total from tablea a, tableb b where a.id=b.id delete from tableb"
写成存储过程如下:
CREATE PROCEDURE SP_Mytest
AS
update a set a.total=b.total from tablea a, tableb b where a.id=b.id
delete from tableb
GO
但要用到事任务,这个存储过程该怎么写呢?
来保持数据同步
AS
begin
begin tran
update a set a.total=b.total from tablea a, tableb b where a.id=b.id
if @@error<>0
begin
--操作失败,则事务回滚
rollback tran
end
delete from tableb
if @@error<>0
begin
--操作失败,则事务回滚
rollback tran
end
--如果操作执行正确,则提交事务
commit tran
end
GO各位大侠!写成上面那样,是否正确呢?
CREATE PROCEDURE SP_Mytest
AS
--开始事务
set xact_abort on
begin tran tran1update a set a.total=b.total from tablea a, tableb b where a.id=b.id delete from tableb
--执行事务
IF @@error>0
rollback tran tran1
ELSE
commit tran tran1
GO
存储过程中用事物:begin tran ........