C#使用Ado.Net读写数据库

1.使用DataReader方式读取资料

[csharp]view plaincopy StringconnString=ConfigurationManager.ConnectionStrings["astt"].ConnectionString; //如果数据库连接字符串有加密,可以经过解密后重新获得连接字符串 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对单表的增删改查，根据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绑定数据源进行编辑，使用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(); }