/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "INSERT INTO ";
res += _table.ToSqlString(tp);
string cols = "";
string vals = "";
foreach (var key in _row.Keys)
{
if (cols != "")
{
cols += ",";
}
if (vals != "")
{
vals += ",";
}
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, key)) {
cols += field.ToSqlString(tp);
}
switch (tp)
{
case DatabaseTypes.MySQL:
vals += $"'{_row[key].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
vals += $"'{_row[key].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
res += $"({cols}) VALUES ({vals})";
return(res);
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "UPDATE ";
res += _table.ToSqlString(tp);
res += " SET ";
string cols = "";
string keyCol = "";
if (!Equals(_keyField, null))
{
keyCol = _keyField.ToString();
}
foreach (var key in _row.Keys)
{
if (key != keyCol)
{
if (cols != "")
{
cols += ",";
}
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, key)) {
cols += field.ToSqlString(tp);
}
//cols += $" = '{_row[key]}'";
switch (tp)
{
case DatabaseTypes.MySQL:
cols += $" = '{_row[key].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
cols += $" = '{_row[key].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
}
res += cols;
if (!keyCol.IsNone())
{
res += " WHERE ";
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, keyCol)) {
res += field.ToSqlString(tp);
switch (tp)
{
case DatabaseTypes.MySQL:
res += $" = '{_row[keyCol].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
res += $" = '{_row[keyCol].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
//res += $" = '{_row[keyCol]}'";
}
}
else
{
if (!Equals(_where, null))
{
res += " WHERE " + _where.ToSqlString(tp);
}
}
return(res);
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "SELECT";
// 字段列表字符串
string fieldsSql = "";
// 表列表字符串
string tablesSql = "";
// 筛选字符串
string whereSql = "";
// 排序列表字符串
string ordersSql = "";
// 组合列表字符串
string groupsSql = "";
// 生成字段列表字符串
if (_sqlFields == null)
{
if (_sqlTables.Length > 1)
{
throw new Exception("多表查询请指定返回字段");
}
fieldsSql = "*";
}
else
{
multiTable = _sqlTables.Length > 1;
for (int i = 0; i < _sqlFields.Length; i++)
{
var field = _sqlFields[i];
if (fieldsSql != "")
{
fieldsSql += ",";
}
fieldsSql += field.ToSqlString(tp, multiTable);
}
}
// 判断浮标情况
if (!Equals(_limitFeild, null))
{
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
res += $" TOP {_limitIndex} {_limitFeild.ToSqlString(tp, multiTable)}";
break;
default:
res += " " + fieldsSql;
break;
}
}
else
{
res += " " + fieldsSql;
}
// 生成表列表字符串
res += " FROM";
for (int i = 0; i < _sqlTables.Length; i++)
{
var table = _sqlTables[i];
if (tablesSql != "")
{
tablesSql += ",";
}
tablesSql += table.ToSqlTableString(tp);
}
res += " " + tablesSql;
// 生成筛选条件
if (!Equals(_where, null))
{
whereSql = _where.ToSqlString(tp, multiTable);
res += " WHERE " + whereSql;
}
// 生成组合条件
if (_group != null)
{
res += " GROUP BY ";
for (int i = 0; i < _group.Length; i++)
{
if (groupsSql != "")
{
groupsSql += ",";
}
groupsSql += _group[i].ToSqlString(tp, multiTable);
}
res += groupsSql;
}
// 生成排序条件
if (_order.Count > 0)
{
res += " ORDER BY ";
for (int i = 0; i < _order.Count; i++)
{
if (ordersSql != "")
{
ordersSql += ",";
}
ordersSql += _order[i].ToSqlString(tp, multiTable);
}
res += ordersSql;
}
// 游标模式
if (!Equals(_limitFeild, null))
{
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
//具有游标的情况下，进行微软产品的子查询构建
#region [=====具有游标的情况下，进行微软产品的子查询构建=====]
//子查询
string szSelect2 = res;
string szSelect1 = "SELECT ";
string szTab = "Auto_Table_" + _dbc.GetNewIndex();
//From查询构建
szSelect1 += "TOP " + (_limitIndex + _limitLength);
//输出字段
if (fieldsSql != "")
{
szSelect1 += " " + fieldsSql;
}
//添加表
if (tablesSql != "")
{
szSelect1 += " FROM " + tablesSql;
}
//添加条件
if (whereSql != "")
{
szSelect1 += " WHERE " + whereSql;
}
//添加聚合
if (groupsSql != "")
{
szSelect1 += " GROUP BY " + groupsSql;
}
//添加排序
if (ordersSql != "")
{
szSelect1 += " ORDER BY " + ordersSql;
}
//重建查询
res = "SELECT *";
//添加表
res += " FROM (" + szSelect1 + ") AS " + szTab;
//构架新的条件
res += " WHERE " + _limitFeild.ToSqlString(tp, false) + " NOT IN (" + szSelect2 + ")";
#endregion
break;
case DatabaseTypes.MySQL:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
//将支持limit的数据库组织limit语句
res += " LIMIT " + _limitIndex + "," + _limitLength;
break;
case DatabaseTypes.PostgreSQL:
//将支持limit的数据库组织limit语句
res += " LIMIT " + _limitLength + " OFFSET " + _limitIndex;
break;
default:
throw new Exception("尚未支持[" + tp + "]数据库类型");
}
}
return(res);
}
