private void getCurrencyList()
{
CdbcConnection con = UtilCommon.getConnection();
string cmdQuery = "SELECT C.CURRENCY_CODE "
+ " , C.CURRENCY_SYMBOL "
+ " FROM CURRENCIES C "
+ " ORDER BY C.CURRENCY_CODE";
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
CdbcDataAdapter dataAdapter = new CdbcDataAdapter(cmd);
try
{
dataAdapter.Fill(dsCurrencies, dsCurrencies.CURRENCIES.TableName);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message,
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
;
}
}
private void GetData()
{
var sw = new System.Diagnostics.Stopwatch();
sw.Start();
if (!isNew)
{
CdbcConnection con = UtilCommon.getConnection();
string cmdQuery = "SELECT P.PRODUCT_CODE "
+ " , P.PRODUCT_NAME "
+ " , P.PRODUCT_PRICE "
+ " , P.CURRENCY_CODE "
+ " , P.PRODUCT_SUMMARY "
+ " , P.CATEGORY_CODE "
+ " , P.PRODUCT_IMAGE "
+ " , P.RELEASE_DATE "
+ " FROM PRODUCTS P "
+ " WHERE P.PRODUCT_CODE = :PRODUCT_CODE ";
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
CdbcParameter para = new CdbcParameter(":PRODUCT_CODE", Cdbc.Common.Data.Type.CdbcOracleDbType.Varchar2);
para.Value = ProductCode;
cmd.Parameters.Add(para);
DataSetProducts dsProduct = new DataSetProducts();
CdbcDataAdapter myDa = new CdbcDataAdapter(cmd);
try
{
myDa.Fill(dsProduct, dsProduct.PRODUCTS.TableName);
if (dsProduct.Tables.Count > 0 && dsProduct.Tables[0].Rows.Count > 0)
{
DataSetProducts.PRODUCTSRow dr = (DataSetProducts.PRODUCTSRow)dsProduct.PRODUCTS.Rows[0];
txtProductName.Text = UtilCommon.NvlStr(dr.PRODUCT_NAME);
txtPrice.Text = UtilCommon.NvlStr(dr.PRODUCT_PRICE);
cbCurrency.SelectedValue = UtilCommon.NvlStr(dr.CURRENCY_CODE);
txtSummary.Text = UtilCommon.NvlStr(dr.PRODUCT_SUMMARY);
cbCategory.SelectedValue = UtilCommon.NvlStr(dr.CATEGORY_CODE);
dtReleaseDate.Value = dr.RELEASE_DATE;
pictureBox1.Image = UtilCommon.ByteArrayToImage(dr.PRODUCT_IMAGE);
}
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message,
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
;
}
}
sw.Stop();
this.Text = $"{sw.ElapsedMilliseconds} ms";
}
protected void Page_Load(object sender, EventArgs e)
{
string imageType = Page.Request.QueryString.Get("imagetype");
string code = Page.Request.QueryString.Get("code");
if (String.IsNullOrEmpty(imageType) || String.IsNullOrEmpty(code))
{
Response.End();
return;
}
string cmdQuery = "";
if (imageType == "product")
{
cmdQuery = " SELECT P.PRODUCT_IMAGE "
+ " FROM PRODUCTS P "
+ " WHERE P.PRODUCT_CODE=:PRODUCT_CODE";
}
Response.ContentType = "image";
Response.Flush();
if (!String.IsNullOrEmpty(cmdQuery))
{
CdbcConnection con = UtilCommon.getConnection();
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
CdbcParameter para = new CdbcParameter(":PRODUCT_CODE", Cdbc.Common.Data.Type.CdbcOracleDbType.Varchar2);
para.Value = code;
cmd.Parameters.Add(para);
CdbcDataAdapter dataAdapter = new CdbcDataAdapter(cmd);
DataSet dsProduct = new DataSet();
try
{
dataAdapter.Fill(dsProduct, "PRODUCTS");
if (dsProduct.Tables.Count > 0 && dsProduct.Tables[0].Rows.Count > 0)
{
DataRow dr = dsProduct.Tables[0].Rows[0];
byte[] bImage = (byte[])(dr["PRODUCT_IMAGE"]);
Response.OutputStream.Write(bImage, 0, bImage.Length);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//Please treat the exception here
}
}
Response.End();
}
private void GetProductDetail()
{
bool hasProduct = false;
String productcode = Request.QueryString["productcode"];
CdbcConnection con = UtilCommon.getConnection();
string cmdQuery = "SELECT P.PRODUCT_CODE "
+ " , P.PRODUCT_NAME "
+ " , P.PRODUCT_PRICE "
+ " , P.CURRENCY_CODE "
+ " , P.PRODUCT_SUMMARY "
+ " , P.CATEGORY_CODE "
+ " , P.PRODUCT_IMAGE "
+ " , P.RELEASE_DATE "
+ " FROM PRODUCTS P "
+ " WHERE P.PRODUCT_CODE = :PRODUCT_CODE ";
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
CdbcParameter para = new CdbcParameter(":PRODUCT_CODE", Cdbc.Common.Data.Type.CdbcOracleDbType.Varchar2);
para.Value = productcode;
cmd.Parameters.Add(para);
DataSet dsProduct = new DataSet();
CdbcDataAdapter myDa = new CdbcDataAdapter(cmd);
try
{
myDa.Fill(dsProduct, "PRODUCT");
if (dsProduct.Tables.Count > 0 && dsProduct.Tables[0].Rows.Count > 0)
{
DataRow dr = dsProduct.Tables[0].Rows[0];
lblProductName.Text = UtilCommon.NvlStr(dr["PRODUCT_NAME"]);
lblPrice.Text = UtilCommon.NvlStr(dr["CURRENCY_CODE"]) + UtilCommon.FormatNumber(dr["PRODUCT_PRICE"]);
lblDescription.Text = UtilCommon.NvlStr(dr["PRODUCT_SUMMARY"]);
lblReleaseDate.Text = UtilCommon.NvlStr(dr["RELEASE_DATE"]);
imageProduct.Src = @"./ShowImage?imagetype=product&code=" + UtilCommon.NvlStr(dr["PRODUCT_CODE"]);
hasProduct = true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//Please treat the exception here
}
if (!hasProduct)
{
Response.Redirect("./Default");
}
}
public static void DataAdapter_MultiFill_Sample()
{
Console.WriteLine(String.Format("{0} DataAdapter_MultiFill_Sample", databaseProductType.ToString()));
Console.WriteLine("Start");
Console.WriteLine("--------------------------");
Console.WriteLine("DataAdapter.MultiFill is original Method of CdbcDataAdapter.");
Console.WriteLine("It can fill multi select commands into multi datatables while access the database.");
Console.WriteLine("It is designed to reduce call count and improve performance.");
Console.WriteLine("--------------------------");
CdbcConnection con = UtilCommon.getConnection(databaseProductType);
CdbcDataAdapter adap = new CdbcDataAdapter();
string queryStr1 = "SELECT TABLE_NAME FROM ALL_TABLES";
CdbcCommand cmd = new CdbcCommand(queryStr1, con);
adap.AddMultiSelectCommand(cmd, "ALL_TABLES");
string queryStr2 = "SELECT DISTINCT OBJECT_TYPE FROM ALL_OBJECTS";
cmd = new CdbcCommand(queryStr2, con);
adap.AddMultiSelectCommand(cmd, "ALL_OBJECTS");
DataSet ds = new DataSet();
try
{
adap.MultiFill(ds);
Console.WriteLine("--------------------------");
Console.WriteLine("SQL 1 :" + queryStr1);
Console.WriteLine("SQL 2 :" + queryStr2);
Console.WriteLine("--------------------------");
for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
{
Console.WriteLine("");
Console.WriteLine(ds.Tables[iTable].TableName);
for (int iRow = 0; iRow < ds.Tables[iTable].Rows.Count; iRow++)
{
Console.WriteLine(String.Format("{0}:{1}", ds.Tables[iTable].Columns[0].ColumnName, ds.Tables[iTable].Rows[iRow][0]));
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception:");
Console.WriteLine(ex.Message);
}
Console.WriteLine("End");
}
private void GetProductList()
{
var sw = new System.Diagnostics.Stopwatch();
sw.Start();
CdbcConnection con = UtilCommon.getConnection();
string cmdQuery = "SELECT P.PRODUCT_CODE "
+ " , P.PRODUCT_NAME "
+ " , P.PRODUCT_PRICE "
+ " , P.CURRENCY_CODE "
+ " , P.PRODUCT_SUMMARY "
+ " , P.CATEGORY_CODE "
+ " , CA.CATEGORY_NAME "
+ " FROM PRODUCTS P "
+ " INNER JOIN CATEGORIES CA "
+ " ON P.CATEGORY_CODE = CA.CATEGORY_CODE "
+ " ORDER BY P.PRODUCT_CODE ";
// Create the OracleCommand
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
dataSetProducts1.PRODUCTS.Clear();
CdbcDataAdapter dataAdapter = new CdbcDataAdapter(cmd);
try
{
dataAdapter.Fill(dataSetProducts1, dataSetProducts1.PRODUCTS.TableName);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message,
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
;
}
cmd.Dispose();
sw.Stop();
this.Text = $"{sw.ElapsedMilliseconds} ms";
}
public static void DataAdapter_Fill_Sample()
{
Console.WriteLine(String.Format("{0} DataAdapter_Fill_Sample", databaseProductType.ToString()));
Console.WriteLine("Start");
CdbcConnection con = UtilCommon.getConnection(databaseProductType);
string queryStr = "SELECT :VAR1 AS COLUMN1 FROM DUAL";
CdbcCommand cmd = new CdbcCommand(queryStr, con);
string guid = Guid.NewGuid().ToString();
CdbcParameter para = new CdbcParameter(":VAR1", CdbcOracleDbType.Varchar2);
para.Direction = ParameterDirection.Input;
para.Value = guid;
cmd.Parameters.Add(para);
try
{
CdbcDataAdapter adap = new CdbcDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
Console.WriteLine("--------------------------");
Console.WriteLine("Sql : " + queryStr);
Console.WriteLine("Parameter : VAR1=" + guid);
Console.WriteLine("--------------------------");
Console.WriteLine("Expected value:" + guid);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Console.WriteLine("Actual value:" + ds.Tables[0].Rows[0][0]);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception:");
Console.WriteLine(ex.Message);
}
Console.WriteLine("--------------------------");
Console.WriteLine("End");
}
private void GetProductList()
{
CdbcConnection con = UtilCommon.getConnection();
string cmdQuery = "SELECT P.PRODUCT_CODE "
+ " , P.PRODUCT_NAME "
+ " , P.PRODUCT_PRICE "
+ " , P.CURRENCY_CODE "
+ " , P.PRODUCT_SUMMARY "
+ " , P.CATEGORY_CODE "
+ " , C.CATEGORY_NAME "
+ " FROM PRODUCTS P "
+ " INNER JOIN CATEGORIES C "
+ " ON P.CATEGORY_CODE = C.CATEGORY_CODE "
+ " ORDER BY P.PRODUCT_CODE ";
// Create the OracleCommand
CdbcCommand cmd = new CdbcCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
CdbcDataAdapter dataAdapter = new CdbcDataAdapter(cmd);
DataSet dsProduct = new DataSet();
try
{
dataAdapter.Fill(dsProduct, "PRODUCTS");
ListView1.DataSource = dsProduct;
ListView1.DataMember = "PRODUCTS";
ListView1.DataBind();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//Please treat the exception here
lblError.Text = "Error:" + ex.Message;
lblInfo.Visible = true;
lblError.Visible = true;
}
}
public static void DataAdapter_FillSchema_Sample()
{
Console.WriteLine(String.Format("{0} DataAdapter_FillSchema_Sample", databaseProductType.ToString()));
Console.WriteLine("Start");
CdbcConnection con = UtilCommon.getConnection(databaseProductType);
string queryStr = "SELECT * FROM USER_TABLES";
CdbcCommand cmd = new CdbcCommand(queryStr, con);
CdbcDataAdapter adap = new CdbcDataAdapter(cmd);
DataTable dt1 = new DataTable();
Console.WriteLine("--------------------------");
Console.WriteLine("Sql : " + queryStr);
Console.WriteLine("--------------------------");
Console.WriteLine("Result:");
try
{
DataTable dt2 = adap.FillSchema(dt1, SchemaType.Mapped);
for (int iColumn = 0; iColumn < dt1.Columns.Count; iColumn++)
{
Console.WriteLine(String.Format("ColumnName:{0} DataType:{1} MaxLength:{2}", dt1.Columns[iColumn].ColumnName, dt1.Columns[iColumn].DataType, dt1.Columns[iColumn].MaxLength));
}
for (int iColumn = 0; iColumn < dt2.Columns.Count; iColumn++)
{
Console.WriteLine(String.Format("ColumnName:{0} DataType:{1} MaxLength:{2}", dt2.Columns[iColumn].ColumnName, dt2.Columns[iColumn].DataType, dt2.Columns[iColumn].MaxLength));
}
}
catch (Exception ex)
{
Console.WriteLine("Exception:");
Console.WriteLine(ex.Message);
}
Console.WriteLine("End");
}
private void getCategoryCurrencyList()
{
CdbcConnection con = UtilCommon.getConnection();
CdbcDataAdapter dataAdapter = new CdbcDataAdapter();
//CATEGORIES
string cmdQuery = "SELECT C.CATEGORY_CODE "
+ " , C.CATEGORY_NAME "
+ " FROM CATEGORIES C "
+ " ORDER BY C.CATEGORY_CODE";
CdbcCommand cmd = new CdbcCommand(cmdQuery, con);
dataAdapter.AddMultiSelectCommand(cmd, dsCategoriesCurrencies.CATEGORIES.TableName);
//CURRENCIES
cmdQuery = "SELECT C.CURRENCY_CODE "
+ " , C.CURRENCY_SYMBOL "
+ " FROM CURRENCIES C "
+ " ORDER BY C.CURRENCY_CODE";
cmd = new CdbcCommand(cmdQuery, con);
dataAdapter.AddMultiSelectCommand(cmd, dsCategoriesCurrencies.CURRENCIES.TableName);
try
{
dsCategoriesCurrencies = new DataSetProducts();
int[] fillResult = dataAdapter.MultiFill(dsCategoriesCurrencies);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message,
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
;
}
}
public static void DataAdapter_Update_Sample()
{
Console.WriteLine(String.Format("{0} DataAdapter_Update_Sample", databaseProductType.ToString()));
Console.WriteLine("Start");
Console.WriteLine("This sample will show you how to use CdbcDataAdapter.update insert ,update and delete data.");
string guid = Guid.NewGuid().ToString();
CdbcConnection con = UtilCommon.getConnection(databaseProductType);
try
{
//Sample 1 :Add new row (insert)
Console.WriteLine("--------------------------");
Console.WriteLine("Sample 1 : Insert a record into table CODE_SAMPLE");
Console.WriteLine("--------------------------");
string queryStrInsert = @"SELECT VAR1,NUM1,DATE1,CLOB1,BLOB1 FROM CODE_SAMPLE WHERE VAR1=:VAR1";
CdbcCommand cmdInsert = new CdbcCommand(queryStrInsert, con);
CdbcDataAdapter adapInsert = new CdbcDataAdapter(cmdInsert);
CdbcCommandBuilder cb = new CdbcCommandBuilder(adapInsert);
DataTable dt1 = new DataTable();
adapInsert.FillSchema(dt1, SchemaType.Mapped);
DataRow dr1 = dt1.NewRow();
dr1["VAR1"] = guid;
dr1["NUM1"] = DateTime.Now.Second;
dr1["DATE1"] = DateTime.Now;
dr1["CLOB1"] = "testclob";
// dr1["BLOB1"] = System.IO.File.ReadAllBytes(@"your file path");
dt1.Rows.Add(dr1);
int i = adapInsert.Update(dt1);
Console.WriteLine(String.Format("{0} Line(s) inserted.", i));
//Sample 2 :Edit row data (update)
Console.WriteLine("--------------------------");
Console.WriteLine("Sample 2 : update the record we inserted before.");
Console.WriteLine("--------------------------");
string queryStrUpdate = @"SELECT VAR1,NUM1,DATE1,CLOB1,BLOB1 FROM CODE_SAMPLE WHERE VAR1=:VAR1";
CdbcCommand cmdUpdate = new CdbcCommand(queryStrUpdate, con);
CdbcParameter para = new CdbcParameter(":VAR1", CdbcOracleDbType.Varchar2);
para.Value = guid;
cmdUpdate.Parameters.Add(para);
CdbcDataAdapter adapUpdate = new CdbcDataAdapter(cmdUpdate);
CdbcCommandBuilder cbUpdate = new CdbcCommandBuilder(adapUpdate);
DataSet dsUpdate = new DataSet();
adapUpdate.Fill(dsUpdate);
dsUpdate.Tables[0].Rows[0]["NUM1"] = DateTime.Now.Second;
dsUpdate.Tables[0].Rows[0]["DATE1"] = DateTime.Now;
i = adapUpdate.Update(dsUpdate, dsUpdate.Tables[0].TableName);
Console.WriteLine(String.Format("{0} Line(s) updated.", i));
//Sample 3 :Delete row (delete)
Console.WriteLine("--------------------------");
Console.WriteLine("Sample 3 : Delete the record we inserted before.");
Console.WriteLine("--------------------------");
DataSet dsDelete = new DataSet();
adapUpdate.Fill(dsDelete);
dsDelete.Tables[0].Rows[0].Delete();
i = adapUpdate.Update(dsDelete.Tables[0]);
//The following code will have same result.
//i = adapUpdate.Update(dsDelete, dsDelete.Tables[0].TableName);
Console.WriteLine(String.Format("{0} Line(s) Deleted.", i));
}
catch (Exception ex)
{
Console.WriteLine("Exception:");
Console.WriteLine(ex.Message);
}
Console.WriteLine("end");
}
