Exemple #1
0
        /// <summary>
        /// Adiciona um parâmetro à lista de parâmetros
        /// </summary>
        /// <param name="nomeParametro">Nome do parâmetro</param>
        /// <param name="tipoParametro">Tipo do parâmetro</param>
        /// <param name="valor">Valor do parâmetro</param>
        public void AddParam(string nomeParametro, OracleDbType tipoParametro, object valor)
        {
            // Remove o parâmetro caso já exista
            OracleParameter paramExiste = ListaParametros.Where(x => x.ParameterName.Equals(nomeParametro)).FirstOrDefault();

            if (paramExiste != null)
            {
                ListaParametros.Remove(paramExiste);
            }
            // Adiciona o parâmetro
            //OracleParameter param = new OracleParameter(nomeParametro, tipoParametro);
            OracleParameter param = new OracleParameter();

            param = new Oracle.DataAccess.Client.OracleParameter();
            param.ParameterName = nomeParametro;
            param.OracleDbType  = tipoParametro;
            param.Direction     = ParameterDirection.Input;

            if (tipoParametro == OracleDbType.Blob)
            {
                param.Size = ((byte[])valor).Length;
            }

            param.Value = valor;
            ListaParametros.Add(param);
        }
Exemple #2
0
        public bool new_customers(int id)
        {
            var con = new OracleConnection(constr);

            con.Open();
            var objCmd = new OracleCommand
            {
                Connection  = con,
                CommandText = "Tables.new_customers",
                CommandType = CommandType.StoredProcedure
            };
            var prm = new OracleParameter
            {
                Direction = ParameterDirection.ReturnValue,
                DbType    = DbType.Int64
            };

            objCmd.Parameters.Add(prm);
            objCmd.Parameters.Add("new_cust", OracleDbType.Int64).Direction = ParameterDirection.Input;
            objCmd.Parameters["new_cust"].Value = id;

            objCmd.ExecuteNonQuery();

            return(Convert.ToInt32(objCmd.Parameters[0].Value) == 1);
        }
Exemple #3
0
        private string SaveToDatabase(byte[] btReport, string id)
        {
            // Write to J Drive
            string filepath    = "";
            string timestamp   = DateTime.Now.ToString("MMddHHmmssfff");
            string filename    = "fax" + id + "_" + timestamp + ".pdf";
            string credentials = Data.getJCredential();

            String[] credentialsArray = credentials.Split("|".ToCharArray());
            string   COMPUTER_IP      = credentialsArray[0].ToString().Split(':')[1];
            string   DOMAIN           = credentialsArray[1].ToString().Split(':')[1];
            string   USER_NAME        = credentialsArray[2].ToString().Split(':')[1];
            string   PASSWORD         = credentialsArray[3].ToString().Split(':')[1];

            try
            {
                using (NetworkShareAccesser.Access(COMPUTER_IP, DOMAIN, USER_NAME, PASSWORD))
                {
                    filepath = "\\\\" + COMPUTER_IP + "\\j_drive\\Clients\\LifeEvent\\" + filename;

                    File.WriteAllBytes(filepath, btReport);

                    filepath = filepath.Replace("\\\\" + COMPUTER_IP + "\\j_drive\\", "J:\\");
                }
            }
            catch
            {
            }
            // Save to Database
            Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
            try
            {
                Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.SaveFaxImage", conn);
                cmd.CommandType    = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 30;
                SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]);
                SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", SQLStatic.Sessions.GetUserName(HttpContext.Current.Request.Cookies["Session_ID"].Value.ToString()));
                Oracle.DataAccess.Client.OracleParameter parm;
                parm = new Oracle.DataAccess.Client.OracleParameter(
                    "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
                    ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
                cmd.Parameters.Add(parm);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Dispose();
                }
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return(filename);
        }
 private void CreateOracleParameter(IDbCommand command, OracleDataAccessClient.OracleCommand commandNew)
 {
     foreach (OracleParameter para in command.Parameters)
     {
         OracleDataAccessClient.OracleDbType    oracleType = ConvertOracleType(para.OracleType);
         OracleDataAccessClient.OracleParameter paraNew    = new OracleDataAccessClient.OracleParameter(para.ParameterName, oracleType);
         paraNew.Value = para.Value;
         commandNew.Parameters.Add(paraNew);
     }
 }
