C#資料庫操作(新增、修改、刪除、查詢)

一、虛擬資料表方式

1、新增

bindingSource1.MoveLast();//指標移到最後一筆 hw2DataSet.hw2.Addhw2Row(textBox7.Text, textBox6.Text);//新增資料

2、查詢

bindingSource1.Position = bindingSource1.Find("Name", textBox11.Text);

3、修改

bindingSource1.MoveNext();//移動指標 this.hw2TableAdapter.Update(this.hw2DataSet.hw2);//更新資料表 bindingSource1.MovePrevious();//移動指標

4、刪除以MessageBox確認

Message.DialogResult result; result = MessageBox.Show("Delete?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button3);//刪除訊息 if (result == DialogResult.Yes) { hw2DataSet.hw2.FindByName(textBox1.Text).Delete();//刪除 bindingSource1.MoveNext(); this.hw2TableAdapter.Update(this.hw2DataSet.hw2); bindingSource1.MovePrevious(); }

 二、SQL語法方式

1、程式起始，增加一個資料庫連接套件 using System.Data.SqlClient;

2、在全域變數區塊宣告兩個變數：
(1)static string Path = Application.StartupPath; //紀錄程式起始位置
(2)SqlConnection con1;//宣告sql連接變數

3、在Load副程式內指派變數

con1 = new SqlConnection(); con1.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDbFilename =" + Path + "\\shop.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

4、下達查詢命令

con1.Open();//開啟連接 SqlCommand sqlcom = new SqlCommand("select * from input WHERE ino ='" + comboBox2.Text + "'", con1);//SQL Command SqlDataAdapter sqldataadpt = new SqlDataAdapter(sqlcom); DataTable dt1 = new DataTable(); sqldataadpt.Fill(dt1); dataGridView2.DataSource = dt1;//指派GridView顯示 con1.Close();//關閉連接 textBox2.Text = dataGridView2.Rows[dataGridView2.CurrentCell.RowIndex].Cells[5].Value.ToString();//取欄位值 textBox2.Focus(); textBox2.SelectAll();

5、新增

SqlCommand sqlcom = new SqlCommand("INSERT input (ino,cno,gno,gname,iprice,qty) VALUES('" + s1 + "','" + s2 + "','" + s3 + "','" + s4 + "'," + n5 + "," + n6 + ") select * from input", con1); SqlDataAdapter sqldataadpt = new SqlDataAdapter(sqlcom); DataTable dt1 = new DataTable(); sqldataadpt.Fill(dt1); dataGridView1.DataSource = dt1; con1.Close();

6、修改

con1.Open(); SqlCommand sqlcom = new SqlCommand("update input set qty = " + Convert.ToInt32(textBox2.Text) + " where ino = '"+comboBox2.Text + "'", con1); SqlDataAdapter sqldataadpt = new SqlDataAdapter(sqlcom); DataTable dt1 = new DataTable(); sqldataadpt.Fill(dt1); dataGridView2.DataSource = dt1; con1.Close(); Search(); MessageBox.Show("數量修改完成");

7、刪除

DialogResult tip = MessageBox.Show("確認是否刪除該筆資料?", "小提醒", MessageBoxButtons.OKCancel, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); if (tip == DialogResult.Cancel) return; //如果選取消的話就不會執行下一行程式 shopDataSet.input.FindByino(comboBox2.Text).Delete(); inputBindingSource.MoveNext(); inputTableAdapter.Update(this.shopDataSet.input); inputBindingSource.MovePrevious();