/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.Menus object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.Menus> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblMenus"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("id", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("subitem", typeof(System.Int32)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("parent", typeof(System.Int32)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("menukey", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("prompt", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("type", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("procerdue", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("imageidx", typeof(System.Int32)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("password", typeof(System.Boolean)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("basicright", typeof(System.Int32)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("depcode", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("skip", typeof(System.Boolean)); col11.AllowDBNull = true; bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("subitem", "subitem"); bulkCopy.ColumnMappings.Add("parent", "parent"); bulkCopy.ColumnMappings.Add("menukey", "menukey"); bulkCopy.ColumnMappings.Add("prompt", "prompt"); bulkCopy.ColumnMappings.Add("type", "type"); bulkCopy.ColumnMappings.Add("procerdue", "procerdue"); bulkCopy.ColumnMappings.Add("imageidx", "imageidx"); bulkCopy.ColumnMappings.Add("password", "password"); bulkCopy.ColumnMappings.Add("basicright", "basicright"); bulkCopy.ColumnMappings.Add("depcode", "depcode"); bulkCopy.ColumnMappings.Add("skip", "skip"); foreach (LibraryManagement.Domain.Menus entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["id"] = entity.Id; row["subitem"] = entity.Subitem.HasValue ? (object)entity.Subitem : System.DBNull.Value; row["parent"] = entity.Parent.HasValue ? (object)entity.Parent : System.DBNull.Value; row["menukey"] = entity.Menukey; row["prompt"] = entity.Prompt; row["type"] = entity.Type; row["procerdue"] = entity.Procerdue; row["imageidx"] = entity.Imageidx.HasValue ? (object)entity.Imageidx : System.DBNull.Value; row["password"] = entity.Password.HasValue ? (object)entity.Password : System.DBNull.Value; row["basicright"] = entity.Basicright.HasValue ? (object)entity.Basicright : System.DBNull.Value; row["depcode"] = entity.Depcode; row["skip"] = entity.Skip.HasValue ? (object)entity.Skip : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.Menus entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the ClinicDoctor.Entities.Appointment object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<ClinicDoctor.Entities.Appointment> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Appointment"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CustomerId", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("CustomerName", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("ContentId", typeof(System.Int64)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("ContentTitle", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("DoctorUsername", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("DoctorShortName", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("DoctorEmail", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("RoomId", typeof(System.Int64)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("RoomTitle", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("NurseUsername", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("NurseShortName", typeof(System.String)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("StatusId", typeof(System.Int64)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("StatusTitle", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("Note", typeof(System.String)); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("StartTime", typeof(System.DateTime)); col15.AllowDBNull = true; DataColumn col16 = dataTable.Columns.Add("EndTime", typeof(System.DateTime)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("ColorCode", typeof(System.String)); col17.AllowDBNull = false; DataColumn col18 = dataTable.Columns.Add("IsComplete", typeof(System.Boolean)); col18.AllowDBNull = false; DataColumn col19 = dataTable.Columns.Add("IsDisabled", typeof(System.Boolean)); col19.AllowDBNull = false; DataColumn col20 = dataTable.Columns.Add("CreateUser", typeof(System.String)); col20.AllowDBNull = true; DataColumn col21 = dataTable.Columns.Add("CreateDate", typeof(System.DateTime)); col21.AllowDBNull = false; DataColumn col22 = dataTable.Columns.Add("UpdateUser", typeof(System.String)); col22.AllowDBNull = true; DataColumn col23 = dataTable.Columns.Add("UpdateDate", typeof(System.DateTime)); col23.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("CustomerId", "CustomerId"); bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName"); bulkCopy.ColumnMappings.Add("ContentId", "ContentId"); bulkCopy.ColumnMappings.Add("ContentTitle", "ContentTitle"); bulkCopy.ColumnMappings.Add("DoctorUsername", "DoctorUsername"); bulkCopy.ColumnMappings.Add("DoctorShortName", "DoctorShortName"); bulkCopy.ColumnMappings.Add("DoctorEmail", "DoctorEmail"); bulkCopy.ColumnMappings.Add("RoomId", "RoomId"); bulkCopy.ColumnMappings.Add("RoomTitle", "RoomTitle"); bulkCopy.ColumnMappings.Add("NurseUsername", "NurseUsername"); bulkCopy.ColumnMappings.Add("NurseShortName", "NurseShortName"); bulkCopy.ColumnMappings.Add("StatusId", "StatusId"); bulkCopy.ColumnMappings.Add("StatusTitle", "StatusTitle"); bulkCopy.ColumnMappings.Add("Note", "Note"); bulkCopy.ColumnMappings.Add("StartTime", "StartTime"); bulkCopy.ColumnMappings.Add("EndTime", "EndTime"); bulkCopy.ColumnMappings.Add("ColorCode", "ColorCode"); bulkCopy.ColumnMappings.Add("IsComplete", "IsComplete"); bulkCopy.ColumnMappings.Add("IsDisabled", "IsDisabled"); bulkCopy.ColumnMappings.Add("CreateUser", "CreateUser"); bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate"); bulkCopy.ColumnMappings.Add("UpdateUser", "UpdateUser"); bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate"); foreach(ClinicDoctor.Entities.Appointment entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["CustomerId"] = entity.CustomerId; row["CustomerName"] = entity.CustomerName; row["ContentId"] = entity.ContentId.HasValue ? (object) entity.ContentId : System.DBNull.Value; row["ContentTitle"] = entity.ContentTitle; row["DoctorUsername"] = entity.DoctorUsername; row["DoctorShortName"] = entity.DoctorShortName; row["DoctorEmail"] = entity.DoctorEmail; row["RoomId"] = entity.RoomId.HasValue ? (object) entity.RoomId : System.DBNull.Value; row["RoomTitle"] = entity.RoomTitle; row["NurseUsername"] = entity.NurseUsername; row["NurseShortName"] = entity.NurseShortName; row["StatusId"] = entity.StatusId.HasValue ? (object) entity.StatusId : System.DBNull.Value; row["StatusTitle"] = entity.StatusTitle; row["Note"] = entity.Note; row["StartTime"] = entity.StartTime.HasValue ? (object) entity.StartTime : System.DBNull.Value; row["EndTime"] = entity.EndTime.HasValue ? (object) entity.EndTime : System.DBNull.Value; row["ColorCode"] = entity.ColorCode; row["IsComplete"] = entity.IsComplete; row["IsDisabled"] = entity.IsDisabled; row["CreateUser"] = entity.CreateUser; row["CreateDate"] = entity.CreateDate; row["UpdateUser"] = entity.UpdateUser; row["UpdateDate"] = entity.UpdateDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(ClinicDoctor.Entities.Appointment entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the School.Entities.Students object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<School.Entities.Students> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Students"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Address", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ClassId", typeof(System.Int32)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Birthdate", typeof(System.DateTime)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("Gender", typeof(System.String)); col5.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("ClassId", "ClassId"); bulkCopy.ColumnMappings.Add("Birthdate", "Birthdate"); bulkCopy.ColumnMappings.Add("Gender", "Gender"); foreach(School.Entities.Students entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["Name"] = entity.Name; row["Address"] = entity.Address; row["ClassId"] = entity.ClassId; row["Birthdate"] = entity.Birthdate; row["Gender"] = entity.Gender; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(School.Entities.Students entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Customers object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.Customers> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Customers"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CustomerID", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CompanyName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ContactName", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("ContactTitle", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Address", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("City", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("Region", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("PostalCode", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("Country", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("Phone", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("Fax", typeof(System.String)); col10.AllowDBNull = true; bulkCopy.ColumnMappings.Add("CustomerID", "CustomerID"); bulkCopy.ColumnMappings.Add("CompanyName", "CompanyName"); bulkCopy.ColumnMappings.Add("ContactName", "ContactName"); bulkCopy.ColumnMappings.Add("ContactTitle", "ContactTitle"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Region", "Region"); bulkCopy.ColumnMappings.Add("PostalCode", "PostalCode"); bulkCopy.ColumnMappings.Add("Country", "Country"); bulkCopy.ColumnMappings.Add("Phone", "Phone"); bulkCopy.ColumnMappings.Add("Fax", "Fax"); foreach(Northwind.Entities.Customers entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["CustomerID"] = entity.CustomerId; row["CompanyName"] = entity.CompanyName; row["ContactName"] = entity.ContactName; row["ContactTitle"] = entity.ContactTitle; row["Address"] = entity.Address; row["City"] = entity.City; row["Region"] = entity.Region; row["PostalCode"] = entity.PostalCode; row["Country"] = entity.Country; row["Phone"] = entity.Phone; row["Fax"] = entity.Fax; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.Customers entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.TimestampPk object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.TimestampPk> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "TimestampPK"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("TimestampPK", typeof(System.Byte[])); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("SomeText", typeof(System.String)); col1.AllowDBNull = true; bulkCopy.ColumnMappings.Add("TimestampPK", "TimestampPK"); bulkCopy.ColumnMappings.Add("SomeText", "SomeText"); foreach(Nettiers.AdventureWorks.Entities.TimestampPk entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["TimestampPK"] = entity.TimestampPk; row["SomeText"] = entity.SomeText; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.TimestampPk entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Library.BLL.BookImage object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Library.BLL.BookImage> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "BookImage"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Format", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Size", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Link", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("IsAvailable", typeof(System.String)); col5.AllowDBNull = true; bulkCopy.ColumnMappings.Add("ID", "ID"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Format", "Format"); bulkCopy.ColumnMappings.Add("Size", "Size"); bulkCopy.ColumnMappings.Add("Link", "Link"); bulkCopy.ColumnMappings.Add("IsAvailable", "IsAvailable"); foreach(Library.BLL.BookImage entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["ID"] = entity.ID; row["Name"] = entity.Name; row["Format"] = entity.Format; row["Size"] = entity.Size; row["Link"] = entity.Link; row["IsAvailable"] = entity.IsAvailable; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Library.BLL.BookImage entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.TestIssue117Tablec object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.TestIssue117Tablec> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "TestIssue117TableC"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("TestIssue117TableAId", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("TestIssue117TableBId", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("DumbField", typeof(System.Boolean)); col2.AllowDBNull = true; bulkCopy.ColumnMappings.Add("TestIssue117TableAId", "TestIssue117TableAId"); bulkCopy.ColumnMappings.Add("TestIssue117TableBId", "TestIssue117TableBId"); bulkCopy.ColumnMappings.Add("DumbField", "DumbField"); foreach(Nettiers.AdventureWorks.Entities.TestIssue117Tablec entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["TestIssue117TableAId"] = entity.TestIssue117TableAid; row["TestIssue117TableBId"] = entity.TestIssue117TableBid; row["DumbField"] = entity.DumbField.HasValue ? (object) entity.DumbField : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.TestIssue117Tablec entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.ProductVendor object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.ProductVendor> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "ProductVendor"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("VendorID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("AverageLeadTime", typeof(System.Int32)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("StandardPrice", typeof(System.Decimal)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("LastReceiptCost", typeof(System.Decimal)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("LastReceiptDate", typeof(System.DateTime)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("MinOrderQty", typeof(System.Int32)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("MaxOrderQty", typeof(System.Int32)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("OnOrderQty", typeof(System.Int32)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("UnitMeasureCode", typeof(System.String)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col10.AllowDBNull = false; bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("VendorID", "VendorID"); bulkCopy.ColumnMappings.Add("AverageLeadTime", "AverageLeadTime"); bulkCopy.ColumnMappings.Add("StandardPrice", "StandardPrice"); bulkCopy.ColumnMappings.Add("LastReceiptCost", "LastReceiptCost"); bulkCopy.ColumnMappings.Add("LastReceiptDate", "LastReceiptDate"); bulkCopy.ColumnMappings.Add("MinOrderQty", "MinOrderQty"); bulkCopy.ColumnMappings.Add("MaxOrderQty", "MaxOrderQty"); bulkCopy.ColumnMappings.Add("OnOrderQty", "OnOrderQty"); bulkCopy.ColumnMappings.Add("UnitMeasureCode", "UnitMeasureCode"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.ProductVendor entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["ProductID"] = entity.ProductId; row["VendorID"] = entity.VendorId; row["AverageLeadTime"] = entity.AverageLeadTime; row["StandardPrice"] = entity.StandardPrice; row["LastReceiptCost"] = entity.LastReceiptCost.HasValue ? (object)entity.LastReceiptCost : System.DBNull.Value; row["LastReceiptDate"] = entity.LastReceiptDate.HasValue ? (object)entity.LastReceiptDate : System.DBNull.Value; row["MinOrderQty"] = entity.MinOrderQty; row["MaxOrderQty"] = entity.MaxOrderQty; row["OnOrderQty"] = entity.OnOrderQty.HasValue ? (object)entity.OnOrderQty : System.DBNull.Value; row["UnitMeasureCode"] = entity.UnitMeasureCode; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.ProductVendor entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.Document object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.Document> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Document"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("DocumentID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Title", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("FileName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("FileExtension", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Revision", typeof(System.String)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("ChangeNumber", typeof(System.Int32)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("Status", typeof(System.Byte)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("DocumentSummary", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("Document", typeof(System.Byte[])); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col9.AllowDBNull = false; bulkCopy.ColumnMappings.Add("DocumentID", "DocumentID"); bulkCopy.ColumnMappings.Add("Title", "Title"); bulkCopy.ColumnMappings.Add("FileName", "FileName"); bulkCopy.ColumnMappings.Add("FileExtension", "FileExtension"); bulkCopy.ColumnMappings.Add("Revision", "Revision"); bulkCopy.ColumnMappings.Add("ChangeNumber", "ChangeNumber"); bulkCopy.ColumnMappings.Add("Status", "Status"); bulkCopy.ColumnMappings.Add("DocumentSummary", "DocumentSummary"); bulkCopy.ColumnMappings.Add("Document", "Document"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.Document entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["DocumentID"] = entity.DocumentId; row["Title"] = entity.Title; row["FileName"] = entity.FileName; row["FileExtension"] = entity.FileExtension; row["Revision"] = entity.Revision; row["ChangeNumber"] = entity.ChangeNumber; row["Status"] = entity.Status; row["DocumentSummary"] = entity.DocumentSummary; row["Document"] = entity.Document; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.Document entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
// BulkCopy("dbo.T_Benutzer", dt) public override bool BulkCopy(string tableSchema, string tableName, System.Data.DataTable dt, bool bWithDelete) { try { string sanitizedTableName = this.QuoteObjectWhereNecessary(tableName); // Ensure table is empty - and throw on foreign-key if (bWithDelete) { this.Execute("DELETE FROM " + sanitizedTableName); } System.Collections.Generic.List <string> lsComputedColumns = GetComputedColumnNames(tableSchema, tableName); // http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx System.Data.SqlClient.SqlBulkCopyOptions bcoOptions = //System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints | System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls | System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity; // http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column // http://msdn.microsoft.com/en-us/library/ms186335.aspx System.Data.SqlClient.SqlBulkCopy BulkCopyInstance = new System.Data.SqlClient.SqlBulkCopy(this.m_ConnectionString.ConnectionString, bcoOptions); foreach (System.Data.DataColumn dc in dt.Columns) { // The column "foo" cannot be modified because it is either a computed column or... if (MyExtensionMethods.Contains(lsComputedColumns, dc.ColumnName, System.StringComparer.InvariantCultureIgnoreCase)) { continue; } BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, "[" + dc.ColumnName.Replace("]", "]]") + "]"); } BulkCopyInstance.DestinationTableName = sanitizedTableName; /* * string strSQL = "INSERT INTO " + BulkCopyInstance.DestinationTableName + Environment.NewLine + "(" + Environment.NewLine; * * * for(int i=0; i < dt.Columns.Count; ++i) * { * if(i==0) * strSQL += " [" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * else * strSQL += " ,[" + dt.Columns[i].ColumnName + "]" + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * strSQL += ") " + Environment.NewLine + "Values "+ Environment.NewLine + "(" + Environment.NewLine; * * for (int i = 0; i < dt.Columns.Count; ++i) * { * if (i == 0) * strSQL += " @parameter" + i.ToString() + Environment.NewLine; * else * strSQL += " ,@parameter" + i.ToString() + Environment.NewLine; * //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); * } * * strSQL += "); "; * * // http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622244 * System.Data.IDbCommand idbc = this.CreateCommand(strSQL); * * for (int i = 0; i < dt.Rows.Count; ++i) * { * * for (int j = 0; j < dt.Columns.Count; ++j) * { * this.AddParameter(idbc, "parameter" + j.ToString(), dt.Rows[i][j]); * } * * //this.Execute(idbc); * this.ExecuteWithoutTransaction(idbc); * idbc.Parameters.Clear(); * } * * //MsgBox(strSQL); */ BulkCopyInstance.WriteToServer(dt); BulkCopyInstance.Close(); BulkCopyInstance = null; } catch (System.Exception ex) { if (Log("cMS_SQL_specific.BulkCopy", ex, "BulkCopy: Copy dt to " + tableName)) { throw; } //COR.Logging.WriteLogFile("FEHLER", "Ausnahme in COR.SQL.MSSQL.BulkCopy"); //COR.Logging.WriteLogFile("FEHLER", ex.Message); //COR.Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------"); //COR.Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString()); //Console.WriteLine(ex.Message.ToString() + Environment.NewLine + ex.StackTrace.ToString()); //MsgBoxStyle.Critical, "FEHLER ..."); //COR.Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------"); } return(false); } // End Function BulkCopy
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.AtrCode object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.AtrCode> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblAtrCode"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("trans_code", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("trans_name", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("status", typeof(System.Boolean)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("db_accid", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("cr_accid", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("accid1", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("accid2", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("accid3", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("pcommand", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("form", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("voucher", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("fvoucher", typeof(System.String)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("nov", typeof(System.Decimal)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("tr_type", typeof(System.String)); col13.AllowDBNull = true; bulkCopy.ColumnMappings.Add("trans_code", "trans_code"); bulkCopy.ColumnMappings.Add("trans_name", "trans_name"); bulkCopy.ColumnMappings.Add("status", "status"); bulkCopy.ColumnMappings.Add("db_accid", "db_accid"); bulkCopy.ColumnMappings.Add("cr_accid", "cr_accid"); bulkCopy.ColumnMappings.Add("accid1", "accid1"); bulkCopy.ColumnMappings.Add("accid2", "accid2"); bulkCopy.ColumnMappings.Add("accid3", "accid3"); bulkCopy.ColumnMappings.Add("pcommand", "pcommand"); bulkCopy.ColumnMappings.Add("form", "form"); bulkCopy.ColumnMappings.Add("voucher", "voucher"); bulkCopy.ColumnMappings.Add("fvoucher", "fvoucher"); bulkCopy.ColumnMappings.Add("nov", "nov"); bulkCopy.ColumnMappings.Add("tr_type", "tr_type"); foreach (LibraryManagement.Domain.AtrCode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["trans_code"] = entity.TransCode; row["trans_name"] = entity.TransName; row["status"] = entity.Status.HasValue ? (object)entity.Status : System.DBNull.Value; row["db_accid"] = entity.DbAccid; row["cr_accid"] = entity.CrAccid; row["accid1"] = entity.Accid1; row["accid2"] = entity.Accid2; row["accid3"] = entity.Accid3; row["pcommand"] = entity.Pcommand; row["form"] = entity.Form; row["voucher"] = entity.Voucher; row["fvoucher"] = entity.Fvoucher; row["nov"] = entity.Nov.HasValue ? (object)entity.Nov : System.DBNull.Value; row["tr_type"] = entity.TrType; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.AtrCode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Supports <see cref="System.Data.SqlClient.SqlBulkCopy"/> /// </summary> /// <typeparam name="T"></typeparam> /// <param name="context"></param> /// <param name="type"></param> /// <param name="entities"></param> /// <param name="sqlBulkCopy"></param> /// <param name="tableInfo"></param> /// <returns></returns> internal static DataTable GetDataTable <T>(DbContext context, Type type, IList <T> entities, System.Data.SqlClient.SqlBulkCopy sqlBulkCopy, TableInfo tableInfo) { DataTable dataTable = InnerGetDataTable(context, ref type, entities, tableInfo); foreach (DataColumn item in dataTable.Columns) //Add mapping { sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName); } return(dataTable); }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.OnOrder object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.OnOrder> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblOn_order"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("tran_date", typeof(System.DateTime)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("tran_time", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("deliver_dt", typeof(System.DateTime)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("trans_num", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("trans_code", typeof(System.String)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("exp_id", typeof(System.String)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("goods_id", typeof(System.String)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("qty", typeof(System.Decimal)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("unit_symb", typeof(System.String)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("price", typeof(System.Decimal)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("merc_type", typeof(System.String)); col10.AllowDBNull = false; DataColumn col11 = dataTable.Columns.Add("user_id", typeof(System.Decimal)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("remark", typeof(System.String)); col12.AllowDBNull = false; DataColumn col13 = dataTable.Columns.Add("recnum", typeof(System.Decimal)); col13.AllowDBNull = false; DataColumn col14 = dataTable.Columns.Add("status", typeof(System.Boolean)); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("end_qty", typeof(System.Decimal)); col15.AllowDBNull = true; bulkCopy.ColumnMappings.Add("tran_date", "tran_date"); bulkCopy.ColumnMappings.Add("tran_time", "tran_time"); bulkCopy.ColumnMappings.Add("deliver_dt", "deliver_dt"); bulkCopy.ColumnMappings.Add("trans_num", "trans_num"); bulkCopy.ColumnMappings.Add("trans_code", "trans_code"); bulkCopy.ColumnMappings.Add("exp_id", "exp_id"); bulkCopy.ColumnMappings.Add("goods_id", "goods_id"); bulkCopy.ColumnMappings.Add("qty", "qty"); bulkCopy.ColumnMappings.Add("unit_symb", "unit_symb"); bulkCopy.ColumnMappings.Add("price", "price"); bulkCopy.ColumnMappings.Add("merc_type", "merc_type"); bulkCopy.ColumnMappings.Add("user_id", "user_id"); bulkCopy.ColumnMappings.Add("remark", "remark"); bulkCopy.ColumnMappings.Add("recnum", "recnum"); bulkCopy.ColumnMappings.Add("status", "status"); bulkCopy.ColumnMappings.Add("end_qty", "end_qty"); foreach (LibraryManagement.Domain.OnOrder entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["tran_date"] = entity.TranDate; row["tran_time"] = entity.TranTime; row["deliver_dt"] = entity.DeliverDt.HasValue ? (object)entity.DeliverDt : System.DBNull.Value; row["trans_num"] = entity.TransNum; row["trans_code"] = entity.TransCode; row["exp_id"] = entity.ExpId; row["goods_id"] = entity.GoodsId; row["qty"] = entity.Qty; row["unit_symb"] = entity.UnitSymb; row["price"] = entity.Price; row["merc_type"] = entity.MercType; row["user_id"] = entity.UserId; row["remark"] = entity.Remark; row["recnum"] = entity.Recnum; row["status"] = entity.Status.HasValue ? (object)entity.Status : System.DBNull.Value; row["end_qty"] = entity.EndQty.HasValue ? (object)entity.EndQty : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.OnOrder entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/* * 功能: * 1、将System.Data.DataTable数据导出到Excel文件 * 2、将Model(Entity)数据实体导出到Excel文件 * 完整调用: * 1、ExcelHelper.ToExcelForDataTable(DataTable,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList); * 2、ExcelHelper.ToExcelForModelList(Model,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList); * 参数说明: * 1、DataTable:DataSet.DataTable[0];数据表 * 2、Model:Model.Users users = new Model.Users(){...};数据实体 * 3、excelPathName:含Excel名称的保存路径 在pathType=1时有效。用户自定义保存路径时请赋值空字符串 ""。格式:"E://456.xlsx" * 4、pathType:路径类型。只能取值:0用户自定义路径,弹出用户选择路径对话框;1服务端定义路径。标识文件保存路径是服务端指定还是客户自定义路径及文件名,与excelPathName参数合用 * 5、colName:各列的列别名List string,比如:字段名为userName,此处可指定为"用户名",并以此显示 * 6、excludeColumn:要显示/排除的列,指定这些列用于显示,或指定这些列用于不显示。倒低这些列是显示还是不显示,由excludeType参数决定 * 7、excludeType:显示/排除列方式。 0为显示所有列 1指定的是要显示的列 2指定的是要排除的列,与excludeColumn合用 * 8、sheetName:sheet1的名称,要使期保持默认名称请指定为空字符串 "" * 9、TemplatePath:模版在项目服务器中路径 例:tp.xlsx 。当为空字符串 "" 时表示无模版 * 10、TemplateRow:模版中已存在数据的行数,与TemplatePath合用,无模版时请传入参数 0 * 11、exDataTableList:扩展 DataTable List 用于当上下两个及以上DataTable数据类型不一至,但又都在同一列时使用,要求格式与参数第一个 DataTable的列名字段名一至,仅字段类型可不同 * 注意: * 1、exDataTableList参数为一个List<System.Data.DataTable> 集合,当数据为 Model 时,可先调用 ExcelHelper.ModelListToDataTable(System.Data.DataTable dt)将Model转为System.Data.DataTable */ #endregion #endregion #region 从Excel导入数据到 Ms Sql /// <summary> /// 从Excel导入数据到 Ms Sql /// </summary> /// <param name="excelFile">Excel文件路径(含文件名)</param> /// <param name="sheetName">sheet名</param> /// <param name="DbTableName">存储到数据库中的数据库表名称</param> /// <param name="columnType">对应表格的数据类型,如果为null,则为默认类型:double,nvarchar(500),datetime</param> /// <param name="connectionString">连接字符串</param> /// <returns></returns> public static bool FromExcel(string excelFile, string sheetName, string DbTableName, List <string> columnType, string connectionString) { DataSet ds = new DataSet(); try { //获取全部数据 //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连 #region 知识扩展 //HDR=Yes,代表第一行是标题,不做为数据使用。HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES //IMEX=0 只读模式 //IMEX=1 写入模式 //IMEX=2 可读写模式 #endregion #region 命名执行 using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); #region 数据库表是否存在的 T-SQL 检测语句准备 //如果目标表不存在则创建 string strSql = string.Format("if object_id('{0}') is null create table {0}(", DbTableName != "" ? DbTableName : sheetName); if (columnType != null && columnType.Count > 0) { #region 手动指定定每个字段的数据类型 //指定数据格式,要求一一对应 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { System.Data.DataColumn c = ds.Tables[0].Columns[i]; strSql += string.Format("[{0}] {1},", c.ColumnName, columnType[i]); } #endregion } else { #region 使用默认数据类型 foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { //使用默认格式:只有double,DateTime,String三种类型 switch (c.DataType.ToString()) { case "DateTime": { strSql += string.Format("[{0}] DateTime,", c.ColumnName); }; break; case "Double": { strSql += string.Format("[{0}] double,", c.ColumnName); }; break; default: strSql += string.Format("[{0}] nvarchar(500),", c.ColumnName); break; } } #endregion } strSql = strSql.Trim(',') + ")"; #endregion #region 执行 T-SQL 如果数据库表不存在则新建表,如果存在则不新建 using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } #endregion #region 向数据库表插入数据 using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { sbc.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); sbc.BatchSize = 100; //每次传输的行数 sbc.NotifyAfter = 100; //进度提示的行数 sbc.DestinationTableName = DbTableName != "" ? DbTableName : sheetName; //数据库表名表名 sbc.WriteToServer(ds.Tables[0]); } #endregion } #endregion } catch (Exception ex) { return(false); } return(true); }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.CreditCard object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.CreditCard> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "CreditCard"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CreditCardID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CardType", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("CardNumber", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ExpMonth", typeof(System.Byte)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ExpYear", typeof(System.Int16)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col5.AllowDBNull = false; bulkCopy.ColumnMappings.Add("CreditCardID", "CreditCardID"); bulkCopy.ColumnMappings.Add("CardType", "CardType"); bulkCopy.ColumnMappings.Add("CardNumber", "CardNumber"); bulkCopy.ColumnMappings.Add("ExpMonth", "ExpMonth"); bulkCopy.ColumnMappings.Add("ExpYear", "ExpYear"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.CreditCard entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["CreditCardID"] = entity.CreditCardId; row["CardType"] = entity.CardType; row["CardNumber"] = entity.CardNumber; row["ExpMonth"] = entity.ExpMonth; row["ExpYear"] = entity.ExpYear; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.CreditCard entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.PurchaseOrderDetail object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.PurchaseOrderDetail> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "PurchaseOrderDetail"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("PurchaseOrderID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("PurchaseOrderDetailID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("DueDate", typeof(System.DateTime)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("OrderQty", typeof(System.Int16)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("UnitPrice", typeof(System.Decimal)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("LineTotal", typeof(System.Decimal)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("ReceivedQty", typeof(System.Decimal)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("RejectedQty", typeof(System.Decimal)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("StockedQty", typeof(System.Decimal)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col10.AllowDBNull = false; bulkCopy.ColumnMappings.Add("PurchaseOrderID", "PurchaseOrderID"); bulkCopy.ColumnMappings.Add("PurchaseOrderDetailID", "PurchaseOrderDetailID"); bulkCopy.ColumnMappings.Add("DueDate", "DueDate"); bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty"); bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice"); bulkCopy.ColumnMappings.Add("LineTotal", "LineTotal"); bulkCopy.ColumnMappings.Add("ReceivedQty", "ReceivedQty"); bulkCopy.ColumnMappings.Add("RejectedQty", "RejectedQty"); bulkCopy.ColumnMappings.Add("StockedQty", "StockedQty"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.PurchaseOrderDetail entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["PurchaseOrderID"] = entity.PurchaseOrderId; row["PurchaseOrderDetailID"] = entity.PurchaseOrderDetailId; row["DueDate"] = entity.DueDate; row["OrderQty"] = entity.OrderQty; row["ProductID"] = entity.ProductId; row["UnitPrice"] = entity.UnitPrice; row["LineTotal"] = entity.LineTotal; row["ReceivedQty"] = entity.ReceivedQty; row["RejectedQty"] = entity.RejectedQty; row["StockedQty"] = entity.StockedQty; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.PurchaseOrderDetail entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.MofSys object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.MofSys> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblMof_sys"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("id", typeof(System.Decimal)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("mof_gl", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("mof_name", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("mof_nameb", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("ac_type", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("negative", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("master", typeof(System.Boolean)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("glevel", typeof(System.Int32)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("last_level", typeof(System.Boolean)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("other_equi", typeof(System.String)); col9.AllowDBNull = true; bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("mof_gl", "mof_gl"); bulkCopy.ColumnMappings.Add("mof_name", "mof_name"); bulkCopy.ColumnMappings.Add("mof_nameb", "mof_nameb"); bulkCopy.ColumnMappings.Add("ac_type", "ac_type"); bulkCopy.ColumnMappings.Add("negative", "negative"); bulkCopy.ColumnMappings.Add("master", "master"); bulkCopy.ColumnMappings.Add("glevel", "glevel"); bulkCopy.ColumnMappings.Add("last_level", "last_level"); bulkCopy.ColumnMappings.Add("other_equi", "other_equi"); foreach (LibraryManagement.Domain.MofSys entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["id"] = entity.Id; row["mof_gl"] = entity.MofGl; row["mof_name"] = entity.MofName; row["mof_nameb"] = entity.MofNameb; row["ac_type"] = entity.AcType; row["negative"] = entity.Negative; row["master"] = entity.Master.HasValue ? (object)entity.Master : System.DBNull.Value; row["glevel"] = entity.Glevel.HasValue ? (object)entity.Glevel : System.DBNull.Value; row["last_level"] = entity.LastLevel; row["other_equi"] = entity.OtherEqui; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.MofSys entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Account object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <PetShop.Business.Account> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Account"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("AccountId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("UniqueID", typeof(int)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Email", typeof(string)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("FirstName", typeof(string)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("LastName", typeof(string)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("Address1", typeof(string)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("Address2", typeof(string)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("City", typeof(string)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("State", typeof(string)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("Zip", typeof(string)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("Country", typeof(string)); col10.AllowDBNull = false; DataColumn col11 = dataTable.Columns.Add("Phone", typeof(string)); col11.AllowDBNull = true; bulkCopy.ColumnMappings.Add("AccountId", "AccountId"); bulkCopy.ColumnMappings.Add("UniqueID", "UniqueID"); bulkCopy.ColumnMappings.Add("Email", "Email"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("Address1", "Address1"); bulkCopy.ColumnMappings.Add("Address2", "Address2"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("State", "State"); bulkCopy.ColumnMappings.Add("Zip", "Zip"); bulkCopy.ColumnMappings.Add("Country", "Country"); bulkCopy.ColumnMappings.Add("Phone", "Phone"); foreach (PetShop.Business.Account entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["AccountId"] = entity.AccountId; row["UniqueID"] = entity.UniqueId; row["Email"] = entity.Email; row["FirstName"] = entity.FirstName; row["LastName"] = entity.LastName; row["Address1"] = entity.Address1; row["Address2"] = entity.Address2; row["City"] = entity.City; row["State"] = entity.State; row["Zip"] = entity.Zip; row["Country"] = entity.Country; row["Phone"] = entity.Phone; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (PetShop.Business.Account entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.OrderStatus object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <PetShop.Business.OrderStatus> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "OrderStatus"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LineNum", typeof(int)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Timestamp", typeof(System.DateTime)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Status", typeof(string)); col3.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderId", "OrderId"); bulkCopy.ColumnMappings.Add("LineNum", "LineNum"); bulkCopy.ColumnMappings.Add("Timestamp", "Timestamp"); bulkCopy.ColumnMappings.Add("Status", "Status"); foreach (PetShop.Business.OrderStatus entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["OrderId"] = entity.OrderId; row["LineNum"] = entity.LineNum; row["Timestamp"] = entity.Timestamp; row["Status"] = entity.Status; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (PetShop.Business.OrderStatus entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.Trcode object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.Trcode> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblTrcode"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("trCode", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("trName", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("description", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("trDepart", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("frmCaption", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("voucherFile", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("voucherFile1", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("voucherFile2", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("Status", typeof(System.Boolean)); col8.AllowDBNull = true; bulkCopy.ColumnMappings.Add("trCode", "trCode"); bulkCopy.ColumnMappings.Add("trName", "trName"); bulkCopy.ColumnMappings.Add("description", "description"); bulkCopy.ColumnMappings.Add("trDepart", "trDepart"); bulkCopy.ColumnMappings.Add("frmCaption", "frmCaption"); bulkCopy.ColumnMappings.Add("voucherFile", "voucherFile"); bulkCopy.ColumnMappings.Add("voucherFile1", "voucherFile1"); bulkCopy.ColumnMappings.Add("voucherFile2", "voucherFile2"); bulkCopy.ColumnMappings.Add("Status", "Status"); foreach (LibraryManagement.Domain.Trcode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["trCode"] = entity.TrCode; row["trName"] = entity.TrName; row["description"] = entity.Description; row["trDepart"] = entity.TrDepart; row["frmCaption"] = entity.FrmCaption; row["voucherFile"] = entity.VoucherFile; row["voucherFile1"] = entity.VoucherFile1; row["voucherFile2"] = entity.VoucherFile2; row["Status"] = entity.Status.HasValue ? (object)entity.Status : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.Trcode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the AppointmentSystem.Entities.MessageConfig object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<AppointmentSystem.Entities.MessageConfig> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "MessageConfig"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("MessageKey", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("MessageValue", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("Description", typeof(System.String)); col2.AllowDBNull = true; bulkCopy.ColumnMappings.Add("MessageKey", "MessageKey"); bulkCopy.ColumnMappings.Add("MessageValue", "MessageValue"); bulkCopy.ColumnMappings.Add("Description", "Description"); foreach(AppointmentSystem.Entities.MessageConfig entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["MessageKey"] = entity.MessageKey; row["MessageValue"] = entity.MessageValue; row["Description"] = entity.Description; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(AppointmentSystem.Entities.MessageConfig entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.ModePayment object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.ModePayment> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblModePayment"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("pmt_mode", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("description", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("forex", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("forex_rate", typeof(System.Decimal)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("iscard", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("status", typeof(System.Boolean)); col5.AllowDBNull = true; bulkCopy.ColumnMappings.Add("pmt_mode", "pmt_mode"); bulkCopy.ColumnMappings.Add("description", "description"); bulkCopy.ColumnMappings.Add("forex", "forex"); bulkCopy.ColumnMappings.Add("forex_rate", "forex_rate"); bulkCopy.ColumnMappings.Add("iscard", "iscard"); bulkCopy.ColumnMappings.Add("status", "status"); foreach (LibraryManagement.Domain.ModePayment entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["pmt_mode"] = entity.PmtMode; row["description"] = entity.Description; row["forex"] = entity.Forex; row["forex_rate"] = entity.ForexRate.HasValue ? (object)entity.ForexRate : System.DBNull.Value; row["iscard"] = entity.Iscard; row["status"] = entity.Status.HasValue ? (object)entity.Status : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.ModePayment entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the HearbalKartDB.Entities.States object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<HearbalKartDB.Entities.States> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "States"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CountryID", typeof(System.Int32)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("Name", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Pin", typeof(System.Int64)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("IsActive", typeof(System.Boolean)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("CreatedDate", typeof(System.DateTime)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("DeletedDate", typeof(System.DateTime)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("PinCode", typeof(System.String)); col8.AllowDBNull = true; bulkCopy.ColumnMappings.Add("ID", "ID"); bulkCopy.ColumnMappings.Add("CountryID", "CountryID"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Pin", "Pin"); bulkCopy.ColumnMappings.Add("IsActive", "IsActive"); bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); bulkCopy.ColumnMappings.Add("DeletedDate", "DeletedDate"); bulkCopy.ColumnMappings.Add("PinCode", "PinCode"); foreach(HearbalKartDB.Entities.States entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["ID"] = entity.Id; row["CountryID"] = entity.CountryId.HasValue ? (object) entity.CountryId : System.DBNull.Value; row["Name"] = entity.Name; row["Pin"] = entity.Pin.HasValue ? (object) entity.Pin : System.DBNull.Value; row["IsActive"] = entity.IsActive.HasValue ? (object) entity.IsActive : System.DBNull.Value; row["CreatedDate"] = entity.CreatedDate.HasValue ? (object) entity.CreatedDate : System.DBNull.Value; row["ModifiedDate"] = entity.ModifiedDate.HasValue ? (object) entity.ModifiedDate : System.DBNull.Value; row["DeletedDate"] = entity.DeletedDate.HasValue ? (object) entity.DeletedDate : System.DBNull.Value; row["PinCode"] = entity.PinCode; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(HearbalKartDB.Entities.States entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Product object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <PetShop.Business.Product> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Product"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ProductId", typeof(string)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CategoryId", typeof(string)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Name", typeof(string)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Descn", typeof(string)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Image", typeof(string)); col4.AllowDBNull = true; bulkCopy.ColumnMappings.Add("ProductId", "ProductId"); bulkCopy.ColumnMappings.Add("CategoryId", "CategoryId"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Descn", "Descn"); bulkCopy.ColumnMappings.Add("Image", "Image"); foreach (PetShop.Business.Product entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["ProductId"] = entity.ProductId; row["CategoryId"] = entity.CategoryId; row["Name"] = entity.Name; row["Descn"] = entity.Descn; row["Image"] = entity.Image; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (PetShop.Business.Product entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.SalesPerson object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.SalesPerson> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "SalesPerson"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("SalesPersonID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("TerritoryID", typeof(System.Int32)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("SalesQuota", typeof(System.Decimal)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Bonus", typeof(System.Decimal)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("CommissionPct", typeof(System.Decimal)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("SalesYTD", typeof(System.Decimal)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("SalesLastYear", typeof(System.Decimal)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("rowguid", typeof(System.Guid)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col8.AllowDBNull = false; bulkCopy.ColumnMappings.Add("SalesPersonID", "SalesPersonID"); bulkCopy.ColumnMappings.Add("TerritoryID", "TerritoryID"); bulkCopy.ColumnMappings.Add("SalesQuota", "SalesQuota"); bulkCopy.ColumnMappings.Add("Bonus", "Bonus"); bulkCopy.ColumnMappings.Add("CommissionPct", "CommissionPct"); bulkCopy.ColumnMappings.Add("SalesYTD", "SalesYTD"); bulkCopy.ColumnMappings.Add("SalesLastYear", "SalesLastYear"); bulkCopy.ColumnMappings.Add("rowguid", "rowguid"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.SalesPerson entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["SalesPersonID"] = entity.SalesPersonId; row["TerritoryID"] = entity.TerritoryId.HasValue ? (object) entity.TerritoryId : System.DBNull.Value; row["SalesQuota"] = entity.SalesQuota.HasValue ? (object) entity.SalesQuota : System.DBNull.Value; row["Bonus"] = entity.Bonus; row["CommissionPct"] = entity.CommissionPct; row["SalesYTD"] = entity.SalesYtd; row["SalesLastYear"] = entity.SalesLastYear; row["rowguid"] = entity.Rowguid; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.SalesPerson entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.ProductInventory object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.ProductInventory> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "ProductInventory"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LocationID", typeof(System.Int16)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Shelf", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Bin", typeof(System.Byte)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Quantity", typeof(System.Int16)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("rowguid", typeof(System.Guid)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col6.AllowDBNull = false; bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("LocationID", "LocationID"); bulkCopy.ColumnMappings.Add("Shelf", "Shelf"); bulkCopy.ColumnMappings.Add("Bin", "Bin"); bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); bulkCopy.ColumnMappings.Add("rowguid", "rowguid"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.ProductInventory entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["ProductID"] = entity.ProductId; row["LocationID"] = entity.LocationId; row["Shelf"] = entity.Shelf; row["Bin"] = entity.Bin; row["Quantity"] = entity.Quantity; row["rowguid"] = entity.Rowguid; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.ProductInventory entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Orders object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<PetShop.Business.Orders> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Orders"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("UserId", typeof(string)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("OrderDate", typeof(System.DateTime)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ShipAddr1", typeof(string)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ShipAddr2", typeof(string)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("ShipCity", typeof(string)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("ShipState", typeof(string)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("ShipZip", typeof(string)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("ShipCountry", typeof(string)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("BillAddr1", typeof(string)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("BillAddr2", typeof(string)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("BillCity", typeof(string)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("BillState", typeof(string)); col12.AllowDBNull = false; DataColumn col13 = dataTable.Columns.Add("BillZip", typeof(string)); col13.AllowDBNull = false; DataColumn col14 = dataTable.Columns.Add("BillCountry", typeof(string)); col14.AllowDBNull = false; DataColumn col15 = dataTable.Columns.Add("Courier", typeof(string)); col15.AllowDBNull = false; DataColumn col16 = dataTable.Columns.Add("TotalPrice", typeof(decimal)); col16.AllowDBNull = false; DataColumn col17 = dataTable.Columns.Add("BillToFirstName", typeof(string)); col17.AllowDBNull = false; DataColumn col18 = dataTable.Columns.Add("BillToLastName", typeof(string)); col18.AllowDBNull = false; DataColumn col19 = dataTable.Columns.Add("ShipToFirstName", typeof(string)); col19.AllowDBNull = false; DataColumn col20 = dataTable.Columns.Add("ShipToLastName", typeof(string)); col20.AllowDBNull = false; DataColumn col21 = dataTable.Columns.Add("AuthorizationNumber", typeof(int)); col21.AllowDBNull = false; DataColumn col22 = dataTable.Columns.Add("Locale", typeof(string)); col22.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderId", "OrderId"); bulkCopy.ColumnMappings.Add("UserId", "UserId"); bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate"); bulkCopy.ColumnMappings.Add("ShipAddr1", "ShipAddr1"); bulkCopy.ColumnMappings.Add("ShipAddr2", "ShipAddr2"); bulkCopy.ColumnMappings.Add("ShipCity", "ShipCity"); bulkCopy.ColumnMappings.Add("ShipState", "ShipState"); bulkCopy.ColumnMappings.Add("ShipZip", "ShipZip"); bulkCopy.ColumnMappings.Add("ShipCountry", "ShipCountry"); bulkCopy.ColumnMappings.Add("BillAddr1", "BillAddr1"); bulkCopy.ColumnMappings.Add("BillAddr2", "BillAddr2"); bulkCopy.ColumnMappings.Add("BillCity", "BillCity"); bulkCopy.ColumnMappings.Add("BillState", "BillState"); bulkCopy.ColumnMappings.Add("BillZip", "BillZip"); bulkCopy.ColumnMappings.Add("BillCountry", "BillCountry"); bulkCopy.ColumnMappings.Add("Courier", "Courier"); bulkCopy.ColumnMappings.Add("TotalPrice", "TotalPrice"); bulkCopy.ColumnMappings.Add("BillToFirstName", "BillToFirstName"); bulkCopy.ColumnMappings.Add("BillToLastName", "BillToLastName"); bulkCopy.ColumnMappings.Add("ShipToFirstName", "ShipToFirstName"); bulkCopy.ColumnMappings.Add("ShipToLastName", "ShipToLastName"); bulkCopy.ColumnMappings.Add("AuthorizationNumber", "AuthorizationNumber"); bulkCopy.ColumnMappings.Add("Locale", "Locale"); foreach(PetShop.Business.Orders entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderId"] = entity.OrderId; row["UserId"] = entity.UserId; row["OrderDate"] = entity.OrderDate; row["ShipAddr1"] = entity.ShipAddr1; row["ShipAddr2"] = entity.ShipAddr2; row["ShipCity"] = entity.ShipCity; row["ShipState"] = entity.ShipState; row["ShipZip"] = entity.ShipZip; row["ShipCountry"] = entity.ShipCountry; row["BillAddr1"] = entity.BillAddr1; row["BillAddr2"] = entity.BillAddr2; row["BillCity"] = entity.BillCity; row["BillState"] = entity.BillState; row["BillZip"] = entity.BillZip; row["BillCountry"] = entity.BillCountry; row["Courier"] = entity.Courier; row["TotalPrice"] = entity.TotalPrice; row["BillToFirstName"] = entity.BillToFirstName; row["BillToLastName"] = entity.BillToLastName; row["ShipToFirstName"] = entity.ShipToFirstName; row["ShipToLastName"] = entity.ShipToLastName; row["AuthorizationNumber"] = entity.AuthorizationNumber; row["Locale"] = entity.Locale; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Orders entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.AccTrans object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.AccTrans> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblAccTrans"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("id", typeof(System.Decimal)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("tran_date", typeof(System.DateTime)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("tran_time", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("ef_date", typeof(System.DateTime)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("trans_num", typeof(System.String)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("voucher", typeof(System.String)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("trans_code", typeof(System.String)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("account_id", typeof(System.String)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("db_amount", typeof(System.Decimal)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("cr_amount", typeof(System.Decimal)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("user_id", typeof(System.Decimal)); col10.AllowDBNull = false; DataColumn col11 = dataTable.Columns.Add("corr_acc", typeof(System.String)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("remark", typeof(System.String)); col12.AllowDBNull = false; DataColumn col13 = dataTable.Columns.Add("name", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("address", typeof(System.String)); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("person_id", typeof(System.String)); col15.AllowDBNull = true; DataColumn col16 = dataTable.Columns.Add("updated", typeof(System.Boolean)); col16.AllowDBNull = false; DataColumn col17 = dataTable.Columns.Add("recnum", typeof(System.Decimal)); col17.AllowDBNull = true; DataColumn col18 = dataTable.Columns.Add("status", typeof(System.Boolean)); col18.AllowDBNull = false; bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("tran_date", "tran_date"); bulkCopy.ColumnMappings.Add("tran_time", "tran_time"); bulkCopy.ColumnMappings.Add("ef_date", "ef_date"); bulkCopy.ColumnMappings.Add("trans_num", "trans_num"); bulkCopy.ColumnMappings.Add("voucher", "voucher"); bulkCopy.ColumnMappings.Add("trans_code", "trans_code"); bulkCopy.ColumnMappings.Add("account_id", "account_id"); bulkCopy.ColumnMappings.Add("db_amount", "db_amount"); bulkCopy.ColumnMappings.Add("cr_amount", "cr_amount"); bulkCopy.ColumnMappings.Add("user_id", "user_id"); bulkCopy.ColumnMappings.Add("corr_acc", "corr_acc"); bulkCopy.ColumnMappings.Add("remark", "remark"); bulkCopy.ColumnMappings.Add("name", "name"); bulkCopy.ColumnMappings.Add("address", "address"); bulkCopy.ColumnMappings.Add("person_id", "person_id"); bulkCopy.ColumnMappings.Add("updated", "updated"); bulkCopy.ColumnMappings.Add("recnum", "recnum"); bulkCopy.ColumnMappings.Add("status", "status"); foreach (LibraryManagement.Domain.AccTrans entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["id"] = entity.Id; row["tran_date"] = entity.TranDate; row["tran_time"] = entity.TranTime; row["ef_date"] = entity.EfDate.HasValue ? (object)entity.EfDate : System.DBNull.Value; row["trans_num"] = entity.TransNum; row["voucher"] = entity.Voucher; row["trans_code"] = entity.TransCode; row["account_id"] = entity.AccountId; row["db_amount"] = entity.DbAmount; row["cr_amount"] = entity.CrAmount; row["user_id"] = entity.UserId; row["corr_acc"] = entity.CorrAcc; row["remark"] = entity.Remark; row["name"] = entity.Name; row["address"] = entity.Address; row["person_id"] = entity.PersonId; row["updated"] = entity.Updated; row["recnum"] = entity.Recnum.HasValue ? (object)entity.Recnum : System.DBNull.Value; row["status"] = entity.Status; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.AccTrans entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.StoreContact object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.StoreContact> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "StoreContact"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CustomerID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ContactID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ContactTypeID", typeof(System.Int32)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("rowguid", typeof(System.Guid)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col4.AllowDBNull = false; bulkCopy.ColumnMappings.Add("CustomerID", "CustomerID"); bulkCopy.ColumnMappings.Add("ContactID", "ContactID"); bulkCopy.ColumnMappings.Add("ContactTypeID", "ContactTypeID"); bulkCopy.ColumnMappings.Add("rowguid", "rowguid"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.StoreContact entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["CustomerID"] = entity.CustomerId; row["ContactID"] = entity.ContactId; row["ContactTypeID"] = entity.ContactTypeId; row["rowguid"] = entity.Rowguid; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.StoreContact entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Library.BLL.Book object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Library.BLL.Book> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Book"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Publisher", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Publish_Date", typeof(System.DateTime)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("IsAvailablePdf", typeof(System.Boolean)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("IsAvailablePaper", typeof(System.Boolean)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("IsBorrowed", typeof(System.Boolean)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("User_ID", typeof(System.Int32)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("BorrowDate", typeof(System.DateTime)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("Borrow_Times", typeof(System.Int32)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("IsLost", typeof(System.Boolean)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("Type_ID", typeof(System.Int32)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("Publisher_ID", typeof(System.Int32)); col12.AllowDBNull = false; DataColumn col13 = dataTable.Columns.Add("Papers_no", typeof(System.Int32)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("Introducer_ID", typeof(System.Int32)); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("EmployeeI_D", typeof(System.Int32)); col15.AllowDBNull = false; DataColumn col16 = dataTable.Columns.Add("Size", typeof(System.Double)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("Price", typeof(System.Int32)); col17.AllowDBNull = true; DataColumn col18 = dataTable.Columns.Add("Pdf_Link", typeof(System.String)); col18.AllowDBNull = true; DataColumn col19 = dataTable.Columns.Add("Image_ID", typeof(System.Int32)); col19.AllowDBNull = false; DataColumn col20 = dataTable.Columns.Add("ISBN", typeof(System.String)); col20.AllowDBNull = true; DataColumn col21 = dataTable.Columns.Add("CreatedOn", typeof(System.DateTime)); col21.AllowDBNull = true; DataColumn col22 = dataTable.Columns.Add("CreatedBy", typeof(System.Int32)); col22.AllowDBNull = true; DataColumn col23 = dataTable.Columns.Add("UpdatedOn", typeof(System.DateTime)); col23.AllowDBNull = true; DataColumn col24 = dataTable.Columns.Add("UpdatedBy", typeof(System.Int32)); col24.AllowDBNull = true; DataColumn col25 = dataTable.Columns.Add("DeletedOn", typeof(System.DateTime)); col25.AllowDBNull = true; DataColumn col26 = dataTable.Columns.Add("DeletedBy", typeof(System.Int32)); col26.AllowDBNull = true; bulkCopy.ColumnMappings.Add("ID", "ID"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Publisher", "Publisher"); bulkCopy.ColumnMappings.Add("Publish_Date", "Publish_Date"); bulkCopy.ColumnMappings.Add("IsAvailablePdf", "IsAvailablePdf"); bulkCopy.ColumnMappings.Add("IsAvailablePaper", "IsAvailablePaper"); bulkCopy.ColumnMappings.Add("IsBorrowed", "IsBorrowed"); bulkCopy.ColumnMappings.Add("User_ID", "User_ID"); bulkCopy.ColumnMappings.Add("BorrowDate", "BorrowDate"); bulkCopy.ColumnMappings.Add("Borrow_Times", "Borrow_Times"); bulkCopy.ColumnMappings.Add("IsLost", "IsLost"); bulkCopy.ColumnMappings.Add("Type_ID", "Type_ID"); bulkCopy.ColumnMappings.Add("Publisher_ID", "Publisher_ID"); bulkCopy.ColumnMappings.Add("Papers_no", "Papers_no"); bulkCopy.ColumnMappings.Add("Introducer_ID", "Introducer_ID"); bulkCopy.ColumnMappings.Add("EmployeeI_D", "EmployeeI_D"); bulkCopy.ColumnMappings.Add("Size", "Size"); bulkCopy.ColumnMappings.Add("Price", "Price"); bulkCopy.ColumnMappings.Add("Pdf_Link", "Pdf_Link"); bulkCopy.ColumnMappings.Add("Image_ID", "Image_ID"); bulkCopy.ColumnMappings.Add("ISBN", "ISBN"); bulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn"); bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy"); bulkCopy.ColumnMappings.Add("UpdatedOn", "UpdatedOn"); bulkCopy.ColumnMappings.Add("UpdatedBy", "UpdatedBy"); bulkCopy.ColumnMappings.Add("DeletedOn", "DeletedOn"); bulkCopy.ColumnMappings.Add("DeletedBy", "DeletedBy"); foreach(Library.BLL.Book entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["ID"] = entity.ID; row["Name"] = entity.Name; row["Publisher"] = entity.Publisher; row["Publish_Date"] = entity.Publish_Date.HasValue ? (object) entity.Publish_Date : System.DBNull.Value; row["IsAvailablePdf"] = entity.IsAvailablePdf.HasValue ? (object) entity.IsAvailablePdf : System.DBNull.Value; row["IsAvailablePaper"] = entity.IsAvailablePaper.HasValue ? (object) entity.IsAvailablePaper : System.DBNull.Value; row["IsBorrowed"] = entity.IsBorrowed; row["User_ID"] = entity.User_ID; row["BorrowDate"] = entity.BorrowDate.HasValue ? (object) entity.BorrowDate : System.DBNull.Value; row["Borrow_Times"] = entity.Borrow_Times.HasValue ? (object) entity.Borrow_Times : System.DBNull.Value; row["IsLost"] = entity.IsLost.HasValue ? (object) entity.IsLost : System.DBNull.Value; row["Type_ID"] = entity.Type_ID; row["Publisher_ID"] = entity.Publisher_ID; row["Papers_no"] = entity.Papers_no.HasValue ? (object) entity.Papers_no : System.DBNull.Value; row["Introducer_ID"] = entity.Introducer_ID.HasValue ? (object) entity.Introducer_ID : System.DBNull.Value; row["EmployeeI_D"] = entity.EmployeeI_D; row["Size"] = entity.Size.HasValue ? (object) entity.Size : System.DBNull.Value; row["Price"] = entity.Price.HasValue ? (object) entity.Price : System.DBNull.Value; row["Pdf_Link"] = entity.Pdf_Link; row["Image_ID"] = entity.Image_ID; row["ISBN"] = entity.ISBN; row["CreatedOn"] = entity.CreatedOn.HasValue ? (object) entity.CreatedOn : System.DBNull.Value; row["CreatedBy"] = entity.CreatedBy.HasValue ? (object) entity.CreatedBy : System.DBNull.Value; row["UpdatedOn"] = entity.UpdatedOn.HasValue ? (object) entity.UpdatedOn : System.DBNull.Value; row["UpdatedBy"] = entity.UpdatedBy.HasValue ? (object) entity.UpdatedBy : System.DBNull.Value; row["DeletedOn"] = entity.DeletedOn.HasValue ? (object) entity.DeletedOn : System.DBNull.Value; row["DeletedBy"] = entity.DeletedBy.HasValue ? (object) entity.DeletedBy : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Library.BLL.Book entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the ClinicDoctor.Entities.Staff object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<ClinicDoctor.Entities.Staff> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Staff"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.Int64)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("FirstName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("LastName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ShortName", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("UserName", typeof(System.String)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("Email", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("Address", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("HomePhone", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("WorkPhone", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("CellPhone", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("Birthdate", typeof(System.DateTime)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("IsFemale", typeof(System.Boolean)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("Title", typeof(System.String)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("Note", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("Roles", typeof(System.String)); col14.AllowDBNull = false; DataColumn col15 = dataTable.Columns.Add("IsDisabled", typeof(System.Boolean)); col15.AllowDBNull = false; DataColumn col16 = dataTable.Columns.Add("CreateUser", typeof(System.String)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("CreateDate", typeof(System.DateTime)); col17.AllowDBNull = false; DataColumn col18 = dataTable.Columns.Add("UpdateUser", typeof(System.String)); col18.AllowDBNull = true; DataColumn col19 = dataTable.Columns.Add("UpdateDate", typeof(System.DateTime)); col19.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("ShortName", "ShortName"); bulkCopy.ColumnMappings.Add("UserName", "UserName"); bulkCopy.ColumnMappings.Add("Email", "Email"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("HomePhone", "HomePhone"); bulkCopy.ColumnMappings.Add("WorkPhone", "WorkPhone"); bulkCopy.ColumnMappings.Add("CellPhone", "CellPhone"); bulkCopy.ColumnMappings.Add("Birthdate", "Birthdate"); bulkCopy.ColumnMappings.Add("IsFemale", "IsFemale"); bulkCopy.ColumnMappings.Add("Title", "Title"); bulkCopy.ColumnMappings.Add("Note", "Note"); bulkCopy.ColumnMappings.Add("Roles", "Roles"); bulkCopy.ColumnMappings.Add("IsDisabled", "IsDisabled"); bulkCopy.ColumnMappings.Add("CreateUser", "CreateUser"); bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate"); bulkCopy.ColumnMappings.Add("UpdateUser", "UpdateUser"); bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate"); foreach(ClinicDoctor.Entities.Staff entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["FirstName"] = entity.FirstName; row["LastName"] = entity.LastName; row["ShortName"] = entity.ShortName; row["UserName"] = entity.UserName; row["Email"] = entity.Email; row["Address"] = entity.Address; row["HomePhone"] = entity.HomePhone; row["WorkPhone"] = entity.WorkPhone; row["CellPhone"] = entity.CellPhone; row["Birthdate"] = entity.Birthdate.HasValue ? (object) entity.Birthdate : System.DBNull.Value; row["IsFemale"] = entity.IsFemale; row["Title"] = entity.Title; row["Note"] = entity.Note; row["Roles"] = entity.Roles; row["IsDisabled"] = entity.IsDisabled; row["CreateUser"] = entity.CreateUser; row["CreateDate"] = entity.CreateDate; row["UpdateUser"] = entity.UpdateUser; row["UpdateDate"] = entity.UpdateDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(ClinicDoctor.Entities.Staff entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.StockInfo object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.StockInfo> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblStockInfo"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("id", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("goods_id", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("open_date", typeof(System.DateTime)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("last_date", typeof(System.DateTime)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("minus_date", typeof(System.DateTime)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("stktake_dt", typeof(System.DateTime)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("stktake_tm", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("begin_qty", typeof(System.Decimal)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("exp_qty", typeof(System.Decimal)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("imp_qty", typeof(System.Decimal)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("begin_amt", typeof(System.Decimal)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("exp_amt", typeof(System.Decimal)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("imp_amt", typeof(System.Decimal)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("averimppr", typeof(System.Decimal)); col13.AllowDBNull = true; bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("goods_id", "goods_id"); bulkCopy.ColumnMappings.Add("open_date", "open_date"); bulkCopy.ColumnMappings.Add("last_date", "last_date"); bulkCopy.ColumnMappings.Add("minus_date", "minus_date"); bulkCopy.ColumnMappings.Add("stktake_dt", "stktake_dt"); bulkCopy.ColumnMappings.Add("stktake_tm", "stktake_tm"); bulkCopy.ColumnMappings.Add("begin_qty", "begin_qty"); bulkCopy.ColumnMappings.Add("exp_qty", "exp_qty"); bulkCopy.ColumnMappings.Add("imp_qty", "imp_qty"); bulkCopy.ColumnMappings.Add("begin_amt", "begin_amt"); bulkCopy.ColumnMappings.Add("exp_amt", "exp_amt"); bulkCopy.ColumnMappings.Add("imp_amt", "imp_amt"); bulkCopy.ColumnMappings.Add("averimppr", "averimppr"); foreach (LibraryManagement.Domain.StockInfo entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["id"] = entity.Id; row["goods_id"] = entity.GoodsId; row["open_date"] = entity.OpenDate.HasValue ? (object)entity.OpenDate : System.DBNull.Value; row["last_date"] = entity.LastDate.HasValue ? (object)entity.LastDate : System.DBNull.Value; row["minus_date"] = entity.MinusDate.HasValue ? (object)entity.MinusDate : System.DBNull.Value; row["stktake_dt"] = entity.StktakeDt.HasValue ? (object)entity.StktakeDt : System.DBNull.Value; row["stktake_tm"] = entity.StktakeTm; row["begin_qty"] = entity.BeginQty.HasValue ? (object)entity.BeginQty : System.DBNull.Value; row["exp_qty"] = entity.ExpQty.HasValue ? (object)entity.ExpQty : System.DBNull.Value; row["imp_qty"] = entity.ImpQty.HasValue ? (object)entity.ImpQty : System.DBNull.Value; row["begin_amt"] = entity.BeginAmt.HasValue ? (object)entity.BeginAmt : System.DBNull.Value; row["exp_amt"] = entity.ExpAmt.HasValue ? (object)entity.ExpAmt : System.DBNull.Value; row["imp_amt"] = entity.ImpAmt.HasValue ? (object)entity.ImpAmt : System.DBNull.Value; row["averimppr"] = entity.Averimppr.HasValue ? (object)entity.Averimppr : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.StockInfo entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.Barcode object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.Barcode> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblBarcode"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("barcode", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("goods_id", typeof(System.String)); col1.AllowDBNull = false; bulkCopy.ColumnMappings.Add("barcode", "barcode"); bulkCopy.ColumnMappings.Add("goods_id", "goods_id"); foreach (LibraryManagement.Domain.Barcode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["barcode"] = entity.Barcode; row["goods_id"] = entity.GoodsId; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.Barcode entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.ProductPhoto object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.ProductPhoto> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "ProductPhoto"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ProductPhotoID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ThumbNailPhoto", typeof(System.Byte[])); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("ThumbnailPhotoFileName", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("LargePhoto", typeof(System.Byte[])); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("LargePhotoFileName", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col5.AllowDBNull = false; bulkCopy.ColumnMappings.Add("ProductPhotoID", "ProductPhotoID"); bulkCopy.ColumnMappings.Add("ThumbNailPhoto", "ThumbNailPhoto"); bulkCopy.ColumnMappings.Add("ThumbnailPhotoFileName", "ThumbnailPhotoFileName"); bulkCopy.ColumnMappings.Add("LargePhoto", "LargePhoto"); bulkCopy.ColumnMappings.Add("LargePhotoFileName", "LargePhotoFileName"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.ProductPhoto entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["ProductPhotoID"] = entity.ProductPhotoId; row["ThumbNailPhoto"] = entity.ThumbNailPhoto; row["ThumbnailPhotoFileName"] = entity.ThumbnailPhotoFileName; row["LargePhoto"] = entity.LargePhoto; row["LargePhotoFileName"] = entity.LargePhotoFileName; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.ProductPhoto entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the EmployeeDB.BLL.EmployeeSkills object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <EmployeeDB.BLL.EmployeeSkills> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "EmployeeSkills"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("EmployeeSkillId", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("EmployeeId", typeof(System.Int32)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("SkillCode", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("SkillLevel", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("CreatedOn", typeof(System.DateTime)); col4.AllowDBNull = true; bulkCopy.ColumnMappings.Add("EmployeeSkillId", "EmployeeSkillId"); bulkCopy.ColumnMappings.Add("EmployeeId", "EmployeeId"); bulkCopy.ColumnMappings.Add("SkillCode", "SkillCode"); bulkCopy.ColumnMappings.Add("SkillLevel", "SkillLevel"); bulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn"); foreach (EmployeeDB.BLL.EmployeeSkills entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["EmployeeSkillId"] = entity.EmployeeSkillId; row["EmployeeId"] = entity.EmployeeId.HasValue ? (object)entity.EmployeeId : System.DBNull.Value; row["SkillCode"] = entity.SkillCode; row["SkillLevel"] = entity.SkillLevel; row["CreatedOn"] = entity.CreatedOn.HasValue ? (object)entity.CreatedOn : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (EmployeeDB.BLL.EmployeeSkills entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Use bulk copy to insert the worksheet rows into the specified table /// </summary> /// <param name="tempTableName"></param> /// <param name="excelConnectionString"></param> /// <param name="worksheetName"></param> private void OleCopyToTempTable(string tempTableName, string excelConnectionString, string worksheetName) { using (System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(excelConnectionString)) { try { excelConnection.Open(); if (string.IsNullOrEmpty(ExcelQuery)) ExcelQuery = "SELECT * FROM [" + worksheetName + "$]"; else ExcelQuery += "FROM [" + worksheetName + "$]"; using (System.Data.OleDb.OleDbCommand selectAllComand = new System.Data.OleDb.OleDbCommand(ExcelQuery)) //SELECT * FROM [" + worksheetName + "$]")) { selectAllComand.Connection = excelConnection; using (System.Data.OleDb.OleDbDataReader excelReader = selectAllComand.ExecuteReader()) { using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(_dbConn)) { bc.BatchSize = 50; bc.DestinationTableName = @"[dbo].[" + tempTableName + @"]"; //tempDatasheetImport]"; // User notification with the SqlRowsCopied event //bc.NotifyAfter = 100; //bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); if (_dbConn.State != System.Data.ConnectionState.Open) _dbConn.Open(); bc.WriteToServer(excelReader); bc.Close(); } excelReader.Close(); _dbConn.Close(); } } excelConnection.Close(); } catch (InvalidOperationException ex) { //if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.") //not a problem, just reaching the footer and easier to skip an exception than try to filter it out // throw new Exception("OleCopyToTempTable 1: " + ex.Message); //else if (ex.Message != @"Column 'tmpMSEP' does not allow DBNull.Value.") //not a problem, just reaching the footer // throw new Exception("OleCopyToTempTable 2: " + ex.Message); //else if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.") // throw new Exception("OleCopyToTempTable 3: " + ex.Message); } finally { excelConnection.Close(); _dbConn.Close(); } //catch (Exception ex) //{ // throw new Exception("OleCopyToTempTable 4: " + ex.Message); //} } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.Vendor object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.Vendor> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Vendor"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("VendorID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("AccountNumber", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Name", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("CreditRating", typeof(System.Byte)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("PreferredVendorStatus", typeof(System.Boolean)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("ActiveFlag", typeof(System.Boolean)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("PurchasingWebServiceURL", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col7.AllowDBNull = false; bulkCopy.ColumnMappings.Add("VendorID", "VendorID"); bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("CreditRating", "CreditRating"); bulkCopy.ColumnMappings.Add("PreferredVendorStatus", "PreferredVendorStatus"); bulkCopy.ColumnMappings.Add("ActiveFlag", "ActiveFlag"); bulkCopy.ColumnMappings.Add("PurchasingWebServiceURL", "PurchasingWebServiceURL"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.Vendor entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["VendorID"] = entity.VendorId; row["AccountNumber"] = entity.AccountNumber; row["Name"] = entity.Name; row["CreditRating"] = entity.CreditRating; row["PreferredVendorStatus"] = entity.PreferredVendorStatus; row["ActiveFlag"] = entity.ActiveFlag; row["PurchasingWebServiceURL"] = entity.PurchasingWebServiceUrl; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.Vendor entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk many entity to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the SmartMassEmail.Entities.EmailMessageDetail object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<SmartMassEmail.Entities.EmailMessageDetail> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "EmailMessageDetail"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ID", typeof(System.Guid)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ChangeStamp", typeof(System.DateTime)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("IsBinary", typeof(System.Int32)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Name", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("BinaryData", typeof(System.Byte[])); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("StringData", typeof(System.String)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("EmailMessageID", typeof(System.Guid)); col6.AllowDBNull = false; bulkCopy.ColumnMappings.Add("ID", "ID"); bulkCopy.ColumnMappings.Add("ChangeStamp", "ChangeStamp"); bulkCopy.ColumnMappings.Add("IsBinary", "IsBinary"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("BinaryData", "BinaryData"); bulkCopy.ColumnMappings.Add("StringData", "StringData"); bulkCopy.ColumnMappings.Add("EmailMessageID", "EmailMessageID"); foreach(SmartMassEmail.Entities.EmailMessageDetail entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["ID"] = entity.ID; row["ChangeStamp"] = entity.ChangeStamp; row["IsBinary"] = entity.IsBinary; row["Name"] = entity.Name; row["BinaryData"] = entity.BinaryData; row["StringData"] = entity.StringData; row["EmailMessageID"] = entity.EmailMessageID; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(SmartMassEmail.Entities.EmailMessageDetail entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.SalesTerritory object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.SalesTerritory> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "SalesTerritory"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("TerritoryID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("CountryRegionCode", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Group", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("SalesYTD", typeof(System.Decimal)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("SalesLastYear", typeof(System.Decimal)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("CostYTD", typeof(System.Decimal)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("CostLastYear", typeof(System.Decimal)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("rowguid", typeof(System.Guid)); col8.AllowDBNull = false; DataColumn col9 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col9.AllowDBNull = false; bulkCopy.ColumnMappings.Add("TerritoryID", "TerritoryID"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("CountryRegionCode", "CountryRegionCode"); bulkCopy.ColumnMappings.Add("Group", "Group"); bulkCopy.ColumnMappings.Add("SalesYTD", "SalesYTD"); bulkCopy.ColumnMappings.Add("SalesLastYear", "SalesLastYear"); bulkCopy.ColumnMappings.Add("CostYTD", "CostYTD"); bulkCopy.ColumnMappings.Add("CostLastYear", "CostLastYear"); bulkCopy.ColumnMappings.Add("rowguid", "rowguid"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach (Nettiers.AdventureWorks.Entities.SalesTerritory entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["TerritoryID"] = entity.TerritoryId; row["Name"] = entity.Name; row["CountryRegionCode"] = entity.CountryRegionCode; row["Group"] = entity.Group; row["SalesYTD"] = entity.SalesYtd; row["SalesLastYear"] = entity.SalesLastYear; row["CostYTD"] = entity.CostYtd; row["CostLastYear"] = entity.CostLastYear; row["rowguid"] = entity.Rowguid; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.SalesTerritory entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.OrderStatus object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<PetShop.Business.OrderStatus> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "OrderStatus"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LineNum", typeof(int)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Timestamp", typeof(System.DateTime)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Status", typeof(string)); col3.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderId", "OrderId"); bulkCopy.ColumnMappings.Add("LineNum", "LineNum"); bulkCopy.ColumnMappings.Add("Timestamp", "Timestamp"); bulkCopy.ColumnMappings.Add("Status", "Status"); foreach(PetShop.Business.OrderStatus entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderId"] = entity.OrderId; row["LineNum"] = entity.LineNum; row["Timestamp"] = entity.Timestamp; row["Status"] = entity.Status; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.OrderStatus entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the AppointmentSystem.Entities.SmsReceiver object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<AppointmentSystem.Entities.SmsReceiver> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "SmsReceiver"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.Int64)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Mobile", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("FirstName", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("LastName", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("UserType", typeof(System.Byte)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("SmsId", typeof(System.Int64)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("IsSent", typeof(System.Boolean)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("SendingTimes", typeof(System.Int32)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("Note", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("IsDisabled", typeof(System.Boolean)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("CreateUser", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("CreateDate", typeof(System.DateTime)); col11.AllowDBNull = false; DataColumn col12 = dataTable.Columns.Add("UpdateUser", typeof(System.String)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("UpdateDate", typeof(System.DateTime)); col13.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("Mobile", "Mobile"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("UserType", "UserType"); bulkCopy.ColumnMappings.Add("SmsId", "SmsId"); bulkCopy.ColumnMappings.Add("IsSent", "IsSent"); bulkCopy.ColumnMappings.Add("SendingTimes", "SendingTimes"); bulkCopy.ColumnMappings.Add("Note", "Note"); bulkCopy.ColumnMappings.Add("IsDisabled", "IsDisabled"); bulkCopy.ColumnMappings.Add("CreateUser", "CreateUser"); bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate"); bulkCopy.ColumnMappings.Add("UpdateUser", "UpdateUser"); bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate"); foreach(AppointmentSystem.Entities.SmsReceiver entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["Mobile"] = entity.Mobile; row["FirstName"] = entity.FirstName; row["LastName"] = entity.LastName; row["UserType"] = entity.UserType; row["SmsId"] = entity.SmsId; row["IsSent"] = entity.IsSent; row["SendingTimes"] = entity.SendingTimes; row["Note"] = entity.Note; row["IsDisabled"] = entity.IsDisabled; row["CreateUser"] = entity.CreateUser; row["CreateDate"] = entity.CreateDate; row["UpdateUser"] = entity.UpdateUser; row["UpdateDate"] = entity.UpdateDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(AppointmentSystem.Entities.SmsReceiver entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Supplier object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<PetShop.Business.Supplier> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Supplier"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("SuppId", typeof(int)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(string)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("Status", typeof(string)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Addr1", typeof(string)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Addr2", typeof(string)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("City", typeof(string)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("State", typeof(string)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("Zip", typeof(string)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("Phone", typeof(string)); col8.AllowDBNull = true; bulkCopy.ColumnMappings.Add("SuppId", "SuppId"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Status", "Status"); bulkCopy.ColumnMappings.Add("Addr1", "Addr1"); bulkCopy.ColumnMappings.Add("Addr2", "Addr2"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("State", "State"); bulkCopy.ColumnMappings.Add("Zip", "Zip"); bulkCopy.ColumnMappings.Add("Phone", "Phone"); foreach(PetShop.Business.Supplier entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["SuppId"] = entity.SuppId; row["Name"] = entity.Name; row["Status"] = entity.Status; row["Addr1"] = entity.Addr1; row["Addr2"] = entity.Addr2; row["City"] = entity.City; row["State"] = entity.State; row["Zip"] = entity.Zip; row["Phone"] = entity.Phone; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Supplier entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
public void TransferData(string excelFile, string sheetName, string connectionString="") { connectionString = ConfigurationManager.ConnectionStrings["testStr"].ConnectionString; DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); //如果目标表不存在则创建 string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName); foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string.Format("[{0}] nvarchar(255),", c.ColumnName); } strSql = strSql.Trim(',') + ")"; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100;//每次传输的行数 bcp.NotifyAfter = 100;//进度提示的行数 bcp.DestinationTableName = sheetName;//目标表 bcp.WriteToServer(ds.Tables[0]); } } catch (Exception ex) { Console.Write(ex.Message); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.DatabaseLog object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.DatabaseLog> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "DatabaseLog"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("DatabaseLogID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("PostTime", typeof(System.DateTime)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("DatabaseUser", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Event", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Schema", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("Object", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("TSQL", typeof(System.String)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("XmlEvent", typeof(string)); col7.AllowDBNull = false; bulkCopy.ColumnMappings.Add("DatabaseLogID", "DatabaseLogID"); bulkCopy.ColumnMappings.Add("PostTime", "PostTime"); bulkCopy.ColumnMappings.Add("DatabaseUser", "DatabaseUser"); bulkCopy.ColumnMappings.Add("Event", "Event"); bulkCopy.ColumnMappings.Add("Schema", "Schema"); bulkCopy.ColumnMappings.Add("Object", "Object"); bulkCopy.ColumnMappings.Add("TSQL", "TSQL"); bulkCopy.ColumnMappings.Add("XmlEvent", "XmlEvent"); foreach(Nettiers.AdventureWorks.Entities.DatabaseLog entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["DatabaseLogID"] = entity.DatabaseLogId; row["PostTime"] = entity.PostTime; row["DatabaseUser"] = entity.DatabaseUser; row["Event"] = entity.SafeNameEvent; row["Schema"] = entity.Schema; row["Object"] = entity.SafeNameObject; row["TSQL"] = entity.Tsql; row["XmlEvent"] = entity.XmlEvent; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.DatabaseLog entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.WorkOrderRouting object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.WorkOrderRouting> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "WorkOrderRouting"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("WorkOrderID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("OperationSequence", typeof(System.Int16)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("LocationID", typeof(System.Int16)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ScheduledStartDate", typeof(System.DateTime)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("ScheduledEndDate", typeof(System.DateTime)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("ActualStartDate", typeof(System.DateTime)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("ActualEndDate", typeof(System.DateTime)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("ActualResourceHrs", typeof(System.Decimal)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("PlannedCost", typeof(System.Decimal)); col9.AllowDBNull = false; DataColumn col10 = dataTable.Columns.Add("ActualCost", typeof(System.Decimal)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col11.AllowDBNull = false; bulkCopy.ColumnMappings.Add("WorkOrderID", "WorkOrderID"); bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("OperationSequence", "OperationSequence"); bulkCopy.ColumnMappings.Add("LocationID", "LocationID"); bulkCopy.ColumnMappings.Add("ScheduledStartDate", "ScheduledStartDate"); bulkCopy.ColumnMappings.Add("ScheduledEndDate", "ScheduledEndDate"); bulkCopy.ColumnMappings.Add("ActualStartDate", "ActualStartDate"); bulkCopy.ColumnMappings.Add("ActualEndDate", "ActualEndDate"); bulkCopy.ColumnMappings.Add("ActualResourceHrs", "ActualResourceHrs"); bulkCopy.ColumnMappings.Add("PlannedCost", "PlannedCost"); bulkCopy.ColumnMappings.Add("ActualCost", "ActualCost"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.WorkOrderRouting entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["WorkOrderID"] = entity.WorkOrderId; row["ProductID"] = entity.ProductId; row["OperationSequence"] = entity.OperationSequence; row["LocationID"] = entity.LocationId; row["ScheduledStartDate"] = entity.ScheduledStartDate; row["ScheduledEndDate"] = entity.ScheduledEndDate; row["ActualStartDate"] = entity.ActualStartDate.HasValue ? (object) entity.ActualStartDate : System.DBNull.Value; row["ActualEndDate"] = entity.ActualEndDate.HasValue ? (object) entity.ActualEndDate : System.DBNull.Value; row["ActualResourceHrs"] = entity.ActualResourceHrs.HasValue ? (object) entity.ActualResourceHrs : System.DBNull.Value; row["PlannedCost"] = entity.PlannedCost; row["ActualCost"] = entity.ActualCost.HasValue ? (object) entity.ActualCost : System.DBNull.Value; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.WorkOrderRouting entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.Files object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.Files> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblFiles"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("id", typeof(System.Decimal)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("filename", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("version", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("filebinary", typeof(System.Byte[])); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("last_date", typeof(System.DateTime)); col4.AllowDBNull = true; bulkCopy.ColumnMappings.Add("id", "id"); bulkCopy.ColumnMappings.Add("filename", "filename"); bulkCopy.ColumnMappings.Add("version", "version"); bulkCopy.ColumnMappings.Add("filebinary", "filebinary"); bulkCopy.ColumnMappings.Add("last_date", "last_date"); foreach (LibraryManagement.Domain.Files entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["id"] = entity.Id; row["filename"] = entity.Filename; row["version"] = entity.Version; row["filebinary"] = entity.Filebinary; row["last_date"] = entity.LastDate.HasValue ? (object)entity.LastDate : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.Files entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Employees object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.Employees> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Employees"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("EmployeeID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("LastName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("FirstName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Title", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("TitleOfCourtesy", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("BirthDate", typeof(System.DateTime)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("HireDate", typeof(System.DateTime)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("Address", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("City", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("Region", typeof(System.String)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("PostalCode", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("Country", typeof(System.String)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("HomePhone", typeof(System.String)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("Extension", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("Photo", typeof(System.Byte[])); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("Notes", typeof(System.String)); col15.AllowDBNull = true; DataColumn col16 = dataTable.Columns.Add("ReportsTo", typeof(System.Int32)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("PhotoPath", typeof(System.String)); col17.AllowDBNull = true; bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("Title", "Title"); bulkCopy.ColumnMappings.Add("TitleOfCourtesy", "TitleOfCourtesy"); bulkCopy.ColumnMappings.Add("BirthDate", "BirthDate"); bulkCopy.ColumnMappings.Add("HireDate", "HireDate"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("Region", "Region"); bulkCopy.ColumnMappings.Add("PostalCode", "PostalCode"); bulkCopy.ColumnMappings.Add("Country", "Country"); bulkCopy.ColumnMappings.Add("HomePhone", "HomePhone"); bulkCopy.ColumnMappings.Add("Extension", "Extension"); bulkCopy.ColumnMappings.Add("Photo", "Photo"); bulkCopy.ColumnMappings.Add("Notes", "Notes"); bulkCopy.ColumnMappings.Add("ReportsTo", "ReportsTo"); bulkCopy.ColumnMappings.Add("PhotoPath", "PhotoPath"); foreach(Northwind.Entities.Employees entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["EmployeeID"] = entity.EmployeeId; row["LastName"] = entity.LastName; row["FirstName"] = entity.FirstName; row["Title"] = entity.Title; row["TitleOfCourtesy"] = entity.TitleOfCourtesy; row["BirthDate"] = entity.BirthDate.HasValue ? (object) entity.BirthDate : System.DBNull.Value; row["HireDate"] = entity.HireDate.HasValue ? (object) entity.HireDate : System.DBNull.Value; row["Address"] = entity.Address; row["City"] = entity.City; row["Region"] = entity.Region; row["PostalCode"] = entity.PostalCode; row["Country"] = entity.Country; row["HomePhone"] = entity.HomePhone; row["Extension"] = entity.Extension; row["Photo"] = entity.Photo; row["Notes"] = entity.Notes; row["ReportsTo"] = entity.ReportsTo.HasValue ? (object) entity.ReportsTo : System.DBNull.Value; row["PhotoPath"] = entity.PhotoPath; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.Employees entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk many entity to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the GenTest.Entities.Motore object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <GenTest.Entities.Motore> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Motore"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Descrizione", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("AggregationMotoreID", typeof(System.Int32)); col2.AllowDBNull = true; bulkCopy.ColumnMappings.Add("Descrizione", "Descrizione"); bulkCopy.ColumnMappings.Add("ID", "ID"); bulkCopy.ColumnMappings.Add("AggregationMotoreID", "AggregationMotoreID"); foreach (GenTest.Entities.Motore entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["Descrizione"] = entity.Descrizione; row["ID"] = entity.ID; row["AggregationMotoreID"] = entity.AggregationMotoreID.HasValue ? (object)entity.AggregationMotoreID : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (GenTest.Entities.Motore entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.StudentMasterIndex object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.StudentMasterIndex> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "STUDENT_MASTER_INDEX"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("STUDENT_ID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("EPASS_ID", typeof(System.String)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("STUDENT_UPN", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("SSABSA_ID", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("SURNAME", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("FIRST_NAME", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("OTHER_NAMES", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("KNOWN_NAME", typeof(System.String)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("LEGAL_NAME", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("DOB", typeof(System.DateTime)); col9.AllowDBNull = true; DataColumn col10 = dataTable.Columns.Add("GENDER", typeof(System.String)); col10.AllowDBNull = true; DataColumn col11 = dataTable.Columns.Add("INDIGENEOUS_STATUS", typeof(System.String)); col11.AllowDBNull = true; DataColumn col12 = dataTable.Columns.Add("LBOTE", typeof(System.String)); col12.AllowDBNull = true; DataColumn col13 = dataTable.Columns.Add("ESL_PHASE", typeof(System.String)); col13.AllowDBNull = true; DataColumn col14 = dataTable.Columns.Add("TRIBAL_GROUP", typeof(System.String)); col14.AllowDBNull = true; DataColumn col15 = dataTable.Columns.Add("SLP_CREATED_FLAG", typeof(System.String)); col15.AllowDBNull = true; DataColumn col16 = dataTable.Columns.Add("ADDRESS_LINE_1", typeof(System.String)); col16.AllowDBNull = true; DataColumn col17 = dataTable.Columns.Add("ADDRESS_LINE_2", typeof(System.String)); col17.AllowDBNull = true; DataColumn col18 = dataTable.Columns.Add("ADDRESS_LINE_3", typeof(System.String)); col18.AllowDBNull = true; DataColumn col19 = dataTable.Columns.Add("ADDRESS_LINE_4", typeof(System.String)); col19.AllowDBNull = true; DataColumn col20 = dataTable.Columns.Add("SUBURB", typeof(System.String)); col20.AllowDBNull = true; DataColumn col21 = dataTable.Columns.Add("POSTCODE", typeof(System.String)); col21.AllowDBNull = true; DataColumn col22 = dataTable.Columns.Add("PHONE1", typeof(System.String)); col22.AllowDBNull = true; DataColumn col23 = dataTable.Columns.Add("PHONE2", typeof(System.String)); col23.AllowDBNull = true; DataColumn col24 = dataTable.Columns.Add("SOURCE_SYSTEM", typeof(System.String)); col24.AllowDBNull = true; DataColumn col25 = dataTable.Columns.Add("PHONETIC_MATCH_ID", typeof(System.Int32)); col25.AllowDBNull = true; bulkCopy.ColumnMappings.Add("STUDENT_ID", "STUDENT_ID"); bulkCopy.ColumnMappings.Add("EPASS_ID", "EPASS_ID"); bulkCopy.ColumnMappings.Add("STUDENT_UPN", "STUDENT_UPN"); bulkCopy.ColumnMappings.Add("SSABSA_ID", "SSABSA_ID"); bulkCopy.ColumnMappings.Add("SURNAME", "SURNAME"); bulkCopy.ColumnMappings.Add("FIRST_NAME", "FIRST_NAME"); bulkCopy.ColumnMappings.Add("OTHER_NAMES", "OTHER_NAMES"); bulkCopy.ColumnMappings.Add("KNOWN_NAME", "KNOWN_NAME"); bulkCopy.ColumnMappings.Add("LEGAL_NAME", "LEGAL_NAME"); bulkCopy.ColumnMappings.Add("DOB", "DOB"); bulkCopy.ColumnMappings.Add("GENDER", "GENDER"); bulkCopy.ColumnMappings.Add("INDIGENEOUS_STATUS", "INDIGENEOUS_STATUS"); bulkCopy.ColumnMappings.Add("LBOTE", "LBOTE"); bulkCopy.ColumnMappings.Add("ESL_PHASE", "ESL_PHASE"); bulkCopy.ColumnMappings.Add("TRIBAL_GROUP", "TRIBAL_GROUP"); bulkCopy.ColumnMappings.Add("SLP_CREATED_FLAG", "SLP_CREATED_FLAG"); bulkCopy.ColumnMappings.Add("ADDRESS_LINE_1", "ADDRESS_LINE_1"); bulkCopy.ColumnMappings.Add("ADDRESS_LINE_2", "ADDRESS_LINE_2"); bulkCopy.ColumnMappings.Add("ADDRESS_LINE_3", "ADDRESS_LINE_3"); bulkCopy.ColumnMappings.Add("ADDRESS_LINE_4", "ADDRESS_LINE_4"); bulkCopy.ColumnMappings.Add("SUBURB", "SUBURB"); bulkCopy.ColumnMappings.Add("POSTCODE", "POSTCODE"); bulkCopy.ColumnMappings.Add("PHONE1", "PHONE1"); bulkCopy.ColumnMappings.Add("PHONE2", "PHONE2"); bulkCopy.ColumnMappings.Add("SOURCE_SYSTEM", "SOURCE_SYSTEM"); bulkCopy.ColumnMappings.Add("PHONETIC_MATCH_ID", "PHONETIC_MATCH_ID"); foreach(Nettiers.AdventureWorks.Entities.StudentMasterIndex entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["STUDENT_ID"] = entity.StudentId; row["EPASS_ID"] = entity.EpassId; row["STUDENT_UPN"] = entity.StudentUpn; row["SSABSA_ID"] = entity.SsabsaId; row["SURNAME"] = entity.Surname; row["FIRST_NAME"] = entity.FirstName; row["OTHER_NAMES"] = entity.OtherNames; row["KNOWN_NAME"] = entity.KnownName; row["LEGAL_NAME"] = entity.LegalName; row["DOB"] = entity.Dob.HasValue ? (object) entity.Dob : System.DBNull.Value; row["GENDER"] = entity.Gender; row["INDIGENEOUS_STATUS"] = entity.IndigeneousStatus; row["LBOTE"] = entity.Lbote; row["ESL_PHASE"] = entity.EslPhase; row["TRIBAL_GROUP"] = entity.TribalGroup; row["SLP_CREATED_FLAG"] = entity.SlpCreatedFlag; row["ADDRESS_LINE_1"] = entity.AddressLine1; row["ADDRESS_LINE_2"] = entity.AddressLine2; row["ADDRESS_LINE_3"] = entity.AddressLine3; row["ADDRESS_LINE_4"] = entity.AddressLine4; row["SUBURB"] = entity.Suburb; row["POSTCODE"] = entity.Postcode; row["PHONE1"] = entity.Phone1; row["PHONE2"] = entity.Phone2; row["SOURCE_SYSTEM"] = entity.SourceSystem; row["PHONETIC_MATCH_ID"] = entity.PhoneticMatchId.HasValue ? (object) entity.PhoneticMatchId : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.StudentMasterIndex entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.ErrorLog object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.ErrorLog> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "ErrorLog"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("ErrorLogID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ErrorTime", typeof(System.DateTime)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("UserName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ErrorNumber", typeof(System.Int32)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("ErrorSeverity", typeof(System.Int32)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("ErrorState", typeof(System.Int32)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("ErrorProcedure", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("ErrorLine", typeof(System.Int32)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("ErrorMessage", typeof(System.String)); col8.AllowDBNull = false; bulkCopy.ColumnMappings.Add("ErrorLogID", "ErrorLogID"); bulkCopy.ColumnMappings.Add("ErrorTime", "ErrorTime"); bulkCopy.ColumnMappings.Add("UserName", "UserName"); bulkCopy.ColumnMappings.Add("ErrorNumber", "ErrorNumber"); bulkCopy.ColumnMappings.Add("ErrorSeverity", "ErrorSeverity"); bulkCopy.ColumnMappings.Add("ErrorState", "ErrorState"); bulkCopy.ColumnMappings.Add("ErrorProcedure", "ErrorProcedure"); bulkCopy.ColumnMappings.Add("ErrorLine", "ErrorLine"); bulkCopy.ColumnMappings.Add("ErrorMessage", "ErrorMessage"); foreach (Nettiers.AdventureWorks.Entities.ErrorLog entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["ErrorLogID"] = entity.ErrorLogId; row["ErrorTime"] = entity.ErrorTime; row["UserName"] = entity.UserName; row["ErrorNumber"] = entity.ErrorNumber; row["ErrorSeverity"] = entity.ErrorSeverity.HasValue ? (object)entity.ErrorSeverity : System.DBNull.Value; row["ErrorState"] = entity.ErrorState.HasValue ? (object)entity.ErrorState : System.DBNull.Value; row["ErrorProcedure"] = entity.ErrorProcedure; row["ErrorLine"] = entity.ErrorLine.HasValue ? (object)entity.ErrorLine : System.DBNull.Value; row["ErrorMessage"] = entity.ErrorMessage; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.ErrorLog entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the PetShop.Business.Profiles object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<PetShop.Business.Profiles> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Profiles"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("UniqueID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Username", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ApplicationName", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("IsAnonymous", typeof(System.Boolean)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("LastActivityDate", typeof(System.DateTime)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("LastUpdatedDate", typeof(System.DateTime)); col5.AllowDBNull = true; bulkCopy.ColumnMappings.Add("UniqueID", "UniqueID"); bulkCopy.ColumnMappings.Add("Username", "Username"); bulkCopy.ColumnMappings.Add("ApplicationName", "ApplicationName"); bulkCopy.ColumnMappings.Add("IsAnonymous", "IsAnonymous"); bulkCopy.ColumnMappings.Add("LastActivityDate", "LastActivityDate"); bulkCopy.ColumnMappings.Add("LastUpdatedDate", "LastUpdatedDate"); foreach(PetShop.Business.Profiles entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["UniqueID"] = entity.UniqueId; row["Username"] = entity.Username; row["ApplicationName"] = entity.ApplicationName; row["IsAnonymous"] = entity.IsAnonymous.HasValue ? (object) entity.IsAnonymous : System.DBNull.Value; row["LastActivityDate"] = entity.LastActivityDate.HasValue ? (object) entity.LastActivityDate : System.DBNull.Value; row["LastUpdatedDate"] = entity.LastUpdatedDate.HasValue ? (object) entity.LastUpdatedDate : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(PetShop.Business.Profiles entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.Price object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.Price> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblPrice"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("goods_id", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("rtprice", typeof(System.Decimal)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("averimppr", typeof(System.Decimal)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("lastimppr", typeof(System.Decimal)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("wsprice", typeof(System.Decimal)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("prefprice", typeof(System.Decimal)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("discpercent", typeof(System.Decimal)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("disc_frdate", typeof(System.DateTime)); col7.AllowDBNull = true; DataColumn col8 = dataTable.Columns.Add("disc_todate", typeof(System.DateTime)); col8.AllowDBNull = true; bulkCopy.ColumnMappings.Add("goods_id", "goods_id"); bulkCopy.ColumnMappings.Add("rtprice", "rtprice"); bulkCopy.ColumnMappings.Add("averimppr", "averimppr"); bulkCopy.ColumnMappings.Add("lastimppr", "lastimppr"); bulkCopy.ColumnMappings.Add("wsprice", "wsprice"); bulkCopy.ColumnMappings.Add("prefprice", "prefprice"); bulkCopy.ColumnMappings.Add("discpercent", "discpercent"); bulkCopy.ColumnMappings.Add("disc_frdate", "disc_frdate"); bulkCopy.ColumnMappings.Add("disc_todate", "disc_todate"); foreach (LibraryManagement.Domain.Price entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["goods_id"] = entity.GoodsId; row["rtprice"] = entity.Rtprice.HasValue ? (object)entity.Rtprice : System.DBNull.Value; row["averimppr"] = entity.Averimppr.HasValue ? (object)entity.Averimppr : System.DBNull.Value; row["lastimppr"] = entity.Lastimppr.HasValue ? (object)entity.Lastimppr : System.DBNull.Value; row["wsprice"] = entity.Wsprice.HasValue ? (object)entity.Wsprice : System.DBNull.Value; row["prefprice"] = entity.Prefprice.HasValue ? (object)entity.Prefprice : System.DBNull.Value; row["discpercent"] = entity.Discpercent.HasValue ? (object)entity.Discpercent : System.DBNull.Value; row["disc_frdate"] = entity.DiscFrdate.HasValue ? (object)entity.DiscFrdate : System.DBNull.Value; row["disc_todate"] = entity.DiscTodate.HasValue ? (object)entity.DiscTodate : System.DBNull.Value; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.Price entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.Categories object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.Categories> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Categories"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("CategoryID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("CategoryName", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Description", typeof(System.String)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("Picture", typeof(System.Byte[])); col3.AllowDBNull = true; bulkCopy.ColumnMappings.Add("CategoryID", "CategoryID"); bulkCopy.ColumnMappings.Add("CategoryName", "CategoryName"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.ColumnMappings.Add("Picture", "Picture"); foreach(Northwind.Entities.Categories entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["CategoryID"] = entity.CategoryId; row["CategoryName"] = entity.CategoryName; row["Description"] = entity.Description; row["Picture"] = entity.Picture; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.Categories entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the LibraryManagement.Domain.TransValue object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <LibraryManagement.Domain.TransValue> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "tblTransValue"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("trans_num", typeof(System.String)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("amount", typeof(System.Decimal)); col1.AllowDBNull = true; DataColumn col2 = dataTable.Columns.Add("frcustomer", typeof(System.Decimal)); col2.AllowDBNull = true; DataColumn col3 = dataTable.Columns.Add("forex", typeof(System.String)); col3.AllowDBNull = true; bulkCopy.ColumnMappings.Add("trans_num", "trans_num"); bulkCopy.ColumnMappings.Add("amount", "amount"); bulkCopy.ColumnMappings.Add("frcustomer", "frcustomer"); bulkCopy.ColumnMappings.Add("forex", "forex"); foreach (LibraryManagement.Domain.TransValue entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["trans_num"] = entity.TransNum; row["amount"] = entity.Amount.HasValue ? (object)entity.Amount : System.DBNull.Value; row["frcustomer"] = entity.Frcustomer.HasValue ? (object)entity.Frcustomer : System.DBNull.Value; row["forex"] = entity.Forex; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (LibraryManagement.Domain.TransValue entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Northwind.Entities.OrderDetails object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Northwind.Entities.OrderDetails> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Order Details"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("OrderID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("UnitPrice", typeof(System.Decimal)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Quantity", typeof(System.Int16)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Discount", typeof(System.Single)); col4.AllowDBNull = false; bulkCopy.ColumnMappings.Add("OrderID", "OrderID"); bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice"); bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); bulkCopy.ColumnMappings.Add("Discount", "Discount"); foreach(Northwind.Entities.OrderDetails entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["OrderID"] = entity.OrderId; row["ProductID"] = entity.ProductId; row["UnitPrice"] = entity.UnitPrice; row["Quantity"] = entity.Quantity; row["Discount"] = entity.Discount; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Northwind.Entities.OrderDetails entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the School.Entities.Students object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <School.Entities.Students> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "Students"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Name", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("Address", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ClassId", typeof(System.Int32)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Birthdate", typeof(System.DateTime)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("Gender", typeof(System.String)); col5.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("Name", "Name"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("ClassId", "ClassId"); bulkCopy.ColumnMappings.Add("Birthdate", "Birthdate"); bulkCopy.ColumnMappings.Add("Gender", "Gender"); foreach (School.Entities.Students entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["Name"] = entity.Name; row["Address"] = entity.Address; row["ClassId"] = entity.ClassId; row["Birthdate"] = entity.Birthdate; row["Gender"] = entity.Gender; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (School.Entities.Students entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the ClinicDoctor.Entities.RosterType object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<ClinicDoctor.Entities.RosterType> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "RosterType"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("Id", typeof(System.Int64)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("Title", typeof(System.String)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("IsBooked", typeof(System.Boolean)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ColorCode", typeof(System.String)); col3.AllowDBNull = true; DataColumn col4 = dataTable.Columns.Add("Note", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("IsDisabled", typeof(System.Boolean)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("CreateUser", typeof(System.String)); col6.AllowDBNull = true; DataColumn col7 = dataTable.Columns.Add("CreateDate", typeof(System.DateTime)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("UpdateUser", typeof(System.String)); col8.AllowDBNull = true; DataColumn col9 = dataTable.Columns.Add("UpdateDate", typeof(System.DateTime)); col9.AllowDBNull = false; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("Title", "Title"); bulkCopy.ColumnMappings.Add("IsBooked", "IsBooked"); bulkCopy.ColumnMappings.Add("ColorCode", "ColorCode"); bulkCopy.ColumnMappings.Add("Note", "Note"); bulkCopy.ColumnMappings.Add("IsDisabled", "IsDisabled"); bulkCopy.ColumnMappings.Add("CreateUser", "CreateUser"); bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate"); bulkCopy.ColumnMappings.Add("UpdateUser", "UpdateUser"); bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate"); foreach(ClinicDoctor.Entities.RosterType entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["Id"] = entity.Id; row["Title"] = entity.Title; row["IsBooked"] = entity.IsBooked; row["ColorCode"] = entity.ColorCode; row["Note"] = entity.Note; row["IsDisabled"] = entity.IsDisabled; row["CreateUser"] = entity.CreateUser; row["CreateDate"] = entity.CreateDate; row["UpdateUser"] = entity.UpdateUser; row["UpdateDate"] = entity.UpdateDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(ClinicDoctor.Entities.RosterType entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.NullFkeyParent object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.NullFkeyParent> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "NullFKeyParent"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("NullFKeyParentID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("SomeText", typeof(System.String)); col1.AllowDBNull = true; bulkCopy.ColumnMappings.Add("NullFKeyParentID", "NullFKeyParentID"); bulkCopy.ColumnMappings.Add("SomeText", "SomeText"); foreach (Nettiers.AdventureWorks.Entities.NullFkeyParent entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["NullFKeyParentID"] = entity.NullFkeyParentId; row["SomeText"] = entity.SomeText; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.NullFkeyParent entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.TransactionHistory object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList<Nettiers.AdventureWorks.Entities.TransactionHistory> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "TransactionHistory"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("TransactionID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("ProductID", typeof(System.Int32)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("ReferenceOrderID", typeof(System.Int32)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("ReferenceOrderLineID", typeof(System.Int32)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("TransactionDate", typeof(System.DateTime)); col4.AllowDBNull = false; DataColumn col5 = dataTable.Columns.Add("TransactionType", typeof(System.String)); col5.AllowDBNull = false; DataColumn col6 = dataTable.Columns.Add("Quantity", typeof(System.Int32)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("ActualCost", typeof(System.Decimal)); col7.AllowDBNull = false; DataColumn col8 = dataTable.Columns.Add("ModifiedDate", typeof(System.DateTime)); col8.AllowDBNull = false; bulkCopy.ColumnMappings.Add("TransactionID", "TransactionID"); bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("ReferenceOrderID", "ReferenceOrderID"); bulkCopy.ColumnMappings.Add("ReferenceOrderLineID", "ReferenceOrderLineID"); bulkCopy.ColumnMappings.Add("TransactionDate", "TransactionDate"); bulkCopy.ColumnMappings.Add("TransactionType", "TransactionType"); bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); bulkCopy.ColumnMappings.Add("ActualCost", "ActualCost"); bulkCopy.ColumnMappings.Add("ModifiedDate", "ModifiedDate"); foreach(Nettiers.AdventureWorks.Entities.TransactionHistory entity in entities) { if (entity.EntityState != EntityState.Added) continue; DataRow row = dataTable.NewRow(); row["TransactionID"] = entity.TransactionId; row["ProductID"] = entity.ProductId; row["ReferenceOrderID"] = entity.ReferenceOrderId; row["ReferenceOrderLineID"] = entity.ReferenceOrderLineId; row["TransactionDate"] = entity.TransactionDate; row["TransactionType"] = entity.TransactionType; row["Quantity"] = entity.Quantity; row["ActualCost"] = entity.ActualCost; row["ModifiedDate"] = entity.ModifiedDate; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach(Nettiers.AdventureWorks.Entities.TransactionHistory entity in entities) { if (entity.EntityState != EntityState.Added) continue; entity.AcceptChanges(); } }
/// <summary> /// Lets you efficiently bulk insert many entities to the database. /// </summary> /// <param name="transactionManager">The transaction manager.</param> /// <param name="entities">The entities.</param> /// <remarks> /// After inserting into the datasource, the Nettiers.AdventureWorks.Entities.DatabaseLog object will be updated /// to refelect any changes made by the datasource. (ie: identity or computed columns) /// </remarks> public override void BulkInsert(TransactionManager transactionManager, TList <Nettiers.AdventureWorks.Entities.DatabaseLog> entities) { //System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); System.Data.SqlClient.SqlBulkCopy bulkCopy = null; if (transactionManager != null && transactionManager.IsOpen) { System.Data.SqlClient.SqlConnection cnx = transactionManager.TransactionObject.Connection as System.Data.SqlClient.SqlConnection; System.Data.SqlClient.SqlTransaction transaction = transactionManager.TransactionObject as System.Data.SqlClient.SqlTransaction; bulkCopy = new System.Data.SqlClient.SqlBulkCopy(cnx, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints, transaction); //, null); } else { bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._connectionString, System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints); //, null); } bulkCopy.BulkCopyTimeout = 360; bulkCopy.DestinationTableName = "DatabaseLog"; DataTable dataTable = new DataTable(); DataColumn col0 = dataTable.Columns.Add("DatabaseLogID", typeof(System.Int32)); col0.AllowDBNull = false; DataColumn col1 = dataTable.Columns.Add("PostTime", typeof(System.DateTime)); col1.AllowDBNull = false; DataColumn col2 = dataTable.Columns.Add("DatabaseUser", typeof(System.String)); col2.AllowDBNull = false; DataColumn col3 = dataTable.Columns.Add("Event", typeof(System.String)); col3.AllowDBNull = false; DataColumn col4 = dataTable.Columns.Add("Schema", typeof(System.String)); col4.AllowDBNull = true; DataColumn col5 = dataTable.Columns.Add("Object", typeof(System.String)); col5.AllowDBNull = true; DataColumn col6 = dataTable.Columns.Add("TSQL", typeof(System.String)); col6.AllowDBNull = false; DataColumn col7 = dataTable.Columns.Add("XmlEvent", typeof(string)); col7.AllowDBNull = false; bulkCopy.ColumnMappings.Add("DatabaseLogID", "DatabaseLogID"); bulkCopy.ColumnMappings.Add("PostTime", "PostTime"); bulkCopy.ColumnMappings.Add("DatabaseUser", "DatabaseUser"); bulkCopy.ColumnMappings.Add("Event", "Event"); bulkCopy.ColumnMappings.Add("Schema", "Schema"); bulkCopy.ColumnMappings.Add("Object", "Object"); bulkCopy.ColumnMappings.Add("TSQL", "TSQL"); bulkCopy.ColumnMappings.Add("XmlEvent", "XmlEvent"); foreach (Nettiers.AdventureWorks.Entities.DatabaseLog entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } DataRow row = dataTable.NewRow(); row["DatabaseLogID"] = entity.DatabaseLogId; row["PostTime"] = entity.PostTime; row["DatabaseUser"] = entity.DatabaseUser; row["Event"] = entity.SafeNameEvent; row["Schema"] = entity.Schema; row["Object"] = entity.SafeNameObject; row["TSQL"] = entity.Tsql; row["XmlEvent"] = entity.XmlEvent; dataTable.Rows.Add(row); } // send the data to the server bulkCopy.WriteToServer(dataTable); // update back the state foreach (Nettiers.AdventureWorks.Entities.DatabaseLog entity in entities) { if (entity.EntityState != EntityState.Added) { continue; } entity.AcceptChanges(); } }