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;
 }
Exemple #5
0
        /// <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;
        }
Exemple #7
0
        /// <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;
 }
Exemple #9
0
        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;
 }
Exemple #12
0
        /// <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();
                }
            }
        }
Exemple #13
0
 /// <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);
 }
Exemple #19
0
 /// <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;
        }
Exemple #23
0
    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 ();
			}
		}
Exemple #25
0
                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;
        }
Exemple #28
0
 public int IndexOf(OdbcParameter value)
 {
     return(this.IndexOf(value));
 }
Exemple #29
0
 public OdbcParameter Add(OdbcParameter value)
 {
     this.Add(value);
     return(value);
 }
Exemple #30
0
 public void Insert(int index, OdbcParameter value)
 {
     this.Insert(index, value);
 }
Exemple #31
0
 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));
        }
Exemple #34
0
            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);
        }
Exemple #38
0
 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;
 }
Exemple #39
0
 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");
            }
        }
Exemple #41
0
 public bool Contains(OdbcParameter value)
 {
     return(-1 != IndexOf(value));
 }
Exemple #42
0
        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;
        }
Exemple #43
0
 public int IndexOf(OdbcParameter value)
 {
     return(IndexOf((object)value));
 }
Exemple #44
0
 public virtual OdbcCommand GetOdbcCommand(String cmdsqsl, OdbcParameter[] paras)
 {
     return null;
 }
Exemple #45
0
 public void Insert(int index, OdbcParameter value)
 {
     Insert(index, (object)value);
 }
Exemple #46
0
        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);

        }
Exemple #47
0
 public void Remove(OdbcParameter value)
 {
     Remove((object)value);
 }