用存储过程就可以,当不能完全执行的时候系统会自动回滚。
在这里给你个例子,希望对你有帮助。
public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open(); SqlCommand myCommand = new SqlCommand();
SqlTransaction myTrans; // Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans; try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
myTrans.Rollback("SampleTransaction");
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}
在这里给你个例子,希望对你有帮助。
public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open(); SqlCommand myCommand = new SqlCommand();
SqlTransaction myTrans; // Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans; try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
myTrans.Rollback("SampleTransaction");
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}
//可以直接修改命令代码,以实现目的
//请注意标记**的地方//////////////////////////////////////////////////////////////////////////////////////////////////////////////
//建立连接
string ConnectionString = "Provider=MSDAORA.1;Password=forrest;User ID=forrest;Data Source=oratest";
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
myConnection.Open();//生成SQL语句
string strCreateUser = "CREATE USER " + txtUserName.Text.Trim() + " IDENTIFIED BY " + txtPassword.Text.Trim();
string strGrantRoles = "GRANT connect, resource TO " + txtUserName.Text.Trim().ToUpper();////如下,分别执行两条语句是没有错误的
// OleDbCommand cmdCreateUser = new OleDbCommand(strCreateUser, myConnection);
// OleDbCommand cmdGrantRoles = new OleDbCommand(strGrantRoles, myConnection);
// cmdCreateUser.ExecuteNonQuery();
// cmdGrantRoles.ExecuteNonQuery();//如下,将两条SQL语句以分号连接后,则不能成功执行
string strCreateUserGrantRoles = strCreateUser ;//+ " ; " + strGrantRoles;
OleDbCommand cmdCreateUserGrantRoles = new OleDbCommand(strCreateUserGrantRoles, yConnection);
cmdCreateUserGrantRoles.ExecuteNonQuery();
//**改变命令行 **
cmdCreateUserGrantRoles.CommandText = strGrantRoles;
cmdCreateUserGrantRoles.ExecuteNonQuery();//关闭连接
myConnection.Close();
以空格分隔不行。To:zhuohs(凌九霄)
你用到了事务处理,并非存储过程。To: TonyJoule(寒星㊣)
“批处理”如何做?能否写详细些?To:tanrui(Tanruy)
存储过程应如何写?MSDN上全是关于SQL Server的例子,我想知道Oracle的如何写,或是标准SQL的写法。To:Happyboy_zjy(阳光男孩) & yqdeng(享受生活每一天)
我之前就是用分号分隔,不行。