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