批量插入数据到数据库

 

第一种方法使用事务向

    除了可以批量插入数据外还可以监测插入数据所消耗的时间

protected void btnInsert_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start(); //  开始监视代码运行时间
            //插入1000条数据到数据库
            InsertDataTODB();

            stopwatch.Stop(); //  停止监视
            TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间
            double hours = timespan.TotalHours; // 总小时
            double minutes = timespan.TotalMinutes;  // 总分钟
            double seconds = timespan.TotalSeconds;  //  总秒数
            double milliseconds = timespan.TotalMilliseconds;  //  总毫秒数
            TextBox1.Text = hours.ToString();
            TextBox2.Text = minutes.ToString();
            TextBox3.Text = seconds.ToString();
            TextBox4.Text = milliseconds.ToString();
        }

        /// <summary>
        /// 批量插入数据到数据库的某一张表
        /// </summary>
        private void InsertDataTODB()
        {
            string connStr = ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
            SqlConnection myConnection = new SqlConnection(connStr);
            myConnection.Open();
            SqlTransaction myTrans = myConnection.BeginTransaction();
            //使用New新生成一个事务
            SqlCommand myCommand = new SqlCommand();
            myCommand.Transaction = myTrans;
            try
            {
                //这儿做循环插入
                for (int i = 0; i < 1000; i++)
                {
                    myCommand.CommandText = string.Format("insert into aa values('{0}','{1}')", "张" + i, i);
                    myCommand.Connection = myConnection;
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
            }
            catch (Exception e)
            {
                myTrans.Rollback();
            }
            finally
            {
                myConnection.Close();
            }
        }

 

第二种方法使用集合

   除了可以批量插入数据外还可以监测插入数据所消耗的时间

/// <summary>
        /// 两个知识点,第一监视代码的运行时间,显示结果,第二批量向数据库中插头数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start(); //  开始监视代码运行时间
            //插入1000条数据到数据库
            ArrayList list = new ArrayList();

            for (int i = 0; i < 1000; i++)
            {
                string sql =string.Format("insert into aa values('{0}','{1}')","五"+i,i);
                list.Add(sql);
            }
            ExecuteSqlTran(list);

            stopwatch.Stop(); //  停止监视
            TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间
            double hours = timespan.TotalHours; // 总小时
            double minutes = timespan.TotalMinutes;  // 总分钟
            double seconds = timespan.TotalSeconds;  //  总秒数
            double milliseconds = timespan.TotalMilliseconds;  //  总毫秒数
            TextBox1.Text = hours.ToString();
            TextBox2.Text = minutes.ToString();
            TextBox3.Text = seconds.ToString();
            TextBox4.Text = milliseconds.ToString();

        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param> 
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }