public static string GetFindByIdSql(TableInfo tableInfo)
{
StringBuilder sbColumns = new StringBuilder();
if (tableInfo.Columns.ContainsKey(tableInfo.Id.Key))
{
tableInfo.Columns[tableInfo.Id.Key] = tableInfo.Id.Value;
}
else
{
tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);
}
foreach (String key in tableInfo.Columns.Keys)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append(",");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
string strSql = "SELECT {0} FROM {1} WHERE {2} = " + AdoHelper.DbParmChar + "{2}";
strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName, tableInfo.Id.Key);
return(strSql);
}
public static string GetUpdateSql(TableInfo tableInfo)
{
StringBuilder sbBody = new StringBuilder();
foreach (String key in tableInfo.Columns.Keys)
{
Object value = tableInfo.Columns[key];
if (!string.IsNullOrEmpty(key.Trim()) && value != null)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbBody.Append(nKey).Append("=").Append(AdoHelper.DbParmChar + key).Append(",");
}
}
if (sbBody.Length > 0)
{
sbBody.Remove(sbBody.ToString().Length - 1, 1);
}
//tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);
string strSql = "update {0} set {1} where {2} =" + AdoHelper.DbParmChar + tableInfo.Id.Key;
strSql = string.Format(strSql, tableInfo.TableName, sbBody.ToString(), tableInfo.Id.Key);
return(strSql);
}
public static string GetFindSql(TableInfo tableInfo, DbCondition condition)
{
StringBuilder sbColumns = new StringBuilder();
tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);
foreach (String key in tableInfo.Columns.Keys)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append(",");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
string strSql = String.Empty;
if (String.IsNullOrEmpty(condition.queryString))
{
strSql = "SELECT {0} FROM {1}";
strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName);
strSql += condition.ToString();
}
else
{
strSql = condition.ToString();
}
strSql = strSql.ToUpper();
return(strSql);
}
public static string GetFindAllSql(TableInfo tableInfo)
{
StringBuilder sbColumns = new StringBuilder();
// tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);
foreach (String key in tableInfo.Id.Keys)
{
tableInfo.Columns.Put(key, tableInfo.Id[key]);
}
foreach (String key in tableInfo.Columns.Keys)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append(",");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
string strSql = "SELECT {0} FROM {1}";
strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName);
return(strSql);
}
public static string GetInsertSql(TableInfo tableInfo)
{
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
if (tableInfo.Strategy != GenerationType.INDENTITY)
{
foreach (String key in tableInfo.Id.Keys)
{
if (tableInfo.Strategy == GenerationType.GUID && tableInfo.Id[key] == null)
{
tableInfo.Id[key] = Guid.NewGuid().ToString();
}
if (tableInfo.Id[key] != null)
{
tableInfo.Columns.Put(key, tableInfo.Id[key]);
}
}
}
foreach (String key in tableInfo.Columns.Keys)
{
Object value = tableInfo.Columns[key];
if (!string.IsNullOrEmpty(key.Trim()) && value != null)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append(",");
sbValues.Append(AdoHelper.DbParmChar).Append(key).Append(",");
}
}
if (sbColumns.Length > 0 && sbValues.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
sbValues.Remove(sbValues.ToString().Length - 1, 1);
}
string strSql = "INSERT INTO {0}({1}) VALUES({2})";
strSql = string.Format(strSql, tableInfo.TableName, sbColumns.ToString(), sbValues.ToString());
if (!tableInfo.NoAutomaticKey)
{
if (AdoHelper.DbType == DatabaseType.SQLSERVER || AdoHelper.DbType == DatabaseType.MYSQL)
{
string autoSql = EntityHelper.GetAutoSql();
strSql = strSql + autoSql;
}
}
return(strSql);
}
public static string GetFindByIdSql(TableInfo tableInfo)
{
StringBuilder sbColumns = new StringBuilder();
// if (tableInfo.Columns.ContainsKey(tableInfo.Id.Key))
// tableInfo.Columns[tableInfo.Id.Key] = tableInfo.Id.Value;
// else
// tableInfo.Columns.Put(tableInfo.Id.Key, tableInfo.Id.Value);
int idx = 0;
string where = "";
foreach (String key in tableInfo.Id.Keys)
{
if (tableInfo.Columns.ContainsKey(key))
{
tableInfo.Columns[key] = tableInfo.Id[key];
}
else
{
tableInfo.Columns.Put(key, tableInfo.Id[key]);
}
where += string.Format("{0}={1}{2}", key, AdoHelper.DbParmChar, key);
if (idx++ < (tableInfo.Id.Count - 1))
{
where += " and ";
}
}
foreach (String key in tableInfo.Columns.Keys)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append(",");
}
if (sbColumns.Length > 0)
{
sbColumns.Remove(sbColumns.ToString().Length - 1, 1);
}
// string strSql = "SELECT {0} FROM {1} WHERE {2} = " + AdoHelper.DbParmChar + "{2}";
// strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName, tableInfo.Id.Key);
string strSql = "SELECT {0} FROM {1} WHERE {2}";
strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName, where);
return(strSql);
}
public static string GetFindCountSql(TableInfo tableInfo)
{
StringBuilder sbColumns = new StringBuilder();
string strSql = "SELECT COUNT(0) FROM {1} ";
strSql = string.Format(strSql, sbColumns.ToString(), tableInfo.TableName);
foreach (String key in tableInfo.Columns.Keys)
{
string nKey = DbKeywords.FormatColumnName(key.Trim());
sbColumns.Append(nKey).Append("=").Append(AdoHelper.DbParmChar).Append(key);
}
if (sbColumns.Length > 0)
{
strSql += " WHERE " + sbColumns.ToString();
}
return(strSql);
}
