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 {}
            }

转载于:https://www.cnblogs.com/hotsoho.net/articles/240721.html