Example #1
0
        public static DataTable GetCoalChartDataTable(int reportId, string key)
        {
            //check params
            if (string.IsNullOrEmpty(key))
            {
                return(new DataTable());
            }
            COALCHARTLEGEND chartL;

            using (var db = new CMAEntities())
            {
                chartL = db.COALCHARTLEGENDs.FirstOrDefault(o => o.REPORTID == reportId);
            }
            var sqlWhere = key.Replace("^|^", "=").Replace("||", " AND ");
            var sqlStr   = string.Format(chartL.CHARTSQL, sqlWhere);

            var ds = new DataSet();

            using (var zcxdb = new CNE_ZCXNewEntities())
            {
                using (var adp = new OracleDataAdapter(sqlStr, zcxdb.Database.Connection.ConnectionString))
                {
                    adp.Fill(ds);
                }
            }
            return(ds.Tables[0]);
        }
Example #2
0
        public List <PartitionBallanceTableData> GetPartitionBallenceData(string areaCode, string productCode, List <string> years)
        {
            var ds    = new DataTable();
            var paras = new[] { new OracleParameter("P_AreaCode", areaCode),
                                new OracleParameter("P_productCode", productCode),
                                new OracleParameter("P_myear1", years[2]),
                                new OracleParameter("P_myear2", years[1]),
                                new OracleParameter("P_myear3", years[0]),
                                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                                {
                                    Direction = ParameterDirection.Output
                                } };

            using (var zcxDb = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    zcxDb.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)zcxDb.Database.Connection;
                    spCmd.CommandText    = "GetPartitionBallenceData";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;
                    spCmd.Parameters.AddRange(paras);
                    var da = new OracleDataAdapter(spCmd);
                    da.Fill(ds);
                }
            }
            return(DataTableSerializer.ToList <PartitionBallanceTableData>(ds));
        }
        public DataTable GetHomeItemAll()
        {
            var paramArray = new[]
            {
                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cmaDb = new CMAEntities())
            {
                using (OracleCommand spCmd = new OracleCommand())
                {
                    cmaDb.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
                    spCmd.CommandText    = "GetHomeItemAll";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);

                    OracleDataAdapter da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    return(ds.Tables[0]);
                }
            }
        }
Example #4
0
        public List <BallanceFilter> GetAreaFilters(int reportId, string productCode)
        {
            var ds    = new DataTable();
            var paras = new[] { new OracleParameter("P_reportID", reportId),
                                new OracleParameter("P_ProductCode", productCode),
                                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                                {
                                    Direction = ParameterDirection.Output
                                } };

            using (var zcxDb = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    zcxDb.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)zcxDb.Database.Connection;
                    spCmd.CommandText    = "GetAreaFiltes";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;
                    spCmd.Parameters.AddRange(paras);
                    var da = new OracleDataAdapter(spCmd);
                    da.Fill(ds);
                }
            }
            return(DataTableSerializer.ToList <BallanceFilter>(ds));
        }
Example #5
0
        /// <summary>
        /// Get data by store procedure
        /// </summary>
        /// <param name="inName">sp name</param>
        /// <param name="inParms">parameters</param>
        /// <returns></returns>
        protected DataSet GetDataSetBySp(string inName, OracleParameter[] inParms)
        {
            using (var cnEDB = new CneNewEntities())
            {
                using (OracleCommand spCmd = new OracleCommand())
                {
                    DataSet ds = null;

                    cnEDB.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(cnEDB.Database.Connection.ConnectionString);
                    spCmd.CommandText    = inName;
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    if (inParms != null)
                    {
                        spCmd.Parameters.AddRange(inParms);
                    }

                    OracleDataAdapter da = new OracleDataAdapter(spCmd);
                    ds = new DataSet();
                    da.Fill(ds);

                    return(ds);
                }
            }
        }
