/// <summary>
/// 更新-事务支持 - 若为交易视图表则会根据最后修改时间自动计算表名。
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Update(EntityBase entity, DataAccessBroker broker)
{
List <ColumnInfo> listUpdateColumnInfo = new List <ColumnInfo>();
foreach (ColumnInfo columnInfo in entity.OringTableSchema.ValueColumnInfo)
{
if (columnInfo.ColumnName == "CREATE_TIME" || columnInfo.ColumnName == "CREATED_BY" || columnInfo.ColumnName == "EFFECTIVE_DATE" || columnInfo.ColumnName == "EXPIRATION_DATE")
{
continue;
}
ColumnInfo updateColumnInfo = new ColumnInfo(columnInfo.ColumnName, columnInfo.ColumnCaption, columnInfo.IsPrimaryKey, columnInfo.DataType);
listUpdateColumnInfo.Add(updateColumnInfo);
}
string sqlString = "";
sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(listUpdateColumnInfo, broker.ParameterPrefix, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND ");
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
object tpValue;
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
if (field.ColumnName == "CREATE_TIME" || field.ColumnName == "CREATED_BY" || field.ColumnName == "EFFECTIVE_DATE" || field.ColumnName == "EXPIRATION_DATE")
{
continue;
}
//dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
tpValue = entity.GetData(field.ColumnName);
if (tpValue == null)
{
if (field.DataType == typeof(bool))
{
tpValue = false;
}
else if (field.DataType == typeof(string))
{
tpValue = "";
}
else if (field.DataType == typeof(int))
{
tpValue = 0;
}
else if (field.DataType == typeof(decimal))
{
tpValue = 0;
}
else if (field.DataType == typeof(DateTime))
{
tpValue = DateTime.MaxValue;
}
else
{
tpValue = "";
}
}
dpc.AddWithValue(field, tpValue);
}
broker.ExecuteSQL(sqlString, dpc);
}
public static void Update <T>(DataAccessBroker broker, List <T> list) where T : EntityBase
{
// 目标为数据表的情况
string curProcess = "";
try
{
// 保存数据
curProcess = "修改数据";
if (list.Count > 0)
{
string sqlString = "UPDATE " + list[0].OringTableSchema.TableName + " SET " + ParseSQL(list[0].OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(list[0].OringTableSchema.KeyColumnInfo, " AND ");
foreach (T entity in list)
{
Application.DoEvents();
// 给目标表新增记录
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
}
}
catch (Exception ex)
{
// 写日志
string description = "运行中异常，目前执行到'" + curProcess + "',单一事务，事务回滚.";
// 继续抛出异常
throw new InvalidOperationException(description + ex.Message);
}
}
public static void ExcuteNoneQuery(string sqlString, DataAccessParameterCollection para, CommandType type)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
broker.ExecuteNonQuery(sqlString, para, type);
}
}
public static void UpdateAndSave(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "";
if (entity.OringTableSchema.TableName == TransactionProductView || entity.OringTableSchema.TableName == TransactionView)
{
sqlString = "UPDATE " + GetTableName(entity) + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, " AND ");
}
else
{
sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, " AND ");
}
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
}
int i = 0;
i = broker.ExecuteSQL(sqlString, dpc);
if (i == 0)
{
DataAccess.Insert(entity);
}
}
public static object SelectScalar(string sqlString, DataAccessParameterCollection paraCollection)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
return(broker.ExecuteScalar(sqlString, paraCollection, CommandType.Text));
}
}
/// <summary>
/// 执行存储过程并返回Dataset-有参数
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet FillCommandDataSet(string commandString, DataAccessParameterCollection parameters)
{
DataSet result = new DataSet();
ExecuteDataSet(commandString, parameters, CommandType.StoredProcedure, null, ref result);
return(result);
}
///// <summary>
///// 组合Update语句，根据id值加where条件
///// </summary>
///// <param name="strLeftSql"></param>
///// <param name="id"></param>
///// <returns></returns>
//public string GetTargetUpdateSQL(string strLeftSql, string id)
//{
// return strLeftSql + " where " + this.TargetTable.PrimaryKey + "='" + id + "'";
//}
/// <summary>
/// 得到客户端语句的参数值
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public DataAccessParameterCollection GetTargetParameters(DataRow row, List <string> nullFieldList)
{
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (FieldMap field in FieldMapList)
{
object value = row[field.SourceField];
string fieldName = field.TargetField;
if (nullFieldList != null && nullFieldList.IndexOf(fieldName) != -1)
{
if (value != null && value.ToString() == "")
{
value = null;
}
}
ColumnInfo columnInfo = new ColumnInfo(fieldName, fieldName, false, value.GetType());
dpc.AddWithValue(columnInfo, value);//field.TargetField, row[field.SourceField]);
}
//// 加入Guid主键
//if (TargetTable.GUIDPrimaryKey != "")
// dpc.AddWithValue(TargetTable.GUIDPrimaryKey, Guid.NewGuid().ToString());
return(dpc);
}
/// <summary>
/// 获取数据通过Command
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command">SQL</param>
/// <returns></returns>
public static EntityCollection <T> Select <T>(string queryString, DataAccessParameterCollection parameters, CommandType cmdType)
where T : EntityBase, new()
{
EntityCollection <T> result = new EntityCollection <T>();
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
IDataReader reader = broker.ExecuteReader(queryString, parameters, cmdType);
int fieldCount = reader.FieldCount;
List <string> columns = new List <string>();
object[] values = new object[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
columns.Add(reader.GetName(i));
}
while (reader.Read())
{
T t = new T();
reader.GetValues(values);
for (int i = 0; i < fieldCount; i++)
{
if (!(values[i] == DBNull.Value))
{
t.SetData(columns[i], values[i]);
t.GetData(columns[i]);
}
}
result.Add(t);
}
}
return(result);
}
/// <summary>
/// 执行SQL语句并返回Dataset-有参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet FillSQLDataSet(string sqlString, DataAccessParameterCollection parameters)
{
DataSet result = new DataSet();
ExecuteDataSet(sqlString, parameters, CommandType.Text, null, ref result);
return(result);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlString">必须带上where 1=1</param>
/// <param name="lstParameter"></param>
/// <param name="grid"></param>
/// <param name="DbInstanceName">数据库实例</param>
/// <returns></returns>
public static LigerGrid QueryTable <T>(string sqlString, DataAccessParameterCollection lstParameter, LigerGrid grid, string DbInstanceName = "") where T : EntityBase, new()
{
string hql = sqlString;
string where = grid.Where;
if (!string.IsNullOrEmpty(where))
{
hql += " and (" + where + ")";
}
if (lstParameter == null)
{
lstParameter = new DataAccessParameterCollection();
}
if (grid != null && grid.LstParms != null && grid.LstParms.Count > 0)
{
foreach (var item in grid.LstParms)
{
lstParameter.Add(item.Name, new DataAccessParameter(item.Name, item.Value));
}
}
if (!string.IsNullOrEmpty(DbInstanceName))
{
using (DataAccessBroker broker = DataAccessFactory.Instance(DbInstanceName))
{
return(ExcuteTable <T>(hql, lstParameter, grid, broker));
}
}
else
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
return(ExcuteTable <T>(hql, lstParameter, grid, broker));
}
}
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
protected virtual void ResolveParameters(DbCommand command, DataAccessParameterCollection parameters)
{
foreach (DataAccessParameter parameter in parameters.Values)
{
AddParameter(command, parameter);
}
}
/// <summary>
/// SQL
/// </summary>
/// <param name="sqlString"></param>
public WhereBuilder(string sqlString)
{
_whereString = "";
_fixFirstCondition = true;
_sqlString = sqlString;
_parameters = new DataAccessParameterCollection();
}
/// <summary>
/// 根据实体生成关系为AND的条件
/// </summary>
/// <param name="entity"></param>
public WhereBuilder(EntityBase entity)
{
_sqlString = "SELECT * FROM " + entity.OringTableSchema.TableName;
_whereString = "";
_parameters = new DataAccessParameterCollection();
AddAndCondition(entity);
}
/// <summary>
/// 执行存储过程并返回DataReader-无参数
/// </summary>
/// <param name="queryString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <param name="mapping"></param>
/// <param name="result"></param>
/// <returns></returns>
public int ExecuteDataSet(string queryString, DataAccessParameterCollection parameters, CommandType cmdType, DataTableMapping mapping, ref DataSet result)
{
DbCommand command = CreateCommand(queryString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
return(ExecuteDataSet(command, mapping, ref result));
}
public static int ExecuteStoreProcedureHasReturn(string spName, DataAccessParameterCollection parameters)
{
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
var rowsaffected = broker.ExecuteCommand(spName, parameters);
return(1);
}
}
public static DataSet Select(string queryString, string instanceName, DataAccessParameterCollection parameters)
{
DataSet ds = null;
using (DataAccessBroker broker = DataAccessFactory.Instance(instanceName))
{
ds = broker.FillSQLDataSet(queryString, parameters);
}
return(ds);
}
public ActionResult TemplateReviewProcessReport(string id)
{
CasContractEntity entity = new CasContractEntity();
if (!string.IsNullOrEmpty(id))
{
var sql = @"
SELECT CAS_CONTRACT.CONTRACT_ID,CAS_CONTRACT.TEMPLATE_NO, CAS_CONTRACT.FERRERO_ENTITY,
CAS_CONTRACT.TEMPLATE_NAME,CAS_CONTRACT.CONTRACT_TYPE_NAME,
CAS_CONTRACT.EFFECTIVE_DATE, CAS_CONTRACT.EXPIRATION_DATE,
CAS_CONTRACT.TEMPLATE_OWNER,CAS_CONTRACT.APPLY_DATE,CAS_CONTRACT.CREATE_TIME,
CAS_CONTRACT.TEMPLATE_INITIATOR,CAS_CONTRACT.SCOPE_OF_APPLICATION
FROM CAS_CONTRACT
WHERE CAS_CONTRACT.CONTRACT_ID = @CONTRACT_ID";
DataAccessParameterCollection param = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("CONTRACT_ID", "CONTRACT_ID", true, typeof(string));
param.AddWithValue(columnInfo, id);
List <CasContractEntity> list = null;
try
{
list = DataAccess.Select <CasContractEntity>(sql, param);
}
catch (Exception)
{
}
if (list != null && list.Any())
{
entity = list[0];
//CasContractTypeEntity casContractTypeEntity = BusinessDataService.ContractTypeManagementService.GetById<CasContractTypeEntity>(entity.ContractTypeId);
//ViewBag.ContractType = casContractTypeEntity;
//List<CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(id);
//var mineFileIds = list.Where(f => f.AttachmentType == 1).ToList();
//var originalFileIds = list.Where(f => f.AttachmentType == 2).ToList();
//ViewBag.MineFiles = mineFileIds;
//ViewBag.OriginalFiles = originalFileIds;
#region 合同审批结果
//获取审批结果信息
DataTable approvalResultDt = BusinessDataService.CommonHelperService.GetReportApprovalResultDt(entity.ContractId);
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var ret = JsonConvert.SerializeObject(approvalResultDt, setting);
ViewBag.ApprovalResult = ret;
#endregion
}
}
return(View(entity));
}
/// <summary>
/// 执行executeString
/// </summary>
/// <param name="executeString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public object ExecuteScalar(string executeString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(executeString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
object val = command.ExecuteScalar();
command.Parameters.Clear();
return(val);
}
/// <summary>
/// 新增-事务支持
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Insert(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "INSERT INTO " + entity.OringTableSchema.TableName + " ( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, ParameterPrefix) + ")";
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
/// <summary>
/// 执行queryString语句并返回DataReader
/// </summary>
/// <param name="queryString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string queryString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(queryString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
DbDataReader rdr = command.ExecuteReader();
command.Parameters.Clear();
return((IDataReader)rdr);
}
/// <summary>
/// 准备Command及相应参数
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
protected virtual void PrepareCommand(DbCommand command, DataAccessParameterCollection parameters)
{
if (null != parameters && parameters.Count > 0)
{
ResolveParameters(command, parameters);
}
if (_inTransaction)//如果使用事务则该command应该使用事务
{
command.Transaction = _transaction;
}
}
public int ExecuteNonQuery(string executeString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(executeString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
int iReturn = command.ExecuteNonQuery();
command.Parameters.Clear();
return(iReturn);
}
/// <summary>
/// 删除-事务支持-根据主键删除
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Delete(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "DELETE FROM " + entity.OringTableSchema.TableName + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND ");
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.KeyColumnInfo)
{
dpc.AddWithValue(field, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
/// <summary>
/// 新增-事务支持-若为交易视图表则会根据最后修改时间自动计算表名。
/// 此方法不把时间为NULL的替换为最大时间
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void InsertEX(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "";
sqlString = "INSERT INTO " + entity.OringTableSchema.TableName + " ( " + ParseInsertSQL(entity.OringTableSchema.NoIdentityColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.NoIdentityColumnInfo, broker.ParameterPrefix) + ")";
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
object tpValue;
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
if (field.IsIdentity)
{
continue;
}
//dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
tpValue = entity.GetData(field.ColumnName);
if (tpValue == null)
{
if (field.DataType == typeof(bool))
{
tpValue = false;
}
else if (field.DataType == typeof(string))
{
tpValue = "";
}
else if (field.DataType == typeof(int))
{
tpValue = 0;
}
else if (field.DataType == typeof(decimal))
{
tpValue = 0;
}
else if (field.DataType == typeof(DateTime))
{
//tpValue = DateTime.MaxValue;
}
else
{
tpValue = "";
}
}
if (field.DataType == typeof(DateTime) && tpValue != null && tpValue.ToString() == DateTime.MaxValue.ToString())
{
tpValue = null;
}
dpc.AddWithValue(field, tpValue);
}
broker.ExecuteSQL(sqlString, dpc);
}
///// <summary>
///// 组合Update语句，根据id值加where条件
///// </summary>
///// <param name="strLeftSql"></param>
///// <param name="id"></param>
///// <returns></returns>
//public string GetSourceUpdateSQL(string strLeftSql, string id)
//{
// return strLeftSql + " where " + this.SourceTable.PrimaryKey + "='" + id + "'";
//}
/// <summary>
/// 得到客户端语句的参数值
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public DataAccessParameterCollection GetSourceParameters(DataRow row)
{
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (FieldMap field in FieldMapList)
{
ColumnInfo columnInfo = new ColumnInfo(field.SourceField, field.SourceField, false, row[field.TargetField].GetType());
dpc.AddWithValue(columnInfo, row[field.TargetField]);
}
return(dpc);
}
public static Dictionary <string, object> ExecuteProcReturnOutput(DataAccessBroker broker, string commandString, DataAccessParameter[] dps)
{
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
if (dps != null && dps.Length > 0)
{
foreach (DataAccessParameter dp in dps)
{
dpc.Add(dp.ParameterName, dp);
}
}
return(broker.ExecuteProcReturnOutput(commandString, dpc));
}
public virtual int SetPassword(string userAccount, byte[] encPassword)
{
DataAccessParameterCollection param = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("UserAccount", "UserAccount", false, typeof(string));
param.AddWithValue(columnInfo, userAccount);
columnInfo = new ColumnInfo("EncryptedPassword", "EncryptedPassword", false, typeof(string));
param.AddWithValue(columnInfo, encPassword);
var num = DataAccess.ExecuteStoreProcedureHasReturn("sp_Sec_User_SetPassword", param);
return(num);
}
public static LigerGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, LigerGrid grid, DataAccessBroker broker) where T : EntityBase, new()
{
object total = broker.ExecuteScalar("select count(1) as Total from (" + hql + ") as ttable", lstParameter, CommandType.Text);
StringBuilder strsql = new StringBuilder();
if (grid.pageIndex != 0 && grid.pageSize != 0)
{
strsql.Append("select * from ( select top " + (grid.pageIndex * grid.pageSize) + " row_number() over(order by " + grid.sortName + " " + grid.sortOrder.ToLower() + ") as Row_Number, ");
strsql.Append(hql.Replace(":", "@").Substring(7));
if (!string.IsNullOrEmpty(grid.Where))
{
strsql.Append(" and (" + grid.Where.Replace(":", "@") + ")");;
}//as
strsql.Append(" ) ttable where Row_Number between " + (((grid.pageIndex - 1) * grid.pageSize) + 1) + " and " + ((grid.pageIndex) * grid.pageSize));
}
else
{
strsql.Append(hql);
if (!string.IsNullOrEmpty(grid.sortName))
{
strsql.Append(" order by " + grid.sortName);
if (!string.IsNullOrEmpty(grid.sortOrder))
{
strsql.Append(" " + grid.sortOrder);
}
else
{
strsql.Append(" ASC");
}
}
}
DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter);
EntityCollection <T> lstEntity = new EntityCollection <T>();
List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >();
ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity);
if (grid.IsDataEntity)
{
grid.Rows = lstEntity;
}
else
{
grid.Rows = dicLstRow;
}
grid.Total = (total != null ? Convert.ToInt32(total) : 0);
return(grid);
}
/// <summary>
/// 更新-事务支持 - 若为交易视图表则会根据最后修改时间自动计算表名。
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void UpdateEX(EntityBase entity, DataAccessBroker broker)
{
string sqlString = "";
sqlString = "UPDATE " + entity.OringTableSchema.TableName + " SET " + ParseSQL(entity.OringTableSchema.ValueColumnInfo, broker.ParameterPrefix, ",") + " WHERE " + ParseSQL(entity.OringTableSchema.KeyColumnInfo, broker.ParameterPrefix, " AND ");
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
object tpValue;
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
//dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
tpValue = entity.GetData(field.ColumnName);
if (tpValue == null)
{
if (field.DataType == typeof(bool))
{
tpValue = false;
}
else if (field.DataType == typeof(string))
{
tpValue = "";
}
else if (field.DataType == typeof(int))
{
tpValue = 0;
}
else if (field.DataType == typeof(decimal))
{
tpValue = 0;
}
else if (field.DataType == typeof(DateTime))
{
//tpValue = DateTime.MaxValue;
}
else
{
tpValue = "";
}
}
if (field.DataType == typeof(DateTime) && tpValue != null && tpValue.ToString() == DateTime.MaxValue.ToString())
{
tpValue = null;
}
dpc.AddWithValue(field, tpValue);
}
broker.ExecuteSQL(sqlString, dpc);
}
public virtual UserDomain GetDomainByLoginName(string systemName, string userAccount)
{
var sql =
"SELECT * FROM CAS_USER WHERE USER_ACCOUNT = @UserAccount OR EMAIL = @UserAccount OR MOBILE_PHONE = @UserAccount";
DataAccessParameterCollection param = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("UserAccount", "UserAccount", false, typeof(string));
param.AddWithValue(columnInfo, userAccount);
var list = DataAccess.Select <CasUserEntity>(sql, param);
if (list != null && list.Any())
{
var user = list[0];
return(new UserDomain(systemName, user));
}
return(null);
}
/// <summary>
/// 新增-事务支持-若为交易视图表则会根据最后修改时间自动计算表名。
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
public static void Insert(EntityBase entity, DataAccessBroker broker)
{
ParameterPrefix = broker.ParameterPrefix.ToString();
string sqlString = "";
if (entity.OringTableSchema.TableName == TransactionProductView || entity.OringTableSchema.TableName == TransactionView)
{
sqlString = "INSERT INTO " + GetTableName(entity) + " ( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, ParameterPrefix) + ")";
}
else
{
sqlString = "INSERT INTO " + entity.OringTableSchema.TableName + " ( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, "") + " ) VALUES( " + ParseInsertSQL(entity.OringTableSchema.AllColumnInfo, ParameterPrefix) + ")";
}
DataAccessParameterCollection dpc = new DataAccessParameterCollection();
foreach (ColumnInfo field in entity.OringTableSchema.AllColumnInfo)
{
dpc.AddWithValue(field.ColumnName, entity.GetData(field.ColumnName));
}
broker.ExecuteSQL(sqlString, dpc);
}
/// <summary>
/// 执行存储过程并返回执行结果-有参数
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteCommandScalar(string commandString, DataAccessParameterCollection parameters)
{
return ExecuteScalar(commandString, parameters, CommandType.StoredProcedure);
}
/// <summary>
/// 执行SQL语句并返回执行结果-有参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteSQLScalar(string sqlString, DataAccessParameterCollection parameters)
{
return ExecuteScalar(sqlString, parameters, CommandType.Text);
}
/// <summary>
/// 执行executeString
/// </summary>
/// <param name="executeString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public object ExecuteScalar(string executeString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(executeString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
object val = command.ExecuteScalar();
command.Parameters.Clear();
command.Dispose();
return val;
}
/// <summary>
/// 准备Command及相应参数
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
protected virtual void PrepareCommand(DbCommand command, DataAccessParameterCollection parameters)
{
if (null != parameters && parameters.Count > 0)
{
ResolveParameters(command, parameters);
}
if (_inTransaction)//如果使用事务则该command应该使用事务
{
command.Transaction = _transaction;
}
}
public int ExecuteNonQuery(string executeString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(executeString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
int iReturn = command.ExecuteNonQuery();
command.Parameters.Clear();
command.Dispose();
return iReturn;
}
/// <summary>
/// 执行queryString语句并返回DataReader
/// </summary>
/// <param name="queryString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string queryString, DataAccessParameterCollection parameters, CommandType cmdType)
{
DbCommand command = CreateCommand(queryString);
command.CommandType = cmdType;
PrepareCommand(command, parameters);
DbDataReader rdr = command.ExecuteReader();
command.Parameters.Clear();
command.Dispose();
return (IDataReader)rdr;
}
/// <summary>
/// 执行存储过程并返回DataReader-无参数
/// </summary>
/// <param name="queryString"></param>
/// <param name="parameters"></param>
/// <param name="cmdType"></param>
/// <param name="mapping"></param>
/// <param name="result"></param>
/// <returns></returns>
public int ExecuteDataSet(string queryString, DataAccessParameterCollection parameters, CommandType cmdType, DataTableMapping mapping, ref DataSet result)
{
DbCommand command = CreateCommand(queryString);
command.CommandType = cmdType;
command.CommandTimeout = 300;
PrepareCommand(command, parameters);
return ExecuteDataSet(command, mapping, ref result);
}
/// <summary>
/// 执行存储过程并返回Dataset-有参数
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet FillCommandDataSet(string commandString, DataAccessParameterCollection parameters)
{
DataSet result = new DataSet();
ExecuteDataSet(commandString, parameters, CommandType.StoredProcedure, null, ref result);
return result;
}
/// <summary>
/// 执行SQL语句并返回Dataset-有参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet FillSQLDataSet(string sqlString, DataAccessParameterCollection parameters)
{
DataSet result = new DataSet();
ExecuteDataSet(sqlString, parameters, CommandType.Text, null, ref result);
return result;
}
/// <summary>
/// SQL 与 参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
public WhereBuilder(string sqlString, DataAccessParameterCollection parameters):this(sqlString)
{
_parameters = parameters;
}
/// <summary>
/// 执行SQL语句-有参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteSQL(string sqlString, DataAccessParameterCollection parameters)
{
return ExecuteNonQuery(sqlString, parameters, CommandType.Text);
}
/// <summary>
/// 执行存储过程-有参数
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteCommand(string commandString, DataAccessParameterCollection parameters)
{
return ExecuteNonQuery(commandString, parameters, CommandType.StoredProcedure);
}
/// <summary>
/// 执行SQl语句并返回DataReader-有参数
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteSQLReader(string sqlString, DataAccessParameterCollection parameters)
{
return ExecuteReader(sqlString, parameters, CommandType.Text);
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="command"></param>
/// <param name="parameters"></param>
protected virtual void ResolveParameters(DbCommand command, DataAccessParameterCollection parameters)
{
foreach (DataAccessParameter parameter in parameters.Values)
{
AddParameter(command, parameter);
}
}
/// <summary>
/// 执行存储过程并返回DataReader-有参数
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteCommandReader(string commandString, DataAccessParameterCollection parameters)
{
return ExecuteReader(commandString, parameters, CommandType.StoredProcedure);
}
/// <summary>
/// 执行存储过程并返回出参值列表 Lucas 2011-01-04
/// </summary>
/// <param name="commandString"></param>
/// <param name="parameters"></param>
/// <returns>outputList</returns>
public Dictionary<string,object> ExecuteProcReturnOutput(string commandString, DataAccessParameterCollection parameters)
{
Dictionary<string, object> outputKeyValues = new Dictionary<string, object>();
DbCommand command = CreateCommand(commandString);
command.CommandType = CommandType.StoredProcedure;
PrepareCommand(command, parameters);
int iReturn = command.ExecuteNonQuery();
foreach (DbParameter param in command.Parameters)
{
if (param.Direction == ParameterDirection.Output)
{
outputKeyValues.Add(param.ParameterName,param.Value);
}
}
command.Parameters.Clear();
command.Dispose();
return outputKeyValues;
}
/// <summary>
/// 根据实体生成关系为AND的条件
/// </summary>
/// <param name="entity"></param>
public WhereBuilder(EntityBase entity)
{
_sqlString = "SELECT * FROM " + entity.OringTableSchema.TableName;
_whereString = "";
_fixFirstCondition = true;
_parameters = new DataAccessParameterCollection();
AddAndCondition(entity);
}
