應用環境：visual studio 2010開發工具，Database為Sql2008以上版本
最近在生產環境中需要開發一款應用程式，上傳電子檔(.csv)資料至Database
最初方案：
以txt方式打開Csv檔案，逐行進行數據上傳處理，代碼見下文。因為需要上傳N台機器產生的檔案，每天數據量非常龐大，並且在上傳時要進行篩選去重，并保留最新的（以測試時間為準）測試記錄，導致以上方案在執行時，當天的資料無法在當天上傳完成，嚴重影響第二天的資料匯總和報表的生成。
改善方案：
因為資料太多太慢被老闆不知道K了多少次。本來想要使用多線程解決，但是考慮到線程間的安全問題，最終還是放棄額。後來思考為什麼不能把整個Csv檔案用放入Table中進行上傳，減少Database的訪問次數，并降低佔用網絡帶寬。
話不多少，先上改善前後的效果圖相差竟然有12秒之多.效率提升43倍之多。
詳細代碼如下：
獲取檔案并逐行讀取資料代碼
private void Readlog()
{
state = false;
foreach (string a in Directory.GetFiles(logfile, "*.csv"))
{
if (GetDataSet.FileCopy(a, logbak + "\\" + a.Substring(a.LastIndexOf("\\") + 1)) == true)
{
string[] read = File.ReadAllLines(a, Encoding.GetEncoding("GB18030"));
for (int i = 1; i < read.Length; i++)
{
if (DataSave(a.Substring(a.LastIndexOf("\\") + 1), read[i].ToString()) == false)
{
ErrorWrite(a.Substring(a.LastIndexOf("\\") + 1) + ":LINE(" + i + ")Write Error");
continue;
}
}
GetDataSet.DeleteFile(a);
}
}
}
上傳資料代碼
private bool DataSave(string a, string sr)
{
try
{
f = false;
SqlConnection con = new SqlConnection();
SqlCommand com = new SqlCommand();
string sql;
con.ConnectionString = "#####";
//ArrayList Panel = new ArrayList();
string[] Panel;
Panel = sr.ToString().Split(',');
com.CommandType = CommandType.Text;
sql = "Insert Avi_Data " +
"([CreateMachine No],[Machie Model],[Model no],[Panel Barcode],[MCH],[Board ID],[Pin NO],[Bad board result],[Pitch FINGER]" +
",[Pitch FINGER(Spec)],[Width FINGER],[Width FINGER (Spec)],[FINGER to Board EDGE SMALL],[FINGER to Board EDGE SMALL(Spec)],[FINGER to Board EDGE LARGE],[FINGER to Board EDGE LARGE(Spec)]" +
",[PCB Width_Finger Area],[PCB Width_Finger Airea (Spec)],[CreateDatetime],[FileName]) values(";
for (int i = 0; i < Panel.Length; i++)
{
sql = sql + "'" + Strings.StrConv(Panel[i].ToString().Trim(), VbStrConv.TraditionalChinese, 0) + "',";
}
sql = sql + "Getdate(),'" + a + "')";
com.CommandText = sql;
com.Connection = con;
con.Open();
com.ExecuteNonQuery();
con.Close();
AddItem(Panel[3].ToString() + ":" + Panel[5].ToString()+"測試資料已上傳！");
return true;
}
catch(Exception ex)
{
ErrorWrite(ex.ToString());
return false;
}
}
改善后方案實施步驟：
1、 在DataBase中創建table變量。
CREATE TYPE [dbo].[MultiRowsInsert] AS TABLE
（
）
2、 創建對應的數據保存的存儲過程。
CREATE PROCEDURE [dbo].[InsertMultiRow]
@DataTable dbo.MultiRowsInsert readonly
as
declare @ID int,@MaxID int
set @ID=1
set @MaxID=(Select max(ID) from @DataTable)
while @ID<=@MaxID
begin
insert Avi_Data
select[CreateMachine No],[Machie Model],[Model no],[Panel Barcode],[MCH],[Board ID],[Pin NO],[Bad board result]
,[Pitch FINGER],[Pitch FINGER(Spec)],[Width FINGER],[Width FINGER (Spec)],[FINGER to Board EDGE SMALL]
,[FINGER to Board EDGE SMALL(Spec)],[FINGER to Board EDGE LARGE],[FINGER to Board EDGE LARGE(Spec)]
,[PCB Width_Finger Area],[PCB Width_Finger Airea (Spec)],GETDATE(),[FileName]
from @DataTable
where ID=@ID
set @ID=@ID+1
end
3、 修改程式代碼。
以下為讀取Csv檔案代碼
private void Readlog()
{
state = false;
foreach (string a in Directory.GetFiles(logfile, "*.csv"))
{
if (GetDataSet.FileCopy(a, logbak + "\\" + a.Substring(a.LastIndexOf("\\") + 1)) == true)
{
string[] read = File.ReadAllLines(a, Encoding.GetEncoding("GB18030"));
DataTable TableInsert = new DataTable();
TableInsert.Columns.Add("ID", typeof(int));
TableInsert.Columns.Add("CreateMachine No", typeof(string));
TableInsert.Columns.Add("Machie Model", typeof(string));
TableInsert.Columns.Add("Model no", typeof(string));
TableInsert.Columns.Add("Panel Barcode", typeof(string));
TableInsert.Columns.Add("MCH", typeof(string));
TableInsert.Columns.Add("Board ID", typeof(string));
TableInsert.Columns.Add("Pin NO", typeof(string));
TableInsert.Columns.Add("Bad board result", typeof(string));
TableInsert.Columns.Add("Pitch FINGER", typeof(string));
TableInsert.Columns.Add("Pitch FINGER(Spec)", typeof(string));
TableInsert.Columns.Add("Width FINGER", typeof(string));
TableInsert.Columns.Add("Width FINGER (Spec)", typeof(string));
TableInsert.Columns.Add("FINGER to Board EDGE SMALL", typeof(string));
TableInsert.Columns.Add("FINGER to Board EDGE SMALL(Spec)", typeof(string));
TableInsert.Columns.Add("FINGER to Board EDGE LARGE", typeof(string));
TableInsert.Columns.Add("FINGER to Board EDGE LARGE(Spec)", typeof(string));
TableInsert.Columns.Add("PCB Width_Finger Area", typeof(string));
TableInsert.Columns.Add("PCB Width_Finger Airea (Spec)", typeof(string));
TableInsert.Columns.Add("FileName", typeof(string));
for (int i = 1; i < read.Length; i++)
{
string[] ReadCloumn;
int c=1;
ReadCloumn=read[i].Split(',');
DataRow dr = TableInsert.NewRow();
dr[0] = i;
while (c <= ReadCloumn.Length)
{
dr[c] = ReadCloumn[c-1].Trim();
c++;
}
dr[c] = a.Substring(a.LastIndexOf("\\") + 1);
TableInsert.Rows.Add(dr);
}
if (SaveData(TableInsert) == false)
{
int sn = a.Split('_').Length - 2;
ErrorWrite(a.Split('_')[sn] + ":Write Error");
continue;
}
else
{
int sn=a.Split('_').Length-2;
AddItem( a.Split('_')[sn]+ "測試資料已上傳！");
GetDataSet.DeleteFile(a);
}
}
}
}
數據保存代碼
private bool SaveData(DataTable dt)
{
try
{
SqlConnection con = new SqlConnection();
SqlCommand com = new SqlCommand();
con.ConnectionString = "######";
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@DataTable",dt)
};
string msg= GetDataSet.RunSqlProdurce("InsertMultiRow", paras, con);
if (msg == "ok")
return true;
else
{
return false;
}
}
catch (Exception ex)
{
return false;
}
}
以上為完整代碼，如有更好的方案，還請各方大神不吝賜教……