Example #6
0
        public static List <CoalTrafficPortChartEntity> GetCoalTrafficPortData(string key)
        {
            const string proName = "CoalTrafficPort_ChartData";
            var          paras   = new[] {
                new OracleParameter("P_key", key),
                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };
            var chartTable = new DataTable();

            using (var cnE = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    cnE.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)(cnE.Database.Connection);
                    spCmd.CommandText    = proName;
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;
                    spCmd.Parameters.AddRange(paras);
                    var da = new OracleDataAdapter(spCmd);
                    da.Fill(chartTable);
                }
            }
            return(DataTableSerializer.ToList <CoalTrafficPortChartEntity>(chartTable));
        }
Example #7
0
 /// <summary>
 /// 執行SQL語句返回DataTable
 /// </summary>
 /// <param name="SQL">SQL語句</param>
 /// <param name="DBUrl">數據庫鏈接地址</param>
 /// <returns></returns>
 public DataTable GetDataTableBySQL(string SQL, string DBUrl)
 {
     if (DBUrl.ToString().Trim() == "" || DBUrl == null)
     {
         throw new Exception("數據庫鏈接地址不能為空");
     }
     // 获取与数据库的连接对象並且绑定连接字符串
     Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
     conn.Open(); //打開資源
                  //获取数据库操作对象
     Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
     try
     {
         cmd.CommandText = SQL;
         Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
         DataTable dataTable = new DataTable();
         adapter.Fill(dataTable);
         dataTable.TableName = "數據集";
         cmd.Dispose();  //釋放資源
         conn.Dispose(); //釋放資源
         conn.Close();   //關閉
         return(dataTable);
     }
     catch (Exception ex)
     {
         cmd.Dispose();  //釋放資源
         conn.Dispose(); //釋放資源
         conn.Close();   //關閉
         throw ex;
     }
 }
Example #8
0
        public static DataTable GetDataTableBySqlSettle(string conStr, string sql)
        {
            DataSet ds = new DataSet();

            OracleConnection  con = new OracleConnection(conStr);
            OracleCommand     cmd = new OracleCommand(sql, con);
            OracleDataAdapter da  = new OracleDataAdapter(cmd);

            da.Fill(ds);
            return(ds.Tables[0]);
        }
        public static DataTable GetDataTableBySql2(string sql)
        {
            DataSet ds = new DataSet();
            //string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=124.207.105.120)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl11g.us.oracle.com)));User Id=settle;Password=settle;";
            string            conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.130.23)(PORT=8903))(CONNECT_DATA=(SERVICE_NAME = settle_primary)));User Id=settle;Password=settle;";
            OracleConnection  con    = new OracleConnection(conStr);
            OracleCommand     cmd    = new OracleCommand(sql, con);
            OracleDataAdapter da     = new OracleDataAdapter(cmd);

            da.Fill(ds);
            return(ds.Tables[0]);
        }
Example #10
0
 /// <summary>
 /// 調用存儲返回String字符串信息和DataTable數據表格(最後兩個位置必須為返回參數,一個為輸出字符串另一個為游標,位置不能顛倒)
 /// <para>obj使用方法:new{ v_data=value, v_data1=value1,out_string="",out_cursor=""}</para>
 /// <para>注意:obj中v_data為存儲參數名稱,value為對應的值,out_string為輸出參數不需要輸入值, out_cursor為游標不需要輸入值</para>
 /// </summary>
 /// <param name="storageName">存儲名稱</param>
 /// <param name="DBUrl">數據庫鏈接地址</param>
 /// <param name="obj">存儲參數對象</param>
 /// <param name="dataTable">返回結果集</param>
 /// <returns></returns>
 public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
 {
     if (DBUrl.ToString().Trim() == "" || DBUrl == null)
     {
         throw new Exception("數據庫鏈接地址不能為空");
     }
     // 获取与数据库的连接对象並且绑定连接字符串
     Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
     conn.Open();//打開資源
     //获取数据库操作对象
     Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
     try
     {
         cmd.CommandText = storageName;                                                                        //存儲名稱
         cmd.CommandType = CommandType.StoredProcedure;
         PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); //獲取object中的字段名和值
         for (int i = 0; i < properties.Length; i++)
         {
             if (i == (properties.Length - 2))
             {                                                            //設定輸出的類型和值
                 cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, short.MaxValue).Direction = ParameterDirection.Output;
                 cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
             }
             else if (i == (properties.Length - 1))
             {                                                            //設定輸出的類型和值
                 cmd.Parameters.Add(properties[i].Name, Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                 cmd.Parameters[properties[i].Name].Value = DBNull.Value; //賦值
             }
             else
             {                                                                                 //設定輸入的類型和值
                 cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
                 cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null); //賦值
             }
         }
         DataTable dt = new DataTable();
         dt.TableName = "數據集";
         Oracle.ManagedDataAccess.Client.OracleDataAdapter oda = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
         oda.Fill(dt);
         dataTable = dt;                                                                           //返回數據結果集
         string message = cmd.Parameters[properties[properties.Length - 2].Name].Value.ToString(); //獲取輸出的字符串
         cmd.Dispose();                                                                            //釋放資源
         conn.Dispose();                                                                           //釋放資源
         conn.Close();                                                                             //關閉
         return(message);
     }
     catch (Exception ex)
     {
         cmd.Dispose();  //釋放資源
         conn.Dispose(); //釋放資源
         conn.Close();   //關閉
         throw ex;
     }
 }
