private static string GetIdentitySeq(DataTableEntityMapping mapping, CreateSqlState state)
{
if (mapping.IdentityField == null)
{
throw new LightDataException(SR.NoIdentityField);
}
string seq;
var postgreIdentity = mapping.ExtentParams.GetParamValue("PostgreIdentitySeq");
if (!string.IsNullOrEmpty(postgreIdentity))
{
seq = postgreIdentity;
}
else
{
if (!state.TryGetAliasTableName(mapping, out var name))
{
name = mapping.TableName;
}
seq = $"{name}_{mapping.IdentityField.Name}_seq";
}
return(seq);
}
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state)
{
if (mapping.IdentityField != null)
{
return("select @@Identity;");
}
return(string.Empty);
}
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state)
{
if (mapping.IdentityField != null)
{
return($"select currval('\"{GetIdentitySeq(mapping, state)}\"');");
}
return(string.Empty);
}
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state)
{
if (mapping.IdentityField != null)
{
return("select last_insert_id();");
}
else
{
return(string.Empty);
}
}
public override string CreateIdentitySql(DataTableEntityMapping mapping, CreateSqlState state)
{
if (mapping.IdentityField != null)
{
return(string.Format("select currval('\"{0}\"');", GetIndentitySeq(mapping, state)));
}
else
{
return(string.Empty);
}
}
public override CommandData CreateTruncateTableCommand(DataTableEntityMapping mapping, CreateSqlState state)
{
CommandData data = base.CreateTruncateTableCommand(mapping, state);
if (mapping.IdentityField != null)
{
string restartSeq = string.Format("alter sequence \"{0}\" restart;", GetIndentitySeq(mapping, state));
data.CommandText += restartSeq;
}
return(data);
}
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh, bool updateIdentity, CreateSqlState state)
{
CommandData command = base.CreateBaseInsertCommand(mapping, entity, refresh, false, state);
if (updateIdentity && mapping.IdentityField != null)
{
string idensql = string.Format("returning {0}", CreateDataFieldSql(mapping.IdentityField.Name));
command.CommandText = command.CommandText + idensql;
command.IdentitySql = true;
}
return(command);
}
public override CommandData CreateTruncateTableCommand(DataTableEntityMapping mapping, CreateSqlState state)
{
var data = base.CreateTruncateTableCommand(mapping, state);
if (mapping.IdentityField != null)
{
var restartSeq = $"alter sequence \"{GetIdentitySeq(mapping, state)}\" restart;";
data.CommandText += restartSeq;
}
return(data);
}
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh,
bool updateIdentity, CreateSqlState state)
{
var command = base.CreateBaseInsertCommand(mapping, entity, refresh, false, state);
if (updateIdentity && mapping.IdentityField != null)
{
var idenSql = $"returning {CreateDataFieldSql(mapping.IdentityField.Name)}";
command.CommandText += idenSql;
command.IdentitySql = true;
}
return(command);
}
public override CommandData CreateBatchInsertCommand(DataTableEntityMapping mapping, IList entitys,
bool refresh, CreateSqlState state)
{
if (entitys == null || entitys.Count == 0)
{
throw new ArgumentNullException(nameof(entitys));
}
IList <DataFieldMapping> fields = mapping.CreateFieldList;
var insertLen = fields.Count;
if (insertLen == 0)
{
throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType));
}
string insertSql = null;
string cacheKey = null;
if (state.Seed == 0)
{
cacheKey = CommandCache.CreateKey(mapping, state);
if (_batchInsertCache.TryGetCommand(cacheKey, out var cache))
{
insertSql = cache;
}
}
if (insertSql == null)
{
var insertList = new string[insertLen];
for (var i = 0; i < insertLen; i++)
{
var field = fields[i];
insertList[i] = CreateDataFieldSql(field.Name);
}
var insert = string.Join(",", insertList);
insertSql = $"insert into {CreateDataTableMappingSql(mapping, state)}({insert})";
if (cacheKey != null)
{
_batchInsertCache.SetCommand(cacheKey, insertSql);
}
}
var totalSql = new StringBuilder();
totalSql.AppendFormat("{0}values", insertSql);
var cur = 0;
var end = entitys.Count;
foreach (var entity in entitys)
{
var valuesList = new string[insertLen];
for (var i = 0; i < insertLen; i++)
{
var field = fields[i];
var value = field.ToInsert(entity, refresh);
valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
var values = string.Join(",", valuesList);
totalSql.Append($"({values})");
cur++;
totalSql.Append(cur < end ? ',' : ';');
}
var command = new CommandData(totalSql.ToString());
return(command);
}
public override CommandData CreateBatchInsertWithIdentityCommand(DataTableEntityMapping mapping, IList entitys,
bool refresh, CreateSqlState state)
{
if (entitys == null || entitys.Count == 0)
{
throw new ArgumentNullException(nameof(entitys));
}
IList <DataFieldMapping> fields = mapping.CreateFieldList;
var insertLen = fields.Count;
if (insertLen == 0)
{
throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType));
}
if (!mapping.HasIdentity)
{
throw new LightDataException(string.Format(SR.NoIdentityField, mapping.ObjectType));
}
string insertSql = null;
string cacheKey = null;
if (state.Seed == 0)
{
cacheKey = CommandCache.CreateKey(mapping, state);
if (_batchInsertCache.TryGetCommand(cacheKey, out var cache))
{
insertSql = cache;
}
}
if (insertSql == null)
{
var insertList = new string[insertLen];
for (var i = 0; i < insertLen; i++)
{
var field = fields[i];
insertList[i] = CreateDataFieldSql(field.Name);
}
var insert = string.Join(",", insertList);
insertSql = $"insert into {CreateDataTableMappingSql(mapping, state)}({insert})";
if (cacheKey != null)
{
_batchInsertCache.SetCommand(cacheKey, insertSql);
}
}
var totalSql = new StringBuilder();
totalSql.Append("drop temporary table if exists `temptb`;create temporary table `temptb`(`id` int(11));");
foreach (var entity in entitys)
{
var valuesList = new string[insertLen];
for (var i = 0; i < insertLen; i++)
{
var field = fields[i];
var value = field.ToInsert(entity, refresh);
valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
var values = string.Join(",", valuesList);
totalSql.Append($"{insertSql}values({values});insert into `temptb`(`id`) select last_insert_id();");
}
totalSql.Append("select `id` from `temptb`;");
var command = new CommandData(totalSql.ToString());
return(command);
}
public override CommandData CreateBatchInsertWithIdentityCommand(DataTableEntityMapping mapping, IList entitys, bool refresh, CreateSqlState state)
{
if (entitys == null || entitys.Count == 0)
{
throw new ArgumentNullException(nameof(entitys));
}
int totalCount = entitys.Count;
IList <DataFieldMapping> fields = mapping.CreateFieldList;
int insertLen = fields.Count;
if (insertLen == 0)
{
throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType));
}
if (!mapping.HasIdentity)
{
throw new LightDataException(string.Format(SR.NoIdentityField, mapping.ObjectType));
}
string insertSql = null;
string cachekey = null;
if (state.Seed == 0)
{
cachekey = CommandCache.CreateKey(mapping, state);
if (_batchInsertCache.TryGetCommand(cachekey, out string cache))
{
insertSql = cache;
}
}
if (insertSql == null)
{
string[] insertList = new string[insertLen];
for (int i = 0; i < insertLen; i++)
{
DataFieldMapping field = fields[i];
insertList[i] = CreateDataFieldSql(field.Name);
}
string insert = string.Join(",", insertList);
insertSql = string.Format("insert into {0}({1})", CreateDataTableMappingSql(mapping, state), insert);
if (cachekey != null)
{
_batchInsertCache.SetCommand(cachekey, insertSql);
}
}
StringBuilder totalSql = new StringBuilder();
totalSql.AppendFormat("{0}values", insertSql);
int cur = 0;
int end = entitys.Count;
foreach (object entity in entitys)
{
string[] valuesList = new string[insertLen];
for (int i = 0; i < insertLen; i++)
{
DataFieldMapping field = fields[i];
object value = field.GetInsertData(entity, refresh);
valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
string values = string.Join(",", valuesList);
totalSql.AppendFormat("({0})", values);
cur++;
if (cur < end)
{
totalSql.Append(',');
}
else
{
totalSql.AppendFormat("returning {0} as id;", CreateDataFieldSql(mapping.IdentityField.Name));
}
}
CommandData command = new CommandData(totalSql.ToString());
return(command);
}
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh,
bool updateIdentity, CreateSqlState state)
{
string cacheKey = null;
var identity = updateIdentity && mapping.HasIdentity;
if (state.Seed == 0 && !state.UseDirectNull)
{
cacheKey = CommandCache.CreateKey(mapping, state);
if (identity)
{
cacheKey = string.Concat(cacheKey, "|id");
}
if (_baseInsertCache.TryGetCommand(cacheKey, out var cache))
{
var command1 = new CommandData(cache)
{
IdentitySql = identity
};
foreach (var field in mapping.CreateFieldList)
{
var value = field.ToInsert(entity, refresh);
state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
return(command1);
}
}
IList <DataFieldMapping> fields = mapping.CreateFieldList;
var insertLen = fields.Count;
if (insertLen == 0)
{
throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType));
}
var insertList = new string[insertLen];
var valuesList = new string[insertLen];
for (var i = 0; i < insertLen; i++)
{
var field = fields[i];
var value = field.ToInsert(entity, refresh);
insertList[i] = CreateDataFieldSql(field.Name);
valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
var insert = string.Join(",", insertList);
var values = string.Join(",", valuesList);
var sql = identity
? $"insert into {CreateDataTableMappingSql(mapping, state)}({insert}) output inserted.{CreateDataFieldSql(mapping.IdentityField.Name)} values({values})"
: $"insert into {CreateDataTableMappingSql(mapping, state)}({insert})values({values})";
var command = new CommandData(sql)
{
IdentitySql = identity
};
if (cacheKey != null)
{
_baseInsertCache.SetCommand(cacheKey, command.CommandText);
}
return(command);
}
public override IDbCommand[] CreateBulkInsertCommand(Array entitys, int batchCount)
{
if (entitys == null || entitys.Length == 0)
{
throw new ArgumentNullException("entitys");
}
if (batchCount <= 0)
{
batchCount = 10;
}
object tmpEntity = entitys.GetValue(0);
DataTableEntityMapping mapping = DataMapping.GetTableMapping(tmpEntity.GetType());
List <FieldMapping> fields = new List <FieldMapping> ();
int totalCount = entitys.Length;
fields.AddRange(mapping.GetFieldMappings());
if (mapping.IdentityField != null)
{
fields.Remove(mapping.IdentityField);
}
StringBuilder values = new StringBuilder();
StringBuilder insert = new StringBuilder();
List <DataParameter> paramList = GetDataParameters(fields, tmpEntity);
foreach (DataParameter dataParameter in paramList)
{
insert.AppendFormat("{0},", CreateDataFieldSql(dataParameter.ParameterName));
}
insert.Remove(insert.Length - 1, 1);
string insertsql = string.Format("insert into {0}({1})", CreateDataTableSql(mapping.TableName), insert);
int createCount = 0;
int totalCreateCount = 0;
IDbCommand command = _database.CreateCommand();
int paramCount = 0;
List <IDbCommand> commands = new List <IDbCommand> ();
foreach (object entity in entitys)
{
paramList = GetDataParameters(fields, entity);
StringBuilder value = new StringBuilder();
foreach (DataParameter dataParameter in paramList)
{
IDataParameter param = _database.CreateParameter("P" + paramCount, dataParameter.Value, dataParameter.DbType, dataParameter.Direction);
value.AppendFormat("{0},", param.ParameterName);
command.Parameters.Add(param);
paramCount++;
}
value.Remove(value.Length - 1, 1);
values.AppendFormat("({0}),", value);
createCount++;
totalCreateCount++;
if (createCount == batchCount || totalCreateCount == totalCount)
{
values.Remove(values.Length - 1, 1);
command.CommandText = string.Format("{0}values{1}", insertsql, values);
commands.Add(command);
if (totalCreateCount == totalCount)
{
break;
}
command = _database.CreateCommand();
createCount = 0;
paramCount = 0;
values = new StringBuilder();
}
}
return(commands.ToArray());
}
public override CommandData CreateBaseInsertCommand(DataTableEntityMapping mapping, object entity, bool refresh, bool updateIdentity, CreateSqlState state)
{
string cachekey = null;
bool identity = updateIdentity && mapping.HasIdentity;
if (state.Seed == 0 && !state.UseDirectNull)
{
cachekey = CommandCache.CreateKey(mapping, state);
if (identity)
{
cachekey = string.Concat(cachekey, "|id");
}
if (_baseInsertCache.TryGetCommand(cachekey, out string cache))
{
CommandData command1 = new CommandData(cache);
command1.IdentitySql = identity;
foreach (DataFieldMapping field in mapping.CreateFieldList)
{
object value = field.GetInsertData(entity, refresh);
state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
return(command1);
}
}
IList <DataFieldMapping> fields = mapping.CreateFieldList;
int insertLen = fields.Count;
if (insertLen == 0)
{
throw new LightDataException(string.Format(SR.NotContainNonIdentityKeyFields, mapping.ObjectType));
}
string[] insertList = new string[insertLen];
string[] valuesList = new string[insertLen];
for (int i = 0; i < insertLen; i++)
{
DataFieldMapping field = fields[i];
object value = field.GetInsertData(entity, refresh);
insertList[i] = CreateDataFieldSql(field.Name);
valuesList[i] = state.AddDataParameter(this, value, field.DBType, field.ObjectType);
}
string insert = string.Join(",", insertList);
string values = string.Join(",", valuesList);
string sql;
if (identity)
{
sql = string.Format("insert into {0}({1}) output inserted.{3} values({2})", CreateDataTableMappingSql(mapping, state), insert, values, CreateDataFieldSql(mapping.IdentityField.Name));
}
else
{
sql = string.Format("insert into {0}({1})values({2})", CreateDataTableMappingSql(mapping, state), insert, values);
}
CommandData command = new CommandData(sql);
command.IdentitySql = identity;
if (cachekey != null)
{
_baseInsertCache.SetCommand(cachekey, command.CommandText);
}
return(command);
}
