C# DBConnection.ExecuteSql方法代码示例

 public DataSet RefreshDataset(SMDataSource smDS, DataSet ds)
 {
     DBConnection connection = new DBConnection(smDS);
     connection.Open();
     try
     {
         for (int i = 0; i < ds.Tables.Count; i++)
         {
             DataTable dt = ds.Tables[i];
             dt.Rows.Clear();
             string sql = (string) dt.ExtendedProperties["selectsql"];
             connection.ExecuteSql(dt, sql);
         }
     }
     finally
     {
         connection.Close();
     }
     return ds;
 }

 protected override DataTable QueryTrace(string table, string field, string keyValue)
 {
     DataTable table3;
     StringBuilder builder = new StringBuilder(100);
     builder.Append("select * from ").Append(TraceUtil.TraceTableName);
     builder.Append(" \r where ").Append(TraceUtil.FLD_TABLE).Append("='").Append(table);
     builder.Append("' \r and ").Append(TraceUtil.FLD_FIELD).Append("='").Append(field);
     builder.Append("' \r and ").Append(TraceUtil.FLD_KEY).Append("='").Append(keyValue);
     builder.Append("' \r order by ").Append(TraceUtil.FLD_TIMESTAMP);
     DBConnection connection = new DBConnection(base.SMDataSource);
     connection.Open();
     try
     {
         DataTable dt = new DataTable();
         connection.ExecuteSql(dt, builder.ToString());
         table3 = dt;
     }
     finally
     {
         connection.Close();
     }
     return table3;
 }

 private void SyncDataSouce(string keyValue, DAODataForm form, DBConnection dbcn)
 {
     try
     {
         StringBuilder sb = new StringBuilder();
         foreach (DataControl control in form.DataControls)
         {
             if (!string.IsNullOrEmpty(control.ValueDataSource))
             {
                 if (control.MapColumn != null)
                 {
                     object syncValue = this.GetSyncValue(control.ValueDataSource, keyValue);
                     LoggingService.DebugFormatted("将同步业务:{0} 字段:{1} 到值：{2}", new object[] { keyValue, control.MapColumn.Name, syncValue });
                     if (!((syncValue == null) || Convert.IsDBNull(syncValue)))
                     {
                         sb.AppendFormat("update {0} set {1}='{2}' where {3}='{4}' and ({1} is null or {1}!='{2}');\r\n", new object[] { control.MapTable.Name, control.MapColumn.Name, syncValue.ToString().Replace("'", "''"), control.MapTable.Key, keyValue });
                     }
                 }
                 else
                 {
                     int? nullable = dbcn.ExecuteSql<int?>(string.Format("select count(1) from {0} where {1}='{2}'", control.MapTable.Name, control.MapTable.RelKey, keyValue));
                     if (!(nullable.HasValue && (nullable.Value > 1)))
                     {
                         DataTable syncDataTable = this.GetSyncDataTable(control.ValueDataSource, keyValue);
                         this.BuildLowLevelSyncSQL(sb, nullable.Value, syncDataTable, control.MapTable, keyValue);
                     }
                 }
             }
         }
         if (sb.Length > 0)
         {
             LoggingService.DebugFormatted("将执行SQL语句：{0}", new object[] { sb.ToString() });
             dbcn.ExecuteSql(sb.ToString());
         }
     }
     catch (Exception exception)
     {
         LoggingService.Error("执行同步数据时出错：", exception);
     }
 }

 private void SaveDataToSQlDB(SMDataSource smDS, ref DataSet ds)
 {
     int count = ds.Tables.Count;
     DBConnection connection = new DBConnection(smDS);
     connection.Open();
     string str = null;
     if (ds.ExtendedProperties.ContainsKey("CascadeSql"))
     {
         str = (string) ds.ExtendedProperties["CascadeSql"];
     }
     try
     {
         IDbTransaction transaction = connection.BeginTrasaction();
         for (int i = 0; i < count; i++)
         {
             DataTable dataTable = ds.Tables[i];
             if (dataTable.GetChanges() != null)
             {
                 string strSelect = (string) dataTable.ExtendedProperties["selectsql"];
                 DbDataAdapter adapter = connection.GetDataAdapter(strSelect, true, true, true);
                 try
                 {
                     adapter.Update(dataTable);
                 }
                 catch (DBConcurrencyException exception)
                 {
                     LoggingService.Error("数据可能被其他人修改了，不能保存...");
                     throw exception;
                 }
             }
         }
         connection.CommitTransaction();
         try
         {
             if (!string.IsNullOrEmpty(str))
             {
                 if (LoggingService.IsDebugEnabled)
                 {
                     LoggingService.DebugFormatted("将执行级联SQL:{0}", new object[] { str });
                 }
                 connection.ExecuteSql(str);
             }
         }
         catch (Exception exception2)
         {
             LoggingService.ErrorFormatted("执行级联更新语句：{0}，发生错语：{1}\r\n{2}", new object[] { str, exception2.Message, exception2.StackTrace });
         }
     }
     catch (Exception exception3)
     {
         connection.RollBackTransaction();
         throw exception3;
     }
     finally
     {
         connection.Close();
     }
 }

 private DataTable GetDataTable(DAODataTable DAODataTable, string keyValue, DBConnection dbcn)
 {
     DataTable dt = new DataTable(DAODataTable.Name);
     string selectString = this.GetSelectString(DAODataTable, keyValue);
     dbcn.ExecuteSql(dt, selectString);
     dt.PrimaryKey = new DataColumn[] { dt.Columns[DAODataTable.Key] };
     if (DAODataTable.Level)
     {
         dt.Columns[DAODataTable.Key].AutoIncrement = true;
         dt.Columns[DAODataTable.RelKey].DefaultValue = keyValue;
     }
     else
     {
         dt.Columns[DAODataTable.Key].DefaultValue = keyValue;
     }
     dt.ExtendedProperties.Add("selectsql", selectString);
     return dt;
 }