.NET学习（7）ADO.NET进行数据库编程

最新推荐文章于 2025-04-07 16:29:25 发布

原创最新推荐文章于 2025-04-07 16:29:25 发布 · 592 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #.net #dataset #sql server #datagrid #exception

.NET/MICROSOFT 专栏收录该内容

26 篇文章

订阅专栏

本文介绍使用C#进行数据库编程的方法，包括MS Access和SQL Server的操作，详细讲解了使用OleDb和SqlConnection连接数据库、执行SQL语句、使用SqlDataAdapter填充DataSet以及更新数据库等内容。

看了Modern C#系列课程7--ADO.NET进行数据库编程的笔记，主讲俞晖

MS Access的操作

using SD=System.Data; // general DB classes using MSA=System.Data.OleDb; // OleDB databases, e.g. MS Access using SWF=System.Windows.Forms; private void Form1_Load(object sender, System.EventArgs e) { MSA.OleDbConnection dbConn = null; MSA.OleDbDataReader dbReader = null; try { string sConnect; sConnect = string.Format("Provider={0};Data Source={1}{2}", "Microsoft.Jet.OLEDB.4.0;", System.AppDomain.CurrentDomain.BaseDirectory, "Sales.mdb"); dbConn = new MSA.OleDbConnection(sConnect); dbConn.Open(); SWF.MessageBox.Show( dbConn.State.ToString() ); string sql; BankCustomer c; sql = "Select * From Customers ;"; MSA.OleDbCommand dbCmd; dbCmd = new MSA.OleDbCommand(sql, dbConn); dbReader = dbCmd.ExecuteReader();//只能单向执行，不可返回 while (dbReader.Read()) // retrieve records 1-by-1... { c = new BankCustomer(dbReader["FirstName"].ToString(), dbReader["LastName"].ToString(), System.Convert.ToDecimal(dbReader["AcctBalance"])); Globals.Customers.Add(c); } } catch(Exception ex) { throw ex; } finally { try{ dbReader.Close(); } catch{} try{ dbConn.Close(); } catch{} } SWF.MessageBox.Show( dbConn.State.ToString() ); // bind customers to list box for display this.lstCustomers.DataSource = Globals.Customers; this.lstCustomers.SelectedIndex = -1; }

SQL SERVER的操作

using SD=System.Data; // general DB classes using SQL=System.Data.SqlClient; // Microsoft SQL Server databases using SWF=System.Windows.Forms; private void Form1_Load(object sender, System.EventArgs e) { SQL.SqlConnection dbConn = null; SQL.SqlDataReader dbReader = null; try { string sConnect; sConnect = string.Format("Server={0};Database={1};{2}", "localhost", "Sales", "Integrated Security=SSPI"); //或者写成"id=sa;pwd=????" dbConn = new SQL.SqlConnection(sConnect); dbConn.Open(); SWF.MessageBox.Show( dbConn.State.ToString() ); string sql; BankCustomer c; sql = "Select * From Customers ;"; SQL.SqlCommand dbCmd; dbCmd = new SQL.SqlCommand(sql, dbConn); dbReader = dbCmd.ExecuteReader(); while (dbReader.Read()) // retrieve records 1-by-1... { c = new BankCustomer(dbReader["FirstName"].ToString(), dbReader["LastName"].ToString(), System.Convert.ToDecimal(dbReader["AcctBalance"])); Globals.Customers.Add(c); } } catch(Exception ex) { throw ex; } finally { try{ dbReader.Close(); } catch{} try{ dbConn.Close(); } catch{} } SWF.MessageBox.Show( dbConn.State.ToString() ); // bind customers to list box for display this.lstCustomers.DataSource = Globals.Customers; this.lstCustomers.SelectedIndex = -1; }

SQL SERVER的SqlDataAdapter，DataSet的操作

