using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace MT_WriteinExcel
{
public class readFromDB
{
public DataTable readDataFromDB()
{
// List<DT_Tickets> tickets = new List<DT_Tickets>();
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = MtConfig.DbConnectionString;
SqlCommand cmd = new SqlCommand("sp_readMT_Data", con);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
con.Open();
dtTable().Load(cmd.ExecuteReader());
}
IList<DT_Tickets> tickets1 = dtTable().AsEnumerable()
.Select(dr => new DT_Tickets
{
LocalTCID = dr.Field<string>("LocalTCID"),
SourceReference = dr.Field<string>("SourceReference"),
TimeOfDeal = dr.Field<DateTime>("TimeOfDeal"),
DealerId = dr.Field<string>("DealerId"),
PureDealType = dr.Field<int>("PureDealType"),
DealType = dr.Field<int>("DealType"),
Bank1DealingCode = dr.Field<string>("Bank1DealingCode"),
DealVolumeCurrency1 = dr.Field<double>("DealVolumeCurrency1"),
ExchangeRatePeriod1 = dr.Field<double>("ExchangeRatePeriod1"),
Bank1Name = dr.Field<string>("Bank1Name"),
Period1 = dr.Field<int>("Period1"),
Period2 = dr.Field<int>("Period2"),
MidMarketNearRate = dr.Field<double>("MidMarketNearRate"),
UserDefinedData1 = dr.Field<string>("UserDefinedData1"),
UserDefinedData2 = dr.Field<string>("UserDefinedData2"),
UserDefinedData3 = dr.Field<string>("UserDefinedData3"),
TransactionID = dr.Field<string>("TransactionID"),
ValueDatePeriod1Currency2 = dr.Field<DateTime>("ValueDatePeriod1Currency2"),
ValueDatePeriod2Currency2 = dr.Field<DateTime>("ValueDatePeriod2Currency2"),
DealerName = dr.Field<string>("DealerName"),
ConfirmedByName = dr.Field<string>("ConfirmedByName"),
SecondarySourceRef = dr.Field<string>("SecondarySourceRef")
}).ToList();
//return tickets1;
return dtTable();
}
public DataTable dtTable()
{
DataTable dtTickets = new DataTable("dt_tickets");
dtTickets.Columns.Add("LocalTCID", typeof(string));
dtTickets.Columns.Add("SourceReference", typeof(string));
dtTickets.Columns.Add("TimeOfDeal", typeof(DateTime));
dtTickets.Columns.Add("DealerId", typeof(string));
dtTickets.Columns.Add("PureDealType", typeof(int));
dtTickets.Columns.Add("DealType", typeof(int));
dtTickets.Columns.Add("Bank1DealingCode", typeof(string));
dtTickets.Columns.Add("Currency1", typeof(string));
dtTickets.Columns.Add("Currency2", typeof(string));
dtTickets.Columns.Add("DealVolumeCurrency1", typeof(double));
dtTickets.Columns.Add("ExchangeRatePeriod1", typeof(double));
dtTickets.Columns.Add("Bank1Name", typeof(string));
dtTickets.Columns.Add("Period1", typeof(int));
dtTickets.Columns.Add("Period2", typeof(int));
dtTickets.Columns.Add("MidMarketNearRate", typeof(double));
dtTickets.Columns.Add("MidMarketFarRate", typeof(double));
dtTickets.Columns.Add("UserDefinedData1", typeof(string));
dtTickets.Columns.Add("UserDefinedData2", typeof(string));
dtTickets.Columns.Add("UserDefinedData3", typeof(string));
dtTickets.Columns.Add("TransactionID", typeof(string));
dtTickets.Columns.Add("ValueDatePeriod1Currency2", typeof(DateTime));
dtTickets.Columns.Add("ValueDatePeriod2Currency2", typeof(DateTime));
dtTickets.Columns.Add("DealerName", typeof(string));
dtTickets.Columns.Add("ConfirmedByName", typeof(string));
dtTickets.Columns.Add("SecondarySourceRef", typeof(string));
return dtTickets;
}
}
public class DT_Tickets
{
public string LocalTCID { get; set; }
public string SourceReference { get; set; }
public DateTime TimeOfDeal { get; set; }
public string DealerId { get; set; }
public int PureDealType { get; set; }
public int DealType { get; set; }
public string Bank1DealingCode { get; set; }
public string Currency1 { get; set; }
public string Currency2 { get; set; }
public double DealVolumeCurrency1 { get; set; }
public double ExchangeRatePeriod1 { get; set; }
public string Bank1Name { get; set; }
public int Period1 { get; set; }
public int Period2 { get; set; }
public double MidMarketNearRate { get; set; }
public double MidMarketFarRate { get; set; }
public string UserDefinedData1 { get; set; }
public string UserDefinedData2 { get; set; }
public string UserDefinedData3 { get; set; }
public string TransactionID { get; set; }
public string TransactionID2 { get; set; }
public DateTime ValueDatePeriod1Currency2 { get; set; }
public DateTime ValueDatePeriod2Currency2 { get; set; }
public string DealerName { get; set; }
public string ConfirmedByName { get; set; }
public string SecondarySourceRef { get; set; }
}
}