Example #11
0
        public DataTable GetHomeItems(int moduleId, string itemName, int startPage, int pageSize, out int total)
        {
            var paramArray = new[]
            {
                new OracleParameter("P_ModuleId", OracleDbType.Int32)
                {
                    Value = moduleId
                },
                new OracleParameter("P_ItemName", OracleDbType.Varchar2)
                {
                    Value = itemName
                },
                new OracleParameter("P_StartPage", OracleDbType.Int32)
                {
                    Value = startPage
                },
                new OracleParameter("P_PageSize", OracleDbType.Int32)
                {
                    Value = pageSize
                },
                new OracleParameter("P_Total", OracleDbType.Int32, ParameterDirection.Output),
                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cmaDb = new CMAEntities())
            {
                using (OracleCommand spCmd = new OracleCommand())
                {
                    cmaDb.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
                    spCmd.CommandText    = "GetHomeItems";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);

                    OracleDataAdapter da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    object value;
                    value = spCmd.Parameters["P_Total"].Value;
                    total = value.ToString() != "null" ? Convert.ToInt32(value.ToString()) : 0;
                    return(ds.Tables[0]);
                }
            }
        }
Example #12
0
        public List <string> GetBallanceColumnNames(int reportId, string areacode, string productCode)
        {
            var ds = new DataTable();

            using (var zcxDb = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    string str = "select distinct MARKET_YEAR from GetBalanceTableData where length(MARKET_YEAR)=9 ";
                    if (!string.IsNullOrEmpty(areacode))
                    {
                        str += " and areacode=" + areacode;
                    }
                    if (!string.IsNullOrEmpty(productCode))
                    {
                        str += " and productCode=" + productCode;
                    }
                    str += " and reportid=" + reportId + " order by MARKET_YEAR desc";
                    zcxDb.Database.Connection.Open();
                    spCmd.Connection  = (OracleConnection)zcxDb.Database.Connection;
                    spCmd.CommandText = str;

                    spCmd.CommandTimeout = 0;
                    var da = new OracleDataAdapter(spCmd);
                    da.Fill(ds);
                }
            }
            var columnNames = new List <string>();
            var rowItor     = ds.Rows.GetEnumerator();

            while (rowItor.MoveNext())
            {
                string years     = ((DataRow)rowItor.Current)[0].ToString();
                int    startYear = Convert.ToInt32(years.Substring(0, 4));
                int    endYear   = Convert.ToInt32(years.Substring(5, 4));
                if (endYear == startYear + 1)
                {
                    columnNames.Add(years);
                }
            }
            if (columnNames.Count == 0)
            {
                columnNames.Add((DateTime.Now.Year - 1) + "/" + DateTime.Now.Year);
                columnNames.Add((DateTime.Now.Year - 2) + "/" + (DateTime.Now.Year - 1));
                columnNames.Add((DateTime.Now.Year - 3) + "/" + (DateTime.Now.Year - 2));
            }
            return(columnNames);
        }
