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