/// <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); }