C# SqlCommandBuilder.Dispose方法代码示例

        static void Main(string[] args)
        {
            Console.Title = "增加性别字段";

            string connectionString =                               //数据库连接字串
            "Data Source=.\\SQLExpress;Database=student;Trusted_Connection=true;";
            SqlConnection connection = new SqlConnection(connectionString);//创建数据库连接实例
            connection.Open();                                      //打开数据库连接
            Console.WriteLine("数据库student连接成功！");

            SqlCommand cmd = new SqlCommand();                      //创建数据查询类实例
            cmd.Connection = connection;
            cmd.CommandText = "ALTER TABLE student_info ADD sex varchar(2)";
            cmd.ExecuteNonQuery();                                  //执行添加sex字段SQL语句
            cmd.Dispose();

            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM student_info",
            "Data Source=.\\SQLExpress;Database=student;Trusted_Connection=true;");
            DataSet dataSet = new DataSet();                        //创建数据集
            adapter.Fill(dataSet);                                  //填充数据集
            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
            {
            dataSet.Tables[0].Rows[i][5] = random.Next(2) == 0 ? "男" : "女";//修改性别值
            }
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);//将数据集更新与数据库协调
            adapter.Update(dataSet);                                //更新数据集到数据库

            builder.Dispose();
            dataSet.Dispose();
            adapter.Dispose();
            connection.Close();                                     //关闭数据库连接

            Console.ReadLine();
        }

 /// <summary>
 /// 根据sql语句更新ds；
 /// Update ds 
 /// </summary>
 /// <param name="sql"></param>
 /// <returns></returns>
 public static void UpdateDataSet(DataSet ds, string sql, string tableName)
 {
     SqlDataAdapter da = new SqlDataAdapter();
     SqlCommandBuilder cb = new SqlCommandBuilder();
     try
     {
         da.SelectCommand = new SqlCommand(sql, myConn);
         cb.DataAdapter = da;
         //da.InsertCommand = cb.GetInsertCommand();
         //da.UpdateCommand = cb.GetUpdateCommand();
         //da.DeleteCommand = cb.GetDeleteCommand();
         da.Update(ds.Tables[tableName]);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         da.Dispose();
         cb.Dispose();
     }
 }

 /// <summary>
 /// ��������DataTable
 /// </summary>
 /// <param name="strSelectSQL">����ѡ���dt��¼��ͬ��SQL���</param>
 /// <param name="dt">Ҫ�������µ�DataTable</param>
 public static void UpdateTable(string strSelectSQL, DataTable dt)
 {
     try
     {
         using (SqlDataAdapter sda = new SqlDataAdapter(strSelectSQL, ConnectionString))
         {
             SqlCommandBuilder scb = new SqlCommandBuilder(sda);
             sda.Update(dt);
             scb.Dispose();
         }
     }
     catch (SqlException e)
     {
         throw e;
     }
 }

        public bool UpdateTable(DataTable table, string tableName)
        {
            try
            {
                TableHelper.SetDefaultColumnValues(table);

                var con = CONNECTION.OpenCon();

                var adapter = new SqlDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
                var cmd = new SqlCommandBuilder(adapter);
                adapter.Update(table);

                cmd.Dispose();
                adapter.Dispose();
                CONNECTION.CloseCon(con);

                return true;
            }
            catch (DBConcurrencyException cex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable DBConcurrencyError!",
                    Ex = cex,
                });
                return false;
            }
            catch (Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable Error!",
                    Ex = ex,
                });
                return false;
            }
        }

        // 1- ListView Control
        // 2- Total Columns
        // 3- Select Query
        // 4- Field List
        // 5- Column Width
        // 6- bool CheckBox Column
        // 7- connection string

        public static void FillListView(
            ListView pLV,
            int pCol,
            string pSQL,
            string pFL,
            string pFLen,
            bool pChkBox = false,
            string pCon = clsGVar.ConString1)
        {

            // 1- ListView Control
            // 2- Columns
            // 3- Query
            // 4- Field List
            // 5- Del: Title List
            // 6- Field Length
            // 7- Optional: pChkBox bool
            // 8- Optional: Connection string

            // ListView

            //
            // ListView Headers
            string[] cFL = pFL.Split(',');
            if (pFL.Count() < pCol)
            {
                pCol = pFL.Count();
            }
            //
            SqlConnection CurrCon = new SqlConnection(pCon);
            DataTable table = new DataTable();
            try
            {
                CurrCon.Open();
                SqlDataAdapter dataAdaptor = new SqlDataAdapter(pSQL, CurrCon);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdaptor);
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdaptor.Fill(table);

                //pLV.DataBindings.Add("User a", table, "usr_name");
                //pLV.DataBindings.Add("User b", table, "usr_fullname");
                if (pLV.Items.Count > 0)
                {
                    pLV.Items.Clear();
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow drow = table.Rows[i];

                    // Only row that have not been deleted
                    if (drow.RowState != DataRowState.Deleted)
                    {
                        // Define the list items
                        ListViewItem lvi = new ListViewItem(drow[cFL[0]].ToString());
                        for (int j = 1; j < pCol; j++)
                        {
                            lvi.SubItems.Add(drow[cFL[j]].ToString());
                        }
                        // Add the list items to the ListView
                        pLV.Items.Add(lvi);
                    }
                }





                // pLV.DataBindings = table;
                //
                //if (pReadOnly) { pLV.ReadOnly = true; }
                //else { pLV.ReadOnly = false; }
                //
                commandBuilder.Dispose();
                dataAdaptor.Dispose();
                CurrCon.Close();
            }
            catch (Exception exp)
            {
                if (CurrCon.State != ConnectionState.Closed) { CurrCon.Close(); }
                MessageBox.Show("Exception: " + exp.Message, "Fill List View ");
            }
        }

 // =========================================================
 // Fill Grid Start 
 // 1- Grid Control
 // 2- Query
 // 3- Connection
 // 4- Readonly
 public static void FillGrid(
     DataGridView pDGV1,
     string pSQL,
     string pCon = clsGVar.ConString1,
     bool pReadOnly = true
     )
 {
     SqlConnection CurrCon = new SqlConnection(pCon);
     DataTable table = new DataTable();
     try
     {
         CurrCon.Open();
         SqlDataAdapter dataAdaptor = new SqlDataAdapter(pSQL, CurrCon);
         SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdaptor);
         table.Locale = System.Globalization.CultureInfo.InvariantCulture;
         dataAdaptor.Fill(table);
         pDGV1.DataSource = table;
         //
         if (pReadOnly) { pDGV1.ReadOnly = true; }
         else { pDGV1.ReadOnly = false; }
         //
         commandBuilder.Dispose();
         dataAdaptor.Dispose();
         CurrCon.Close();
     }
     catch (Exception exp)
     {
         if (CurrCon.State != ConnectionState.Closed) { CurrCon.Close(); }
         MessageBox.Show("Exception: " + exp.Message, "Fill Grid ");
     }
 }

        public bool SaveData(DataTable DataTable)
        {
            bool success = false;
            try
            {
                SqlConnection dbConnection = new SqlConnection(_connectionString);
                SqlDataAdapter dbDataAdapter = new SqlDataAdapter("SELECT * FROM " + DataTable.TableName, dbConnection);
                SqlCommandBuilder dbCommandBuilder = new SqlCommandBuilder(dbDataAdapter);

                dbDataAdapter.Update(DataTable);

                dbCommandBuilder.Dispose();
                dbCommandBuilder = null;
                dbDataAdapter.Dispose();
                dbDataAdapter = null;
                dbConnection.Close();
                dbConnection.Dispose();
                dbConnection = null;
                success = true;
            }
            catch
            {
                success = false;
            }
            return success;
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (fuDonorFile.HasFile)
            {
                string strFilePath = HttpContext.Current.Request.PhysicalApplicationPath + @"Uploads\" + Request["eid"].ToString();

                if (!Directory.Exists(strFilePath))
                    Directory.CreateDirectory(strFilePath);

                string strFileName = Guid.NewGuid().ToString() + ".csv";
                fuDonorFile.SaveAs(strFilePath + @"\" + strFileName);

                try
                {
                    try
                    {
                        SqlConnection ConnT = new SqlConnection(_ConnStr);
                        ConnT.Open();
                        SqlCommand cmd = new SqlCommand("DELETE DonorListStage", ConnT);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        ConnT.Close();
                    }
                    catch(Exception EX)
                    {
                        throw new Exception("T Error: " + EX.Message);
                    }

                    SqlConnection Conn = new SqlConnection(_ConnStr);
                    Conn.Open();
                    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DonorListStage", Conn);
                    SqlCommandBuilder cb = new SqlCommandBuilder(da);
                    

                    DataSet ds = new DataSet();
                    try { da.Fill(ds); }
                    catch (Exception EX) {  throw new Exception("DA Error: " + EX.Message); }

                    // Read file
                    string line = "";
                    string[] arrLine;
                    
                    
                    StreamReader file = new StreamReader(strFilePath + @"\" + strFileName);
                    line = file.ReadLine();  // Skip the first line of column headers

                    int intRowCount = 1;
                    while((line = file.ReadLine()) != null)
                    {

                        int intCount = line.Split((char)34).Length;
                        string[] arrComma = line.Split((char)34);

                        if (intCount > 2)
                            line = ReplaceComma(line);
                        if(intCount > 4)
                            line = ReplaceComma(line);
                        if (intCount > 6)
                            line = ReplaceComma(line);
                        if (intCount > 8)
                            line = ReplaceComma(line);
                        if (intCount > 10)
                            line = ReplaceComma(line);
                        if (intCount > 12)
                            line = ReplaceComma(line);



                        arrLine = line.Split(",".ToCharArray());
                        DataRow dr = ds.Tables[0].NewRow();

                        if (arrLine.Count() != 31)
                        {
                            dr["pk_DonorList"] = intRowCount.ToString();
                            dr["UploadNotes"] = "Error: Data not in the correct format.";
                        }
                        else
                        {
                            try
                            {
                                dr["pk_DonorList"] = arrLine[28].ToString().Replace(";",",");
                                dr["DonorType"] = arrLine[2].ToString().Replace(";", ",");
                                dr["AccountType"] = arrLine[3].ToString().Replace(";", ",");
                                dr["KeyName"] = arrLine[4].ToString().Replace(";", ",");
                                dr["AccountID"] = arrLine[5].ToString().Replace(";", ",");
                                dr["InsideSal"] = arrLine[6].ToString().Replace(";", ",");
                                dr["OutSideSal"] = arrLine[7].ToString().Replace(";", ",");
                                dr["HHOutsideSal"] = arrLine[8].ToString().Replace(";", ",");
                                dr["AccountName"] = arrLine[9].ToString().Replace(";", ",");
                                dr["AddressLine1"] = arrLine[10].ToString().Replace(";", ",");
                                dr["AddressLine2"] = arrLine[11].ToString().Replace(";", ",");
                                dr["AddressLine3"] = arrLine[12].ToString().Replace(";", ",");
                                dr["AddressLine4"] = arrLine[13].ToString().Replace(";", ",");
                                dr["AddressLine5"] = arrLine[14].ToString().Replace(";", ",");
                                dr["City"] = arrLine[15].ToString().Replace(";", ",");
                                dr["State"] = arrLine[16].ToString().Replace(";", ",");
                                dr["PostCode"] = arrLine[17].ToString().Replace(";", ",");
                                dr["CountryIDTrans"] = arrLine[18].ToString().Replace(";", ",");
                                dr["StateDescription"] = arrLine[19].ToString().Replace(";", ",");
                                dr["EmailAddress"] = arrLine[20].ToString().Replace(";", ",");
//.........这里部分代码省略.........