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"); }