本文适合有部分经验的人多,表自己想象一下,或则参考我上篇文章的表,其实就是上篇内容的部分。
同时更新表两次,当然其他插入删除什么的一样做。参考PetShop4.0
部分代码如下:
调用的两个函数如下:
测试通过!
下面是微软的PetShop大家可以看看:
同时更新表两次,当然其他插入删除什么的一样做。参考PetShop4.0
部分代码如下:
OracleConnection conn
=
new
OracleConnection(System.Configuration.ConfigurationManager.AppSettings[
"
DBConnStr
"
]);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
string updSql = " update sys_dict set 内容=' " + txtContent.Text.Trim() + " ' where 编号= " + txtID.Text.Trim() + " " ;
string updCatSql = " update sys_dict set 分类名称 = ' " + txtContent.Text.Trim() + " ' where 分类名称 = ' " + lblOldContent.Text.Trim() + " ' " ;
try
{
ExecuteNonQuery(trans, CommandType.Text, updSql, null);
ExecuteNonQuery(trans, CommandType.Text, updCatSql, null);
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new ApplicationException(e.Message);
UPD_SUCESS_FLAG = false;
}
finally
{
conn.Close();
}
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
string updSql = " update sys_dict set 内容=' " + txtContent.Text.Trim() + " ' where 编号= " + txtID.Text.Trim() + " " ;
string updCatSql = " update sys_dict set 分类名称 = ' " + txtContent.Text.Trim() + " ' where 分类名称 = ' " + lblOldContent.Text.Trim() + " ' " ;
try
{
ExecuteNonQuery(trans, CommandType.Text, updSql, null);
ExecuteNonQuery(trans, CommandType.Text, updCatSql, null);
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
throw new ApplicationException(e.Message);
UPD_SUCESS_FLAG = false;
}
finally
{
conn.Close();
}
调用的两个函数如下:
private
int
ExecuteNonQuery(OracleTransaction trans, CommandType cmdType,
string
cmdText,
params
OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Internal function to prepare a command for execution by the database
/// </summary>
/// <param name="cmd">Existing command object</param>
/// <param name="conn">Database connection object</param>
/// <param name="trans">Optional transaction object</param>
/// <param name="cmdType">Command type, e.g. stored procedure</param>
/// <param name="cmdText">Command test</param>
/// <param name="commandParameters">Parameters for the command</param>
private void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
//Bind it to the transaction if it exists
if (trans != null)
cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (OracleParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
}
测试通过!
下面是微软的PetShop大家可以看看:
/// <summary>
/// Update account for current user
/// </summary>
/// <param name="uniqueID">User id</param>
/// <param name="addressInfo">Account information for current user</param>
public void SetAccountInfo( int uniqueID, AddressInfo addressInfo) {
string sqlDelete = "DELETE FROM Account WHERE UniqueID = :UniqueID";
OracleParameter param = new OracleParameter(":UniqueID", OracleType.Int32);
param.Value = uniqueID;
string sqlInsert = "INSERT INTO Account (UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone) VALUES (:UniqueID, :Email, :FirstName, :LastName, :Address1, :Address2, :City, :State, :Zip, :Country, :Phone)";
OracleParameter[] parms = {
new OracleParameter(":UniqueID", OracleType.Number, 10),
new OracleParameter(":Email", OracleType.VarChar, 80),
new OracleParameter(":FirstName", OracleType.VarChar, 80),
new OracleParameter(":LastName", OracleType.VarChar, 80),
new OracleParameter(":Address1", OracleType.VarChar, 80),
new OracleParameter(":Address2", OracleType.VarChar, 80),
new OracleParameter(":City", OracleType.VarChar, 80),
new OracleParameter(":State", OracleType.VarChar, 80),
new OracleParameter(":Zip", OracleType.VarChar, 80),
new OracleParameter(":Country", OracleType.VarChar, 80),
new OracleParameter(":Phone", OracleType.VarChar, 80)};
parms[0].Value = uniqueID;
parms[1].Value = addressInfo.Email;
parms[2].Value = addressInfo.FirstName;
parms[3].Value = addressInfo.LastName;
parms[4].Value = addressInfo.Address1;
parms[5].Value = addressInfo.Address2;
parms[6].Value = addressInfo.City;
parms[7].Value = addressInfo.State;
parms[8].Value = addressInfo.Zip;
parms[9].Value = addressInfo.Country;
parms[10].Value = addressInfo.Phone;
OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringProfile);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, param);
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
trans.Commit();
}
catch(Exception e) {
trans.Rollback();
throw new ApplicationException(e.Message);
}
finally {
conn.Close();
}
}
/// Update account for current user
/// </summary>
/// <param name="uniqueID">User id</param>
/// <param name="addressInfo">Account information for current user</param>
public void SetAccountInfo( int uniqueID, AddressInfo addressInfo) {
string sqlDelete = "DELETE FROM Account WHERE UniqueID = :UniqueID";
OracleParameter param = new OracleParameter(":UniqueID", OracleType.Int32);
param.Value = uniqueID;
string sqlInsert = "INSERT INTO Account (UniqueID, Email, FirstName, LastName, Address1, Address2, City, State, Zip, Country, Phone) VALUES (:UniqueID, :Email, :FirstName, :LastName, :Address1, :Address2, :City, :State, :Zip, :Country, :Phone)";
OracleParameter[] parms = {
new OracleParameter(":UniqueID", OracleType.Number, 10),
new OracleParameter(":Email", OracleType.VarChar, 80),
new OracleParameter(":FirstName", OracleType.VarChar, 80),
new OracleParameter(":LastName", OracleType.VarChar, 80),
new OracleParameter(":Address1", OracleType.VarChar, 80),
new OracleParameter(":Address2", OracleType.VarChar, 80),
new OracleParameter(":City", OracleType.VarChar, 80),
new OracleParameter(":State", OracleType.VarChar, 80),
new OracleParameter(":Zip", OracleType.VarChar, 80),
new OracleParameter(":Country", OracleType.VarChar, 80),
new OracleParameter(":Phone", OracleType.VarChar, 80)};
parms[0].Value = uniqueID;
parms[1].Value = addressInfo.Email;
parms[2].Value = addressInfo.FirstName;
parms[3].Value = addressInfo.LastName;
parms[4].Value = addressInfo.Address1;
parms[5].Value = addressInfo.Address2;
parms[6].Value = addressInfo.City;
parms[7].Value = addressInfo.State;
parms[8].Value = addressInfo.Zip;
parms[9].Value = addressInfo.Country;
parms[10].Value = addressInfo.Phone;
OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringProfile);
conn.Open();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try {
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, param);
OracleHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parms);
trans.Commit();
}
catch(Exception e) {
trans.Rollback();
throw new ApplicationException(e.Message);
}
finally {
conn.Close();
}
}