/// <summary>
/// Logic used to perform bulk inserts with SqlServer's BulkCopy
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="sqlSyntaxProvider"></param>
/// <param name="pd"></param>
/// <param name="collection"></param>
/// <returns></returns>
internal static int BulkInsertRecordsSqlServer <T>(Database db, SqlServerSyntaxProvider sqlSyntaxProvider,
Database.PocoData pd, IEnumerable <T> collection)
{
//NOTE: We need to use the original db.Connection here to create the command, but we need to pass in the typed
// connection below to the SqlBulkCopy
using (var cmd = db.CreateCommand(db.Connection, string.Empty))
{
using (var copy = new SqlBulkCopy(
GetTypedConnection <SqlConnection>(db.Connection),
SqlBulkCopyOptions.Default,
GetTypedTransaction <SqlTransaction>(cmd.Transaction))
{
BulkCopyTimeout = 10000,
DestinationTableName = pd.TableInfo.TableName
})
{
//var cols = pd.Columns.Where(x => IncludeColumn(pd, x)).Select(x => x.Value).ToArray();
using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(collection, pd, sqlSyntaxProvider))
{
copy.WriteToServer(bulkReader);
return(bulkReader.RecordsAffected);
}
}
}
}
protected virtual string VisitMemberAccess(MemberExpression m)
{
if (m.Expression != null &&
m.Expression.NodeType == ExpressionType.Parameter &&
m.Expression.Type == typeof(T))
{
string field = GetFieldName(pd, m.Member.Name);
return(field);
}
if (m.Expression != null && m.Expression.NodeType != ExpressionType.Constant)
{
Database.Mapper = new ModelDtoMapper();
var def = new Database.PocoData(m.Expression.Type);
string field = GetFieldName(def, m.Member.Name);
return(field);
}
var member = Expression.Convert(m, typeof(object));
var lambda = Expression.Lambda <Func <object> >(member);
var getter = lambda.Compile();
object o = getter();
return(GetQuotedValue(o, o != null ? o.GetType() : null));
}
public PocoDataDataReader(
IEnumerable <T> dataSource,
Database.PocoData pd,
MicrosoftSqlSyntaxProviderBase <TSyntax> sqlSyntaxProvider)
{
if (dataSource == null)
{
throw new ArgumentNullException("dataSource");
}
if (sqlSyntaxProvider == null)
{
throw new ArgumentNullException("sqlSyntaxProvider");
}
_tableDefinition = DefinitionFactory.GetTableDefinition(sqlSyntaxProvider, pd.type);
if (_tableDefinition == null)
{
throw new InvalidOperationException("No table definition found for type " + pd.type);
}
_readerColumns = pd.Columns.Select(x => x.Value).ToArray();
_sqlSyntaxProvider = sqlSyntaxProvider;
_enumerator = dataSource.GetEnumerator();
_columnDefinitions = _tableDefinition.Columns.ToArray();
}
protected virtual string GetFieldName(Database.PocoData pocoData, string name)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.PropertyInfo.Name == name);
return(string.Format("{0}.{1}",
SqlSyntaxContext.SqlSyntaxProvider.GetQuotedTableName(pocoData.TableInfo.TableName),
SqlSyntaxContext.SqlSyntaxProvider.GetQuotedColumnName(column.Value.ColumnName)));
}
/// <summary>
/// A filter used below a few times to get all columns except result cols and not the primary key if it is auto-incremental
/// </summary>
/// <param name="data"></param>
/// <param name="column"></param>
/// <returns></returns>
private static bool IncludeColumn(Database.PocoData data, KeyValuePair <string, Database.PocoColumn> column)
{
if (column.Value.ResultColumn)
{
return(false);
}
if (data.TableInfo.AutoIncrement && column.Key == data.TableInfo.PrimaryKey)
{
return(false);
}
return(true);
}
/// <summary>
/// Logic used to perform bulk inserts with SqlCe's TableDirect
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="pd"></param>
/// <param name="collection"></param>
/// <returns></returns>
internal static int BulkInsertRecordsSqlCe <T>(Database db,
Database.PocoData pd,
IEnumerable <T> collection)
{
var cols = pd.Columns.ToArray();
using (var cmd = db.CreateCommand(db.Connection, string.Empty))
{
cmd.CommandText = pd.TableInfo.TableName;
cmd.CommandType = CommandType.TableDirect;
//cmd.Transaction = GetTypedTransaction<SqlCeTransaction>(db.Connection.);
//get the real command
using (var sqlCeCommand = GetTypedCommand <SqlCeCommand>(cmd))
{
// This seems to cause problems, I think this is primarily used for retrieval, not
// inserting. see: https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.indexname%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396
//sqlCeCommand.IndexName = pd.TableInfo.PrimaryKey;
var count = 0;
using (var rs = sqlCeCommand.ExecuteResultSet(ResultSetOptions.Updatable))
{
var rec = rs.CreateRecord();
foreach (var item in collection)
{
for (var i = 0; i < cols.Length; i++)
{
//skip the index if this shouldn't be included (i.e. PK)
if (IncludeColumn(pd, cols[i]))
{
var val = cols[i].Value.GetValue(item);
rec.SetValue(i, val);
}
}
rs.Insert(rec);
count++;
}
}
return(count);
}
}
}
public System.Collections.Generic.IEnumerable <TEntity> GetAll(string orderBy)
{
System.Collections.Generic.IEnumerable <object> enumerable = null;
string text = null;
if (Repository <TEntity> .RealTimeCacheHelper.EnableCache)
{
text = Repository <TEntity> .RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.GlobalVersion);
if (!string.IsNullOrEmpty(orderBy))
{
text = text + "SB-" + orderBy;
}
enumerable = this.cacheService.Get <System.Collections.Generic.IEnumerable <object> >(text);
}
if (enumerable == null)
{
Database.PocoData pocoData = PetaPoco.Database.PocoData.ForType(typeof(TEntity));
Sql sql = Sql.Builder.Select(new object[]
{
pocoData.TableInfo.PrimaryKey
}).From(new object[]
{
pocoData.TableInfo.TableName
});
if (!string.IsNullOrEmpty(orderBy))
{
sql.OrderBy(new object[]
{
orderBy
});
}
enumerable = this.CreateDAO().FetchFirstColumn(sql);
if (Repository <TEntity> .RealTimeCacheHelper.EnableCache)
{
this.cacheService.Add(text, enumerable, Repository <TEntity> .RealTimeCacheHelper.CachingExpirationType);
}
}
return(this.PopulateEntitiesByEntityIds <object>(enumerable));
}
public virtual void Update(TEntity entity)
{
PetaPocoDatabase petaPocoDatabase = this.CreateDAO();
if (entity is ISerializableProperties)
{
ISerializableProperties serializableProperties = entity as ISerializableProperties;
if (serializableProperties != null)
{
serializableProperties.Serialize();
}
}
int num;
if (Repository <TEntity> .RealTimeCacheHelper.PropertyNameOfBody != null && Repository <TEntity> .RealTimeCacheHelper.PropertyNameOfBody.GetValue(entity, null) == null)
{
Database.PocoData pocoData = PetaPoco.Database.PocoData.ForType(typeof(TEntity));
System.Collections.Generic.List <string> list = new System.Collections.Generic.List <string>();
foreach (System.Collections.Generic.KeyValuePair <string, Database.PocoColumn> current in pocoData.Columns)
{
if (string.Compare(current.Key, pocoData.TableInfo.PrimaryKey, true) != 0 && (SqlBehaviorFlags.Update & current.Value.SqlBehavior) != (SqlBehaviorFlags)0 && string.Compare(current.Key, Repository <TEntity> .RealTimeCacheHelper.PropertyNameOfBody.Name, true) != 0 && !current.Value.ResultColumn)
{
list.Add(current.Key);
}
}
num = petaPocoDatabase.Update(entity, list);
}
else
{
num = petaPocoDatabase.Update(entity);
}
if (num > 0)
{
this.OnUpdated(entity);
}
}
/// <summary>
/// Bulk insert records with Sql BulkCopy or TableDirect or whatever sql platform specific bulk insert records should be used
/// </summary>
/// <param name="db"></param>
/// <param name="syntaxProvider"></param>
/// <param name="pd"></param>
/// <param name="collection"></param>
/// <param name="processed">The number of records inserted</param>
private static bool NativeSqlPlatformBulkInsertRecords <T>(Database db, ISqlSyntaxProvider syntaxProvider, Database.PocoData pd, IEnumerable <T> collection, out int processed)
{
var dbConnection = db.Connection;
//unwrap the profiled connection if there is one
var profiledConnection = dbConnection as ProfiledDbConnection;
if (profiledConnection != null)
{
dbConnection = profiledConnection.InnerConnection;
}
//check if it's SQL or SqlCe
var sqlConnection = dbConnection as SqlConnection;
if (sqlConnection != null)
{
processed = BulkInsertRecordsSqlServer(db, (SqlServerSyntaxProvider)syntaxProvider, pd, collection);
return(true);
}
var sqlCeConnection = dbConnection as SqlCeConnection;
if (sqlCeConnection != null)
{
processed = BulkInsertRecordsSqlCe(db, pd, collection);
return(true);
}
//could not use the SQL server's specific bulk insert operations
processed = 0;
return(false);
}
/// <summary>
/// Creates a bulk insert command
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="collection"></param>
/// <param name="sql"></param>
/// <param name="pd"></param>
/// <returns>Sql commands with populated command parameters required to execute the sql statement</returns>
/// <remarks>
/// The limits for number of parameters are 2100 (in sql server, I think there's many more allowed in mysql). So
/// we need to detect that many params and split somehow.
/// For some reason the 2100 limit is not actually allowed even though the exception from sql server mentions 2100 as a max, perhaps it is 2099
/// that is max. I've reduced it to 2000 anyways.
/// </remarks>
internal static IDbCommand[] GenerateBulkInsertCommand <T>(
this Database db,
Database.PocoData pd,
IEnumerable <T> collection,
out string[] sql)
{
if (db == null)
{
throw new ArgumentNullException("db");
}
if (db.Connection == null)
{
throw new ArgumentException("db.Connection is null.");
}
var tableName = db.EscapeTableName(pd.TableInfo.TableName);
//get all columns to include and format for sql
var cols = string.Join(", ",
pd.Columns
.Where(c => IncludeColumn(pd, c))
.Select(c => tableName + "." + db.EscapeSqlIdentifier(c.Key)).ToArray());
var itemArray = collection.ToArray();
//calculate number of parameters per item
var paramsPerItem = pd.Columns.Count(i => IncludeColumn(pd, i));
//Example calc:
// Given: we have 4168 items in the itemArray, each item contains 8 command parameters (values to be inserterted)
// 2100 / 8 = 262.5
// Math.Floor(2100 / 8) = 262 items per trans
// 4168 / 262 = 15.908... = there will be 16 trans in total
//all items will be included if we have disabled db parameters
var itemsPerTrans = Math.Floor(2000.00 / paramsPerItem);
//there will only be one transaction if we have disabled db parameters
var numTrans = Math.Ceiling(itemArray.Length / itemsPerTrans);
var sqlQueries = new List <string>();
var commands = new List <IDbCommand>();
for (var tIndex = 0; tIndex < numTrans; tIndex++)
{
var itemsForTrans = itemArray
.Skip(tIndex * (int)itemsPerTrans)
.Take((int)itemsPerTrans);
var cmd = db.CreateCommand(db.Connection, string.Empty);
var pocoValues = new List <string>();
var index = 0;
foreach (var poco in itemsForTrans)
{
var values = new List <string>();
//get all columns except result cols and not the primary key if it is auto-incremental
foreach (var i in pd.Columns.Where(x => IncludeColumn(pd, x)))
{
db.AddParam(cmd, i.Value.GetValue(poco), "@");
values.Add(string.Format("{0}{1}", "@", index++));
}
pocoValues.Add("(" + string.Join(",", values.ToArray()) + ")");
}
var sqlResult = string.Format("INSERT INTO {0} ({1}) VALUES {2}", tableName, cols, string.Join(", ", pocoValues));
sqlQueries.Add(sqlResult);
commands.Add(cmd);
}
sql = sqlQueries.ToArray();
return(commands.ToArray());
}
public Snapshot(T original)
{
memberWiseClone = Clone(original);
trackedObject = original;
pocoData = Database.PocoData.ForType(typeof(T));
}
protected virtual string GetFieldName(Database.PocoData pocoData, string name)
{
var column = pocoData.Columns.FirstOrDefault(x => x.Value.PropertyInfo.Name == name);
return(column.Value.ColumnName);
}
public ExpressionHelper()
{
Database.Mapper = new ModelDtoMapper();
pd = new Database.PocoData(typeof(T));
}
public PocoToSqlExpressionHelper()
{
pd = new Database.PocoData(typeof(T));
}
public PocoToSqlExpressionVisitor(ISqlSyntaxProvider syntaxProvider)
: base(syntaxProvider)
{
_pd = new Database.PocoData(typeof(T));
}
/// <summary>
/// 删除记录
/// </summary>
/// <param name="sql">SQL条件语句，如："Where id=12"</param>
/// <returns></returns>
public int Delete(Sql sql)
{
Database.PocoData pd = Database.PocoData.ForType(typeof(T));
return
(Execute(new Sql(string.Format("DELETE FROM {0} ", EscapeTableName(pd.TableInfo.TableName))).Append(sql)));
}
