C# 3.0 修正 DAL

2008-10-05 置頂文章

3413
0
.NET

摘要:C# 3.0 修正 DAL

留紀錄中 ...

1.修正所有Where條件式

2.加入ConditionMode(AND|OR)

3.try ... catch 修正

MySqlServerusing System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Text; // Update:2008.10.05 // Developer:Phoenix // Version:1.0.1005 ///  /// <summary> /// 資料存取層核心元件 - design By Phoenix 2008 /// </summary> public class MySqlServer : IQuery { //宣告連線字串物件 private ConnectionString ConnStr; #region 組合SQL片段 ///  /// <summary> /// 去片段多餘的字元 - design By Phoenix 2008 /// </summary> /// <param name="Part">片段</param> /// <returns>Part.Trim().TrimEnd(char.Parse(","))</returns> private string TrimPart(string Part) { //去掉開頭多餘的空白 //去除結尾多餘的逗號 //回傳結果 return Part.Trim().TrimEnd(char.Parse(",")); } ///  /// <summary> /// 產生加入參數的片段 - design By Phoenix 2008 /// </summary> /// <param name="FieldName">要加上參數的強型別物件清單</param> /// <returns>DataField1 = @DataField1, DataField2 = @DataField2, … DataFieldN = @DataFieldN</returns> private string SetParameterPart(List<DataField> FieldName) { //建立回傳暫存 StringBuilder result = new StringBuilder(); //一一取出清單內的值，並且加上參數 foreach (DataField s in FieldName) result.AppendFormat ( " {0} = @{1},", s.ColumnName.Replace("Original_", ""), s.ColumnName ); //呼叫TrimPart //回傳結果 return TrimPart(result.ToString()); } ///  /// <summary> /// 產生Insert參數片段 - design By Phoenix 2008 /// </summary> /// <param name="FieldName">要加入參數的強型別物件清單</param> /// <returns>(DataField1, DataField2, … DataFieldN) VALUES (@DataField1, @DataField2, … @DataFieldN)</returns> private string SetInsertPart(List<DataField> FieldName) { //建立回傳暫存(欄位片段、參數片段) StringBuilder insertField = new StringBuilder(); StringBuilder insertParameter = new StringBuilder(); //一一取出清單內的值，並且產生欄位片段以及參數片段 foreach (DataField s in FieldName) { insertField.AppendFormat(" {0},", s.ColumnName); insertParameter.AppendFormat(" @{0},", s.ColumnName); } //呼叫TrimPart //將欄位片段以及參數片段組合成Insert片段 //回傳結果 return string.Format("({0}) VALUES ({1})", TrimPart(insertField.ToString()), TrimPart(insertParameter.ToString())); } ///  /// <summary> /// 產生 Select 敘述片段 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <returns>DataField1, DataField2, … DataFieldN</returns> private string SetSelectPart(string[] SelectField) { //建立回傳暫存 StringBuilder result = new StringBuilder(); //一一取出清單內的值，加上逗號 foreach (string s in SelectField) result.AppendFormat(" {0},", s); //呼叫TrimPart //回傳結果 return TrimPart(result.ToString()); } ///  /// <summary> /// 產生 Where 敘述片段 - design By Phoenix 2008 /// </summary> /// <param name="Condition">條件限制</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>DataField1 = @DataField1 AND|OR DataField2 = @DataField2 AND|OR … DataFieldN = @DataFieldN</returns> private string SetWherePart(List<DataField> Condition, ConditionMode CM) { //建立回傳暫存 StringBuilder result = new StringBuilder(); //一一取出清單內的值 foreach (DataField s in Condition) result.AppendFormat ( " ({0} = @{1}) {2}", s.ColumnName.Replace("Original_", ""), s.ColumnName, CM.ToString() ); result.Remove((result.Length - 3), 3); return result.ToString(); } ///  /// <summary> /// Where`產生 Like 敘述片段 - design By Phoenix 2008 /// </summary> /// <param name="Condition">條件限制(Like)</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>DataField1 LIKE @DataField1 AND|OR DataField2 LIKE @DataField2 AND|OR … DataFieldN LIKE @DataFieldN</returns> private string SetLikePart(List<DataField> Condition, ConditionMode CM) { /* //建立回傳暫存 StringBuilder result = new StringBuilder(); //一一取出清單內的值，加上LIKE foreach (DataField s in Condition) result.AppendFormat ( " ({0} LIKE @{1}) {2}", s.ColumnName.Replace("Original_", ""), s.ColumnName, CM.ToString () ); result.Remove((result.Length - 2), 2); return result.ToString(); */ //呼叫SetWherePart將=取代為LIKE return this.SetWherePart(Condition, CM).Replace("=", "LIKE"); } ///  /// <summary> /// 產生Select`From 敘述 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <returns>SELECT SelectField FORM DbName</returns> private string GetSelectQuery(string[] SelectField, DbName Name) { //將 SelectField 和 DbName 加入到敘述內 //回傳結果。 return string.Format("SELECT {0} FROM [{1}]", SetSelectPart(SelectField), Name.ToString()); } ///  /// <summary> /// 產生Select`From`Where 敘述 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <returns>SELECT SelectField FORM DbName WHERE (DataField1 = @DataField1, DataField2 = @DataField2, … DataFieldN = @DataFieldN)</returns> private string GetSelectWhereQuery(string[] SelectField, DbName Name, List<DataField> Condition) { //呼叫GetSelectWhereQuery使用AND模式 return this.GetSelectWhereQuery(SelectField, Name, Condition, ConditionMode.AND); } ///  /// <summary> /// 產生Select`From`Where 敘述 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>SELECT SelectField FORM DbName WHERE (DataField1 = @DataField1, DataField2 = @DataField2, … DataFieldN = @DataFieldN)</returns> private string GetSelectWhereQuery(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { //呼叫GetSelectQuery，取得敘述片段 //呼叫SetParameterPart，取得參數片段 //組合兩片段 //回傳結果 return string.Format("{0} WHERE ({1})", GetSelectQuery(SelectField, Name), SetWherePart(Condition, CM)); } ///  /// <summary> /// 產生Select`From`Where 敘述(LIKE) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <returns>SELECT SelectField FORM DbName WHERE (DataField1 LIKE @DataField1, DataField2 LIKE @DataField2, … DataFieldN LIKE @DataFieldN)</returns> private string GetSelectLikeQuery(string[] SelectField, DbName Name, List<DataField> Condition) { //呼叫GetSelectLikeQuery使用AND模式 return this.GetSelectLikeQuery(SelectField, Name, Condition, ConditionMode.AND); } ///  /// <summary> /// 產生Select`From`Where 敘述(LIKE) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>SELECT SelectField FORM DbName WHERE (DataField1 LIKE @DataField1, DataField2 LIKE @DataField2, … DataFieldN LIKE @DataFieldN)</returns> private string GetSelectLikeQuery(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { //呼叫GetSelectQuery，取得敘述片段 //呼叫SetLikePart，取得參數片段 //組合兩片段 //回傳結果 return string.Format("{0} WHERE {1}", GetSelectQuery(SelectField, Name), SetLikePart(Condition, CM)); } ///  /// <summary> /// 產生Insert`Into``Values` 敘述 - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="DataField">新增清單</param> /// <returns>INSERT INTO DbName (DataField1,DataField2…DataFieldN) VALUES (@DataField1, @DataField2, … @DataFieldN)</returns> private string GetInsertQuery(DbName Name, List<DataField> DataField) { //呼叫SetInsertPart 取得 Insert片段 //將 DbName 和 Insert片段放入敘述內。 //回傳結果 return string.Format("INSERT INTO [{0}] {1}", Name.ToString(), SetInsertPart(DataField)); } ///  /// <summary> /// 加上回傳自動編號的參數 - design By Phoenix 2008 - /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="DataField">Insert清單</param> /// <returns></returns> private string SerReturn(DbName Name, List<DataField> DataField) { return string.Format("{0} select scope_identity()", GetInsertQuery(Name, DataField)); } ///  /// <summary> /// 產生Delete From`Where`敘述 - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <returns>DELETE FROM DbName WHERE(Condition1 = @Condition1 AND|OR Condition2 = @Condition2 AND|OR … ConditionN = @ConditionN)</returns> private string GetDeleteQuery(DbName Name, List<DataField> Condition) { //呼叫SetParameterPart，取得參數片段 //將 DbName 和參數片段放入敘述內。 //回傳結果 return string.Format("DELETE FROM {0} WHERE ({1})", Name.ToString(), SetWherePart(Condition, ConditionMode.AND)); } ///  /// <summary> /// 產生Delete From`Where`敘述 - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>DELETE FROM DbName WHERE(Condition1 = @Condition1 AND|OR Condition2 = @Condition2 AND|OR … ConditionN = @ConditionN)</returns> private string GetDeleteQuery(DbName Name, List<DataField> Condition, ConditionMode CM) { //呼叫SetParameterPart，取得參數片段 //將 DbName 和參數片段放入敘述內。 //回傳結果 return string.Format("DELETE FROM {0} WHERE ({1})", Name.ToString(), SetWherePart(Condition, ConditionMode.AND)); } ///  /// <summary> /// 產生Update`Set`Where`敘述 - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Data">DataField強行別物件清單</param> /// <returns></returns> private string GetUpdateQuery(DbName Name, List<DataField> Data) { //取得條件式(以Original開頭) List<DataField> Condition = Data.FindAll(c => c.ColumnName.StartsWith("Original")); //取得新的值(除Original以外) List<DataField> UpdateField = Data.FindAll(c => !(c.ColumnName.StartsWith("Original"))); //呼叫SetParameterPart，取得參數片段(新值) //呼叫SetParameterPart，取得參數片段(條件) //組合兩片段 //回傳結果 return string.Format("UPDATE [{0}] SET {1} WHERE ({2})", Name.ToString(), SetParameterPart(UpdateField), SetWherePart(Condition, ConditionMode.AND)); } #endregion #region 創造Parameter ///  /// <summary> /// 創造Parameters - design By Phoenix 2008 /// </summary> /// <param name="Data">DataField強行別物件清單</param> /// <returns>Parameters陣列</returns> private SqlParameter[] CreateParameters(List<DataField> Data) { //建立回傳暫存 SqlParameter[] result = new SqlParameter[Data.Count]; //一一取出清單內的名字和值，放入result集合 for (int i = 0; i < Data.Count; i++) result[i] = new SqlParameter("@" + Data[i].ColumnName, Data[i].ColumnValue); //回傳結果 return result; } ///  /// <summary> /// 創造LikeParameters - design By Phoenix 2008 /// </summary> /// <param name="Data">DataField強行別物件清單</param> /// <returns>Parameters陣列</returns> private SqlParameter[] CreateLikeParameters(List<DataField> Data) { //建立回傳暫存 SqlParameter[] result = new SqlParameter[Data.Count]; //一一取出清單內的名字和值，放入result集合 for (int i = 0; i < Data.Count; i++) result[i] = new SqlParameter("@" + Data[i].ColumnName, string.Format("%{0}%", Data[i].ColumnValue)); //回傳結果 return result; } #endregion #region 執行SQL指令 ///  /// <summary> /// 執行ExecuteReader - 取得DataTable - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <returns>DataTable資料表</returns> private DataTable ExecuteReader(string SqlQuery) { //例外處理 try { //建立回傳暫存 DataTable dt = new DataTable(); //使用 SqlConnection 建立 SqlConnection 執行個體(傳入連線字串) using (SqlConnection SqlConn = new SqlConnection(ConnStr.GetConnStr())) { //宣告 SqlCommand ，建立 SqlCommand 執行個體(傳入Sql命令、SqlConnection) SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn); //開啟連線 SqlConn.Open(); //載入DataTable dt.Load(SqlCmd.ExecuteReader()); } //回傳結果 return dt; } catch (SqlException ex) { throw new InvalidOperationException(string.Format("於 - {0} -\r\nExecuteReader區段發生SqlException錯誤！\r\n錯誤代碼：{1}\r\n訊息：", DateTime.Now, ex.ErrorCode, ex.Message), ex); } } ///  /// <summary> /// 執行ExecuteReader - 取得DataTable - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <param name="Parameters">參數</param> /// <returns>DataTable資料表</returns> private DataTable ExecuteReader(string SqlQuery, SqlParameter[] Parameters) { //例外處理 try { //建立回傳暫存 DataTable dt = new DataTable(); //使用 SqlConnection 建立 SqlConnection 執行個體(傳入連線字串) using (SqlConnection SqlConn = new SqlConnection(ConnStr.GetConnStr())) { //宣告 SqlCommand ，建立 SqlCommand 執行個體(傳入Sql命令、SqlConnection) SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn); //將參數加入SqlCommand SqlCmd.Parameters.AddRange(Parameters); //開啟連線 SqlConn.Open(); //載入DataTable dt.Load(SqlCmd.ExecuteReader()); } //回傳結果 return dt; } catch (SqlException ex) { throw new InvalidOperationException(string.Format("於 - {0} -\r\nExecuteReader區段發生SqlException錯誤！\r\n錯誤代碼：{1}\r\n訊息：", DateTime.Now, ex.ErrorCode, ex.Message), ex); } } ///  /// <summary> /// 執行ExecuteNonQuery - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <returns>受影響的行數</returns> private int ExecuteNonQuery(string SqlQuery) { //例外處理 try { //建立回傳暫存 int result = 0; //使用 SqlConnection 建立 SqlConnection 執行個體(傳入連線字串) using (SqlConnection SqlConn = new SqlConnection(ConnStr.GetConnStr())) { //宣告 SqlCommand ，建立 SqlCommand 執行個體(傳入Sql命令、SqlConnection) SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn); //開啟連線 SqlConn.Open(); //執行ExecuteNonQuery，傳回受影響的行數 result = SqlCmd.ExecuteNonQuery(); } //傳回受影響的行數 return result; } catch (SqlException ex) { throw new InvalidOperationException(string.Format("於 - {0} -\r\nExecuteReader區段發生SqlException錯誤！\r\n錯誤代碼：{1}\r\n訊息：", DateTime.Now, ex.ErrorCode, ex.Message), ex); } } ///  /// <summary> /// 執行ExecuteNonQuery - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <param name="Parameters">參數陣列</param> /// <returns>受影響的行數</returns> private int ExecuteNonQuery(string SqlQuery, SqlParameter[] Parameters) { //例外處理 try { //建立回傳暫存 int result = 0; //使用 SqlConnection 建立 SqlConnection 執行個體(傳入連線字串) using (SqlConnection SqlConn = new SqlConnection(ConnStr.GetConnStr())) { //宣告 SqlCommand ，建立 SqlCommand 執行個體(傳入Sql命令、SqlConnection) SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn); //將參數加入SqlCommand SqlCmd.Parameters.AddRange(Parameters); //開啟連線 SqlConn.Open(); //執行ExecuteNonQuery，傳回受影響的行數 result = SqlCmd.ExecuteNonQuery(); } //傳回受影響的行數 return result; } catch (SqlException ex) { throw new InvalidOperationException(string.Format("於 - {0} -\r\nExecuteReader區段發生SqlException錯誤！\r\n錯誤代碼：{1}\r\n訊息：", DateTime.Now, ex.ErrorCode, ex.Message), ex); } } #endregion #region IQuery 成員 - 實作介面 ///  /// <summary> /// 執行SQL命令 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">SQL命令</param> /// <returns>受影響的行數</returns> public int RunQuery(string SqlQuery) { //呼叫ExecuteNonQuery(傳入Sql命令)(傳回受影響的行數)。 //傳回受影響的行數 return this.ExecuteNonQuery(SqlQuery); } ///  /// <summary> /// 執行SQL命令 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">SQL命令</param> /// <param name="Parameters">參數陣列</param> /// <returns>受影響的行數</returns> public int RunQuery(string SqlQuery, SqlParameter[] Parameters) { //呼叫ExecuteNonQuery(傳入Sql命令)(傳回受影響的行數)。 //傳回受影響的行數 return this.ExecuteNonQuery(SqlQuery, Parameters); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <returns>DataTable資料表</returns> public DataTable GetTable(string SqlQuery) { //呼叫 ExecuteReader(傳入Sql命令)(回傳DataTable資料表) //回傳DataTable資料表。 return this.ExecuteReader(SqlQuery); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <param name="Parameters">參數陣列</param> /// <returns>DataTable資料表</returns> public DataTable GetTable(string SqlQuery, SqlParameter[] Parameters) { //呼叫 ExecuteReader(傳入Sql命令)(回傳DataTable資料表) //回傳DataTable資料表。 return this.ExecuteReader(SqlQuery, Parameters); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <returns>DataTable資料表</returns> public DataTable GetTable(string[] SelectField, DbName Name) { //呼叫GetSelectQuery(傳入Select欄位、資料表)(傳回Sql命令) //呼叫ExecuteReader(傳入Sql命令)(傳回DataTable資料表) //傳回DataTable資料表 return this.ExecuteReader(GetSelectQuery(SelectField, Name)); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <returns>DataTable資料表</returns> public DataTable GetTable(string[] SelectField, DbName Name, List<DataField> Condition) { //呼叫GetTable使用AND模式 return this.GetTable(SelectField, Name, Condition, ConditionMode.AND); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>DataTable資料表</returns> public DataTable GetTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { //呼叫GetSelectWhereQuery(傳入Select欄位、資料表、條件清單、條件模式)(傳回Sql命令) //呼叫CreateParameters(傳入條件清單)(傳回Parameters陣列) //呼叫ExecuteReader(傳入Sql命令、Parameters陣列)(回傳DataTable資料表) //傳回DataTable資料表 return this.ExecuteReader(GetSelectWhereQuery(SelectField, Name, Condition, CM), CreateParameters(Condition)); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">Like條件清單</param> /// <returns>DataTable資料表</returns> public DataTable GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition) { //呼叫GetLikeTable使用OR模式 return this.GetLikeTable(SelectField, Name, Condition, ConditionMode.OR); } ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">Like條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>DataTable資料表</returns> public DataTable GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { //呼叫GetSelectWhereQuery(傳入Select欄位、資料表、條件清單)(傳回Sql命令) //呼叫CreateParameters(傳入條件清單)(傳回Parameters陣列) //呼叫ExecuteReader(傳入Sql命令、Parameters陣列)(回傳DataTable資料表) //傳回DataTable資料表 return this.ExecuteReader(GetSelectLikeQuery(SelectField, Name, Condition, CM), CreateLikeParameters(Condition)); } ///  /// <summary> /// Insert介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="InsertList">Insert清單</param> /// <returns>受影響的行數</returns> public int Insert(DbName Name, List<DataField> InsertList) { //呼叫GetInsertQuery(傳入資料表、Insert清單)(傳回Sql命令) //呼叫CreateParameters(傳入Insert清單)(傳回Parameters陣列) //呼叫ExecuteNonQuery(傳入Sql命令、Parameters陣列)(傳回影響的行數) //傳回影響的行數 return this.ExecuteNonQuery(GetInsertQuery(Name, InsertList), CreateParameters(InsertList)); } ///  /// <summary> /// Insert介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="InsertList">Insert清單</param> /// <returns>資料表</returns> public DataTable InsertWithReturn(DbName Name, List<DataField> InsertList) { //呼叫GetUpdateQuery(傳入資料表、更新清單)(傳回Sql命令) //呼叫CreateParameters(傳入更新清單)(傳回Parameters陣列) //呼叫ExecuteNonQuery(傳入Sql命令、Parameters陣列)(傳回受影響的行數) //傳回受影響的行數 return this.ExecuteReader(SerReturn(Name, InsertList), CreateParameters(InsertList)); } ///  /// <summary> /// Delete介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="DeleteList">刪除清單</param> /// <returns>受影響的行數</returns> public int Delete(DbName Name, List<DataField> DeleteList) { //呼叫GetDeleteQuery(傳入資料表、刪除清單)(傳回Sql命令) //呼叫CreateParameters(傳入刪除清單)(傳回Parameters陣列) //呼叫ExecuteNonQuery(傳入Sql命令、Parameters陣列)(傳回受影響的行數) //傳回受影響的行數 return this.ExecuteNonQuery(GetDeleteQuery(Name, DeleteList), CreateParameters(DeleteList)); } ///  /// <summary> /// Update介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="UpdateList">更新清單</param> /// <returns>受影響的行數</returns> public int Update(DbName Name, List<DataField> UpdateList) { //呼叫GetUpdateQuery(傳入資料表、更新清單)(傳回Sql命令) //呼叫CreateParameters(傳入更新清單)(傳回Parameters陣列) //呼叫ExecuteNonQuery(傳入Sql命令、Parameters陣列)(傳回受影響的行數) //傳回受影響的行數 return this.ExecuteNonQuery(GetUpdateQuery(Name, UpdateList), CreateParameters(UpdateList)); } #endregion #region IQuery 成員 - 明確實做介面 int IQuery.RunQuery(string SqlQuery) { return this.RunQuery(SqlQuery); } #region iSelect 成員 DataTable ISelect.GetTable(string SqlQuery) { return this.GetTable(SqlQuery); } DataTable ISelect.GetTable(string[] SelectField, DbName Name) { return this.GetTable(SelectField, Name); } DataTable ISelect.GetTable(string[] SelectField, DbName Name, List<DataField> Condition) { return this.GetTable(SelectField, Name, Condition); } DataTable ISelect.GetTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { return this.GetTable(SelectField, Name, Condition, CM); } DataTable ISelect.GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition) { return this.GetLikeTable(SelectField, Name, Condition); } DataTable ISelect.GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM) { return this.GetLikeTable(SelectField, Name, Condition, CM); } #endregion #region iInsert 成員 int IInsert.Insert(DbName Name, List<DataField> InsertList) { return this.Insert(Name, InsertList); } DataTable IInsert.InsertWithReturn(DbName Name, List<DataField> InsertList) { return this.InsertWithReturn(Name, InsertList); } #endregion #region iDelete 成員 int IDelete.Delete(DbName Name, List<DataField> DeleteList) { return this.Delete(Name, DeleteList); } #endregion #region iUpdate 成員 int IUpdate.Update(DbName Name, List<DataField> UpdateList) { return this.Update(Name, UpdateList); } #endregion #endregion ///  /// <summary> /// 初始化 DataAccessLayer.DataObject.MySqlServer 類別的新執行個體 - design By Phoenix 2008 /// </summary> public MySqlServer() { //初始化 ConnStr 物件 ConnStr = new ConnectionString(); } }