Example #13
0
 public DataTable GetEnergyInvntoryData(string tableName, string columns, string order, string filter = "1=1")
 {
     using (var cnE = new CneNewEntities())
     {
         using (var spCmd = new OracleCommand())
         {
             var ds = new DataSet();
             cnE.Database.Connection.Open();
             spCmd.Connection     = (OracleConnection)cnE.Database.Connection;
             spCmd.CommandText    = "SELECT " + columns + " FROM " + tableName + " where " + filter + " order by " + order;
             spCmd.CommandTimeout = 0;
             var da = new OracleDataAdapter(spCmd);
             da.Fill(ds);
             return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
         }
     }
 }
Example #14
0
        public string getCode(int type, int order, OracleConnection conn)
        {
            string sql = "select * from SmmCode where TYPEC=" + type + "and ORDERC=" + order;

            using (OracleCommand cmd = new OracleCommand(sql, conn))
            {
                OracleDataAdapter sda = new OracleDataAdapter(cmd);
                DataTable         tb  = new DataTable();
                sda.Fill(tb);
                if (tb != null && tb.Rows.Count == 1)
                {
                    return(tb.Rows[0]["CODE"].ToString());
                }
                else
                {
                    return("");
                }
            }
        }
Example #15
0
        private List <string> GetCategoryIdWhichHasData(string tableName, string categoryName)
        {
            string proName = "select distinct " + categoryName + " from " + tableName;
            var    dt      = new DataTable();

            using (var cnE = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    cnE.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)(cnE.Database.Connection);
                    spCmd.CommandText    = proName;
                    spCmd.CommandType    = CommandType.Text;
                    spCmd.CommandTimeout = 0;
                    var da = new OracleDataAdapter(spCmd);
                    da.Fill(dt);
                }
            }
            return((from DataRow dr in dt.Rows select dr[0] == null ? "" : dr[0].ToString()).ToList());
        }
Example #16
0
        public void ImportData(DataTable tb, string[] filters, StringBuilder sb)
        {
            int updateRows = 0, insertRows = 0;

            using (OracleConnection conn = new OracleConnection(connstr))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = conn;
                    foreach (DataRow dr in tb.Rows)
                    {
                        string strWhere  = CreateFilter(filters, dr);
                        string sqlExists = "select * from " + tb.TableName + strWhere;
                        cmd.CommandText = sqlExists;
                        OracleDataAdapter sda = new OracleDataAdapter(cmd);
                        DataTable         t   = new DataTable();
                        sda.Fill(t);
                        if (t != null && t.Rows.Count == 1)
                        {
                            //更新数据;
                            string updatesql = CreateUpdateSql(tb, dr, strWhere);
                            cmd.CommandText = updatesql;
                            cmd.ExecuteNonQuery();
                            updateRows++;
                        }
                        else
                        {
                            //插入数据;
                            string updatesql = CreateInsertSql(tb, dr);
                            cmd.CommandText = updatesql;
                            cmd.ExecuteNonQuery();
                            insertRows++;
                        }
                    }
                }
                conn.Close();
                sb.Append(" update:" + updateRows + " insert:" + insertRows + "\r\n");
            }
        }
Example #17
0
        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                string connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.252.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=admin;Password=renda#weixin;";
                Oracle.ManagedDataAccess.Client.OracleConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(connString);

                con.Open();
                string sql = "SELECT * FROM TB_USERS"; // DemoOP是表T_TEST的user
                Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                DataSet ds = new DataSet();
                Oracle.ManagedDataAccess.Client.OracleDataAdapter da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
                var a = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Example #18