Exemple #5
0
        public IDbDataParameter CreateParameter()
        {
            var name = this.Name;

            switch (this.DbType.DatabaseServer)
            {
            case DatabaseServer.SqlServer:
            {
                var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                p.Value     = GetParameterValue(this.DbType.SqlServerDbType.Value);
                return(p);
            }

            case DatabaseServer.Oracle:
            {
                var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                //p.Value = GetParameterValue(this.OracleServerDbType.Value);
                return(p);
            }

            case DatabaseServer.MySql:
            {
                var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                p.Value     = GetParameterValue(this.DbType.MySqlServerDbType.Value);
                return(p);
            }

            case DatabaseServer.PostgreSql:
            {
                var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value);
                p.Direction = ParameterDirection.Input;
                //p.Value = GetParameterValue(this.MySqlDbType.Value);
                return(p);
            }

            default: throw new InvalidOperationException();
            }
        }
Exemple #6
0
        public static Oracle.DataAccess.Client.OracleParameter[] GetParamArray <T>(T t)
        {
            string tStr = string.Empty;

            if (t == null)
            {
                return(null);
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return(null);
            }
            System.Collections.Generic.List <Oracle.DataAccess.Client.OracleParameter> list = new System.Collections.Generic.List <Oracle.DataAccess.Client.OracleParameter>();
            Oracle.DataAccess.Client.OracleParameter[] paramArray = new Oracle.DataAccess.Client.OracleParameter[properties.Length];

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                string type  = properties[i].PropertyType.Name;

                Oracle.DataAccess.Client.OracleParameter parm = new Oracle.DataAccess.Client.OracleParameter();
                parm.ParameterName = ":" + name;

                if (null == value)
                {
                    //parm = new Oracle.DataAccess.Client.OracleParameter(":" + name, DBNull.Value);
                    parm.Value = DBNull.Value;
                }
                else
                {
                    //parm = new Oracle.DataAccess.Client.OracleParameter(":" + name, value);
                    parm.Value = value;
                }
                list.Add(parm);
                break;
            }

            return(list.ToArray());
        }
Exemple #7
0
        private void SaveToDatabase2(byte[] btReport)
        {
            // SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString();

            //string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]);

            //if (reccord_id.Equals("-1"))
            //    return;

            //SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id);

            //SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", btReport);

            Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
            try
            {
                Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn);
                cmd.CommandType    = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 30;
                SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]);
                Oracle.DataAccess.Client.OracleParameter parm;
                parm = new Oracle.DataAccess.Client.OracleParameter(
                    "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
                    ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
                cmd.Parameters.Add(parm);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Dispose();
                }
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
Exemple #8
0
        public int promotion_average_credit()
        {
            var con = new OracleConnection(constr);

            con.Open();
            var objCmd = new OracleCommand
            {
                Connection  = con,
                CommandText = "Tables.promotion_average_credit",
                CommandType = CommandType.StoredProcedure
            };
            var prm = new OracleParameter
            {
                Direction = ParameterDirection.ReturnValue,
                DbType    = DbType.Int64
            };

            objCmd.Parameters.Add(prm);

            objCmd.ExecuteNonQuery();

            return(Convert.ToInt32(objCmd.Parameters[0].Value));
        }
Exemple #9
0
        public IDbDataParameter CreateParameter()
        {
            var name = this.Name;

            switch (this.DbType.DatabaseServer)
            {
                case DatabaseServer.SqlServer:
                    {
                        var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value);
                        p.Direction = ParameterDirection.Input;
                        p.Value = GetParameterValue(this.DbType.SqlServerDbType.Value);
                        return p;
                    }
                case DatabaseServer.Oracle:
                    {
                        var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value);
                        p.Direction = ParameterDirection.Input;
                        //p.Value = GetParameterValue(this.OracleServerDbType.Value);
                        return p;
                    }
                case DatabaseServer.MySql:
                    {
                        var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value);
                        p.Direction = ParameterDirection.Input;
                        p.Value = GetParameterValue(this.DbType.MySqlServerDbType.Value);
                        return p;
                    }
                case DatabaseServer.PostgreSql:
                    {
                        var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value);
                        p.Direction = ParameterDirection.Input;
                        //p.Value = GetParameterValue(this.MySqlDbType.Value);
                        return p;
                    }
                default: throw new InvalidOperationException();
            }
        }
