/// <param name="savePath">文件的完整路径（包括扩展名）</param>
/// <param name="destinationTableName">目标数据库表名</param>
/// <returns>如果成功插入，返回true</returns>
public bool SqlBulkCopyToDB(string savePath, string destinationTableName)
{
DataTable ds = new DataTable();
string connectionString = "server=.; database=CSMP; uid=sa; pwd= 123456";
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; //需要导入的数据库表名
string[] sqlTableName = { "courseId", "courseName", "semester" };
try
{
//excel表头与数据库列对应关系
for (int i = 0; i < ds.Columns.Count; ++i)
{
//string s = ds.Columns[i].ColumnName;
bcp.ColumnMappings.Add(ds.Columns[i].ColumnName, sqlTableName[i]); // 设置excel表中列名与数据库中表列名的映射关系 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>");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//1.0
string str = "server=.;database=test;uid=sa;pwd=master;";
//2.0 构造一个内存表
DataTable dt = new DataTable();
dt.Columns.Add("name1", typeof(string));
//2.0.1 给内存表增加1w条数据
for (int i = 0; i < 10000; i++)
{
DataRow dr = dt.NewRow();
dr["name1"] = "test" + i;
dt.Rows.Add(dr);
}
System.Diagnostics.Stopwatch st = new System.Diagnostics.Stopwatch();
st.Start();
//3.0 实例化SqlBulkCopy对象实例
using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(str))
{
//将内存表中的列名称与数据表的列名称做一一映射
copy.ColumnMappings.Add("name1", "CName");
//指定要存入的数据表名称
copy.DestinationTableName = "tb1";
//调用WriteToServer 方法将数据批量的插入到指定的表中
copy.WriteToServer(dt);
}
st.Stop();
MessageBox.Show("批量新增1W条数据耗时=" + st.ElapsedMilliseconds + "毫秒");
}
public static bool BulkInsertDataTable(System.Data.DataTable dt, string tableName, string _connectionString)
{
bool IsSuccess = false;
try
{
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(_connectionString))
{
con.Open();
//TODO: See if using TableLock is good, bad, or does not matter.
using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(con, System.Data.SqlClient.SqlBulkCopyOptions.TableLock, null))
{
bc.DestinationTableName = tableName;
bc.BatchSize = dt.Rows.Count;
bc.BulkCopyTimeout = 0;
bc.WriteToServer(dt);
}
IsSuccess = true;
logger.ConditionalDebug(string.Format("Batch Complete"));
}
}
catch (Exception ex)
{
logger.Error(string.Format("BulkInsertDataTable DataTable : {0}", dt.Rows.Count.ToString()));
logger.Error(string.Format("BulkInsertDataTable Error : {0}", ex.Message));
if (ex.InnerException != null)
{
logger.Error(string.Format("BulkInsertDataTable Inner : {0}", ex.InnerException.Message));
}
throw ex;
}
return(IsSuccess);
}
public void BulkCopy(System.Data.DataTable dt)
{
System.Data.SqlClient.SqlBulkCopy bulk = null;
try
{
bulk = new System.Data.SqlClient.SqlBulkCopy(this.connection_string, System.Data.SqlClient.SqlBulkCopyOptions.UseInternalTransaction);
bulk.BulkCopyTimeout = 9999;
bulk.BatchSize = 50000;
bulk.DestinationTableName = dt.TableName;
foreach (DataColumn col in dt.Columns)
{
bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulk.WriteToServer(dt);
}
catch (Exception ex)
{
throw;
}
finally
{
if (bulk != null)
{
bulk.Close();
}
}
}
public ActionResult Import(HttpPostedFileBase file)
{
var cnnstr = System.Data.SqlClient.ConnectionStringHelper.GetProviderConnectionString();
var textReader = new System.IO.StreamReader(file.InputStream);
using (var csvreqder = new CsvHelper.CsvReader(textReader))
{
csvreqder.Configuration.SkipEmptyRecords = true;
var sqlcnn = (System.Data.SqlClient.SqlConnection)((System.Data.EntityClient.EntityConnection)db.Connection).StoreConnection;
sqlcnn.Open();
ExecuteSqlCommandText(sqlcnn, "create table #resources ([Culture] varchar(5), [Key] varchar(100), [Value] nvarchar(4000));");
using (var bcp = new System.Data.SqlClient.SqlBulkCopy(sqlcnn))
{
bcp.DestinationTableName = "tempdb..#resources";
bcp.ColumnMappings.Add("Culture", "Culture");
bcp.ColumnMappings.Add("Key", "Key");
bcp.ColumnMappings.Add("Value", "Value");
var records = csvreqder.GetRecords <Resource>();
var irdf = CC.Web.Helpers.idrf.GetReader(records);
bcp.WriteToServer(irdf);
}
var cmd = "merge dbo.resources as t using (select distinct * from #resources) as s on t.[Culture] = s.[Culture] and t.[Key] = s.[Key]";
cmd += "when not matched by target then insert ([Culture], [Key], [Value]) values (lower(ltrim(rtrim(s.[Culture]))), ltrim(rtrim(s.[Key])), ltrim(rtrim(s.[Value])))";
cmd += "when matched and t.[Value] <> s.[Value] then update set [Value] = s.[Value];";
ExecuteSqlCommandText(sqlcnn, cmd);
sqlcnn.Close();
Resources.Resource.InvalidateCache();
}
return(RedirectToAction("Index"));
}
public static bool InsertEntitiesNew(string TableName, List <EmailLog> objList)
{
bool status = false;
try
{
workorderEMSEntities context = new workorderEMSEntities();
var cs = context.Database.Connection.ConnectionString;
System.Data.SqlClient.SqlBulkCopy bulkInsert = new System.Data.SqlClient.SqlBulkCopy(cs);
bulkInsert.DestinationTableName = TableName;
using (MyGenericDataReader <EmailLog> dataReader = new
MyGenericDataReader <EmailLog>(objList))
{
bulkInsert.WriteToServer(dataReader);
status = true;
}
}
catch (Exception)
{
status = false;
throw;
}
return(status);
}
public bool SqlBulkCopy(DataTable dt, int batchSize, out string err)
{
err = null;
batchSize = batchSize < 1000 ? 1000 : batchSize;
if (ConnType != ConnectionType.MsSql)
{
throw new Exception("MsSqlBulkCopy 仅支持 MsSql");
}
try
{
using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(this.ConnStr))
{
sbc.BatchSize = batchSize;
sbc.BulkCopyTimeout = this.CommandTimeout;
sbc.DestinationTableName = dt.TableName;
foreach (DataColumn item in dt.Columns)
{
sbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
sbc.WriteToServer(dt);
}
return(true);
}
catch (Exception e)
{
err = e.Message;
return(false);
}
}
/// <summary>
/// 一次性将datatable数据导入数据表
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="table">表名</param>
/// <param name="colsString">表列名</param>
/// <param name="connectString">数据库连接字符串</param>
/// <returns></returns>
public Int32 DTToDB(DataTable dt, string table, string[] colsString, string connectString)
{
Int32 ret = 0;
try
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
System.Data.SqlClient.SqlBulkCopy sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectString);
sqlBulkCopy.DestinationTableName = table;
if (dt != null && dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
string sclname = dt.Columns[i].ColumnName;
// Set up the column mappings by name.
System.Data.SqlClient.SqlBulkCopyColumnMapping mapID = new System.Data.SqlClient.SqlBulkCopyColumnMapping(colsString[i], colsString[i]);
sqlBulkCopy.ColumnMappings.Add(mapID);
}
sqlBulkCopy.WriteToServer(dt);
ret = 1;
}
sqlBulkCopy.Close();
stopwatch.Stop();
}
catch (Exception ex)
{
throw ex;
}
return(ret);
}
public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
string fileName = Path.GetFileNameWithoutExtension(excelFile);
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}(", fileName);
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 = fileName; //目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
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 strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties = Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建,excel文件的第一行为列标题,从第二行开始全部都是数据记录
string strSql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", sheetName); //以sheetName为表名
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
//excel文件中列的顺序必须和数据表的列顺序一致，因为数据导入时，是从excel文件的第二行数据开始，不管数据表的结构是什么样的，反正就是第一列的数据会插入到数据表的第一列字段中，第二列的数据插入到数据表的第二列字段中，以此类推，它本身不会去判断要插入的数据是对应数据表中哪一个字段的
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 bool BatchInsert(EumDBWay dbWay, string dbName, DataTable table)
{
bool isSuccess = false;
if (string.IsNullOrEmpty(table.TableName))
{
throw new ArgumentNullException($"The DataTable TableName cannot be empty");
}
if (table.Columns.Count <= 0)
{
throw new ArgumentNullException($"The DataTable Columns cannot be empty");
}
if (table.Rows.Count <= 0)
{
throw new ArgumentNullException($"The DataTable Rows cannot be empty");
}
using (IDbConnection connection = IoC.Resolve <IMapper>().GetConnection(DataBaseHelper.GetConnectionStrings(dbWay, dbWay.GetDisplayName())))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (System.Data.SqlClient.SqlBulkCopy blukCopy = new System.Data.SqlClient.SqlBulkCopy((System.Data.SqlClient.SqlConnection)connection))
{
try
{
blukCopy.DestinationTableName = table.TableName;
foreach (DataColumn col in table.Columns)
{
blukCopy.ColumnMappings.Add(new System.Data.SqlClient.SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
}
//复制数据
blukCopy.WriteToServer(table);
isSuccess = true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
return(isSuccess);
}
public static Import Upload(HttpPostedFileWrapper file, int userId)
{
var newImport = NewMethod(userId);
var csvConf = new CsvHelper.Configuration.CsvConfiguration
{
SkipEmptyRecords = true,
IsCaseSensitive = false,
IsStrictMode = true
};
csvConf.ClassMapping <MasterIdImportCsvMap>();
var streamReader = new System.IO.StreamReader(file.InputStream);
using (var csvReader = new CsvHelper.CsvReader(streamReader, csvConf))
{
var data = csvReader.GetRecords <ImportClient>().Select((record, i) =>
new ImportClient
{
RowIndex = i,
ImportId = newImport.Id,
ClientId = record.ClientId,
MasterId = record.MasterId,
UpdatedAt = newImport.StartedAt,
UpdatedById = newImport.UserId,
});
string connectionString = System.Data.SqlClient.ConnectionStringHelper.GetProviderConnectionString();
var sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "ImportClients";
sqlBulkCopy.ColumnMappings.Add("ImportId", "ImportId");
sqlBulkCopy.ColumnMappings.Add("RowIndex", "RowIndex");
sqlBulkCopy.ColumnMappings.Add("ClientId", "ClientId");
sqlBulkCopy.ColumnMappings.Add("MasterId", "MasterId");
sqlBulkCopy.ColumnMappings.Add("UpdatedAt", "UpdatedAt");
sqlBulkCopy.ColumnMappings.Add("UpdatedById", "UpdatedById");
var dataReader = new IEnumerableDataReader <ImportClient>(data);
sqlBulkCopy.WriteToServer(dataReader);
sqlBulkCopy.Close();
}
return(newImport);
}
public void CopyData(DataTable SourceData, string tbName, string connectionString, string[][] mapCols = null)
{
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.DestinationTableName = tbName;
if (mapCols != null)
{
for (int i = 0; i < mapCols[0].Count(); i++)
{
bcp.ColumnMappings.Add(mapCols[0][i], mapCols[1][i]);
}
}
bcp.WriteToServer(SourceData);
}
}
public static void AddWorkTicketInfo(DataTable dt)
{
using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(DbHelperSQL.connection))
{
sqlBC.BatchSize = 100000;
sqlBC.BulkCopyTimeout = 60;
sqlBC.DestinationTableName = "dbo.WorkTicketInfo";
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlBC.ColumnMappings.Add(i, i);
}
sqlBC.WriteToServer(dt);
dt = null;
GC.Collect();
}
}
private void writeReRayToDb()
{
Console.WriteLine("start writing reRay to db...................................");
// 删除旧的reRay
Hashtable ht = new Hashtable();
ht["CI"] = this.cellInfo.CI;
ht["eNodeB"] = this.cellInfo.eNodeB;
IbatisHelper.ExecuteDelete("deleteSpecifiedReRay", ht);
System.Data.DataTable dtable = new System.Data.DataTable();
dtable.Columns.Add("ci");
dtable.Columns.Add("emitX");
dtable.Columns.Add("emitY");
dtable.Columns.Add("emitZ");
dtable.Columns.Add("pwrDbm");
dtable.Columns.Add("dirX");
dtable.Columns.Add("dirY");
dtable.Columns.Add("dirZ");
dtable.Columns.Add("type");
for (int i = 0; i < this.MultiTasksReRay.Count; i++)
{
System.Data.DataRow thisrow = dtable.NewRow();
thisrow["ci"] = this.cellInfo.CI;
thisrow["emitX"] = Math.Round(this.MultiTasksReRay[i].emitX, 3);
thisrow["emitY"] = Math.Round(this.MultiTasksReRay[i].emitY, 3);
thisrow["emitZ"] = Math.Round(this.MultiTasksReRay[i].emitZ, 3);
thisrow["pwrDbm"] = Math.Round(this.MultiTasksReRay[i].pwrDbm, 3);
thisrow["dirX"] = Math.Round(this.MultiTasksReRay[i].dirX, 4);
thisrow["dirY"] = Math.Round(this.MultiTasksReRay[i].dirY, 4);
thisrow["dirZ"] = Math.Round(this.MultiTasksReRay[i].dirZ, 4);
thisrow["type"] = this.MultiTasksReRay[i].type;
dtable.Rows.Add(thisrow);
}
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(DataUtil.ConnectionString))
{
bcp.BatchSize = dtable.Rows.Count;
bcp.BulkCopyTimeout = 1000;
bcp.DestinationTableName = "tbReRay";
bcp.WriteToServer(dtable);
bcp.Close();
}
dtable.Clear();
Console.WriteLine("tbReRay 写入结束！");
}
private void BulkCopyMSSQL(BulkCopyArguments args)
{
System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(this._Connection as System.Data.SqlClient.SqlConnection);
bulkCopy.BatchSize = args.BatchSize;
bulkCopy.BulkCopyTimeout = args.BulkCopyTimeout;
bulkCopy.DestinationTableName = args.DestinationTableName;
bulkCopy.NotifyAfter = args.NotifyAfter;
if (args.RowsCopied != null)
{
bulkCopy.SqlRowsCopied += (ss, ee) =>
{
ee.Abort = args.RowsCopied(ee.RowsCopied);
};
}
bulkCopy.WriteToServer(args.Table, args.RowState);
}
public void WriteData(object obj)
{
Int32 count = 0;
while (true)
{
DataTable dt = null;
try
{
dataLock.EnterWriteLock();
count = listDataTable.Count;
if (count > 0)
{
dt = listDataTable.Dequeue();
}
}
finally
{
dataLock.ExitWriteLock();
}
if (count == 0)
{
System.Threading.Thread.Sleep(100);
continue;
}
while (GetRedisQueueCount() > 3)
{
System.Threading.Thread.Sleep(300);
}
System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(txtDataBase.Text);
sqlConn.Open();
System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(sqlConn);
DateTime currentTime = DateTime.Now;
WriterFile(string.Format("排队个数：{0}---------------------------------------------------------------------------------------------------------------------", count));
WriterFile(string.Format(obj.ToString() + "数据库插入开始，数量{1},当前时间：{0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), dt.Rows.Count));
sbc.DestinationTableName = dt.TableName;
sbc.BulkCopyTimeout = 60000;
sbc.WriteToServer(dt);
WriterFile(string.Format(obj.ToString() + "数据库插入结束，数量{2},用时{0},当前时间：{1}", DateTime.Now - currentTime, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), dt.Rows.Count));
sbc.Close();
sqlConn.Close();
AddRedisQueue(dt);
}
}
public void ExecuteMainData(DataTable mainDatatable, string tableName)
{
using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(context.connStr))
{
bulk.DestinationTableName = tableName;//设置目标表
for (int i = 0; i < mainDatatable.Columns.Count; i++)
{
bulk.ColumnMappings.Add(mainDatatable.Columns[i].ColumnName, mainDatatable.Columns[i].ColumnName);
}
try
{
bulk.WriteToServer(mainDatatable.GetChanges());
}
catch (Exception ex)
{
throw ex;
}
}
}
private void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
string strSql = string.Format("if not exists( select * from sysobjects where name='{0} create table {0}'(", sheetName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
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);
}
}
void BlukCopy(string tablename, List <ChatMsg> list)
{
//1.0 创建一个内存表
DataTable dt = new DataTable();
dt.Columns.Add("ToUserId", typeof(int));
dt.Columns.Add("ToRealName", typeof(string));
dt.Columns.Add("FromUserId", typeof(int));
dt.Columns.Add("FromRealName", typeof(string));
dt.Columns.Add("MessageBody", typeof(string));
dt.Columns.Add("SendTime", typeof(DateTime));
DataRow datarow;
foreach (var item in list)
{
datarow = dt.NewRow();
datarow["ToUserId"] = item.ToUserId.ToString();
datarow["ToRealName"] = item.ToRealName;
datarow["FromUserId"] = item.FromUserId.ToString();
datarow["FromRealName"] = item.FromRealName;
datarow["MessageBody"] = item.MessageBody;
datarow["SendTime"] = item.SendTime.ToString();
dt.Rows.Add(datarow);
}
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["copyConn"].ConnectionString;
using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(connStr))
{
copy.DestinationTableName = tablename;
copy.ColumnMappings.Add("ToUserId", "ToUserId");
copy.ColumnMappings.Add("ToRealName", "ToRealName");
copy.ColumnMappings.Add("FromUserId", "FromUserId");
copy.ColumnMappings.Add("FromRealName", "FromRealName");
copy.ColumnMappings.Add("MessageBody", "MessageBody");
copy.ColumnMappings.Add("SendTime", "SendTime");
copy.WriteToServer(dt);
}
}
public static void InsertSqlBulkCopy(DataTable table, int bulkCopyTimeout = 100, int?batchSize = null, DbConfig config = null)
{
if (config == null)
{
config = DbConfig.Default;
}
using (var connection = new System.Data.SqlClient.SqlConnection(config.ConnectionString))
{
connection.Open();
var sqlbulkcopy = new System.Data.SqlClient.SqlBulkCopy(connection);
sqlbulkcopy.BulkCopyTimeout = bulkCopyTimeout;
sqlbulkcopy.BatchSize = (batchSize.HasValue) ? batchSize.Value : table.Rows.Count;
sqlbulkcopy.DestinationTableName = table.TableName;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(i, i);
}
sqlbulkcopy.WriteToServer(table);
connection.Close();
connection.Dispose();
}
}
public static void AddSizePartTask(DataTable dt)
{
try
{
using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(DbHelperSQL.connection))
{
sqlBC.BatchSize = 100000;
sqlBC.BulkCopyTimeout = 60;
sqlBC.DestinationTableName = "dbo.SizeTableTask";
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlBC.ColumnMappings.Add(i, i);
}
sqlBC.WriteToServer(dt);
dt = null;
GC.Collect();
}
}
catch (Exception Exce)
{
}
}
/// <summary>
/// BulkCopy
/// </summary>
/// <param name="dtSrc"></param>
/// <param name="dtDesc"></param>
public void BulkCopy(System.Data.DataTable dtSrc, string dtDesc)
{
using (DbConnection connection = m_db.CreateConnection())
{
System.Data.SqlClient.SqlConnection cn = connection as System.Data.SqlClient.SqlConnection;
if (cn == null)
{
throw new NotSupportedException("BuldCopy is only supported in MS SQL SERVER now!");
}
cn.Open();
using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(cn))
{
copy.BulkCopyTimeout = 3600 * 24;
for (int i = 0; i < dtSrc.Columns.Count; ++i)
{
copy.ColumnMappings.Add(i, i);
}
copy.DestinationTableName = dtDesc;
copy.WriteToServer(dtSrc);
}
}
}
public override void BulkCopyData <D>(IEnumerable <D> data)
{
YzkSoftWare.DataModel.IDataModel model = typeof(D).GetDataModel();
using (DataTable dt = CreateDataTableSchema(model))
{
int columcount = dt.Columns.Count;
foreach (var d in data)
{
DataRow nr = dt.NewRow();
for (int i = 0; i < columcount; i++)
{
var col = dt.Columns[i];
object fieldvalue = model.Fields[col.ColumnName].GetModelFieldValue(d);
nr[col.ColumnName] = fieldvalue;
}
dt.Rows.Add(nr);
}
System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(Connect as System.Data.SqlClient.SqlConnection);
try
{
bulkCopy.DestinationTableName = model.Name;
bulkCopy.BatchSize = dt.Rows.Count;
if (Connect.State == ConnectionState.Closed)
{
Connect.Open();
}
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
}
finally
{
bulkCopy.Close();
}
}
}
/// <summary>
/// Ejecuta Bulk Insert
/// </summary>
/// <param name="DTOrigen">Datatable: DataTable donde se tiene la informacion</param>
/// <param name="TablaDestino">String: Nombre de la tabal de destino donde va insertar la informacion</param>
/// <param name="pi_sLLaveConexion">String: cadena de conexion</param>
/// <returns>String: con informacion de carga</returns>
/// <remarks>Ivan Garcia Avila</remarks>
public string SubirDataTable(DataTable DTOrigen, string TablaDestino, string ConnectionString)
{
try
{
using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(ConnectionString, System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls))
{
bulkCopy.DestinationTableName = TablaDestino;
bulkCopy.BulkCopyTimeout = 0;
try
{
bulkCopy.WriteToServer(DTOrigen);
return("OK");
}
catch (Exception ex)
{
return("NOK:" + ex.ToString());
}
}
}
catch (Exception ex)
{
return("NOK:" + ex.ToString());
}
}
public static bool InsertBulk(System.Data.DataTable dataTable, string connectionstring, string destinationtable, List <string> sourceColumns, List <string> destinationColumns)
{
using (var connection = new System.Data.SqlClient.SqlConnection(connectionstring))
{
System.Data.SqlClient.SqlTransaction transaction = null;
connection.Open();
try
{
if (sourceColumns.Count == destinationColumns.Count)
{
int count = sourceColumns.Count;
transaction = connection.BeginTransaction();
using (var sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection, System.Data.SqlClient.SqlBulkCopyOptions.TableLock | System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, transaction))
{
sqlBulkCopy.DestinationTableName = destinationtable;
for (int i = 0; i < count; i++)
{
sqlBulkCopy.ColumnMappings.Add(sourceColumns[i], destinationColumns[i]);
}
sqlBulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
else
{
throw new Exception("Source Columns Do not match with Destination Columns");
}
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
private async void btnDownload_Click(object sender, EventArgs e)
{
const string CONN_STRING = @"Data Source=(local);Initial Catalog=DataPro;Trusted_Connection=true;Connect Timeout=60";
//IEnumerable<string> uris = new string[] { "http://mlb.com/lookup/named.cur_bio.bam", "http://mlb.com/lookup/named.cur_hitting.bam", "http://mlb.com/lookup/named.cur_hitting.bam?season=%272013%27", "http://mlb.com/lookup/named.cur_pitching.bam", "http://mlb.com/lookup/named.cur_fielding.bam" };
List<MLBAMFeed> feeds = new List<MLBAMFeed>();
feeds.Add(new MLBAMFeed() { Url = "http://mlb.com/lookup/named.cur_hitting.bam?season=%272013%27", FileName = "cur_hitting_2013.xml", DestinationTable="temp.curbat_v2" });
feeds.Add(new MLBAMFeed() { Url = "http://mlb.com/lookup/named.cur_hitting.bam", FileName = "cur_hitting.xml", DestinationTable = "temp.curbat_v2" });
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
//build the collection of threaded tasks
List<Task> tasks = new List<Task>();
foreach (var xmlFeed in feeds)
{
tasks.Add(downloadFile(xmlFeed));
}
UpdateStatus(string.Format("downloading {0} files.", feeds.Count()));
Cursor = Cursors.AppStarting;
sw.Start();
//kick them all off & wait until they've all completed.
await Task.WhenAll(tasks.ToArray());
sw.Stop();
Cursor = Cursors.Default;
UpdateStatus(string.Format("Completed in {0} seconds.", sw.Elapsed));
this.Refresh();
Cursor = Cursors.WaitCursor;
//initialize the db tables
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(CONN_STRING))
{
conn.Open();
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("truncate table temp.curBio_v2;", conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
//write them all to disk
//clean up any previous .xml files
DirectoryInfo di = new DirectoryInfo(Directory.GetCurrentDirectory());
FileInfo[] files = di.GetFiles("*.xml")
.Where(p => p.Extension == ".xml").ToArray();
foreach (FileInfo file in files)
{
file.Attributes = FileAttributes.Normal;
File.Delete(file.FullName);
}
// save the Task results to disk as .xml files
foreach (Task<MLBAMFeed> result in tasks)
{
MLBAMFeed xmlResult = result.Result;
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(xmlResult.XML);
string fileName = xmlResult.FileName; //string.Format("{0}.xml", xmlDoc.DocumentElement.Name);
//append to the .xml if it exists
using (StreamWriter writer = new StreamWriter(fileName, true))
{
writer.Write(xmlResult.XML);
}
//Load it into a dataset so we can bcp it
var ds = new DataSet();
ds.ReadXml(fileName);
lstPlayers.Items.Add(string.Format("{0} tables in {1} dataset...", ds.Tables.Count, fileName));
lstPlayers.Items.Add(string.Format("{0} rows.", ds.Tables[1].Rows.Count));
if (fileName.ToLower().Equals("cur_bio.xml"))
{
using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(CONN_STRING))
{
sbc.DestinationTableName="temp.curBio_v2";
sbc.BatchSize = 1000;
sbc.BulkCopyTimeout = 300;
//map the xml attribute to the db column
foreach (System.Data.DataColumn col in ds.Tables[1].Columns)
{
//don't include the ado queryResults node
if (col.ColumnName.ToLower() != "queryresults_id")
{
System.Diagnostics.Debug.WriteLine(col.ColumnName);
sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
}
sw.Reset();
sw.Start();
//push it up to the server
sbc.WriteToServer(ds.Tables[1]);
sw.Stop();
lstPlayers.Items.Add(string.Format("{0} rows inserted in {1} seconds.", ds.Tables[1].Rows.Count, sw.Elapsed));
}
}
}
Cursor = Cursors.Default;
}
/*
* 功能：
* 1、将System.Data.DataTable数据导出到Excel文件
* 2、将Model(Entity)数据实体导出到Excel文件
* 完整调用：
* 1、ExcelHelper.ToExcelForDataTable(DataTable,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList);
* 2、ExcelHelper.ToExcelForModelList(Model,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList);
* 参数说明：
* 1、DataTable：DataSet.DataTable[0];数据表
* 2、Model：Model.Users users = new Model.Users(){...};数据实体
* 3、excelPathName：含Excel名称的保存路径 在pathType＝1时有效。用户自定义保存路径时请赋值空字符串 ""。格式："E://456.xlsx"
* 4、pathType：路径类型。只能取值：0用户自定义路径，弹出用户选择路径对话框；1服务端定义路径。标识文件保存路径是服务端指定还是客户自定义路径及文件名，与excelPathName参数合用
* 5、colName：各列的列别名List string，比如：字段名为userName，此处可指定为"用户名"，并以此显示
* 6、excludeColumn：要显示/排除的列，指定这些列用于显示，或指定这些列用于不显示。倒低这些列是显示还是不显示，由excludeType参数决定
* 7、excludeType：显示/排除列方式。 0为显示所有列 1指定的是要显示的列 2指定的是要排除的列，与excludeColumn合用
* 8、sheetName：sheet1的名称，要使期保持默认名称请指定为空字符串 ""
* 9、TemplatePath：模版在项目服务器中路径 例:tp.xlsx 。当为空字符串 "" 时表示无模版
* 10、TemplateRow：模版中已存在数据的行数，与TemplatePath合用，无模版时请传入参数 0
* 11、exDataTableList：扩展 DataTable List 用于当上下两个及以上DataTable数据类型不一至,但又都在同一列时使用,要求格式与参数第一个 DataTable的列名字段名一至,仅字段类型可不同
* 注意：
* 1、exDataTableList参数为一个List<System.Data.DataTable> 集合，当数据为 Model 时，可先调用 ExcelHelper.ModelListToDataTable(System.Data.DataTable dt)将Model转为System.Data.DataTable
*/
#endregion
#endregion
#region 从Excel导入数据到 Ms Sql
/// <summary>
/// 从Excel导入数据到 Ms Sql
/// </summary>
/// <param name="excelFile">Excel文件路径(含文件名)</param>
/// <param name="sheetName">sheet名</param>
/// <param name="DbTableName">存储到数据库中的数据库表名称</param>
/// <param name="columnType">对应表格的数据类型，如果为null，则为默认类型：double,nvarchar(500),datetime</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public static bool FromExcel(string excelFile, string sheetName, string DbTableName, List <string> columnType, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连
#region 知识扩展
//HDR=Yes，代表第一行是标题，不做为数据使用。HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
//IMEX=0 只读模式
//IMEX=1 写入模式
//IMEX=2 可读写模式
#endregion
#region 命名执行
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
#region 数据库表是否存在的 T-SQL 检测语句准备
//如果目标表不存在则创建
string strSql = string.Format("if object_id('{0}') is null create table {0}(", DbTableName != "" ? DbTableName : sheetName);
if (columnType != null && columnType.Count > 0)
{
#region 手动指定定每个字段的数据类型
//指定数据格式,要求一一对应
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
System.Data.DataColumn c = ds.Tables[0].Columns[i];
strSql += string.Format("[{0}] {1},", c.ColumnName, columnType[i]);
}
#endregion
}
else
{
#region 使用默认数据类型
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
//使用默认格式：只有double,DateTime,String三种类型
switch (c.DataType.ToString())
{
case "DateTime":
{
strSql += string.Format("[{0}] DateTime,", c.ColumnName);
}; break;
case "Double":
{
strSql += string.Format("[{0}] double,", c.ColumnName);
}; break;
default:
strSql += string.Format("[{0}] nvarchar(500),", c.ColumnName);
break;
}
}
#endregion
}
strSql = strSql.Trim(',') + ")";
#endregion
#region 执行 T-SQL 如果数据库表不存在则新建表，如果存在则不新建
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
#endregion
#region 向数据库表插入数据
using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
sbc.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
sbc.BatchSize = 100; //每次传输的行数
sbc.NotifyAfter = 100; //进度提示的行数
sbc.DestinationTableName = DbTableName != "" ? DbTableName : sheetName; //数据库表名表名
sbc.WriteToServer(ds.Tables[0]);
}
#endregion
}
#endregion
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public static void Execute(string filter = null)
{
var dbcd = new DBCD.DBCD(new DBCProvider(dbPath), new DBDProvider(definitionsPath));
var build = "8.3.0.34220";
var connectionString = new SqlConnectionStringBuilder
{
InitialCatalog = "wow",
DataSource = "(local)",
IntegratedSecurity = true
}.ToString();
using var connection = new SqlConnection(connectionString);
connection.Open();
var server = new Server();
var database = server.Databases["wow"];
// The DBCache which stores downloaded / patched database records from the server side
Console.WriteLine("Loading in hotfix cache...");
var hotfix = new HotfixReader(Path.Combine(cachePath, "DBCache.bin"));
var caches = Directory.EnumerateFiles(cachePath, "DBCache.bin*.tmp").ToList();
hotfix.CombineCaches(caches.ToArray());
Console.WriteLine("Loading tables..");
foreach (var databaseFile in Directory.EnumerateFiles(dbPath, "*.db?", SearchOption.TopDirectoryOnly))
{
Console.WriteLine();
var name = Path.GetFileNameWithoutExtension(databaseFile);
if (name.StartsWith("UnitTest", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (!string.IsNullOrWhiteSpace(filter) && !filter.Contains(name, StringComparison.OrdinalIgnoreCase))
{
continue;
}
var storage = dbcd.Load(name, build, Locale.EnUS).ApplyingHotfixes(hotfix);
DBCDRow item = storage.Values.FirstOrDefault();
if (item == null)
{
Console.WriteLine(name + ": **EMPTY**");
continue;
}
Console.WriteLine(name);
Console.WriteLine(string.Join("", Enumerable.Repeat("=", name.Length)));
using var table = new DataTable(name);
for (var j = 0; j < storage.AvailableColumns.Length; ++j)
{
string fieldname = storage.AvailableColumns[j];
var field = item[fieldname];
var isEndOfRecord = j == storage.AvailableColumns.Length - 1;
if (field is Array a)
{
for (var i = 0; i < a.Length; i++)
{
var isEndOfArray = a.Length - 1 == i;
Console.Write($"{fieldname}[{i}]");
if (!isEndOfArray)
{
Console.Write(",");
}
table.Columns.Add(new DataColumn(fieldname + "_" + i)
{
DataType = a.GetType().GetElementType()
});
}
}
else
{
var column = new DataColumn(fieldname)
{
DataType = field.GetType()
};
table.Columns.Add(column);
if (fieldname.Equals("id", StringComparison.OrdinalIgnoreCase))
{
table.PrimaryKey = new[] { column };
}
Console.Write(fieldname);
}
if (!isEndOfRecord)
{
Console.Write(",");
}
}
database.CreateTableSchema(table);
// Process rows
foreach (var row in storage.Values)
{
var dataRow = table.NewRow();
foreach (var fieldName in storage.AvailableColumns)
{
var value = row[fieldName];
if (value is Array a)
{
for (var j = 0; j < a.Length; j++)
{
var arrayValue = a.GetValue(j).ToString();
// if (searchValues.Contains(arrayValue))
// {
// Console.ForegroundColor = ConsoleColor.Yellow;
// Console.WriteLine($"Found matching record: {table.TableName}#{row.ID}");
// Console.ResetColor();
// }
dataRow[fieldName + "_" + j] = arrayValue;
}
}
else
{
dataRow[fieldName] = value;
}
}
table.Rows.Add(dataRow);
}
// Bulk import the data
var bulk = new SqlBulkCopy(connection);
bulk.DestinationTableName = table.TableName;
bulk.WriteToServer(table);
Console.WriteLine();
}
}
/// <summary>
/// 使用datatable保存方法，table中的列要與數據表中的列一至，并且將數據表名放到table.TableName中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool InsertDataTable(DataTable dt)
{
try
{
string dbcon = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLModel.Properties.Settings.HBPMSDBConnectionString"].ConnectionString;
using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(dbcon))
{
sbc.BatchSize = 100000;//每次传输的行数
sbc.BulkCopyTimeout = 900;
sbc.DestinationTableName = dt.TableName;
sbc.WriteToServer(dt);
sbc.Close();
}
return true;
}
catch
{
}
return false;
}
/// <summary>
/// Use bulk copy to insert the worksheet rows into the specified table
/// </summary>
/// <param name="tempTableName"></param>
/// <param name="excelConnectionString"></param>
/// <param name="worksheetName"></param>
private void OleCopyToTempTable(string tempTableName, string excelConnectionString, string worksheetName)
{
using (System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(excelConnectionString))
{
try
{
excelConnection.Open();
if (string.IsNullOrEmpty(ExcelQuery))
ExcelQuery = "SELECT * FROM [" + worksheetName + "$]";
else
ExcelQuery += "FROM [" + worksheetName + "$]";
using (System.Data.OleDb.OleDbCommand selectAllComand = new System.Data.OleDb.OleDbCommand(ExcelQuery)) //SELECT * FROM [" + worksheetName + "$]"))
{
selectAllComand.Connection = excelConnection;
using (System.Data.OleDb.OleDbDataReader excelReader = selectAllComand.ExecuteReader())
{
using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(_dbConn))
{
bc.BatchSize = 50;
bc.DestinationTableName = @"[dbo].[" + tempTableName + @"]"; //tempDatasheetImport]";
// User notification with the SqlRowsCopied event
//bc.NotifyAfter = 100;
//bc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
if (_dbConn.State != System.Data.ConnectionState.Open)
_dbConn.Open();
bc.WriteToServer(excelReader);
bc.Close();
}
excelReader.Close();
_dbConn.Close();
}
}
excelConnection.Close();
}
catch (InvalidOperationException ex)
{
//if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.") //not a problem, just reaching the footer and easier to skip an exception than try to filter it out
// throw new Exception("OleCopyToTempTable 1: " + ex.Message);
//else if (ex.Message != @"Column 'tmpMSEP' does not allow DBNull.Value.") //not a problem, just reaching the footer
// throw new Exception("OleCopyToTempTable 2: " + ex.Message);
//else if (ex.Message != @"Column 'tmpIndentNo' does not allow DBNull.Value.")
// throw new Exception("OleCopyToTempTable 3: " + ex.Message);
}
finally
{
excelConnection.Close();
_dbConn.Close();
}
//catch (Exception ex)
//{
// throw new Exception("OleCopyToTempTable 4: " + ex.Message);
//}
}
}
// BulkCopy("dbo.T_Benutzer", dt)
public override bool BulkCopy(string strDestinationTable, System.Data.DataTable dt, bool bWithDelete)
{
try
{
strDestinationTable = "[" + strDestinationTable + "]";
if (bWithDelete)
{
this.Execute("DELETE FROM " + strDestinationTable.Replace("'", "''"));
}
// http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx
System.Data.SqlClient.SqlBulkCopyOptions bcoOptions = //System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints |
System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls |
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity;
// http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column
// http://msdn.microsoft.com/en-us/library/ms186335.aspx
System.Data.SqlClient.SqlBulkCopy BulkCopyInstance = new System.Data.SqlClient.SqlBulkCopy(this.m_ConnectionString.ConnectionString, bcoOptions);
foreach (System.Data.DataColumn dc in dt.Columns)
{
BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, "[" + dc.ColumnName + "]");
}
BulkCopyInstance.DestinationTableName = strDestinationTable;
/*
* string strSQL = "INSERT INTO " + BulkCopyInstance.DestinationTableName + Environment.NewLine + "(" + Environment.NewLine;
*
*
* for(int i=0; i < dt.Columns.Count; ++i)
* {
* if(i==0)
* strSQL += " [" + dt.Columns[i].ColumnName + "]" + Environment.NewLine;
* else
* strSQL += " ,[" + dt.Columns[i].ColumnName + "]" + Environment.NewLine;
* //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
* }
* strSQL += ") " + Environment.NewLine + "Values "+ Environment.NewLine + "(" + Environment.NewLine;
*
* for (int i = 0; i < dt.Columns.Count; ++i)
* {
* if (i == 0)
* strSQL += " @parameter" + i.ToString() + Environment.NewLine;
* else
* strSQL += " ,@parameter" + i.ToString() + Environment.NewLine;
* //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
* }
*
* strSQL += "); ";
*
* // http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622244
* System.Data.IDbCommand idbc = this.CreateCommand(strSQL);
*
* for (int i = 0; i < dt.Rows.Count; ++i)
* {
*
* for (int j = 0; j < dt.Columns.Count; ++j)
* {
* this.AddParameter(idbc, "parameter" + j.ToString(), dt.Rows[i][j]);
* }
*
* //this.Execute(idbc);
* this.ExecuteWithoutTransaction(idbc);
* idbc.Parameters.Clear();
* }
*
* //MsgBox(strSQL);
*/
BulkCopyInstance.WriteToServer(dt);
BulkCopyInstance.Close();
BulkCopyInstance = null;
}
catch (System.Exception ex)
{
if (Log("cSQLite_specific.cs ==> BulkCopy", ex, "BulkCopy: Copy dt to " + strDestinationTable))
{
throw;
}
//COR.Logging.WriteLogFile("FEHLER", "Ausnahme in COR.SQL.MSSQL.BulkCopy");
//COR.Logging.WriteLogFile("FEHLER", ex.Message);
//COR.Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------");
//COR.Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString());
//Console.WriteLine(ex.Message.ToString() + Environment.NewLine + ex.StackTrace.ToString()); //MsgBoxStyle.Critical, "FEHLER ...");
//COR.Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------");
}
return(false);
} // End Function BulkCopy
// BulkCopy("dbo.T_Benutzer", dt)
public override bool BulkCopy(string tableSchema, string tableName, System.Data.DataTable dt, bool bWithDelete)
{
try
{
string sanitizedTableName = this.QuoteObjectWhereNecessary(tableName);
// Ensure table is empty - and throw on foreign-key
if (bWithDelete)
{
this.Execute("DELETE FROM " + sanitizedTableName);
}
System.Collections.Generic.List <string> lsComputedColumns = GetComputedColumnNames(tableSchema, tableName);
// http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx
System.Data.SqlClient.SqlBulkCopyOptions bcoOptions = //System.Data.SqlClient.SqlBulkCopyOptions.CheckConstraints |
System.Data.SqlClient.SqlBulkCopyOptions.KeepNulls |
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity;
// http://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column
// http://msdn.microsoft.com/en-us/library/ms186335.aspx
System.Data.SqlClient.SqlBulkCopy BulkCopyInstance = new System.Data.SqlClient.SqlBulkCopy(this.m_ConnectionString.ConnectionString, bcoOptions);
foreach (System.Data.DataColumn dc in dt.Columns)
{
// The column "foo" cannot be modified because it is either a computed column or...
if (MyExtensionMethods.Contains(lsComputedColumns, dc.ColumnName, System.StringComparer.InvariantCultureIgnoreCase))
{
continue;
}
BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, "[" + dc.ColumnName.Replace("]", "]]") + "]");
}
BulkCopyInstance.DestinationTableName = sanitizedTableName;
/*
* string strSQL = "INSERT INTO " + BulkCopyInstance.DestinationTableName + Environment.NewLine + "(" + Environment.NewLine;
*
*
* for(int i=0; i < dt.Columns.Count; ++i)
* {
* if(i==0)
* strSQL += " [" + dt.Columns[i].ColumnName + "]" + Environment.NewLine;
* else
* strSQL += " ,[" + dt.Columns[i].ColumnName + "]" + Environment.NewLine;
* //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
* }
* strSQL += ") " + Environment.NewLine + "Values "+ Environment.NewLine + "(" + Environment.NewLine;
*
* for (int i = 0; i < dt.Columns.Count; ++i)
* {
* if (i == 0)
* strSQL += " @parameter" + i.ToString() + Environment.NewLine;
* else
* strSQL += " ,@parameter" + i.ToString() + Environment.NewLine;
* //BulkCopyInstance.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
* }
*
* strSQL += "); ";
*
* // http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622244
* System.Data.IDbCommand idbc = this.CreateCommand(strSQL);
*
* for (int i = 0; i < dt.Rows.Count; ++i)
* {
*
* for (int j = 0; j < dt.Columns.Count; ++j)
* {
* this.AddParameter(idbc, "parameter" + j.ToString(), dt.Rows[i][j]);
* }
*
* //this.Execute(idbc);
* this.ExecuteWithoutTransaction(idbc);
* idbc.Parameters.Clear();
* }
*
* //MsgBox(strSQL);
*/
BulkCopyInstance.WriteToServer(dt);
BulkCopyInstance.Close();
BulkCopyInstance = null;
}
catch (System.Exception ex)
{
if (Log("cMS_SQL_specific.BulkCopy", ex, "BulkCopy: Copy dt to " + tableName))
{
throw;
}
//COR.Logging.WriteLogFile("FEHLER", "Ausnahme in COR.SQL.MSSQL.BulkCopy");
//COR.Logging.WriteLogFile("FEHLER", ex.Message);
//COR.Logging.WriteLogFile("FEHLER", "-----------------------------------------------------------------");
//COR.Logging.WriteLogFile("FEHLER", ex.StackTrace.ToString());
//Console.WriteLine(ex.Message.ToString() + Environment.NewLine + ex.StackTrace.ToString()); //MsgBoxStyle.Critical, "FEHLER ...");
//COR.Logging.WriteLogFile("MELDUNG", "-----------------------------------------------------------------");
}
return(false);
} // End Function BulkCopy
public void TransferData(string excelFile, string sheetName, string connectionString="")
{
connectionString = ConfigurationManager.ConnectionStrings["testStr"].ConnectionString;
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建
string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] nvarchar(255),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
//bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// BulkCopy
/// </summary>
/// <param name="dtSrc"></param>
/// <param name="dtDesc"></param>
public void BulkCopy(System.Data.DataTable dtSrc, string dtDesc)
{
using (DbConnection connection = m_db.CreateConnection())
{
System.Data.SqlClient.SqlConnection cn = connection as System.Data.SqlClient.SqlConnection;
if (cn == null)
{
throw new NotSupportedException("BuldCopy is only supported in MS SQL SERVER now!");
}
cn.Open();
using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(cn))
{
copy.BulkCopyTimeout = 3600 * 24;
for (int i = 0; i < dtSrc.Columns.Count; ++i)
{
copy.ColumnMappings.Add(i, i);
}
copy.DestinationTableName = dtDesc;
copy.WriteToServer(dtSrc);
}
}
}