public override bool SetDatatableSchema(string tableName)
{
string query = "select * from " + tableName + " where 1=0;";
Table table = GetTable(tableName);
OdbcCommand command = new OdbcCommand(query, this.connection);
try
{
command.Prepare();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(command);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
table.dataTable = dataSet.Tables[0];
this.GetTable(tableName).columns = table.dataTable.Columns.Cast <DataColumn>().ToList();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Helper.Log(e.Message, "ODBC-SetDatatableSchema" + tableName);
return(false);
}
}
public Resource query(string query, AdapterCommandType type)
{
//Console.WriteLine("Query: " + query);
Resource datatable = new Resource();
if (!connected || connection.State != ConnectionState.Open)
{
connect(host, db, user, password);
if (connected)
{
return(this.query(query, type));
}
else
{
return(datatable);
}
}
try
{
if (type == AdapterCommandType.Update || type == AdapterCommandType.Insert || type == AdapterCommandType.Delete)
{
OdbcCommand odbcCommand = connection.CreateCommand();
odbcCommand.CommandText = query;
odbcCommand.Prepare();
odbcCommand.ExecuteNonQuery();
return(datatable);
}
else if (type == AdapterCommandType.Select)
{
OdbcCommand command = connection.CreateCommand();
command.CommandText = query;
adapter.SelectCommand = command;
adapter.Fill(datatable);
return(datatable);
}
}
catch (InvalidOperationException e) //Database is disconnected
{
Console.WriteLine("Invalid Operation Exception at Query: " + query);
Console.WriteLine("Message: " + e.Message);
Console.WriteLine(e.StackTrace);
/*connect(host, db, user, password);
* if(connected)
* return this.query(query, type);*/
return(datatable);
}
catch (OdbcException e) //Database already has the value
{
Console.WriteLine("OdbcException at Query: " + query);
Console.WriteLine("Message: " + e.Message);
Console.WriteLine(e.StackTrace);
return(datatable);
}
return(datatable);
}
/// <summary>
/// Lis et affiche les données d'une table en base
/// </summary>
/// <param name="connection">Connexion à la base.</param>
/// <param name="column">Nom de(s) colonne(s) de la table</param>
/// <param name="value">Valeur(s) de la table</param>
/// <param name="table">Objet générique table</param>
/// <returns>Retourne une liste si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static List <T> SelectTableNextGen <T>(ConnectionPostGre connection, string column, string value, T table)
{
string reqSelectElement;
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(null);
}
bool isAProperty = false;
foreach (PropertyInfo item in table.GetType().GetProperties())
{
if (column.Equals(item.Name))
{
isAProperty = true;
}
}
if (isAProperty == false)
{
Console.WriteLine("property obj not found");
return(null);
}
if (value == null || column == null)
{
reqSelectElement = $"SELECT * FROM {table.GetType().Name.ToString()}";
}
else
{
reqSelectElement = $"SELECT * FROM {table.GetType().Name.ToString()} WHERE {column} = ?";
}
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToSelectElement = new OdbcCommand(reqSelectElement, conn))
{
queryToSelectElement.Parameters.AddWithValue(column, value);
queryToSelectElement.Prepare();
OdbcDataReader dr = queryToSelectElement.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
List <T> list = MappingOperations.MapList(dt, table);
return(list);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Modifie le ou les élément(s) d'une table en base
/// </summary>
/// <param name="connection">Connexion à la base.</param>
/// <param name="id">Id des propriétés à modifier </param>
/// <param name="table">Objet générique table</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool UpdateElementNextGen <T>(ConnectionPostGre connection, int id, T table)
{
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(false);
}
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(table);
string reqUpdate = $"UPDATE {table.GetType().Name.ToString()} SET ";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
reqUpdate += $"{objectMapping.PropertiesAttributes[i].NameInfo}= ?";
if (i != objectMapping.PropertiesAttributes.Count() - 1)
{
reqUpdate += ",";
}
}
reqUpdate += $" WHERE id = ?";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryUpdate = new OdbcCommand(reqUpdate, conn))
{
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
queryUpdate.Parameters.AddWithValue($"{infoFormapping.NameInfo}", infoFormapping.ValueInfo);
}
queryUpdate.Parameters.AddWithValue($"id", id);
queryUpdate.Prepare();
queryUpdate.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Supprime un element d'une table en base
/// </summary>
/// <param name="connection">Connexion à la base.</param>
/// <param name="column">Nom de(s) colonne(s) de la table</param>
/// <param name="value">Valeur(s) de la table</param>
/// <param name="table">Objet générique table</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool DeleteElemetFromTableNextGen <T>(ConnectionPostGre connection, string column, string value,
T table)
{
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(false);
}
bool isAProperty = false;
foreach (PropertyInfo item in table.GetType().GetProperties())
{
if (column.Equals(item.Name))
{
isAProperty = true;
break;
}
}
if (isAProperty == false)
{
Console.WriteLine("property obj not found");
return(false);
}
string reqDelete = $"DELETE FROM {table.GetType().Name.ToString()} WHERE {column} = ?";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToDeleteElement = new OdbcCommand(reqDelete, conn))
{
queryToDeleteElement.Parameters.AddWithValue(column, value);
queryToDeleteElement.Prepare();
queryToDeleteElement.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/*
* public static void Select()
* {
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
* // 1. Instantiate the connection
*
*
* OdbcDataReader rdr = null;
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("select * from customers", conn);
*
* //
* // 4. Use the connection
* //
*
* // get query results
* rdr = cmd.ExecuteReader();
*
* // print the CustomerID of each record
* while (rdr.Read())
* {
* Console.WriteLine(rdr["name"]);
* }
* Console.ReadLine();
*
* }
* finally
* {
* // close the reader
* if (rdr != null)
* {
* rdr.Close();
* }
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
* }
*
* public static void Insert()
* {
* Console.WriteLine("Que voulez-vous insérer ?");
* Console.WriteLine("Entrez le nom de la propriété");
* string propertyname = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("insert into customers2 (surname) values (?)", conn);
*
*
* cmd.Parameters.Add("@PropertyName", OdbcType.NVarChar).Value = propertyname;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void Update()
* {
* Console.WriteLine("Que voulez-vous modifier ?");
* Console.WriteLine("Entrez la valeur à modifier");
* string oldvalue = Console.ReadLine();
* Console.WriteLine("Entrez la nouvelle valeur");
* string newvalue = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("update customers set prenom = ? where prenom = ?", conn);
*
*
* cmd.Parameters.Add("@NewValue", OdbcType.NVarChar).Value = newvalue;
* cmd.Parameters.Add("@OldValue", OdbcType.NVarChar).Value = oldvalue;
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void Delete()
* {
* Console.WriteLine("Que voulez-vous supprimer ?");
* Console.WriteLine("Entrez le nom de la propriété");
* string propertyname = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("delete from customers2 where surname = ?", conn);
*
* cmd.Parameters.Add("@PropertyName", OdbcType.NVarChar).Value = propertyname;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void CreateTable()
* {
* Console.WriteLine("Entrez le nom de la table à créer");
* string tablename = Console.ReadLine();
* Console.WriteLine("Entrez le nom de la colonne à créer");
* string columnname = Console.ReadLine();
* Console.WriteLine("Entrez le type de donnée de la colonne");
* string columntype = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL Unicode ;Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("create table ? ( ? ? )", conn);
*
* cmd.Parameters.Add("@TableName", OdbcType.NVarChar).Value = tablename;
* cmd.Parameters.Add("@ColumnName", OdbcType.NVarChar).Value = columnname;
* cmd.Parameters.Add("@ColumnType", OdbcType.NVarChar).Value = columntype;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void DropTable()
* {
* Console.WriteLine("Entrez le nom de la table à supprimer");
* string tablename = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("DROP TABLE IF EXISTS ?", conn);
*
* cmd.Parameters.Add("@TableName", OdbcType.NVarChar).Value = tablename;
*
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*/
#endregion
#region Insert
/// <summary>
/// Insertion d'élément(s) dans une table en base
/// </summary>
/// <param name="connection">Connexion à la base</param>
/// <param name="obj">Objet générique</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool InsertNextGen <T>(ConnectionPostGre connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(obj);
string reqInsertElement = $" INSERT INTO {objectMapping.ObjectName} VALUES(DEFAULT,";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
if (i == objectMapping.PropertiesAttributes.Count() - 1)
{
reqInsertElement += "?";
}
else
{
reqInsertElement += "?,";
}
}
reqInsertElement += ")";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToInsert = new OdbcCommand(reqInsertElement, conn))
{
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
queryToInsert.Parameters.AddWithValue($"{infoFormapping.NameInfo}",
infoFormapping.ValueInfo);
}
queryToInsert.Prepare();
queryToInsert.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public async Task <(DateTime?, bool)> GetDueDateInfo(string fullWorkOrderNumber)
{
string workOrderNumber;
string workOrderNumberDash = string.Empty;
if (fullWorkOrderNumber.Contains("-"))
{
string[] workOrderSplit = fullWorkOrderNumber.Split('-');
workOrderNumber = workOrderSplit[0].Trim();
workOrderNumberDash = workOrderSplit[1].Trim();
}
else
{
workOrderNumber = fullWorkOrderNumber;
}
DateTime?dueDate = null;
bool expedite = false;
await using (OdbcConnection conn = new OdbcConnection())
{
conn.ConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=database_location\database;";
await conn.OpenAsync();
string query = $"select [Date Due], Expedite from [Work Orders] where [Work Order No] = ? and [WO Dash] = ?";
var cmd = new OdbcCommand(query, conn);
cmd.Parameters.Add(new OdbcParameter("work order no", OdbcType.Int)).Value = workOrderNumber;
cmd.Parameters.Add(new OdbcParameter("wo dash", OdbcType.Text)).Value = workOrderNumberDash;
cmd.Prepare();
var reader = await cmd.ExecuteReaderAsync() as OdbcDataReader;
if (await reader.ReadAsync())
{
dueDate = reader.GetDate(0);
expedite = reader.GetBoolean(1);
}
else
{
}
}
return(dueDate, expedite);
}
public override bool Select(string tableOrQueryName, string type)
{
TableOrQuery tableOrQuery;
if (type == TableOrQuery.TYPE_TABLE)
{
tableOrQuery = this.tables[this.GetTableIndexByName(tableOrQueryName)];
}
else
{
tableOrQuery = this.queries[this.GetQueryIndexByName(tableOrQueryName)];
}
if (tableOrQuery == null)
{
return(false);
}
string query = tableOrQuery.query;
OdbcCommand command = new OdbcCommand(query, this.connection);
try
{
command.Prepare();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(command);
tableOrQuery.dataTable.Clear();
tableOrQuery.dataTable.RowChanged += new DataRowChangeEventHandler(Row_Added);
dataAdapter.Fill(tableOrQuery.dataTable);
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Helper.Log(e.Message, "ODBC-SelectData");
return(false);
}
}
public async Task <string> GetJobYear(string fullWorkOrderNumber)
{
string workOrderNumber;
string workOrderNumberDash = string.Empty;
if (fullWorkOrderNumber.Contains("-"))
{
string[] workOrderSplit = fullWorkOrderNumber.Split('-');
workOrderNumber = workOrderSplit[0];
workOrderNumberDash = workOrderSplit[1];
}
else
{
workOrderNumber = fullWorkOrderNumber;
}
string dateReceivedYear = string.Empty;
using (var conn = new OdbcConnection())
{
conn.ConnectionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=database_location\database;";
await conn.OpenAsync();
string query = $"select [Date Received] from [Work Orders] where [Work Order No] = ? and [WO Dash] = ?";
var cmd = new OdbcCommand(query, conn);
cmd.Parameters.Add(new OdbcParameter("work order no", OdbcType.Int)).Value = workOrderNumber;
cmd.Parameters.Add(new OdbcParameter("wo dash", OdbcType.Text)).Value = workOrderNumberDash;
cmd.Prepare();
var reader = await cmd.ExecuteReaderAsync() as OdbcDataReader;
dateReceivedYear = await reader.ReadAsync() ? ((DateTime)reader[0]).Year.ToString() : string.Empty;
//dateReceivedYear = string.IsNullOrEmpty(reader[0].ToString()) ? string.Empty : ((DateTime)reader[0]).Year.ToString();
conn.Close();
}
return(dateReceivedYear);
}
public void PrepareAndExecuteTest()
{
OdbcDataReader reader = null;
try {
string tableName = DBHelper.GetRandomName("PAE", 3);
try {
// setup table
string query = "DROP TABLE " + tableName;
DBHelper.ExecuteNonQuery(conn, query);
query = String.Format("CREATE TABLE {0} ( id INT, small_id SMALLINT )",
tableName);
DBHelper.ExecuteNonQuery(conn, query);
query = String.Format("INSERT INTO {0} values (?, ?)", tableName);
cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.Prepare();
OdbcParameter param1 = cmd.Parameters.Add("?", OdbcType.Int);
OdbcParameter param2 = cmd.Parameters.Add("?", OdbcType.SmallInt);
param1.Value = 1;
param2.Value = 5;
cmd.ExecuteNonQuery();
param1.Value = 2;
param2.Value = 6;
cmd.ExecuteNonQuery();
cmd.CommandText = "select id, small_id from " + tableName + " order by id asc";
reader = cmd.ExecuteReader();
#if NET_2_0
Assert.IsTrue(reader.Read(), "#A1");
Assert.AreEqual(1, reader.GetValue(0), "#A2");
Assert.AreEqual(5, reader.GetValue(1), "#A3");
Assert.IsTrue(reader.Read(), "#A4");
Assert.AreEqual(2, reader.GetValue(0), "#A5");
Assert.AreEqual(6, reader.GetValue(1), "#A6");
Assert.IsFalse(reader.Read(), "#A7");
#else
// in .NET 1.x, changing the CommandText
// does not reset prepared state
Assert.IsFalse(reader.Read(), "#A1");
#endif
reader.Close();
cmd.Dispose();
cmd = conn.CreateCommand();
cmd.CommandText = "select id, small_id from " + tableName + " order by id asc";
reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read(), "#B1");
Assert.AreEqual(1, reader.GetValue(0), "#B2");
Assert.AreEqual(5, reader.GetValue(1), "#B3");
Assert.IsTrue(reader.Read(), "#B4");
Assert.AreEqual(2, reader.GetValue(0), "#B5");
Assert.AreEqual(6, reader.GetValue(1), "#B6");
#if NET_2_0
Assert.IsFalse(reader.Read(), "#B7");
#else
Assert.IsTrue(reader.Read(), "#B7");
Assert.AreEqual(2, reader.GetValue(0), "#B8");
Assert.AreEqual(6, reader.GetValue(1), "#B9");
#endif
} finally {
DBHelper.ExecuteNonQuery(conn, "DROP TABLE " + tableName);
}
} finally {
if (reader != null)
{
reader.Close();
}
}
}
public void ExecuteNonQueryTest()
{
int ret;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from employee where id <= ?;";
cmd.Parameters.Add("@un", OdbcType.Int).Value = 3;
ret = cmd.ExecuteNonQuery();
#if NET_2_0
switch (ConnectionManager.Singleton.Engine.Type)
{
case EngineType.SQLServer:
Assert.AreEqual(-1, ret, "#1");
break;
case EngineType.MySQL:
Assert.AreEqual(1, ret, "#1");
break;
default:
Assert.Fail("Engine type not supported.");
break;
}
#else
Assert.AreEqual(-1, ret, "#1");
#endif
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from employee where id <= ?;";
cmd.Parameters.Add("@un", OdbcType.Int).Value = 3;
ret = cmd.ExecuteNonQuery();
#if NET_2_0
switch (ConnectionManager.Singleton.Engine.Type)
{
case EngineType.SQLServer:
Assert.AreEqual(-1, ret, "#2");
break;
case EngineType.MySQL:
Assert.AreEqual(3, ret, "#2");
break;
default:
Assert.Fail("Engine type not supported.");
break;
}
#else
Assert.AreEqual(-1, ret, "#2");
#endif
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from employee where id <= 3;";
ret = cmd.ExecuteNonQuery();
#if NET_2_0
switch (ConnectionManager.Singleton.Engine.Type)
{
case EngineType.SQLServer:
Assert.AreEqual(-1, ret, "#3");
break;
case EngineType.MySQL:
Assert.AreEqual(3, ret, "#3");
break;
default:
Assert.Fail("Engine type not supported.");
break;
}
#else
Assert.AreEqual(-1, ret, "#3");
#endif
try {
// insert
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into employee (id, fname, dob, doj) values " +
" (6001, 'tttt', '1999-01-22', '2005-02-11');";
ret = cmd.ExecuteNonQuery();
Assert.AreEqual(1, ret, "#4");
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into employee (id, fname, dob, doj) values " +
" (?, 'tttt', '1999-01-22', '2005-02-11');";
cmd.Parameters.Add(new OdbcParameter("id", OdbcType.Int));
cmd.Parameters [0].Value = 6002;
cmd.Prepare();
ret = cmd.ExecuteNonQuery();
Assert.AreEqual(1, ret, "#5");
} finally {
// delete
cmd = (OdbcCommand)conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from employee where id > 6000";
ret = cmd.ExecuteNonQuery();
Assert.AreEqual(2, ret, "#6");
}
}
public DatabaseResult Query(string sQuery, object[] oParameter)
{
// stripped //
OdbcConnection localConnection = _connectIntern();
// Validate connection state and abort on error.
// stripped //
// Beginn command parsing and execution.
DatabaseResult drReturn = new DatabaseResult();
using (OdbcCommand mCommand = new OdbcCommand(sQuery, localConnection)) {
if (oParameter != null)
{
for (int i = 0; i < oParameter.Length; i++)
{
Type tParameterType = oParameter[i].GetType();
if (tParameterType == typeof(ulong))
{
mCommand.Parameters.AddWithValue("?", Convert.ToDecimal(oParameter[i]));
}
else if (tParameterType == typeof(DateTime))
{
DateTime dtParam = (DateTime)oParameter[i];
if (dtParam == DateTime.MinValue)
{
mCommand.Parameters.AddWithValue("?", "0000-00-00 00:00:00");
}
else
{
var param = mCommand.Parameters.AddWithValue("?", dtParam.ToString("yyyy-MM-dd HH:mm:ss"));
param.DbType = System.Data.DbType.DateTime;
param.OdbcType = OdbcType.Text;
}
}
else
{
var cmd = mCommand.Parameters.AddWithValue("?", oParameter[i]);
}
}
}
mCommand.Prepare();
string sAction = mCommand.CommandText.Substring(0, mCommand.CommandText.IndexOf(' ')).Trim().ToLowerInvariant();
switch (sAction)
{
default:
case "call":
case "select":
using (OdbcDataReader mReader = mCommand.ExecuteReader()) {
while (mReader.Read())
{
DatabaseRow d = new DatabaseRow();
for (int i = 0; i < mReader.VisibleFieldCount; i++)
{
string sFieldName = mReader.GetName(i);
Type tFieldType = mReader.GetFieldType(i);
if (tFieldType == typeof(DateTime))
{
try {
if (mReader.IsDBNull(i))
{
d[sFieldName] = DateTime.MinValue;
}
else
{
d[sFieldName] = mReader.GetValue(i);
}
}
catch (Exception) {
d[sFieldName] = DateTime.MinValue;
}
}
else
{
var val = mReader.GetValue(i);
d[sFieldName] = val;
}
}
if (drReturn.Result == null)
{
drReturn.Result = d;
}
drReturn.ResultSet.Add(d);
}
}
drReturn.Affected = drReturn.ResultSet.Count;
drReturn.Success = true;
break;
case "insert":
drReturn.Affected = mCommand.ExecuteNonQuery();
mCommand.CommandText = "select last_insert_id()";
drReturn.ID = Convert.ToUInt64(mCommand.ExecuteScalar());
drReturn.Success = true;
break;
}
// stripped //
localConnection.Dispose();
// stripped //
return(drReturn);
}
}
/// <summary>
/// Gets an order from the AS400 or from SQL, depending on setting of chkUseThisParm
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFetchOrder_Click(object sender, EventArgs e)
{
try
{
string ordToRetrieve = (chkUseThisParm.Checked) ? txtOrderNumber.Text : cboOrderToRetrieveSql.Text;
OdbcCommand cmd = new OdbcCommand(string.Format("call {0} ({1})", txtOrderDataProc.Text, (chkUseThisParm.Checked) ? txtOrderNumber.Text : ""));
cmd.Connection = new OdbcConnection(cboAs400Connections.Text);
cmd.Connection.Open();
cmd.Prepare();
OdbcDataReader dr = cmd.ExecuteReader();
tOrd = new DataTable("myOrderTable");
tOrd.Load(dr);
dgvOrder.DataSource = tOrd;
// FIT data
cmd.CommandText = string.Format("call {0}", txtFitDataProc.Text);
cmd.Prepare();
dr = cmd.ExecuteReader();
tFit = new DataTable("myFitDataTable");
tFit.Load(dr);
int fitCount = tFit.Rows.Count;
//dgvFit.DataSource = tFit;
// care instructions
// FIT data
cmd.CommandText = string.Format("call {0}", txtCareDataProc.Text);
cmd.Prepare();
dr = cmd.ExecuteReader();
tCare = new DataTable("myCareDataTable");
tCare.Load(dr);
int careCount = tCare.Rows.Count;
dgvCare.DataSource = tCare;
// special descriptions
// data
cmd.CommandText = string.Format("call {0}", txtDescDataProc.Text);
cmd.Prepare();
dr = cmd.ExecuteReader();
tDesc = new DataTable("myDescDataTable");
tDesc.Load(dr);
dgvCare.DataSource = tDesc;
// lining and filling
cmd.CommandText = string.Format("call {0}", txtLiningDataProc.Text);
cmd.Prepare();
dr = cmd.ExecuteReader();
tLining = new DataTable("myLiningDataTable");
tLining.Load(dr);
// write a new instance if an order has been retrieved from AS400
if (chkUseThisParm.Checked)
{
// write header
string instanceName = string.Format("{0}-{1}", txtOrderNumber.Text.Replace("'", ""), DateTime.Now.ToString());
using (SqlCommand sqlc = new SqlCommand("INSERT INTO tInstanceHeader (InstanceName, CreatedBy, Status) VALUES(@InstanceName,@CreatedBy, @Status); select scope_identity()",
new SqlConnection(cboSqlServer.Text)))
{
sqlc.Parameters.AddWithValue("@InstanceName", instanceName);
sqlc.Parameters.AddWithValue("@CreatedBy", Environment.UserName);
sqlc.Parameters.AddWithValue("@Status", "NEW");
sqlc.Connection.Open();
int headerId = -1;
object return_value = sqlc.ExecuteScalar();
if (return_value != null)
{
int.TryParse(return_value.ToString(), out headerId);
}
sqlc.CommandText = "INSERT INTO [dbo].[tInstanceDetail] ([InstanceHeader] ,[SkuNumber],[Category],[EAN],[Condition]," +
"[StockStatus],[Price],[PriceCurrency],[ProductDescription],[SellerCost],[SellerCostCurrency], " +
"[ManufSuggRetailPrice],[ManufSuggRetailPriceCurrency],[ParentSKU],[RelationshipName],[ResellerSilhouette],[AgeGroups],[Brand], " +
"[Description],[Gender],[MainColor],[Season],[Size],[SizeRegister],[SpecialDescription],[SupplierColor],[VatType],[OuterFabricMaterial], " +
"[WashInstructions],[Lining],[Filling]) " +
"VALUES(@InstanceHeader, @SkuNumber, @Category, @EAN, @Condition, @StockStatus, @Price, @PriceCurrency, @ProductDescription, @SellerCost, " +
"@SellerCostCurrency, @ManufSuggRetailPrice, @ManufSuggRetailPriceCurrency, @ParentSKU, @RelationshipName, @ResellerSilhouette, @AgeGroups, " +
"@Brand, @Description, @Gender, @MainColor, @Season, @Size, @SizeRegister, @SpecialDescription, @SupplierColor, @VatType, " +
"@OuterFabricMaterial, @WashInstructions, @Lining, @Filling ) ";
foreach (DataRow r in tOrd.Rows)
{
sqlc.Parameters.Clear();
sqlc.Parameters.AddWithValue("@InstanceHeader", headerId);
sqlc.Parameters.AddWithValue("@SkuNumber", (string)r["STYLE"]);
sqlc.Parameters.AddWithValue("@Category", (string)r["CATEGORY"]);
sqlc.Parameters.AddWithValue("@EAN", (string)r["UPCCODE"]);
sqlc.Parameters.AddWithValue("@Condition", "NEW");
sqlc.Parameters.AddWithValue("@StockStatus", "0");
sqlc.Parameters.AddWithValue("@Price", ((decimal)r["WHOLESALEPRICE"]).ToString());
sqlc.Parameters.AddWithValue("@PriceCurrency", (string)r["CURRENCY"]);
sqlc.Parameters.AddWithValue("@ProductDescription", (string)r["STYLEDESCRIPTION"]);
sqlc.Parameters.AddWithValue("@SellerCost", ((decimal)r["WHOLESALEPRICE"]).ToString());
sqlc.Parameters.AddWithValue("@SellerCostCurrency", (string)r["CURRENCY"]);
sqlc.Parameters.AddWithValue("@ManufSuggRetailPrice", ((decimal)r["SALESPRICEFROMLIST"]).ToString());
sqlc.Parameters.AddWithValue("@ManufSuggRetailPriceCurrency", (string)r["CURRENCY"]);
sqlc.Parameters.AddWithValue("@ParentSKU", (string)r["STYLE"]);
sqlc.Parameters.AddWithValue("@RelationshipName", "STYLE_COLORSIZE");
sqlc.Parameters.AddWithValue("@ResellerSilhouette", "");
sqlc.Parameters.AddWithValue("@AgeGroups", "");
sqlc.Parameters.AddWithValue("@Brand", "FCUK");
sqlc.Parameters.AddWithValue("@Description", "FCUK");
sqlc.Parameters.AddWithValue("@Gender", (string)r["RANGENAME"]);
sqlc.Parameters.AddWithValue("@MainColor", (string)r["COLOURDESC"]);
sqlc.Parameters.AddWithValue("@Season", (string)r["SEASON"]);
switch ((string)r["SIZE"])
{
case "10":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ1"]);
break;
case "20":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ2"]);
break;
case "30":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ3"]);
break;
case "40":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ4"]);
break;
case "50":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ5"]);
break;
case "60":
sqlc.Parameters.AddWithValue("@Size", (string)r["SZ6"]);
break;
default:
sqlc.Parameters.AddWithValue("@Size", "UNKNOWN");
break;
}
sqlc.Parameters.AddWithValue("@SizeRegister", (string)r["SIZE"]);
sqlc.Parameters.AddWithValue("@SpecialDescription", getDescription((string)r["STYLE"]));
sqlc.Parameters.AddWithValue("@SupplierColor", (string)r["COLOURDESC"]);
sqlc.Parameters.AddWithValue("@VatType", "100");
sqlc.Parameters.AddWithValue("@OuterFabricMaterial", (string)r["FABRIC"]);
sqlc.Parameters.AddWithValue("@WashInstructions", getCareInstructions((string)r["STYLE"]));
sqlc.Parameters.AddWithValue("@Lining", getLining((string)r["STYLE"]));
sqlc.Parameters.AddWithValue("@Filling", getFilling((string)r["STYLE"]));
//sqlc.Connection.Open();
sqlc.ExecuteScalar();
}
MessageBox.Show(string.Format("Instance with id <{0}> written to SQL", headerId));
if (sqlc.Connection.State == System.Data.ConnectionState.Open)
{
sqlc.Connection.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Message <{0}?>, inner: <{1}>, stack: <{2}>", ex.Message, ex.InnerException, ex.StackTrace), "Error when running SP");
}
}