public override DatabaseModel.ResultSets GetResultSets(IDbConnection connection, string storedProcedureName)
{
var transaction = connection.BeginTransaction();
try
{
var command = PrepareDefaultStoredProcedureCommand(connection, storedProcedureName);
command.Transaction = transaction;
using (OracleDataAdapter adapter = new OracleDataAdapter())
{
adapter.SelectCommand = (OracleCommand)command;
DataSet dataSet = new DataSet();
try
{
adapter.FillSchema(dataSet, SchemaType.Source);
}
catch (Exception exc)
{
if (exc.Message == "TTCExecuteSql:ReceiveExecuteResponse - Unexpected Packet received.")
{
adapter.FillSchema(dataSet, SchemaType.Source);
}
else
{
throw exc;
}
}
return(new DatabaseModel.ResultSets(dataSet));
}
}
finally
{
transaction.Rollback();
}
}
/// <summary>
/// save photo into db
/// 保存图片到数据库（2005-5-14）
/// </summary>
/// <param name="data_id">ID的数值</param>
/// <param name="p_Blob">图片BLOB</param>
/// <param name="id">ID键</param>
/// <param name="photo">图片键</param>
/// <param name="tablename">表名</param>
private int SavePhoto(string PrimaryID, byte[] p_Blob, string id, string filecontent, string tablename, OracleTransaction trans)
{
try
{
OracleDataAdapter photoAdapter;
DataSet photoDataSet;
DataTable photoTable;
DataRow photoRow;
string strSql = "SELECT primary_id, file_name,local_directory,file_content, file_version, oper_code, oper_date FROM com_downloadfile t WHERE t.primary_id = '{0}'";
strSql = string.Format(strSql, PrimaryID);
photoAdapter = new OracleDataAdapter(strSql, Oracon);
photoDataSet = new DataSet(tablename);
string strSQL = "UPDATE com_downloadfile SET file_content= :fileContent ,oper_date= sysdate WHERE primary_id= '{0}'";
strSQL = string.Format(strSQL, PrimaryID);
photoAdapter.UpdateCommand = new OracleCommand(strSQL, Oracon);
//设置事务
photoAdapter.UpdateCommand.Transaction = trans;
photoAdapter.UpdateCommand.Parameters.Add(":fileContent",
OracleType.Blob, p_Blob.Length, filecontent);
//
// photoAdapter.UpdateCommand.Parameters.Add(":ID",
// OracleType.VarChar, PrimaryID.Length, PrimaryID);
photoAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// photoAdapter.InsertCommand.Transaction = trans;
photoAdapter.SelectCommand.Transaction = trans;
// photoAdapter.DeleteCommand.Transaction = trans;
// Configures the schema to match with Data Source
photoAdapter.FillSchema(photoDataSet, SchemaType.Source, tablename);
// Fills the DataSet with 'drivers' table data
photoAdapter.Fill(photoDataSet, tablename);
// Get the current driver ID row for updation
photoTable = photoDataSet.Tables[tablename];
photoRow = photoTable.Rows.Find(PrimaryID);
// Start the edit operation on the current row in
// the 'drivvers' table within the dataset.
photoRow.BeginEdit();
// Assign the value of the Photo if not empty
if (p_Blob.Length != 0)
{
photoRow[filecontent] = p_Blob;
}
// End the editing current row operation
photoRow.EndEdit();
// Update the database table 'drivers'
photoAdapter.Update(photoDataSet, tablename);
return(1);
}
catch (Exception e)
{
trans.Rollback();
MessageBox.Show(e.Message);
return(-1);
}
}
/// <summary>
/// 返回具有指定表完整架构信息，包括主键，约束等信息的DataTable。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="schemaType">Type of the schema.</param>
/// <param name="con">数据库连接</param>
/// <param name="ts">事务</param>
/// <returns>包含完整架构信息的DataTable</returns>
public DataTable FillShema(string tableName, SchemaType schemaType, IDbConnection con, IDbTransaction ts)
{
OracleDataAdapter adapter = new OracleDataAdapter();
DataTable dt = null;
try
{
adapter.SelectCommand = new OracleCommand("select * from " + tableName, con as OracleConnection);
if (ts != null)
{
adapter.SelectCommand.Transaction = ts as OracleTransaction;
}
dt = new DataTable(tableName);
adapter.FillSchema(dt, schemaType);
return(dt);
}
catch (OracleException ex)
{
throw ex;
}
}
public DataSet QueryDataSet(string CommandString)
{
try
{
OracleDataAdapter adapter = new OracleDataAdapter(CommandString, OracleDbase.mOralConnection);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
adapter.FillSchema(ds, SchemaType.Source);
ArrayList ar = new ArrayList();
if (ds.Tables[0].PrimaryKey != null)
{
foreach (DataColumn dc in ds.Tables[0].PrimaryKey)
{
ar.Add(dc.ColumnName);
}
mPrimaryKey = (string[])ar.ToArray(Type.GetType("System.String"));
}
return(ds);
}
else
{
return(null);
}
}
catch (Exception ex)
{
MessageBox.Show("数据库连接错误!" + ex.Message.ToString());
return(null);
}
}
/// <summary>
/// Instanct FillSchema
/// </summary>
/// <param name="Ps_SQL"></param>
/// <param name="Ps_ConnectionName"></param>
/// <returns></returns>
public static DataSet Schema(string Ps_SQL, CommandType P_CmdType = CommandType.Text)
{
DataSet L_DataSet = null;
try
{
using (OraClient ORA = new OraClient(InstantConnectionName))
{
using (OracleDataAdapter DA = new OracleDataAdapter(Ps_SQL, ORA.Conn))
{
DA.SelectCommand.CommandType = P_CmdType;
DA.SelectCommand.CommandTimeout = ORA.CommandTimeout;
L_DataSet = new DataSet();
DA.FillSchema(L_DataSet, SchemaType.Source);
}
}
}
catch
{
throw;
}
return(L_DataSet);
}
public DataSet GetSchema(string sql)
{
OracleDataAdapter dadapter = new OracleDataAdapter();
dadapter.SelectCommand = new OracleCommand(sql, _OracleConnection);
DataSet ds = new DataSet();
dadapter.FillSchema(ds, SchemaType.Mapped);
return(ds);
}
/// <summary>
/// 填充SQL或存储过程的表构架信息
/// </summary>
/// <param name="commandText">将结果集中架构填充到DataTable</param>
public static void FillSchema(string ConnectionString, DataTable table, string commandText, out Exception e)
{
e = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleDataAdapter adapter = new OracleDataAdapter(commandText, connection);
try
{
adapter.FillSchema(table, SchemaType.Source);
}
catch (Exception ex)
{
e = ex;
}
adapter.Dispose();
connection.Dispose();
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=<PASSWORD>;Data Source=oracle";
OracleConnection con = Connect(constr);
// Setup the table & Data
Setup(con);
// Create the OracleCommand
OracleCommand cmd = new OracleCommand("select story from multimedia_tab");
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
// Create the OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
// Populate a DataSet
DataSet ds = new DataSet();
try
{
da.FillSchema(ds, SchemaType.Source, "Media");
// Populate the DataSet with LOB data
da.Fill(ds, "Media");
// Obtain LOB data from the database and print it out
Console.WriteLine(ds.Tables["Media"].Rows[0]["story"]);
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
}
public string ExecuteBlog(string deviceId, string blob)
{
byte[] byteArray = System.Convert.FromBase64String(blob);
string sql = "SELECT device_id,dt,image_name FROM image";
DbCommand dbCommand = _db.GetSqlStringCommand(sql);
using (OracleConnection dbConn = new OracleConnection(connectionString))
{
dbConn.Open();
OracleDataAdapter da = new OracleDataAdapter("SELECT device_id,dt,image_name FROM image", dbConn);
DataTable dt = new DataTable();
da.FillSchema(dt, SchemaType.Source);
OracleCommandBuilder cb = new OracleCommandBuilder(da);
// create a row containing the data
DataRow row = dt.NewRow();
row["device_id"] = deviceId;
row["dt"] = DateTime.Now.ToString();
row["image_name"] = byteArray;
dt.Rows.Add(row);
// update the table
int i = 20;
try
{
i = da.Update(dt);
}
catch (Exception e)
{
return(e.Message.ToString());
}
finally
{
dbConn.Close();
}
return("true");
}
}
public void TestLongSqlExpression()
{
BeginCase("Long SQL string cause java.lang.StackOverflowError (Test case for bug #4708)");
StringBuilder querySb = new StringBuilder();
querySb.Append("SELECT ");
querySb.Append("c.CustomerID as ci1, c.CustomerID as ci2, c.CustomerID as ci3, c.CustomerID as ci4, ");
querySb.Append("c.CompanyName as cn1, c.CompanyName as cn2, c.CompanyName as cn3, c.CompanyName as cn4, ");
querySb.Append("c.ContactName as cntn1, c.ContactName as cntn2, c.ContactName as cntn3, c.ContactName as cntn4, ");
querySb.Append("c.ContactTitle as ct1, c.ContactTitle as ct2, c.ContactTitle as ct3, c.ContactTitle as ct4, ");
querySb.Append("c.Address as ad1, c.Address as ad2, c.Address as ad3, c.Address as ad4, ");
querySb.Append("c.City as ct1, c.City as ct2, c.City as ct3, c.City as ct4, ");
querySb.Append("c.Region as rg1, c.Region as rg2, c.Region as rg3, c.Region as rg4, ");
querySb.Append("c.PostalCode as pc1, c.PostalCode as pc2, c.PostalCode as pc3, c.PostalCode as pc4, ");
querySb.Append("c.Country as co1, c.Country as co2, c.Country as co3, c.Country as co4, ");
querySb.Append("c.Phone as ph1, c.Phone as ph2, c.Phone as ph3, c.Phone as ph4, ");
querySb.Append("c.Fax as fx1, c.Fax as fx2, c.Fax as fx3, c.Fax as fx4 ");
querySb.Append("FROM Customers c");
OracleDataAdapter adapter = null;
DataSet schemaDs = new DataSet();
try
{
using (adapter = new OracleDataAdapter(querySb.ToString(), this.connectionString))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.FillSchema(schemaDs, SchemaType.Source);
Compare(schemaDs.Tables.Count, 1);
}
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
public DataTable GetSchemaStruct(string sqlstr, params IDbDataParameter[] args)
{
error = null;
using (OracleConnection myConn = new OracleConnection(this.ConnStr))
{
OracleCommand myCmd = new OracleCommand(sqlstr, myConn);
try
{
DataTable dt = new DataTable();
myConn.Open();
foreach (IDbDataParameter arg in args)
{
if (myCmd.CommandType != CommandType.StoredProcedure)
{
myCmd.CommandType = CommandType.StoredProcedure;
}
myCmd.Parameters.Add(arg);
}
OracleDataAdapter myAdapter = new OracleDataAdapter(myCmd);
myAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdapter.FillSchema(dt, SchemaType.Mapped);
myAdapter.Dispose();
return(dt);
}
catch (OracleException ex)
{
error = ex;
return(null);
}
finally
{
myConn.Close();
myCmd.Dispose();
myConn.Dispose();
}
}
}
internal DataSet OracleImport()
{
//DataSet Object that will contain contents of file
//DataSet FileContents = new DataSet();
RawData = new DataSet();
DateTime LookBackDate = new DateTime();
OracleConnection conn_oracle = new OracleConnection(dataProp.SourceConnectionString);
OracleCommand comm = new OracleCommand();
comm.Connection = conn_oracle;
string commandText = "Select * from " + dataProp.SourceTableName;
if (dataProp.UseLookBack)
{
commandText += " WHERE " + dataProp.LookBackColumnName + " >= :LookBackPeroid";
//Set the look back date to be one second prior to midnight on the date of the look back period.
LookBackDate = DateTime.Now.AddDays(-dataProp.LookBackPeriod - 1).AddHours(-DateTime.Now.Hour + 23).AddMinutes(-DateTime.Now.Minute + 59).AddSeconds(-DateTime.Now.Second + 59);
comm.Parameters.Add(new OracleParameter("LookBackPeroid", LookBackDate));
comm.Parameters["LookBackPeroid"].OracleType = OracleType.DateTime;
}
comm.CommandText = commandText;
//Pass the SQL Command with the command text, parameters and connection into the adapter
OracleDataAdapter Adapter_Oracle = new OracleDataAdapter(comm);
Adapter_Oracle.FillSchema(RawData, SchemaType.Source, dataProp.SourceTableName);
Adapter_Oracle.Fill(RawData, dataProp.SourceTableName);
rowImportedFromSource = RawData.Tables[0].Rows.Count;
return(RawData);
}
/// <summary>
/// 未测试
/// </summary>
//public DataSet ExecuteDataSet(CommandType cmdType, string cmdText,List<Parm> cmdParameters)
//{
// OracleCommand cmd = new OracleCommand();
// using (OracleConnection conn = new OracleConnection(ConnectStr))
// {
// PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParameters);
// OracleDataAdapter adapter = new OracleDataAdapter(cmd);
// DataSet ds = new DataSet();
// try
// {
// adapter.FillSchema(ds, SchemaType.Source);
// // Fill data
// adapter.Fill(ds);
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// cmd.Parameters.Clear();
// cmd.Dispose();
// }
// return ds;
// }
//}
public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, OracleParameter[] cmdParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, GetConnect, null, cmdType, cmdText, cmdParameters);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
adapter.FillSchema(ds, SchemaType.Source);
adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Parameters.Clear();
cmd.Dispose();
}
return(ds);
}
public DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType)
{
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].TableName != "Table" && m_adapter.TableMappings.Count == 0)
{
m_adapter.TableMappings.Add("Table", dataSet.Tables[0].TableName);
}
//Isole la partie de requête avant le where
int nIndexWhere = m_adapter.SelectCommand.CommandText.ToUpper().IndexOf("WHERE");
string strRequetePourSchema = m_adapter.SelectCommand.CommandText.Trim();
if (nIndexWhere > 0)
{
strRequetePourSchema = strRequetePourSchema.Substring(0, nIndexWhere - 1).Trim();
}
DataTable tableCache = null;
string strKeySchema = m_connexion.ConnectionString + "/" + strRequetePourSchema.ToUpper();
//ATTENTION : la requête de schéma peut être la même s'il y a deux tables
//du même nom dans deux bases différentes. Par contre, on est sûr que le nom
//dans le CContexteDonnee est unique, donc on l'ajoute à la suite de la requête
//pour la clé de cache
if (dataSet.Tables.Count > 0)
{
strKeySchema += "_" + dataSet.Tables[0].TableName;
}
else
if (m_adapter.TableMappings.Count > 0)
{
strKeySchema += "_" + m_adapter.TableMappings[0].DataSetTable;
}
if (m_cacheSchemas.TryGetValue(strKeySchema, out tableCache))
{
DataTable tableDest = dataSet.Tables[tableCache.TableName];
if (tableDest != null)
{
//Copie la structure de la table modèle dans la table du DataSet
if (tableDest.Columns.Count == 0)
{
foreach (DataColumn col in tableCache.Columns)
{
DataColumn colCopie = new DataColumn();
colCopie.AllowDBNull = col.AllowDBNull;
colCopie.AutoIncrement = col.AutoIncrement;
colCopie.AutoIncrementSeed = col.AutoIncrementSeed;
colCopie.AutoIncrementStep = col.AutoIncrementStep;
colCopie.Caption = col.Caption;
colCopie.ColumnMapping = col.ColumnMapping;
colCopie.ColumnName = col.ColumnName;
colCopie.DataType = col.DataType;
colCopie.DateTimeMode = col.DateTimeMode;
colCopie.DefaultValue = col.DefaultValue;
colCopie.Expression = col.Expression;
colCopie.MaxLength = col.MaxLength;
colCopie.ReadOnly = col.ReadOnly;
colCopie.Unique = col.Unique;
tableDest.Columns.Add(colCopie);
}
List <DataColumn> keys = new List <DataColumn>();
foreach (DataColumn col in tableCache.PrimaryKey)
{
keys.Add(tableDest.Columns[col.ColumnName]);
}
tableDest.PrimaryKey = keys.ToArray();
}
return(new DataTable[] { tableDest });
}
else
{
tableDest = (DataTable)tableCache.Clone();
dataSet.Tables.Add(tableDest);
return(new DataTable[] { tableDest });
}
}
else
{
DataTable[] tables = m_adapter.FillSchema(dataSet, schemaType);
for (int ndt = tables.Length; ndt > 0; ndt--)
{
DataTable dt = tables[ndt - 1];
dt = TyperLesColonnes(dt);
m_cacheSchemas[strKeySchema] = (DataTable)dt.Clone();
}
return(tables);
}
}
public static DataTable RetrieveSchema(ConnectionType connectionType, string connectionString, string sqlString)
{
DataTable dataSet = new DataTable();
switch (connectionType)
{
case ConnectionType.SqlServer:
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlString, connectionString))
{
sqlDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
break;
case ConnectionType.Odbc:
using (OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter(sqlString, connectionString))
{
odbcDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
if (dataSet.Columns.Count == 0 && dataSet.Rows.Count == 0)
{
OdbcCommand myOdbcCommand = null;
try
{
myOdbcCommand = new OdbcCommand(sqlString);
myOdbcCommand.Connection = new OdbcConnection(connectionString);
myOdbcCommand.Connection.Open();
OdbcDataReader dataReader = myOdbcCommand.ExecuteReader();
DataTable schema = dataReader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
Type runtimeType = row[5].GetType();
PropertyInfo propInfo = runtimeType.GetProperty("UnderlyingSystemType");
dataSet.Columns.Add(row[0].ToString(), (Type)propInfo.GetValue(row[5], null));
}
}
finally
{
myOdbcCommand.Connection.Close();
}
}
break;
case ConnectionType.Oracle:
using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(sqlString, connectionString))
{
try
{
oracleDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
catch (Exception exc)
{
if (exc.Message == "TTCExecuteSql:ReceiveExecuteResponse - Unexpected Packet received.")
{
oracleDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
else
{
throw exc;
}
}
}
break;
case ConnectionType.OleDb:
using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(sqlString, connectionString))
{
oleDbDataAdapter.FillSchema(dataSet, SchemaType.Source);
}
break;
}
return(dataSet);
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
// Connect
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = Connect(constr);
// Setup the table & Data
Setup(con);
// Create the OracleCommand
OracleCommand cmd = new OracleCommand("SELECT * FROM po_tab");
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
// Create the OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
// Populate a DataSet
DataSet ds = new DataSet();
try
{
da.FillSchema(ds, SchemaType.Source, "po_tab");
// Populate the DataSet with XMLType data
da.Fill(ds, "po_tab");
// Obtain XMLType data from the database and print it out
Console.WriteLine("XMLType data from Database: " +
ds.Tables["po_tab"].Rows[0]["po"]);
Console.WriteLine("");
StringBuilder blr = new StringBuilder();
blr.Append("<?xml version=\"1.0\"?> <PO pono=\"1\"> ");
blr.Append("<PNAME>NewPo_1</PNAME> <CUSTNAME>John Doe</CUSTNAME> ");
blr.Append("<SHIPADDR> <STREET>555, Market Street</STREET> ");
blr.Append("<CITY>San Francisco</CITY> <STATE>CA</STATE> </SHIPADDR> ");
blr.Append("</PO>");
// Update the data in the DataSet
ds.Tables["po_tab"].Rows[0]["PO"] = blr.ToString();
OracleCommandBuilder CmdBuilder = new OracleCommandBuilder(da);
// Update the table in the database
da.Update(ds, "po_tab");
// Fetch the data from the table
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
Console.WriteLine("XMLType data from database after update statement: " +
dr.GetOracleXmlType(0).Value);
dr.Close();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Cleanup
Cleanup(con);
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
}
// Demonstrates how a DataSet can be populated from a Ref Cursor.
// The sample also demonstrates how a Ref Cursor can be updated
public void refCursor4()
{
// Connect
OracleConnection con = Connect(constr);
// Setup
DynamicDBObject.Setup4(con);
// Set the command
OracleCommand cmd = new OracleCommand("TESTPACKAGE.Ret1Cur", con);
cmd.CommandType = CommandType.StoredProcedure;
// Bind
// TEST.Ret1Cur is a function so ParameterDirection is ReturnValue.
OracleParameter param = cmd.Parameters.Add("refcursor",
OracleDbType.RefCursor);
param.Direction = ParameterDirection.ReturnValue;
// Create an OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
try
{
// 1. Demostrate populating a DataSet with RefCursor
// Populate a DataSet
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source, "myRefCursor");
da.Fill(ds, "myRefCursor");
// Obtain the row which we want to modify
DataRow[] rowsWanted = ds.Tables["myRefCursor"].Select("THEKEY = 1");
// 2. Demostrate how to update with RefCursor
// Update the "story" column
rowsWanted[0]["story"] = "New story";
// Setup the update command on the DataAdapter
OracleCommand updcmd = new OracleCommand("TESTPACKAGE.UpdateREFCur", con);
updcmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = updcmd.Parameters.Add("myStory",
OracleDbType.Varchar2, 32);
param1.SourceVersion = DataRowVersion.Current;
param1.SourceColumn = "STORY";
OracleParameter param2 = updcmd.Parameters.Add("myClipId",
OracleDbType.Decimal);
param2.SourceColumn = "THEKEY";
param2.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = updcmd;
// Update
da.Update(ds, "myRefCursor");
Console.WriteLine("Data has been updated.");
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}
}
protected override ExeResEdm UpdateDsToDB(DataSet dsTables, Dictionary <string, string> dicDtMainFields = null)
{
ExeResEdm dBResEdm = new ExeResEdm();
int n = 0;
try
{
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
OracleTransaction tsOprate = conn.BeginTransaction();
try
{
OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
foreach (DataTable dtTemp in dsTables.Tables)
{
string strComFields = "*";
if (dicDtMainFields != null && dicDtMainFields.Count > 0 && dicDtMainFields.ContainsKey(dtTemp.TableName))
{
strComFields = dicDtMainFields[dtTemp.TableName];
}
cmd.CommandText = GetColumnsNameSql(dtTemp.TableName, strComFields);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
var dtChanges = dtTemp.GetChanges();
adapter.FillSchema(dtChanges, SchemaType.Mapped); //new added
if (dtChanges != null) //是添加或更新
{
adapter.UpdateCommand = new OracleCommandBuilder(adapter).GetUpdateCommand();
n += adapter.Update(dtChanges);
dtTemp.AcceptChanges();
}
else //是删除
{
adapter.DeleteCommand = new OracleCommandBuilder(adapter).GetDeleteCommand();
for (int i = dtTemp.Rows.Count - 1; i >= 0; i--)
{
dtTemp.Rows[i].Delete();
}
n += adapter.Update(dtTemp);
}
}
dsTables.AcceptChanges();
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeNum = n;
return(dBResEdm);
}
/*****************************************************************************
* This method is called from the click event of Save button and
* SelectedIndexChanged event of Products DropDown list.
*
* The purpose of this method is to demonstrate DML operations on a Data Set for
* LOB(Large Object)data. The functionalitity of this method is to insert
* a new advertisement or update an existing advertisement for the product chosen from
* the 'productCbBx' list. The advertisement data is stored in the
* database table 'PrintMedia'.
*
* The flow of this method is as follows:
* 1. Instantiate an OracleDataAdapter object with the query for 'PrintMedia'
* table.
* 2. Configure the schema to match with Data Source. Set Primary Key information.
* 3. OracleCommandBuilder automatically generates the command for loading data
* for the given query.
* 4. The Dataset is filled with data that is loaded through OracleDataAdapter.
* 5. Create a DataRow in a DataTable contained in the DataSet for a new
* advertisement or find the current advertisement DataRow for existing
* advertisement.
* 6. Convert new advertisement image into a byte array.
* 7. Assign the corresponding values to the columns in the Data Row.
* 8. Add the Data Row to the Data Set for a new advertisement or end the edit
* operation for existing advertisement.
* 9. Update the database with the Data Set values. Hence adding/updating
* 'PrintMedia' table data.
*************************************************************************/
private void updateData()
{
try
{
//Check if Ad Image or Ad Text is changed.
if (strImageName != "" || strExistText != adTextBx.Text)
{
//Change the default cursor to 'WaitCursor'(an HourGlass)
this.Cursor = Cursors.WaitCursor;
//If curAdId is null then insert record
if (curAdID == "")
{
//To fill DataSet and update datasource
OracleDataAdapter printmediaAdapter;
//In-memory cache of data
DataSet printmediaDataSet;
//Data Row contained in Data Table
DataRow printmediaRow;
//For automatically generating commands to make changes to database through DataSet
OracleCommandBuilder printmediaCmdBldr;
//Step 1.//
//Query for 'PrintMedia' table given with OracleDbAdapter
printmediaAdapter = new OracleDataAdapter("SELECT ad_text, ad_image, " +
" product_id, date_of_creation FROM PrintMedia", conn);
//Instantiate a DataSet object
printmediaDataSet = new DataSet("PrintMedia");
//Step 2.//
//AddWithKey sets the Primary Key information to complete the
//schema information
printmediaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//Configures the schema to match with Data Source
printmediaAdapter.FillSchema(printmediaDataSet, SchemaType.Source, "PrintMedia");
//Step 3.//
//In this case 'OracleCommandBuilder' automatically generates
//'SelectCommand'
printmediaCmdBldr = new OracleCommandBuilder(printmediaAdapter);
//Step 4.//
//Adapter fills the DataSet with 'PrintMedia' data
printmediaAdapter.Fill(printmediaDataSet, "PrintMedia");
//Step 5.//
//Create a new row in the DataTable contained in the DataSet
printmediaRow = printmediaDataSet.Tables["PrintMedia"].NewRow();
//part of Step 7.//
//Assigning the value of advertisement text
printmediaRow["Ad_text"] = adTextBx.Text;
//If image is added
if (strImageName != "")
{
//Step 6.//
//providing read access to the file chosen using the 'Browse' button
FileStream fs = new FileStream(@strImageName, FileMode.Open, FileAccess.Read);
//Create a byte array of file stream length
byte[] adImageData = new byte[fs.Length];
//Read block of bytes from stream into the byte array
fs.Read(adImageData, 0, System.Convert.ToInt32(fs.Length));
//Close the File Stream
fs.Close();
// part of Step 7.//
//Assigning the byte array containing image data
printmediaRow["Ad_image"] = adImageData;
}
//Step 7.//
//Assigning product id value with the 'ValueMember' of product drop down list
printmediaRow["product_id"] = intProdID;
//Assigning date of creation for advertisement to current date
printmediaRow["date_of_creation"] = System.DateTime.Today;
//Step 8.//
//Adding the 'printmediaRow' to the DataSet
printmediaDataSet.Tables["PrintMedia"].Rows.Add(printmediaRow);
//Step 9.//
//Update the database table 'PrintMedia' with new printmedia rows
printmediaAdapter.Update(printmediaDataSet, "PrintMedia");
//On successful Insertion of Ad Image display the image in "exisitingImagePicBx"
//and clear the "newImagePicBx"
if (strImageName != "")
{
//Create a bitmap for selected image
Bitmap newImage = new Bitmap(strImageName);
//Fit the image to the size of picture box
existingImagePicBx.SizeMode = PictureBoxSizeMode.StretchImage;
//Show the bitmap in picture box
existingImagePicBx.Image = (Image)newImage;
//Clear contents
newImagePicBx.Image = null;
}
//Reset Values
strImageName = "";
strExistText = adTextBx.Text;
//Set the wait cursor to default cursor
this.Cursor = Cursors.Default;
}
//If advertisement exists, then update
else
{
//Change the default cursor to 'WaitCursor'(an HourGlass)
this.Cursor = Cursors.WaitCursor;
//To fill Dataset and update datasource
OracleDataAdapter printmediaAdapter;
//In-memory cache of data
DataSet printmediaDataSet;
//Data Row contained in Data Table
DataRow printmediaRow;
//For automatically generating commands to make changes to database through DataSet
OracleCommandBuilder printmediaCmdBldr;
//Step 1.//
//Query for 'PrintMedia' table given with OracleDataAdapter
printmediaAdapter = new OracleDataAdapter("SELECT ad_id, ad_text, ad_image, " +
" date_of_creation FROM PrintMedia WHERE ad_id =" + curAdID, conn);
//Instantiate a DataSet object
printmediaDataSet = new DataSet("PrintMedia");
//Step 2.//
//AddWithKey sets the Primary Key information to complete the
//schema information
printmediaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//Configures the schema to match with Data Source
printmediaAdapter.FillSchema(printmediaDataSet, SchemaType.Source, "PrintMedia");
//Step 3.//
//In this case 'OracleCommandBuilder' automatically generates
//'SelectCommand'
printmediaCmdBldr = new OracleCommandBuilder(printmediaAdapter);
//Step 4.//
//OracleDataAdapter fills the DataSet with 'PrintMedia' table data
printmediaAdapter.Fill(printmediaDataSet, "PrintMedia");
//Step 5.//
//Get the current advertisement row for updation
printmediaRow = printmediaDataSet.Tables[0].Rows.Find(curAdID);
//Start the edit operation on the current row
printmediaRow.BeginEdit();
//part of Step 7.//
//Assigning the value of advertisement text
printmediaRow["Ad_text"] = adTextBx.Text;
if (strImageName != "")
{
//Step 6.//
//providing read access to the file chosen using the 'Browse' button
FileStream fs = new FileStream(@strImageName, FileMode.Open, FileAccess.Read);
//Create a byte array of file stream length
byte[] adImageData = new byte[fs.Length];
//Read block of bytes from stream into the byte array
fs.Read(adImageData, 0, System.Convert.ToInt32(fs.Length));
//Close the File Stream
fs.Close();
//part of Step 7.//
//Assigning the byte array containing image data
printmediaRow["Ad_image"] = adImageData;
}
//Step 7.//
//Assigning date of creation for advertisement to current date
printmediaRow["date_of_creation"] = System.DateTime.Today;
//Step 8.//
//End the editing current row operation
printmediaRow.EndEdit();
//Step 9.//
//Update the database table 'PrintMedia' with new printmedia rows
printmediaAdapter.Update(printmediaDataSet, "PrintMedia");
//On successful Updation of Ad Image display the image in "exisitingImagePicBx"
//and clear the "newImagePicBx"
if (strImageName != "")
{
//Create a bitmap for selected image
Bitmap newImage = new Bitmap(strImageName);
//Fit the image to the size of picture box
existingImagePicBx.SizeMode = PictureBoxSizeMode.StretchImage;
//Show the bitmap in picture box
existingImagePicBx.Image = (Image)newImage;
//Clear contents
newImagePicBx.Image = null;
}
//Reset variables
strImageName = "";
strExistText = adTextBx.Text;
//Set the wait cursor to default cursor
this.Cursor = Cursors.Default;
}
//Display message on successful data updatation
MessageBox.Show("Data saved successfully");
}
else
{
MessageBox.Show("Select image or change text for the advertisement!");
}
}catch (Exception ex)
{ //Display error message
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
