Modern C#系列课程(7): ADO.NET进行数据库编程
ADO.NET进行数据库编程
1、连接字符串查询:http://www.connectionstrings.com
2、注意Connection、Command、DataAdapter、CommandBuilder、Parameters、Transaction对象的关系
3、连接对象打开后一定要记得关闭,不管是否有异常发生都是。
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![](/Images/dot.gif)
{
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{}
}
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
![](/Images/dot.gif)
{
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{}
}