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