private string GenerateUpdateSQL(IList <RdbColumn> lobColumns)
{
var sql = new StringWriter();
sql.Write("UPDATE ");
sql.AppendQuoteName(this);
sql.Write(" SET ");
bool comma = false;
var paramIndex = 0;
//先更新所有非 lob 字段。
for (int i = 0, c = _columns.Count; i < c; i++)
{
var column = _columns[i];
if (!column.Info.IsPrimaryKey && !column.IsLOB)
{
if (comma)
{
sql.Write(',');
}
else
{
comma = true;
}
sql.AppendQuote(this, column.Name).Write(" = {");
sql.Write(paramIndex++);
sql.Write('}');
}
}
//再更新所有 lob 字段。
if (lobColumns != null)
{
for (int i = 0, c = lobColumns.Count; i < c; i++)
{
if (comma)
{
sql.Write(',');
}
else
{
comma = true;
}
var column = lobColumns[i];
sql.AppendQuote(this, column.Name);
sql.Write(" = {");
sql.Write(paramIndex++);
sql.Write('}');
}
}
sql.Write(" WHERE ");
sql.AppendQuote(this, _pkColumn.Name);
sql.Write(" = {");
sql.Write(paramIndex);
sql.Write('}');
return(sql.ToString());
}
private string GenerateInsertSQL()
{
var sql = new StringWriter();
sql.Write("INSERT INTO ");
sql.AppendQuote(this, this._name).Write(" (");
var values = new StringBuilder();
bool comma = false;
var index = 0;
foreach (DbColumn column in _columns)
{
if (this.CanInsert(column))
{
if (comma)
{
sql.Write(',');
values.Append(',');
}
else
{
comma = true;
}
sql.AppendQuote(this, column.Name);
values.Append('{').Append(index++).Append('}');
}
}
sql.Write(") VALUES (");
sql.Write(values.ToString());
sql.Write(")");
return(sql.ToString());
}
/// <summary>
/// 生成 Insert 语句
/// </summary>
/// <param name="withIdentity">是否将 Identity 列也生成到 Insert 语句中。</param>
/// <returns></returns>
internal string GenerateInsertSQL(bool withIdentity)
{
var sql = new StringWriter();
sql.Write("INSERT INTO ");
sql.AppendQuote(this, this.Name).Write(" (");
var values = new StringBuilder();
var index = 0;
for (int i = 0, c = _columns.Count; i < c; i++)
{
var column = _columns[i];
if (column.ShouldInsert(withIdentity))
{
if (index > 0)
{
sql.Write(',');
values.Append(',');
}
sql.AppendQuote(this, column.Name);
values.Append('{').Append(index++).Append('}');
}
}
sql.Write(") VALUES (");
sql.Write(values.ToString());
sql.Write(")");
return(sql.ToString());
}
/// <summary>
/// 生成批量更新的Sql语句
/// </summary>
/// <param name="meta"></param>
/// <param name="entities">需要插入的实体类型集合</param>
/// <returns>返回拼接完成的、批量插入的Sql语句</returns>
private string GenerateBatchUpdateStatement(EntityBatch meta, IList <Entity> entities)
{
var dba = meta.DBA.RawAccesser;
var table = meta.Table;
var sql = new StringWriter();
sql.Write("REPLACE INTO ");
sql.AppendQuote(table, table.Name).Write("(");
var columns = table.Columns;
for (int i = 0, c = columns.Count; i < c; i++)
{
if (i != 0)
{
sql.Write(',');
}
sql.AppendQuote(table, columns[i].Name);
}
sql.Write(")VALUES");
this.GenerateValuesSql(sql, entities, table);
return(sql.ToString());
}
/// <summary>
/// 生成 Insert 语句
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private static string GenerateInsertSQL(RdbTable table)
{
//代码参考 RdbTable.GenerateInsertSQL() 方法。
var sql = new StringWriter();
sql.Write("INSERT INTO ");
sql.AppendQuote(table, table.Name).Write(" (");
var columns = table.Columns;
bool comma = false;
for (int i = 0, c = columns.Count; i < c; i++)
{
var column = columns[i];
if (column.CanInsert)
{
if (comma)
{
sql.Write(',');
}
else
{
comma = true;
}
sql.AppendQuote(table, column.Name);
}
}
sql.Write(") VALUES (");
comma = false;
for (int i = 0, c = columns.Count; i < c; i++)
{
var column = columns[i];
if (column.CanInsert)
{
if (comma)
{
sql.Write(',');
}
else
{
comma = true;
}
sql.Write(":");
sql.Write(column.Name);
}
}
sql.Write(")");
return(sql.ToString());
}
private string ReplaceLOBColumns(string sql)
{
//如果 sql 中编写了 LOBColumnsToken 这个符号，则表示需要进行列名替换。
var tokenIndex = sql.IndexOf(LOBColumnsToken);
if (tokenIndex < 0) return sql;
var res = new StringWriter();
var tableName = string.Empty;
//如果使用了 XXX.{*}，则表示指定了表的名称，这时需要在每个字段前使用这个表的名称。
var hasTablePrefix = sql[tokenIndex - 1] == '.';
if (hasTablePrefix)
{
var tablePrefixIndex = tokenIndex - 1;
while (true)
{
tablePrefixIndex--;
if (tablePrefixIndex < 0) throw new InvalidOperationException("sql 语句格式有误。");
var c = sql[tablePrefixIndex];
if (c == ' ')
{
tablePrefixIndex++;
tableName = sql.Substring(tablePrefixIndex, tokenIndex - 1 - tablePrefixIndex);
var before = sql.Substring(0, tablePrefixIndex);
res.Write(before);
break;
}
}
}
else
{
var before = sql.Substring(0, tokenIndex);
res.Write(before);
tableName = this.GetQuoteName();
}
//输出所有非 lob 列的列名。
bool comma = false;
for (int i = 0, c = _columns.Count; i < c; i++)
{
var column = _columns[i];
if (column.IsLOB) { continue; }
if (comma) { res.Write(','); }
else { comma = true; }
res.Write(tableName);
res.Write('.');
res.AppendQuote(this, column.Name);
}
var after = sql.Substring(tokenIndex + LOBColumnsToken.Length);
res.Write(after);
return res.ToString();
}
private string GenerateDeleteSQL()
{
var sql = new StringWriter();
sql.Write("DELETE FROM ");
sql.AppendQuoteName(this);
sql.Write(" WHERE ");
sql.AppendQuote(this, this.PKColumn.Name).Write(" = {0}");
return sql.ToString();
}
/// <summary>
/// 生成 Update 语句。
/// 注意，此方法不会更新 LOB 字段。
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private string GenerateUpdateSQL(RdbTable table)
{
//代码参考 RdbTable.GenerateUpdateSQL() 方法。
var sql = new StringWriter();
sql.Write("UPDATE ");
sql.AppendQuoteName(table);
sql.Write(" SET ");
bool comma = false;
var updateLOB = this.UpdateLOB;
var columns = table.Columns;
for (int i = 0, c = columns.Count; i < c; i++)
{
var column = columns[i];
if (!column.Info.IsPrimaryKey && (updateLOB || !column.IsLOB))
{
if (comma)
{
sql.Write(',');
}
else
{
comma = true;
}
sql.AppendQuote(table, column.Name).Write(" = :");
sql.Write(column.Name);
}
}
sql.Write(" WHERE ");
sql.AppendQuote(table, table.PKColumn.Name);
sql.Write(" = :");
sql.Write(table.PKColumn.Name);
return(sql.ToString());
}
/// <summary>
/// 子类重写此方法，来实现自己的 GetEntityValue 方法的数据层代码。
/// </summary>
/// <param name="entityId"></param>
/// <param name="property"></param>
/// <returns></returns>
public override LiteDataTable GetEntityValue(object entityId, string property)
{
var table = this.DbTable;
var idColumn = table.PKColumn.Name;
var column = table.Translate(property);
var sql = new StringWriter();
sql.Write("SELECT ");
sql.AppendQuote(table, column);
sql.Write(" FROM ");
sql.AppendQuoteName(table);
sql.Write(" WHERE ");
sql.AppendQuote(table, idColumn);
sql.Write(" = {0}");
return(this.QueryTable(new TableQueryArgs
{
FormattedSql = sql.ToString(),
Parameters = new object[] { entityId },
}));
}
internal string GenerateInsertSQL()
{
var sql = new StringWriter();
sql.Write("INSERT INTO ");
sql.AppendQuote(this, this.Name).Write(" (");
var values = new StringBuilder();
bool comma = false;
var index = 0;
for (int i = 0, c = _columns.Count; i < c; i++)
{
var column = _columns[i];
if (column.CanInsert)
{
if (comma)
{
sql.Write(',');
values.Append(',');
}
else
{
comma = true;
}
sql.AppendQuote(this, column.Name);
values.Append('{').Append(index++).Append('}');
}
}
sql.Write(") VALUES (");
sql.Write(values.ToString());
sql.Write(")");
return(sql.ToString());
}
/// <summary>
/// 生成批量更新的Sql语句
/// </summary>
/// <param name="meta"></param>
/// <param name="entities">需要插入的实体类型集合</param>
/// <returns>返回拼接完成的、批量插入的Sql语句</returns>
private static string GenerateBatchUpdateStatement(EntityBatch meta, IList <Entity> entities)
{
var dba = meta.DBA.RawAccesser;
var table = meta.Table;
var sql = new StringWriter();
sql.Write("REPLACE INTO ");
sql.AppendQuote(table, table.Name).Write("(");
var columns = table.Columns;
for (int i = 0, c = columns.Count; i < c; i++)
{
var column = columns[i];
if (i != 0)
{
sql.Write(',');
}
sql.AppendQuote(table, column.Name);
}
sql.Write(")VALUES");
for (int e = 0; e < entities.Count; e++)
{
sql.Write("(");
for (int i = 0, c = table.Columns.Count; i < c; i++)
{
var column = columns[i];
if (i != 0)
{
sql.Write(',');
}
var parameter = GetDataParameterObject(entities[e], column, dba);
if (parameter.DbType == DbType.Int16 || parameter.DbType == DbType.Int32 || parameter.DbType == DbType.Int64 || parameter.DbType == DbType.Decimal || parameter.DbType == DbType.Double || parameter.DbType == DbType.Single || parameter.DbType == DbType.UInt16 || parameter.DbType == DbType.UInt32 || parameter.DbType == DbType.UInt64)
{
if (string.IsNullOrEmpty(parameter.Value.ToString()))
{
sql.Write("null");
}
else
{
sql.Write(parameter.Value);
}
}
else if (parameter.DbType == DbType.Binary)
{
byte[] values = parameter.Value as byte[];
if (values == null || values.Length == 0)
{
sql.Write("null");
}
else
{
string newValue = Convert.ToBase64String(values);
sql.Write("'" + newValue + "'");
}
}
else if (parameter.DbType == DbType.String)
{
if (string.Compare(parameter.Value.ToString(), "false", true) == 0 || string.Compare(parameter.Value.ToString(), "true", true) == 0)
{
if (string.Compare(parameter.Value.ToString(), "false", true) == 0)
{
parameter.Value = 0;
}
else
{
parameter.Value = 1;
}
sql.Write(parameter.Value);
}
else
{
sql.Write("'" + parameter.Value + "'");
}
}
else
{
sql.Write("'" + parameter.Value + "'");
}
}
if (e == entities.Count - 1)
{
sql.Write(")");
}
else
{
sql.Write("),");
}
}
return(sql.ToString());
}
