/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connectionName"></param> /// <param name="dr"></param> /// <returns></returns> public SqlCommandObject GenerateCommand(string connectionName,string tableName, System.Data.DataRow dr) { List<SqlCommandParameter> paramList = new List<SqlCommandParameter>(); SqlCommandObject cmd = new SqlCommandObject(); //生成WHERE子句 var tableFields = EntityHelper.GetTableFields(connectionName, tableName); int count = tableFields.Count(p => p.IsPrimaryKey); if (count < 1) throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName)); if (count > 1) { throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName)); } string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name; if (!dr.Table.Columns.Contains(primaryKey)) { throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Delete脚本.", primaryKey)); } StringBuilder whereBuilder = new StringBuilder(); foreach (var field in tableFields) { string fieldName = field.Name.Trim() ; if (field.IsPrimaryKey) { if (dr.Table.Columns.Contains(fieldName)) { whereBuilder.Append(string.Format("[{0}]=@Orig_{1} AND ", fieldName, fieldName)); paramList.Add(new SqlCommandParameter() { Name = "@Orig_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName, DataRowVersion.Original] }); } } } string where = whereBuilder.ToString(); int index = where.LastIndexOf("AND"); if (index >= 0) where = where.Substring(0, index); cmd.CommandText = string.Format("DELETE {0} WHERE {1}", tableName, where); cmd.Parameters = paramList.ToArray(); return cmd; }
public OperationResult ExecuteNonQueryByTransaction(string serviceName, string connectionName, SqlCommandObject[] sqlScrips) { try { Database db = DatabaseFactory.CreateDatabase(serviceName, connectionName); using (var connection = db.GetNewOpenConnection()) { using (var trans = connection.BeginTransaction()) { try { int result = 0; foreach (var item in sqlScrips) { using (var cmd = db.GetSqlStringCommandFromSqlCommandObject(item)) { result += db.ExecuteNonQuery(cmd, trans); } } trans.Commit(); return new OperationResult() { Data = result, IsSucess = true }; } catch { trans.Rollback(); throw; } } } } catch (Exception ex) { return new OperationResult() { Message = ex.GetAllMessage(), IsSucess = false }; } }
public OperationResult ExecuteNonQuery(string serviceName, string connectionName, SqlCommandObject[] sqlScrips) { try { Database db = DatabaseFactory.CreateDatabase(serviceName, connectionName); int result = 0; foreach (var item in sqlScrips) { using (var cmd = db.GetSqlStringCommandFromSqlCommandObject(item)) { result += db.ExecuteNonQuery(cmd); } } return new OperationResult() { Data = result, IsSucess = true }; } catch (Exception ex) { return new OperationResult() { Message = ex.GetAllMessage(), IsSucess = false }; } }
/// <summary> /// /// </summary> /// <param name="groupId"></param> /// <param name="commandText"></param> /// <param name="parameters"></param> public void Execute(int groupId, string commandText, params object[] parameters) { SqlCommandObject obj = new SqlCommandObject(); obj.CommandText = commandText; obj.GroupId = groupId; obj.ParameterValues = parameters.ToArray(); this.Execute(obj); }
/// <summary> /// /// </summary> /// <param name="sqlObj"></param> public void Execute(SqlCommandObject sqlObj) { this.sqlObjects.Add(sqlObj); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connectionName"></param> /// <param name="dr"></param> /// <returns></returns> public SqlCommandObject GenerateCommand(string connectionName, string tableName, System.Data.DataRow dr) { List<SqlCommandParameter> paramList = new List<SqlCommandParameter>(); SqlCommandObject cmd = new SqlCommandObject(); StringBuilder fieldsBuilder = new StringBuilder(); StringBuilder fieldParamsBuilder = new StringBuilder(); //生成字段列表 var tableFields = EntityHelper.GetTableFields(connectionName, tableName); int count = tableFields.Count(p => p.IsPrimaryKey); if (count < 1) throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName)); if (count > 1) { throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName)); } string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name; if (!dr.Table.Columns.Contains(primaryKey)) { throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Insert脚本.", primaryKey)); } foreach (var field in tableFields) { string fieldName = field.Name.Trim(); if (dr.Table.Columns.Contains(fieldName) && !field.IsIdentity && !field.IsVersionNumber) { fieldsBuilder.Append(string.Format("[{0}], ", fieldName)); if (dr[fieldName] != null && dr[fieldName] != DBNull.Value) { fieldParamsBuilder.Append(string.Format("@Curr_{0}, ", fieldName)); paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName] }); } else { if (field.InsertDefaultValue != null && field.InsertDefaultValue != DBNull.Value) { var paramValue = DefaultValueHelper.GetValue(field.InsertDefaultValue); fieldParamsBuilder.Append(string.Format("{0}, ", paramValue)); } else { fieldParamsBuilder.Append(string.Format("@Curr_{0}, ", fieldName)); paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = DBNull.Value }); } } } } string fields = fieldsBuilder.ToString(); string fieldParams = fieldParamsBuilder.ToString(); if (!string.IsNullOrWhiteSpace(fields) && fields.LastIndexOf(",") >= 0) { fields = fields.Substring(0, fields.LastIndexOf(",")); fieldParams = fieldParams.Substring(0, fieldParams.LastIndexOf(",")); } cmd.CommandText = string.Format(" INSERT {0} ({1}) VALUES({2})", tableName, fields, fieldParams); cmd.Parameters = paramList.ToArray(); return cmd; }
/// <summary> /// /// </summary> /// <param name="connectionName"></param> /// <param name="sqlScripts"></param> /// <returns></returns> public OperationResult ExecuteNonQueryByTransaction(string serviceName, string connectionName, SqlCommandObject[] sqlScripts) { ChannelFactory<IWcfPortal> cf = GetChannelFactory(); IWcfPortal svr = GetProxy(cf); OperationResult result = null; try { result = svr.ExecuteNonQueryByTransaction(serviceName, connectionName, sqlScripts); if (cf != null) cf.Close(); } catch { cf.Abort(); throw; } return result; }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="connectionName"></param> /// <param name="dr"></param> /// <returns></returns> public SqlCommandObject GenerateCommand(string connectionName, string tableName, System.Data.DataRow dr) { List<SqlCommandParameter> paramList = new List<SqlCommandParameter>(); SqlCommandObject cmd = new SqlCommandObject(); StringBuilder setBuilder = new StringBuilder(); StringBuilder whereBuilder = new StringBuilder(); var tableFields = EntityHelper.GetTableFields(connectionName, tableName); int count = tableFields.Count(p => p.IsPrimaryKey); if (count < 1) throw new FieldNotFoundException(string.Format("主键在表中没有定义,表名为:{0}", tableName)); if (count > 1) { throw new FieldNotFoundException(string.Format("实体类不支持复合主键,表名为:{0}", tableName)); } string primaryKey = tableFields.FirstOrDefault(p => p.IsPrimaryKey).Name; if (!dr.Table.Columns.Contains(primaryKey)) { throw new FieldNotFoundException(string.Format("主键列\"{0}\"在查询结果中不存在,无法生成Update脚本.", primaryKey)); } foreach (var field in tableFields) { string fieldName = field.Name.Trim(); if (dr.Table.Columns.Contains(fieldName) && !field.IsIdentity && !field.IsVersionNumber) { if (field.IsPrimaryKey) { whereBuilder.Append(string.Format("[{0}] = @Orig_{1} ", fieldName, fieldName)); paramList.Add(new SqlCommandParameter() { Name = "@Orig_{0}".FormatWith(fieldName), Value = dr[fieldName, DataRowVersion.Original] }); } if (!dr[fieldName].Equals(dr[fieldName, DataRowVersion.Original])) { if (field.UpdateDefaultValue != null && field.UpdateDefaultValue != DBNull.Value) { var paramValue = DefaultValueHelper.GetValue(field.UpdateDefaultValue); setBuilder.Append(string.Format("[{0}] = {1}, ", fieldName, paramValue)); } else { setBuilder.Append(string.Format("[{0}] = @Curr_{1}, ", fieldName, fieldName)); paramList.Add(new SqlCommandParameter() { Name = "@Curr_{0}".FormatWith(fieldName), DataType = field.DataType, Value = dr[fieldName] }); } } else { if (field.UpdateDefaultValue != null && field.UpdateDefaultValue != DBNull.Value) { var paramValue = DefaultValueHelper.GetValue(field.UpdateDefaultValue); setBuilder.Append(string.Format("[{0}] = {1}, ", fieldName, paramValue)); } } } } int index = 0; string set = setBuilder.ToString(); index = set.LastIndexOf(","); if (index >= 0) set = set.Substring(0, index); if (!string.IsNullOrWhiteSpace(set)) { cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, set, whereBuilder.ToString()); cmd.Parameters = paramList.ToArray(); } else { cmd = null; } return cmd; }