2013年4月20日 星期六

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();

1 則留言: