public List <Tuple <DateTime, decimal, decimal> > GetSDLocalRefineryExcelIndex() { var data = new List <Tuple <DateTime, decimal, decimal> >(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select a.redate,a.gasoline as gasoline,b.dieselindex as Diesel from GasolineIndex a join DieselIndex b on a.redate = b.redate order by a.ReDate"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { data.Add(new Tuple <DateTime, decimal, decimal>(reader.GetDateTime(0), reader.GetDecimal(1), reader.GetDecimal(2))); } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(data); }
/// <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); } } }
public List <Tuple <DateTime, double, double, double, double> > GetSDLocalRefineryExcelStock() { var oil = new List <Tuple <DateTime, double, double, double, double> >(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select ReDate,GasolineStock,DieselStock,nvl(GasolineCapacityRate,0) GasolineCapacityRate,nvl(DieselCapacityRate,0) DieselCapacityRate from TotalStock order by ReDate"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var pointline = new Tuple <DateTime, double, double, double, double>(reader.GetDateTime(0), reader.GetDouble(1), reader.GetDouble(2), reader.GetDouble(3) * 100, reader.GetDouble(4) * 100); oil.Add(pointline); } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(new List <Tuple <DateTime, double, double, double, double> >(oil)); }
/// <summary> /// Non-query store procedure /// </summary> /// <param name="inName">sp name</param> /// <param name="inParms">parameters</param> protected void ExecNonQuerySp(string inName, OracleParameter[] inParms) { using (var cnEDB = new CneNewEntities()) { cnEDB.Database.Connection.Open(); DbCommand cmd = cnEDB.Database.Connection.CreateCommand(); cmd.CommandText = inName; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.AddRange(inParms); cmd.ExecuteNonQuery(); } }
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()); } } }
public object GetSDLocalRefineryValuation() { var gasoline = new List <object>(); var diesel = new List <object>(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select a.PriceDate,a.Price as gasoline,b.Price as Diesel from GasolineValuation a join DieselValuation b on a.PriceDate = b.PriceDate where a.PriceDate>sysdate-365 order by a.PriceDate"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)); var pointGasoline = new List <object> { date.TotalMilliseconds, reader.GetDecimal(1) }; gasoline.Add(pointGasoline); var pointDiesel = new List <object> { date.TotalMilliseconds, reader.GetDecimal(2) }; diesel.Add(pointDiesel); } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(new { gasoline, diesel }); }
public DataTable GetSdLocalRefineryCompany(bool isEnglish) { var dt = new DataTable(); string sql = isEnglish ? "SELECT DISTINCT Code,ItemName_EN as ItemName FROM View_DeviceStatisticsMap ORDER BY Code" : "SELECT DISTINCT Code,ItemName_CN as ItemName FROM View_DeviceStatisticsMap ORDER BY Code"; using (var db = new CneNewEntities()) { var cmd = new OracleCommand { Connection = (OracleConnection)(db.Database.Connection), CommandText = sql }; var da = new OracleDataAdapter(cmd); da.Fill(dt); db.Database.Connection.Close(); } return(dt); }
public DataTable GetSdLocalRefineryDeviceInfoTable(bool isEnglish, string code, string reDate) { var dt = new DataTable(); string sql = isEnglish ? "SELECT code,Device_EN as Device,YieldByTon,YieldByBarrel FROM View_DeviceStatisticsMap where Code='" + code + "'" : "SELECT code,Device,YieldByTon,YieldByBarrel FROM View_DeviceStatisticsMap where Code='" + code + "' order by row_num"; using (var db = new CneNewEntities()) { var cmd = new OracleCommand { Connection = (OracleConnection)(db.Database.Connection), CommandText = sql }; var da = new OracleDataAdapter(cmd); da.Fill(dt); db.Database.Connection.Close(); } return(dt); }
public Tuple <List <Tuple <DateTime, double, double> >, List <Tuple <DateTime, double> > > GetSDLocalRefinerExcelData() { var oil = new List <Tuple <DateTime, double, double> >(); var operating = new List <Tuple <DateTime, double> >(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select Month,Gasoline,Diesel from MonthProduction order by Month"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var pointline = new Tuple <DateTime, double, double>(reader.GetDateTime(0), reader.GetDouble(1), reader.GetDouble(2)); oil.Add(pointline); } } // Move to second result set and read Posts cmd.CommandText = "select ReDate,OperatingRate from TotalOperatingRate order by ReDate"; reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var pointOperating = new Tuple <DateTime, double>(reader.GetDateTime(0), reader.GetDouble(1)); operating.Add(pointOperating); } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(new Tuple <List <Tuple <DateTime, double, double> >, List <Tuple <DateTime, double> > >(oil, operating)); }
public DataTable GetSdLocalRefineryDailyOutputTable(DateTime start, DateTime end, string code, out string reDate) { using (var db = new CneNewEntities()) { var connection = (OracleConnection)(db.Database.Connection); connection.Open(); var cmd = new OracleCommand { Connection = connection, CommandText = "SELECT max(ReDate)FROM View_DeviceStatisticsMap" }; var obj = cmd.ExecuteScalar(); if (obj == DBNull.Value) { reDate = ""; } else { reDate = Convert.ToDateTime(obj).ToString("yyyy-MM-dd"); } connection.Close(); } var dt = new DataTable(); using (var db = new CneNewEntities()) { var cmd = new OracleCommand { Connection = (OracleConnection)(db.Database.Connection), CommandText = "SELECT ReDate,ProcessCapacity,Gasoline,Diesel FROM DayProduction where Code='" + code + "' and ReDate>='" + start.ToString("dd-MMM-yyyy") + "' and ReDate<='" + end.ToString("dd-MMM-yyyy") + "' order by ReDate desc" }; var da = new OracleDataAdapter(cmd); da.Fill(dt); db.Database.Connection.Close(); } return(dt); }
public new DataSet GetDataSetBySp(string inName, params OracleParameter[] inParams) { using (var cnE = new CneNewEntities()) { using (var spCmd = new OracleCommand()) { cnE.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(cnE.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); } } }
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]); } } }
public object GetSDLocalRefineryChartData() { var gasoline = new List <object>(); var diesel = new List <object>(); var operating = new List <object>(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select Month,Gasoline,Diesel from MonthProduction order by Month"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)); var pointGasoline = new List <object> { date.TotalMilliseconds, reader.GetDouble(1) }; gasoline.Add(pointGasoline); var pointDiesel = new List <object> { date.TotalMilliseconds, reader.GetDouble(2) }; diesel.Add(pointDiesel); } } // Move to second result set and read Posts cmd.CommandText = "select ReDate,OperatingRate from TotalOperatingRate order by ReDate"; reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)); var pointOperating = new List <object> { date.TotalMilliseconds, reader.GetDouble(1) * 100 }; operating.Add(pointOperating); } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(new { gasoline, diesel, operating }); }
public object GetSDLocalRefineryStock() { var gasolineStock = new List <object>(); var dieselStock = new List <object>(); var gasolineRate = new List <object>(); var dieselRate = new List <object>(); using (var db = new CneNewEntities()) { // Create a SQL command to execute the sproc var cmd = db.Database.Connection.CreateCommand(); cmd.CommandText = "select ReDate,GasolineStock,DieselStock,GasolineCapacityRate,DieselCapacityRate from TotalStock order by ReDate"; try { db.Database.Connection.Open(); // Run the sproc var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)); var pointGasoline = new List <object> { date.TotalMilliseconds, reader.GetDouble(1) }; gasolineStock.Add(pointGasoline); var pointDiesel = new List <object> { date.TotalMilliseconds, reader.GetDouble(2) }; dieselStock.Add(pointDiesel); if (reader[3] != DBNull.Value) { var pointGasolineRate = new List <object> { date.TotalMilliseconds, reader.GetDouble(3) * 100 }; gasolineRate.Add(pointGasolineRate); } if (reader[4] != DBNull.Value) { var pointDieselRate = new List <object> { date.TotalMilliseconds, reader.GetDouble(4) * 100 }; dieselRate.Add(pointDieselRate); } } } reader.Close(); } finally { db.Database.Connection.Close(); } } return(new { gasolineStock, dieselStock, gasolineRate, dieselRate }); }