Modern C#系列课程(7): ADO.NET进行数据库编程

ADO.NET进行数据库编程
   1、连接字符串查询：http://www.connectionstrings.com
   2、注意Connection、Command、DataAdapter、CommandBuilder、Parameters、Transaction对象的关系
   3、连接对象打开后一定要记得关闭，不管是否有异常发生都是。

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();
                string  sql;
/*
                //dataset
                sql = "Select * From Customers Where CID=1 Order By LastName Asc, FirstName Asc;";
                SQL.SqlCommand  dbCmd;
                dbCmd = new SQL.SqlCommand(sql, dbConn);
                SQL.SqlDataAdapter adapter = new SQL.SqlDataAdapter(dbCmd);
                DataSet ds;
                adapter.Fill(ds);

                //update dataset
                SQL.SqlCommand dbCmd = dbConn.CreateCommand();
                SQL.SqlDataAdapter adapter = new SQL.SqlDataAdapter(dbCmd);
                SQL.SqlCommandBuilder cb = new SQL.SqlCommandBuilder(adapert);
                adapter.Update(ds);
*/
/*
                //parameter begin
                sql = "Select * From Customers 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;
                //parameter end
*/    
/*                
                //procedure begin
                SQL.SqlCommand  dbCmd =dbConn.CreateCommand();
                dbCmd.CommandText = "GetCustomer";
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add("@CID", SqlDbType.BigInt);
                dbCmd.Parameters[0].Value = 1;
                //precedure end
*/
                //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( "Update Successful");
                    txn.Commit();
                }
                else
                {
                    SWF.MessageBox.Show( "Update Failed");
                    txn.Rollback();
                }            
            
/*                
                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{}
            }