/// <summary>
/// 对象实例化
/// </summary>
/// <param name="dbc">数据库连接</param>
/// <param name="table">表对象</param>
/// <param name="row">数据行对象</param>
/// <param name="keyField">更新键字段</param>
public Update(Connection dbc, SqlUnits.Table table, Row row, SqlUnits.TableField keyField = null)
{
_dbc = dbc;
_table = table;
_row = row;
_keyField = keyField;
}
/// <summary>
/// 对象实例化
/// </summary>
/// <param name="dbc"></param>
/// <param name="sqlTables"></param>
public Select(Connection dbc, params ISqlTableStringable[] sqlTables)
{
_dbc = dbc;
_sqlTables = sqlTables;
_sqlFields = null;
_where = null;
_order = new List <SqlUnits.SelectOrder>();
_group = null;
_limitFeild = null;
_limitIndex = 0;
_limitLength = 0;
}
/// <summary>
/// 设置游标
/// </summary>
/// <param name="field"></param>
/// <param name="idx"></param>
/// <param name="len"></param>
/// <returns></returns>
public Select Limit(SqlUnits.TableField field, int idx, int len)
{
_limitFeild = field;
_limitIndex = idx;
if (_limitIndex < 0)
{
_limitIndex = 0;
}
_limitLength = len;
if (_limitLength < 0)
{
_limitLength = 0;
}
return(this);
}
/// <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);
}
