C#(7)：ADO.NET进行数据库编程

建立简单数据库程序：
1打开数据库连接
2执行查询语句并取回记录
3关闭数据库连接
-------------------
1打开数据库连接
  ADO.NET Connection对象；
  using SD=System.Data;
  using MSA=System.Data.OleDb;
  using SWF=System.Windows.Froms;

  string sConnect;
  sConnect=string.Format("Provider={0};Data Source={1}{2}",
 "provider=Microsoft.Jet.OLEDB.4.0;",
  System.AppDomain.CurrentDomain.BaseDirectory,
  "Sales.mdb");
MSA.OleDbConnection dbConn;
dbConn=new MSA.OleDbConnection(sConnect);
dbConn.open();
SWF.MessageBox.Show(dbConn.State.ToString());

2执行查询和取回数据
Command and Reader 对象：

string sql; BankCustomer c;
sql="Select*From Customers Order By LastName Asc,FirstName ASC;";

MSA.OleDbCommand dbCmd;
dbCmd=new MSA.OleDbCommand(sql,dbConn);

MSA.OleDbDataReader dbReader;
dbReader=dbCmd.ExecuteReader();

while(dbReader.Read())
{
  c=new BankCustomer(dbReader["FirstName"].ToString(),
                     dbReader["LastName"].ToString(),
              System.Convert.ToDecimal(dbReader["AccBalance"]));
   Globals.Customers.Add(c);
}

3 关闭数据库连接
dbReader.Close()；
dbConn.Close();
SWF.MessageBox.Show(dbConn.State.ToString());

ADO.NET体系结构
ADO.NET 基于Data Provider
  Data Provider 封装特定数据库的细节信息
    .net programmer ---ADO.NET---Data Provider---DB
Data provider有sql server and oracle
              oledb odbc
对象模型
不同的数据库之间
  1  相同的编程模型，不管使用何种数据库
 2 不同的数据库连接字符串
 3 不同的名称空间和类
    System.Data.SqlClient.SqlConnection
    System.Data.OracleClient.OracleConnection
   System.Data.Odbc.OdbcConnection
   etc.
Executive概要
 几种访问数据库的方式
   连接的，读取并返回一个值  ExecuteScalar()
   连接的，只读，单向        ExecuteReader()
   连接的 ，只写             ExecuteNonQuery()
   非连接的，读写            DataSets

ExecuteScalar()

string sql;
sql=string.Format("Select AcctBalance From Customers Where CID={0};",cid);

Sql.SqlCommand dbCmd;
dbcmd=new SQL.sqlCommand(sql,dbConn);

decimal balance;
balance=System.Convert.ToDecimal(dbCmd.ExecuteScalar());

DataSet

foreach(System.Data.DataRow row in ds.Table["Table"].Rows)
{
   c=new BankCustomer(row["FirstName"].ToString(),
                     row["LastName"].ToString(),
                   System.Convert.ToDecimal(row["AccBalance"]));
            Globals.Customers.Add(c);
            
}

绑定到DataGrid
this.dataGrid1.SetDataBinding(ds,"Table")

ExecuteNonQuery
写入数据库，插入，更新，和删除来对数据库进行操作

String sql;int rows;
sql.string.Format("Delete From Customers where..");
SQL.SqlCommand dbCmd;
dbCmd=new SQL.SqlCommand(sql,dbConn);

dbConn.Open()
rows=dbCmd.ExecuteNonQutery();
dbConn.Close();
if(rows!=1)
  throw new System.ApplicationException("Delete failed?!");

参数化查询
Select FirstName,LastName From Customers where CIC=@CID
Sql="Select*From Customer Where CID=@CID order By LastName Asc, FirstName Asc;";
SQL.SqlCommand dbcmd;
dbCmd=new SQL.SqlCommand(sql,dbConn);
dbCmd.Parameters.Add("@CID",SqlDbType.BigInt);
dbCmd,Parameters[0].Value=1;
dbReader=dbCmd.ExecuteReader();

调用存储过程
Create PROCEDURE GetCustomer (@CID BigInt) AS
Select FirstName,LastName,AcctBalance From Customers Where CID = @CID
RETURN
SQL.SqlCommand dbCmd =dbConn.CreateCommand();
dbCmd.CommandText = "GetCustomer";
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Parameters.Add("@CID", SqlDbType.BigInt);
dbCmd.Parameters[0].Value = 1;
dbReader = dbCmd.ExecuteReader();

Transaction中执行查询
Command对象的BeginTransaction来创
建一个Transaction对象
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( "UpdateSuccessful");txn.Commit();
}
  else
 {
  SWF.MessageBox.Show( "Update Failed"); txn.Rollback();
}