private static void UpdateDatabaseSub(object obj)
{
UpdataDataBaseClass udbc = (UpdataDataBaseClass)obj;
string tableName = udbc.tableNam;
string[,] exceldata = udbc.exceldata;
CDataBaseInfo dbInfo = udbc.dbInfo;
int index = udbc.index;
int part = udbc.partNum;
MySqlConnection con = LinkDataBase(dbInfo.address, dbInfo.userName, dbInfo.password, dbInfo.databaseName);
//MySqlConnection con = udbc.connection;
MySqlCommand cmd;
// TextBox text = udbc.text;
try
{
con.Open();
cmd = con.CreateCommand();
for (int i = 5; i < exceldata.GetLength(0); i++)
{
cmd.CommandText = string.Format("insert into {0} values (", tableName);
for (int j = 0; j < exceldata.GetLength(1); j++)
{
/*if (exceldata[0, j].Contains("备注") || exceldata[1, j].Contains("备注") || exceldata[2, j].Contains("备注") || exceldata[3, j].Contains("备注") || exceldata[4, j].Contains("备注"))
* {
* continue;
* }*/
if (!udbc.colNames.Contains(exceldata[1, j]))
{
continue;
}
if (exceldata[i, j].Length == 255)
{
Debug.Log(string.Format("{0}表第{1}行第{2}列字符长度为255", tableName, (part - 5) * index + i + 1, j + 1), 2);
}
//if (j< exceldata.GetLength(1)-1)
{
if (exceldata[2, j].Contains("varchar") || exceldata[2, j].Contains("datetime"))
{
exceldata[i, j] = exceldata[i, j].Replace("，", ",").Replace("：", ":").Replace("|", "|").Replace("“", "\"").Replace("”", "\"")
.Replace("‘’", "'").Replace("’", "'").Replace("｛", "{").Replace("｝", "}").Replace("'", "\\'");
cmd.CommandText += "'" + exceldata[i, j] + "',";
}
else
{
if (string.IsNullOrEmpty(exceldata[i, j]))
{
exceldata[i, j] = "NULL";
}
cmd.CommandText += exceldata[i, j] + ",";
}
}
//else
//{
// if (exceldata[2, j].Contains("varchar"))
// {
// cmd.CommandText += "'" + exceldata[i, j] + "'";
// }
// else
// {
// if (string.IsNullOrEmpty(exceldata[i, j]))
// {
// exceldata[i, j] = "NULL";
// }
// cmd.CommandText += exceldata[i, j];
// }
//}
}
cmd.CommandText += ");";
while (true)
{
string str = cmd.CommandText.Substring(cmd.CommandText.Length - 3, 1);
if (str == ",")
{
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 3, 1);
}
else
{
break;
}
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ee)
{
Debug.Log(string.Format("{0}表第{1}行 {2} {3}", tableName, (part - 5) * index + i + 1, cmd.CommandText, ee.Message));
}
}
if (index != -1)
{
lock (overFlag)
{
udbc.mutex.WaitOne();
overFlag[index] = true;
bool isOver = true;
for (int i = 0; i < overFlag.Length; i++)
{
if (overFlag[i] == false)
{
isOver = false;
break;
}
}
udbc.mutex.ReleaseMutex();
if (isOver)
{
currentOver = true;
Debug.Log(string.Format("{0}表更新成功!", tableName));
}
}
}
else
{
currentOver = true;
Debug.Log(string.Format("{0}表更新成功!", tableName));
}
}
catch (Exception e)
{
currentOver = true;
Debug.Log("更新数据库失败 " + e.Message + "");
}
finally
{
con.Close();
}
}
public static void UpdateDataBase(CDataBaseInfo dbInfo, string tableName, ExcelData excel)
{
string[,] exceldata = Global.GetSheetFromExcel(excel.ExcelName, excel.SheetName);
if (exceldata == null)
{
return;
}
MySqlConnection con = LinkDataBase(dbInfo.address, dbInfo.userName, dbInfo.password, dbInfo.databaseName);
if (con != null)
{
MySqlCommand cmd;
currentOver = false;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName +
"' AND TABLE_SCHEMA = '" + dbInfo.databaseName + "';";
//cmd.ExecuteNonQuery();
MySqlDataReader reader = null;
reader = cmd.ExecuteReader();
List <string> colNames = new List <string>();
if (reader.HasRows)
{
while (reader.Read())
{
try
{
string name = reader.GetString("COLUMN_NAME"); //字段名
colNames.Add(name);
}
catch (Exception e)
{
Debug.Log(string.Format("{0}:{1}", tableName, e.Message));
}
}
}
reader.Close();
if (colNames.Count <= 0)
{
Debug.Log(string.Format("{0}表的列数为0", tableName));
return;
}
cmd.CommandText = string.Format("delete from {0};", tableName);
cmd.ExecuteNonQuery();
Debug.Log(string.Format("{0}表已清空", tableName));
Debug.Log(string.Format("{0}表写入数据中...", tableName));
con.Close();
//UpdateDatabaseSub(con, cmd, tableName, exceldata, text);
Mutex mutex = new Mutex();
/* int threadNum = Process.GetCurrentProcess().Threads.Count;
* while (threadNum>80)
* {
* Thread.Sleep(100);
* threadNum = Process.GetCurrentProcess().Threads.Count;
* }*/
/*if (exceldata.GetLength(0) > 50)
* {
* int left = (exceldata.GetLength(0)+45) / 10;
* int right = left * 2;
* int line = exceldata.GetLength(1);
* string[,] excel1 = new string[left, line];
* string[,] excel2 = new string[left, line];
* string[,] excel3 = new string[left, line];
* string[,] excel4 = new string[left, line];
* string[,] excel5 = new string[left, line];
* string[,] excel6 = new string[left, line];
* string[,] excel7 = new string[left, line];
* string[,] excel8 = new string[left, line];
* string[,] excel9 = new string[left, line];
* string[,] excel10 = new string[exceldata.GetLength(0)- left * 9+45, line];
* Array.Copy(exceldata, 0, excel1, 0, 5* line);
* Array.Copy(exceldata, 0, excel2, 0, 5 * line);
* Array.Copy(exceldata, 0, excel3, 0, 5 * line);
* Array.Copy(exceldata, 0, excel4, 0, 5 * line);
* Array.Copy(exceldata, 0, excel5, 0, 5 * line);
* Array.Copy(exceldata, 0, excel6, 0, 5 * line);
* Array.Copy(exceldata, 0, excel7, 0, 5 * line);
* Array.Copy(exceldata, 0, excel8, 0, 5 * line);
* Array.Copy(exceldata, 0, excel9, 0, 5 * line);
* Array.Copy(exceldata, 0, excel10, 0, 5 * line);
* Array.Copy(exceldata, 5* line, excel1, 5 * line, (left-5) * line);
* Array.Copy(exceldata, left * line, excel2, 5 * line, (left-5) * line);
* Array.Copy(exceldata, (left * 2 - 5) * line, excel3, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 3 - 10) * line, excel4, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 4 - 15) * line, excel5, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 5 - 20) * line, excel6, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 6 - 25) * line, excel7, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 7 - 30) * line, excel8, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 8 - 35) * line, excel9, 5 * line, (left - 5) * line);
* Array.Copy(exceldata, (left * 9 - 40) * line, excel10, 5 * line, (exceldata.GetLength(0)+40 - left * 9) * line);
* UpdataDataBaseClass udbc1 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel1,index=0,dbInfo=dbInfo,partNum=left,connection = con,colNames = colNames,mutex = mutex};
* UpdataDataBaseClass udbc2 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel2, index=1, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc3 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel3, index=2, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc4 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel4, index = 3, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc5 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel5, index = 4, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc6 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel6, index = 5, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc7 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel7, index = 6, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc8 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel8, index = 7, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc9 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel9, index = 8, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* UpdataDataBaseClass udbc10 = new UpdataDataBaseClass { tableNam = tableName, exceldata = excel10, index = 9, dbInfo = dbInfo, partNum = left, connection = con, colNames = colNames, mutex = mutex };
* Thread t1 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t2 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t3 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t4 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t5 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t6 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t7 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t8 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t9 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* Thread t10 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* overFlag = new bool[10];
* t1.Start(udbc1);
* t2.Start(udbc2);
* t3.Start(udbc3);
* t4.Start(udbc4);
* t5.Start(udbc5);
* t6.Start(udbc6);
* t7.Start(udbc7);
* t8.Start(udbc8);
* t9.Start(udbc9);
* t10.Start(udbc10);
* }
* else*/
{
UpdataDataBaseClass udbc = new UpdataDataBaseClass {
tableNam = tableName, exceldata = exceldata, index = -1, dbInfo = dbInfo, connection = con, colNames = colNames, mutex = mutex, partNum = 5
};
/*Thread t1 = new Thread(new ParameterizedThreadStart(UpdateDatabaseSub));
* t1.Start(udbc);*/
UpdateDatabaseSub(udbc);
}
}
catch (Exception e)
{
Debug.Log("更新数据库失败 " + e.Message, 3);
currentOver = true;
con.Close();
}
/*while (true)
* {
* Thread.Sleep(10);
* if (currentOver)
* {
* break;
* }
* }*/
}
}
