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