public ImportQueue ImportQueueBulkImport(ImportQueue importQueue) { using (var conn = DbHelper.GetDBConnection()) { using (var bulk = new SqlBulkCopy((SqlConnection)conn) { BulkCopyTimeout = 600 }) { var columnIndex = 0; bulk.DestinationTableName = "dbo.Fdp_ImportData"; bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "FdpImportId")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "LineNumber")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Pipeline Code")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Model Year Desc")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "NSC or Importer Description (Vista Market)")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Country Description")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Derivative Code")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Derivative Description")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Trim Pack Description")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Bff Feature Code")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex++, "Feature Description")); bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(columnIndex, "Count of Specific Order No")); bulk.WriteToServer(importQueue.ImportData); bulk.Close(); } } return ImportQueueGet(importQueue.ImportQueueId.GetValueOrDefault()); }
public void BatchSaveData(IList<Model.WeekStockModel> list, string stockCode, bool isChange) { string sql; if (isChange) { sql = "delete from " + GetTableName(stockCode) + " where stockcode='" + stockCode + "'"; Data.DB.DbInstance.ExecuteNonQuery(CommandType.Text, sql); } if (list.Count > 0) { sql = "delete from " + GetTableName(stockCode) + " where stockcode='" + stockCode + "' and begindate="+list[list.Count-1].BeginDate.ToString(); Data.DB.DbInstance.ExecuteNonQuery(CommandType.Text, sql); string connectionString = Data.DB.DbInstance.ConnectionString; SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); sqlBulkCopy.DestinationTableName = GetTableName(stockCode); sqlBulkCopy.BatchSize = list.Count; SqlBulkCopyColumnMappingCollection collection = sqlBulkCopy.ColumnMappings; SqlConnection sqlConnection = new SqlConnection(connectionString); sqlConnection.Open(); DataTable dataTable = new DataTable(); dataTable.Columns.Add("stockcode"); collection.Add("stockcode", "stockcode"); dataTable.Columns.Add("begindate"); collection.Add("begindate", "begindate"); dataTable.Columns.Add("enddate"); collection.Add("enddate", "enddate"); foreach (string key in list[0].Dic.Keys) { dataTable.Columns.Add(key); collection.Add(key, key); } for (int i = 0; i < list.Count; i++) { if (list[i].Status == 1) { DataRow newRow = dataTable.NewRow(); newRow["stockcode"] = stockCode; newRow["begindate"] = list[i].BeginDate; newRow["enddate"] = list[i].EndDate; foreach (string key in list[i].Dic.Keys) { newRow[key] = list[i].Dic[key]; } dataTable.Rows.Add(newRow); } } if (dataTable != null && dataTable.Rows.Count != 0) { sqlBulkCopy.WriteToServer(dataTable); } sqlBulkCopy.Close(); sqlConnection.Close(); } }
public static bool BulkToDb(SqlConnection sqlConnection, SqlTransaction sqlTransaction, DataTable dt, string tb, out string sMsg) { bool ret = false; sMsg = null; SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction); bulkCopy.DestinationTableName = tb; bulkCopy.BatchSize = dt.Rows.Count; try { if (dt != null && dt.Rows.Count != 0) bulkCopy.WriteToServer(dt); ret = true; } catch (Exception ex) { sMsg = ex.Message; throw ex; } finally { if (bulkCopy != null) bulkCopy.Close(); } return ret; }
/// <summary> /// 往数据库中批量插入数据 /// </summary> /// <param name="sourceDt">数据源表</param> /// <param name="targetTable">服务器上目标表</param> public static void BulkToDB(DataTable sourceDt, string targetTable, SqlBulkCopyColumnMapping[] mapping) { SqlConnection conn = new SqlConnection(strConn); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中 bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称 bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数 try { conn.Open(); if (sourceDt != null && sourceDt.Rows.Count != 0) { for (int i = 0; i < mapping.Length; i++) bulkCopy.ColumnMappings.Add(mapping[i]); bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中 } } catch (Exception ex) { throw ex; } finally { conn.Close(); if (bulkCopy != null) bulkCopy.Close(); } }
/// <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); }
/// <summary> /// 批量插入数据表 /// </summary> /// <param name="dt">内容表</param> /// <param name="DestinationTableName">插入目标表名</param> public static void BulkToDataTable(DataTable FromDataTable, string DestinationTableName) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); bulkCopy.DestinationTableName = DestinationTableName; bulkCopy.BatchSize = FromDataTable.Rows.Count; try { conn.Open(); if (FromDataTable != null && FromDataTable.Rows.Count > 0) bulkCopy.WriteToServer(FromDataTable); } catch (Exception ex) { throw ex; } finally { conn.Close(); if (bulkCopy != null) bulkCopy.Close(); } } }
public void BulkLoadDestinationTables() { var sourceQueryRunner = new QueryRunner(tableSet.SourceConnectionStringName); foreach (var tableMap in tableSet.Mappings.Where(ts=>ts.TruncateDestinationAndBulkLoadFromSource).OrderBy(ts => ts.Ordinal)) { var query = syncChangesData.BuildQueryToRemoveChangesForTable(tableMap.SourceSchema, tableMap.SourceTable); sourceQueryRunner.RunQuery(query); Logging.WriteMessageToApplicationLog("About to bulk load data from " + tableMap.FullyQualifiedSourceTable + " to " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information); using (var bulkCopy = new SqlBulkCopy(DestinationConnectionString, SqlBulkCopyOptions.KeepIdentity)) { bulkCopy.DestinationTableName = tableMap.FullyQualifiedDestinationTable; bulkCopy.EnableStreaming = true; bulkCopy.BulkCopyTimeout = BulkCopyTimeout; using (var conn = openSourceConnection()) { var sql = "select * from " + tableMap.FullyQualifiedSourceTable + ";"; if (!string.IsNullOrEmpty(tableMap.CustomSourceSQLForBulkLoadOnly)) { sql = tableMap.CustomSourceSQLForBulkLoadOnly; } var command = createCommand(conn, sql); command.CommandTimeout = BulkCopyTimeout; using (var reader = command.ExecuteReader()) { bulkCopy.WriteToServer(reader); } } bulkCopy.Close(); } } }
protected void btnDownloadClasses_Click(object sender, EventArgs e) { com.maplewood.www.mwWebSrvStAc Integration = new com.maplewood.www.mwWebSrvStAc(); Integration.Url = GetWebServiceURL(); Integration.Timeout = 360000; //Loop thru Sudbury Schools string strSQL = "SELECT cpSchoolCode, SchoolId, InstitutionName FROM UserInfo WHERE cpSystemNameId = " + GetSystemNameId() + " order by institutionType, schoolid"; DataTable dtb = DataAccess.GetDataTable(strSQL); foreach (DataRow dr in dtb.Rows) { Response.Write("--------------------------------------<br/>"); Response.Write(dr["cpSchoolCode"].ToString() + ":" + dr["InstitutionName"].ToString() + "<br/>"); Response.Write("--------------------------------------<br/>"); //Student Courses in Progress //-------------------------- com.maplewood.www.StAc_StuClass objMWProgress = new com.maplewood.www.StAc_StuClass(); objMWProgress = Integration.GetStudentClasses(dr["cpSchoolCode"].ToString(), ""); Response.Write("In Progress Courses: " + objMWProgress.Student_Class_Details.Count.ToString() + "<br/>"); SqlBulkCopy oBulkInProgress = new SqlBulkCopy(strCareerdbImportLocal); oBulkInProgress.DestinationTableName = "MWStudentClasses"; oBulkInProgress.WriteToServer(objMWProgress.Student_Class_Details); oBulkInProgress.Close(); //-------------------------- } }
/// <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); }
/// <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(); }
/// <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 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); } }
/// <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; } }
public void batch_save_2_db(string _tableName, ref DataTable dt, bool _clearDT = true) { try { if (dt.Rows.Count > 0) { using(SqlConnection conn = get_conn()) { SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); bulkCopy.DestinationTableName = _tableName; string colNames = ""; for (int ccc = 0; ccc < dt.Columns.Count; ccc++) { string colName = dt.Columns[ccc].ColumnName; if (colName != "CELL") bulkCopy.ColumnMappings.Add(colName, colName); } bulkCopy.WriteToServer(dt); bulkCopy.Close(); bulkCopy = null; if (_clearDT) { dt.Clear(); } } } } catch (System.Exception ex) { LogHelper.instance().write(ex.Message); } finally { } }
public static void batch_save_2_db(string _tableName, ref DataTable dt, bool _clearDT = true) { try { if (dt.Rows.Count > 0) { SqlBulkCopy bulkCopy = new SqlBulkCopy(DBHelper.get_conn()); bulkCopy.DestinationTableName = _tableName; for (int ccc = 0; ccc < dt.Columns.Count; ccc++) { bulkCopy.ColumnMappings.Add(dt.Columns[ccc].ColumnName, dt.Columns[ccc].ColumnName); } bulkCopy.WriteToServer(dt); bulkCopy.Close(); bulkCopy = null; if (_clearDT) { dt.Clear(); } } } catch (System.Exception ex) { LogHelper.write(ex.Message); } finally { } }
/// <summary> /// 利用Net SqlBulkCopy 批量导入数据库,速度超快 /// </summary> /// <param name="dataTable">源内存数据表</param> public bool MsSqlBulkCopyData(DataTable dt, string connectionString) { try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction trans = conn.BeginTransaction(); SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans); // 设置源表名称 sqlbulkCopy.DestinationTableName = dt.TableName; //分几次拷贝 //sqlbulkCopy.BatchSize = 10; // 设置超时限制 sqlbulkCopy.BulkCopyTimeout = 1000; foreach (DataColumn dtColumn in dt.Columns) { sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName); } try { // 写入 sqlbulkCopy.WriteToServer(dt); // 提交事务 trans.Commit(); return true; } catch { trans.Rollback(); sqlbulkCopy.Close(); return false; } finally { sqlbulkCopy.Close(); conn.Close(); } } } catch (Exception e) { Logger.WriteLog("-----------利用Net SqlBulkCopyData 批量导入数据库,速度超快-----------\r\n" + e.Message + "\r\n"); return false; } }
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); } }
//private //************************************** //* Purpose: Accessing SQL database //*Methods: //*GetDataSet //*RunProc //*GetDataReader //*GetDataView //* ************************************* public bool BulkCopyData(string strConnect, string bulkCopyTable, SqlDataReader sourcedata) { //******************************** //* Purpose: Performs bulk copy from one data source to another //* Input parameters: //* bulkCopytable ---the target table to bulk data into //* sourcedata ---the SqlDataReader holding the data to bulk insert //* Returns : //* nothing //************************************************** bool err = false; //create source connection var sourceConnection = new SqlConnection(strConnect); sourceConnection.Open(); // Create SqlBulkCopy var bulkData = new SqlBulkCopy(strConnect, SqlBulkCopyOptions.TableLock) { BatchSize = 1000, BulkCopyTimeout = 360, DestinationTableName = bulkCopyTable }; //set number of records to process in one batch //set timeout for a single bulk process // Set destination table name try { bulkData.WriteToServer(sourcedata); } catch (Exception e) { err = true; PLOG.Write(e.Message, 1); bulkData.Close(); sourceConnection.Close(); sourceConnection.Dispose(); } finally { bulkData.Close(); sourceConnection.Close(); sourceConnection.Dispose(); } return err; }
public static void ImportContactTmp(DataTable dtData) { if (dtData == null || dtData.Rows.Count == 0) return; using (var sqlBulkCopy = new SqlBulkCopy(ImportConfig.ConnectionString, SqlBulkCopyOptions.TableLock)) // Lock the table { sqlBulkCopy.DestinationTableName = "ContactTmps"; sqlBulkCopy.WriteToServer(dtData); sqlBulkCopy.Close(); } }
public static void ImportObjectChanges(DataTable dtData, SqlConnection conn, SqlTransaction transaction) { if (dtData == null || dtData.Rows.Count == 0) return; using (var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction)) // Lock the table { sqlBulkCopy.DestinationTableName = "ActivityObjectChanges"; sqlBulkCopy.WriteToServer(dtData); sqlBulkCopy.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(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 string CopyData(string strSourceDBPath, string strDestinationDBPath) { string strFailed = ""; try { string strServer = ".\\sqlExpress"; if (File.Exists(Application.StartupPath + "\\sys.txt")) { strServer = File.ReadAllText(Application.StartupPath + "\\sys.txt"); // getting ip of server } sqlconSource = new SqlConnection(@"Data Source=" + strServer + ";AttachDbFilename=" + strSourceDBPath + ";Integrated Security=True;Connect Timeout=30;User Instance=True"); sqlconDestination = new SqlConnection(@"Data Source=" + strServer + ";AttachDbFilename=" + strDestinationDBPath + ";Integrated Security=True;Connect Timeout=30;User Instance=True"); sqlconSource.Open(); sqlconDestination.Open(); DataTable dtblTables = new DataTable(); SqlDataAdapter adp = new SqlDataAdapter("select table_name as name from information_schema.tables where TABLE_TYPE='BASE TABLE'", sqlconSource); adp.Fill(dtblTables); SqlBulkCopy sbc = new SqlBulkCopy(@"Data Source=" + strServer + ";AttachDbFilename=" + strDestinationDBPath + ";Integrated Security=True;Connect Timeout=30;User Instance=True", SqlBulkCopyOptions.KeepIdentity); for (int i = 0; i < dtblTables.Rows.Count; i++) { frmCopyData.strTable = dtblTables.Rows[i][0].ToString(); if (frmCopyData.strTable != "tbl_QuickLaunchItemsToCopy" && frmCopyData.strTable != "tbl_FormCopy" && frmCopyData.strTable != "tbl_MasterCopy" && frmCopyData.strTable != "tbl_FieldsCopy" && frmCopyData.strTable != "tbl_DetailsCopy") { SqlCommand sccmdTrucate = new SqlCommand("TRUNCATE TABLE " + frmCopyData.strTable, sqlconDestination); try { sccmdTrucate.ExecuteNonQuery(); } catch { } SqlCommand sccmd = new SqlCommand("SELECT * FROM " + frmCopyData.strTable, sqlconSource); SqlDataReader sdrreader = sccmd.ExecuteReader(); sbc.DestinationTableName = frmCopyData.strTable; try { sbc.WriteToServer(sdrreader); } catch { strFailed = strFailed + frmCopyData.strTable + "\n"; } sdrreader.Close(); } } sbc.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { sqlconSource.Close(); } return strFailed; }
public static void ImportContact(DataTable dtData, SqlConnection conn, SqlTransaction transaction) { if (dtData == null || dtData.Rows.Count == 0) return; using (var sqlBulkCopy = new SqlBulkCopy(ImportConfig.ConnectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.KeepIdentity)) // Lock the table { sqlBulkCopy.DestinationTableName = "Contacts"; sqlBulkCopy.WriteToServer(dtData); sqlBulkCopy.Close(); } }
/// <summary> ///大批量数据插入 /// </summary> /// <param name="table">数据表</param> /// <param name="connectionString">数据库连接字符串</param> /// <returns></returns> public bool SqlServerBulkInsert(DataTable table, string connectionString) { try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction trans = conn.BeginTransaction(); SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans); sqlbulkCopy.DestinationTableName = table.TableName; //设置源表名称 sqlbulkCopy.BulkCopyTimeout = CommandTimeOut; //设置超时限制 foreach (DataColumn dtColumn in table.Columns) { sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName); } try { sqlbulkCopy.WriteToServer(table); //写入 trans.Commit(); //提交事务 return true; } catch { trans.Rollback(); sqlbulkCopy.Close(); return false; } finally { conn.Close(); conn.Dispose(); sqlbulkCopy.Close(); } } } catch (Exception e) { System.Diagnostics.Debug.WriteLine(e.Message); return false; } }
public static void BulkToDB(DataTable dt) { //SqlClient中有SqlBulkCopy, System.Data.OracleClient中却没有。 //使用Oracle DataAcess Provider For dotnet中自带的OracleBulkCopy组件 // if (DBType == "SQL") { SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); bulkCopy.DestinationTableName = BulkTable; bulkCopy.BatchSize = dt.Rows.Count; try { //sqlConn.Open(); if (dt != null && dt.Rows.Count != 0) bulkCopy.WriteToServer(dt); } catch (Exception ex) { throw ex; } finally { //sqlConn.Close(); if (bulkCopy != null) bulkCopy.Close(); } } else if (DBType == "ORACLE") { //OracleBulkCopy bulkCopy = new OracleBulkCopy(oraConn); //bulkCopy.DestinationTableName = BulkTable; //bulkCopy.BatchSize = dt.Rows.Count; try { //sqlConn.Open(); //if (dt != null && dt.Rows.Count != 0) // bulkCopy.WriteToServer(dt); } catch (Exception ex) { throw ex; } finally { //sqlConn.Close(); //if (bulkCopy != null) // bulkCopy.Close(); } } }
public void BatchInsert(DataTable dataTable, string tableName) { if (!(this.connection is SqlConnection)) { throw new Exception("此方法只支持SQL Server数据库。"); } SqlBulkCopy copy = new SqlBulkCopy((SqlConnection) this.connection) { DestinationTableName = tableName }; copy.WriteToServer(dataTable); copy.Close(); }
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); } }
static void Main(string[] args) { var builder = new SqlConnectionStringBuilder { DataSource = @"10.6.33.35\sqlexpress", IntegratedSecurity = false, UserID = "sa", Password = "******", InitialCatalog = "Catalog" }; var stopWatch = new Stopwatch(); var rowsStopWatch = new Stopwatch(); stopWatch.Start(); using (var textReader = File.OpenText(@"C:\Users\rgunawan\Downloads\dataJul-12-2013.csv")) using (var reader = new CsvReader(textReader, true)) using (var conn = new SqlConnection(builder.ToString())) using (var sql = new SqlBulkCopy(conn)) { conn.Open(); sql.BatchSize = 10000; sql.NotifyAfter = 10000; sql.SqlRowsCopied += (sender, eventArgs) => { Console.WriteLine("Copied: {0} rows in {1} seconds.", eventArgs.RowsCopied, rowsStopWatch.Elapsed.TotalSeconds); rowsStopWatch.Restart(); }; sql.DestinationTableName = "TestTable"; rowsStopWatch.Start(); sql.WriteToServer(reader); sql.Close(); conn.Close(); } stopWatch.Stop(); Console.WriteLine("Elapsed seconds: " + stopWatch.Elapsed.TotalSeconds); Console.ReadKey(); }
/// <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); } }
private void cust_import_csv_btn_import_Click(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(cust_import_txtbox_csv.Text)) { MessageBox.Show("Geen bestand gekozen. Kies een .CSV bestand en probeer het opnieuw"); } else { var result = MessageBox.Show("Deze actie laadt alle klanten uit bestand " + cust_import_csv_path.Text); string text = File.ReadAllText(cust_import_txtbox_csv.Text, Encoding.Default); File.WriteAllText(cust_import_txtbox_csv.Text, text, Encoding.BigEndianUnicode); if (result == DialogResult.OK) { SqlConnection con = new SqlConnection((@"Data Source=DENNIS-PC\SQLEXPRESS;Initial Catalog=helpdesk;Integrated Security=True")); string filepath = cust_import_filedialog1.FileName.ToString(); StreamReader sr = new StreamReader(filepath); string line = sr.ReadLine(); string[] value = line.Split(','); DataTable dt = new DataTable(); DataRow row; foreach (string dc in value) { dt.Columns.Add(new DataColumn(dc)); } while (!sr.EndOfStream) { value = sr.ReadLine().Split(','); if (value.Length == dt.Columns.Count) { row = dt.NewRow(); row.ItemArray = value; dt.Rows.Add(row); } } SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock); bc.DestinationTableName = "klanten"; bc.BatchSize = dt.Rows.Count; con.Open(); bc.WriteToServer(dt); bc.Close(); con.Close(); MessageBox.Show("Klanten zijn succesvol geimporteerd in de klanten database."); } } }
/// <summary> /// 游戏战斗日志 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool LogFightDb(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_Fight"; sqlbulk.ColumnMappings.Add(0, "ApplicationId"); sqlbulk.ColumnMappings.Add(1, "SubId"); sqlbulk.ColumnMappings.Add(2, "LineId"); sqlbulk.ColumnMappings.Add(3, "RoomId"); sqlbulk.ColumnMappings.Add(4, "RoomType"); sqlbulk.ColumnMappings.Add(5, "FightType"); sqlbulk.ColumnMappings.Add(6, "ChangeTeam"); sqlbulk.ColumnMappings.Add(7, "PlayBegin"); sqlbulk.ColumnMappings.Add(8, "PlayEnd"); sqlbulk.ColumnMappings.Add(9, "UserCount"); sqlbulk.ColumnMappings.Add(10, "MapId"); sqlbulk.ColumnMappings.Add(11, "TeamA"); sqlbulk.ColumnMappings.Add(12, "TeamB"); sqlbulk.ColumnMappings.Add(13, "PlayResult"); sqlbulk.ColumnMappings.Add(14, "WinTeam"); sqlbulk.ColumnMappings.Add(15, "Detail"); sqlbulk.WriteToServer(dt); result = true; } catch (Exception ex) { if (log.IsErrorEnabled) { log.Error("Fight Log Error:" + ex.ToString()); } } finally { sqlbulk.Close(); dt.Clear(); } return(result); }
public void BulkInsert(DataTable dataTable) { SqlConnection connection = null; SqlTransaction transaction = null; SqlBulkCopy bulkCopy = null; try { connection = new SqlConnection(ConnectionString); bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = dataTable.TableName; bulkCopy.BatchSize = dataTable.Rows.Count; if (dataTable != null && dataTable.Columns.Count > 0) { foreach (DataColumn column in dataTable.Columns) { bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName); } } connection.Open(); if (dataTable != null && dataTable.Rows.Count != 0) { bulkCopy.WriteToServer(dataTable); } } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } throw ex; } finally { if (connection != null) { connection.Close(); } if (bulkCopy != null) { bulkCopy.Close(); } } }
public static void DBatchInsert(DataTable dt) { string connectionString = "your connection string"; SqlConnection conn = new SqlConnection (connectionString); SqlBulkCopy sbc = new SqlBulkCopy (conn); sbc.DestinationTableName = "your table name"; sbc.BatchSize = dt.Rows.Count; try { conn.Open (); if (dt != null && dt.Rows.Count != 0) { sbc.WriteToServer (dt); } } catch (Exception ex) { throw ex; } finally { sbc.Close(); conn.Close(); } }
/// <summary> /// 铁匠铺记录保存到数据库中 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool LogItemDb(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_Item"; sqlbulk.ColumnMappings.Add(0, "ApplicationId"); sqlbulk.ColumnMappings.Add(1, "SubId"); sqlbulk.ColumnMappings.Add(2, "LineId"); sqlbulk.ColumnMappings.Add(3, "EnterTime"); sqlbulk.ColumnMappings.Add(4, "UserId"); sqlbulk.ColumnMappings.Add(5, "Operation"); sqlbulk.ColumnMappings.Add(6, "ItemName"); sqlbulk.ColumnMappings.Add(7, "ItemID"); sqlbulk.ColumnMappings.Add(8, "AddItem"); sqlbulk.ColumnMappings.Add(9, "BeginProperty"); sqlbulk.ColumnMappings.Add(10, "EndProperty"); sqlbulk.ColumnMappings.Add(11, "Result"); sqlbulk.WriteToServer(dt); result = true; dt.Clear(); } catch (Exception ex) { if (log.IsErrorEnabled) { log.Error("Smith Log Error:" + ex.ToString()); } } finally { sqlbulk.Close(); } return(result); }
/// <summary> /// 游戏战斗日志 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool LogFightDb(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_Fight"; sqlbulk.ColumnMappings.Add(0, "ApplicationId"); sqlbulk.ColumnMappings.Add(1, "SubId"); sqlbulk.ColumnMappings.Add(2, "LineId"); sqlbulk.ColumnMappings.Add(3, "RoomId"); sqlbulk.ColumnMappings.Add(4, "RoomType"); sqlbulk.ColumnMappings.Add(5, "FightType"); sqlbulk.ColumnMappings.Add(6, "ChangeTeam"); sqlbulk.ColumnMappings.Add(7, "PlayBegin"); sqlbulk.ColumnMappings.Add(8, "PlayEnd"); sqlbulk.ColumnMappings.Add(9, "UserCount"); sqlbulk.ColumnMappings.Add(10, "MapId"); sqlbulk.ColumnMappings.Add(11, "TeamA"); sqlbulk.ColumnMappings.Add(12, "TeamB"); sqlbulk.ColumnMappings.Add(13, "PlayResult"); sqlbulk.ColumnMappings.Add(14, "WinTeam"); sqlbulk.ColumnMappings.Add(15, "Detail"); sqlbulk.WriteToServer(dt); result = true; } catch (Exception ex) { if (log.IsErrorEnabled) log.Error("Fight Log Error:" + ex.ToString()); } finally { sqlbulk.Close(); dt.Clear(); } return result; }
/// <summary> /// 将对象集合批量插入 /// 20180810 fj /// 列名需区分大小写 /// </summary> /// <typeparam name="T">泛型</typeparam> /// <param name="list">对象集合</param> public static int BatchInsert <T>(List <T> list) { //DataTable dt = ConvertToDataTable(list); DataTable dt = list.ToDataTable(a => new object[] { list }); using (System.Data.SqlClient.SqlBulkCopy bulkcopy = new System.Data.SqlClient.SqlBulkCopy(constr)) { //1 指定数据插入目标表名称 bulkcopy.DestinationTableName = dt.TableName; bulkcopy.BulkCopyTimeout = 660; //2 告诉SqlBulkCopy对象 内存表中的 OrderNO1和Userid1插入到OrderInfos表中的哪些列中 foreach (DataColumn dc in dt.Columns) { bulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } //3 将内存表dt中的数据一次性批量插入到OrderInfos表中 bulkcopy.WriteToServer(dt); bulkcopy.Close(); } return(list.Count); }
public void CreateChunks() { dbKeyOffset.Create(Settings.Current.Building.Id.Value); dbChunk.ClearChunks(Settings.Current.Building.Id.Value); dbSource.CreateChunkTable(); foreach (var chunk in GetPersonKeys(Settings.Current.Builder.BatchSize)) { var chunkId = dbChunk.AddChunk(Settings.Current.Building.Id.Value); using (var bulkCopy = new SqlBulkCopy(Settings.Current.Building.SourceConnectionString)) { bulkCopy.Write( new ChunkDataReader( chunk.Select( c => new ChunkRecord {Id = chunkId, PersonId = Convert.ToInt64(c.Key), PersonSource = c.Value}) .ToList()), "_chunks"); bulkCopy.Close(); } } dbSource.CreateIndexesChunkTable(); }
public static void BulkToDb(DataTable dt) { var setting = JsonOperater.GetAppSetting(); var sqlConn = new SqlConnection(setting.Connection); var bulkCopy = new SqlBulkCopy(sqlConn) { DestinationTableName = "IISLogger", BatchSize = dt.Rows.Count }; try { sqlConn.Open(); if (dt.Rows.Count != 0) bulkCopy.WriteToServer(dt); } catch (Exception ex) { throw; } finally { sqlConn.Close(); bulkCopy.Close(); } }
public void BulkCopyToDatabase() { using (System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(_excelConnection)) { try { excelConnection.Open(); using (System.Data.OleDb.OleDbCommand selectAllComand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + _worksheetName + "$]")) { selectAllComand.Connection = excelConnection; using (System.Data.OleDb.OleDbDataReader excelReader = selectAllComand.ExecuteReader()) { //Gets scheme of datasheet to create table of same schema for bulk insert System.Data.DataTable dt = excelReader.GetSchemaTable(); ////Do the bulk insert using (System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(_dbConn)) { bc.BatchSize = 100; bc.DestinationTableName = "[dbo].[" + _tempTableName + "]"; //tmpReleaseNumbers_BulkLoad]"; // 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(); } } } catch (Exception ex) { throw new Exception("BulkCreate.PrattReleaseNumbers.CreateTempReleaseNumbers: " + ex.Message); } } }
/// <summary> /// 网内干扰分析 /// </summary> /// <returns></returns> public Result defectAnalysis() { // 经纬度转换为栅格ID LTE.Geometric.Point pMin = new Geometric.Point(); pMin.X = this.minLongitude; pMin.Y = this.minLatitude; pMin.Z = 0; pMin = LTE.Utils.PointConvertByProj.Instance.GetProjectPoint(pMin); LTE.Geometric.Point pMax = new Geometric.Point(); pMax.X = this.maxLongitude; pMax.Y = this.maxLatitude; pMax.Z = 0; pMax = LTE.Utils.PointConvertByProj.Instance.GetProjectPoint(pMax); int minxid = 0, minyid = 0, maxxid = 0, maxyid = 0; GridHelper.getInstance().XYToGGrid(pMin.X, pMin.Y, ref minxid, ref minyid); GridHelper.getInstance().XYToGGrid(pMax.X, pMax.Y, ref maxxid, ref maxyid); //GridHelper.getInstance().LngLatToGGrid(pMin.X, pMin.Y, ref minxid, ref minyid); //GridHelper.getInstance().LngLatToGGrid(pMax.X, pMax.Y, ref maxxid, ref maxyid); double T = 6; // 门限 double T1 = 6; Hashtable ht = new Hashtable(); ht["MinGXID"] = minxid; ht["MinGYID"] = minyid; ht["MaxGXID"] = maxxid; ht["MaxGYID"] = maxyid; System.Data.DataTable dtable = new System.Data.DataTable(); dtable.Columns.Add("GXID"); dtable.Columns.Add("GYID"); dtable.Columns.Add("GZID"); dtable.Columns.Add("type"); dtable.Columns.Add("CI"); dtable.Columns.Add("PCI"); dtable.Columns.Add("ReceivedPowerdbm"); dtable.Columns.Add("explain"); double effective = -110; // 有效信号阈值 DataTable tb1 = IbatisHelper.ExecuteQueryForDataTable("CoverAnalysis", ht); DataTable tb2 = IbatisHelper.ExecuteQueryForDataTable("CoverAnalysis3D", ht); int cnt = tb1.Rows.Count + tb2.Rows.Count; //if (cnt < 1) //{ // return new Result(false, "当前区域未进行覆盖计算"); //} if (cnt < 0.85 * (maxxid - minxid + 1) * (maxyid - minyid + 1)) { return(new Result(false, "当前区域已计算覆盖率过小,请对此区域重新计算小区覆盖")); } #region 地面初始化 DataTable tb = tb1; int n = tb.Rows.Count; //if (n < 1) // return new Result(false, "当前区域未进行覆盖计算"); Dictionary <string, List <Sub> > dic = new Dictionary <string, List <Sub> >(); // 栅格的其它信息 Dictionary <string, List <double> > xy = new Dictionary <string, List <double> >(); // 栅格位置 int weak = 0; // 弱覆盖 List <Analysis> exessive = new List <Analysis>(); // 过覆盖 List <Analysis> overlap = new List <Analysis>(); // 重叠覆盖 int pcim3 = 0; // pci mod 3 对打 int pcic = 0; // pci 冲突 int pcih = 0; // pci 混淆 for (int i = 0; i < n; i++) { int x = Convert.ToInt32(tb.Rows[i]["GXID"].ToString()); int y = Convert.ToInt32(tb.Rows[i]["GYID"].ToString()); double minX = Convert.ToDouble(tb.Rows[i]["minX"].ToString()); double minY = Convert.ToDouble(tb.Rows[i]["minY"].ToString()); double maxX = Convert.ToDouble(tb.Rows[i]["maxX"].ToString()); double maxY = Convert.ToDouble(tb.Rows[i]["maxY"].ToString()); double p = Convert.ToDouble(tb.Rows[i]["ReceivedPowerdbm"].ToString()); int ci = Convert.ToInt32(tb.Rows[i]["ci"].ToString()); int pci = Convert.ToInt32(tb.Rows[i]["pci"].ToString()); string key = string.Format("{0},{1}", x, y); if (!xy.Keys.Contains(key)) { xy[key] = new List <double>(); xy[key].Add(minX); xy[key].Add(minY); xy[key].Add(maxX); xy[key].Add(maxY); } if (dic.Keys.Contains(key)) { dic[key].Add(new Sub(p, ci, pci)); } else { dic[key] = new List <Sub>(); dic[key].Add(new Sub(p, ci, pci)); } } #endregion #region 立体初始化 int weak1 = 0; // 弱覆盖点数 int pcim31 = 0; // pci mod3 对打 int pcih1 = 0; // pci 混淆数 int pcic1 = 0; // pci 冲突数 List <Analysis> overlap1 = new List <Analysis>(); // 重叠覆盖 List <Analysis> exessive1 = new List <Analysis>(); // 过覆盖 tb = tb2; n = tb.Rows.Count; //if (n < 1) // return new Result(false, "当前区域未进行覆盖计算"); double h = GridHelper.getInstance().getGHeight(); Dictionary <string, List <Sub> > dic1 = new Dictionary <string, List <Sub> >(); Dictionary <string, List <double> > xy1 = new Dictionary <string, List <double> >(); for (int i = 0; i < n; i++) { int x = Convert.ToInt32(tb.Rows[i]["GXID"].ToString()); int y = Convert.ToInt32(tb.Rows[i]["GYID"].ToString()); int z = Convert.ToInt32(tb.Rows[i]["level"].ToString()); double minX = Convert.ToDouble(tb.Rows[i]["minX"].ToString()); double minY = Convert.ToDouble(tb.Rows[i]["minY"].ToString()); double maxX = Convert.ToDouble(tb.Rows[i]["maxX"].ToString()); double maxY = Convert.ToDouble(tb.Rows[i]["maxY"].ToString()); double p = Convert.ToDouble(tb.Rows[i]["ReceivedPowerdbm"].ToString()); int ci = Convert.ToInt32(tb.Rows[i]["ci"].ToString()); int pci = Convert.ToInt32(tb.Rows[i]["pci"].ToString()); string key = string.Format("{0},{1},{2}", x, y, z); if (!dic1.Keys.Contains(key)) { xy1[key] = new List <double>(); xy1[key].Add(minX); xy1[key].Add(minY); xy1[key].Add(maxX); xy1[key].Add(maxY); xy1[key].Add(z * h); } if (dic1.Keys.Contains(key)) { dic1[key].Add(new Sub(p, ci, pci)); } else { dic1[key] = new List <Sub>(); dic1[key].Add(new Sub(p, ci, pci)); } } #endregion LoadInfo loadInfo = new LoadInfo(); int count = dic.Keys.Count + dic1.Keys.Count; int updateSize = (int)Math.Round(count * 0.02); loadInfo.loadCountAdd(count); int cnt1 = 0; #region 地面 foreach (string key in dic.Keys) { if (updateSize == ++cnt1) { loadInfo.loadHashAdd(updateSize); cnt1 = 0; } dic[key].Sort(new SubCompare()); // 按功率从大到小排序 string[] id = key.Split(','); int xid = Convert.ToInt32(id[0]); int yid = Convert.ToInt32(id[1]); // 弱覆盖 if (dic[key][0].p < -95)// && dic[key][m] > -110) { weak++; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.Weak; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = ""; dtable.Rows.Add(thisrow); } // 当前栅格只接收到了1个信号,不存在pci模3对打、过覆盖、重叠覆盖、pci冲突、pci混淆 if (dic[key].Count < 2) { continue; } // 过覆盖 if (dic[key][0].p > effective && dic[key][1].p > effective && Math.Abs(dic[key][0].p - dic[key][1].p) < T1) { Analysis A = new Analysis(xid, yid, xy[key][0], xy[key][1], xy[key][2], xy[key][3]); exessive.Add(A); System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.Excessive; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1};{2}:{3}", dic[key][0].ci, dic[key][0].p, dic[key][1].ci, dic[key][1].p);; dtable.Rows.Add(thisrow); } // 当前栅格接收到了 2 个信号 if (dic[key].Count == 2) { // pci mod3 对打 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][0].pci % 3 == dic[key][1].pci % 3) { ++pcim3; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.PCImod3; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5}", dic[key][0].ci, dic[key][0].pci, dic[key][0].p, dic[key][1].ci, dic[key][1].pci, dic[key][1].p); dtable.Rows.Add(thisrow); } // pci 冲突 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][0].pci == dic[key][1].pci) { ++pcic; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.PCIconflict; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5}", dic[key][0].ci, dic[key][0].pci, dic[key][0].p, dic[key][1].ci, dic[key][1].pci, dic[key][1].p); dtable.Rows.Add(thisrow); } } else if (dic[key].Count > 2) // 当前栅格接收到了>2个信号 { // 重叠覆盖 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][2].p > effective && Math.Abs(dic[key][0].p - dic[key][1].p) < T && Math.Abs(dic[key][0].p - dic[key][2].p) < T && Math.Abs(dic[key][1].p - dic[key][2].p) < T) { Analysis A = new Analysis(xid, yid, xy[key][0], xy[key][1], xy[key][2], xy[key][3]); overlap.Add(A); System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.Overlapped; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1};{2}:{3};{4}:{5}", dic[key][0].ci, dic[key][0].p, dic[key][1].ci, dic[key][1].p, dic[key][2].ci, dic[key][2].p); dtable.Rows.Add(thisrow); } // pci mod3 对打 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][2].p > effective && (dic[key][0].pci % 3 == dic[key][1].pci % 3 || dic[key][0].pci % 3 == dic[key][2].pci % 3)) { ++pcim3; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.PCImod3; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic[key][0].ci, dic[key][0].pci, dic[key][0].p, dic[key][1].ci, dic[key][1].pci, dic[key][1].p, dic[key][2].ci, dic[key][2].pci, dic[key][2].p); dtable.Rows.Add(thisrow); } // pci 冲突 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][2].p > effective && (dic[key][0].pci == dic[key][1].pci || dic[key][0].pci == dic[key][2].pci)) { ++pcic; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.PCIconflict; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic[key][0].ci, dic[key][0].pci, dic[key][0].p, dic[key][1].ci, dic[key][1].pci, dic[key][1].p, dic[key][2].ci, dic[key][2].pci, dic[key][2].p); dtable.Rows.Add(thisrow); } // pci 混淆 if (dic[key][0].p > effective && dic[key][1].p > effective && dic[key][2].p > effective && dic[key][1].pci == dic[key][2].pci) { ++pcih; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = 0; thisrow["type"] = (short)DefectType.PCIconfusion; thisrow["CI"] = dic[key][0].ci; thisrow["PCI"] = dic[key][0].pci; thisrow["ReceivedPowerdbm"] = dic[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic[key][0].ci, dic[key][0].pci, dic[key][0].p, dic[key][1].ci, dic[key][1].pci, dic[key][1].p, dic[key][2].ci, dic[key][2].pci, dic[key][2].p); dtable.Rows.Add(thisrow); } } } #endregion //----------------------------------------------- #region 立体 foreach (string key in dic1.Keys) { if (updateSize == ++cnt1) { loadInfo.loadHashAdd(updateSize); cnt1 = 0; } dic1[key].Sort(new SubCompare()); int m = dic1[key].Count - 1; string[] id = key.Split(','); int xid = Convert.ToInt32(id[0]); int yid = Convert.ToInt32(id[1]); int zid = Convert.ToInt32(id[2]); if (dic1[key][0].p < -95) // && dic1[key][m] > -110) { ++weak1; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.Weak; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = ""; dtable.Rows.Add(thisrow); } if (dic1[key][0].p < -110) { continue; } // 当前栅格只接收到一个信号,不存在pci模3对打、过覆盖、重叠覆盖、PCI冲突、PCI混淆 if (dic1[key].Count < 2) { continue; } // 过覆盖 if (dic1[key][0].p > effective && dic1[key][1].p > effective && Math.Abs(dic1[key][0].p - dic1[key][1].p) < T1) { Analysis A = new Analysis(xid, yid, zid, xy1[key][0], xy1[key][1], xy1[key][2], xy1[key][3], zid * h); exessive1.Add(A); System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.Excessive; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1};{2}:{3}", dic1[key][0].ci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].p);; dtable.Rows.Add(thisrow); } // 当前栅格接收到了 2 个信号 if (dic1[key].Count == 2) { // pci mod3 对打 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][0].pci % 3 == dic1[key][1].pci % 3) { ++pcim31; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.PCImod3; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5}", dic1[key][0].ci, dic1[key][0].pci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].pci, dic1[key][1].p); dtable.Rows.Add(thisrow); } // pci 冲突 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][0].pci == dic1[key][1].pci) { ++pcic1; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.PCIconflict; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5}", dic1[key][0].ci, dic1[key][0].pci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].pci, dic1[key][1].p); dtable.Rows.Add(thisrow); } } else if (dic1[key].Count > 2) // 当前栅格接收到了>2个信号 { // 重叠覆盖 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][2].p > effective && Math.Abs(dic1[key][0].p - dic1[key][1].p) < T && Math.Abs(dic1[key][0].p - dic1[key][2].p) < T && Math.Abs(dic1[key][1].p - dic1[key][2].p) < T) { Analysis A = new Analysis(xid, yid, zid, xy1[key][0], xy1[key][1], xy1[key][2], xy1[key][3], zid * h); overlap1.Add(A); System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.Overlapped; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1};{2}:{3};{4}:{5}", dic1[key][0].ci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].p, dic1[key][2].ci, dic1[key][2].p); dtable.Rows.Add(thisrow); } // pci mod3 对打 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][2].p > effective && (dic1[key][0].pci % 3 == dic1[key][1].pci % 3 || dic1[key][0].pci % 3 == dic1[key][2].pci % 3)) { ++pcim31; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.PCImod3; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic1[key][0].ci, dic1[key][0].pci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].pci, dic1[key][1].p, dic1[key][2].ci, dic1[key][2].pci, dic1[key][2].p); dtable.Rows.Add(thisrow); } // pci 冲突 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][2].p > effective && (dic1[key][0].pci == dic1[key][1].pci || dic1[key][0].pci == dic1[key][2].pci)) { ++pcic1; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.PCIconflict; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic1[key][0].ci, dic1[key][0].pci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].pci, dic1[key][1].p, dic1[key][2].ci, dic1[key][2].pci, dic1[key][2].p); dtable.Rows.Add(thisrow); } // pci 混淆 if (dic1[key][0].p > effective && dic1[key][1].p > effective && dic1[key][2].p > effective && dic1[key][1].pci == dic1[key][2].pci) { ++pcih1; System.Data.DataRow thisrow = dtable.NewRow(); thisrow["GXID"] = xid; thisrow["GYID"] = yid; thisrow["GZID"] = zid; thisrow["type"] = (short)DefectType.PCIconfusion; thisrow["CI"] = dic1[key][0].ci; thisrow["PCI"] = dic1[key][0].pci; thisrow["ReceivedPowerdbm"] = dic1[key][0].p; thisrow["explain"] = string.Format("{0}:{1},{2};{3}:{4},{5};{6}:{7},{8}", dic1[key][0].ci, dic1[key][0].pci, dic1[key][0].p, dic1[key][1].ci, dic1[key][1].pci, dic1[key][1].p, dic1[key][2].ci, dic1[key][2].pci, dic1[key][2].p); 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 = "tbDefect"; bcp.WriteToServer(dtable); bcp.Close(); } dtable.Clear(); double total = dic.Count + dic1.Count; double totalG = dic.Count; double totalB = dic1.Count; string mess = string.Format(" 地面---总栅格数:{0}\n 弱覆盖点数:{1}, 占比:{2}\n 过覆盖点数:{3}, 占比:{4}\n 重叠覆盖点数:{5}, 占比:{6}\n PCI模3对打点数:{7}, 占比:{8}\n PCI冲突点数:{9}, 占比:{10}\n PCI混淆点数:{11}, 占比:{12}\n", dic.Count, weak, weak / totalG, exessive.Count, exessive.Count / totalG, overlap.Count, overlap.Count / totalG, pcim3, pcim3 / totalG, pcic, pcic / totalG, pcih, pcih / totalG); string mess3D = string.Format(" 立体---总栅格数:{0}\n 弱覆盖点数:{1}, 占比:{2}\n 过覆盖点数:{3}, 占比:{4}\n 重叠覆盖点数:{5}, 占比:{6}\n PCI模3对打点数:{7}, 占比:{8}\n PCI冲突点数:{9}, 占比:{10}\n PCI混淆点数:{11}, 占比:{12}\n", dic1.Count, weak1, weak1 / totalB, exessive1.Count, exessive1.Count / totalB, overlap1.Count, overlap1.Count / totalB, pcim31, pcim31 / totalB, pcic1, pcic1 / totalB, pcih1, pcih1 / totalB); string messAll = string.Format(" 总计---总栅格数:{0}\n 弱覆盖点数:{1}, 占比:{2}\n 过覆盖点数:{3}, 占比:{4}\n 重叠覆盖点数:{5}, 占比:{6}\n PCI模3对打点数:{7}, 占比:{8}\n PCI冲突点数:{9}, 占比:{10}\n PCI混淆点数:{11}, 占比:{12}\n", total, weak + weak1, (weak + weak1) / total, (exessive.Count + exessive1.Count), (exessive.Count + exessive1.Count) / total, (overlap.Count + overlap1.Count), (overlap.Count + overlap1.Count) / total, (pcim3 + pcim31), (pcim3 + pcim31) / total, (pcic + pcic1), (pcic + pcic1) / total, (pcih + pcih1), (pcih + pcih1) / total); return(new Result(true, mess + mess3D + messAll)); #endregion }
public override void Dispose() { bulkCopy.Close(); connection.Close(); }