第一、sql中寫事物
begin try
?begin transaction
?insert into shiwu (asd) values ('aasdasda');
?commit transaction
end try
begin catch
?select ERROR_NUMBER() as errornumber
?rollback transaction
end catch?
第二、c#中執(zhí)行事物
?
SqlParameter[] paras = new SqlParameter[] { }; paras = list.ToArray(); Baitour.Utility.DBHelper db = Baitour.Utility.DBHelper.CreateMapping(); DbTransaction dbTran = db.CreateTran(); try { // 在這里將插入得到的子訂單號返回 object obj = db.ExecuteScalar(sql.ToString(), paras, dbTran); fsId = obj.ToString(); // int index = db.ExecuteNonQuery(sql.ToString(), paras, dbTran); if (! string .IsNullOrWhiteSpace(fsId)) { dbTran.Commit(); flag = true ; } } catch (Exception ex) { flag = false ; dbTran.Rollback(); // throw; } finally { db.CloseConn(); }
?
dbHelper

using System; using System.Collections.Specialized; using System.Configuration; using System.Data; using System.Data.Common; using System.Data.SqlClient; using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Data.Sql; /* * 作 者: zjf * 創(chuàng)建時間: 2013-1-29 10:20:53 * 說明: */ namespace Baitour.Utility { /// <summary> /// 數(shù)據(jù)庫訪問類 /// </summary> public sealed class DBHelper { public static int ConnIndex = 0 ; public static DBHelper CreateMapping( string connStr = "" ) { DBHelper db = new DBHelper(connStr); return db; } /// <summary> /// 增刪改的數(shù)據(jù)庫連接字符串 /// </summary> string conString = "" ; /// <summary> /// 查詢的數(shù)據(jù)庫連接字符串 /// </summary> string queryConString = "" ; DBHelper( string connStr = "" ) { if ( string .IsNullOrWhiteSpace(connStr)) { conString = ConfigurationManager.ConnectionStrings[ " DBConfig " ].ToString(); // 第一個鏈接字符串是ConfigurationManager.ConnectionStrings[0].Name LocalSqlServer不知道是誰,所以要從第二個取 // 如果連接字符串的數(shù)量和當前索引+1相同 則從第一個索引開始取 NameValueCollection connStrs = new NameValueCollection(); int j = 0 ; for ( int i = 0 ; i < ConfigurationManager.ConnectionStrings.Count; i++ ) { if (ConfigurationManager.ConnectionStrings[i].Name.StartsWith( " QueryDB " )) { connStrs.Add(j.ToString(), ConfigurationManager.ConnectionStrings[i].ToString()); j ++ ; } } if (connStrs.Count > 0 ) { if (connStrs.Count == ConnIndex) { ConnIndex = 0 ; } queryConString = connStrs[ConnIndex].ToString(); ConnIndex ++ ; } else { queryConString = conString; } } else { conString = connStr; queryConString = connStr; } DBMapping = new SqlDatabase(conString); QueryDBMapping = new SqlDatabase(queryConString); } #region 變量or屬性 /// <summary> /// 增刪改的企業(yè)庫訪問映射對象 /// </summary> internal Database DBMapping { get ; set ; } /// <summary> /// 查詢的企業(yè)庫訪問映射對象 /// </summary> internal Database QueryDBMapping { get ; set ; } /// <summary> /// 增刪改的數(shù)據(jù)庫連接 /// </summary> internal DbConnection DBConnMapping { get ; set ; } /// <summary> /// 查詢的數(shù)據(jù)庫連接 /// </summary> internal DbConnection QueryDBConnMapping { get ; set ; } /// <summary> /// 數(shù)據(jù)庫事務 /// </summary> internal DbTransaction DBTranMapping { get ; set ; } #endregion #region 方法 #region 準備方法 /// <summary> /// 創(chuàng)建數(shù)據(jù)庫連接 /// </summary> void CreateDB() { DBMapping = new SqlDatabase(conString); QueryDBMapping = new SqlDatabase(queryConString); } /// <summary> /// 創(chuàng)建并打開連接 /// </summary> void CreateConn() { #region 增刪改 if (DBMapping == null ) { CreateDB(); } if (DBConnMapping == null ) { DBConnMapping = DBMapping.CreateConnection(); DBConnMapping.Open(); // 打開連接 } // 打開存在的連接 if (DBConnMapping != null && DBConnMapping.State != ConnectionState.Open) { DBConnMapping.Open(); // 打開連接 } #endregion #region 查詢 if (QueryDBMapping == null ) { CreateDB(); } if (QueryDBConnMapping == null ) { QueryDBConnMapping = QueryDBMapping.CreateConnection(); QueryDBConnMapping.Open(); // 打開連接 } // 打開存在的連接 if (QueryDBConnMapping != null && QueryDBConnMapping.State != ConnectionState.Open) { QueryDBConnMapping.Open(); // 打開連接 } #endregion } /// <summary> /// 關閉數(shù)據(jù)庫連接 /// </summary> public void CloseConn() { if (DBConnMapping != null && DBConnMapping.State == ConnectionState.Open) { DBConnMapping.Close(); DBConnMapping.Dispose(); } if (QueryDBConnMapping != null && QueryDBConnMapping.State == ConnectionState.Open) { QueryDBConnMapping.Close(); QueryDBConnMapping.Dispose(); } } /// <summary> /// 創(chuàng)建并返回事務 /// </summary> public DbTransaction CreateTran() { if (DBMapping == null ) { CreateDB(); } CreateConn(); DBTranMapping = DBTranMapping ?? DBConnMapping.BeginTransaction(); return DBTranMapping; } /// <summary> /// 創(chuàng)建命令對象 /// </summary> /// <param name="commandType"> sql語句/存儲過程 </param> /// <param name="commandText"> 名稱 </param> /// <param name="commParameters"> sql參數(shù) </param> /// <param name="isQuery"> 是否是創(chuàng)建查詢命令 </param> /// <returns></returns> DbCommand CreateComm(CommandType commandType, string commandText, DbParameter[] commParameters = null , bool isQuery = false ) { DbCommand command = null ; if (commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( " sql語句/存儲過程為空 " ); // 創(chuàng)建命令對象 if (commandType == CommandType.StoredProcedure) command = isQuery ? QueryDBMapping.GetStoredProcCommand(commandText) : DBMapping.GetStoredProcCommand(commandText); else command = isQuery ? QueryDBMapping.GetSqlStringCommand(commandText) : DBMapping.GetSqlStringCommand(commandText); // 清空參數(shù) command.Parameters.Clear(); // 填充參數(shù) if (commParameters != null ) { if (! string .IsNullOrWhiteSpace(ConfigurationManager.AppSettings[ " DBType " ]) && ConfigurationManager.AppSettings[ " DBType " ].ToLower() == " oracle " ) { foreach (DbParameter item in commParameters) { if (!item.ParameterName.StartsWith( " ! " )) item.ParameterName = " ! " + item.ParameterName; } } else { foreach (DbParameter item in commParameters) { if (!item.ParameterName.StartsWith( " @ " )) item.ParameterName = " @ " + item.ParameterName; } } if (commParameters.Length == 1 ) { command.Parameters.Add(commParameters[ 0 ]); } else { command.Parameters.AddRange(commParameters); } } return command; } #endregion #region 執(zhí)行方法 #region sql、存儲過程 /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar( string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { object obj = null ; try { DbCommand command = CreateComm(cType, sql, paras, true ); obj = DBMapping.ExecuteScalar(command); } catch (Exception ex) { } finally { CloseConn(); } return obj; } /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar( string sql, DbParameter[] paras, DbTransaction tran, CommandType cType = CommandType.Text) { object obj = null ; try { DbCommand command = CreateComm(cType, sql, paras); obj = DBMapping.ExecuteScalar(command, tran); } catch (Exception ex) { } finally { } return obj; } /// <summary> /// 返回reader /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public IDataReader ExecuteReader( string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras, true ); command.Connection = QueryDBMapping.CreateConnection(); command.Connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { return null ; } } /// <summary> /// 返回數(shù)據(jù)集 /// </summary> /// <returns></returns> public DataSet ExecuteDataSet( string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras, true ); return QueryDBMapping.ExecuteDataSet(command); } catch (Exception ex) { return null ; } finally { CloseConn(); } } public DataSet ExecuteDataSet( string sql) { try { DbCommand db = CreateComm(CommandType.Text, sql); return QueryDBMapping.ExecuteDataSet(CommandType.Text,sql); } catch { return null ; } finally { CloseConn(); } } /// <summary> /// 得到影響行數(shù) /// </summary> /// <returns></returns> public int ExecuteNonQuery( string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras); return DBMapping.ExecuteNonQuery(command); } catch (Exception ex) { return - 1 ; } finally { CloseConn(); } } /// <summary> /// 得到影響行數(shù)需要手動關閉連接 /// </summary> /// <returns></returns> public int ExecuteNonQuery( string sql, DbParameter[] paras, DbTransaction tran, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras); return DBMapping.ExecuteNonQuery(command, tran); } catch (Exception ex) { return - 1 ; } finally { } } /// <summary> /// 數(shù)據(jù)庫分頁獲取DataSet對象 /// </summary> /// <param name="sTable_Name"> 表名/視圖名 </param> /// <param name="sSign_Record"> 顯示的字段(以,分隔)/*表示全部字段 </param> /// <param name="sFilter_Condition"> 查詢條件 </param> /// <param name="iPage_Size"> 每頁顯示條數(shù) </param> /// <param name="iPage_Index"> 第幾頁 </param> /// <param name="sTaxisField"> 排序字段(以,分隔) </param> /// <param name="iTaxis_Sign"> 排序0升序1降序 </param> /// <param name="iPageCount"> 返回總頁數(shù) </param> /// <param name="iiRecord_Count"> 返回總記錄數(shù) </param> /// <returns> DataSet對象 </returns> public DataSet ExecutePageDataSet( string sTable_Name, string sSign_Record, string sFilter_Condition, int iPage_Size, int iPage_Index, string sTaxisField, int iTaxis_Sign, out int iPageCount, out int iiRecord_Count) { DataSet ds = new DataSet(); try { SqlParameter[] param = new SqlParameter[] { new SqlParameter( " @TableName " ,sTable_Name), new SqlParameter( " @Fields " ,sSign_Record), new SqlParameter( " @OrderField " ,sTaxisField), new SqlParameter( " @Taxis_Sign " ,iTaxis_Sign), new SqlParameter( " @sqlWhere " ,sFilter_Condition), new SqlParameter( " @pageSize " ,iPage_Size), new SqlParameter( " @pageIndex " ,iPage_Index), new SqlParameter( " @TotalPage " , System.Data.SqlDbType.Int), new SqlParameter( " @totalCount " , System.Data.SqlDbType.Int) }; param[ 7 ].Direction = System.Data.ParameterDirection.Output; param[ 8 ].Direction = System.Data.ParameterDirection.Output; // 執(zhí)行 DbCommand command = CreateComm(CommandType.StoredProcedure, " up_ProcCustomPage2005 " , param); ds = QueryDBMapping.ExecuteDataSet(command); iPageCount = 0 ; iiRecord_Count = 0 ; try { iPageCount = Convert.ToInt32(command.Parameters[ " @TotalPage " ].Value); iiRecord_Count = Convert.ToInt32(command.Parameters[ " @totalCount " ].Value); } catch (Exception ex) { iPageCount = 0 ; iiRecord_Count = 0 ; } finally { command.Parameters.Clear(); } } catch (Exception ex) { ds = null ; iPageCount = 0 ; iiRecord_Count = 0 ; } finally { } return ds; } #endregion #region 不加上查詢參數(shù)的 /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar( string sql) { object obj = null ; try { DbCommand command = CreateComm(CommandType.Text, sql, null , true ); return DBMapping.ExecuteScalar(command); } catch (Exception ex) { } finally { CloseConn(); } return obj; } /// <summary> /// 返回reader /// </summary> /// <returns></returns> public IDataReader ExecuteReader( string sql) { try { DbCommand command = CreateComm(CommandType.Text, sql, null , true ); return QueryDBMapping.ExecuteReader(command); } catch (Exception ex) { return null ; } } /// <summary> /// 得到影響行數(shù) /// </summary> /// <returns></returns> public int ExecuteNonQuery( string sql) { try { DbCommand command = CreateComm(CommandType.Text, sql, null , true ); return DBMapping.ExecuteNonQuery(command); } catch (Exception ex) { throw ; } } #endregion #endregion #endregion } }
?
?
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
