C#更新sqlserver数据库方法

1. DataAdapter+SqlCommandBuilder(效率低，适用于少量数据)

        /// <summary>
        /// 更新数据
        /// </summary>
        public void UpdateData()
        {
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                DrProcessor dp = new DrProcessor();
                dp.Dr = dtSource.Rows[i];
                DealDRChain chain = new DealDRChain();

                chain.AddDealDR(new Init())
                    .AddDealDR(new THKK(this.ConnectionString))
                    ;
                dp.DChain = chain;
                dp.Process();
            }

            scb.GetUpdateCommand();
            da.Update(ds,"CBZD");

        }

        /// <summary>
        /// 构造方法
        /// </summary>
        public ParticularProcess(string connectionString)
        {
            this.ConnectionString = connectionString;
            da = new SqlDataAdapter(GetSourceSql(), this.ConnectionString);
            ds = new DataSet();
            da.Fill(ds, "CBZD");
            dtSource = ds.Tables[0];
            scb = new SqlCommandBuilder(da);

           
           
            //dtSource = MyCommon.GetDt(GetSourceSql(), this.ConnectionString);
            //dtSource.TableName = "CBZD";
        }

2. 建立中间表，一次性更新（大量数据，高效）

        public void GenerateMiddle() {

            string tablename = "TEMP";
            string sql = string.Format( "truncate table {0}",tablename);
            MyCommon.ExecuteSql(sql, ConnectionString);


            BigData bd = new BigData(new BdConfigBase(this.configfilename, tablename));
            bd.InitDataTable();
            DataTable dt = bd.DtData;

            foreach (DataRow drSource in dtSource.Rows)
            {
                string oid = drSource["oid"].ToString();
                

                DataRow drNew = dt.NewRow();
                drNew["OBJECT_ID"] = oid;
                drNew["SZ"] = drSource["sz"];
                drNew["XZ"] = drSource["xz"];
                drNew["JF"] = drSource["jf"];
                dt.Rows.Add(drNew);
            }


            SqlConnectConfig sqc = new SqlConnectConfig();
            sqc.ConnectString = this.ConnectionString;
            sqc.TableName = tablename;
            new BigDataToSqlServer(sqc, dt, true);

            sql = "UPDATE dbo.CBZD SET sz=b.XZ,xz=b.XZ,jf=b.JF FROM dbo.CBZD a JOIN dbo.TEMP b ON a.OBJECT_ID=b.OBJECT_ID WHERE 1=1  ";
            MyCommon.ExecuteSql(sql, this.ConnectionString);
        
        }


        /// <summary>
        /// 更新数据
        /// </summary>
        public void UpdateData()
        {
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                DrProcessor dp = new DrProcessor();
                dp.Dr = dtSource.Rows[i];
                DealDRChain chain = new DealDRChain();

                chain.AddDealDR(new Init())
                    .AddDealDR(new THKK(this.ConnectionString))
                    ;
                dp.DChain = chain;
                dp.Process();
            }

            //scb.GetUpdateCommand();
            //da.Update(ds,"CBZD");
            this.GenerateMiddle();

        }