protected void Page_Load(object sender, EventArgs e) { OleDbDataReader greet = (OleDbDataReader)Session["User"]; lblGreet.Text = "Welcome " + greet["FirstName"].ToString() + " " + greet["LastName"].ToString(); OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\scami\\source\\repos\\PjFreindBookSeungyeonKim\\PjFreindBookSeungyeonKim\\App_Data\\dbFriendBook.mdb"); myCon.Open(); string sql = "SELECT Messages.RefMessage, Messages.Message, Messages.Sender, Messages.Receiver, Messages.Title, Members.FirstName + ' ' + Members.LastName AS Fullname FROM Messages, Members WHERE Messages.Sender = Members.RefMember AND Messages.Receiver = " + greet["RefMember"]; OleDbCommand myCmd = new OleDbCommand(sql, myCon); OleDbDataReader myRd = myCmd.ExecuteReader(); //GridView1.DataSource = myRd; //GridView1.DataBind(); while (myRd.Read()) { ltlMessage.Text = "<table><tr><td>Messages</td><td>From</td><td>Actions</td></tr><tr><td>" + myRd["Title"].ToString() + "</td><td>" + myRd["Fullname"].ToString() + "</td><td><a href=" + "WebRead.aspx?refM=" + myRd["RefMessage"].ToString() + ">Read</a><a href=" + "WebDelete.aspx?refM=" + myRd["RefMessage"].ToString() + ">Delete</a></td></tr></table> "; myRd.NextResult(); } //if (!myRd.Read()) //{ // ltlMessage.Text = "No messages"; //} myRd.Close(); // ltlMessage.Text = "<table>< tr >< td >< strong > Messages </ strong ></ td >< td >< strong > From </ strong ></ td >< td >< strong > Actions </ strong ></ td ></ tr ><tr>< td >" + myRd["Title"].ToString() + "</ td >< td >" + myRd["Fullname"].ToString() + "</ td >< td > < a href = "read.aspx? refM =<%= myRec.Fields["RefMessage"].Value %> " > Read </ a > //< a href = "delete.aspx?refM=<%= myRec.Fields["RefMessage"].Value %>" > Delete </ a > </ td ></ tr ></ table > "; }
public static string toDelimitedString(this OleDbDataReader Reader, string ColDelimitator = "|", string RowDelimitator = "§", string TableDelimitator = "♠") { string DelimitedString = ""; try { do { List <string> cols = Enumerable.Range(0, Reader.FieldCount).Select(Reader.GetName).ToList(); while (Reader.Read()) { foreach (var col in cols) { DelimitedString += (Reader[col] + ColDelimitator); } DelimitedString += RowDelimitator; } DelimitedString += TableDelimitator; } while (Reader.NextResult()); } catch (Exception ex) { DelimitedString += ex.Message; } return(DelimitedString); }
/// <summary> /// 执行一条查询语句,并以实体对象集合形式返回查询结果集。 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="sqlCmd">SQL命令</param> /// <param name="parameters">SQL参数对应值的集合,如果SQL语句字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="commandType">CommandType</param> /// <param name="drToEntityAction">读出DataReader数据到DTO</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>实体对象集合</returns> protected virtual List <TEntity> GetEntities <TEntity>(string sqlCmd, OleDbParameter[] parameters, CommandType commandType, Func <OleDbDataReader, MetaDataTable, string[], TEntity> drToEntityAction, params string[] columnNames) { List <TEntity> entities = new List <TEntity>(); var metaDataTable = new MetaDataTable(typeof(TEntity), this._tableName); using (OleDbDataReader dr = OleDbHelper.ExecuteReader(this._connectionString, commandType, sqlCmd, parameters)) { try { do { while (dr.Read()) { entities.Add(drToEntityAction(dr, metaDataTable, columnNames)); } } while (dr.NextResult()); } catch (Exception ex) { string message = string.Format("[SQL]:{0},[Exception]:{1}", sqlCmd, ex.ToString()); System.Diagnostics.EventLog.WriteEntry("LightFramework.Data.OleDb", message); } } return(entities); }
void ReadData() { const int NAME_INDEX = 2; OleDbConnection connection = CreateConnection(); OleDbCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO Peoples (Фамилия, Имя, ДатаРождения, Пол) " + " Values ('Сергеева', 'Валентина', '05.10.1971', 'Ж'); " + "SELECT * FROM Peoples WHERE Фамилия='Смирнов'; " + "SELECT * FROM Peoples WHERE Фамилия='Иванов'"; OleDbDataReader reader = command.ExecuteReader(); do { while (reader.Read()) { ListViewItem item = listView1.Items.Add(reader["Фамилия"].ToString().TrimEnd()); item.SubItems.Add(reader.GetValue(NAME_INDEX).ToString().TrimEnd()); item.SubItems.Add(reader.GetDateTime(3).ToLongDateString()); item.SubItems.Add(reader.GetValue(4).ToString()); } } while (reader.NextResult()); connection.Close(); }
/// <summary> /// Converts DataReader to DataSet. /// </summary> /// <param name="myReader"></param> /// <param name="tableName"></param> /// <returns></returns> protected DataSet ConvertToDataSet(OleDbDataReader dataReader, string tableName) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = dataReader.GetSchemaTable(); DataTable dataTable = new DataTable(tableName); if (schemaTable != null) { // A query returning records was executed for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow dataRow = schemaTable.Rows[i]; // Create a column name that is unique in the data table string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>"; // Add the column definition to the data table DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]); dataTable.Columns.Add(column); } //Add the data table to the dataset. dataSet.Tables.Add(dataTable); // Fill the data table. while (dataReader.Read()) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < dataReader.FieldCount; i++) { dataRow[i] = dataReader.GetValue(i); } dataTable.Rows.Add(dataRow); } } else { // No records were returned. DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add(dataTable); DataRow dataRow = dataTable.NewRow(); dataRow[0] = dataReader.RecordsAffected; dataTable.Rows.Add(dataRow); } }while (dataReader.NextResult()); return(dataSet); }
public bool NextResult() { if (SDR != null) { return(SDR.NextResult()); } else { return(ODR.NextResult()); } }
private void btnsave_Click(object sender, EventArgs e) { Boolean inc = false; if (txtworkersid.Text == "") { DialogResult sav = MessageBox.Show("The Worker ID is Empty", "Blank Record", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (txtname.Text == "") { DialogResult sav = MessageBox.Show("The Workers Name is Empty", "Blank Record", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (txtjobtitle.Text == "") { DialogResult sav = MessageBox.Show("The Job Title is Empty", "Balnk record", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { //string cmdstr = "SELECT Workers_ID FROM tblWorkers WHERE (Workers_ID='" + txtworkersid.Text + "')"; //cmd = new OleDbCommand(cmdstr, conn); cmd=new OleDbCommand("SELECT Workers_ID FROM tblWorkers WHERE (Workers_ID='" + txtname.Text + "')",conn); cmd.ExecuteNonQuery(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { if (txtworkersid.Text == dr.GetValue(0).ToString()) { DialogResult sav = MessageBox.Show("The Record is Duplicate", "Duplicate Record", MessageBoxButtons.OK, MessageBoxIcon.Error); inc = true; break; } else { dr.NextResult(); } } if (inc == false) { string sql = "INSERT INTO tblWorkers(Workers_ID,name,job_title) VALUES ('" + txtworkersid.Text + "','" + txtname.Text + "','" + txtjobtitle.Text + "')"; Execute(sql); DialogResult save = MessageBox.Show("Workers Record is saved Sucessfully."); clrdata(); } } }
public List <string> ReadData(string nameOfTable, string colName) { List <string> data = new List <string>(); OleDbCommand cmd = new OleDbCommand("Select [" + colName + "] From [" + nameOfTable + "]", myOleDbConnection); myOleDbConnection.Open(); OleDbDataReader dr = cmd.ExecuteReader(); while (dr.HasRows) { while (dr.HasRows && dr.Read()) { data.Add(dr.GetValue(0).ToString()); } dr.NextResult(); } myOleDbConnection.Close(); return(data); }
/// <summary> /// Fill /// </summary> /// <param name="ds"></param> public void Fill(DataSet ds) { if (ds == null) { ds = new DataSet(); } using (OleDbDataReader dr = command.ExecuteReader()) { do { var dt = new DataTable(); var columns = dt.Columns; var rows = dt.Rows; for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.Contains(name)) { columns.Add(new DataColumn(name, dr.GetFieldType(i))); } else { columns.Add(new DataColumn(name + i, dr.GetFieldType(i))); } } while (dr.Read()) { DataRow daRow = dt.NewRow(); for (int i = 0; i < columns.Count; i++) { daRow[columns[i].ColumnName] = dr.GetValue(i); } dt.Rows.Add(daRow); } dt.AcceptChanges(); ds.Tables.Add(dt); } while (dr.NextResult()); } }
/// <summary> /// /// </summary> public void Example5() { // Create Connection OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = "Provider=SQLOLEDB.1;" + "Server=localhost;" + "Database=Northwind;" + "Integrated Security=SSPI;"; // Set Events conn.InfoMessage += new OleDbInfoMessageEventHandler(this.ConnInfo); conn.StateChange += new StateChangeEventHandler(this.ConnChange); // Open Database conn.Open(); // Create the Command Object OleDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * from [Order Details];" + "SELECT * from orders;"; // Get the Reader Object by Executing the Query OleDbDataReader rdr = cmd.ExecuteReader(); // Dump All Results do { Console.WriteLine("Result:"); // Iterate through all the records of the result while (rdr.Read()) { Console.WriteLine(rdr[0]); } }while (rdr.NextResult()); }
private void btnVisualize_Click(object sender, EventArgs e) { chartPredict.Series["Total Sales per Month"].Points.Clear(); chartPredict.Titles.Clear(); DateTime dt = DateTime.Now; int CurrYear = dt.Year; int NextMonth = dt.AddMonths(+1).Month; chartPredict.ChartAreas[0].AxisX.Minimum = 1; chartPredict.ChartAreas[0].AxisX.Maximum = 12; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = SRePS.Properties.Settings.Default.SRePS_DatabaseConnectionString; conn.Open(); if (cmbGroup.SelectedItem == null || cmbSubGroup.SelectedItem == null) { MessageBox.Show("Please select a category or sub-category.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { if (rdSales.Checked) { chartPredict.Titles.Add("Monthly Total Sales of " + cmbSubGroup.SelectedItem.ToString()); chartPredict.ChartAreas[0].AxisX.Title = "Month"; chartPredict.ChartAreas[0].AxisY.Title = "Sales (RM)"; chartPredict.Series["Total Sales per Month"].ToolTip = "Month: #VALX\nSales: #VALY"; string query = "SELECT MONTH(Sales.S_Date),SUM(Product.P_Price*[Order].S_Quantity) As Total FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No)" + "INNER JOIN Product ON [Order].P_ID = Product.P_ID)" + "WHERE (YEAR(Sales.S_Date) = @curYear) AND (Product.P_SubGroup = @pGroup)" + "GROUP BY MONTH(Sales.S_Date),Product.P_SubGroup"; OleDbCommand cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@curYear", CurrYear); cmd.Parameters.AddWithValue("@pGroup", cmbSubGroup.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); for (int x = 1; x < 13; x++) { chartPredict.Series["Total Sales per Month"].Points.AddXY(x, 0); } if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { while (reader.HasRows) { while (reader.Read()) { chartPredict.Visible = true; int y = Convert.ToInt32(reader.GetValue(0).ToString()) - 1; chartPredict.Series["Total Sales per Month"].Points.ElementAt(y).SetValueY(reader.GetValue(1)); chartPredict.Refresh(); chartPredict.ChartAreas[0].RecalculateAxesScale(); } reader.NextResult(); } } conn.Close(); } else { chartPredict.Titles.Add("Monthly Total Quantity Sold for " + cmbSubGroup.SelectedItem.ToString()); chartPredict.ChartAreas[0].AxisX.Title = "Month"; chartPredict.ChartAreas[0].AxisY.Title = "Quantity"; chartPredict.Series["Total Sales per Month"].ToolTip = "Month: #VALX\nQuantity: #VALY"; string query = "SELECT MONTH(Sales.S_Date),SUM([Order].S_Quantity) As Total FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No)" + "INNER JOIN Product ON [Order].P_ID = Product.P_ID)" + "WHERE (YEAR(Sales.S_Date) = @curYear) AND (Product.P_SubGroup = @pGroup)" + "GROUP BY MONTH(Sales.S_Date),Product.P_SubGroup"; OleDbCommand cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@curYear", CurrYear); cmd.Parameters.AddWithValue("@pGroup", cmbSubGroup.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); for (int x = 1; x < 13; x++) { chartPredict.Series["Total Sales per Month"].Points.AddXY(x, 0); } if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { while (reader.HasRows) { while (reader.Read()) { chartPredict.Visible = true; int y = Convert.ToInt32(reader.GetValue(0).ToString()) - 1; chartPredict.Series["Total Sales per Month"].Points.ElementAt(y).SetValueY(reader.GetValue(1)); chartPredict.Refresh(); chartPredict.ChartAreas[0].RecalculateAxesScale(); } reader.NextResult(); } } conn.Close(); } } }
static void Main(string[] args) { OleDbConnection conn = null; OleDbCommand cmd = null; OleDbDataReader rdr = null; OleDbDataAdapter da = null; DataTable tmpDataTable = null; string tmpString; int tmpInt; try { string //ConnectionString = "Provider=sqloledb;Server=.;Initial Catalog=testdb;User Id=sa;Password=123"; ConnectionString = "Provider=SQLNCLI11;Server=.;Database=testdb;Uid=sa;Pwd=123"; //ConnectionString = "Provider=sqloledb;Server=NOZHENKO-S8\\SQLEXPRESS;Database=testdb;Trusted_Connection=yes;"; //ConnectionString = "Provider=SQLNCLI10;Server=NOZHENKO-S8\\SQLEXPRESS;Database=testdb;Trusted_Connection=yes;"; //ConnectionString = "Provider=sqloledb;Data Source=fobos_web;Initial Catalog=CMS_Connect;User Id=sa;Password=developer"; //ConnectionString = "Provider=sqloledb;Data Source=10.135.197.86,2057;Initial Catalog=CMS_Connect;User Id=sa;Password=developer"; conn = new OleDbConnection(ConnectionString); conn.Open(); #if TEST_PARAMETERIZED_STATEMENTS if (cmd == null) { cmd = conn.CreateCommand(); } else { cmd.Parameters.Clear(); } cmd.CommandType = CommandType.Text; cmd.CommandText = @"select id from Staff where name = ?"; cmd.Parameters.Add("@name", OleDbType.VarChar).Value = "Вашингтон Джордж"; if (da == null) { da = new OleDbDataAdapter(); } da.SelectCommand = cmd; if (tmpDataTable == null) { tmpDataTable = new DataTable(); } else { tmpDataTable.Reset(); } da.Fill(tmpDataTable); tmpInt = tmpDataTable.Rows.Count; #endif #if TEST_TYPES if (cmd == null) { cmd = conn.CreateCommand(); } else { cmd.Parameters.Clear(); } cmd.CommandType = CommandType.Text; cmd.CommandText = "select top 10 * from TestTable4Types"; rdr = cmd.ExecuteReader(); do { if (rdr.HasRows) { tmpInt = rdr.GetOrdinal("FVarChar"); while (rdr.Read()) { tmpString = !rdr.IsDBNull(tmpInt) ? rdr.GetString(tmpInt) : string.Empty; } } } while (rdr.NextResult()); rdr.Close(); if (da == null) { da = new OleDbDataAdapter(); } da.SelectCommand = cmd; if (tmpDataTable == null) { tmpDataTable = new DataTable(); } else { tmpDataTable.Reset(); } da.Fill(tmpDataTable); #endif } catch (Exception eException) { Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + (eException.InnerException != null && !string.IsNullOrEmpty(eException.InnerException.Message) ? "InnerException.Message" + eException.InnerException.Message + Environment.NewLine : string.Empty) + "StackTrace:" + Environment.NewLine + eException.StackTrace); } finally { if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } } }
/// <summary> /// Ejecuta el sl_help de la tabla que se envia /// </summary> /// <param name="sNombreTabla"> Nombre de la tabla a consultar</param> /// <param name="sConexionString"> cadena de conexión que se obtiene de la clase ConfiguracionDA</param> /// <returns>En lstCatalogos[].DESCRIPCION se regresa el nombre de las columnas de la tabla. En psIdentityTabla Se regresa el Identity de la tabla ///</returns> public RespuestaComunBE GetDefinicionTabla(string sNombreTabla, string sConexionString) { RespuestaComunBE RespuestaComun = new RespuestaComunBE(); OleDbCommand Comando = new OleDbCommand(); OleDbConnection Conexion = new OleDbConnection(); OleDbDataReader Lector = null; string sComando = string.Empty; StringBuilder sMensajeError = new StringBuilder(); string sResultado = string.Empty; RespuestaComun.lstCatalogo = new List <CatalogosBE>(); RespuestaComun.itemError = new ErrorBE(); RespuestaComun.itemError.psMensaje = new StringBuilder(string.Empty); try { Conexion.ConnectionString = sConexionString; Conexion.Open(); Comando.Connection = Conexion; sComando = "spGetDefinicionTabla"; Comando.CommandText = sComando; Comando.CommandType = CommandType.StoredProcedure; Comando.CommandTimeout = 0; Comando.Parameters.Clear(); Comando.Parameters.Add(new OleDbParameter("NombreTabla", sNombreTabla)).Direction = ParameterDirection.Input; //[15102015][falta agregar la instruccion que obtiene el nombre del metodo]RespuestaComun.itemError.psMensaje.Append("[spGetConfigApp]"); RespuestaComun.itemError.psMensaje.Append("[spGetDefinicionTabla]"); Lector = Comando.ExecuteReader(); if (Lector.HasRows) { Lector.NextResult(); while (Lector.Read()) { CatalogosBE itemLector = new CatalogosBE(); itemLector.DESCRIPCION = Lector["Column_name"].ToString(); RespuestaComun.lstCatalogo.Add(itemLector); } Lector.NextResult(); while (Lector.Read()) { //Se encuentra el nombre de la columna IDentity RespuestaComun.psIdentityTabla = Lector["Identity"].ToString(); RespuestaComun.psDescripcionTabla = "Descripcion"; } } RespuestaComun.itemError.pbFlag = true; } catch (Exception Ex) { //Generar una deccion para crear Log de errores RespuestaComun.itemError.psMensaje.Append("["); RespuestaComun.itemError.psMensaje.Append(Ex.Message); RespuestaComun.itemError.psMensaje.Append("]"); RespuestaComun.itemError.pbFlag = false; } finally { Lector.Close(); Lector.Dispose(); Lector = null; Comando.Dispose(); Comando = null; Conexion.Close(); Conexion = null; } return(RespuestaComun); }
public void TestMultipleResultSetsWithSQLText() { if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle) { this.Log("Multiple result sets by sql text is not tested in oracle."); return; } if (ConnectedDataProvider.GetDbType() == DataBaseServer.DB2) { this.Log("Multiple result sets using compound statement not supported at DB2."); return; } bool NextResultExists = false; OleDbDataReader rdr = null; OleDbCommand cmd; int TblResult0 = -1; int TblResult1 = -1; int TblResult2 = -1; try { BeginCase("Setup: Get expected results."); //get excpected results GetExcpectedResults(ref TblResult0, ref TblResult1, ref TblResult2); this.Pass("Setup: Get expected results ended."); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } string cmdTxt = BuildCommandText(); cmd = new OleDbCommand(cmdTxt, con); cmd.CommandType = CommandType.Text; rdr = cmd.ExecuteReader(); // -------------- ResultSet 1 ------------ try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 exists"); Compare(rdr != null, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } int i = 1; try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Data"); while (rdr.Read()) { i++; } Compare(i, TblResult0); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } // -------------- ResultSet 2 ------------ try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Data"); i = 1; while (rdr.Read()) { i++; } Compare(i, TblResult1); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CATEGORYID"); } catch (Exception ex) { exp = ex; } // -------------- ResultSet 3 ------------ finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Data"); i = 1; while (rdr.Read()) { i++; } Compare(i, TblResult2); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "REGIONID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not exist."); NextResultExists = rdr.NextResult(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not contain data."); NextResultExists = rdr.Read(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } if (!rdr.IsClosed) { rdr.Close(); } }
private void btnVisualize_Click(object sender, EventArgs e) { //clear all series foreach (var s in TopSalesChart.Series) { s.Points.Clear(); } System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = SRePS.Properties.Settings.Default.SRePS_DatabaseConnectionString; //startDate string pickedSMonthYear = Program.frmSalesM.dtpickerStart.Text; string[] splitSMonthYear = pickedSMonthYear.Split(' '); string pickedSMonth = splitSMonthYear[0]; string pickedSYear = splitSMonthYear[1]; string pickedSDate = "1/" + Program.frmSalesM.monthDictionary(pickedSMonth) + "/" + pickedSYear; //endDate string pickedEMonthYear = Program.frmSalesM.dtpickerEnd.Text; string[] splitEMonthYear = pickedEMonthYear.Split(' '); string pickedEMonth = splitEMonthYear[0]; string pickedEYear = splitEMonthYear[1]; int endofmonth = DateTime.DaysInMonth(Convert.ToInt32(pickedEYear), Convert.ToInt32(Program.frmSalesM.monthDictionary(pickedEMonth))); string pickedEDate = endofmonth.ToString() + "/" + pickedEMonth + "/" + pickedEYear; con.Open(); if (GroupComboBox.SelectedItem == null) { MessageBox.Show("Please select Product Group.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { try { string query = "SELECT YEAR(Sales.S_Date), MONTH(Sales.S_Date), Product.P_SubGroup As SubGroup, SUM(Order.S_Quantity) AS Quantity " + "FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No) INNER JOIN Product ON [Order].P_ID = Product.P_ID) " + "WHERE (Sales.S_Date >= @startdate) AND (Sales.S_Date <= @endDate) AND (((Product.P_Group) = @Group)) " + "GROUP BY YEAR(Sales.S_Date), MONTH(Sales.S_Date), Product.P_SubGroup "; OleDbCommand cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@startdate", pickedSDate); cmd.Parameters.AddWithValue("@endDate", pickedEDate); cmd.Parameters.AddWithValue("@Group", GroupComboBox.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { string yearmonth = String.Empty; //combine value of year and month together as one string while (reader.HasRows) { while (reader.Read()) { TopSalesChart.Visible = true; yearmonth = reader.GetValue(0) + "-" + reader.GetValue(1); if (TopSalesChart.Series.FindByName(reader.GetValue(2).ToString()) == null) //only create new series when it is not exists { TopSalesChart.Series.Add(reader.GetValue(2).ToString()); } TopSalesChart.Series[reader.GetValue(2).ToString()].Points.AddXY(yearmonth, reader.GetValue(3)); //show tooltip when user hover on the series(bar) TopSalesChart.Series[reader.GetValue(2).ToString()].ToolTip = "SubGroup: " + reader.GetValue(2).ToString() + "\n" + "Quantity: " + "#VALY"; } reader.NextResult(); } TopSalesChart.AlignDataPointsByAxisLabel(); } } catch (Exception) { throw; } con.Close(); } }
private void btnVisualize_Click(object sender, EventArgs e) { TopSalesChart.Series["P_SubGroup"].Points.Clear(); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = SRePS.Properties.Settings.Default.SRePS_DatabaseConnectionString; //startDate string pickedSMonthYear = Program.frmSalesM.dtpickerStart.Text; string[] splitSMonthYear = pickedSMonthYear.Split(' '); string pickedSMonth = splitSMonthYear[0]; string pickedSYear = splitSMonthYear[1]; string pickedSDate = "1/" + Program.frmSalesM.monthDictionary(pickedSMonth) + "/" + pickedSYear; //endDate string pickedEMonthYear = Program.frmSalesM.dtpickerEnd.Text; string[] splitEMonthYear = pickedEMonthYear.Split(' '); string pickedEMonth = splitEMonthYear[0]; string pickedEYear = splitEMonthYear[1]; int endofmonth = DateTime.DaysInMonth(Convert.ToInt32(pickedEYear), Convert.ToInt32(Program.frmSalesM.monthDictionary(pickedEMonth))); string pickedEDate = endofmonth.ToString() + "/" + pickedEMonth + "/" + pickedEYear; con.Open(); if (pickedSMonthYear == pickedEMonthYear) { if (GroupComboBox.SelectedItem == null) { MessageBox.Show("Please select Product Group.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { try { string query = "SELECT Product.P_SubGroup As SubGroup, SUM(Order.S_Quantity) AS Quantity " + "FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No) INNER JOIN Product ON [Order].P_ID = Product.P_ID) " + "WHERE (Sales.S_Date >= @startdate) AND (Sales.S_Date <= @endDate) AND (((Product.P_Group) = @Group)) " + "GROUP BY YEAR(Sales.S_Date), MONTH(Sales.S_Date), Product.P_SubGroup "; OleDbCommand cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@startdate", pickedSDate); cmd.Parameters.AddWithValue("@endDate", pickedEDate); cmd.Parameters.AddWithValue("@Group", GroupComboBox.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { while (reader.HasRows) { while (reader.Read()) { TopSalesChart.Visible = true; TopSalesChart.Series["P_SubGroup"].Points.AddXY(reader.GetValue(0), reader.GetValue(1)); } reader.NextResult(); } } } catch (Exception) { throw; } con.Close(); } } else { if (Program.frmSalesM.salesReportMDataGridView.Visible) { if (Program.isOpened(Program.frmVRev) == true) { Program.frmVRev.Focus(); } else { Program.frmVRev = new frmVRevenue(); Program.frmVRev.Show(); } } } }
public override DataSet ExecuteProcedureWithReturn(string commandText, ref ArrayList parameters) { OpenConnection(); using (OleDbCommand command = (OleDbCommand)this.Connection.CreateCommand()) { command.CommandText = commandText; command.CommandType = CommandType.StoredProcedure; object[] parameterValues = new object[parameters.Count]; CommandParam(command, parameters, ref parameterValues); DataSet dataSet = new DataSet(); OleDbDataReader reader = null; DateTime dtStart = DateTime.Now; try { reader = command.ExecuteReader(CommandBehavior.SequentialAccess); for (int i = 0; i < parameters.Count; i++) { if (((SQLParameter)parameters[i]).Direction != DirectionType.Input) { ((SQLParameter)parameters[i]).Value = ((OleDbParameter)parameterValues[i]).Value; } } do { DataTable dt = new DataTable(); for (int i = 0; i < reader.FieldCount; i++) { if (!dt.Columns.Contains(reader.GetName(i))) { DataColumn dc = new DataColumn(reader.GetName(i), reader.GetFieldType(i)); dt.Columns.Add(dc); } } while (reader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i < reader.FieldCount; i++) { object objVal = reader[i]; dr[reader.GetName(i)] = objVal; } dt.Rows.Add(dr); } dataSet.Tables.Add(dt); } while (reader.NextResult()); reader.Close(); } catch (Exception e) { if (reader != null) { reader.Close(); } throw new Exception(e.Message); } finally { if (this.Transaction == null && AutoCloseConnection == true) { CloseConnection(); } } return(dataSet); } }
//--------------------------------------------------------------------------- private void ButtonDownloadImageFromBLOB_Click(object sender, EventArgs e) { OleDbConnection connection = null; OleDbDataReader rdr = null; string MethodName = "ButtonDownloadImageFromBLOB_Click"; try { try { connection = new OleDbConnection(ConnectionString); connection.Open(); OleDbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from TestTypes"; rdr = cmd.ExecuteReader(); do { if (rdr.HasRows) { while (rdr.Read()) { Response.Clear(); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment; filename=FromBlob.bmp"); //Response.Flush(); Response.BinaryWrite((byte[])rdr["FImage"]); Response.End(); // http://www.sql.ru/forum/actualthread.aspx?bid=19&tid=610998 } } }while(rdr.NextResult()); rdr.Close(); } catch (OleDbException eException) { throw(new Exception(eException.GetType().FullName + Environment.NewLine + "ErrorCode: " + eException.ErrorCode.ToString() + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace: " + Environment.NewLine + eException.StackTrace)); } catch (Exception eException) { throw(new Exception(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace)); } } finally { if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (connection != null && connection.State == ConnectionState.Open) { Log.Log.WriteToLog("AnyTestSmlForm::" + MethodName + "() OleDbConnection.Close() started...", true); connection.Close(); Log.Log.WriteToLog("AnyTestSmlForm::" + MethodName + "() OleDbConnection.Close() finished", true); } } }
static int Main(string[] args) { int Result = -1; StreamWriter fstr_out = null; string tmpString = "log.log", TableName; #if TEST_DB_BY_ODBC OdbcConnection odbc_conn = null; OdbcCommand odbc_cmd = null; OdbcDataReader odbc_rdr = null; OdbcDataAdapter odbc_da = null; #endif OleDbConnection conn = null; OleDbCommand cmd = null; OleDbDataReader rdr = null; OleDbDataAdapter da = null; DataTable tmpDataTable; int tmpInt; object[] tmpObjects; FileStream fs; byte[] Blob; try { try { fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251)); fstr_out.AutoFlush = true; string PathToDb = "E:\\Soft.src\\CBuilder\\Tests\\Paradox\\Test.#1\\db", CommonDbTableName = "Common", CommonDbTableSQLCreate = @" create table " + CommonDbTableName + @"( FInt integer, FChar char(254) )"; #if TEST_DB_BY_ODBC if (!PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString())) { PathToDb += Path.DirectorySeparatorChar; } tmpString = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=" + PathToDb + ";Dbq=" + PathToDb + ";CollatingSequence=ASCII"; odbc_conn = new OdbcConnection(tmpString); odbc_conn.Open(); fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString); fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString()); fstr_out.WriteLine("Database: " + odbc_conn.Database); fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource); fstr_out.WriteLine("Driver: " + odbc_conn.Driver); fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion); fstr_out.WriteLine("State: " + odbc_conn.State.ToString()); fstr_out.WriteLine(); tmpString = PathToDb + CommonDbTableName + ".db"; if (File.Exists(tmpString)) { File.Delete(tmpString); } odbc_cmd = odbc_conn.CreateCommand(); odbc_cmd.CommandType = CommandType.Text; odbc_cmd.CommandText = CommonDbTableSQLCreate; odbc_cmd.ExecuteNonQuery(); odbc_cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')"; odbc_cmd.ExecuteNonQuery(); #if TEST_BLOB if (odbc_cmd == null) { odbc_cmd = odbc_conn.CreateCommand(); } odbc_cmd.CommandType = CommandType.Text; odbc_cmd.CommandText = "select * from TestTypes"; odbc_cmd.Parameters.Clear(); odbc_rdr = odbc_cmd.ExecuteReader(); do { if (odbc_rdr.HasRows) { for (int i = 0; i < odbc_rdr.FieldCount; ++i) { fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\""); } tmpInt = odbc_rdr.GetOrdinal("FGraphic"); while (odbc_rdr.Read()) { tmpString = "FromBlob.bmp"; if (File.Exists(tmpString)) { File.Delete(tmpString); } Blob = (byte[])odbc_rdr["FGraphic"]; fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); tmpString = "FromBlob_1.bmp"; if (File.Exists(tmpString)) { File.Delete(tmpString); } Blob = new byte[odbc_rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)]; rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length); fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); } } }while(rdr.NextResult()); odbc_rdr.Close(); #endif odbc_conn.Close(); #endif if (PathToDb.EndsWith(Path.DirectorySeparatorChar.ToString())) { PathToDb = PathToDb.Remove(PathToDb.Length - 1, 1); } tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDb + ";Extended Properties=Paradox 5.x"; conn = new OleDbConnection(tmpString); conn.Open(); fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString); fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString()); fstr_out.WriteLine("Database: " + conn.Database); fstr_out.WriteLine("DataSource: " + conn.DataSource); fstr_out.WriteLine("Provider: " + conn.Provider); fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion); fstr_out.WriteLine("State: " + conn.State.ToString()); fstr_out.WriteLine(); tmpString = PathToDb + Path.DirectorySeparatorChar + CommonDbTableName + ".db"; if (File.Exists(tmpString)) { File.Delete(tmpString); } cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = CommonDbTableSQLCreate; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into " + CommonDbTableName + " values (1,'FChar (‘„ар)')"; cmd.ExecuteNonQuery(); #if TEST_BLOB if (cmd == null) { cmd = conn.CreateCommand(); } cmd.CommandType = CommandType.Text; TableName = "TestTypes"; tmpObjects = new object[] { null, null, TableName, null }; fstr_out.WriteLine("OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns)"); tmpDataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, tmpObjects); fstr_out.WriteLine("Columns in " + TableName + " table:"); foreach (DataRow row in tmpDataTable.Rows) { fstr_out.WriteLine("\t" + row["TABLE_CATALOG"] + " " + row["TABLE_NAME"] + " " + row["COLUMN_NAME"].ToString() + " " + row["DATA_TYPE"] + " " + row["TABLE_SCHEMA"]); } fstr_out.WriteLine(); #if TEST_BLOB_SAVE cmd.CommandText = "update TestTypes set FGraphic = ?"; cmd.Parameters.Clear(); cmd.Parameters.Add("FGraphic", OleDbType.LongVarBinary); fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read); Blob = new byte[fs.Length]; fs.Read(Blob, 0, Blob.Length); cmd.Parameters["FGraphic"].Value = Blob; tmpInt = cmd.ExecuteNonQuery(); #endif cmd.CommandText = "select * from TestTypes"; cmd.Parameters.Clear(); rdr = cmd.ExecuteReader(); do { if (rdr.HasRows) { for (int i = 0; i < rdr.FieldCount; ++i) { fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\""); } tmpInt = rdr.GetOrdinal("FGraphic"); while (rdr.Read()) { tmpString = "FromBlob.bmp"; if (File.Exists(tmpString)) { File.Delete(tmpString); } Blob = (byte[])rdr["FGraphic"]; fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); tmpString = "FromBlob_1.bmp"; if (File.Exists(tmpString)) { File.Delete(tmpString); } Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)]; rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length); fs = new FileStream(tmpString, FileMode.Create); fs.Write(Blob, 0, Blob.Length); fs.Close(); } } }while(rdr.NextResult()); rdr.Close(); #endif Result = 0; } catch (Exception eException) { Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace); } } finally { #if TEST_DB_BY_ODBC if (odbc_rdr != null && !odbc_rdr.IsClosed) { odbc_rdr.Close(); } if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open) { odbc_conn.Close(); } #endif if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (conn != null && conn.State == System.Data.ConnectionState.Open) { conn.Close(); } if (fstr_out != null) { fstr_out.Close(); } } return(Result); }
public void TestMultipleResultsets() { #if !JAVA if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) { //In .NET there is a bug when calling a SP with multiple REFCURSORS, the workaround is to use OracleClient and not OleDb. //In GH we are not bug complient in this issue, because there is no workaround (We do not support the OracleClient namespace. this.Log("Not testing multi result set Oracle on .NET"); return; } if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) { // fail to work on .NET OLEDB //reader = Microsoft.ApplicationBlocks.Data.PostgresOleDbHelper.ADOExecuteReader(cmd1); this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF"); return; } #endif Exception exp = null; BeginCase("Test multi result set from stored procedure"); OleDbDataReader reader = null; OleDbTransaction tr = null; try { //Check SP with the structre : insert Select + update Select + delete Select if (con.State != ConnectionState.Open) { con.Open(); } // transaction use was add for PostgreSQL tr = con.BeginTransaction(); OleDbCommand cmd1 = new OleDbCommand("GHSP_TYPES_SIMPLE_4", con, tr); cmd1.CommandType = CommandType.StoredProcedure; OleDbParameter param = new OleDbParameter(); param.ParameterName = "ID1"; param.Value = string.Format("13268_{0}", this.TestCaseNumber); param.OleDbType = OleDbType.VarWChar; cmd1.Parameters.Add(param); reader = cmd1.ExecuteReader(); //Count the number of result sets. int resultSetCount = 0; //Count the number of the records int recordCounter = 0; do { //this.Log(string.Format("resultSetCount:{0}",resultSetCount)); while (reader.Read()) { recordCounter++; } //this.Log(string.Format("recordCounter:{0}",recordCounter)); if (resultSetCount != 2) { Compare(recordCounter, 1); //Insert + update } else { Compare(recordCounter, 0); //Delete } recordCounter = 0; resultSetCount++; }while (reader.NextResult()); Compare(resultSetCount, 3); } catch (Exception ex) { exp = ex; } finally { if (reader != null) { reader.Close(); } tr.Commit(); con.Close(); EndCase(exp); } }
private DataTable DetalleOtrosCli(string rutafile, string Tipodesp) { Pedido Pedido = new Pedido(); TablaEncabezado(); DataTable retorno = (DataTable)ViewState["tabla"]; DataRow drow = null; OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source = " + rutafile + "; " + "Extended Properties='Excel 8.0;HDR=No';"); Pedido.Archivo = rutafile; string sql = "select * from [Sheet1$A6:N1000]"; OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn); //Variables de item string detalleProducto; double Ancho; double Alto; double kg; double precioProd; double NetoItem; double NetoItemCDesp; double metroLineal; string modelo; kilosPedido = 0; NetoPedido = 0; double Margen = iNFOcli.FactorMargen(); try { cnn.Open(); OleDbDataReader leerCadena = da.SelectCommand.ExecuteReader(); while (leerCadena.HasRows) { while (leerCadena.Read()) { if (leerCadena[1] != DBNull.Value && leerCadena[2] != DBNull.Value) { detalleProducto = Convert.ToString(leerCadena[1]); ValDiccionario InfodelDicc = Funciones.BuscaValDicc(detalleProducto); if (InfodelDicc.Existe) { CodigoAlfakProd = InfodelDicc.CodigoAlfakProd; EspCristal1 = InfodelDicc.EspCRistal1; EspCristal2 = InfodelDicc.EspCRistal2; EspSeparador = InfodelDicc.EspSeparador; } else { Response.Redirect("~/Diccionario/agregar-Modelo-DVH.aspx?Producto=" + detalleProducto + "&Archivo=" + Pedido.Archivo + "&ID=" + id_temp.Value); } Ancho = 0; Alto = 0; kg = 0; precioProd = 0; NetoItem = 0; NetoItemCDesp = 0; metroLineal = 0; Ancho = Convert.ToDouble(leerCadena[2].ToString()); Alto = Convert.ToDouble(leerCadena[3].ToString()); CalculosProd CalProd = new CalculosProd(); UnidadesProd Datos = CalProd.Calculos(Ancho, Alto, EspCristal1, EspCristal2); kg = Datos.Kilos; m2 = Datos.MetroCuad; metroLineal = Datos.MetroLi; modelo = Convert.ToString(leerCadena[0]).Trim(); int digitos = 22 - modelo.Length; drow = retorno.NewRow(); drow["Modelo"] = Convert.ToString(leerCadena[0]) + " - " + NombrePedido.Substring(0, digitos); drow["Detalle producto"] = Convert.ToString(leerCadena[1]); drow["Cantidad"] = Convert.ToInt32(leerCadena[4]); int cantidad = Convert.ToInt32(leerCadena[4]); drow["Ancho"] = Convert.ToDecimal(leerCadena[2]); drow["Alto"] = Convert.ToDecimal(leerCadena[3]); drow["mt2"] = (m2 * cantidad).ToString("0.##"); drow["Kilos"] = (kg * cantidad); drow["Esp c1"] = EspCristal1; drow["Esp sep"] = EspSeparador; drow["Esp c2"] = EspCristal2; drow["Codigo"] = CodigoAlfakProd; kilosPedido = kilosPedido + (Convert.ToInt32(kg) * cantidad); m2Pedido = m2Pedido + (m2 * cantidad); CalculosProd Precio = new CalculosProd(); //traer precio unitado por producto precioProd = Precio.PrecioProd(CodigoAlfakProd, Ancho, Alto); //sumar proceso al precio uinitario NetoItem = precioProd + Precio.PrecioProcesoDVH("Corte", m2, metroLineal, kg) + Precio.PrecioProcesoDVH("Armado", m2, metroLineal, kg); //incluir Margen y cantidad double factorC = iNFOcli.FactorMargen(); NetoItem = NetoItem * factorC * cantidad; //Castigo por dimension pequeña if (Ancho <= 250 || Alto <= 250) { NetoItem = NetoItem + 20000; } else { if (Ancho <= 350 || Alto <= 350) { NetoItem = NetoItem + 5000; } } //Neto con despacho incluido NetoItemCDesp = NetoItem + Precio.CostoDespacho(Tipodesp, kg); NetoDespacho = NetoDespacho + Precio.CostoDespacho(Tipodesp, kg); drow["Neto"] = Math.Round(NetoItem); drow["NetoCDesp"] = Math.Round(NetoItemCDesp); NetoPedido = NetoPedido + Math.Round(NetoItem); NetoPedidoCdesp = NetoPedidoCdesp + Math.Round(NetoItemCDesp); retorno.Rows.Add(drow); } else { } } leerCadena.NextResult(); } leerCadena.Close(); cnn.Close(); } catch (Exception e) { } ViewState["tabla"] = retorno; return(retorno); }
public void run() { Exception exp = null; bool RecordsExists = false; OleDbDataReader rdr = null; // testBug3965(); // TestMultipleResultsets(); // TestCompoundVariable(); cmd.CommandText = "Select FirstName,City From Employees"; if (con.State != ConnectionState.Open) { con.Open(); } try { BeginCase("check reader is null"); rdr = cmd.ExecuteReader(); Compare(rdr == null, false); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } try { BeginCase("check reader.read"); rdr = cmd.ExecuteReader(); RecordsExists = rdr.Read(); Compare(RecordsExists, true); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } try { BeginCase("execute reader again "); rdr = cmd.ExecuteReader(); Compare(rdr == null, false); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } try { BeginCase("Test compound SQL statement"); //Build a compund SQL command. string[] sqlStatements = new string[] { "INSERT INTO Categories (CategoryName, Description) VALUES('__TEST_RECORD__', 'Inserted')", "UPDATE Categories SET Description='Updated' WHERE CategoryName='__TEST_RECORD__'", "DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", }; cmd.CommandText = CreateCompundSqlStatement(sqlStatements, ConnectedDataProvider.GetDbType()); rdr = cmd.ExecuteReader(); Compare(rdr.Read(), false); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } if (ConnectedDataProvider.GetDbType() != DataBaseServer.Oracle) { try { BeginCase("Check that in a compound SQL statement, resultsets are returned only for SELECT statements. (bug #3358)"); //prepare db: OleDbCommand prepare = new OleDbCommand("DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", con); prepare.ExecuteNonQuery(); //Test body int resultSetCount; //Build a compund SQL command that contains only one select statement. string[] sqlStatements = new string[] { "INSERT INTO Categories (CategoryName, Description) VALUES('__TEST_RECORD__', 'Inserted')", "UPDATE Categories SET Description='Updated' WHERE CategoryName='__TEST_RECORD__'", "DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", "SELECT * FROM Categories " }; string insertCmdTxt = CreateCompundSqlStatement(sqlStatements, ConnectedDataProvider.GetDbType()); //this.Log(insertCmdTxt); OleDbCommand InsertCmd = new OleDbCommand(insertCmdTxt, con); rdr = InsertCmd.ExecuteReader(); //Count the number of result sets. resultSetCount = 0; do { resultSetCount++; }while (rdr.NextResult()); //Test that there is only one result set. Compare(resultSetCount, 1); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } //cleanup db: OleDbCommand cleanup = new OleDbCommand("DELETE FROM Categories WHERE CategoryName='__TEST_RECORD__'", con); cleanup.ExecuteNonQuery(); EndCase(exp); exp = null; } } if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle) { try { BeginCase("Use out refcursor implicitly to get resultset from stored-procedure in command type text."); cmd.CommandText = "{ call ghsp_types_simple_1 (null, null, 1.234, null, null, null, null)}"; cmd.CommandType = CommandType.Text; rdr = cmd.ExecuteReader(); Compare(rdr.HasRows, true); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } try { BeginCase("Use out refcursor implicitly to get resultset from stored-procedure in CommandType.StoredProcedure."); cmd.CommandText = "GHSP_TYPES_SIMPLE_1"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("", 123456); cmd.Parameters.Add("", "asdasd"); cmd.Parameters.Add("", 1.234); cmd.Parameters.Add("", "asdasd"); cmd.Parameters.Add("", "asdasd"); cmd.Parameters.Add("", "asdasd"); cmd.Parameters.Add("", "asdasd"); rdr = cmd.ExecuteReader(); Compare(rdr.HasRows, true); } catch (Exception ex) { exp = ex; } finally { if (rdr != null) { rdr.Close(); } EndCase(exp); exp = null; } } }
public void AddFromReader(OleDbDataReader reader, bool addSpace) { do { if (reader.HasRows) { HtmlGenericControl resultSetDiv = CreateDiv("ResultSet"); _container.Controls.Add(resultSetDiv); int headerColumn = reader.GetColumnIndex("Header"); string lastHeader = null; int subheaderColumn = reader.GetColumnIndex("Subheader"); string lastSubHeader = null; int lastColumn = reader.FieldCount - 1; for (int i = 0; i < 2; ++i) { if (lastColumn == headerColumn || lastColumn == subheaderColumn) { lastColumn -= 1; } } while (reader.Read()) { HtmlGenericControl rowSetDiv = CreateDiv("RowSet"); resultSetDiv.Controls.Add(rowSetDiv); bool headerAdded = false; bool subheaderAdded = false; if (headerColumn >= 0 && !reader.IsDBNull(headerColumn)) { string header = reader.GetValue(headerColumn).ToString(); headerAdded = header != lastHeader; if (headerAdded) { HtmlGenericControl headerDiv = CreateDiv("RowSetHeader", header); rowSetDiv.Controls.Add(headerDiv); lastHeader = header; } } if (subheaderColumn >= 0 && !reader.IsDBNull(subheaderColumn)) { string subheader = reader.GetValue(subheaderColumn).ToString(); subheaderAdded = subheader != lastSubHeader; if (subheaderAdded) { if (headerAdded && addSpace) { AddSpace(rowSetDiv, "Space2"); } HtmlGenericControl subheaderDiv = CreateDiv("RowSetSubheader", subheader); rowSetDiv.Controls.Add(subheaderDiv); lastSubHeader = subheader; } } if (lastColumn < 0) { if (addSpace) { AddSpace(rowSetDiv, "Space2"); } } else { if ((headerAdded || subheaderAdded) && addSpace) { AddSpace(rowSetDiv, "Space2"); } for (int i = 0; i < reader.FieldCount; ++i) { if (i != headerColumn && i != subheaderColumn) { HtmlGenericControl valueSetDiv = CreateDiv("ValueSet"); rowSetDiv.Controls.Add(valueSetDiv); HtmlGenericControl labelDiv = CreateDiv("Label", reader.GetName(i)); valueSetDiv.Controls.Add(labelDiv); if (addSpace) { HtmlGenericControl separatorDiv = CreateDiv("Separator", ""); valueSetDiv.Controls.Add(separatorDiv); } HtmlGenericControl valueDiv = CreateValueDiv(!reader.IsDBNull(i) ? reader.GetValue(i) : null); valueSetDiv.Controls.Add(valueDiv); if (i < lastColumn && addSpace) { AddSpace(rowSetDiv, "Space1"); } } } if (addSpace) { AddSpace(rowSetDiv, "Space4"); } } } } }while (reader.NextResult()); }
public void TestMultipleResultSetsWithSP() { #if !JAVA if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle) { this.Log("Not testing Stored procedures with multiple ref-cursors on Oracle with .NET due to bug in .NET (only the first ref-cursor is retrived)."); return; } if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) { // fail to work on .NET OLEDB this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF"); return; } #endif bool NextResultExists = false; // transaction use was add for PostgreSQL OleDbTransaction tr = con.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("GH_MULTIRECORDSETS", con, tr); cmd.CommandType = CommandType.StoredProcedure; OleDbDataReader rdr = cmd.ExecuteReader(); try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 exists"); Compare(rdr != null, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Data"); Compare(rdr.GetValue(1).ToString(), "Yavine"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "EMPLOYEEID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } // -------------- ResultSet 2 ------------ try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Data"); Compare(rdr.GetValue(1).ToString(), "Morgenstern Gesundkost"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } // -------------- ResultSet 3 ------------ try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 3 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "ORDERID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not exist."); NextResultExists = rdr.NextResult(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not contain data."); NextResultExists = rdr.Read(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } //Cleanup: if (!rdr.IsClosed) { rdr.Close(); } // transaction use was add for PostgreSQL tr.Commit(); }
public void ExecuteCmd(Func <OleDbConnection> dataSouce, string cmdText, Action <OleDbParameterCollection> inputParamMapper, Action <IDataReader, short> map, Action <OleDbParameterCollection> returnParameters = null, int?timeOutSpan = null) { if (map == null) { throw new NullReferenceException("ObjectMapper is required."); } OleDbDataReader reader = null; OleDbCommand cmd = null; OleDbConnection conn = null; short resultSet = 0; try { using (conn = dataSouce()) { if (conn != null) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd = GetCommand(conn, cmdText, inputParamMapper); if (cmd != null) { reader = cmd.ExecuteReader(); while (true) { while (reader.Read()) { if (map != null) { map(reader, resultSet); } } resultSet += 1; if (reader.IsClosed || !reader.NextResult()) { break; } if (resultSet > 10) { throw new Exception("Too many result sets returned"); } } reader.Close(); if (returnParameters != null) { returnParameters(cmd.Parameters); } if (conn.State != ConnectionState.Closed) { conn.Close(); } } } } } catch (Exception ex) { throw ex; } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } if (conn != null && conn.State != ConnectionState.Closed) { conn.Close(); } } }
private bool UpLoad(OleDbDataReader reader) { strExt = ""; int cashID = -1; int CashBusy = -1; int state = -1; string strUpdate = ""; //object obj; // DataTable dataTable = new DataTable(); int LineID = -1; do { switch (state) { case -1: if (!reader.Read()) { break; } if ((reader.GetName(0) == "TABLE") && (reader[0].ToString() == "HEADER")) { state = 0; cashID = -1; CashBusy = 0; //dataTable.Load(reader); //strTable = ""; } break; case 0: if (!reader.Read()) { break; } if (reader.GetName(0).ToUpper() == "CASHID") { if (LineID != -1) { if (CashBusy == 0) { string strTemp; strTemp = String.Format(" update #cashman set Processing=2 " + "WHERE CashID IN (SELECT CashID FROM CashLines WHERE lineid={0})", cashID); if (ExecuteNonQueryMySql("INSERT INTO `signal` (`signal`,`version`) VALUES ('" + strExt.Trim() + "',0);")) { strUpdate += strTemp; } else { log(String.Format("Ошибка при добавлении в `signal` : {0}", ErrStr)); } } } state = 1; cashID = (int)reader[0]; LineID = cashID; strExt = reader[1].ToString(); string posKey = String.Format("Pos_{0}", cashID); if (!PosDb.ContainsKey(cashID)) { log(String.Format("Кассовая линейка {0} ({1}) не прописана в конфиге", cashID, posKey)); CashBusy = 1; } else { log(String.Format("Обработка кассовой линейки {0}", cashID)); mDB.ChangeDatabase(PosDb[cashID]); if (!CheckSignal()) { log("*ОШИБКА* select * from signal : " + ErrStr); CashBusy = 1; break; } if (nRows != 0) { log(String.Format("Линейка {0} занята...", cashID)); CashBusy = 1; break; } CashBusy = 0; } } //dataTable.Load(reader); break; case 1: if (!reader.Read()) { break; } if ((reader.GetName(0) == "TABLE") && (reader[0].ToString() == "HEADER")) { if (CashBusy == 0) { strUpdate += String.Format("update #cashman set Processing=2 " + "WHERE CashID IN (SELECT CashID FROM CashLines WHERE lineid={0})", cashID); } state = 0; cashID = -1; CashBusy = 0; strTable = ""; break; } Skip = false; if (CashBusy == 1) { break; } if (reader.GetName(0).ToUpper() == "TABLE") { strTable = reader[0].ToString(); state = 2; if (skipTables.IndexOf(strTable.Trim() + ",") != -1) { Skip = true; } } //dataTable.Load(reader); break; case 2: if ((CashBusy == 0) && !Skip) { // if (!CopyToTable(cMySql, reader)) if (!SqlToMySQL(reader, strTable)) { reader.Close(); return(false); } } else { //dataTable.Load(reader); SkipTable(reader); } //if ((nRows == 0) && (strExt == "upd")) // File.Delete(Path.Combine(DirectoryDbf.Trim(), strTable.Trim() + ".DBF")); state = 1; break; } } while (reader.NextResult()); reader.Close(); if (LineID != -1) { if (CashBusy == 0) { string strTemp; strTemp = String.Format(" update #cashman set Processing=2 " + "WHERE CashID IN (SELECT CashID FROM CashLines WHERE lineid={0})", cashID); if (ExecuteNonQueryMySql("INSERT INTO `signal` (`signal`,`version`) VALUES ('" + strExt.Trim() + "',0);")) { strUpdate += strTemp; } else { log(String.Format("Ошибка при добавлении в `signal` : {0}", ErrStr)); } } } if (strUpdate != "") { ExecuteNonQuery(con, strUpdate); if (ErrStr != "") { log("*ОШИБКА* update #cashman set Processing=2 : " + ErrStr); } } return(true); }