0
        public void AddUserToDb(USER user)
        {
            var userNameParam   = new OracleParameter("USERNAME", OracleDbType.Varchar2, user.USER_NAME, ParameterDirection.Input);
            var userPassParam   = new OracleParameter("PASSWORD", OracleDbType.Varchar2, user.USER_PASSWORD, ParameterDirection.Input);
            var userGenderParam = new OracleParameter("USERGENDER", OracleDbType.Varchar2, user.GENDER, ParameterDirection.Input);

            using (OracleConnection connection = new OracleConnection(_connectionString))
            {
                OracleCommand cmd = new OracleCommand("CREATEUSER", connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add(userNameParam);
                cmd.Parameters.Add(userPassParam);
                cmd.Parameters.Add(userGenderParam);

                connection.Open();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                cmd.ExecuteNonQuery();

                //  context.SaveChanges();
            }
        }
Example #19
0
        // retourne un DataSet avec le contenu d'une procedure qui retourne plusieurs résultats
        public DataSet GetSelectProc(string Nom_Procedure)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet       dataset = new DataSet();
                OracleCommand cmd     = new OracleCommand();
                cmd.Connection  = connection;
                cmd.CommandText = Nom_Procedure;
                cmd.CommandType = CommandType.StoredProcedure;
                OracleParameter curseur =
                    new OracleParameter("curseur", OracleDbType.RefCursor, ParameterDirection.Output);
                cmd.Parameters.Add(curseur);
                connection.Open();
                cmd.ExecuteNonQuery();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(dataset);

                cmd.Dispose();
                connection.Close();
                return(dataset);
            }
        }
Example #20
0
        public DataSet GetDataSetBySpFromCma(string inName, params OracleParameter[] inParams)
        {
            using (var vav = new CMAEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    vav.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)(vav.Database.Connection);
                    spCmd.CommandText    = inName;
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;
                    if (inParams != null)
                    {
                        spCmd.Parameters.AddRange(inParams);
                    }
                    var da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);

                    return(ds);
                }
            }
        }
Example #21
0
        private DataSet GetDataSetBySp(string inName, OracleParameter[] inParms, string outName, out object outValue)
        {
            using (var IPPDB = new IPPEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    IPPDB.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(IPPDB.Database.Connection.ConnectionString);
                    spCmd.CommandText    = inName;
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;
                    if (inParms != null)
                    {
                        spCmd.Parameters.AddRange(inParms);
                    }

                    var da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    outValue = spCmd.Parameters[outName].Value;
                    return(ds);
                }
            }
        }
Example #22
0
        public DataTable GetPagedTableData(IEnumerable <REPORTCOLUMNDEFINITION> columns, string tableName, string strOrder, string strWhere, int pageIndex, out int recordCount)
        {
            var strGetFields =
                columns.Select(x => x.COLUMN_NAME).Aggregate((a, b) => a + "," + string.Format("{0}", b));

            if (string.IsNullOrEmpty(tableName))
            {
                recordCount = 0;
                return(new DataTable());
            }
            else//20141027 yy Localization
            {
                if (IsEnglishCulture())
                {
                    tableName = tableName + "_EN";
                }
            }
            if (string.IsNullOrEmpty(strOrder))
            {
                strOrder = "parcode";
            }
            var paramArray = new[]
            {
                new OracleParameter("tblName", OracleDbType.NVarchar2)
                {
                    Value = tableName
                },
                new OracleParameter("strGetFields", OracleDbType.NVarchar2)
                {
                    Value = strGetFields
                },
                new OracleParameter("strOrder", OracleDbType.NVarchar2)
                {
                    Value = strOrder
                },
                new OracleParameter("strWhere", OracleDbType.NVarchar2)
                {
                    Value = strWhere
                },
                new OracleParameter("pageIndex", OracleDbType.Int32)
                {
                    Value = pageIndex
                },
                new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output)
                {
                    Value = 0
                },
                new OracleParameter("O_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cnEdb = new CNE_ZCXNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    cnEdb.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)(cnEdb.Database.Connection);
                    spCmd.CommandText    = "GetDataPaged";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);
                    var da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString());
                    return(ds.Tables[0]);
                }
            }
        }
Example #23
0
        public DbDataAdapter CreateDataAdapter(DbCommand command)
        {
            var adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter((Oracle.ManagedDataAccess.Client.OracleCommand)command);

            return(adapter);
        }
