.NET的多种事务处理
Oracle 的事务操作，有时候想在批量操作数据集合的时候，执行一次失败，即为了避免数据异常，将所有的操作回滚。
.NET给我们提供了良好的事务操作，Oracle端也有事务操作，可以灵活使用，此处介绍.NET的事务。
1、.NET事务操作Oracle存储过程
public string insert_input_attach_info_moto(DataTable dt, string ass_code, string cust_model, string ship_date, string cust_name, string ship_city, string ship_country, string po_cstname, string po_no, string chd_code, ref decimal batch_id) { string rtnMsg = ""; //存储过程返回信息 OracleConnection conn = (OracleConnection)DataBase.Provider.GetConnection(); //创建并开启 OracleConnection conn.Open(); //启动事务 OracleTransaction tran = conn.BeginTransaction(); try { OracleCommand cmd = conn.CreateCommand(); cmd.Transaction = tran; //绑定事务 cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "pkg_name.procedure_name"; //具体的方法 cmd.Parameters.AddWithValue("p_ass_code", ass_code); //参数占位符 cmd.Parameters.AddWithValue("p_cust_model", cust_model); cmd.Parameters.AddWithValue("p_ship_date", ship_date); cmd.Parameters.AddWithValue("p_cust_name", cust_name); cmd.Parameters.AddWithValue("p_ship_city", ship_city); cmd.Parameters.AddWithValue("p_ship_country", ship_country); cmd.Parameters.AddWithValue("p_po_cstname", po_cstname); cmd.Parameters.AddWithValue("p_po_no", po_no); cmd.Parameters.AddWithValue("p_chd_code", chd_code); cmd.Parameters.AddWithValue("p_batch_id", batch_id); cmd.Parameters.Add("p_msg", OracleType.VarChar, 100); cmd.Parameters[10].Direction = ParameterDirection.Output; foreach(DataRow row in dt.Rows) { cmd.Parameters[0].Value = row["SN"]; //此处是改变的参数，根据实际情况赋值 cmd.ExecuteNonQuery(); rtnMsg = cmd.Parameters[10].Value.ToString(); //每次执行的返回值,若出现异常，可以对此值判断，并确认是否回滚操作 } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw (ex); } finally { if (conn.State != ConnectionState.Closed) //关闭链接，避免资源浪费 conn.Close(); } return rtnMsg; //对比无事务的 //OracleParameter[] paras = new OracleParameter[] //{ // new OracleParameter("p_ass_code", ass_code), // new OracleParameter("p_cust_model", cust_model), // new OracleParameter("p_ship_date", ship_date), // new OracleParameter("p_cust_name", cust_name), // new OracleParameter("p_ship_city", ship_city), // new OracleParameter("p_ship_country", ship_country), // new OracleParameter("p_po_cstname", po_cstname), // new OracleParameter("p_po_no", po_no), // new OracleParameter("p_chd_code", chd_code), // new OracleParameter("p_batch_id", batch_id), // new OracleParameter("p_msg", OracleType.VarChar, 100) //}; //paras[10].Direction = ParameterDirection.Output; //DataBase.Provider.ExecuteNonQuery(CommandType.StoredProcedure, @"pkg_name.procedure_name", paras); //batch_id = Convert.ToDecimal(paras[9].Value); //return paras[10].Value.ToString(); }
2、
public string updateSuccessFlag(DataTable dt) { string rtnMsg = ""; OracleConnection conn = (OracleConnection)DataBase.Provider.GetConnection(); conn.Open(); //启动事务 OracleTransaction tran = conn.BeginTransaction(); try { OracleCommand cmd = conn.CreateCommand(); cmd.Transaction = tran; //绑定事务 cmd.CommandType = CommandType.Text; cmd.CommandText = @"UPDATE table_name d SET d.flag = 1 WHERE d.prodid = :prod_id"; cmd.Parameters.AddWithValue("prod_id", ""); //参数占位符 //cmd.Parameters.Add("p_msg", OracleType.VarChar, 100); //cmd.Parameters[10].Direction = ParameterDirection.Output; foreach (DataRow dr in dt.Rows) //动态参数,我们参数 { cmd.Parameters["prod_id"].Value = Convert.ToString(dr["PRODID"]); cmd.ExecuteNonQuery(); } tran.Commit(); rtnMsg = "SUCCESS"; } catch (Exception ex) { tran.Rollback(); throw (ex); } finally { if (conn.State != ConnectionState.Closed) conn.Close(); } return rtnMsg; }
3.SQLServer简单事务
//SQL语句事务 //更新数据到SqlServer public string UploadStbDataToHuaWei(DataTable dt) { string rtnMsg = ""; //获取链接，并开启链接 SqlConnection conn = (SqlConnection)DataBase.ProviderSQL.GetConnection(); conn.Open(); //启动事务 SqlTransaction tran = conn.BeginTransaction(); try { SqlCommand cmd = conn.CreateCommand(); cmd.Transaction = tran; //绑定事务 cmd.CommandType = CommandType.Text; cmd.CommandText = @"insert into [database_name].[dbo].[table_name] ([bar_code] ,[type] ,[stb_value],[created_date], [updated_date]) values( @bar_code, @type, @stb_value, GETDATE(), GETDATE())"; cmd.Parameters.AddWithValue("@bar_code", ""); //占位符 cmd.Parameters.AddWithValue("@type", ""); cmd.Parameters.AddWithValue("@stb_value", ""); foreach (DataRow dr in dt.Rows) { ///cmd.Parameters.Add("@bar_code", SqlDbType.VarChar); //输出 //SqlServer的事务 cmd.Parameters["@bar_code"].Value = Convert.ToString(dr["PRODID"]); cmd.Parameters["@type"].Value = Convert.ToString(dr["TYPES"]); cmd.Parameters["@stb_value"].Value = Convert.ToString(dr["VAL"]); cmd.ExecuteNonQuery(); } tran.Commit(); rtnMsg = "SUCCESS"; } catch (Exception ex) { tran.Rollback(); throw (ex); } finally { if (conn.State != ConnectionState.Closed) conn.Close(); } return rtnMsg; } ------------------------------------------------------------------------------------------------------- //事务的一般写法和逻辑 SqlConnection conn = DataBase.Provider.GetConnection() as OracleConnection; //OracleCommand cmd = conn.CreateCommand(); conn.Open(); //...创建数据库连接 cnn System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction(); try { foreach (DataRow dr in dt.Rows) { cm.CommandText = "update [表] set [数量] = @amount where productID = @productID"; cm.Parameters.Add("@amount", SqlDbType.Int); cm.Parameters["@amount"].Value = Convert.ToInt32(dr["amount"]); cm.Parameters.Add("@productID", SqlDbType.VarChar); cm.Parameters["@productID"].Value = dr["productID"].ToString(); cm.ExecuteNonQuery(); } trans.Commit(); } catch { trans.Rollback(); }
分享共进步
谢谢阅读！
