資料庫的新增,刪除,修改

public partial class Form1 : Form
{
    private SqlConnection objConn;
    private SqlDataAdapter dataAdapter;
    private DataSet dataSet;
    private DataTable dataTable;
    private string tableName = "userlist";
 
    public Form1()
    {
        InitializeComponent();
        string sConnectionString = "Password=xxxx; User ID=xxxx;"
            + "Initial Catalog=mydb; Data Source=ip\\SQLEXPRESS;";
        objConn = new SqlConnection(sConnectionString);
        objConn.Open();
    }
 
    private void Form1_FormClosed(object sender, FormClosedEventArgs e)
    {
        objConn.Close();
    }
    private void addLine(string data, bool clear=false)
    {
        if (clear)
        {
            this.richTextBox1.Clear();
        }
        this.richTextBox1.AppendText(data + Environment.NewLine);
    }
 
    private void btnLoad_Click(object sender, EventArgs e)
    {
        string sql = "Select * From " + tableName;
        //建立 DataAdapter.
        //SqlCommand cmd = new SqlCommand(sql, objConn);
        //dataAdapter = new SqlDataAdapter(cmd);
        dataAdapter = new SqlDataAdapter(sql, objConn);
        //useSqlCommandBuilder();
    }
    private void useSqlCommandBuilder()
    {
        //替 SqlDataAdapter 自動產生 insert,update,delete 的sql語法
        //update,delete 自動產生的sql語句效率並不佳, 最好還是自行使用 SqlCommand 撰寫執行
        //更動 SelectCommand 需呼叫 RefreshSchema,否則 InsertCommand,UpdateCommand,DeleteCommand內容不會更新
        //call Dispose，則會解除 SqlCommandBuilder 與 SqlDataAdapter 的關係
        SqlCommandBuilder cb = new SqlCommandBuilder(dataAdapter);//若無需異動則不需要
        //顯示自動生成之 sql 語法
        addLine("Insert");
        addLine(cb.GetInsertCommand().CommandText);
        addLine(Environment.NewLine + "Update");
        addLine(cb.GetUpdateCommand().CommandText);
        addLine(Environment.NewLine + "Delete");
        addLine(cb.GetDeleteCommand().CommandText);
    }
 
    private void btnInsert_Click(object sender, EventArgs e)
    {
        //insertUseSqlCommand((int)numId.Value);
        insertUseSqlDataAdapter((int)numId.Value);
    }
    private void insertUseSqlCommand(int id)
    {
        SqlCommand sc = new SqlCommand();
        sc.Connection = objConn;
        sc.CommandType = CommandType.Text;
        sc.CommandText = "insert into userlist (id,name,ps)values(@id,@name,@ps)";
        sc.Parameters.AddWithValue("@id", id);
        sc.Parameters.AddWithValue("@name", "shioulo");
        sc.Parameters.AddWithValue("@ps", "insertUseSqlCommand");
        try
        {
            addLine("", true);
            sc.ExecuteNonQuery();
            addNewRow(id, "shioulo", "insertUseSqlCommand");
        }
        catch (Exception err)
        {
            addLine("Error: " + err);
        }
 
    }
    private void insertUseSqlDataAdapter(int id)
    {
        SqlCommand sc = new SqlCommand();
        sc.Connection = objConn;
        sc.CommandType = CommandType.Text;
        sc.CommandText = "insert into userlist (id,name,ps)values(@id,@name,@ps)";
        //
        SqlParameter parameter = sc.Parameters.Add("@id", SqlDbType.Int, 0, "id");
        parameter.IsNullable = false;
        //parameter.SourceVersion = DataRowVersion.Original;
        sc.Parameters.Add("@name", SqlDbType.NChar, 20, "name");
        sc.Parameters.Add("@ps", SqlDbType.NVarChar, 250, "ps");
        dataAdapter.InsertCommand = sc;
        //
        addNewRow(id, "shioulo中文", "insertUseSqlDataAdapter");
        try
        {
            addLine("", true);
            dataAdapter.Update(dataSet, tableName);
        }
        catch (Exception err)
        {
            addLine("Error: " + err);         
        }
    }
    private void addNewRow(int id, string name, string ps)
    {
        DataTable dt = dataSet.Tables[tableName];
        DataRow newRow = dt.NewRow();
        newRow["id"] = id;
        newRow["name"] = name;
        newRow["ps"] = ps;
        dt.Rows.Add(newRow);
    }
 