Example #24
0
        public DataSet Execute()
        {
            var ds = new DataSet();

            if (_sourceDb.Type.ToString() == "ORACLE")
            {
                using (var con = new OracleConnection(_sourceDb.Conn))
                {
                    foreach (var mapping in TableMappings)
                    {
                        using (var cmd = new OracleCommand())
                        {
                            cmd.Connection = con;
                            var sb = new StringBuilder();
                            sb.Append("select ");
                            foreach (var column in mapping.ColumnMappings)
                            {
                                sb.Append(column.Source + ",");
                            }
                            //_lastSyncTime = Convert.ToDateTime("2015-01-10 11:00:00 PM");
                            sb.Remove(sb.Length - 1, 1);
                            sb.Append(" from " + mapping.Source);
                            sb.Append(" where " + mapping.Filter.Replace("{LastSyncTime}", _lastSyncTime.ToString(_oracleFormatString, CultureInfo.InvariantCulture)).Replace("{CurrentSyncTime}", _currentSyncTime.ToString(_oracleFormatString, CultureInfo.InvariantCulture)));
                            cmd.CommandText    = sb.ToString();
                            cmd.CommandTimeout = _sqlCommandTimeout;
                            var da = new OracleDataAdapter(cmd);
                            da.Fill(ds, mapping.Destination);
                        }
                    }
                }
            }
            else
            {
                using (var con = new SqlConnection(_sourceDb.Conn))
                {
                    foreach (var mapping in TableMappings)
                    {
                        using (var cmd = new SqlCommand())
                        {
                            cmd.Connection = con;
                            var sb = new StringBuilder();
                            sb.Append("select ");
                            foreach (var column in mapping.ColumnMappings)
                            {
                                sb.Append(column.Source + ",");
                            }
                            //_lastSyncTime = Convert.ToDateTime("2015-01-10 11:00:00 PM");
                            sb.Remove(sb.Length - 1, 1);
                            sb.Append(" from " + mapping.Source);
                            sb.Append(" where " + mapping.Filter.Replace("{LastSyncTime}", _lastSyncTime.ToString(_formatString, CultureInfo.InvariantCulture)).Replace("{CurrentSyncTime}", _currentSyncTime.ToString(_formatString, CultureInfo.InvariantCulture)));
                            cmd.CommandText    = sb.ToString();
                            cmd.CommandTimeout = _sqlCommandTimeout;
                            var da = new SqlDataAdapter(cmd);
                            da.Fill(ds, mapping.Destination);
                        }
                    }
                }
            }

            return(ds);
        }
Example #25
0
        public DataTable GetAbsBondList(DateTime startDate, DateTime endDate, string bondClass, string couponClass, string option, string bondRating, string isBondCode, string bondCodeOrIss, string columnList, int startPage, int pageSize, out int total)
        {
            var paramArray = new[]
            {
                new OracleParameter("P_StartDate", OracleDbType.TimeStamp)
                {
                    Value = (OracleTimeStamp)startDate
                },
                new OracleParameter("P_EndDate", OracleDbType.TimeStamp)
                {
                    Value = (OracleTimeStamp)endDate
                },
                new OracleParameter("P_BondClass", OracleDbType.Varchar2)
                {
                    Value = bondClass
                },
                new OracleParameter("P_CouponClass", OracleDbType.Varchar2)
                {
                    Value = couponClass
                },
                new OracleParameter("P_Option", OracleDbType.Varchar2)
                {
                    Value = option
                },
                new OracleParameter("P_BondRating", OracleDbType.Varchar2)
                {
                    Value = bondRating
                },
                new OracleParameter("P_IsBondeCode", OracleDbType.Varchar2)
                {
                    Value = isBondCode
                },
                new OracleParameter("P_BondCodeOrIss", OracleDbType.NVarchar2)
                {
                    Value = bondCodeOrIss
                },
                new OracleParameter("P_Culture", OracleDbType.Varchar2)
                {
                    Value = Thread.CurrentThread.CurrentUICulture.Name
                },
                new OracleParameter("P_ColumnList", OracleDbType.Varchar2)
                {
                    Value = columnList
                },
                new OracleParameter("P_StartPage", OracleDbType.Int32)
                {
                    Value = startPage
                },
                new OracleParameter("P_PageSize", OracleDbType.Int32)
                {
                    Value = pageSize
                },
                new OracleParameter("P_TOTAL", OracleDbType.Int32, ParameterDirection.Output)
                {
                    Value = 0
                },
                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cmaDb = new ZCXEntities())
            {
                using (OracleCommand spCmd = new OracleCommand())
                {
                    cmaDb.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
                    spCmd.CommandText    = "GetAbsListBondInfo";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);

                    OracleDataAdapter da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    total = Convert.ToInt32(spCmd.Parameters["P_TOTAL"].Value.ToString());
                    return(ds.Tables[0]);
                }
            }
        }
