1.使用DataReader方式讀取資料
[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; //如果數(shù)據(jù)庫連接字符串有加密,可以經過解密后重新獲得連接字符串 DbConnectionStringBuilderconnBuilder=newDbConnectionStringBuilder(); connBuilder.ConnectionString=connString; connBuilder["DataSource"]=connBuilder["DataSource"];//可加上解密方法 connBuilder["UserID"]=connBuilder["UserID"];//可加上解密方法 connBuilder["Password"]=connBuilder["Password"];//可加上解密方法 connString=connBuilder.ConnectionString; using(OleDbConnectionconn=newOleDbConnection(connString)) { try { conn.Open(); using(OleDbCommandcmd=newOleDbCommand()) { cmd.CommandText="select*froms_usermwhererownum<=?"; cmd.CommandType=CommandType.Text; cmd.Connection=conn; cmd.Parameters.Add("?",OleDbType.Integer).Value=5;//cmd.Parameters.Add(newOleDbParameter("?",5)); using(OleDbDataReaderreader=cmd.ExecuteReader()) { while(reader.Read()) { MessageBox.Show(reader.GetString(1),"資料"); } } } conn.Close(); } catch(Exceptionex) { MessageBox.Show(ex.Message,"error"); return; } }
2.使用DataAdapter與DataSet方式讀取資料.
[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; using(OleDbConnectionconn=newOleDbConnection(connString)) { try { using(OleDbCommandcmd=newOleDbCommand()) { cmd.CommandText="select*froms_usermwhererownum<=?"; cmd.CommandType=CommandType.Text; cmd.Connection=conn; cmd.Parameters.Add("?",OleDbType.Integer).Value=5;//cmd.Parameters.Add(newOleDbParameter("?",5)); DataSetds=newDataSet(); OleDbDataAdapteradapter=newOleDbDataAdapter(cmd); adapter.Fill(ds,"s_userm"); foreach(DataRowdrinds.Tables[0].Rows) { MessageBox.Show(Convert.ToString(dr["user_nm"]),"資料"); } //listBox1.DataSource=ds.Tables[0].DefaultView; //listBox1.DisplayMember="user_nm"; //listBox1.ValueMember="user_no"; listBox1.DataSource=ds; listBox1.DisplayMember="s_userm.user_nm"; listBox1.ValueMember="s_userm.user_no"; dataGridView1.DataSource=ds.Tables[0].DefaultView; dataGridView1.ReadOnly=true; } } catch(Exceptionex) { MessageBox.Show(ex.Message,"error"); return; } }
3.使用DataAdapter與DataSet對單表的增刪改查，根據(jù)Command的查詢語法，使用CommandBuilder自動生成增刪改的語法.
[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; using(OleDbConnectionconn=newOleDbConnection(connString)) { OleDbCommandcmd=newOleDbCommand(); cmd.CommandText="select*froms_usermwhererownum<=?"; cmd.CommandType=CommandType.Text; cmd.Connection=conn; cmd.Parameters.Add("?",OleDbType.Integer).Value=5; OleDbDataAdapteradapter=newOleDbDataAdapter(cmd); //使用DataAdapter更新單表，可以使用CommandBuilder來自動生成InsertCommand,UpdateCommand,DeleteCommand OleDbCommandBuildercmdbuilder=newOleDbCommandBuilder(adapter);//將自動生成InsertCommand,UpdateCommand,DeleteCommand MessageBox.Show(cmdbuilder.GetUpdateCommand().CommandText,"updcommand"); //select DataSetds=newDataSet(); adapter.Fill(ds,"s_userm"); dataGridView1.DataSource=ds.Tables["s_userm"].DefaultView; //insert DataRowupdateRow=ds.Tables["s_userm"].NewRow(); updateRow["user_no"]="TEST1"; updateRow["user_nm"]="test1"; ds.Tables["s_userm"].Rows.Add(updateRow); adapter.Update(ds,"s_userm"); MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[0]["user_no"]),"user"); //update ds.Tables[0].Rows[1]["EMAIL"]="gymsoft@163.com"; adapter.Update(ds,"s_userm"); //delete ds.Tables["s_userm"].Rows[0].Delete(); adapter.Update(ds,"s_userm"); }
4.使用DataAdapter與DataSet進行增刪改查，手動指定增刪改查的語法。
[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; using(OleDbConnectionconn=newOleDbConnection(connString)) { DataSetds=newDataSet(); OleDbDataAdapteradapter=newOleDbDataAdapter(); //指定DataAdapter的select語句 OleDbCommandselectCmd=newOleDbCommand(); selectCmd.CommandText="select*froms_usermwhererownum<=?"; selectCmd.CommandType=CommandType.Text; selectCmd.Connection=conn; selectCmd.Parameters.Add("?",OleDbType.Integer).Value=5; adapter.SelectCommand=selectCmd; //指定DataAdapter的Insert語句 OleDbCommandinsertCmd=newOleDbCommand(); insertCmd.CommandText="insertintos_userm(user_no,user_nm)values(?,?)"; insertCmd.CommandType=CommandType.Text; insertCmd.Connection=conn; insertCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); insertCmd.Parameters.Add("?",OleDbType.VarChar,80,"user_nm"); adapter.InsertCommand=insertCmd; //指定DataAdapter的Update語句 OleDbCommandupdateCmd=newOleDbCommand(); updateCmd.CommandText="updates_usermsetemail=?whereuser_no=?"; updateCmd.CommandType=CommandType.Text; updateCmd.Connection=conn; updateCmd.Parameters.Add("?",OleDbType.VarChar,50,"email"); OleDbParameterparmUpdPk=updateCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmUpdPk.SourceVersion=DataRowVersion.Original; adapter.UpdateCommand=updateCmd; //指定DataAdapter的Delete語句 OleDbCommanddeleteCmd=newOleDbCommand(); deleteCmd.CommandText="deletefroms_usermwhereuser_no=?"; deleteCmd.CommandType=CommandType.Text; deleteCmd.Connection=conn; OleDbParameterparmDelPk=deleteCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmDelPk.SourceVersion=DataRowVersion.Original; adapter.DeleteCommand=deleteCmd; MessageBox.Show(adapter.SelectCommand.CommandText,"command"); //select adapter.Fill(ds,"s_userm"); dataGridView1.DataSource=ds.Tables["s_userm"].DefaultView; //insert DataRowupdateRow=ds.Tables["s_userm"].NewRow(); updateRow["user_no"]="TEST2"; updateRow["user_nm"]="test2"; ds.Tables["s_userm"].Rows.Add(updateRow); adapter.Update(ds,"s_userm"); MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[3]["user_no"]),"user"); //update ds.Tables[0].Rows[3]["EMAIL"]="gymsoft@163.com"; adapter.Update(ds,"s_userm"); //delete ds.Tables["s_userm"].Rows[3].Delete(); adapter.Update(ds,"s_userm"); }
5.使用DataGridView綁定數(shù)據(jù)源進行編輯，使用DataAdapter與DataSet進行增刪改查，手動指定增刪改查的語法。
[csharp]view plaincopy publicpartialclassForm1:Form { StringconnString; OleDbConnectionconn; DataSetds; OleDbDataAdapteradapter; publicForm1() { InitializeComponent(); } privatevoidbuttonQuery_Click(objectsender,EventArgse) { connString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; conn=newOleDbConnection(connString); ds=newDataSet(); adapter=newOleDbDataAdapter(); //指定DataAdapter的select語句 OleDbCommandselectCmd=newOleDbCommand(); selectCmd.CommandText="select*froms_usermwhererownum<=?"; selectCmd.CommandType=CommandType.Text; selectCmd.Connection=conn; selectCmd.Parameters.Add("?",OleDbType.Integer).Value=5; adapter.SelectCommand=selectCmd; //指定DataAdapter的Insert語句 OleDbCommandinsertCmd=newOleDbCommand(); insertCmd.CommandText="insertintos_userm(user_no,user_nm)values(?,?)"; insertCmd.CommandType=CommandType.Text; insertCmd.Connection=conn; insertCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); insertCmd.Parameters.Add("?",OleDbType.VarChar,80,"user_nm"); adapter.InsertCommand=insertCmd; //指定DataAdapter的Update語句 OleDbCommandupdateCmd=newOleDbCommand(); updateCmd.CommandText="updates_usermsetemail=?whereuser_no=?"; updateCmd.CommandType=CommandType.Text; updateCmd.Connection=conn; updateCmd.Parameters.Add("?",OleDbType.VarChar,50,"email"); OleDbParameterparmUpdPk=updateCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmUpdPk.SourceVersion=DataRowVersion.Original; adapter.UpdateCommand=updateCmd; //指定DataAdapter的Delete語句 OleDbCommanddeleteCmd=newOleDbCommand(); deleteCmd.CommandText="deletefroms_usermwhereuser_no=?"; deleteCmd.CommandType=CommandType.Text; deleteCmd.Connection=conn; OleDbParameterparmDelPk=deleteCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmDelPk.SourceVersion=DataRowVersion.Original; adapter.DeleteCommand=deleteCmd; //MessageBox.Show(adapter.SelectCommand.CommandText,"command"); //select adapter.Fill(ds,"s_userm"); dataGridView1.DataSource=ds.Tables["s_userm"].DefaultView; } privatevoidbuttonSave_Click(objectsender,EventArgse) { adapter.Update(ds,"s_userm"); ds.Clear(); adapter.Fill(ds,"s_userm"); dataGridView1.DataSource=ds.Tables["s_userm"].DefaultView; } }
6.使用DataAdapter與DataSet，以事務的方式進行增刪改查。
[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; using(OleDbConnectionconn=newOleDbConnection(connString)) { DataSetds=newDataSet(); OleDbDataAdapteradapter=newOleDbDataAdapter(); //指定DataAdapter的select語句 OleDbCommandselectCmd=newOleDbCommand(); selectCmd.CommandText="select*froms_usermwhererownum<=?"; selectCmd.CommandType=CommandType.Text; selectCmd.Connection=conn; selectCmd.Parameters.Add("?",OleDbType.Integer).Value=5; adapter.SelectCommand=selectCmd; //指定DataAdapter的Insert語句 OleDbCommandinsertCmd=newOleDbCommand(); insertCmd.CommandText="insertintos_userm(user_no,user_nm)values(?,?)"; insertCmd.CommandType=CommandType.Text; insertCmd.Connection=conn; insertCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); insertCmd.Parameters.Add("?",OleDbType.VarChar,80,"user_nm"); adapter.InsertCommand=insertCmd; //指定DataAdapter的Update語句 OleDbCommandupdateCmd=newOleDbCommand(); updateCmd.CommandText="updates_usermsetemail=?whereuser_no=?"; updateCmd.CommandType=CommandType.Text; updateCmd.Connection=conn; updateCmd.Parameters.Add("?",OleDbType.VarChar,50,"email"); OleDbParameterparmUpdPk=updateCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmUpdPk.SourceVersion=DataRowVersion.Original; adapter.UpdateCommand=updateCmd; //指定DataAdapter的Delete語句 OleDbCommanddeleteCmd=newOleDbCommand(); deleteCmd.CommandText="deletefroms_usermwhereuser_no=?"; deleteCmd.CommandType=CommandType.Text; deleteCmd.Connection=conn; OleDbParameterparmDelPk=deleteCmd.Parameters.Add("?",OleDbType.VarChar,30,"user_no"); parmDelPk.SourceVersion=DataRowVersion.Original; adapter.DeleteCommand=deleteCmd; //MessageBox.Show(adapter.SelectCommand.CommandText,"command"); conn.Open(); using(OleDbTransactiontran=conn.BeginTransaction(IsolationLevel.ReadCommitted)) { adapter.SelectCommand.Transaction=tran; adapter.InsertCommand.Transaction=tran; adapter.UpdateCommand.Transaction=tran; adapter.DeleteCommand.Transaction=tran; try { //select adapter.Fill(ds,"s_userm"); dataGridView1.DataSource=ds.Tables["s_userm"].DefaultView; //insert DataRowupdateRow=ds.Tables["s_userm"].NewRow(); updateRow["user_no"]="TEST2"; updateRow["user_nm"]="test2"; ds.Tables["s_userm"].Rows.Add(updateRow); adapter.Update(ds,"s_userm"); //MessageBox.Show(Convert.ToString(ds.Tables["s_userm"].Rows[3]["user_no"]),"user"); //update ds.Tables[0].Rows[3]["EMAIL"]="gymsoft@163.com"; adapter.Update(ds,"s_userm"); //delete ds.Tables["s_userm"].Rows[3].Delete(); adapter.Update(ds,"s_userm"); tran.Commit(); } catch(Exceptionex) { try { tran.Rollback(); } catch(Exceptionexc) { MessageBox.Show(exc.Message,"error"); } MessageBox.Show(ex.Message,"error"); } } conn.Close(); }
下載文檔
