public BillSetting GetBillSetting(string branchCode, int billType)
{
BillSetting billSetting = _Conn.Select <BillSetting>(pkValues: new object[] { branchCode, billType }, tableName: BillTableName);
if (null == billSetting)
{
return(null);
}
if (billSetting.FSerialFormat > 0)
{
string PrefixSerial = GetInitPrefixSerial(billSetting.FSerialFormat);
if (string.Compare(billSetting.FLstSerialPreFix, PrefixSerial, true) != 0)
{
if (string.IsNullOrWhiteSpace(billSetting.FLstSerialPreFix))//第一次使用,FLstSerialPreFix为空
{
_Conn.Update <BillSetting>(new BillSetting
{
FBillType = billType,
FBranchCode = branchCode,
FLstSerialPreFix = PrefixSerial
}, onlyFields: c => c.FLstSerialPreFix, tableName: BillTableName);
}
else
{
_Conn.ExecuteNonQuery($"Update {BillTableName} set FLstSerialPreFix=#LstPrefix#,FLstNumber=1 where FBranchCode=#BranchCode# and FBillType=#BillType#",
new { LstPrefix = PrefixSerial, BranchCode = branchCode, BillType = billType });
billSetting.FLstSerialPreFix = PrefixSerial;
billSetting.FLstNumber = 1;
}
}
}
return(billSetting);
}
/// <summary>
/// 把数据INSERT到MYSQL的临时表
/// </summary>
/// <param name="ConnInfo"></param>
/// <param name="dt"></param>
/// <param name="TableName"></param>
/// <param name="LotCount"></param>
/// <param name="Fields"></param>
private static void InsertMYSQLTmpTable(this DbContext ConnInfo,
DataTable dt, string TableName, Action <DbContext> funcCreateTable, params string[] Fields)
{
if (ConnInfo.DbType != DbConnType.MYSQL)
{
throw new Exception("Only MYSQL support");
}
if (null != funcCreateTable)
{
funcCreateTable.Invoke(ConnInfo);
}
else
{
ConnInfo.ExecuteNonQuery(GetDeleteMYSQLTmpTableString(TableName)); //删除表
ConnInfo.ExecuteNonQuery(GetCreateMYSQLTmpTableString(dt, TableName, Fields)); //创建表
}
string sql = $"insert into {TableName}(";
sql += string.Join(",", Fields);
sql += ") values(";
sql += string.Join(",", from p in Fields select $"#{p}#");
sql += ")";
RequestBase pars = new RequestBase();
foreach (DataRow dr in dt.Rows)
{
foreach (string fieldName in Fields)
{
pars.SetValue(fieldName, dr[fieldName]);
}
ConnInfo.ExecuteNonQuery(sql, pars);
}
}
public static void DeleteTempTable(this DbContext conn, string tableName)
{
tableName = conn.GetTempTableName(tableName);
if (conn.DbType == DbConnType.MSSQL)
{
conn.ExecuteNonQuery(GetDeleteSQLTmpTableString(tableName));
}
else if (conn.DbType == DbConnType.MYSQL)
{
conn.ExecuteNonQuery(GetDeleteMYSQLTmpTableString(tableName));
}
else if (conn.IsOracleDb)
{
conn.ExecuteNonQuery($"begin execute immediate 'truncate table {tableName}' ; end; ");
}
}
/// <summary>
/// ORACLE INSERT临时表,不会事先清除临时表
/// </summary>
/// <param name="Conn"></param>
/// <param name="dt"></param>
/// <param name="sql"></param>
/// <param name="LotCount"></param>
/// <param name="Fields"></param>
private static void InsertOraTmpTable(this DbContext ConnInfo,
DataTable dt, string TableName, Action <DbContext> funcCreateTable, params string[] Fields)
{
if (!ConnInfo.IsOracleDb)
{
throw new Exception("Only ORACLE support!");
}
funcCreateTable?.Invoke(ConnInfo);
string sql = $"insert into {TableName}(";
sql += string.Join(",", Fields);
sql += ") values(";
sql += string.Join(",", from p in Fields select $"#{p}#");
sql += ")";
RequestBase pars = new RequestBase();
foreach (DataRow dr in dt.Rows)
{
foreach (string fieldName in Fields)
{
pars.SetValue(fieldName, dr[fieldName]);
}
ConnInfo.ExecuteNonQuery(sql, pars);
}
}
private static bool DeleteDataRow2(DbContext connInfo, DataRow dr, string[] Keys, RequestBase Pars, string Sqldelete)
{
//Pars.Clear();
for (int i = 0, count = Keys.Length; i < count; i++)
{
string fieldName = Keys[i];
object obj = null;
obj = GetDataFieldValue(dr[fieldName], connInfo.DbType);
Pars.SetValue(fieldName, obj);
}
return(1 <= connInfo.ExecuteNonQuery(Sqldelete, Pars));
}
/// <summary>
/// 增加行
/// </summary>
/// <param name="Conn"></param>
/// <param name="TableName"></param>
/// <param name="dr"></param>
/// <param name="pars"></param>
/// <param name="ConnType"></param>
/// <param name="Fields"></param>
/// <param name="FieldStr"></param>
/// <param name="ParStr"></param>
/// <returns></returns>
private static bool AddDataRow(DbContext connInfo, string TableName, DataRow dr, RequestBase pars, string[] Fields, string FieldStr, string ParStr)
{
StringBuilder strbld = new StringBuilder(512);
strbld.AppendFormat("insert into {0}({1}) values({2})", TableName, FieldStr, ParStr);
for (int i = 0, count = Fields.Length; i < count; i++)
{
string fieldName = Fields[i];
object obj = GetDataFieldValue(dr[fieldName], connInfo.DbType);
pars.SetValue(fieldName, obj);
}
return(1 <= connInfo.ExecuteNonQuery(strbld.ToString(), pars));
}
public static int Delete <T>(this DbContext connInfo, string filter, object param, string tableName = "")
where T : new()
{
StringBuilder sqlbld = new StringBuilder(256);
sqlbld.Concat("DELETE FROM ", GetTableName <T>(connInfo.DbType, tableName));
if (!filter.StartsWith("WHERE", StringComparison.OrdinalIgnoreCase))
{
sqlbld.Append(" WHERE ");
}
sqlbld.Append(filter);
return(connInfo.ExecuteNonQuery(sqlbld.ToString(), param));
}
/// <summary>
/// 更新行
/// </summary>
/// <param name="Conn"></param>
/// <param name="TableName"></param>
/// <param name="dr"></param>
/// <param name="pars"></param>
/// <param name="ConnType"></param>
/// <param name="Fields"></param>
/// <param name="sqlUpdate"></param>
/// <returns></returns>
private static bool UpdateDataRow(DbContext connInfo, string TableName, DataRow dr, RequestBase pars, string[] Fields, string[] PkFields, string sqlUpdate)
{
for (int i = 0, count = Fields.Length; i < count; i++)
{
object obj = null;
string fieldName = Fields[i];
if (PkFields.Contains(fieldName, StringComparer.OrdinalIgnoreCase))
{
continue;
}
obj = GetDataFieldValue(dr[fieldName], connInfo.DbType);
pars.SetValue(fieldName, obj);
}
for (int i = 0, count = PkFields.Length; i < count; i++)
{
object obj = null;
string fieldName = PkFields[i];
obj = GetDataFieldValue(dr[fieldName], connInfo.DbType);
pars.SetValue(fieldName, obj);
}
return(1 <= connInfo.ExecuteNonQuery(sqlUpdate, pars));
}
bool Increase(DbContext conn, string branchCode, int billType)
{
return(1 == conn.ExecuteNonQuery(
$"Update {BillTableName} set FMaxId=FMaxId+1,FLstNumber=FLstNumber+1 where FBranchCode=#BranchCode# and FBillType=#BillType#",
new { BranchCode = branchCode, BillType = billType }));
}
/// <summary>
/// 删除SQL SERVER的临时表
/// </summary>
/// <param name="Conn"></param>
/// <param name="TmpTableName"></param>
private static void DeleteSQLTmpTable(DbContext Conn, string TmpTableName)
{
Conn.ExecuteNonQuery(GetDeleteSQLTmpTableString(TmpTableName));
}
public int Execute()
{
return(_connInfo.ExecuteNonQuery(GetSql(), param));
}