277 lines
8.2 KiB
C#
277 lines
8.2 KiB
C#
using System;
|
|
using System.Text;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.MarketDataModel;
|
|
using MarketData.Utils;
|
|
|
|
namespace MarketData.DataAccess
|
|
{
|
|
public class ConsumerPriceIndexDA
|
|
{
|
|
private ConsumerPriceIndexDA()
|
|
{
|
|
}
|
|
|
|
public static List<String> GetDistinctIndices()
|
|
{
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlDataReader sqlDataReader=null;
|
|
List<String> indices = new List<String>();
|
|
try
|
|
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlCommand=sqlConnection.CreateCommand();
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("select distinct(index_code) from PriceIndex ");
|
|
sb.Append("ORDER BY 1 desc");
|
|
sqlCommand.CommandText=sb.ToString();
|
|
sqlDataReader=sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
indices.Add(sqlDataReader.GetString(0).ToUpper());
|
|
}
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
sqlCommand.Dispose();
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
return indices;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlDataReader)
|
|
{
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
}
|
|
if(null!=sqlCommand)
|
|
{
|
|
sqlCommand.Dispose();
|
|
}
|
|
if(null!=sqlConnection)
|
|
{
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
|
|
public static PriceIndices GetConsumerPriceIndex(String indexCode)
|
|
{
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlDataReader sqlDataReader=null;
|
|
PriceIndices priceIndices=new PriceIndices();
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlCommand=sqlConnection.CreateCommand();
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("select index_code, index_name, index_value, as_of, source from PriceIndex ");
|
|
sb.Append("where index_code='").Append(indexCode).Append("'");
|
|
sb.Append("ORDER BY as_of desc");
|
|
sqlCommand.CommandText=sb.ToString();
|
|
sqlDataReader=sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
PriceIndex priceIndex=new PriceIndex();
|
|
if(!sqlDataReader.IsDBNull(0))priceIndex.Code=sqlDataReader.GetString(0);
|
|
if(!sqlDataReader.IsDBNull(1))priceIndex.Name=sqlDataReader.GetString(1);
|
|
if(!sqlDataReader.IsDBNull(2))priceIndex.Value=sqlDataReader.GetDouble(2);
|
|
if(!sqlDataReader.IsDBNull(3))priceIndex.AsOf=sqlDataReader.GetDateTime(3);
|
|
if(!sqlDataReader.IsDBNull(4))priceIndex.Source=sqlDataReader.GetString(4);
|
|
priceIndices.Add(priceIndex);
|
|
}
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
sqlCommand.Dispose();
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
return priceIndices;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlDataReader)
|
|
{
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
}
|
|
if(null!=sqlCommand)
|
|
{
|
|
sqlCommand.Dispose();
|
|
}
|
|
if(null!=sqlConnection)
|
|
{
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
public static PriceIndices GetConsumerPriceIndices()
|
|
{
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlDataReader sqlDataReader=null;
|
|
PriceIndices priceIndices=new PriceIndices();
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlCommand=sqlConnection.CreateCommand();
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("select index_code, index_name, index_value, as_of, source from PriceIndex ORDER BY as_of desc");
|
|
sqlCommand.CommandText=sb.ToString();
|
|
sqlDataReader=sqlCommand.ExecuteReader();
|
|
while(sqlDataReader.Read())
|
|
{
|
|
PriceIndex priceIndex=new PriceIndex();
|
|
if(!sqlDataReader.IsDBNull(0))priceIndex.Code=sqlDataReader.GetString(0);
|
|
if(!sqlDataReader.IsDBNull(1))priceIndex.Name=sqlDataReader.GetString(1);
|
|
if(!sqlDataReader.IsDBNull(2))priceIndex.Value=sqlDataReader.GetDouble(2);
|
|
if(!sqlDataReader.IsDBNull(3))priceIndex.AsOf=sqlDataReader.GetDateTime(3);
|
|
if(!sqlDataReader.IsDBNull(4))priceIndex.Source=sqlDataReader.GetString(4);
|
|
priceIndices.Add(priceIndex);
|
|
}
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
sqlCommand.Dispose();
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
return priceIndices;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}",exception.ToString()));
|
|
return null;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlDataReader)
|
|
{
|
|
sqlDataReader.Close();
|
|
sqlDataReader.Dispose();
|
|
}
|
|
if(null!=sqlCommand)
|
|
{
|
|
sqlCommand.Dispose();
|
|
}
|
|
if(null!=sqlConnection)
|
|
{
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
public static bool InsertUpdatePriceIndices(PriceIndices priceIndices)
|
|
{
|
|
MySqlConnection sqlConnection=null;
|
|
MySqlCommand sqlCommand=null;
|
|
MySqlTransaction sqlTransaction=null;
|
|
try
|
|
{
|
|
sqlConnection = SqlUtils.CreateMySqlConnection(MainDataSource.Instance.LocateDataSource("market_data"));
|
|
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
|
|
sqlCommand=sqlConnection.CreateCommand();
|
|
sqlCommand.Transaction=sqlTransaction;
|
|
foreach(PriceIndex priceIndex in priceIndices)
|
|
{
|
|
DeletePriceIndex(priceIndex.Code,priceIndex.AsOf,sqlCommand,sqlTransaction);
|
|
}
|
|
InsertPriceIndices(priceIndices,sqlCommand,sqlTransaction);
|
|
sqlTransaction.Commit();
|
|
sqlTransaction.Dispose();
|
|
sqlCommand.Dispose();
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
sqlConnection=null;
|
|
sqlCommand=null;
|
|
sqlTransaction=null;
|
|
return true;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}",exception.ToString()));
|
|
return false;
|
|
}
|
|
finally
|
|
{
|
|
if(null!=sqlConnection)
|
|
{
|
|
sqlConnection.Close();
|
|
sqlConnection.Dispose();
|
|
}
|
|
if(null!=sqlCommand)
|
|
{
|
|
sqlCommand.Dispose();
|
|
}
|
|
if(null!=sqlTransaction)
|
|
{
|
|
sqlTransaction.Dispose();
|
|
}
|
|
}
|
|
}
|
|
private static bool DeletePriceIndex(String indexCode,DateTime asOf,MySqlCommand sqlCommand,MySqlTransaction sqlTransaction)
|
|
{
|
|
String strQuery=null;
|
|
try
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("delete from PriceIndex ").Append(" where ");
|
|
sb.Append("index_code=").Append("'").Append(indexCode).Append("'").Append(" and ");
|
|
sb.Append("as_of= ").Append(SqlUtils.SqlDate(asOf,true));
|
|
strQuery=sb.ToString();
|
|
sqlCommand.CommandText=strQuery;
|
|
sqlCommand.ExecuteNonQuery();
|
|
return true;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}, query was {1}",exception.ToString(),strQuery));
|
|
return false;
|
|
}
|
|
}
|
|
private static bool InsertPriceIndices(PriceIndices priceIndices,MySqlCommand sqlCommand,MySqlTransaction sqlTransaction)
|
|
{
|
|
String strQuery=null;
|
|
DateTime modified=DateTime.Now;
|
|
try
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
foreach(PriceIndex priceIndex in priceIndices)
|
|
{
|
|
sb.Append("insert into PriceIndex(index_code,index_name,index_value,as_of,source,modified)values(");
|
|
sb.Append(SqlUtils.ToSqlString(priceIndex.Code)).Append(",");
|
|
sb.Append(SqlUtils.ToSqlString(priceIndex.Name)).Append(",");
|
|
sb.Append(priceIndex.Value).Append(",");
|
|
sb.Append(SqlUtils.SqlDate(priceIndex.AsOf,true)).Append(",");
|
|
sb.Append(SqlUtils.ToSqlString(priceIndex.Source)).Append(",");
|
|
sb.Append(SqlUtils.SqlDate(modified,true));
|
|
sb.Append(");");
|
|
}
|
|
strQuery=sb.ToString();
|
|
sqlCommand.CommandText=sb.ToString();
|
|
sqlCommand.ExecuteNonQuery();
|
|
return true;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,String.Format("Exception:{0}",exception.ToString()));
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
}
|