sqldataAdapter/dataset/datatable的使用

 public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //把cities表中的數據載入到窗體的datagridview
            string connString = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            using(SqlConnection sqlconn=new SqlConnection(connString))
            {
                string selectstring = @"select id, cityid, city, provinceid from cities;select * from provinces";

                 #region 單張表
                //創建一個適配器類
                //using(SqlDataAdapter sqladapter=new SqlDataAdapter(selectstring,sqlconn))
                //{

                //    //此時adapter已經連接到了一個表
                //    DataTable dataTable = new DataTable();
                //    //將關聯表的數據填充到dataTable
                //    //sqladapter會自動打開資料庫連接，並執行sql腳本
                //    sqladapter.Fill(dataTable);
                //    //this.dataGridView1.DataSource = dataTable;
                //    List<cities> mycitylist = new List<cities>();

                //    //類定義時欄位{get;set;}不寫顯示不出來
                //    foreach(DataRow datarow in dataTable.Rows)
                //    {
                //        Console.WriteLine(datarow["id"]+" "+datarow[1]);
                //        //把每一行數據封裝成city類 
                //            mycitylist.Add(new cities(){
                //            id=int.Parse(datarow["id"].ToString()),
                //            cityid=int.Parse(datarow["cityid"].ToString()),
                //            city=datarow["city"].ToString(),
                //            provinceid = int.Parse(datarow["provinceid"].ToString())
                //        });
                //    }
                //    //把datatable的數據轉儲成List<city>類型
                //    this.dataGridView1.DataSource = mycitylist;
                    #endregion

                    #region 多張表
                using(SqlDataAdapter sqlDataAdapter =new SqlDataAdapter(selectstring,sqlconn))
                {
                    DataSet dataset=new DataSet();
                    sqlDataAdapter.Fill(dataset);
                    this.dataGridView1.DataSource = dataset.Tables[0];
                }
                    #endregion

                }

            }

        }

 增刪改查

 private void button1_Click(object sender, EventArgs e)
        {
            //把dataGridView修改的數據保存到資料庫中
            string connString = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            string selectstring = @"select id, cityid, city, provinceid from cities;select * from provinces";
            using(SqlDataAdapter dataAdapter=new SqlDataAdapter(selectstring,connString))
            {
                //拿到修改完了之後的datatable
                DataTable dt = this.dataGridView1.DataSource as DataTable;
                //修改後dt的變化映射到資料庫中對應表格的變化
                //幫助dataAdapter生成相關的CRUD 的SqlCommand
                using (SqlCommandBuilder sqlcomBulider = new SqlCommandBuilder(dataAdapter))
                {
                    dataAdapter.Update(dt);
                }
            }
            MessageBox.Show("保存成功");

手動增刪改查

public static SqlDataAdapter CreateCustomerAdapter(
    SqlConnection connection)
{
    SqlDataAdapter adapter = new SqlDataAdapter();

    // Create the SelectCommand.
    SqlCommand command = new SqlCommand("SELECT * FROM Customers " +
        "WHERE Country = @Country AND City = @City", connection);

    // Add the parameters for the SelectCommand.
    command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
    command.Parameters.Add("@City", SqlDbType.NVarChar, 15);

    adapter.SelectCommand = command;

    // Create the InsertCommand.
    command = new SqlCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (@CustomerID, @CompanyName)", connection);

    // Add the parameters for the InsertCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");

    adapter.InsertCommand = command;

    // Create the UpdateCommand.
    command = new SqlCommand(
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
        "WHERE CustomerID = @oldCustomerID", connection);

    // Add the parameters for the UpdateCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
    SqlParameter parameter = command.Parameters.Add(
        "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.UpdateCommand = command;

    // Create the DeleteCommand.
    command = new SqlCommand(
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

    // Add the parameters for the DeleteCommand.
    parameter = command.Parameters.Add(
        "@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    parameter.SourceVersion = DataRowVersion.Original;

    adapter.DeleteCommand = command;

    return adapter;
}