/// <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;
}