/// <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 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 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);
}
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 写入结束！");
}
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 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>
/// 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 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
// 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
/// <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;
}
public void GaMain()
{
DateTime t0 = DateTime.Now;
StreamWriter result;
string path1 = @"result.txt";
result = File.CreateText(path1);
// 初始误差
double sum1 = 0;
double sum2 = 0;
int i = 0;
foreach (string key in meaPwr.Keys)
{
if (i < smaObj)
{
sum1 += Math.Pow((rayDic[key].sumPwrDbm - meaPwr[key]), 2); // 局部，重点栅格，这里先假设为 1/3 ~ 1/4
}
else
{
sum2 += Math.Pow((rayDic[key].sumPwrDbm - meaPwr[key]), 2);
}
i++;
}
double err1 = Math.Sqrt(sum1 / smaObj); // 局部
double err2 = Math.Sqrt((sum1 + sum2) / meaPwr.Count); // 整体
result.WriteLine("初始局部误差与总体误差：");
result.WriteLine(err1 + "\t" + err2);
result.WriteLine();
string currentDirectory = System.Environment.CurrentDirectory;
string currentTime = DateTime.Now.ToString();
string path = @"galog.txt";
string path_avg = @"galog_avg.txt";
//创建并写入(将覆盖已有文件)
sw = File.CreateText(path);
swAvg = File.CreateText(path_avg);
generation = 0;
init();
evaluate(); //评价函数，可以由用户自定义，该函数取得每个基因的适应度
keep_the_best(); //保存每次遗传后的最佳基因
while (generation < MAXGENS)
{
generation++;
select(); //选择函数：用于最大化合并杰出模型的标准比例选择，保证最优秀的个体得以生存
crossover(); //杂交函数：选择两个个体来杂交，这里用单点杂交
mutate(); //变异函数：被该函数选中后会使得某一变量被一个随机的值所取代
report(); //报告模拟进展情况
evaluate(); //评价函数，可以由用户自定义，该函数取得每个基因的适应度
elitist(); //搜寻杰出个体函数：找出最好和最坏的个体。如果某代的最好个体比前一代的最好个体要坏，那么后者将会取代当前种群的最坏个体
}
sw.Close();
swAvg.Close();
double[] wight = { 0.7423, 0.2577, 0 }; //各目标的权重
Best = pareto.AHP(POPSIZE, wight);
result.WriteLine("每个场景的校正系数：");
for (i = 0; i < scenNum; i++)
{
for (int j = 0; j < coeNum; j++)
{
result.Write(Best.gen[i, j] + "\t");
}
result.WriteLine();
}
result.WriteLine();
result.WriteLine("局部误差与总体误差：");
for (i = 0; i < objNum; i++)
{
result.Write(Best.fitnessVec[i] + "\t");
}
result.WriteLine();
DateTime t1 = DateTime.Now;
result.WriteLine("耗时：" + (t1 - t0).TotalMilliseconds / 60000 + " min");
result.Close();
//System.Diagnostics.Process.Start("notepad.exe", "result.txt");
// 写入数据库
System.Data.DataTable dtable = new System.Data.DataTable();
dtable.Columns.Add("Scene");
dtable.Columns.Add("DirectCoefficient");
dtable.Columns.Add("ReflectCoefficient");
dtable.Columns.Add("DiffracteCoefficient");
for (int j = 0; j < scenNum; j++)
{
System.Data.DataRow thisrow = dtable.NewRow();
thisrow["Scene"] = j;
thisrow["DirectCoefficient"] = Best.gen[j, 0];
thisrow["ReflectCoefficient"] = Best.gen[j, 1];
thisrow["DiffracteCoefficient"] = Best.gen[j, 2];
dtable.Rows.Add(thisrow);
}
DB.IbatisHelper.ExecuteDelete("DeleteAdjCoefficient", null);
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(DB.DataUtil.ConnectionString))
{
bcp.BatchSize = dtable.Rows.Count;
bcp.BulkCopyTimeout = 1000;
bcp.DestinationTableName = "tbAdjCoefficient";
bcp.WriteToServer(dtable);
bcp.Close();
}
dtable.Clear();
}