Enumeration

/// <summary> /// 列舉-條件 /// </summary> public enum ConditionMode { /// <summary> /// 使用AND條件 /// </summary> AND = 0, /// <summary> /// 使用OR條件 /// </summary> OR = 1 }

Interfaceusing System.Collections.Generic; using System.Data; // Update:2008.10.05 // Developer:Phoenix // Version:1.0.1005 ///  /// <summary> /// IQuery介面 - design By Phoenix 2008 /// </summary> public interface IQuery : ISelect, IInsert, IDelete, IUpdate { ///  /// <summary> /// 執行SQL命令 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">SQL命令</param> /// <returns>受影響的行數</returns> int RunQuery(string SqlQuery); } /// <summary> /// ISelect介面 /// </summary> public interface ISelect { ///  /// <summary> /// Select介面 - 取得資料表 - design By Phoenix 2008 /// </summary> /// <param name="SqlQuery">Sql命令</param> /// <returns>DataTable資料表</returns> DataTable GetTable(string SqlQuery); ///  /// <summary> /// Select介面 - 取得資料表(Select From)- - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <returns>ataTable資料表</returns> DataTable GetTable(string[] SelectField, DbName Name); ///  /// <summary> /// Select介面 - 取得資料表(Select From Where) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <returns>ataTable資料表</returns> DataTable GetTable(string[] SelectField, DbName Name, List<DataField> Condition); ///  /// <summary> /// Select介面 - 取得資料表(Select From Where) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>ataTable資料表</returns> DataTable GetTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM); ///  /// <summary> /// Select介面 - 取得資料表(Select From Where Like) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">Like條件清單</param> /// <returns>ataTable資料表</returns> DataTable GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition); ///  /// <summary> /// Select介面 - 取得資料表(Select From Where Like) - design By Phoenix 2008 /// </summary> /// <param name="SelectField">Select欄位(*,表示全部)</param> /// <param name="Name">列舉-資料表名稱</param> /// <param name="Condition">Like條件清單</param> /// <param name="CM">條件方法(AND|OR)</param> /// <returns>ataTable資料表</returns> DataTable GetLikeTable(string[] SelectField, DbName Name, List<DataField> Condition, ConditionMode CM); } /// <summary> /// IInsert介面 /// </summary> public interface IInsert { ///  /// <summary> /// Insert介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="InsertList">Insert清單</param> /// <returns>受影響的行數</returns> int Insert(DbName Name, List<DataField> InsertList); ///  /// <summary> /// Insert介面 - 執行Query - 傳回自動編號 - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="InsertList">Insert清單</param> /// <returns>並傳回自動編號</returns> DataTable InsertWithReturn(DbName Name, List<DataField> InsertList); } /// <summary> /// IDelete介面 /// </summary> public interface IDelete { ///  /// <summary> /// Delete介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="DeleteList">刪除清單</param> /// <returns>受影響的行數</returns> int Delete(DbName Name, List<DataField> DeleteList); } /// <summary> /// IUpdate介面 /// </summary> public interface IUpdate { ///  /// <summary> /// Update介面 - 執行Query - design By Phoenix 2008 /// </summary> /// <param name="Name">列舉-資料表名稱</param> /// <param name="UpdateList">更新清單</param> /// <returns>受影響的行數</returns> int Update(DbName Name, List<DataField> UpdateList); }

以上 Phoenix7765

回首頁