public static RequestBase ConvertParameter(object param)
{
RequestBase result = new RequestBase();
if (null == param)
{
return(result);
}
IEnumerable <DbParameter> dbpars = param as IEnumerable <DbParameter>;
if (null != dbpars)
{
foreach (var ele in dbpars)
{
result.SetValue(ele.ParameterName.TrimParaName(), ele.Value);
}
}
else
{
IDictionary <string, object> dic = AsDictionary(param);
if (null != dic)
{
foreach (var ele in dic)
{
result.SetValue(ele.Key, ele.Value);
}
}
}
return(result);
}
/// <summary>
/// Chain操作中使用
/// </summary>
/// <param name="paraName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlChain AddParameter(string paraName, object value, Func <bool> addThisPara = null)
{
if (null == addThisPara || (null != addThisPara && addThisPara()))
{
param.SetValue(paraName, value);
}
return(this);
}
public void UnLockBill(string branchCode, int billType, long billId)
{
RequestBase pars = new RequestBase();
pars.SetValue("BranchCode", branchCode);
pars.SetValue("BillType", billType);
pars.SetValue("BillId", billId);
_Conn.ExecuteNonQuery($"Delete from {BillLockTableName} where FBranchCode=#BranchCode# and FBillType=#BillType# and FBillId=#BillId#", pars);
}
/// <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);
}
}
/// <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);
}
}
public static void AddParameters(Dictionary <string, object> paramemters, ref RequestBase para)
{
if (null == paramemters)
{
return;
}
foreach (var ele in paramemters)
{
para.SetValue(ele.Key, ele.Value);
}
}
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="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));
}
/// <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));
}
/// <summary>
/// 是否存在相应的主键
/// </summary>
/// <param name="Conn"></param>
/// <param name="TableName"></param>
/// <param name="dr"></param>
/// <param name="keyFields"></param>
/// <param name="pars"></param>
/// <param name="ConnType"></param>
/// <returns></returns>
private static bool ExistsDataRow(DbContext connInfo, string TableName, DataRow dr, string[] keyFields, RequestBase pars)
{
StringBuilder strbld = new StringBuilder(256);
strbld.AppendFormat("select 'Y' From {0} where ", TableName);
int count = keyFields.Length;
//pars.Clear();
for (int i = 0; i < count; i++)
{
if (0 != i)
{
strbld.Append(" and ");
}
string fieldName = keyFields[i];
object obj = null;
strbld.AppendFormat(" {0}={1} ", fieldName, connInfo.TreatParaName(fieldName));
obj = GetDataFieldValue(dr[fieldName], connInfo.DbType);
pars.SetValue(fieldName, obj);
}
return(connInfo.ExistsPrimitive(strbld.ToString(), pars));
}