.Net中有关批量添加

一、在开发过程中，经常会碰到把大量Excel数据插入到数据库中，若是单条插入速度很慢，时间紧急的话，根本来不及，因此批量插入，解决了时间方面的问题，下面来研究一下批量插入的方法：

1.首先我们拖label、TextBox控件、Button按钮各一个到窗体中，对要执行的表格数据进行“选择”，

       如图：  代码实现如下：

注：“textbox”命名为：textBox1，  “浏览”按钮命名为：btnSearch,

    /*************选择要操作的文件及其类型******************/
        private void btnSearch_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog1 = new OpenFileDialog();
            fileDialog1.InitialDirectory = "E:\\";
            fileDialog1.Filter = "所有文件(*.*)|*.*|Excel2007文件(*.xlsx)|*.xlsx|97_2003Excel (*.xls)|*.xls";
            fileDialog1.FilterIndex = 1;
            fileDialog1.RestoreDirectory = true;
            if (fileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = fileDialog1.FileName;
            }
            else
            {
                textBox1.Text = "";
            }
        }

 
2.把Excel表格中的数据保存成数据集的形式，写一个方法代码实现如下：
         /// <summary>
        /// 将Excel数据保存到Dataset
        /// </summary>
        /// <param name="FilePath">Excel表格路径</param>
        /// <returns></returns>
        private DataSet importExcelToDataSet(string FilePath)
        {
            string strConn = "";
            string fileNameExt = FilePath.Substring(FilePath.LastIndexOf(".")).ToUpper();
            if (fileNameExt == ".XLS")
            {
                //微软2003驱动版本
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0";
            }
            else if (fileNameExt == ".XLSX")
            {
                //微软2007驱动版本 
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            }
            string tableName = "";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            tableName = table.Rows[0]["Table_Name"].ToString();
            string sqlselect = "select * from [" + tableName + "]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlselect, conn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds);
            conn.Close();
            return ds;
        }
3.需要定义两个类，一个名称为OfficeTools，是文件操作类的一些方法，一个是DataControl类，有关数据库的操作，以下方法会用到。
  /**********把Excel中原始数据添加到数据库T_Wphone表中*******/
        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Trim() != "")
            {
                string fileName = textBox1.Text.Trim();
                string fileFix = OfficeTools.GetPostfixStr(fileName);
                if (fileFix.ToUpper() == ".XLSX" || fileFix.ToUpper() == ".XLS")
                {
                    DataSet tbPhone = importExcelToDataSet(fileName);
                    DateTime begion = DateTime.Now;
                    if (tbPhone != null && tbPhone.Tables[0].Rows.Count > 0)
                    {   //首先确定表中不存在其他数据
                        string sql = "delete from  [T_Wphone] ";
                        DataControl.ExecuteDataset(sql);
                        for (int i = 0; i < tbPhone.Tables[0].Rows.Count; i++)
                        {
                            //拼接字符串进行批量插入
                            StringBuilder strSqlb = new StringBuilder();
                            string Sphone = tbPhone.Tables[0].Rows[i][0].ToString();
                            //判断电话号码是否合法
                            if(IsPhone (Sphone) || IsMobilePhone(Sphone))
                            {
                                try
                                {   //拼接字串、执行批量插入操作
                                    strSqlb.Append("insert into [T_Wphone] (");
                                    strSqlb.Append("phone");
                                    strSqlb.Append(")");
                                    strSqlb.Append("values");
                                    strSqlb.Append("(");
                                    strSqlb.Append("'" + Sphone + "'");
                                    strSqlb.Append(")");
                                    DataControl.ExecuteNonQuery( strSqlb.ToString());
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show(ex.Message);
                                }                           
                            }   
                        }
                        MessageBox.Show("添加成功！");
                        //操作用时统计
                        TimeSpan ts = DateTime.Now - begion;
                        label13.Text = "提取共计：ts=" + ts.TotalMilliseconds + "毫秒";
                    }
                }
                else
                {
                    MessageBox.Show("文件类型不正确，请选择Excel文件！");
                } 
            }
            else
            {
                MessageBox.Show("请选择相应的文件目录！");
            }          
        }
  

执行以上操作之后，表格中的数据就添加到数据中了！