private DataTable ToDataTable(RdbTable meta, IList <Entity> list, bool isUpdating = false)
{
//创建表格式
var table = new DataTable();
var columns = meta.Columns;
foreach (var column in columns)
{
var dataType = TypeHelper.IgnoreNullable(column.Info.DataType);
table.Columns.Add(new DataColumn(column.Name, dataType));
}
//从实体中读取数据
var rows = table.Rows;
for (int i = 0, c = list.Count; i < c; i++)
{
var entity = list[i];
var row = table.NewRow();
for (int j = 0, jc = columns.Count; j < jc; j++)
{
var column = columns[j];
row[j] = column.ReadParameterValue(entity);
}
rows.Add(row);
if (isUpdating)
{
row.AcceptChanges();
row.SetModified();
}
}
return(table);
}
/// <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());
}
/// <summary>
/// 为指定的实体列表构造一个批处理命令。
/// </summary>
/// <param name="entityList"></param>
/// <param name="db"></param>
/// <param name="table"></param>
public BatchInsert(IList <Entity> entityList, SqlConnection db, RdbTable table)
{
if (entityList.Count < 1)
{
throw new ArgumentOutOfRangeException();
}
this._table = table;
this._entityList = entityList;
this._db = db;
}
private void GenerateValuesSql(StringWriter sql, IList <Entity> entities, RdbTable table)
{
var columns = table.Columns;
for (int e = 0, ec = entities.Count; e < ec; e++)
{
var entity = entities[e];
if (e > 0)
{
sql.Write(",");
}
sql.Write("(");
for (int i = 0, c = columns.Count; i < c; i++)
{
var column = columns[i];
if (i != 0)
{
sql.Write(',');
}
//获取数据类型及值。
var dbType = column.Info.DbType;
var value = column.ReadDbParameterValue(entity);
//处理空值
if (value == DBNull.Value || value == null)
{
sql.Write("null");
continue;
}
//按类型写入值。
if (dbType == DbType.Binary)
{
var bytes = value as byte[];
string newValue = Convert.ToBase64String(bytes);
sql.Write("'" + newValue + "'");
}
else if (dbType == DbType.String || dbType == DbType.DateTime)
{
sql.Write("'" + value + "'");
}
else
{
sql.Write(value);
}
}
sql.Write(")");
}
}
/// <summary>
/// 获取指定大小批量的连续的 Id 号。返回第一个 Id 号的值。
/// </summary>
/// <param name="dba">数据操作对象</param>
/// <param name="table">数据表对象</param>
/// <returns></returns>
private static long GetBatchIDs(IDbAccesser dba, RdbTable table)
{
var tableName = table.Name;
lock (_identityLock)
{
var currentValue = Convert.ToInt64(dba.QueryValue(string.Format("SELECT IFNULL(MAX(id),0) FROM {0}", tableName)));
return(currentValue);
}
throw new InvalidOperationException("生成 Id 时，发生未知错误。");
}
/// <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>
/// 获取指定大小批量的连续的 Id 号。返回第一个 Id 号的值。
/// </summary>
/// <param name="dba">The dba.</param>
/// <param name="table">The table.</param>
/// <param name="size">需要连续的多少个 Id 号。</param>
/// <returns></returns>
private static long GetBatchIDs(IDbAccesser dba, RdbTable table, int size)
{
/*********************** 代码块解释 *********************************
* 算法解释：
* 1.先查出当前最大的 ID 值；
* 2.计算出所需要使用的区间的起始、终止值 [start,end]；
* 3.然后设置 IDENTITY 的种子值为终止值；
* 4.由于上两步 SQL 执行过程中有可能其它线程使用了这个 IDENTITY 而造成 [start,end] 中的值被使用，
* 所以需要对表中的数据进行检测，如果没有被使用，才把这些 ID 值返回。
* 如果被使用了，则上述过程重来一次即可。
*
* IDENTITY 的操作方法：http://www.cnblogs.com/gaizai/archive/2013/04/23/3038318.html
**********************************************************************/
var tableName = table.Name;
lock (_identityLock)
{
while (true)
{
var currentValue = Convert.ToInt64(dba.QueryValue(string.Format("SELECT IDENT_CURRENT('{0}')", tableName)));
var start = currentValue + 1;
var end = start + size - 1;
dba.ExecuteText(string.Format("DBCC CHECKIDENT('{0}', RESEED, {1})", tableName, end));
var concurrency = dba.QueryValue(string.Format(
"SELECT 1 WHERE EXISTS (SELECT 1 FROM {0} WHERE {1} >= {2} AND {1} <= {3})",
tableName, table.IdentityColumn.Name, start, end
));
if (concurrency == null)
{
return(start);
}
}
}
throw new InvalidOperationException("生成 Id 时，发生未知错误。");
}
/// <summary>
/// 设置DataTable到数据库中表的映射
/// </summary>
/// <param name="mappings">The mappings.</param>
/// <param name="table">The table.</param>
private void SetMappings(SqlBulkCopyColumnMappingCollection mappings, RdbTable table)
{
foreach (var column in table.Columns)
{
mappings.Add(column.Name, column.Name);
}
//暂留：通过查询的真实列名来实现 Mapping。
//var sql = string.Format("SELECT TOP 0 * FROM [{0}]", _table.Name);
//var dbNames = new List<string>();
//using (var reader = _meta.DBA.QueryDataReader(sql))
//{
// for (int i = 0, c = reader.FieldCount; i < c; i++)
// {
// var name = reader.GetName(i);
// dbNames.Add(name);
// }
//}
//foreach (var column in _table.Columns)
//{
// var correspondingDbName = dbNames.First(c => string.Compare(c, column.Name, true) == 0);
// mappings.Add(column.Name, correspondingDbName);
//}
}
/// <summary>
/// 构造函数 初始化表和 持久列信息
/// </summary>
/// <param name="table">表对象</param>
/// <param name="columnInfo">持久列对象</param>
public MySqlColumn(RdbTable table, IPersistanceColumnInfo columnInfo) : base(table, columnInfo)
{
}
internal OracleColumn(RdbTable table, IPersistanceColumnInfo columnInfo) : base(table, columnInfo)
{
}
internal OracleColumn(RdbTable table, IPersistanceColumnInfo columnInfo)
: base(table, columnInfo)
{
}
internal OracleColumn(RdbTable table, IRdbColumnInfo columnInfo) : base(table, columnInfo)
{
}
/// <summary>
/// 构造函数 初始化表和 持久列信息
/// </summary>
/// <param name="table">表对象</param>
/// <param name="columnInfo">持久列对象</param>
public MySqlColumn(RdbTable table, IRdbColumnInfo columnInfo) : base(table, columnInfo)
{
}