Example #26
0
        public DataTable GetDataPaged(string tableName, string strGetFields, string strOrder, string strWhere, int pageIndex, int pageSize, int doCount, int isExcel, out int recordCount)
        {
            var paramArray = new[]
            {
                new OracleParameter("tblName", OracleDbType.NVarchar2)
                {
                    Value = tableName
                },
                new OracleParameter("strGetFields", OracleDbType.NVarchar2)
                {
                    Value = strGetFields
                },
                new OracleParameter("strOrder", OracleDbType.NVarchar2)
                {
                    Value = strOrder
                },
                new OracleParameter("strWhere", OracleDbType.NVarchar2)
                {
                    Value = strWhere
                },
                new OracleParameter("pageIndex", OracleDbType.Int32)
                {
                    Value = pageIndex
                },
                new OracleParameter("pageSize", OracleDbType.Int32)
                {
                    Value = pageSize
                },
                new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output)
                {
                    Value = pageSize
                },
                new OracleParameter("doCount", OracleDbType.Int32)
                {
                    Value = doCount
                },
                new OracleParameter("isExcelReport", OracleDbType.Int32)
                {
                    Value = isExcel
                },
                new OracleParameter("O_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cnEDB = new CneNewEntities())
            {
                using (var spCmd = new OracleCommand())
                {
                    cnEDB.Database.Connection.Open();
                    spCmd.Connection     = (OracleConnection)(cnEDB.Database.Connection);
                    spCmd.CommandText    = "GetDataPaged";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);
                    var da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString());
                    return(ds.Tables[0]);
                }
            }
        }
Example #27
0
        public DataTable GetChinaSecurities(string category, string title, string code, DateTime startDate, DateTime endDate, out int total, int startPage = 1, int pageSize = 50)
        {
            var paramArray = new[]
            {
                new OracleParameter("P_Category", OracleDbType.Varchar2)
                {
                    Value = category
                },
                new OracleParameter("P_Title", OracleDbType.Varchar2)
                {
                    Value = title
                },
                new OracleParameter("P_Code", OracleDbType.Varchar2)
                {
                    Value = code
                },
                new OracleParameter("P_StartDate", OracleDbType.TimeStamp)
                {
                    Value = (OracleTimeStamp)startDate
                },
                new OracleParameter("P_EndDate", OracleDbType.TimeStamp)
                {
                    Value = (OracleTimeStamp)endDate
                },
                new OracleParameter("P_StartPage", OracleDbType.Int32)
                {
                    Value = startPage
                },
                new OracleParameter("P_PageSize", OracleDbType.Int32)
                {
                    Value = pageSize
                },
                new OracleParameter("P_TOTAL", OracleDbType.Int32, ParameterDirection.Output)
                {
                    Value = 0
                },
                new OracleParameter("P_CUR", OracleDbType.RefCursor)
                {
                    Direction = ParameterDirection.Output
                }
            };

            using (var cmaDb = new CMAEntities())
            {
                using (OracleCommand spCmd = new OracleCommand())
                {
                    cmaDb.Database.Connection.Open();
                    spCmd.Connection     = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
                    spCmd.CommandText    = "GetChinaSecurities";
                    spCmd.CommandType    = CommandType.StoredProcedure;
                    spCmd.CommandTimeout = 0;

                    spCmd.Parameters.AddRange(paramArray);

                    OracleDataAdapter da = new OracleDataAdapter(spCmd);
                    var ds = new DataSet();
                    da.Fill(ds);
                    total = Convert.ToInt32(spCmd.Parameters["P_TOTAL"].Value.ToString());
                    return(ds.Tables[0]);
                }
            }
        }