public CRUDSql GetSelectSql(T searchPara, string tableName, string orderBy, List <string> selectFields = null)
{
// ComDBFun ComDBFun = new ComDBFun(bOrcl);
Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(searchPara);
List <string> whereParas = dic.Keys.ToList();
object[] values = dic.Values.ToArray();
for (int i = dic.Values.Count - 1; i >= 0; i--)//比较值为空的不参与比较
{
if (dic.Values.ToList()[i] == null || string.IsNullOrEmpty(dic.Values.ToList()[i].ToString()))
{
whereParas.Remove(dic.Keys.ToList()[i]);
dic.Remove(dic.Keys.ToList()[i]);
}
}
string whereSql = new ComDBFun(DBBaseAttr).GetWhereCondition(whereParas, "and");
string fds = (selectFields == null || selectFields.Count <= 0) ? "*" : string.Join(",", selectFields);
orderBy = string.IsNullOrEmpty(orderBy) ? "" : "order by " + orderBy;
string sql = string.Format("select {0} from {1} {2} {3}", fds, tableName, whereSql, orderBy);
CRUDSql res = new CRUDSql()
{
Sql = sql
};
res.PMS = GetDbParametersFromDic(dic);
return(res);
}
public ExeResEdm Update(string tableName, T model, List <string> whereParas, DBOperUser dbLogMsg = null, params string[] skipCols)
{
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model);
// object[] values = dic.Values.ToArray();
string idVal = dic.Values.ToArray()[0].ToString();
for (int i = 0; i < skipCols.Length; i++)//自动增长的列要忽略
{
dic.Remove(skipCols[i]);
}
for (int i = 0; i < whereParas.Count; i++)
{
try
{
var curKey = dic.Where(a => a.Key.Equals(whereParas[i], StringComparison.OrdinalIgnoreCase)).FirstOrDefault().Key;
dic.Remove(curKey);
}
catch
{
}
}
for (int i = dic.Values.Count - 1; i >= 0; i--)//比较值为空的不参与比较
{
if (dic.Values.ToList()[i] == null)
{
dic.Remove(dic.Keys.ToList()[i]);
}
}
return(Update(tableName, dic, null, dbLogMsg));
}
public ExeResEdm Add(string tableName, T model, params string[] skipCols)
{
Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model);
object[] values = dic.Values.ToArray();
// string idVal = dic.Values.ToArray()[0].ToString();
//SqlParameter[] pms = GetOleDbParameters(dic.Keys.ToList(), dic.Values.ToList());//参数过多，不会影响程序执行的正确性。
for (int i = 0; i < skipCols.Length; i++)//自动增长的列要忽略
{
dic.Remove(skipCols[i]);
}
for (int i = dic.Values.Count - 1; i >= 0; i--)//值为空的不参与
{
if (dic.Values.ToList()[i] == null)
{
dic.Remove(dic.Keys.ToList()[i]);
}
}
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
string textParas = ComDBFun.GetSQLText(dic.Keys.ToList());
string sql = "insert into " + tableName + textParas;
// SqlParameter[] pms = ComDBFun.GetMSOleDbParameters(dic.Keys.ToList(), dic.Values.ToList());
DbParameter[] //pms = ComDBFun.GetOrclOleDbParameters(dic.Keys.ToList(), dic.Values.ToList());
pms = GetDbParametersFromDic(dic);
var n = ExecuteNonQuery(sql, pms);
return(n);
}
public ExeResEdm Exist(string tableName, T model, List <string> whereParas, DBOperUser dbLogMsg = null)
{
whereParas = whereParas.Where(a => !string.IsNullOrEmpty(a)).Select(a => a.ToLower()).Distinct().ToList();
Dictionary <string, object> whereDic = DtModelConvert <T> .GetPropertity(model);
whereDic = whereDic.Where(a => whereParas.Contains(a.Key.ToLower())).ToDictionary(k => k.Key, v => v.Value);
return(Exist(tableName, whereDic, dbLogMsg));
}
public CRUDSql GetInsertSql <M>(M model, string tableName, bool bParameterizedQuery)
{
Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model);
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
string textParas = ComDBFun.GetSQLText(dic.Keys.ToList(), (bParameterizedQuery ? null : dic.Values.ToList()));
string sql = "insert into " + tableName + textParas;
CRUDSql insertSql = new CRUDSql()
{
Sql = sql
};
if (bParameterizedQuery)
{
insertSql.PMS = GetDbParametersFromDic(dic);
}
return(insertSql);
}
public ExeResEdm Add(string tableName, T model, DBOperUser dbLogMsg = null, params string[] skipCols)
{
Dictionary <string, object> dic = DtModelConvert <T> .GetPropertity(model);
object[] values = dic.Values.ToArray();
// string idVal = dic.Values.ToArray()[0].ToString();
//SqlParameter[] pms = GetOleDbParameters(dic.Keys.ToList(), dic.Values.ToList());//参数过多，不会影响程序执行的正确性。
for (int i = 0; i < skipCols.Length; i++)//自动增长的列要忽略
{
dic.Remove(skipCols[i]);
}
for (int i = dic.Values.Count - 1; i >= 0; i--)//值为空的不参与
{
if (dic.Values.ToList()[i] == null)
{
dic.Remove(dic.Keys.ToList()[i]);
}
}
ComDBFun ComDBFun = new ComDBFun(DBBaseAttr);
string textParas = ComDBFun.GetSQLText(dic.Keys.ToList(), null);
string sql = "insert into " + tableName + textParas;
DbParameter[] pms = GetDbParametersFromDic(dic);
LogTraceEdm logMsg = null;
if (dbLogMsg != null)
{
logMsg = new LogTraceEdm()
{
LogType = LogType.添加, UserId = dbLogMsg.UserId, UserName = dbLogMsg.UserName, TabOrModu = tableName,
};
}
var n = ExecuteNonQuery(sql, logMsg, pms);
return(n);
}