/// <summary> /// Insert questions to database when create questionnarie /// </summary> /// <param name="dtQuestionnarie">for save the bulk of data to database</param> /// <returns>DataTable</returns> public DataTable SaveQuestionDAL(DataTable dtQuestionnarie) { try { connection.Open(); SqlBulkCopy objbulk = new SqlBulkCopy(connection); // Assigning Destination table name objbulk.DestinationTableName = "RE_Questions"; // Mapping Table column objbulk.ColumnMappings.Add("QuestionName", "QuestionName"); objbulk.ColumnMappings.Add("QuestionType", "QuestionType"); objbulk.ColumnMappings.Add("AnswerOption", "AnswerOption"); objbulk.ColumnMappings.Add("QuestionnaireId", "QuestionnaireId"); // Inserting bulk Records into DataBase objbulk.WriteToServer(dtQuestionnarie); } catch (Exception) { throw; } finally { connection.Close(); } return dtQuestionnarie; }
public void Import() { SqlBulkCopy copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock , null); copy.DestinationTableName = targetTable; copy.NotifyAfter = NotifyPerRows; copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(copy_SqlRowsCopied); copy.BatchSize = NotifyPerRows; // TODO: Extract this further up var validator = (ColumnValidator)null; if (targetTable.ToLower() == "comments") { validator = CommentsValidator; } if (targetTable.ToLower() == "posts") { validator = ViewCountValidator; } //targetTable == "Comments" ? CommentsValidator : (ColumnValidator)null; var dumpReader = new DumpReader(filename, targetTable, connection, validator); copy.WriteToServer(dumpReader); }
/// <summary> /// Executest bulk copy to ingest data from the DataReader /// </summary> /// <param name="dr"></param> protected void ExecuteBulkCopy(IDataReader dr, Table destination) { // TODO: it can only import the first resultset from dr var cg = new SqlServerCodeGenerator(); isBulkCopyCanceled = false; bulkCopyFinishedEvent = new AutoResetEvent(false); // Initialize bulk copy var sbc = new System.Data.SqlClient.SqlBulkCopy(destination.Dataset.ConnectionString); sbc.DestinationTableName = cg.GetResolvedTableName(destination); sbc.BatchSize = batchSize; sbc.BulkCopyTimeout = timeout; // Initialize events sbc.NotifyAfter = batchSize; sbc.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e) { //RowsAffected = e.RowsCopied; // TODO: delete if not used e.Abort = isBulkCopyCanceled; }; try { sbc.WriteToServer(dr); } finally { bulkCopyFinishedEvent.Set(); } }
/// <summary> /// 批量插入 /// </summary> public override bool InsertBulk <T>(IDbConnection conn, IEnumerable <T> listT, IDbTransaction trans = null) { try { var type = typeof(T); var tableName = GetTableName(type); //SqlBulkCopy不是根据表的ColumnName来匹配的,而是根据ColumnIndex匹配, //也就是说你的表 字段必须跟数据库的表字段完全一致(Index的排序要跟数据表的一样)。 var dtEmpty = conn.GetDataTable($"SELECT * FROM {tableName} WHERE 1=2");//返回空结构,用于对齐字段 var dt = ToDataTable(listT, dtEmpty); var sqlConn = conn as SqlConnection; var sqlTrans = trans as SqlTransaction; using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTrans)) { bulkCopy.DestinationTableName = tableName; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); return(true); } } catch (Exception) { throw; } }
public int StoreSearchReplaceTagHistory(List <SwordAndTagReplace> data, int LastId) { try { con = ConnectionClass.getConnection(); DataTable table = new DataTable(); table = ConvertToDataTable(data, LastId); using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(con)) { bulkCopy.DestinationTableName = "tbl_SourceFileHistoryTag"; bulkCopy.ColumnMappings.Add("FileID", "FileID"); bulkCopy.ColumnMappings.Add("SearchWord", "SearchWord"); bulkCopy.ColumnMappings.Add("TagName", "TagName"); bulkCopy.ColumnMappings.Add("Instruction", "Instruction"); bulkCopy.ColumnMappings.Add("FilterType", "FilterType"); bulkCopy.WriteToServer(table); } ConnectionClass.closeconnection(con); } catch (Exception ex) { ConnectionClass.closeconnection(con); DataLogger.Write("FHFile-StoreSearchReplaceTagHistory", ex.Message); } finally { ConnectionClass.closeconnection(con); } return(-1); }
/// <summary> /// 利用BCP方法往数据库添加数据 /// </summary> /// <param name="DT"></param> /// <param name="TableName"></param> /// <param name="TableCellName"></param> /// <param name="BcpCellName"></param> /// <returns></returns> public static bool Execute_Bcp(DataTable DT, String TableName, System.Collections.ArrayList TableCellName, System.Collections.ArrayList BcpCellName) { bool ConnectState = false; ConnectState = ConnectSql(); SqlErrorText = ConnectState ? string.Empty : "连接SqlServerError"; if (!ConnectState) { return(ConnectState); } SqlNowCount = 0; using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(SqlHand)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 1000; // 1000;//每次传输的行数 bcp.NotifyAfter = 100; //进度提示的行数 bcp.DestinationTableName = TableName; //目标表 for (int i = 0; i < TableCellName.Count; i++) { bcp.ColumnMappings.Add(TableCellName[i].ToString(), BcpCellName[i].ToString()); } try { bcp.WriteToServer(DT); return(true); } catch (Exception Ex) { SqlErrorText = Ex.Message; return(false); } } }
/// <summary> /// DataTable批量添加(有事务) /// </summary> /// <param name="dataTable"></param> /// <param name="destinationTableName"></param> /// <returns></returns> private void MySqlBulkCopy(DataTable dataTable, string destinationTableName) { string connectionString = "server=172.16.8.208,1109;database=10W;uid=sa;pwd=sa"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, transaction)) { bulkCopy.DestinationTableName = destinationTableName; //获取映射关系 SqlBulkCopyColumnMapping[] mapping = GetMapping(); if (mapping!=null) { foreach (SqlBulkCopyColumnMapping columnMapping in mapping) { bulkCopy.ColumnMappings.Add(columnMapping); } } try { bulkCopy.WriteToServer(dataTable); transaction.Commit(); } catch (Exception ex) { } } } } }
public static bool UpdateBulkCustomerInfo(SqlCommand cmd, Data.dsData.CustomerInfoDataTable BulkTable) { bool outPut = false; DateTime dtStart = DateTime.Now; const string BulkTableName = "CustomerInfo"; try { //Delete All Recored Before Inserting cmd.CommandText = "DELETE FROM CustomerInfo"; cmd.ExecuteNonQuery(); SqlBulkCopy bulkCopy = new SqlBulkCopy(cmd.Connection); cmd.CommandTimeout = 0; bulkCopy.BulkCopyTimeout = 0; bulkCopy.ColumnMappings.Clear(); bulkCopy.ColumnMappings.Add("Distribution Channel", "Distribution Channel"); bulkCopy.ColumnMappings.Add("Customer", "Customer"); bulkCopy.ColumnMappings.Add("Start date of validity", "Start date of validity"); bulkCopy.ColumnMappings.Add("End date of validity", "End date of validity"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.DestinationTableName = BulkTableName; bulkCopy.BatchSize = BulkTable.Count; bulkCopy.WriteToServer(BulkTable); outPut = true; } catch (SqlException ex) { Classes.Core.LogException(Logger, ex, Classes.Core.ExceptionLevelEnum.General, Classes.Managers.UserManager.defaultInstance.User.UserId); System.Windows.Forms.MessageBox.Show("Error while trying to save Bulk - " + ex.Message); } return outPut; }
/// <summary> /// Bulk insert /// </summary> /// <param name="records"></param> /// <param name="tableName"></param> /// <param name="listTableColumn"></param> /// <param name="entityType"></param> public void BulkInsert(IEnumerable<object> records, string tableName, List<string> listTableColumn, Type entityType) { // // Try to create bulk copy through sqlconnection try { // // Open sql connection SqlConnection sqlConnection = new SqlConnection(this._connectionString); sqlConnection.Open(); // // Create and config sql bulk copy SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection); sqlBulkCopy.DestinationTableName = tableName; foreach (string column in listTableColumn) { sqlBulkCopy.ColumnMappings.Add(column, column); } // // Start insert to database DataTable dataTable = ToDataTable(records, entityType); sqlBulkCopy.WriteToServer(dataTable); // // Close connection sqlConnection.Close(); } catch (Exception exc) { LogService.Log.Error("Can not insert new record into database by SQLBulkInsert.", exc); } }
public bool Import(DataTable dt) { DataView dv = dt.DefaultView; DataTable newTable4 = dv.ToTable("NewTableName", true, new string[] { "UserType", "Name", "Sex", "LoginName", "UniqueNo", "AuthenType", "IsDelete" }); bool Flag = true; try { //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(ConfigHelper.GetConfigString("connStr"))) { //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100; //每次传输的行数 bcp.NotifyAfter = 100; //进度提示的行数 bcp.DestinationTableName = "Sys_UserInfo"; //目标表 bcp.WriteToServer(newTable4); Flag = true; } } catch (Exception ex) { Flag = false; } return(Flag); }
/// <summary> /// 直接将DataTable的数据按同样的结构复制到指定的表中 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool SqlBulkCopy(DataTable dt) { try { //数据批量导入sqlserver,创建实例 SqlBulkCopyOptions.UseInternalTransaction采用事务 复制失败自动回滚 using (var sqlbulk = new System.Data.SqlClient.SqlBulkCopy(SqlConnectionString, SqlBulkCopyOptions.UseInternalTransaction)) { sqlbulk.NotifyAfter = dt.Rows.Count; //目标数据库表名 sqlbulk.DestinationTableName = dt.TableName; //数据集字段索引与数据库字段索引映射 foreach (DataColumn dc in dt.Columns) { sqlbulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } //导入数据 sqlbulk.WriteToServer(dt); } return(true); } catch (Exception ex) { throw ex; } finally { dt.Dispose(); } }
/// <summary> /// 批量插入数据 /// </summary> /// <param name="dts">要插入的数据列表(列名与数据库中的列名相同)</param> /// <param name="destinationTableNames">数据库中的目标表的名称列表(列表名与dts顺序相同)</param> /// <returns>如果插入成功则返回true,否则返回false</returns> /// <remarks>作成者:高奇,作成日:2013-9-6</remarks> public static bool BulkInsert(DataTable[] dts, string[] destinationTableNames) { if (dts == null || dts.Length == 0 || destinationTableNames == null || destinationTableNames.Length == 0) return false; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlTransaction tran = connection.BeginTransaction(); try { for (int i = 0; i < dts.Length; i++) { SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran); bulkCopy.DestinationTableName = destinationTableNames[i]; bulkCopy.BatchSize = dts[i].Rows.Count; bulkCopy.WriteToServer(dts[i]); bulkCopy.Close(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw new Exception(ex.Message); } connection.Close(); return true; } }
/// <summary> /// 批量插入数据 /// </summary> /// <param name="dt">要插入的数据(列名与数据库中的列名相同)</param> /// <param name="destinationTableName">数据库中的目标表的名称</param> /// <returns>如果插入成功则返回true,否则返回false</returns> /// <remarks>作成者:高奇,作成日:2013-9-6</remarks> public static bool BulkInsert(DataTable dt, string destinationTableName) { if (dt == null || dt.Rows.Count == 0) return false; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlBulkCopy bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = destinationTableName; bulkCopy.BatchSize = dt.Rows.Count; try { connection.Open(); bulkCopy.WriteToServer(dt); } catch (Exception ex) { throw ex; } finally { connection.Close(); if (bulkCopy != null) bulkCopy.Close(); } return true; } }
public static int bulkinsert(DataTable source_table, SqlConnection conn, string detination_table) { int i = 1; using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn)) { conn.Open(); bulkCopy.DestinationTableName = detination_table; //"[dbo].[ShowAudit_Table]"; try { bulkCopy.WriteToServer(source_table); } catch (Exception ex) { i = 0; Microsoft.Practices.EnterpriseLibrary.Logging.Logger.Write(ex.Message.ToString()); } finally { conn.Close(); } } return i; }
/// <summary> /// 只是负责批量导入 不看字段名 /// </summary> /// <param name="str"></param> /// <returns></returns> private DataSet GetExcelData1(string str) { string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + str + ";Extended Properties=\"Excel 12.0;HDR=YES\""; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [a$];"; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet, "[a$]"); DataTable dt = myDataSet.Tables[0]; GetColumnsByDataTable(dt); ///打开数据库 直接批量插入 string str1 = "Data Source=.;Initial Catalog=my;Pooling=true; min pool size=5; max pool size=600;Persist Security Info=True;User ID=sa;Password=swlk_2011"; using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(str1)) { bcp.BatchSize = 100; //每次传输的行数 bcp.DestinationTableName = "temp5"; //目标表 bcp.WriteToServer(dt); } myConn.Close(); return(myDataSet); }
private void ExecuteBulkInsert(FileDataReader dr) { var cstr = Test.Constants.TestConnectionString; var dropsql = "IF OBJECT_ID('testtable','U') IS NOT NULL DROP TABLE testtable"; var createsql = "CREATE TABLE testtable (one int, two int, three int)"; using (var cn = new SqlConnection(cstr)) { cn.Open(); // Drop table first using (var cmd = new SqlCommand(dropsql, cn)) { cmd.ExecuteNonQuery(); } // Create empty table using (var cmd = new SqlCommand(createsql, cn)) { cmd.ExecuteNonQuery(); } // Run bulk-insert var bcp = new SqlBulkCopy(cn); bcp.DestinationTableName = "testtable"; bcp.WriteToServer(dr); // Drop table using (var cmd = new SqlCommand(dropsql, cn)) { cmd.ExecuteNonQuery(); } } }
public static void Upload(this System.Data.DataTable dt, string tableName) { string query = "SELECT * from " + tableName; using (SqlConnection conn = new SqlConnection(SqlConnStr)) { using (SqlDataAdapter oda = new SqlDataAdapter()) { using (SqlCommandBuilder bu = new SqlCommandBuilder()) { using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn)) { oda.SelectCommand = new SqlCommand(query, conn); bulkcopy.DestinationTableName = "dbo." + tableName; DataTable dtsql = new DataTable(); oda.Fill(dtsql); List<DataRow> lst_temp = dt.AsEnumerable().ToList(); foreach (DataRow row in lst_temp) { dtsql.ImportRow(row); } conn.Open(); bulkcopy.WriteToServer(dtsql); conn.Close(); } } } } }
private async Task BulkCopyExecuteAsync(IDataReader sourceDataReader, SqlConnection targetConnection, string targetTableName, IEnumerable<ColumnsMapping> columnsMappings, Action<long> rowsCopiedNotify) { using (var bulkCopy = new SqlBulkCopy(targetConnection)) { if (columnsMappings != null) { var bulkMappings = CreateBulkCopyMappings(columnsMappings); bulkCopy.AddMappings(bulkMappings); } bulkCopy.DestinationTableName = targetTableName; bulkCopy.BatchSize = _batchSize; bulkCopy.NotifyAfter = _notifyAfter; bulkCopy.BulkCopyTimeout = _bulkCopyTimeout; if (rowsCopiedNotify != null) { bulkCopy.SqlRowsCopied += (sender, e) => rowsCopiedNotify(e.RowsCopied); } await bulkCopy.WriteToServerAsync(sourceDataReader); } }
private static void ImportIDs(long[] IDs, SqlCommand command) { long start = DateTime.Now.Ticks; DataTable t = new DataTable(); DataColumn c = new DataColumn("ID", typeof(long)); t.Columns.Add(c); DataColumn c2 = new DataColumn("seq_nr", typeof(long)); c2.AutoIncrementSeed = 1; c2.AutoIncrementStep = 1; c2.AutoIncrement = true; t.Columns.Add(c2); foreach (long id in IDs) { DataRow dr = t.NewRow(); dr[0] = id; t.Rows.Add(dr); } command.CommandText = "IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL " + "DROP TABLE #temp " + "CREATE TABLE #temp(ID BIGINT, seq_nr BIGINT); CREATE INDEX ix ON #temp (ID);"; command.ExecuteNonQuery(); SqlBulkCopy copy = new SqlBulkCopy(command.Connection, SqlBulkCopyOptions.UseInternalTransaction, null); copy.DestinationTableName = "#temp"; copy.WriteToServer(t); t.Dispose(); Debug.WriteLine("<<ImportIDs>> " + new TimeSpan(DateTime.Now.Ticks - start).ToString()); }
/// <summary> /// Wrapper class to produce an Ado.Net Datatable from any entity, /// and perform SqlBulkCopy operations /// </summary> public SqlEntityBulkCopy(string sqlCnnString, Type entityType) { if (Cfg == null) { //Note: The NHibernate.Cfg.Configuration is meant only as an initialization-time object. //Note: NHibernate.ISessionFactory is immutable and does not retain any association back to the Session Cfg = new Configuration(); //Cfg.SetProperty("proxyfactory.factory_class", "NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle"); Cfg.SetProperty("dialect", "NHibernate.Dialect.MsSql2008Dialect"); Cfg.SetProperty("connection.provider", "NHibernate.Connection.DriverConnectionProvider"); Cfg.SetProperty("connection.driver_class", "NHibernate.Driver.SqlClientDriver"); Cfg.SetProperty("connection.connection_string", sqlCnnString); //add all the mappings embedded in this assembly Cfg.AddAssembly(typeof(SqlEntityBulkCopy).Assembly); var sessionFactory = Cfg.BuildSessionFactory(); SessionFactoryImpl = (ISessionFactoryImplementor)sessionFactory; } EntityType = entityType; //_session = SessionFactoryImpl.OpenSession(); _metaData = SessionFactoryImpl.GetClassMetadata(EntityType); _persistentClass = Cfg.GetClassMapping(EntityType); _sqlCnn = new SqlConnection(sqlCnnString); _sqlBulkCopy = new SqlBulkCopy(_sqlCnn); //Debug.WriteLine("EntityName = " + _metaData.EntityName); //Debug.WriteLine("IdentifierPropertyName = " + _metaData.IdentifierPropertyName); //Debug.WriteLine("IdentifierType = " + _metaData.IdentifierType); BuildDataTable(); BuildAndMapSqlBulkCopy(); }
/// <summary> /// 服务器在线人数 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool LogServerDb(DataTable dt) { bool result = false; if (dt == null) { return(result); } System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.AppSettings["countDb"], SqlBulkCopyOptions.UseInternalTransaction); try { sqlbulk.NotifyAfter = dt.Rows.Count; sqlbulk.DestinationTableName = "Log_Server"; sqlbulk.ColumnMappings.Add(0, "ApplicationId"); sqlbulk.ColumnMappings.Add(1, "SubId"); sqlbulk.ColumnMappings.Add(2, "EnterTime"); sqlbulk.ColumnMappings.Add(3, "Online"); sqlbulk.ColumnMappings.Add(4, "Reg"); sqlbulk.WriteToServer(dt); result = true; } catch (Exception ex) { if (log.IsErrorEnabled) { log.Error("Server Log Error:" + ex.ToString()); } } finally { sqlbulk.Close(); dt.Clear(); } return(result); }
public static bool RecordDownload( List<int> ids,string type) { DataTable dt = new DataTable(); DataColumn colid = new DataColumn("pid",typeof(int)); DataColumn coltype = new DataColumn("type",typeof(string)); dt.Columns.Add(colid); dt.Columns.Add(coltype); foreach (int i in ids) { DataRow row = dt.NewRow(); row["pid"] = i; row["type"] = type; dt.Rows.Add(row); } using (SqlConnection con = SqlDbAccess.GetSqlConnection()) { con.Open(); using (SqlBulkCopy copy = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints, null)) { copy.BatchSize = 5000; copy.BulkCopyTimeout = 3000; copy.DestinationTableName = "dbo.RecordDownload"; copy.ColumnMappings.Add("pid", "pid"); copy.ColumnMappings.Add("type", "type"); copy.WriteToServer(dt); } con.Close(); } return true; }
public static void BulkCopy(DataTable table, string connectionString) { using (var connection = new SqlConnection(connectionString)) { SqlTransaction transaction = null; connection.Open(); try { transaction = connection.BeginTransaction(); using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)) { sqlBulkCopy.BatchSize = table.Rows.Count; sqlBulkCopy.DestinationTableName = table.TableName; sqlBulkCopy.MapColumns(table); sqlBulkCopy.WriteToServer(table); } transaction.Commit(); } catch { transaction?.Rollback(); throw; } } }
public bool GuardarDataTableBD(DataTable dataTable, string nombreTabla) { bool guardado = false; using (SqlConnection connection = new SqlConnection(_conString)) { connection.Open(); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection)) { sqlBulkCopy.DestinationTableName = nombreTabla; try { sqlBulkCopy.WriteToServer(dataTable); guardado = true; } catch (Exception) { throw; } } } return guardado; }
public int InsertBulkRows(DataSet data) { SourceData = data; var val = 0; if (SourceData == null) return val; val = SourceData.Tables[0].Rows.Count; using (var con = new SqlConnection(Connection.ConnectionString)) { using (var bc = new SqlBulkCopy(con)) { bc.BatchSize = BatchSize; bc.NotifyAfter = BatchSize; bc.BulkCopyTimeout = (con.ConnectionTimeout > 3600) ? con.ConnectionTimeout : 3600; foreach (DataColumn c in SourceData.Tables[0].Columns) { bc.ColumnMappings.Add(c.ColumnName, c.ColumnName); } bc.SqlRowsCopied += bc_SqlRowsCopied; bc.DestinationTableName = TableName; con.Open(); bc.WriteToServer(SourceData.Tables[0]); } } return val; }
public bool ExecuteBulkCopy(DataTable valueTable, string tableName) { bool success = false; try { using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.ColumnMappings.Add(0, 0); bulkCopy.ColumnMappings.Add(1, 1); bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(valueTable); success = true; } } } catch (Exception) { throw; } return success; }
/// <summary> /// Insert data from datatable into database using SQL bulk Copy /// </summary> /// <param name="data"></param> /// <param name="DatabaseConnection"></param> /// <param name="TableOwner"></param> /// <param name="Table"></param> /// <returns></returns> public static bool SQLBulkInsert(DataTable data, string DatabaseConnection, string TableOwner, string Table) { try { using (SqlConnection dbConnection = new SqlConnection(DatabaseConnection)) { dbConnection.Open(); // Copies all rows to the database from the data reader. using (SqlBulkCopy bc = new SqlBulkCopy(dbConnection)) { // Destination table with owner - // this example doesn't // check the owner and table names! bc.DestinationTableName = "[" + TableOwner + "].[" + Table + "]"; // User notification with the SqlRowsCopied event bc.NotifyAfter = 100; bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); // Starts the bulk copy. bc.WriteToServer(data); // Closes the SqlBulkCopy instance bc.Close(); } dbConnection.Close(); return true; } } catch (Exception Ex) { EventLog.WriteEntry(Application.ProductName, "BulkSQLInsert Error: " + Ex.Message, EventLogEntryType.Error); throw Ex; } }
/// <summary>批量导入DataTable /// 批量导入DataTable /// </summary> /// <param name="dt">DataTable数据表</param> /// <param name="tblName">表名</param> /// <param name="dtColumn">数据列集合</param> /// <return>Boolean值:true成功,false失败</return> public static Boolean insertTbl(DataTable dt, String tblName, DataColumnCollection dtColumn) { try { using (SqlBulkCopy sqlBC = new SqlBulkCopy(connString)) { //一次批量的插入的数据量 sqlBC.BatchSize = 1000; //超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除 sqlBC.BulkCopyTimeout = 60; //设置要批量写入的表 sqlBC.DestinationTableName = tblName; //自定义的datatable和数据库的字段进行对应 //sqlBC.ColumnMappings.Add("id", "tel"); //sqlBC.ColumnMappings.Add("name", "neirong"); for (int i = 0; i < dtColumn.Count; i++) { sqlBC.ColumnMappings.Add(dtColumn[i].ColumnName.ToString(), dtColumn[i].ColumnName.ToString()); } //批量写入 VipSoft.Common.NPOI2.ExcelUtils.printDT(dt); sqlBC.WriteToServer(dt); } } catch(Exception e) { Console.Write(e.Message); return false; } return true; }
public void Import(IEnumerable<ProductData> products) { var table = new DataTable(); table.Columns.Add("ProductID"); table.Columns.Add("Description"); table.Columns.Add("Price"); using (var bulkCopy = new SqlBulkCopy(_settings.ConnectionString)) { bulkCopy.DestinationTableName = "Products"; bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.ColumnMappings.Add("Price", "Price"); foreach (var product in products) { table.Rows.Add(product.ProductID, product.Description, product.ProductID); if (table.Rows.Count == _settings.BatchSize) { bulkCopy.WriteToServer(table); table.Rows.Clear(); } } bulkCopy.WriteToServer(table); } }
public void Save(SqlBulkCopyConfiguration configuration) { using (var connection = new SqlConnection(configuration.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)) { bulkCopy.BatchSize = configuration.BatchSize; bulkCopy.DestinationTableName = configuration.TableName; try { foreach (var column in configuration.DataTable.Columns) { bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString()); } bulkCopy.WriteToServer(configuration.DataTable); transaction.Commit(); } catch (Exception e) { transaction.Rollback(); connection.Close(); throw new Exception(e.Message); } } } }
public static void Convert(string shapefile, string connectionString, string tableName, int srid = 0, string targetProjectionWKT = null) { GeometryTransform transform = GeometryTransform.GetTransform(shapefile, targetProjectionWKT); GeometryFactory factory = new GeometryFactory(new PrecisionModel(), srid); using (SqlConnection conn = new SqlConnection(connectionString)) using (SqlBulkCopy copy = new SqlBulkCopy(conn)) using (ShapefileDataReader reader = new ShapefileDataReader(shapefile, factory, transform)) { conn.Open(); string createTableSql = GenerateCreateTableQuery(reader, tableName); using (SqlCommand createTableComm = new SqlCommand(createTableSql, conn)) createTableComm.ExecuteNonQuery(); copy.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) => { System.Console.Clear(); System.Console.WriteLine("Copied " + e.RowsCopied); }; copy.NotifyAfter = 257; copy.DestinationTableName = tableName; copy.WriteToServer(new ShapefileBulkSqlReader(reader, srid)); } }
protected void Page_Load(object sender, EventArgs e) { string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings["strConLocal"].ToString(); //string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings["strConTest"].ToString(); string strFileName = Strings.GetQueryString("F"); string strGroupID = Strings.GetQueryString("G"); strImportType = Strings.GetQueryString("T"); //DataAccess.ExecuteDb("Truncate Table App_Scores_" + strImportType + "_Temp"); SqlConnection sql = new SqlConnection(strConnectionString); SqlCommand truncate = new SqlCommand("Truncate table Scores_GPA_CAMS_Temp", sql); sql.Open(); truncate.ExecuteNonQuery(); sql.Close(); truncate = null; sql = null; DataTable ImportData = PopulateDataTable(strFileName); ImportData.Rows.RemoveAt(0); using (SqlBulkCopy MoveXLSData = new SqlBulkCopy(strConnectionString)) { MoveXLSData.DestinationTableName = "App_Scores_" + strImportType + "_Temp" ; MoveXLSData.WriteToServer(ImportData); } Response.Write("1"); Response.End(); }
/// <summary> /// 用户消费日志 /// </summary> /// <param name="db"></param> /// <returns></returns> public bool LogMoneyDb(DataTable dt) { bool result = false; if (dt == null) { return(result); } System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.AppSettings["countDb"], SqlBulkCopyOptions.UseInternalTransaction); try { sqlbulk.NotifyAfter = dt.Rows.Count; sqlbulk.DestinationTableName = "Log_Money"; sqlbulk.ColumnMappings.Add(0, "ApplicationId"); sqlbulk.ColumnMappings.Add(1, "SubId"); sqlbulk.ColumnMappings.Add(2, "LineId"); sqlbulk.ColumnMappings.Add(3, "MastType"); sqlbulk.ColumnMappings.Add(4, "SonType"); sqlbulk.ColumnMappings.Add(5, "UserId"); sqlbulk.ColumnMappings.Add(6, "EnterTime"); sqlbulk.ColumnMappings.Add(7, "Moneys"); sqlbulk.ColumnMappings.Add(8, "Gold"); sqlbulk.ColumnMappings.Add(9, "GiftToken"); sqlbulk.ColumnMappings.Add(10, "Offer"); sqlbulk.ColumnMappings.Add(11, "OtherPay"); sqlbulk.ColumnMappings.Add(12, "GoodId"); sqlbulk.ColumnMappings.Add(13, "ShopId"); sqlbulk.ColumnMappings.Add(14, "Datas"); sqlbulk.WriteToServer(dt); //ApplicationId int Unchecked //SubId int Checked //LineId int Checked //MastType int Checked //SonType int Checked //UserId int Checked //EnterTime datetime Checked //Moneys int Checked //Gold int Checked //GiftToken int Checked //Offer int Checked //OtherPay varchar(400) Checked //GoodId int Checked //ShopId int Checked //Datas int Checked result = true; } catch (Exception ex) { //TrieuLSL //if (log.IsErrorEnabled) // log.Error("Money Log Error:" + ex.ToString()); } finally { sqlbulk.Close(); dt.Clear(); } return(result); }
public void InsertTenThousandAlphabets1() { using (var entities = new Entities()) { var insertTime = new Stopwatch(); var alphabets = Enumerable.Range(0, 10000).Select(i => new Alphabet { A = "A", B = "B", C = "C", D = "D", E = "E", F = "F", G = "G", H = "H", I = "I", J = "J", K = "K", L = "L", M = "M", N = "N", O = "O", P = "P", Q = "Q", R = "R", S = "S", T = "T", U = "U", V = "V", W = "W", X = "X", Y = "Y", Z = "Z" }).ToList(); insertTime.Start(); var sqlBulkCopy = new SqlBulkCopy(entities.SqlConnection) { DestinationTableName = "Alphabets", ColumnMappings = {{"A", "A"}, {"B", "B"}, {"C", "C"}, {"D", "D"}, {"E", "E"}, {"F", "F"}, {"G", "G"}, {"H", "H"}, {"I", "I"}, {"J", "J"}, {"K", "K"}, {"L", "L"}, {"M", "M"}, {"N", "N"}, {"O", "O"}, {"P", "P"}, {"Q", "Q"}, {"R", "R"}, {"S", "S"}, {"T", "T"}, {"U", "U"}, {"V", "V"}, {"W", "W"}, {"X", "X"}, {"Y", "Y"}, {"Z", "Z"}} }; using (var dataReader = new ObjectDataReader<Alphabet>(alphabets)) { sqlBulkCopy.WriteToServer(dataReader); } sqlBulkCopy.Close(); insertTime.Stop(); double actualInsertTimePerAlphabet = (double)insertTime.ElapsedMilliseconds / alphabets.Count(); Debug.WriteLine("Insert Time Milliseconds Per Row: " + actualInsertTimePerAlphabet); } }
// Add your own data access methods. // This file should not get overridden /// <summary> /// Executes the BulkCopy process to load Employee Information from IVantage to a local Table /// </summary> /// <param name="allEmployees">IDataReader that contains all Employees loaded from IVantage</param> public void BulkCopyAllEmployees(IDataReader allEmployees) { try { Database db = DatabaseFactory.CreateDatabase("OneSourceConnectionString"); db.ExecuteNonQuery(CommandType.Text, "truncate table CS_Employee_Load"); } catch (Exception ex) { allEmployees.Close(); // Close DataReader when an exception occurs throw new Exception("An error ocurred while truncating data from Employee Info (load).", ex); } string connString = ConfigurationManager.ConnectionStrings["OneSourceConnectionString"].ConnectionString; using (SqlBulkCopy copy = new SqlBulkCopy(connString)) { try { copy.DestinationTableName = "CS_Employee_Load"; copy.BulkCopyTimeout = 600; copy.WriteToServer(allEmployees); } catch (Exception ex) { throw new Exception("An error ocurred when importing Employee Info (BulkCopy).", ex); } finally { allEmployees.Close(); } } }
/// <summary> /// time=2018.9.17 /// </summary> /// <param name="savePath">文件的完整路径(包括扩展名)</param> /// <param name="destinationTableName">目标数据库表名</param> /// <returns>如果成功插入,返回true</returns> public static bool SqlBulkCopyToDB(string savePath, string destinationTableName) { DataTable ds = new DataTable(); string connectionString = ConfigurationManager.ConnectionStrings["WulinConnection"].ConnectionString; using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) // 使用using 该链接在最后会自动关闭 { ds = GetExcelDatatable(savePath); //调用自定义方法 //bcp.BatchSize = 100;//每次传输的行数 //bcp.NotifyAfter = 100;//进度提示的行数 // bcp.DestinationTableName = "Tb";//需要导入的数据库表名 bcp.DestinationTableName = destinationTableName; //需要导入的数据库表名 try { //excel表头与数据库列对应关系 for (int i = 0; i < ds.Columns.Count; ++i) { //string s = ds.Columns[i].ColumnName; bcp.ColumnMappings.Add(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName); // 设置cxcel表中列名与数据库中表列名的映射关系 sqlTableName[i]中存的时数据库表中的各个字段 } bcp.WriteToServer(ds); return(true); //Response.Write("<script>alert('Excle表导入成功!')</script>"); //不能成功导入时,对用户进行提示 } catch (Exception ex) { Console.WriteLine(ex.Message); return(false); //Response.Write("<script>alert('Excle表导入失败!');</script>"); } } }
public bool GuardarDataTable(DataTable usuariosMasivos) { bool guardado = false; using (SqlConnection connection = new SqlConnection(_conString)) { connection.Open(); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection)) { sqlBulkCopy.DestinationTableName = "dbo.LlamadasCall"; try { sqlBulkCopy.WriteToServer(usuariosMasivos); guardado = true; } catch (Exception) { throw; } } } return guardado; }
/// <summary> /// splbulkcopy函数 /// </summary> public static void SqlBCP(string ConnString, DataTable dt, string destablename) { SqlBulkCopy sqlBulkCopyMobile = new SqlBulkCopy(ConnString); sqlBulkCopyMobile.DestinationTableName = destablename; sqlBulkCopyMobile.WriteToServer(dt); sqlBulkCopyMobile.Close(); }
public static bool UpdateBulkRouteEquipment(SqlCommand cmd, DataSources.dsData.EquipmentDataTable BulkTable) { bool outPut = false; try { SqlBulkCopy bulkCopy = new SqlBulkCopy(cmd.Connection); bulkCopy.ColumnMappings.Clear(); bulkCopy.ColumnMappings.Add("EquipmentId", "EquipmentId"); bulkCopy.ColumnMappings.Add("SerialNumber", "SerialNumber"); bulkCopy.ColumnMappings.Add("Material", "Material"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.ColumnMappings.Add("FunctionalId", "FunctionalId"); bulkCopy.ColumnMappings.Add("FunctionalLocation", "FunctionalLocation"); bulkCopy.ColumnMappings.Add("ValidFrom", "ValidFrom"); bulkCopy.ColumnMappings.Add("InventoryNo", "InventoryNo"); bulkCopy.ColumnMappings.Add("ConstructYear", "ConstructYear"); bulkCopy.ColumnMappings.Add("BrandId", "BrandId"); bulkCopy.ColumnMappings.Add("userin", "userin"); bulkCopy.ColumnMappings.Add("datein", "datein"); bulkCopy.DestinationTableName = BulkTable.TableName; bulkCopy.BatchSize = BulkTable.Count; //deleting data before saving new 1 cmd.CommandText = "delete from Equipment"; cmd.ExecuteNonQuery(); //saving bulk bulkCopy.WriteToServer(BulkTable); outPut = true; } catch (SqlException ex) { Logger.Error("Error while trying to save Equipment Bulk - " + ex.Message, ex); } return outPut; }
public override bool BulkInsert(DbTable schema, MungedDataReader reader) { using (var cn = _getConnection()) { using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { //copy.ColumnMappings = new SqlBulkCopyColumnMappingCollection(); for (var i = 0; i < reader.ColumnNames.Length; i++) { var column = reader.ColumnNames[i]; var sourceOrdinal = i; var destinationOrdinal = schema.Columns.FindIndex(x => x.Name == column); if (destinationOrdinal == -1) { var msg = string.Format("Unable to resolve column mapping, column: {0} was not found in destination table {1}", column, _table ); throw new Exception(msg); } copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, destinationOrdinal)); } copy.DestinationTableName = string.Format("[{0}].[{1}]", _schema, _table); copy.BatchSize = 1000; copy.BulkCopyTimeout = 9999999; copy.WriteToServer(reader); } } return true; }
protected void btnSend_Click(object sender, EventArgs e) { //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=SQL-SERVER;Initial Catalog=schoolerpsoft ;User ID=sa;Password=techmodi123"; //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=199.79.62.22;Initial Catalog=fashvash_inifdvashi ;User ID=inifd_user;Password=inifd!@#"; //String strConnection = "Integrated Security=false;Persist Security Info=False;Data Source=TWS-ERPSERVER\\SQLEXPRESS;Initial Catalog=AsnASoftwaresolutionDB"; String strConnection = "Integrated Security=SSPI;Persist Security Info=False;Data Source=TWS-ERPSERVER\\SQLEXPRESS;Initial Catalog=AsnASoftwaresolutionDB"; //file upload path if (fileuploadExcel.HasFile) { string path = fileuploadExcel.PostedFile.FileName; path = Server.MapPath("~") + "/" + fileuploadExcel.PostedFile.FileName; fileuploadExcel.SaveAs(path); //Create connection string to Excel work book string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False"; //Create Connection to Excel work book OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); //Create OleDbCommand to fetch data from Excel // OleDbCommand cmd = new OleDbCommand("Select [AD_AddressId],[AD_PermanentAddress],[AD_OfficeAddress] ,[AD_CommercialAddress] ,[AD_OptionalAddress],[AD_LandLineNo],[AD_OfficeNo],[AD_MobileNo],[AD_MobileOptionalNo],[AD_EmailId],[AD_PinCode],[AD_ZipCode],[AD_CountryId],[AD_StateId],[AD_CityId],[AD_AddressDate],[AD_Status] from [Sheet1$]", excelConnection); // OleDbCommand cmd = new OleDbCommand("Select [SA_ApplicationId],[SA_StudentPNRId],[SA_FirstName] ,[SA_MiddleName] ,[SA_LastName],[SA_DateOfBirth],[SA_PlaceOfBirth],[SA_AcadamicYear],[SA_BloodGroup],[SA_StudentImage],[SA_Nationality],[SA_Gender],[SA_Caste],[SA_SubCaste],[SA_DateOfApplication],[SA_MotherTounge],[SA_SchoolCategoryId] ,[SA_TransportStatus] ,[SA_MedicalProblem],[SA_LastSchoolAttended],[SA_LastClassAttended],[SA_LastClassPer],[SA_FatherFullName],[SA_FatherPhoto],[SA_FatherEduQualification],[SA_FatherOccupation],[SA_FatherAddressId],[SA_FatherOrgName],[SA_FatherDsgtName],[SA_MotherFullName],[SA_MotherPhoto],[SA_MotherEduQualification],[SA_MotherOccupation],[SA_MotherAddressId],[SA_MotherOrgName],[SA_MotherDsgtName] ,[SA_AddressId] ,[SA_AgeProof],[SA_LastProgressReportCard],[SA_OriginalSchoolLC],[SA_PassportAndVisa],[SA_RegistrationLetter],[SA_ApplicationStatus],[SA_Status] from [Sheet1$]", excelConnection); //OleDbCommand cmd = new OleDbCommand("Select [UL_LoginId],[UL_UserName],[UL_UserPassword],[UL_EmailId],[UL_UserType],[UL_CreatedBy],[UL_ModifiedBy],[UL_CreatedDate],[UL_ModifiedDate],[UL_LastLoginDate],[UL_Status] from [Sheet1$]", excelConnection); OleDbCommand cmd = new OleDbCommand("Select [FD_FacultyDetailsId],[FD_LoginId],[FD_FacultyId],[FD_SchoolCatgoryId],[FD_Title],[FD_FirstName],[FD_MiddleName],[FD_LastName],[FD_Date],[FD_Caste],[FD_SubCaste],[FD_BirthPlace],[FD_BloodGroup],[FD_MotherTounge],[FD_Nationality],[FD_TransportStatus],[FD_MedicalProblem],[FD_SchoolCategoryId],[FD_SchoolSubCategoryId],[FD_SubjectId],[FD_InterestPosition],[FD_CertifiedSubject],[FD_CertifiedAgeGroup],[FD_FullTimeTeaching],[FD_IndustryExpertise],[FD_SpecificExpertise],[FD_HighestDegreeCompletd],[FD_FieldOfStudy],[FD_University],[FD_CompletionDate],[FD_AddressId],[FD_QualificationDetails],[FD_ExperienceFrom],[FD_ExperienceTo],[FD_ExperienceInYrs],[FD_Image],[FD_JoiningDate],[FD_BirthDate],[FD_Gender],[FD_MarrialStatus],[FD_AgeProof],[FD_Resume],[FD_ReleavingLetter],[FD_RetiredOn],[FD_ExtraOne],[FD_CreatedBy],[FD_LastSchoolwas],[FD_LastSchoolFromDate],[FD_LastSchoolToDate],[FD_Status] from [Sheet1$]", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); //Give your Destination table name //sqlBulk.DestinationTableName = "TMS_AddressDetails"; sqlBulk.DestinationTableName = "TMS_FacultyDetails"; //sqlBulk.DestinationTableName = "TMS_UserLogin"; sqlBulk.WriteToServer(dReader); excelConnection.Close(); } }
public void importLCFC_MBBOMUsingADO(string sheetName, string tableName) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.Text + ";Extended Properties=Excel 12.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); //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(Conlist.ConStr)) { // bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 1000; //每次传输的行数 bcp.NotifyAfter = 1000; //进度提示的行数 bcp.DestinationTableName = tableName; //目标表 //客户机器物料编号 机型 配置说明 类别 说明 厂商料号 客户料号 classcode 数据说明 数量 用途 厂商型号 硬盘数量 SSD数量 内存数量 机器颜色 bcp.ColumnMappings.Add("SKU_LNO", "SKU_LNO"); bcp.ColumnMappings.Add("SKU_NO", "SKU_NO"); bcp.ColumnMappings.Add("KEY_TOPIC", "KEY_TOPIC"); bcp.ColumnMappings.Add("TOPIC_ITEM", "TOPIC_ITEM"); bcp.ColumnMappings.Add("ITEM_DESCRIPTION", "ITEM_DESCRIPTION"); bcp.ColumnMappings.Add("LCFC_PN", "LCFC_PN"); bcp.ColumnMappings.Add("LNV_PN", "LNV_PN"); bcp.ColumnMappings.Add("KP_QTY", "KP_QTY"); bcp.ColumnMappings.Add("KP_REL", "KP_REL"); bcp.ColumnMappings.Add("REMARK", "REMARK"); bcp.ColumnMappings.Add("INDICATOR_TYPE", "INDICATOR_TYPE"); bcp.ColumnMappings.Add("PARENT_PN", "PARENT_PN"); bcp.ColumnMappings.Add("GRAND_PN", "GRAND_PN"); bcp.ColumnMappings.Add("ACTION_TYPE", "ACTION_TYPE"); bcp.ColumnMappings.Add("STEP_SEQUENCE", "STEP_SEQUENCE"); bcp.WriteToServer(ds.Tables[0]); bcp.Close(); conn.Close(); MessageBox.Show("导入完成"); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
public int MergeToServer(List <OutputColumn> OutputColumns) { int result = -1; using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(Connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock, Transaction)) { string destination = GetSynonymBase(DestinationTableName); bulkCopy.DestinationTableName = "#tmpBCP"; bulkCopy.BatchSize = BatchSize; bulkCopy.BulkCopyTimeout = QueryTimeout; foreach (string dbcol in GetColumns(DestinationTableName)) { if (Table.Columns.Contains(dbcol)) { bulkCopy.ColumnMappings.Add(dbcol, dbcol); } } using (SqlCommand cmd = Connection.CreateCommand()) { cmd.CommandText = String.Format("SELECT TOP(0) * INTO #tmpBCP FROM {0}", destination); cmd.ExecuteNonQuery(); } bulkCopy.WriteToServer(Table); // Merge data using (SqlCommand cmd = Connection.CreateCommand()) { string sql = @" MERGE INTO {0} AS dest USING #tmpBCP AS src ON {1} WHEN MATCHED THEN UPDATE SET {2} WHEN NOT MATCHED THEN INSERT ({3}) VALUES ({4}); "; string _0 = destination; string _1 = String.Join(" AND ", OutputColumns.Where(s => !(s is AggregatedOutputColumn) && !(s.Hidden)).Select(s => "(src." + s.Name + " = dest." + s.Name + " OR (src." + s.Name + " IS NULL AND dest." + s.Name + " IS NULL))")); string _2 = String.Join(", ", OutputColumns.Where(s => s is AggregatedOutputColumn).Select(s => s.Alias + " = " + BuildMergeSetClause((AggregatedOutputColumn)s))); string _3 = String.Join(", ", OutputColumns.Where(s => !s.Hidden).Select(s => s is AggregatedOutputColumn ? s.Alias : s.Name)); string _4 = String.Join(", ", OutputColumns.Where(s => !s.Hidden).Select(s => "src." + (s is AggregatedOutputColumn ? s.Alias : s.Name))); sql = String.Format(sql, _0, _1, _2, _3, _4); cmd.CommandText = sql; result = cmd.ExecuteNonQuery(); } } return(result); }
static void Main(string[] args) { var time = new DateTime(); string connectionString = GetConnectionString(); // Open a connection to the AdventureWorks database. using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Perform an initial count on the destination table. SqlCommand commandRowCount = new SqlCommand( "SELECT COUNT(*) FROM " + "dbo.BulkCopyDemoMatchingColumns;", connection); long countStart = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Starting row count = {0}", countStart); // Create a table with some rows. DataTable newProducts = MakeTable(); // Create the SqlBulkCopy object. // Note that the column positions in the source DataTable // match the column positions in the destination table so // there is no need to map columns. using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"; try { // Write from the source to the destination. var b = DateTime.Now.Second; bulkCopy.WriteToServer(newProducts); var a = DateTime.Now.Second; Console.WriteLine("bat dau:" + b); Console.WriteLine("Tổng thời gian Lưu:" + a); } catch (Exception ex) { Console.WriteLine(ex.Message); } } // Perform a final count on the destination // table to see how many rows were added. long countEnd = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Ending row count = {0}", countEnd); Console.WriteLine("{0} rows were added.", countEnd - countStart); Console.WriteLine("Press Enter to finish."); Console.ReadLine(); } }
public string InsertTempList(IList <decimal> objectIDList) { string operationGUID = string.Empty; DataTable tempTable = new DataTable(); try { using (NHibernateSessionManager.Instance.BeginTransactionOn()) { tempTable = new DataTable(); tempTable.Columns.Add("ObjectID", typeof(decimal)); tempTable.Columns.Add("OperationGUID", typeof(string)); tempTable.Columns.Add("CreationDate", typeof(string)); operationGUID = Guid.NewGuid().ToString(); DateTime creationDate = DateTime.Now; foreach (decimal objectID in objectIDList) { DataRow drTemp = tempTable.NewRow(); drTemp["ObjectID"] = objectID; drTemp["OperationGUID"] = operationGUID; drTemp["CreationDate"] = creationDate; tempTable.Rows.Add(drTemp); } SqlBulkCopy TempBulkInsert = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)NHibernateSessionManager.Instance.GetSession().Connection, SqlBulkCopyOptions.Default, (SqlTransaction)NHibernateSessionManager.Instance.GetTransaction().GetDbTransaction); TempBulkInsert.DestinationTableName = "dbo.TA_Temp"; TempBulkInsert.ColumnMappings.Add("ObjectID", "temp_ObjectID"); TempBulkInsert.ColumnMappings.Add("OperationGUID", "temp_OperationGUID"); TempBulkInsert.ColumnMappings.Add("CreationDate", "temp_CreationDate"); TempBulkInsert.WriteToServer(tempTable); NHibernateSessionManager.Instance.CommitTransactionOn(); } return(operationGUID); } catch (Exception) { NHibernateSessionManager.Instance.RollbackTransactionOn(); try { SqlBulkCopy TempBulkInsert = new System.Data.SqlClient.SqlBulkCopy(NHibernateSessionManager.Instance.SessionFactoryPropsDic["connection.connection_string"], SqlBulkCopyOptions.Default); TempBulkInsert.DestinationTableName = "dbo.TA_Temp"; TempBulkInsert.ColumnMappings.Add("ObjectID", "temp_ObjectID"); TempBulkInsert.ColumnMappings.Add("OperationGUID", "temp_OperationGUID"); TempBulkInsert.ColumnMappings.Add("CreationDate", "temp_CreationDate"); TempBulkInsert.WriteToServer(tempTable); } catch (Exception exe) { throw exe; } return(operationGUID); } }
public void importLCFC_MBBOMUsingADO(string sheetName, string tableName) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.Text + ";Extended Properties=Excel 12.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); //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(Constlist.ConStr)) { // bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 10000; //每次传输的行数 bcp.NotifyAfter = 10000; //进度提示的行数 bcp.DestinationTableName = tableName; //目标表 bcp.ColumnMappings.Add("日期", "_date"); bcp.ColumnMappings.Add("厂商", "vendor"); bcp.ColumnMappings.Add("客户别", "product"); bcp.ColumnMappings.Add("MB简称", "mb_brief"); bcp.ColumnMappings.Add("MPN", "MPN"); bcp.ColumnMappings.Add("材料MPN", "material_mpn"); bcp.ColumnMappings.Add("料盒位置", "material_box_place"); bcp.ColumnMappings.Add("物料描述", "material_describe"); bcp.ColumnMappings.Add("用料数量", "material_num"); bcp.ColumnMappings.Add("L1", "L1"); bcp.ColumnMappings.Add("L2", "L2"); bcp.ColumnMappings.Add("L3", "L3"); bcp.ColumnMappings.Add("L4", "L4"); bcp.ColumnMappings.Add("L5", "L5"); bcp.ColumnMappings.Add("L6", "L6"); bcp.ColumnMappings.Add("L7", "L7"); bcp.ColumnMappings.Add("L8", "L8"); bcp.WriteToServer(ds.Tables[0]); bcp.Close(); conn.Close(); MessageBox.Show("导入完成"); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
public void TransferData(string excelFile, string sheetName, string connectionString) { DataSet ds = new DataSet(); try { string fileExt = Path.GetExtension(excelFile); string connStr = ""; if (fileExt == ".xls") { connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; } else { connStr = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; } //获取全部数据 //string strConn = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 12.0;"; OleDbConnection conn = new OleDbConnection(connStr); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, connStr); 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(1000),", 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) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
public void InsertUsers(Stream stream) { var reader = _readerService.GetReader(stream); var connectionString = NitkaContext.CONNECTION_STRING; using (var loader = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { loader.DestinationTableName = "Users"; loader.WriteToServer(reader); } }
private void WriteDictionary(Dictionary <string, int> values, SqlConnection conn, SqlTransaction tran, string name) { // create a temporary table string sql = @" SELECT TOP(0) * INTO #{0} FROM [{1}].[{0}]; "; sql = String.Format(sql, name, ConnectionInfo.SchemaName); using (SqlCommand cmd = conn.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } // bulk insert into temporary using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock, tran)) { bulkCopy.DestinationTableName = "#" + name; bulkCopy.BatchSize = 1000; bulkCopy.BulkCopyTimeout = 300; bulkCopy.WriteToServer(DataUtils.ToDataTable(from t in values select new { t.Value, t.Key })); } // merge new data sql = @" INSERT INTO [{1}].[{0}s] SELECT * FROM #{0}s AS src WHERE NOT EXISTS ( SELECT * FROM [{1}].[{0}s] AS dst WHERE dst.[{0}_id] = src.[{0}_id] ); "; sql = String.Format(sql, name.Substring(0, name.Length - 1), ConnectionInfo.SchemaName); using (SqlCommand cmd = conn.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } }
private void WriteNormalizedQueries(Dictionary <long, NormalizedQuery> values, SqlConnection conn, SqlTransaction tran) { // create a temporary table string sql = @" SELECT TOP(0) * INTO #NormalizedQueries FROM [{0}].[NormalizedQueries]; "; sql = String.Format(sql, ConnectionInfo.SchemaName); using (SqlCommand cmd = conn.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } // bulk insert into temporary using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock, tran)) { bulkCopy.DestinationTableName = "#NormalizedQueries"; bulkCopy.BatchSize = 1000; bulkCopy.BulkCopyTimeout = 300; bulkCopy.WriteToServer(DataUtils.ToDataTable(from t in values select new { t.Value.Hash, t.Value.NormalizedText, t.Value.ExampleText })); } // merge new data sql = @" INSERT INTO [{0}].[NormalizedQueries] SELECT * FROM #NormalizedQueries AS src WHERE NOT EXISTS ( SELECT * FROM [{0}].[NormalizedQueries] AS dst WHERE dst.[sql_hash] = src.[sql_hash] ); "; sql = String.Format(sql, ConnectionInfo.SchemaName); using (SqlCommand cmd = conn.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } }
public void importUserInfo(string sheetName, string tableName) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.Text + ";Extended Properties=Excel 12.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); //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(Constlist.ConStr)) { // bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 1000; //每次传输的行数 bcp.NotifyAfter = 1000; //进度提示的行数 bcp.DestinationTableName = tableName; //目标表 bcp.ColumnMappings.Add("用户名", "username"); bcp.ColumnMappings.Add("工号", "workId"); bcp.ColumnMappings.Add("默认密码", "_password"); bcp.ColumnMappings.Add("超级管理员", "super_manager"); bcp.ColumnMappings.Add("BGA", "bga"); bcp.ColumnMappings.Add("Repair", "repair"); bcp.ColumnMappings.Add("Test_all", "test_all"); bcp.ColumnMappings.Add("Test1", "test1"); bcp.ColumnMappings.Add("Test2", "test2"); bcp.ColumnMappings.Add("receive_return", "receive_return"); bcp.ColumnMappings.Add("store", "store"); bcp.ColumnMappings.Add("outlook", "outlook"); bcp.ColumnMappings.Add("running", "running"); bcp.ColumnMappings.Add("obe", "obe"); bcp.WriteToServer(ds.Tables[0]); bcp.Close(); conn.Close(); MessageBox.Show("导入完成"); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
/// <summary> /// Insert một dữ liệu lớn đổ 1 table vào csdl /// </summary> /// <param name="tablename">Tên bảng cần insert</param> /// <param name="table">Bảng dữ liệu cần insert</param> public void Bulk_Insert(string tablename, DataTable table) { using (var connection = _connect.GetConnect()) { connection.Open(); using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "dbo." + tablename; bulkCopy.WriteToServer(table); } connection.Close(); } }
public void BulkInsert <T>(List <T> data) { using (var sqlCopy = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { var metaData = context.Model.FindEntityType(typeof(T)).Relational(); sqlCopy.DestinationTableName = $"{metaData.Schema}.{metaData.TableName}"; sqlCopy.BatchSize = 500; var table = CreateDataTable(data); sqlCopy.WriteToServer(table); } }
private void WriteWaitsData(SqlConnection conn, SqlTransaction tran, int current_interval_id) { if (waitsData == null) { return; } lock (waitsData) { using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock, tran)) { bulkCopy.DestinationTableName = "[" + ConnectionInfo.SchemaName + "].[WaitStats]"; bulkCopy.BatchSize = 1000; bulkCopy.BulkCopyTimeout = 300; var Table = from t in waitsData.AsEnumerable() group t by new { wait_type = t.Field <string>("wait_type") } into grp select new { interval_id = current_interval_id, grp.Key.wait_type, wait_sec = grp.Sum(t => t.Field <double>("wait_sec")), resource_sec = grp.Sum(t => t.Field <double>("resource_sec")), signal_sec = grp.Sum(t => t.Field <double>("signal_sec")), wait_count = grp.Sum(t => t.Field <double>("wait_count")) }; using (var dt = DataUtils.ToDataTable(Table)) { bulkCopy.WriteToServer(dt); } logger.Info("Wait stats written"); } waitsData.Dispose(); waitsData = null; } }
public static void WriteDataTable( String ServerInstance, String Database, String TableName, DataTable Data, int BatchSize, int QueryTimeout, int ConnectionTimeout ) { String ConnectionString; ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}"; ConnectionString = String.Format(ConnectionString, ServerInstance, Database, ConnectionTimeout); using (SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock)) { bulkCopy.DestinationTableName = TableName; bulkCopy.BatchSize = BatchSize; bulkCopy.BulkCopyTimeout = QueryTimeout; // enters this code block when using this collector type for system collection sets // the database_name column is not added automatically by the "Generic T-SQL Query Collector" // so we have to ignore its "__database_name" counterpart // added automatically when the "database_name" column (no underscores) already exists if (TableName.StartsWith("[snapshots].")) { foreach (string dbcol in getColumns(ServerInstance, Database, TableName)) { if (Data.Columns.Contains("__" + dbcol)) { bulkCopy.ColumnMappings.Add("__" + dbcol, dbcol); } else if (Data.Columns.Contains(dbcol)) { bulkCopy.ColumnMappings.Add(dbcol, dbcol); } } } bulkCopy.WriteToServer(Data); } }
private System.Data.SqlClient.SqlBulkCopy CreateBulkCopyOperation(bool useTableLock) { var retVal = new System.Data.SqlClient.SqlBulkCopy(_ConnectionString, useTableLock ? System.Data.SqlClient.SqlBulkCopyOptions.TableLock : System.Data.SqlClient.SqlBulkCopyOptions.Default); retVal.DestinationTableName = _TableName; if (_ColumnMappings != null) { foreach (var kvp in _ColumnMappings) { retVal.ColumnMappings.Add(kvp.Key, kvp.Value); } } return(retVal); }
public void Transdata(string sheetName, string table, string filenameurl, string tbname) { string ojbk = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; '"; OleDbConnection conn = new OleDbConnection(ojbk); DataSet ds = new DataSet(); string exclstr = ""; OleDbDataAdapter odda = null; exclstr = string.Format("select * from [{0}$]", sheetName); conn.Open(); odda = new OleDbDataAdapter(exclstr, conn); odda.Fill(ds, table); conn.Close(); int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示 } else { string strSql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", tbname); foreach (System.Data.DataColumn c in ds.Tables[0].Columns)// Columns 获取属于该表的列的集合。 { strSql += string.Format("[{0}] varchar(255),", c.ColumnName); } strSql = strSql.Trim(',') + ")";//去掉末项的逗号 SqlConnection sqlconn = new SqlConnection(strConn); sqlconn.Open(); SqlCommand updata = new SqlCommand(strSql, sqlconn); updata.ExecuteNonQuery(); sqlconn.Close(); using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(strConn)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.BatchSize = 100; //每次传输的行数 bcp.NotifyAfter = 100; //进度提示的行数 bcp.DestinationTableName = sheetName; //目标表 bcp.WriteToServer(ds.Tables[0]); } void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e) { } } Response.Write("<script>alert('Excle表导入成功!');location='jxqd.aspx'</script>"); }
/// <summary> /// 按照SqlBulkCopy写入数据库 /// </summary> /// <param name="dt">数据表</param> /// <param name="tableName">数据库表名</param> public void WriteDataBySqlBulkCopy(DataTable dt, string tableName, DbConnection dbCon = null) { //判空 if (dt == null || dt.Rows.Count == 0 || string.IsNullOrEmpty(tableName)) { return; } SqlConnection con = dbCon as SqlConnection; if (con == null) { MessageBox.Show("传入的dbCon不是SqlConnection对象", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (con.State != ConnectionState.Open) { con.Open(); } //此部分是重要的东西 using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(con)) { try { for (int i = 0; i < dt.Columns.Count; i++) { sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); //设置目标表和源数据的列映射 } sbc.DestinationTableName = tableName; //取得目标表名 sbc.WriteToServer(dt); } catch (Exception ex) { //MessageBox.Show("ex", "提示", MessageBoxButton.OK, MessageBoxImage.Information); throw ex; } finally { //if (con.State == ConnectionState.Open) //{ // con.Close(); //} //else //{ // //MessageBox.Show("数据库已经关闭", "提示", MessageBoxButton.OK, MessageBoxImage.Information); //} } } }
public void BulkInsert(string connectionString, string nomeTabela, DataTable dataTable) { using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { bulkCopy.BatchSize = dataTable.Rows.Count; bulkCopy.DestinationTableName = nomeTabela; bulkCopy.BulkCopyTimeout = 0; foreach (DataColumn col in dataTable.Columns) { bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulkCopy.WriteToServer(dataTable); } }
/// <summary> /// 批量添加数据到数据库 /// </summary> /// <param name="dtSource">数据源,该数据结构必须和TableName的数据表格式保持一致</param> /// <param name="TableName">表名</param> /// <param name="BatchSize"></param> /// <returns></returns> public static int BatchInsertSqlData(DataTable dtSource, string TableName, int BatchSize = 500) { using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { sqlBC.BatchSize = BatchSize; sqlBC.DestinationTableName = TableName; if (dtSource != null && dtSource.Rows.Count > 0) { sqlBC.WriteToServer(dtSource); } sqlBC.Close(); return(dtSource.Rows.Count); } }