    private void btnDelete_Click(object sender, EventArgs e)
    {
        int row = this.dataGridView1.CurrentRow.Index;
        int delId = (int)this.dataGridView1.Rows[row].Cells["id"].Value;
        this.dataGridView1.Rows.RemoveAt(row);
        deleteUseSqlCommand(delId);
        //deleteUseSqlDataAdapter();
    }
    private void deleteUseSqlCommand(int id)
    {
        SqlCommand sc = new SqlCommand();
        sc.Connection = objConn;
        sc.CommandType = CommandType.Text;
        sc.CommandText = "delete from userlist where id=@id";
        //sc.Parameters.Add("@id", SqlDbType.Int);
        //sc.Parameters["@i"].Value = id;
        sc.Parameters.AddWithValue("@id", id);
        try
        {
            int effect = sc.ExecuteNonQuery();
            addLine(String.Format("刪除筆數:{0}", effect), true);
        }
        catch (Exception err)
        {
            addLine("Error: " + err);
        }
    }
    private void deleteUseSqlDataAdapter()
    {
        SqlCommand sc = new SqlCommand();
        sc.Connection = objConn;
        sc.CommandType = CommandType.Text;
        sc.CommandText = "delete from userlist where id=@id";
        //固定長度資料型態, size 參數會被忽略
        SqlParameter parameter = sc.Parameters.Add("@id", SqlDbType.Int, 0, "id");//固定長度資料數值型態
        //parameter.SourceColumn = "id";
        try
        {
            dataAdapter.DeleteCommand = sc;//自訂 SqlCommand
            int effect = dataAdapter.Update(dataSet, tableName);
            addLine(String.Format("刪除筆數:{0}", effect), true);
        }
        catch (Exception err)
        {
            addLine("Error: " + err);
        }
    }
 
    private void btnUpdate_Click(object sender, EventArgs e)
    {
        SqlCommand sc = new SqlCommand();
        sc.Connection = objConn;
        sc.CommandType = CommandType.Text;
        sc.CommandText = "update userlist set name=@name,ps=@ps where id=@id";
        //
        SqlParameter parameter = sc.Parameters.Add("@id", SqlDbType.Int, 0, "id");
        parameter.IsNullable = false;
        //CustomerID 參數的 SourceVersion 會設定為 Original
        //如此一來，如果已修改的 DataRow 內識別欄位的值有所變更，便可確保資料來源內的現有資料列也已經更新
        //也就是當key欄位可以變更時需要此設定
        parameter.SourceVersion = DataRowVersion.Original;
 
        sc.Parameters.Add("@name", SqlDbType.NChar, 20, "name");
        sc.Parameters.Add("@ps", SqlDbType.NVarChar, 250, "ps");
        dataAdapter.UpdateCommand = sc;
        //
        DataTable dt = null;
        if (this.dataGridView1.DataSource is DataTable)
        {
            dt = (DataTable)this.dataGridView1.DataSource;
        }
        else if (this.dataGridView1.DataSource is DataSet)
        {
            dt = ((DataSet)this.dataGridView1.DataSource).Tables[this.dataGridView1.DataMember];
        }
        else if (this.dataGridView1.DataSource is BindingSource)
        {
            BindingSource bs = (BindingSource)this.dataGridView1.DataSource;
            if (bs.DataSource is DataTable)
            {
                dt = (DataTable)bs.DataSource;
            }
            else if (bs.DataSource is DataSet)
            {
                dt = ((DataSet)bs.DataSource).Tables[bs.DataMember];
            }
        }
        if (dt != null)
        {
            /*DataRow editRow = dt.Rows[0];
            editRow["name"] = "aa11";
            dt.Rows[0]["ps"] = "ba1";*/
            //
            try
            {
                //int effect = dataAdapter.Update(dataSet, tableName);
                int effect = dataAdapter.Update(dt);
                addLine(String.Format("更新筆數:{0}", effect));
            }
            catch (Exception err)
            {
                addLine("Error: " + err, true);
            }
        }
    }
 
