/// <summary>
/// 获取更新的语句
/// </summary>
/// <param name="tableName"></param>
/// <param name="Columns"></param>
/// <param name="Keys"></param>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetUpdateSqlStr(this DbConnType connType, string tableName, string[] Columns, string[] Keys)
{
StringBuilder strbld = new StringBuilder(512);
strbld.AppendFormat("Update {0} set ", connType.DbDelimiter(tableName));
for (int i = 0, count = Columns.Length; i < count; i++)
{
string fieldName = Columns[i];
if (Keys.Contains(fieldName))
{
continue;
}
string parName = connType.TreatParaName(fieldName);
strbld.AppendFormat("{0}={1},", connType.DbDelimiter(fieldName), parName);
}
strbld.Remove(strbld.Length - 1, 1);
strbld.Append(" where ");
for (int i = 0, count = Keys.Length; i < count; i++)
{
if (0 != i)
{
strbld.Append(" and ");
}
string fieldName = Keys[i];
string parName = connType.TreatParaName(fieldName);
strbld.AppendFormat("{0}={1}", connType.DbDelimiter(fieldName), parName);
}
return(strbld.ToString());
}
/// <summary>
/// 处理连接串,参数支持Diction<string,object>,DbParameter[] , new{},useBrackets=true则是使用这样的参数 #{Para1},否则就是使用 #Para1#
/// </summary>
/// <param name="dbConnType"></param>
/// <param name="oriSql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static string TransSQL(this DbConnType dbConnType, string oriSql, object param, bool useBrackets = false)
{
string sql = oriSql;
int countofChar = oriSql.Count(c => c == '#');
if (2 <= countofChar)
{
sql = sql.Replace(CURRENT_TIME, GetCurrentTimeFuncName(dbConnType)).Replace(IS_NULL, GetIsNullFuncName(dbConnType));
if (null != param)
{
IEnumerable <DbParameter> dbpars = param as IEnumerable <DbParameter>;
if (null != dbpars)
{
foreach (var par in dbpars)
{
string naturePara = par.ParameterName.TrimParaName();
string parNameInstead = "";
if (!useBrackets)
{
parNameInstead = string.Format("#{0}#", naturePara);
}
else
{
parNameInstead = "#{" + naturePara + "}";
}
sql = sql.Replace(parNameInstead, dbConnType.TreatParaName(naturePara));
}
}
else
{
IDictionary <string, object> dic = SmartCrudHelper.AsDictionary(param);
foreach (var par in dic)
{
string parNameInstead = "";
if (!useBrackets)
{
parNameInstead = string.Format("#{0}#", par.Key);
}
else
{
parNameInstead = "#{" + par.Key + "}";
}
sql = sql.Replace(parNameInstead, dbConnType.TreatParaName(par.Key));
}
}
}
}
return(sql);
}
/// <summary>
/// 把字段串转换成各数据库连接类型对应的形式,such as @Name,@Id,@Code
/// </summary>
/// <param name="Columns"></param>
/// <param name="connType"></param>
/// <returns></returns>
public static string GetParaStringViaColumns(this DbConnType connType, string[] Columns)
{
StringBuilder strbld = new StringBuilder(512);
for (int i = 0, count = Columns.Length; i < count; i++)
{
strbld.AppendFormat("{0},", connType.TreatParaName(Columns[i]));
}
return(strbld.ToString().TrimEnd(','));
}