c#大檔案讀取和寫入資料庫

c#大檔案讀取和寫入資料庫 c#大檔案讀取和寫入資料庫（帶進度條的原始碼） 最近一個項目需要將大檔案寫入和讀取到資料庫，覺得可能很多人也需要相關得東西，所以就將代碼帖出來 protected int state = 0; //表示進度條當前處理的事件類型，1表讀取word,2表寫入word，3表doc轉pdf，4表txt轉pdf private System.Windows.Forms.Form getDialog(string strFormName,System.Drawing.Icon ico,string strShowContent) { System.Windows.Forms.Form frm = new Form(); //初始化表單 frm.Text = strFormName; frm.Icon = ico; frm.MaximizeBox = false; frm.MinimizeBox = false; frm.TopMost = true; frm.ShowInTaskbar = false; frm.Height = 168; frm.Width = 544; frm.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; //添加控制項 System.Windows.Forms.Label lblContent = new Label(); lblContent.Text = strShowContent; lblContent.Left = 30; lblContent.Top = 20; lblContent.Text = strShowContent; frm.Controls.Add(lblContent); System.Windows.Forms.ProgressBar prgLoader = new ProgressBar(); prgLoader.Left=30; prgLoader.Top = lblContent.Top + lblContent.Height + 5; prgLoader.Width = frm.Width - 2 * 30; frm.Controls.Add(prgLoader); System.Windows.Forms.Label lblShowPercent = new Label(); lblShowPercent.TextAlign = System.Drawing.ContentAlignment.MiddleRight; lblShowPercent.Left = prgLoader.Width + 30 - lblShowPercent.Width; lblShowPercent.Top = prgLoader.Height + prgLoader.Top + 5; lblShowPercent.Text = prgLoader.Value.ToString() + "%"; lblShowPercent.Name = "lblShowPercent"; frm.Controls.Add(lblShowPercent); System.Windows.Forms.Button btnOK = new Button(); btnOK.Text = "取消"; btnOK.Left = prgLoader.Width + 30 - btnOK.Width; btnOK.Top = frm.Height - 30 - btnOK.Height; btnOK.Click +=new EventHandler(btnOk_Click); frm.Controls.Add(btnOK); return frm; } private void btnOk_Click(object sender,System.EventArgs e) { //擷取控制項資訊 System.Windows.Forms.Button btnOk = (System.Windows.Forms.Button)sender; System.Windows.Forms.Form frm = (System.Windows.Forms.Form)btnOk.Parent; System.Windows.Forms.ProgressBar prgLoader = null; foreach(System.Windows.Forms.Control control in frm.Controls) { if(control.GetType().ToString() == "System.Windows.Forms.ProgressBar") { prgLoader = (System.Windows.Forms.ProgressBar)control; } } //判斷當前的完成情況 if(prgLoader.Value == 100) { frm.Close(); } else { System.Windows.Forms.DialogResult dr = MessageBox.Show(frm,"是否停止當前操作?","提示",System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning); if(dr == System.Windows.Forms.DialogResult.Yes) { state = 0; frm.Close(); } } } /// <summary> /// 寫入word到資料庫 /// </summary> /// <param name="sqlcon">sql串連類</param> /// <param name="strTargetInsert">直接將目標內容寫入資料庫的sql，"insert into 表名(目標列名) values (@block)"</param> /// <param name="strTargetHandle">擷取目標內容的控制代碼的sql，"select @content=textptr(目標列名) from 目標表名 where 條件"</param> /// <param name="strTableName">目標表名</param> /// <param name="strColumnName">目標列名</param> /// <param name="strPath">要讀取word的路徑</param> /// <param name="intSetBlock">定義塊大小</param> /// <param name="bolShowDialog">是否顯示進度條</param> /// <param name="strFormName">表單名稱</param> /// <param name="ico">表單表徵圖</param> /// <param name="strShowContent">顯示內容</param> /// <returns>true表成功</returns> public bool WriteWordDocument(System.Data.SqlClient.SqlConnection sqlcon,string strTargetInsert,string strTargetHandle,string strTableName,string strColumnName,string strPath,int intSetBlock,bool bolShowDialog,string strFormName,System.Drawing.Icon ico,string strShowContent) { //初始化SqlCommand System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand(); sqlcmd.Connection = sqlcon; sqlcmd.CommandType = System.Data.CommandType.Text; int intBlock = intSetBlock; //塊大小 int intCount = 0; //分快數 int intLength = 0; //擷取檔案內容的長度 string strSelect = ""; //要執行的sql查詢語句 byte []bytContent = null; //定義內容數組 //比例 int intPercent = 0; //建立要輸入的檔案流 System.IO.FileStream fs = null; //建立二進位讀取 System.IO.BinaryReader br = null; try { fs = new FileStream(strPath,System.IO.FileMode.Open); } catch { return false; } br = new BinaryReader((Stream)fs); //為關鍵參數賦值 try { //是否顯示進度 if(bolShowDialog) { //擷取精度表單，引用表單中的進度條和按鈕控制項 System.Windows.Forms.Form frmProgress = getDialog(strFormName,ico,strShowContent); System.Windows.Forms.ProgressBar prgLoader = null; System.Windows.Forms.Button btnOk = null; System.Windows.Forms.Label lblShowPercent = null; foreach(System.Windows.Forms.Control control in frmProgress.Controls) { if(control.GetType().ToString() == "System.Windows.Forms.ProgressBar") { prgLoader = (System.Windows.Forms.ProgressBar)control; } if(control.GetType().ToString() == "System.Windows.Forms.Button") { btnOk = (System.Windows.Forms.Button)control; } if(control.GetType().ToString() == "System.Windows.Forms.Label" && control.Name == "lblShowPercent") { lblShowPercent = (System.Windows.Forms.Label)control; } } frmProgress.Show(); //啟動轉換 state = 2; if(fs.Length > 2147483647 || fs.Length == 0) //因為image列最多隻能儲存2,147,483,647個位元組，所以這裡做限定 { return false; } intLength = (int)fs.Length; intCount = intLength / intBlock; if(intCount == 0) { strSelect = strTargetInsert; bytContent = new byte[intLength]; bytContent = br.ReadBytes(intLength); sqlcmd.CommandText = strSelect; sqlcmd.Parameters.Add("@block",System.Data.SqlDbType.Image).Value = bytContent; sqlcmd.ExecuteNonQuery(); } else { strSelect = strTargetInsert; bytContent = new byte[intBlock]; bytContent = br.ReadBytes(intBlock); sqlcmd.CommandText = strSelect; sqlcmd.Parameters.Add("@block",System.Data.SqlDbType.Image).Value = bytContent; sqlcmd.Parameters.Add("@length",System.Data.SqlDbType.Int).Value = 0; sqlcmd.ExecuteNonQuery(); int i = 1; while(i != intCount) { if(state == 0) { strSelect = "delete from " + strTableName + strTargetHandle.Substring(strTargetHandle.LastIndexOf(" where ")); sqlcmd.CommandText = strSelect; sqlcmd.ExecuteNonQuery(); bytContent = null; fs.Close(); return false; } strSelect = "declare @content varbinary(16) "; strSelect += strTargetHandle; strSelect += " updatetext " + strTableName + "." + strColumnName + " @content @length 0 @block"; bytContent = br.ReadBytes(intBlock); sqlcmd.Parameters["@block"].Value = bytContent; sqlcmd.Parameters["@length"].Value = i * intBlock; sqlcmd.CommandText = strSelect; sqlcmd.ExecuteNonQuery(); intPercent = (int)(((double)(i * intBlock)) / ((double)intLength) * 100); prgLoader.Value = intPercent; lblShowPercent.Text = prgLoader.Value.ToString() + "%"; ++i; Application.DoEvents(); } int intResidual = intLength % intBlock; if(intResidual > 0) { strSelect = "declare @content varbinary(16) "; strSelect += strTargetHandle; strSelect += " updatetext " + strTableName + "." + strColumnName + " @content @length 0 @block"; bytContent = new byte[intResidual]; bytContent = br.ReadBytes(intResidual); sqlcmd.Parameters["@block"].Value = bytContent; sqlcmd.Parameters["@length"].Value = intCount * intBlock; sqlcmd.CommandText = strSelect; sqlcmd.ExecuteNonQuery(); } } prgLoader.Value = 100; lblShowPercent.Text = prgLoader.Value.ToString() + "%"; btnOk.Text = "關閉"; } else { if(fs.Length > 2147483647 || fs.Length == 0) //因為image列最多隻能儲存2,147,483,647個位元組，所以這裡做限定 { return false; } intLength = (int)fs.Length; intCount = intLength / intBlock; if(intCount == 0) { strSelect = strTargetInsert; bytContent = new byte[intLength]; bytContent = br.ReadBytes(intLength); sqlcmd.CommandText = strSelect; sqlcmd.Parameters.Add("@block",System.Data.SqlDbType.Image).Value = bytContent; sqlcmd.ExecuteNonQuery(); } else { strSelect = strTargetInsert; bytContent = new byte[intBlock]; bytContent = br.ReadBytes(intBlock); sqlcmd.CommandText = strSelect; sqlcmd.Parameters.Add("@block",System.Data.SqlDbType.Image).Value = bytContent; sqlcmd.Parameters.Add("@length",System.Data.SqlDbType.Int).Value = 0; sqlcmd.ExecuteNonQuery(); int i = 1; while(i != intCount) { strSelect = "declare @content varbinary(16) "; strSelect += strTargetHandle; strSelect += " updatetext " + strTableName + "." + strColumnName + " @content @length 0 @block"; bytContent = br.ReadBytes(intBlock); sqlcmd.Parameters["@block"].Value = bytContent; sqlcmd.Parameters["@length"].Value = i * intBlock; sqlcmd.CommandText = strSelect; sqlcmd.ExecuteNonQuery(); ++i; } int intResidual = intLength % intBlock; if(intResidual > 0) { strSelect = "declare @content varbinary(16) "; strSelect += strTargetHandle; strSelect += " updatetext " + strTableName + "." + strColumnName + " @content @length 0 @block"; bytContent = new byte[intResidual]; bytContent = br.ReadBytes(intResidual); sqlcmd.Parameters["@block"].Value = bytContent; sqlcmd.Parameters["@length"].Value = intCount * intBlock; sqlcmd.CommandText = strSelect; sqlcmd.ExecuteNonQuery(); } } } bytContent = null; fs.Close(); } catch { state = 0; bytContent = null; fs.Close(); return false; } state = 0; return true; } /// <summary> /// 從資料庫讀取word /// </summary> /// <param name="sqlcon">sql串連類</param> /// <param name="strTargetSelect">直接擷取目標內容的sql，"select 目標列名 from 目標表名 where 條件"</param> /// <param name="strTargetLength">擷取目標內容的長度的sql，"select datalength(目標列名) from 目標表名 where 條件"</param> /// <param name="strTargetHandle">擷取目標內容的控制代碼的sql，"select @content=textptr(目標列名) from 目標表名 where 條件"</param> /// <param name="strTableName">目標表名</param> /// <param name="strColumnName">目標列名</param> /// <param name="strPath">要匯出並儲存的word的路徑</param> /// <param name="intSetBlock">定義塊大小</param> /// <param name="bolShowDialog">是否顯示進度條</param> /// <param name="strFormName">表單名稱</param> /// <param name="ico">表單表徵圖</param> /// <param name="strShowContent">顯示內容</param> /// <returns>true表成功</returns> public bool ReadWordDocument(System.Data.SqlClient.SqlConnection sqlcon,string strTargetSelect,string strTargetLength,string strTargetHandle,string strTableName,string strColumnName,string strPath,int intSetBlock,bool bolShowDialog,string strFormName,System.Drawing.Icon ico,string strShowContent) { //初始化SqlCommand System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand(); sqlcmd.Connection = sqlcon; sqlcmd.CommandType = System.Data.CommandType.Text; int intBlock = intSetBlock; //塊大小 int intCount = 0; //分快數 int intLength = 0; //擷取的image列中的內容的長度 string strSelect = ""; //要執行的sql查詢語句 byte []bytContent = null; //定義內容數組 int intPercent = 0; //擷取讀取的比例 //建立要輸出的檔案流 System.IO.FileStream fs = new FileStream(strPath,System.IO.FileMode.Create); try { //擷取指定image列中的內容長度 sqlcmd.CommandText = strTargetLength; intLength = (int)sqlcmd.ExecuteScalar(); //如果長度為0 if(intLength == 0) { return false; } //獲得分快數 intCount = intLength / intBlock; //是否顯示精度 if(bolShowDialog) { //擷取精度表單，引用表單中的進度條和按鈕控制項 System.Windows.Forms.Form frmProgress = getDialog(strFormName,ico,strShowContent); System.Windows.Forms.ProgressBar prgLoader = null; System.Windows.Forms.Button btnOk = null; System.Windows.Forms.Label lblShowPercent = null; foreach(System.Windows.Forms.Control control in frmProgress.Controls) { if(control.GetType().ToString() == "System.Windows.Forms.ProgressBar") { prgLoader = (System.Windows.Forms.ProgressBar)control; } if(control.GetType().ToString() == "System.Windows.Forms.Button") { btnOk = (System.Windows.Forms.Button)control; } if(control.GetType().ToString() == "System.Windows.Forms.Label" && control.Name == "lblShowPercent") { lblShowPercent = (System.Windows.Forms.Label)control; } } frmProgress.Show(); //啟動轉換 state = 1; if(intCount == 0) { strSelect = strTargetSelect; sqlcmd.CommandText = strSelect; bytContent = new byte[intLength]; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intLength); prgLoader.Value = 100; lblShowPercent.Text = prgLoader.Value + "%"; btnOk.Text = "關閉"; } else { int i = 0; bytContent = new byte[intBlock]; while(i < intCount) { if(state == 0) { bytContent = null; System.IO.File.Delete(strPath); fs.Close(); return false; } strSelect = "declare @content varbinary(16) "; //再sql中聲明擷取目標image列內容的控制代碼變數 strSelect += strTargetHandle; //擷取控制代碼 //鎖定並讀取指定長度的資料 strSelect += " readtext " + strTableName + "." + strColumnName + " @content @start @count HOLDLOCK"; if(i == 0) { //添加@start和@count變數，分別表位移變數和取的長度 sqlcmd.Parameters.Add("@start",System.Data.SqlDbType.Int).Value = 0; sqlcmd.Parameters.Add("@count",System.Data.SqlDbType.Int).Value = intBlock; } else { sqlcmd.Parameters["@start"].Value = i * intBlock; sqlcmd.Parameters["@count"].Value = intBlock; } sqlcmd.CommandText = strSelect; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intBlock); intPercent = (int)(((double)(i * intBlock)) / (double)(intLength) * 100); prgLoader.Value = intPercent; lblShowPercent.Text = prgLoader.Value.ToString() + "%"; Application.DoEvents(); ++i; } //將剩餘的位元組寫入流 int intResidual = intLength % intBlock; if(intResidual > 0) { strSelect = "declare @content varbinary(16) "; //再sql中聲明擷取目標image列內容的控制代碼變數 strSelect += strTargetHandle; //擷取控制代碼 //鎖定並讀取指定長度的資料 strSelect += " readtext " + strTableName + "." + strColumnName + " @content @start @count HOLDLOCK"; bytContent = new byte[intResidual]; sqlcmd.Parameters["@start"].Value = intCount * intBlock; sqlcmd.Parameters["@count"].Value = intResidual; sqlcmd.CommandText = strSelect; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intResidual); } prgLoader.Value = 100; lblShowPercent.Text = prgLoader.Value.ToString() + "%"; btnOk.Text = "關閉"; } } else { if(intCount == 0) { strSelect = strTargetSelect; sqlcmd.CommandText = strSelect; bytContent = new byte[intLength]; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intLength); } else { int i = 0; bytContent = new byte[intBlock]; while(i < intCount) { strSelect = "declare @content varbinary(16) "; //再sql中聲明擷取目標image列內容的控制代碼變數 strSelect += strTargetHandle; //擷取控制代碼 //鎖定並讀取指定長度的資料 strSelect += " readtext " + strTableName + "." + strColumnName + " @content @start @count HOLDLOCK"; if(i == 0) { //添加@start和@count變數，分別表位移變數和取的長度 sqlcmd.Parameters.Add("@start",System.Data.SqlDbType.Int).Value = 0; sqlcmd.Parameters.Add("@count",System.Data.SqlDbType.Int).Value = intBlock; } else { sqlcmd.Parameters["@start"].Value = i * intBlock; sqlcmd.Parameters["@count"].Value = intBlock; } sqlcmd.CommandText = strSelect; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intBlock); ++i; } //將剩餘的位元組寫入流 int intResidual = intLength % intBlock; if(intResidual > 0) { strSelect = "declare @content varbinary(16) "; //再sql中聲明擷取目標image列內容的控制代碼變數 strSelect += strTargetHandle; //擷取控制代碼 //鎖定並讀取指定長度的資料 strSelect += " readtext " + strTableName + "." + strColumnName + " @content @start @count HOLDLOCK"; bytContent = new byte[intResidual]; sqlcmd.Parameters["@start"].Value = intCount * intBlock; sqlcmd.Parameters["@count"].Value = intResidual; sqlcmd.CommandText = strSelect; bytContent = sqlcmd.ExecuteScalar() as byte[]; fs.Write(bytContent,0,intResidual); } } } bytContent = null; fs.Close(); } catch { state = 0; System.IO.File.Delete(strPath); bytContent = null; fs.Close(); return false; } state = 0; return true; }