using SD=System.Data; // general DB classes using SQL=System.Data.SqlClient; // Microsoft SQL Server databases using SWF=System.Windows.Forms; private void Form1_Load(object sender, System.EventArgs e) { SQL.SqlConnection dbConn = null; try { string sConnect; sConnect = string.Format("Server={0};Database={1};{2}", "localhost", "Sales", "Integrated Security=SSPI"); dbConn = new SQL.SqlConnection(sConnect); string sql; BankCustomer c; sql = "Select * From Customers ;"; SQL.SqlCommand dbCmd; dbCmd = new SQL.SqlCommand(sql, dbConn); SQL.SqlDataAdapter dbAdapter; dbAdapter = new SQL.SqlDataAdapter(dbCmd); SD.DataSet ds; ds = new SD.DataSet(); dbConn.Open(); dbAdapter.Fill(ds);//dbAdapter 中的数据填充到DataSet中 dbConn.Close();//与数据库断开，数据保存在DataSet中 //从DataSet中取数据。先读取每行，然后读取行的每个列 foreach(SD.DataRow row in ds.Tables["Table"].Rows) { c = new BankCustomer(row["FirstName"].ToString(), row["LastName"].ToString(), System.Convert.ToDecimal(row["AcctBalance"])); Globals.Customers.Add(c); } } catch(Exception ex) { throw ex; } finally { try{ dbConn.Close(); } catch{} } // bind customers to list box for display this.lstCustomers.DataSource = Globals.Customers; this.lstCustomers.SelectedIndex = -1; }

DATAGRID的操作

用SqlDataAdapter，DataSet取得数据的操作和上面一样，后面 // bind customers to list box for display 部分的内容改为以下一句话，就将DATASET中的数据绑定到了DATAGRID。 // bind customers to data grid for display this.dataGrid1.SetDataBinding(ds, "Table"); 对DATAGRID中的数据进行修改，然后执行保存操作，将结果反映到数据库。 private void mnuFileSave_Click(object sender, System.EventArgs e) { SQL.SqlConnection dbConn = null; SD.DataSet ds = null; try { string sConnect; sConnect = string.Format("Server={0};Database={1};{2}", "localhost", "Sales", "Integrated Security=SSPI"); dbConn = new SQL.SqlConnection(sConnect); string sql; sql = "Select * From Customers ;"; SQL.SqlCommand dbCmd; dbCmd = new SQL.SqlCommand(sql, dbConn); SQL.SqlDataAdapter dbAdapter; dbAdapter = new SQL.SqlDataAdapter(dbCmd); SQL.SqlCommandBuilder dbCmdBuilder; dbCmdBuilder = new SQL.SqlCommandBuilder(dbAdapter); ds = (SD.DataSet) this.dataGrid1.DataSource; dbConn.Open(); dbAdapter.Update(ds); //修改数据 dbConn.Close(); MessageBox.Show("Changes saved!"); } catch(Exception ex) { throw ex; } finally { try{ dbConn.Close(); } catch{} } }

补充的一些操作

1。参数化操作。查询的条件需要从外部取得，不能直接写在SQL语句中。

sql = "Select * From Customers Where CID=@CID Order By LastName Asc;"; SQL.SqlCommand dbCmd; dbCmd = new SQL.SqlCommand(sql, dbConn); dbCmd.Parameters.Add("@CID", SqlDbType.BigInt); //增加参数和类型 dbCmd.Parameters[0].Value = 1; //直接给参数赋值，也可以从外部参数取得

2。执行存储过程

SQL.SqlCommand dbCmd =dbConn.CreateCommand(); //新建一个命令 dbCmd.CommandText = "GetCustomer"; //存储过程名 dbCmd.CommandType = CommandType.StoredProcedure; //命令类型 dbCmd.Parameters.Add("@CID", SqlDbType.BigInt); //参数 dbCmd.Parameters[0].Value = 1;

3。事务处理

SQL.SqlTransaction txn = dbConn.BeginTransaction(); //开始事务 string strSQL = "INSERT INTO Customers VALUES(100,'Hui','Yu',1000000,1000000,'01-Jan-2005','money')"; SQL.SqlCommand dbCmd; dbCmd = new SQL.SqlCommand(strSQL, dbConn,txn); int intRecord = dbCmd.ExecuteNonQuery(); //执行只写操作 if(intRecord ==1) //如果只生成了一条记录 { SWF.MessageBox.Show( "Update Successful"); txn.Commit(); //数据写入数据库 } else { SWF.MessageBox.Show( "Update Failed"); txn.Rollback(); //恢复原来的数据 }