public int inserir(Voos voo)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO voo(codigo, data, origem, destino) VALUES(?, ?, ?, ?)";
OdbcParameter codigo = new OdbcParameter("?", voo.Codigo);
OdbcParameter data = new OdbcParameter("?", voo.Data);
OdbcParameter origem = new OdbcParameter("?", voo.Origem);
OdbcParameter destino = new OdbcParameter("?", voo.Destino);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(codigo);
comando.Parameters.Add(data);
comando.Parameters.Add(origem);
comando.Parameters.Add(destino);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
public int inserir(Aviao aviao)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO aviao(numeroSerie, modelo) VALUES(?, ?)";
OdbcParameter numeroSerie = new OdbcParameter("?", aviao.NumeroSerie);
OdbcParameter modelo = new OdbcParameter("?", aviao.Modelo);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(numeroSerie);
comando.Parameters.Add(modelo);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
/// <summary>
/// Sobrescreve o metodo de Paramatro Retorno,adiciona o parametro de "@RETURN_VALUE".
/// </summary>
/// <returns>Retorna uma colecao de parametros com o parametro "@RETURN_VALUE" </returns>
internal override IDataParameter GetParametroRetorno()
{
OdbcParameter p = new OdbcParameter("@RETURN_VALUE",OdbcType.Int);
p.Direction = ParameterDirection.ReturnValue;
return p;
}
public int inserir(Cliente cliente)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO cliente(nome, cpf, cidade) VALUES(?, ?, ?)";
OdbcParameter nome = new OdbcParameter("?", cliente.Nome);
OdbcParameter cpf = new OdbcParameter("?", cliente.Cpf);
OdbcParameter cidade = new OdbcParameter("?", cliente.Cidade);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(nome);
comando.Parameters.Add(cpf);
comando.Parameters.Add(cidade);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
SysLog.AddDBLog(strSQL);
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand(strSQL, connection);
System.Data.Odbc.OdbcParameter myParameter = new System.Data.Odbc.OdbcParameter("@fs", OdbcType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.Odbc.OdbcException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// ����
/// </summary>
/// <param name="sql"></param>
public static int Execute(string sql, string connstr, OdbcParameter[] parameters)
{
int n = -1;
OdbcConnection conn = null;
try
{
conn = GetConnection(connstr);
OdbcCommand cmd = new OdbcCommand();//sql, conn);
cmd.CommandText = sql;
cmd.Connection = conn;
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.AddWithValue(parameters[i].ParameterName, parameters[i].Value);
}
conn.Open();
n = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
//MessageBox.Show( sql, "ϵͳ�����������Ա��ϵ", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new DatabaseException(sql + "\n" + e.Message);
}
finally
{
try { conn.Close(); }
catch { }
}
conn = null;
return n;
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
SysLog.AddDBLog(SQLString);
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
System.Data.Odbc.OdbcParameter myParameter = new System.Data.Odbc.OdbcParameter("@content", OdbcType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.Odbc.OdbcException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public int inserir(Reservas reserva)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO reserva(voo, assento, cpf_cliente) VALUES(?, ?, ?)";
OdbcParameter voo = new OdbcParameter("?", reserva.Voo);
OdbcParameter assento = new OdbcParameter("?", reserva.Assento);
OdbcParameter cpf = new OdbcParameter("?", reserva.Cpf_cliente);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(voo);
comando.Parameters.Add(assento);
comando.Parameters.Add(cpf);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
public IDbDataParameter CreateParameter(DbParameter par)
{
IDbDataParameter p = null;
switch (this.ProviderType)
{
// case ProviderType.MySql:
// p = new mysql.MySqlParameter();
// break;
case ProviderType.Odbc:
p = new odbc.OdbcParameter();
break;
case ProviderType.OleDb:
p = new oledb.OleDbParameter();
break;
// case ProviderType.Oracle:
// p = new ora.OracleParameter();
// break;
case ProviderType.Sql:
p = new sql.SqlParameter();
break;
}
if (p != null)
{
p.ParameterName = par.ParameterName;
p.Direction = par.Direction;
p.Value = par.Value;
p.DbType = par.DbType;
}
return(p);
}
public void add_parameter(long value)
{
OdbcParameter param = new OdbcParameter();
param.DbType = DbType.Int64;
param.Value = value;
_parameters.Add(param);
}
public int inserir(Aeroportos aeroporto)
{
OdbcCommand comando;
int result = 0;
String comand = "INSERT INTO aeroporto(nome, cidade, codigo) VALUES(?, ?, ?)";
OdbcParameter nome = new OdbcParameter("?", aeroporto.Nome);
OdbcParameter cidade = new OdbcParameter("?", aeroporto.Cidade);
OdbcParameter codigo = new OdbcParameter("?", aeroporto.Codigo);
try
{
comando = new OdbcCommand(comand, conexao);
comando.Connection.Open();
comando.Parameters.Add(nome);
comando.Parameters.Add(cidade);
comando.Parameters.Add(codigo);
result = comando.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conexao.Close();
}
return result;
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
SysLog.AddDBLog(SQLString);
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
System.Data.Odbc.OdbcParameter myParameter = new System.Data.Odbc.OdbcParameter("@content", OdbcType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (System.Data.Odbc.OdbcException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// ��ȡһ���²�������
/// </summary>
/// <param name="paraName">������</param>
/// <param name="dbType">������������</param>
/// <param name="size">������С</param>
/// <returns>�ض�������Դ�IJ�������</returns>
public override IDataParameter GetParameter(string paraName,System.Data.DbType dbType,int size)
{
OdbcParameter para=new OdbcParameter();
para.ParameterName=paraName;
para.DbType=dbType;
para.Size=size;
return para;
}
public void add_parameter(string value)
{
OdbcParameter param = new OdbcParameter();
param.DbType = DbType.String;
param.Value = value;
_parameters.Add(param);
}
public void add_parameter(bool value)
{
OdbcParameter param = new OdbcParameter();
param.DbType = DbType.Boolean;
param.Value = value;
_parameters.Add(param);
}
public void add_parameter(double value)
{
OdbcParameter param = new OdbcParameter();
param.DbType = DbType.Double;
param.Value = value;
_parameters.Add(param);
}
private OdbcParameter(OdbcParameter source) : this()
{
ADP.CheckArgumentNull(source, "source");
source.CloneHelper(this);
ICloneable cloneable = this._value as ICloneable;
if (cloneable != null)
{
this._value = cloneable.Clone();
}
}
protected void AddParameter(string key, string val, int value)
{
if (lstDbParameters == null)
{
lstDbParameters = new List<DbParameter>();
}
OdbcParameter oParam = new OdbcParameter();
oParam.ParameterName = key;
oParam.Value = val;
lstDbParameters.Add(oParam);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public override DbParameter CreateParameter(string name, Enum dbType, byte? precision, byte? scale)
{
if (dbType is OdbcType)
{
var p = new OdbcParameter(name, (OdbcType)dbType);
if (precision.HasValue == true) p.Precision = precision.Value;
if (scale.HasValue == true) p.Scale = scale.Value;
return p;
}
throw new ArgumentException("dbType must be OdbcType.");
}
public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size)
{
OdbcParameter param;
if (Size > 0)
param = new OdbcParameter(ParamName, (OdbcType)DbType, Size);
else
param = new OdbcParameter(ParamName, (OdbcType)DbType);
return param;
}
public override DbParameter CréerDBParameter()
{
if (this.options.ConnexionODBC == true)
{
OdbcParameter param = new OdbcParameter();
return param;
}
else
{
SqlParameter param = new SqlParameter();
return param;
}
}
private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter)
{
IDbDataParameter clone = new OdbcParameter();
((OdbcParameter)clone).IsNullable = dbParameter.IsNullable;
clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
clone.DbType = dbParameter.DbType;
clone.Direction = dbParameter.Direction;
clone.Precision = dbParameter.Precision;
clone.Scale = dbParameter.Scale;
clone.Size = dbParameter.Size;
clone.SourceColumn = dbParameter.SourceColumn;
clone.SourceVersion = dbParameter.SourceVersion;
clone.Value = dbParameter.Value;
return clone;
}
public bool InsertReport()
{
bool result = false;
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = "{CALL Report_insert(?,?,?,?,?)}";
command.CommandType = CommandType.StoredProcedure;
OdbcParameter paramColModel = new OdbcParameter("@ColModel", OdbcType.VarChar, 5000);
paramColModel.Value = this.ColModel;
command.Parameters.Add(paramColModel);
OdbcParameter paramColNames = new OdbcParameter("@ColNames", OdbcType.VarChar, 500);
paramColNames.Value = this.ColNames;
command.Parameters.Add(paramColNames);
OdbcParameter paramCurrentData = new OdbcParameter("@CurrentData", OdbcType.VarChar, 5000);
paramCurrentData.Value = this.ReportName;
command.Parameters.Add(paramCurrentData);
OdbcParameter paramReportName = new OdbcParameter("@ReportName", OdbcType.VarChar, 200);
paramReportName.Value = this.ReportName;
command.Parameters.Add(paramReportName);
OdbcParameter paramTableName = new OdbcParameter("@TableName", OdbcType.VarChar, 50);
paramTableName.Value = this.TableName;
command.Parameters.Add(paramTableName);
connection.Open();
int n = command.ExecuteNonQuery();
if (n == 1)
result = true;
else
result = false;
}
}
return result;
}
public void InsertTest ()
{
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
ConnectionManager.Singleton.OpenConnection ();
try {
OdbcCommand OdbcCmd = conn.CreateCommand ();
OdbcCmd.CommandType = CommandType.Text;
OdbcCmd.CommandText = "SELECT fname FROM employee WHERE lname=? AND age=?";
/*OdbcParameter p1Lname = */OdbcCmd.Parameters.Add ("@lname", OdbcType.Text, 15);
OdbcParameter p2Age = new OdbcParameter (); //not initialized and not yet added
OdbcParameter p3Tmp = new OdbcParameter ("p3", "abc"); //not added
Assert.AreEqual (1, OdbcCmd.Parameters.Count, "#1 Initialization error, the collection does not contain desired no. of parameters");
OdbcCmd.Parameters.Insert (1, p2Age); //Inserting the second parameter
Assert.AreEqual (2, OdbcCmd.Parameters.Count, "#2 The collection should contain 2 parameters");
//inserting at upper boundary
OdbcCmd.Parameters.Insert (OdbcCmd.Parameters.Count, p3Tmp); //Inserting the third parameter, with name and value at index = count
Assert.AreEqual (3, OdbcCmd.Parameters.Count, "#3 The collection should contain 2 parameters");
//check the inserted positions
Assert.AreEqual (0, OdbcCmd.Parameters.IndexOf ("@lname"), "#4 The first parameter must be at index 0");
Assert.AreEqual (1, OdbcCmd.Parameters.IndexOf (p2Age), "#5 The inserted parameter p2Age must be at index 1");
Assert.AreEqual (2, OdbcCmd.Parameters.IndexOf (p3Tmp), "#6 The inserted parameter p3Tmp must be at index 2");
Assert.AreEqual (2, OdbcCmd.Parameters.IndexOf ("p3"), "#7 The inserted parameter p3 must be at index 2");
Assert.AreEqual (-1, OdbcCmd.Parameters.IndexOf ("non-existing-parameter"), "#8 Non-existing parameters should return -1");
//check for default names and default values
Assert.AreEqual ("Parameter1", OdbcCmd.Parameters[1].ParameterName, "#9 Parameters inserted without any name must get a default name");
Assert.AreEqual (null, OdbcCmd.Parameters[1].Value, "#10 Parameters inserted without any value must have null value");
Assert.AreEqual ("p3", OdbcCmd.Parameters[2].ParameterName, "#11 Parameters inserted without any name must get a default name");
Assert.AreEqual ("abc", OdbcCmd.Parameters[2].Value, "#12 Parameters inserted without any value must have null value");
Assert.AreEqual (OdbcType.NVarChar,OdbcCmd.Parameters[1].OdbcType, "#13 Parameters with null value must be of type NVarChar");
Assert.AreEqual (OdbcType.Text,OdbcCmd.Parameters[0].OdbcType, "#14 Parameter at index 0 is of type Text");
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
}
public void OdbcTypeTest ()
{
OdbcParameter param = new OdbcParameter ();
Assert.AreEqual (OdbcType.NVarChar, param.OdbcType, "#1");
// change test
param.OdbcType = OdbcType.Int;
Assert.AreEqual (OdbcType.Int, param.OdbcType, "#2");
param = new OdbcParameter ("test", 10);
Assert.AreEqual (OdbcType.Int, param.OdbcType, "#3");
param.OdbcType = OdbcType.Real;
Assert.AreEqual (OdbcType.Real, param.OdbcType, "#4");
Assert.AreEqual (10, param.Value, "#5");
param = new OdbcParameter ("test", OdbcType.NText);
Assert.AreEqual (null, param.Value, "#6");
Assert.AreEqual (OdbcType.NText, param.OdbcType, "#7");
param = new OdbcParameter ("test", OdbcType.Binary);
Assert.AreEqual (null, param.Value, "#8");
Assert.AreEqual (OdbcType.Binary, param.OdbcType, "#9");
}
/// <summary>
/// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters
/// (including parameter Value)</param>
/// <returns>SQL WHERE criteria
/// </returns>
private String BuildCustomWhereCriteriaForBankDetails(DataTable ACriteriaData, out OdbcParameter[] AParametersArray)
{
String CustomWhereCriteria = "";
DataTable CriteriaDataTable;
DataRow CriteriaRow;
ArrayList InternalParameters;
CriteriaDataTable = ACriteriaData;
CriteriaRow = CriteriaDataTable.Rows[0];
InternalParameters = new ArrayList();
if (CriteriaRow["PartnerName"].ToString().Length > 0)
{
// Searched DB Field: 'p_partner_short_name_c'
new TDynamicSearchHelper(PPartnerTable.TableId,
PPartnerTable.ColumnPartnerShortNameId, CriteriaRow, "PartnerName", "PartnerNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
if (CriteriaRow["PartnerClass"].ToString() != "*")
{
// Split String into String Array is Restricted Partner Classes are being used
string[] Classes = CriteriaRow["PartnerClass"].ToString().Split(new Char[] { (',') });
String Criteria = null;
foreach (string Class in Classes)
{
if (Class != "*")
{
if (Criteria == null)
{
Criteria = " AND (";
}
else
{
Criteria += " OR ";
}
// Searched DB Field: 'p_partner_class_c': done manually!
Criteria = String.Format("{0} PUB.{1}.{2} = ?", Criteria,
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerClassDBName());
OdbcParameter miParam = TTypedDataTable.CreateOdbcParameter(PPartnerTable.TableId, PPartnerTable.ColumnPartnerClassId);
miParam.Value = (object)Class;
InternalParameters.Add(miParam);
}
}
CustomWhereCriteria += Criteria + ")";
}
// Searched DB Field: 'p_bank_account_number_c'
if (CriteriaRow["AccountNumber"].ToString().Length > 0)
{
new TDynamicSearchHelper(PBankingDetailsTable.TableId,
PBankingDetailsTable.ColumnBankAccountNumberId, CriteriaRow, "AccountNumber", "AccountNumberMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_account_name_c'
if (CriteriaRow["AccountName"].ToString().Length > 0)
{
new TDynamicSearchHelper(PBankingDetailsTable.TableId,
PBankingDetailsTable.ColumnAccountNameId, CriteriaRow, "AccountName", "AccountNameMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_account_name_c'
if (CriteriaRow["Iban"].ToString().Length > 0)
{
new TDynamicSearchHelper(PBankingDetailsTable.TableId,
PBankingDetailsTable.ColumnIbanId, CriteriaRow, "Iban", "IbanMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_account_name_c'
if (CriteriaRow["Bic"].ToString().Length > 0)
{
new TDynamicSearchHelper(PBankTable.TableId,
PBankTable.ColumnBicId, CriteriaRow, "Bic", "BicMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_account_name_c'
if (Convert.ToInt64(CriteriaRow["BankKey"]) > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
PBankTable.GetTableDBName() + "." + PBankTable.GetPartnerKeyDBName()); // CustomWhereCriteria + ' AND p_partner_key_n = ?';
OdbcParameter miParam = new OdbcParameter("", OdbcType.BigInt, 10);
miParam.Value = (object)(CriteriaRow["BankKey"]);
InternalParameters.Add(miParam);
}
if (((Boolean)(CriteriaRow["WorkerFamOnly"]) == true)
&& (CriteriaRow["PartnerClass"].ToString() == "FAMILY"))
{
// A custom subquery seems to only speedy way of doing this!
CustomWhereCriteria = String.Format(
"{0} AND EXISTS (select * FROM PUB.p_partner_gift_destination " +
"WHERE PUB.p_partner.p_partner_key_n = PUB.p_partner_gift_destination.p_partner_key_n " +
"AND (PUB.p_partner_gift_destination.p_date_expires_d IS NULL OR PUB.p_partner_gift_destination.p_date_effective_d <> PUB.p_partner_gift_destination.p_date_expires_d))",
CustomWhereCriteria);
}
#region Partner Status
if ((String)(CriteriaRow["PartnerStatus"]) == "ACTIVE")
{
// Searched DB Field: 'p_status_code_c'
// This will automatically exclude other peoples' PRIVATE Partners!
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria, // CustomWhereCriteria + ' AND p_status_code_c = ?';
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "ACTIVE";
InternalParameters.Add(miParam);
}
if ((String)(CriteriaRow["PartnerStatus"]) == "PRIVATE")
{
// Searched DB Fields: 'p_status_code_c', 'p_user_id_c'
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ? AND PUB.{3}.{4} = ?", CustomWhereCriteria, // CustomWhereCriteria + ' AND p_status_code_c = ?';
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetUserIdDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
// user must be current user
miParam = new OdbcParameter("", OdbcType.VarChar, 20);
miParam.Value = UserInfo.GUserInfo.UserID;
InternalParameters.Add(miParam);
}
if ((String)(CriteriaRow["PartnerStatus"]) == "ALL")
{
// Searched DB Fields: 'p_status_code_c', 'p_user_id_c'
// This must show all partners PLUS the users *own* PRIVATE partners
CustomWhereCriteria = String.Format("{0} AND ((PUB.{1}.{2} = ? AND PUB.{3}.{4} = ? ) OR PUB.{1}.{2} <> ? )",
CustomWhereCriteria,
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetUserIdDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
// user must be current user
miParam = new OdbcParameter("", OdbcType.VarChar, 20);
miParam.Value = UserInfo.GUserInfo.UserID;
InternalParameters.Add(miParam);
miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
}
#endregion
// TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria);
/* Convert ArrayList to a array of ODBCParameters
* seem to need to declare a type first
*/
AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))));
InternalParameters = null; // ensure this is GC'd
return CustomWhereCriteria;
}
/// <summary>
/// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters
/// (including parameter Value)</param>
/// <returns>SQL WHERE criteria
/// </returns>
private String BuildCustomWhereCriteria(DataTable ACriteriaData, out OdbcParameter[] AParametersArray)
{
String CustomWhereCriteria = "";
String PartnerKey;
DataTable CriteriaDataTable;
DataRow CriteriaRow;
ArrayList InternalParameters;
bool ExactPartnerKeyMatch;
Int32 pk_order;
Int32 pk_power;
Int64 pk_maxkey;
Int64 pk_minkey;
CriteriaDataTable = ACriteriaData;
CriteriaRow = CriteriaDataTable.Rows[0];
InternalParameters = new ArrayList();
if (CriteriaRow["PartnerName"].ToString().Length > 0)
{
// Searched DB Field: 'p_partner_short_name_c'
new TDynamicSearchHelper(PPartnerTable.TableId,
PPartnerTable.ColumnPartnerShortNameId, CriteriaRow, "PartnerName", "PartnerNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
if (CriteriaRow["PersonalName"].ToString().Length > 0)
{
// Searched DB Field: 'p_first_name_c'
// Search for family or person or both?
if (CriteriaRow["PartnerClass"].ToString() == "PERSON")
{
new TDynamicSearchHelper(PPersonTable.TableId,
PPersonTable.ColumnFirstNameId, CriteriaRow, "PersonalName", "PersonalNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
else if (CriteriaRow["PartnerClass"].ToString() == "FAMILY")
{
new TDynamicSearchHelper(PFamilyTable.TableId,
PFamilyTable.ColumnFirstNameId, CriteriaRow, "PersonalName", "PersonalNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
else if (CriteriaRow["PartnerClass"].ToString() == "*")
{
// search for first name in both family and person
String Criteria = " AND (";
String SubCriteria = "";
new TDynamicSearchHelper(PPersonTable.TableId,
PPersonTable.ColumnFirstNameId, CriteriaRow, "PersonalName", "PersonalNameMatch",
ref SubCriteria, ref InternalParameters);
Criteria += SubCriteria.Remove(0, 4) + " OR ";
SubCriteria = "";
new TDynamicSearchHelper(PFamilyTable.TableId,
PFamilyTable.ColumnFirstNameId, CriteriaRow, "PersonalName", "PersonalNameMatch",
ref SubCriteria, ref InternalParameters);
Criteria += SubCriteria.Remove(0, 4) + ")";
CustomWhereCriteria += Criteria;
}
}
if (CriteriaRow["PreviousName"].ToString().Length > 0)
{
// Searched DB Field: 'p_previous_name_c'
new TDynamicSearchHelper(PPartnerTable.TableId,
PPartnerTable.ColumnPreviousNameId, CriteriaRow, "PreviousName", "PreviousNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
if (CriteriaRow["PartnerClass"].ToString() != "*")
{
// Split String into String Array is Restricted Partner Classes are being used
string[] Classes = CriteriaRow["PartnerClass"].ToString().Split(new Char[] { (',') });
String Criteria = null;
foreach (string Class in Classes)
{
if (Class != "*")
{
if (Criteria == null)
{
Criteria = " AND (";
}
else
{
Criteria += " OR ";
}
// Searched DB Field: 'p_partner_class_c': done manually!
Criteria = String.Format("{0} PUB.{1}.{2} = ?", Criteria,
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerClassDBName());
OdbcParameter miParam = TTypedDataTable.CreateOdbcParameter(PPartnerTable.TableId, PPartnerTable.ColumnPartnerClassId);
miParam.Value = (object)Class;
InternalParameters.Add(miParam);
}
}
CustomWhereCriteria += Criteria + ")";
}
if (CriteriaRow["Address1"].ToString().Length > 0)
{
// Searched DB Field: 'p_locality_c'
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnLocalityId, CriteriaRow, "Address1", "Address1Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["Address2"].ToString().Length > 0)
{
// Searched DB Field: 'p_street_name_c'
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnStreetNameId, CriteriaRow, "Address2", "Address2Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["Address3"].ToString().Length > 0)
{
// Searched DB Field: 'p_address_3_c'
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnAddress3Id, CriteriaRow, "Address3", "Address3Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_city_c'
if (CriteriaRow["City"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnCityId, CriteriaRow, "City", "CityMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_postal_code_c'
if (CriteriaRow["PostCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnPostalCodeId, CriteriaRow, "PostCode", "PostCodeMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_county_c'
if (CriteriaRow["County"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnCountyId, CriteriaRow, "County", "CountyMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_country_code_c' (Country): done manually
if (CriteriaRow["Country"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria, PLocationTable.GetCountryCodeDBName()); // CustomWhereCriteria + ' AND p_country_code_c = ?';
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 8);
miParam.Value = (object)(CriteriaRow["Country"].ToString().ToUpper());
InternalParameters.Add(miParam);
}
// Searched DB Field: 'p_account_name_c' (AccountName): done manually
if (CriteriaRow["AccountName"].ToString().Length > 0)
{
// Searched DB Field: 'p_partner_short_name_c'
new TDynamicSearchHelper(PBankingDetailsTable.TableId,
PBankingDetailsTable.ColumnAccountNameId, CriteriaRow, "AccountName", "AccountNameMatch",
ref CustomWhereCriteria, ref InternalParameters);
}
if ((Boolean)(CriteriaRow["MailingAddressOnly"]) == true)
{
// Searched DB Fields: 'p_send_mail_l', 'p_date_effective_d', 'p_date_good_until_d'
CustomWhereCriteria = String.Format(
"{0} AND (PUB.p_partner_location.p_send_mail_l = true) AND (PUB.p_partner_location.p_date_effective_d <= ?) and (PUB.p_partner_location.p_date_good_until_d >= ? or PUB.p_partner_location.p_date_good_until_d is null)",
CustomWhereCriteria,
PPartnerLocationTable.GetSendMailDBName()); // CustomWhereCriteria + ' AND p_send_mail_l = true'
OdbcParameter miParam = new OdbcParameter("", OdbcType.Date);
miParam.Value = System.DateTime.Now;
InternalParameters.Add(miParam);
miParam = new OdbcParameter("", OdbcType.Date);
miParam.Value = System.DateTime.Now;
InternalParameters.Add(miParam);
}
if (((Boolean)(CriteriaRow["WorkerFamOnly"]) == true)
&& (CriteriaRow["PartnerClass"].ToString().StartsWith("FAMILY")))
{
string temp =
"EXISTS (select * FROM PUB.p_partner_gift_destination " +
"WHERE PUB.p_partner.p_partner_key_n = PUB.p_partner_gift_destination.p_partner_key_n " +
"AND (PUB.p_partner_gift_destination.p_date_expires_d IS NULL OR PUB.p_partner_gift_destination.p_date_effective_d <> PUB.p_partner_gift_destination.p_date_expires_d))";
// A custom subquery seems to only speedy way of doing this!
if (CriteriaRow["PartnerClass"].ToString() == "FAMILY")
{
CustomWhereCriteria = String.Format(
"{0} AND " + temp,
CustomWhereCriteria);
}
else
{
CustomWhereCriteria = String.Format(
"{0} AND (PUB.p_partner.p_partner_class_c = 'UNIT' OR " + temp + ")",
CustomWhereCriteria);
}
}
if (CriteriaRow["LocationKey"].ToString().Length > 0)
{
// Searched DB Field: 'p_location_key_i'
// DISREGARD ALL OTHER SEARCH CRITERIA!!!
CustomWhereCriteria = "";
InternalParameters.Clear();
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria,
PLocationTable.GetTableDBName(), PLocationTable.GetLocationKeyDBName());
// CustomWhereCriteria + '
// AND p_location_key_i = ?';
OdbcParameter miParam = new OdbcParameter("", OdbcType.Decimal, 10);
miParam.Value = (object)CriteriaRow["LocationKey"];
InternalParameters.Add(miParam);
// if search is restricted then add this to the search criteria
AddRestrictedClassesToCriteria(CriteriaRow, ref InternalParameters, ref CustomWhereCriteria);
}
// if AFindCriteria.Contains('Partner Key') then
if ((Int64)(CriteriaRow["PartnerKey"]) != 0)
{
// Searched DB Field: 'p_partner_key_n'
PartnerKey = CriteriaRow["PartnerKey"].ToString();
ExactPartnerKeyMatch = (Boolean)(CriteriaRow["ExactPartnerKeyMatch"]);
if (ExactPartnerKeyMatch == false)
{
/* PARTNER KEY RANGE SEARCH:
* This was the method that was used by the Progress 4GL Partner Find screen.
*/
pk_minkey = System.Convert.ToInt64(PartnerKey);
pk_maxkey = 1;
// need to do TrimEnd('0').
pk_order = pk_minkey.ToString().Length -
pk_minkey.ToString().TrimEnd(new Char[] { ('0') }).Length;
for (pk_power = 1; pk_power <= pk_order; pk_power += 1)
{
pk_maxkey = pk_maxkey * 10;
}
pk_maxkey = pk_maxkey + pk_minkey - 1;
CustomWhereCriteria = String.Format("{0} AND {1} BETWEEN ? AND ?",
CustomWhereCriteria,
"PUB.p_partner.p_partner_key_n");
OdbcParameter miParam = new OdbcParameter("", OdbcType.Decimal, 10);
miParam.Value = (object)pk_minkey;
InternalParameters.Add(miParam);
miParam = new OdbcParameter("", OdbcType.Decimal, 10);
miParam.Value = (object)pk_maxkey;
InternalParameters.Add(miParam);
}
else
{
// EXACT PARTNER KEY SEARCH
// DISREGARD ALL OTHER SEARCH CRITERIA!!!
CustomWhereCriteria = "";
InternalParameters.Clear();
CustomWhereCriteria = String.Format("{0} AND {1} = ?",
CustomWhereCriteria,
"PUB.p_partner.p_partner_key_n");
// CustomWhereCriteria := CustomWhereCriteria + ' AND PUB_p_partner.p_partner_key_n = ?';
OdbcParameter miParam = new OdbcParameter("", OdbcType.Decimal, 10);
miParam.Value = (object)CriteriaRow["PartnerKey"];
InternalParameters.Add(miParam);
// if search is restricted then add this to the search criteria
AddRestrictedClassesToCriteria(CriteriaRow, ref InternalParameters, ref CustomWhereCriteria);
}
}
#region Partner Status
if ((String)(CriteriaRow["PartnerStatus"]) == "ACTIVE")
{
// Searched DB Field: 'p_status_code_c'
// This will automatically exclude other peoples' PRIVATE Partners!
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria, // CustomWhereCriteria + ' AND p_status_code_c = ?';
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "ACTIVE";
InternalParameters.Add(miParam);
}
if ((String)(CriteriaRow["PartnerStatus"]) == "PRIVATE")
{
// Searched DB Fields: 'p_status_code_c', 'p_user_id_c'
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ? AND PUB.{3}.{4} = ?", CustomWhereCriteria, // CustomWhereCriteria + ' AND p_status_code_c = ?';
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetUserIdDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
// user must be current user
miParam = new OdbcParameter("", OdbcType.VarChar, 20);
miParam.Value = UserInfo.GUserInfo.UserID;
InternalParameters.Add(miParam);
}
if ((String)(CriteriaRow["PartnerStatus"]) == "ALL")
{
// Searched DB Fields: 'p_status_code_c', 'p_user_id_c'
// This must show all partners PLUS the users *own* PRIVATE partners
CustomWhereCriteria = String.Format("{0} AND ((PUB.{1}.{2} = ? AND PUB.{3}.{4} = ? ) OR PUB.{1}.{2} <> ? )",
CustomWhereCriteria,
PPartnerTable.GetTableDBName(),
PPartnerTable.GetStatusCodeDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetUserIdDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
// user must be current user
miParam = new OdbcParameter("", OdbcType.VarChar, 20);
miParam.Value = UserInfo.GUserInfo.UserID;
InternalParameters.Add(miParam);
miParam = new OdbcParameter("", OdbcType.VarChar, 16);
miParam.Value = "PRIVATE";
InternalParameters.Add(miParam);
}
#endregion
// TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria);
/* Convert ArrayList to a array of ODBCParameters
* seem to need to declare a type first
*/
AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))));
InternalParameters = null; // ensure this is GC'd
return CustomWhereCriteria;
}
public int IndexOf(OdbcParameter value)
{
return(this.IndexOf(value));
}
public OdbcParameter Add(OdbcParameter value)
{
this.Add(value);
return(value);
}
public void Insert(int index, OdbcParameter value)
{
this.Insert(index, value);
}
public void Remove(OdbcParameter value)
{
this.Remove(value);
}
// DeriveParametersFromStoredProcedure (
// OdbcConnection connection,
// OdbcCommand command);
//
// Uses SQLProcedureColumns to create an array of OdbcParameters
//
static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command)
{
List <OdbcParameter> rParams = new List <OdbcParameter>();
// following call ensures that the command has a statement handle allocated
CMDWrapper cmdWrapper = command.GetStatementHandle();
OdbcStatementHandle hstmt = cmdWrapper.StatementHandle;
int cColsAffected;
// maps an enforced 4-part qualified string as follows
// parts[0] = null - ignored but removal would be a run-time breaking change from V1.0
// parts[1] = CatalogName (optional, may be null)
// parts[2] = SchemaName (optional, may be null)
// parts[3] = ProcedureName
//
string quote = connection.QuoteChar(ADP.DeriveParameters);
string[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, Res.ODBC_ODBCCommandText, false);
if (null == parts[3])
{ // match Everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace
parts[3] = command.CommandText;
}
// note: native odbc appears to ignore all but the procedure name
ODBC32.RetCode retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null);
// Note: the driver does not return an error if the given stored procedure does not exist
// therefore we cannot handle that case and just return not parameters.
if (ODBC32.RetCode.SUCCESS != retcode)
{
connection.HandleError(hstmt, retcode);
}
using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default))
{
reader.FirstResult();
cColsAffected = reader.FieldCount;
// go through the returned rows and filter out relevant parameter data
//
while (reader.Read())
{
// devnote: column types are specified in the ODBC Programmer's Reference
// COLUMN_TYPE Smallint 16bit
// COLUMN_SIZE Integer 32bit
// DECIMAL_DIGITS Smallint 16bit
// NUM_PREC_RADIX Smallint 16bit
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1);
switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1))
{
case ODBC32.SQL_PARAM.INPUT:
parameter.Direction = ParameterDirection.Input;
break;
case ODBC32.SQL_PARAM.OUTPUT:
parameter.Direction = ParameterDirection.Output;
break;
case ODBC32.SQL_PARAM.INPUT_OUTPUT:
parameter.Direction = ParameterDirection.InputOutput;
break;
case ODBC32.SQL_PARAM.RETURN_VALUE:
parameter.Direction = ParameterDirection.ReturnValue;
break;
default:
Debug.Assert(false, "Unexpected Parametertype while DeriveParamters");
break;
}
parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType;
parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE - 1);
switch (parameter.OdbcType)
{
case OdbcType.Decimal:
case OdbcType.Numeric:
parameter.ScaleInternal = (Byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS - 1);
parameter.PrecisionInternal = (Byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX - 1);
break;
}
rParams.Add(parameter);
}
}
retcode = hstmt.CloseCursor();
return(rParams.ToArray());;
}
/// <include file='doc\OdbcParameterConverter.uex' path='docs/doc[@for="OdbcParameterConverter.ConvertTo"]/*' />
/// <internalonly/>
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
if (destinationType == null)
{
throw ADP.ArgumentNull("destinationType");
}
if (destinationType == typeof(InstanceDescriptor) && value is OdbcParameter)
{
OdbcParameter p = (OdbcParameter)value;
// MDAC 67321 - reducing parameter generated code
int flags = 0; // if part of the collection - the parametername can't be empty
if (OdbcType.NChar != p.OdbcType)
{
flags |= 1;
}
if (0 != p.Size)
{
flags |= 2;
}
if (!ADP.IsEmpty(p.SourceColumn))
{
flags |= 4;
}
if (null != p.Value)
{
flags |= 8;
}
if ((ParameterDirection.Input != p.Direction) || p.IsNullable ||
(0 != p.Precision) || (0 != p.Scale) || (DataRowVersion.Current != p.SourceVersion))
{
flags |= 16;
}
Type[] ctorParams;
object[] ctorValues;
switch (flags)
{
case 0: // ParameterName
case 1: // SqlDbType
ctorParams = new Type[] { typeof(string), typeof(OdbcType) };
ctorValues = new object[] { p.ParameterName, p.OdbcType };
break;
case 2: // Size
case 3: // Size, SqlDbType
ctorParams = new Type[] { typeof(string), typeof(OdbcType), typeof(int) };
ctorValues = new object[] { p.ParameterName, p.OdbcType, p.Size };
break;
case 4: // SourceColumn
case 5: // SourceColumn, SqlDbType
case 6: // SourceColumn, Size
case 7: // SourceColumn, Size, SqlDbType
ctorParams = new Type[] { typeof(string), typeof(OdbcType), typeof(int), typeof(string) };
ctorValues = new object[] { p.ParameterName, p.OdbcType, p.Size, p.SourceColumn };
break;
case 8: // Value
ctorParams = new Type[] { typeof(string), typeof(object) };
ctorValues = new object[] { p.ParameterName, p.Value };
break;
default:
ctorParams = new Type[] {
typeof(string), typeof(OdbcType), typeof(int), typeof(ParameterDirection),
typeof(bool), typeof(byte), typeof(byte), typeof(string),
typeof(DataRowVersion), typeof(object)
};
ctorValues = new object[] {
p.ParameterName, p.OdbcType, p.Size, p.Direction,
p.IsNullable, p.Precision, p.Scale, p.SourceColumn,
p.SourceVersion, p.Value
};
break;
}
ConstructorInfo ctor = typeof(OdbcParameter).GetConstructor(ctorParams);
if (null != ctor)
{
return(new InstanceDescriptor(ctor, ctorValues));
}
}
return(base.ConvertTo(context, culture, value, destinationType));
}
public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
{
if (destinationType == null)
{
throw ADP.ArgumentNull("destinationType");
}
if ((destinationType == typeof(InstanceDescriptor)) && (value is OdbcParameter))
{
object[] objArray3;
Type[] typeArray3;
OdbcParameter parameter = (OdbcParameter)value;
int num = 0;
if (OdbcType.NChar != parameter.OdbcType)
{
num |= 1;
}
if (parameter.ShouldSerializeSize())
{
num |= 2;
}
if (!ADP.IsEmpty(parameter.SourceColumn))
{
num |= 4;
}
if (parameter.Value != null)
{
num |= 8;
}
if (((ParameterDirection.Input != parameter.Direction) || parameter.IsNullable) || ((parameter.ShouldSerializePrecision() || parameter.ShouldSerializeScale()) || (DataRowVersion.Current != parameter.SourceVersion)))
{
num |= 0x10;
}
if (parameter.SourceColumnNullMapping)
{
num |= 0x20;
}
switch (num)
{
case 0:
case 1:
typeArray3 = new Type[] { typeof(string), typeof(OdbcType) };
objArray3 = new object[] { parameter.ParameterName, parameter.OdbcType };
break;
case 2:
case 3:
typeArray3 = new Type[] { typeof(string), typeof(OdbcType), typeof(int) };
objArray3 = new object[] { parameter.ParameterName, parameter.OdbcType, parameter.Size };
break;
case 4:
case 5:
case 6:
case 7:
typeArray3 = new Type[] { typeof(string), typeof(OdbcType), typeof(int), typeof(string) };
objArray3 = new object[] { parameter.ParameterName, parameter.OdbcType, parameter.Size, parameter.SourceColumn };
break;
case 8:
typeArray3 = new Type[] { typeof(string), typeof(object) };
objArray3 = new object[] { parameter.ParameterName, parameter.Value };
break;
default:
if ((0x20 & num) == 0)
{
typeArray3 = new Type[] { typeof(string), typeof(OdbcType), typeof(int), typeof(ParameterDirection), typeof(bool), typeof(byte), typeof(byte), typeof(string), typeof(DataRowVersion), typeof(object) };
objArray3 = new object[] { parameter.ParameterName, parameter.OdbcType, parameter.Size, parameter.Direction, parameter.IsNullable, parameter.PrecisionInternal, parameter.ScaleInternal, parameter.SourceColumn, parameter.SourceVersion, parameter.Value };
}
else
{
typeArray3 = new Type[] { typeof(string), typeof(OdbcType), typeof(int), typeof(ParameterDirection), typeof(byte), typeof(byte), typeof(string), typeof(DataRowVersion), typeof(bool), typeof(object) };
objArray3 = new object[] { parameter.ParameterName, parameter.OdbcType, parameter.Size, parameter.Direction, parameter.PrecisionInternal, parameter.ScaleInternal, parameter.SourceColumn, parameter.SourceVersion, parameter.SourceColumnNullMapping, parameter.Value };
}
break;
}
ConstructorInfo constructor = typeof(OdbcParameter).GetConstructor(typeArray3);
if (null != constructor)
{
return(new InstanceDescriptor(constructor, objArray3));
}
}
return(base.ConvertTo(context, culture, value, destinationType));
}
private void UnloadTestData_GetBatchInfo()
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[2];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = FLedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[1].Value = strTestDataBatchDescription;
TDBTransaction transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.BeginAutoTransaction(ref transaction, ref SubmissionOK,
delegate
{
string strSQL = "DELETE FROM PUB_" + ABatchTable.GetTableDBName() + " ";
strSQL += "WHERE " + ABatchTable.GetLedgerNumberDBName() + " = ? " +
"AND " + ABatchTable.GetBatchDescriptionDBName() + " = ? ";
DBAccess.GDBAccessObj.ExecuteNonQuery(
strSQL, transaction, ParametersArray);
});
}
public void Unsuspense()
{
// The equivalent try/catch block that is used for Suspense() was removed 27 Jan 2015 in order to fix
// the issue in Mantis #3730
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[2];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ledgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[1].Value = strAcount;
TDBTransaction transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.BeginAutoTransaction(ref transaction, ref SubmissionOK,
delegate
{
string strSQL = "DELETE FROM PUB_" + ASuspenseAccountTable.GetTableDBName() + " ";
strSQL += "WHERE " + ASuspenseAccountTable.GetLedgerNumberDBName() + " = ? ";
strSQL += "AND " + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + " = ? ";
DBAccess.GDBAccessObj.ExecuteNonQuery(strSQL, transaction, ParametersArray);
});
}
// DeriveParametersFromStoredProcedure (
// OdbcConnection connection,
// OdbcCommand command);
//
// Uses SQLProcedureColumns to create an array of OdbcParameters
//
static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command)
{
ArrayList rParams = new ArrayList();
// following call ensures that the command has a statement handle allocated
HandleRef hstmt = command.GetStatementHandle();
int cColsAffected;
ODBC32.RETCODE retcode;
retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLProcedureColumnsW(
hstmt,
null, 0,
null, 0,
command.CommandText, (Int16)ODBC32.SQL_NTS,
null, 0);
// Note: the driver does not return an error if the given stored procedure does not exist
// therefore we cannot handle that case and just return not parameters.
if (ODBC32.RETCODE.SUCCESS != retcode)
{
connection.HandleError(hstmt, ODBC32.SQL_HANDLE.STMT, retcode);
}
OdbcDataReader reader = new OdbcDataReader(command, command._cmdWrapper, CommandBehavior.Default);
reader.FirstResult();
cColsAffected = reader.FieldCount;
// go through the returned rows and filter out relevant parameter data
//
while (reader.Read())
{
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1);
switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1))
{
case ODBC32.SQL_PARAM.INPUT:
parameter.Direction = ParameterDirection.Input;
break;
case ODBC32.SQL_PARAM.OUTPUT:
parameter.Direction = ParameterDirection.Output;
break;
case ODBC32.SQL_PARAM.INPUT_OUTPUT:
parameter.Direction = ParameterDirection.InputOutput;
break;
case ODBC32.SQL_PARAM.RETURN_VALUE:
parameter.Direction = ParameterDirection.ReturnValue;
break;
default:
Debug.Assert(false, "Unexpected Parametertype while DeriveParamters");
break;
}
parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType;
parameter.Size = reader.GetInt32(ODBC32.COLUMN_SIZE - 1);
switch (parameter.OdbcType)
{
case OdbcType.Decimal:
case OdbcType.Numeric:
parameter.Scale = (Byte)reader.GetInt32(ODBC32.DECIMAL_DIGITS - 1);
parameter.Precision = (Byte)reader.GetInt32(ODBC32.NUM_PREC_RADIX - 1);
break;
}
rParams.Add(parameter);
}
retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLCloseCursor(hstmt);
// Create a new Parameter array and copy over the ArrayList items
//
OdbcParameter[] pList = new OdbcParameter[rParams.Count];
for (int i = 0; i < rParams.Count; i++)
{
pList[i] = (OdbcParameter)rParams[i];
}
return(pList);
}
public override OdbcCommand GetOdbcCommand(string cmdsql, OdbcParameter[] paras)
{
OdbcCommand cmd = new OdbcCommand(cmdsql, mysqlconn);
if (paras != null)
for (int i = 0; i < paras.Length; i++)
{
cmd.Parameters.Add(paras[i]);
}
return cmd;
}
public OdbcParameter Add(OdbcParameter value)
{
// MDAC 59206
Add((object)value);
return(value);
}
public void Suspense()
{
try
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[2];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ledgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[1].Value = strAcount;
TDBTransaction transaction = null;
bool SubmissionOK = true;
DBAccess.GDBAccessObj.BeginAutoTransaction(ref transaction, ref SubmissionOK,
delegate
{
string strSQL = "INSERT INTO PUB_" + ASuspenseAccountTable.GetTableDBName() + " ";
strSQL += "(" + ASuspenseAccountTable.GetLedgerNumberDBName();
strSQL += "," + ASuspenseAccountTable.GetSuspenseAccountCodeDBName() + ") ";
strSQL += "VALUES ( ? , ? )";
DBAccess.GDBAccessObj.ExecuteNonQuery(strSQL, transaction, ParametersArray);
});
}
catch (Exception)
{
Assert.Fail("No database access to run the test");
}
}
public bool Contains(OdbcParameter value)
{
return(-1 != IndexOf(value));
}
private static IDbCommand GetCommandWithReturn(IDbCommand cmd, DataColumn[] PrimaryKey)
{
int i;
string s;
IDbDataParameter prm, prmKey;
DataColumn key;
IDbCommand cmdNew;
cmdNew = null;
if (cmd != null)
{
s = cmd.CommandText;
if (PrimaryKey != null && PrimaryKey.Length == 1)
{
key = PrimaryKey[0];
if (key.AutoIncrement && key.Unique)
s += string.Format("; select * from {0} where ({1} = @@identity)", key.Table.TableName, key.ColumnName);
}
//cmdNew = Ado.CreateCmd(cmd.Connection, s);
cmdNew = _ado.CreateCmd(cmd.Connection, s);
cmdNew.UpdatedRowSource = UpdateRowSource.Both;
for (i = 0; i < cmd.Parameters.Count; i++)
{
prmKey = (IDbDataParameter)cmd.Parameters[i];
prm = null;
if (cmd is OleDbCommand)
{
prm = new OleDbParameter();
((OleDbParameter)prm).OleDbType = ((OleDbParameter)prmKey).OleDbType;
}
else if (cmd is SqlCommand)
{
prm = new SqlParameter();
((SqlParameter)prm).SqlDbType = ((SqlParameter)prmKey).SqlDbType;
}
else if (cmd is OdbcCommand)
{
prm = new OdbcParameter();
((OdbcParameter)prm).OdbcType = ((OdbcParameter)prmKey).OdbcType;
}
if (prm != null)
{
prm.DbType = prmKey.DbType;
prm.Direction = prmKey.Direction;
prm.ParameterName = prmKey.ParameterName;
prm.Precision = prmKey.Precision;
prm.Scale = prmKey.Scale;
prm.Size = prmKey.Size;
prm.SourceColumn = prmKey.SourceColumn;
prm.SourceVersion = prmKey.SourceVersion;
cmdNew.Parameters.Add(prm);
}
}
}
return cmdNew;
}
public int IndexOf(OdbcParameter value)
{
return(IndexOf((object)value));
}
public virtual OdbcCommand GetOdbcCommand(String cmdsqsl, OdbcParameter[] paras)
{
return null;
}
public void Insert(int index, OdbcParameter value)
{
Insert(index, (object)value);
}
public static void Test_Various_DataTypes2()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, ";//6
sql += "c_decimal decimal(15,3), ";//7
sql += "c_double double, ";//8
sql += "c_char char, ";//9
sql += "c_varchar varchar(4096), ";//10
sql += "c_time time, ";//11
sql += "c_date date, ";//12
sql += "c_timestamp timestamp, ";//13
sql += "c_datetime datetime, ";//14
sql += "c_bit bit(1), ";//15
sql += "c_varbit bit varying(4096), ";//16
sql += "c_monetary monetary, ";//17
sql += "c_string string";//18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
using (OdbcCommand cmd = new OdbcCommand(null, conn))
{
sql = "insert into t values(?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?";
sql += ")";
OdbcParameter p1 = new OdbcParameter("?p1", OdbcType.Int);
p1.Value = 1;
cmd.Parameters.Add(p1);
OdbcParameter p2 = new OdbcParameter("?p2", OdbcType.SmallInt);
p2.Value = 11;
cmd.Parameters.Add(p2);
OdbcParameter p3 = new OdbcParameter("?p3", OdbcType.Int);
p3.Value = 111;
cmd.Parameters.Add(p3);
OdbcParameter p4 = new OdbcParameter("?p4", OdbcType.BigInt);
p4.Value = 1111;
cmd.Parameters.Add(p4);
OdbcParameter p5 = new OdbcParameter("?p5", OdbcType.Numeric);
p5.Value = 1.1;
cmd.Parameters.Add(p5);
OdbcParameter p6 = new OdbcParameter("?p6", OdbcType.Double);
p6.Value = 1.11;
cmd.Parameters.Add(p6);
OdbcParameter p7 = new OdbcParameter("?p7", OdbcType.Decimal);
p7.Value = 1.111;
cmd.Parameters.Add(p7);
OdbcParameter p8 = new OdbcParameter("?p8", OdbcType.Double);
p8.Value = 1.1111;
cmd.Parameters.Add(p8);
OdbcParameter p9 = new OdbcParameter("?p9", OdbcType.Char);
p9.Value = 'a';
cmd.Parameters.Add(p9);
OdbcParameter p10 = new OdbcParameter("?p10", OdbcType.VarChar);
p10.Value = "abcdfghijk";
cmd.Parameters.Add(p10);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
public void Remove(OdbcParameter value)
{
Remove((object)value);
}