Exemple #10
0
        public DbParameter[] GetParamArray <T>(T t, MyDBType mydbtype)
        {
            string tStr = string.Empty;

            if (t == null)
            {
                return(null);
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return(null);
            }
            System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>();
            DbParameter[] paramArray = null;
            switch (mydbtype)
            {
            case MyDBType.Access:
                paramArray = new System.Data.OleDb.OleDbParameter[properties.Length];
                break;

            case MyDBType.Sql:
                paramArray = new System.Data.SqlClient.SqlParameter[properties.Length];
                break;

            case MyDBType.Oracle:
                paramArray = new Oracle.DataAccess.Client.OracleParameter[properties.Length];
                break;
            }

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                string type  = properties[i].PropertyType.Name;

                switch (mydbtype)
                {
                case MyDBType.Access:
                    if (null == value)
                    {
                        paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, value);
                    }
                    break;

                case MyDBType.Sql:
                    if (null == value)
                    {
                        paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, value);
                    }
                    break;

                case MyDBType.Oracle:
                    if (null == value)
                    {
                        paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, DBNull.Value);
                    }
                    else
                    {
                        paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value);
                    }
                    break;
                }
            }

            return(paramArray);
        }
Exemple #11
0
        public DbParameter[] GetSelectParamArray <T>(T t, MyDBType mydbtype)
        {
            string tStr = string.Empty;

            if (t == null)
            {
                return(null);
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return(null);
            }
            System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>();
            DbParameter[] paramArray = null;
            int           lenght     = 0;

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                if (value != null)
                {
                    if (CanAdd(name, t.GetType().Name))
                    {
                        lenght++;
                    }
                }
            }
            switch (mydbtype)
            {
            case MyDBType.Access:
                paramArray = new System.Data.OleDb.OleDbParameter[lenght];
                break;

            case MyDBType.Sql:
                paramArray = new System.Data.SqlClient.SqlParameter[lenght];
                break;

            case MyDBType.Oracle:
                paramArray = new Oracle.DataAccess.Client.OracleParameter[lenght];
                break;
            }

            int index = 0;

            for (int i = 0; i < properties.Length; i++)
            {
                string name  = properties[i].Name;              //����
                object value = properties[i].GetValue(t, null); //ֵ
                string type  = properties[i].PropertyType.Name;
                if (value != null)
                {
                    if (CanAdd(name, t.GetType().Name))
                    {
                        switch (mydbtype)
                        {
                        case MyDBType.Access:
                            paramArray[index] = new System.Data.OleDb.OleDbParameter("@" + name, value);
                            break;

                        case MyDBType.Sql:
                            paramArray[index] = new System.Data.SqlClient.SqlParameter("@" + name, value);
                            break;

                        case MyDBType.Oracle:
                            paramArray[index] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value);
                            break;
                        }
                        index++;
                    }
                }
            }

            return(paramArray);
        }
Exemple #12
0
        private void SaveToDatabase(byte[] btReport)
        {
            // SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString();

            string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]);

            if (reccord_id.Equals("-1"))
            {
                return;
            }

            SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id);

            SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), ViewState["EE"].ToString() + "  " + Request.Params["DpNo"], btReport);

            Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
            try
            {
                Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn);
                cmd.CommandType    = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 30;
                SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", reccord_id);
                Oracle.DataAccess.Client.OracleParameter parm;
                parm = new Oracle.DataAccess.Client.OracleParameter(
                    "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true,
                    ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport);
                cmd.Parameters.Add(parm);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Dispose();
                }
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }



            // Here you save bytes to the database
            //using (OracleConnection con = new OracleConnection(ConnStr))
            //{
            //  using (OracleCommand cmd = new OracleCommand("PKG_Name.Procedure_Save_PDF", con))
            //  {
            //     con.Open();
            //     try
            //     {
            //        foreach (ReportParameters p in parameters)
            //        {
            //          try
            //          {
            //            // GenerateReportByteArray will return byte array for the database
            //            cmd.Parameters.Add("file_blob_", OracleDbType.Blob, ParameterDirection.Input);
            //            cmd.Parameters["file_blob_"].Value = btReport;
            //            cmd.ExecuteNonQuery();
            //          }
            //          catch (Exception ex)
            //          {
            //            Message = ex.Message;
            //          }
            //     }
            //     Message = "Insert done";
            //  }
            //  catch (Exception ex)
            //  {
            //                     Message = ex.Message;
            //  }
            //  finally
            //  {
            //    if (con.State != ConnectionState.Closed) con.Close();
            //  }
            //}
            //}
        }