    private void dataGridView1_SelectionChanged(object sender, EventArgs e)
    {
        int row = this.dataGridView1.CurrentRow.Index;
        numId.Value = row;
        try
        {   // Cells["name"] name 指的是 column.DataPropertyName
            //datagridview.Rows[rowIndex].Cells[columnIndex].Value; => 實際的資料值
            //datagridview.Rows[rowIndex].Cells[columnIndex].FormattedValue; => 畫面格式化後的值
            if (this.dataGridView1.Rows[row].Cells["name"].Value != null)
                addLine(this.dataGridView1.Rows[row].Cells["name"].Value.ToString());
        }
        catch (Exception err)
        {
        }
    }
    //從 SqlDataAdapter 取出table資料
    private void button1_Click(object sender, EventArgs e)
    {
        string idName = objConn.Database;
        SqlDataAdapter da = dataAdapter;
        //使用 DataSet
        dataSet = new DataSet(idName);//idName非一定要,註記用途,匯出xml之root標記
        da.FillSchema(dataSet, SchemaType.Source, tableName);//僅填schema不含資料
        da.Fill(dataSet, tableName);
        //
        //給 DataSet,必須再指定 table name
        this.dataGridView1.DataSource = dataSet;
        this.dataGridView1.DataMember = tableName;
        //dataSet 資料載入完成後, dataGridView_SelectionChanged()僅觸發一次
 
        //this.dataGridView1.DataSource = dataSet.Tables[tableName];
        //this.dataGridView1.DataSource = new BindingSource(dataSet, tableName);
        //使用 DataTable,BindingSource 資料載入完成後, dataGridView_SelectionChanged()共觸發 總筆數+1 次
    }
    //從 SqlDataAdapter 取出table資料
    private void button2_Click(object sender, EventArgs e)
    {
        string idName = tableName;
        SqlDataAdapter da = dataAdapter;
        //使用 DataTable
        dataTable = new DataTable(idName);
        da.Fill(dataTable);
        this.dataGridView1.DataSource = dataTable;
    }
 
    private void button3_Click(object sender, EventArgs e)
    {
        string idName = objConn.Database;
        SqlDataAdapter da = dataAdapter;
        //若資料僅顯示,不會在程式中再取回處理,較適用 DataTableMapping
        //否則異造成程式上的問題, 尤其是開發多語系時, 最好是直接採設定 dataGridView.column 方式
        DataTableMapping map = da.TableMappings.Add(tableName, tableName);
        map.ColumnMappings.Add("id", "編號");
        map.ColumnMappings.Add("name", "名稱");
        map.ColumnMappings.Add("ps", "備註");//未自定的欄位則使用原資料庫欄位名稱
        dataSet = new DataSet(idName);//idName非一定要,註記用途,匯出xml之root標記
        da.FillSchema(dataSet, SchemaType.Mapped, tableName);//需指定 SchemaType.Mapped
        da.Fill(dataSet, tableName);
        //
        this.dataGridView1.DataSource = dataSet;
        this.dataGridView1.DataMember = tableName;
    }
 
    private void btnAcceptChange_Click(object sender, EventArgs e)
    {            
        dataSet.AcceptChanges();//完成資料異動
    }
 
    private void btnRejectChange_Click(object sender, EventArgs e)
    {
        dataSet.RejectChanges();//取消資料異動
    }
    private void getError()
    {
        DataSet dsChanges = dataSet.GetChanges();
        if (dsChanges==null)
            return;
 
        DataTable dtb = new DataTable();
        dtb = dsChanges.Tables[tableName];
        //GetErrors 返回一個數組，它由表中具一個或多個檢驗錯誤資訊
        DataRow[] badRows = dtb.GetErrors();
        if (badRows.Length == 0)
        { /*無錯誤資料,進行相關處理作業 */ }
        else
        {
            foreach (DataRow row in badRows)
            {   //每一行都可能一個或多個誤，GetColumnsInError 返回一個集合，包含所有有問題的欄
                foreach (DataColumn col in row.GetColumnsInError())
                {   //GetColumnError方法獲取一個無效的列的錯誤消息
                    addLine(row.GetColumnError(col));
                }
            }
        }
    }
}