/// <summary>
/// 会排除自动增长列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="Record"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static int InsertPrimitive <T>(this DbContext connInfo, IEnumerable <T> records, SqlFunction repl = null, IEnumerable <string> onlyFields = null, string tableName = "")
where T : new()
{
string sql = connInfo.GetInsertSql <T>(repl, onlyFields, tableName);
if (connInfo.DbType != DbConnType.ODPNET)
{
return(connInfo.Db.Execute(sql, records, connInfo.Transaction, commandType: CommandType.Text));
}
else
{
Type t = typeof(T);
TableInfo tblInfo = EntityReader.GetEntityMapping(t, true);
Dictionary <string, PropertyInfo> fields = tblInfo.PropertyMappings;
int count = 0;
foreach (T record in records)
{
RequestBase param = new RequestBase();
foreach (KeyValuePair <string, PropertyInfo> item in fields)
{
if (tblInfo.IsCurrentTimeField(item.Key) ||
(null != repl && repl.ExistsField(item.Key)))
{
continue;
}
param.Add(item.Key, SqlBuilder.GetPropertyValue <T>(record, item, connInfo.DbType));
}
count += connInfo.Db.Execute(sql, param, connInfo.Transaction, commandType: CommandType.Text);
}
return(count);
}
}
public static bool Exists <T>(this DbContext connInfo, object[] pkValues, string tableName = "", bool forUpdate = false)
where T : new()
{
if (0 == (pkValues?.Length ?? 0))
{
throw new Exception("Primary keys are missing!");
}
Type t = typeof(T);
TableInfo tableInfo = GetTableInfo(t, 0, tableName);
if (0 == (tableInfo?.PKeys?.Count() ?? 0))
{
throw new ArgumentNullException("PKeys");
}
RequestBase param = new RequestBase();
int j = 0;
foreach (string pkName in tableInfo.PKeys)
{
param.Add(pkName, pkValues[j]);
++j;
}
string sql = connInfo.GetExistsSql(t, tableName);
if (forUpdate && connInfo.SupportForUpdate)
{
sql += " for update";
}
return(connInfo.Db.ExecuteScalar <string>(sql, param, connInfo.Transaction, commandType: CommandType.Text).IsTrue());
}
/// <summary>
/// 传入主键值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="PkValues"></param>
/// <returns></returns>
public static int Delete <T>(this DbContext connInfo, object[] pkValues, string tableName = "")
where T : new()
{
int pkCount = pkValues?.Length ?? 0;
if (0 == pkCount)
{
throw new Exception("Primary keys are not specified!");
}
Type t = typeof(T);
RequestBase param = new RequestBase();
TableInfo tableInfo = GetTableInfo(t, 0, tableName);
if (0 == (tableInfo?.PKeys?.Count() ?? 0))
{
throw new ArgumentNullException("PKeys");
}
int j = 0;
foreach (string pkField in tableInfo.PKeys)
{
param.Add(pkField, pkValues[j]);
++j;
}
return(connInfo.Db.Execute(connInfo.GetDeleteSql(t, tableName), param, connInfo.Transaction, commandType: CommandType.Text));
}
/// <summary>
/// 根据主键来取记录(最常用)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="PkValues"></param>
/// <returns></returns>
public static T SelectPrimitive <T>(this DbContext connInfo, object[] pkValues, IEnumerable <string> onlyFields = null, bool isExceptField = false, string tableName = "", bool forUpdate = false)
where T : new()
{
string sql = "";
if (isExceptField && 0 < (onlyFields?.Count() ?? 0))//排除字段
{
sql = connInfo.GetSelectSql <T>(false, GetFields(typeof(T), true, onlyFields.ToArray()), tableName);
}
else
{
sql = connInfo.GetSelectSql <T>(false, onlyFields, tableName);
}
if (forUpdate && connInfo.SupportForUpdate)
{
sql += " for update";
}
TableInfo ti = GetTableInfo(typeof(T), tableName);
RequestBase param = new RequestBase();
int j = 0;
foreach (string pkName in ti.PKeys)
{
param.Add(pkName, pkValues[j]);
++j;
}
return(connInfo.Db.QueryFirstOrDefault <T>(sql, param, connInfo.Transaction, commandType: CommandType.Text));
}
/// <summary>
/// 取单个值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="pkValues"></param>
/// <param name="FieldName"></param>
/// <param name="pkCount"></param>
/// <returns></returns>
public static object SingleValuePrimitive <T>(this DbContext connInfo, object[] pkValues, string fieldName, string tableName = "", bool forUpdate = false)
{
if (0 == (pkValues?.Length ?? 0))
{
throw new Exception("primary keys are missing!");
}
Type t = typeof(T);
TableInfo tableInfo = GetTableInfo(t, tableName);
StringBuilder sqlbld = new StringBuilder(128);
sqlbld.Append("SELECT ");
sqlbld.Append(fieldName.DbDelimiter(connInfo));
sqlbld.Append(" FROM ");
sqlbld.Append(tableInfo.TableName.DbDelimiter(connInfo));
sqlbld.Append(" WHERE ");
RequestBase pars = new RequestBase();
int j = 0;
foreach (string pkName in tableInfo.PKeys)
{
if (0 != j)
{
sqlbld.Append(" AND ");
}
sqlbld.AppendFormat(" {0}={1} ", pkName.DbDelimiter(connInfo), connInfo.TreatParaName(pkName));
pars.Add(pkName, pkValues[j]);
++j;
}
if (forUpdate && connInfo.SupportForUpdate)
{
sqlbld.Append(" for update");
}
return(connInfo.Db.ExecuteScalar(sqlbld.ToString(), pars, connInfo.Transaction, commandType: CommandType.Text));
}
public int GetInterId(string branchCode, int billType)
{
string sql = $"SELECT #ISNULL#(MAX(FMAXID),0) FROM {BillTableName} where FBranchCode=#BranchCode# and FBillType=#BillType#";
RequestBase pars = new RequestBase();
pars.Add("BranchCode", branchCode);
pars.Add("BillType", billType);
object ret = _Conn.ExecuteScalar(sql, pars);
if (SmartCrudHelper.IsNullOrDBNull(ret))
{
return(0);
}
else
{
return(Convert.ToInt32(ret));
}
}
/// <summary>
/// 锁定一条记录,UpdateField在ORACLE下无效，用于执行 Update T set UpdateField=UpdateField where ...
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="obj"></param>
public static bool LockRecord <T>(this DbContext connInfo, T record, string updateField, string tableName = "")
{
Type t = typeof(T);
TableInfo tableInfo = GetTableInfo(t, 0, tableName);
if (null == tableInfo)
{
throw new ArgumentNullException("GetTableInfo");
}
StringBuilder whereState = new StringBuilder(256);
whereState.Append(" WHERE"); // where子句
int pkcount = tableInfo.PKeys.Count;
RequestBase pars = new RequestBase();
Dictionary <string, PropertyInfo> fields = EntityReader.GetTypePropertyMapping(t);
int i = 0;
foreach (KeyValuePair <string, PropertyInfo> item in
fields.Where(c => tableInfo.PKeys.Contains(c.Key, StringComparer.OrdinalIgnoreCase)))
{
if (i > 0)
{
whereState.Append(" AND ");
}
whereState.AppendFormat(" {0}={1} ", connInfo.DbDelimiter(item.Key), connInfo.TreatParaName(item.Key));
pars.Add(item.Key, GetPropertyValue <T>(record, item, connInfo.DbType));
++i;
}
StringBuilder sql = new StringBuilder(512);
if (connInfo.SupportForUpdate)
{
sql.Append("SELECT 'Y' FROM ");
sql.Append(connInfo.DbDelimiter(tableName));
sql.Append(whereState.ToString());
sql.Append(" FOR UPDATE ");
return(connInfo.Db.ExecuteScalar <string>(sql.ToString(), pars, connInfo.Transaction, commandType: CommandType.Text).IsTrue());
}
else
{
if (string.IsNullOrEmpty(updateField))
{
throw new Exception("update field error!");
}
sql.Append("UPDATE ");
sql.Append(tableInfo.TableName.DbDelimiter(connInfo));
sql.AppendFormat(" SET {0}={1}", updateField.DbDelimiter(connInfo), updateField);
sql.Append(whereState.ToString());
return(1 == connInfo.Db.Execute(sql.ToString(), pars, connInfo.Transaction, commandType: CommandType.Text));
}
}