public Dictionary <string, string> GetConvention(String Currency)
{
string sql = "SELECT * FROM CurveGeneratorConvention WHERE Currency = '" + Currency + "'";
SQLServer db = new SQLServer(Settings.Default.DSN);
Utils.Log(string.Format("Calling DSN: {0} {1}", Settings.Default.DSN, sql));
SqlDataReader dr = db.FetchDataReader(sql);
Dictionary <string, string> convention = new Dictionary <string, string>();
while (dr.Read())
{
convention.Add("DayCountConvention", dr["DayCountConvention"].ToString());
convention.Add("NextWorkingDay", dr["NextWorkingDay"].ToString());
convention.Add("HolidayCentre", dr["HolidayCentre"].ToString());
convention.Add("MaxFutureTermInDays", dr["MaxFutureTermInDays"].ToString());
convention.Add("SettleDaysForFutures", dr["SettleDaysForFutures"].ToString());
convention.Add("SettleDaysForSwaps", dr["SettleDaysForSwaps"].ToString());
convention.Add("SettleDaysForLibor", dr["SettleDaysForLibor"].ToString());
convention.Add("LiborDayCountConvention", dr["LiborDayCountConvention"].ToString());
convention.Add("FutureDayCountConvention", dr["FutureDayCountConvention"].ToString());
convention.Add("SwapFixedDayCountConvention", dr["SwapFixedDayCountConvention"].ToString());
convention.Add("SwapFloatPaymentFrequency", dr["SwapFloatPaymentFrequency"].ToString());
convention.Add("SwapFixedPaymentFrequency", dr["SwapFixedPaymentFrequency"].ToString());
}
dr.Close();
db.Close();
return(convention);
}
private string GetPermissions(DbObject Ob)
{
string ret = "";
string sql = "SELECT 'GRANT ' + p.permission_name collate latin1_general_cs_as \r\n" +
" + ' ON [' + s.name + '].[' + o.name + '] TO [' + pr.name + ']' as Line \r\n" +
"FROM sys.database_permissions AS p \r\n" +
"INNER JOIN sys.objects AS o ON p.major_id=o.object_id \r\n" +
"INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id \r\n" +
"INNER JOIN sys.database_principals AS pr ON p.grantee_principal_id=pr.principal_id \r\n" +
"WHERE o.Name = '" + Ob.ObjectName + "'";
SQLServer db = new SQLServer(Ob.Server, Ob.Database, "", "");
try {
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
ret += dr["Line"].ToString() + "\r\n";
}
dr.Close();
} finally {
db.Close();
}
return(ret);
}
private static DataSet GetData(string server, string database, string sql)
{
var db = new SQLServer(server, database, "", "")
{
Timeout = 0,
NTauthentication = true
};
// no timeout
DataSet ds;
try
{
SqlCommand c = new SqlCommand(sql)
{
CommandType = CommandType.Text
};
ds = db.FetchData(c);
}
catch (Exception e)
{
Exception e2 = new Exception(sql, e);
throw e2;
}
finally
{
db.Close();
}
return(ds);
}
public void ExecSql(string sql, string failMessage, string DSN = "", int timeout = 0)
{
SqlCommand cmd = new SqlCommand(sql);
if (DSN == "")
{
DSN = Settings.Default.DSN;
}
SQLServer db = new SQLServer(DSN);
Utils.Log(string.Format("Calling DSN: {0} {1}", DSN, sql));
db.ConnectionTimeout = timeout;
db.Timeout = timeout;
//SQLServer db = new SQLServer(Settings.Default.DSN);
try
{
string ret = db.ExecSql(cmd);
if (ret != "")
{
Utils.Log(failMessage);
throw new Exception(failMessage + " " + ret);
}
}
finally
{
db.Close();
}
}
internal static List <ScheduledItem> FetchNewScheduledItems()
{
List <ScheduledItem> ret = new List <ScheduledItem>();
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
try
{
SqlDataReader dr = db.FetchDataReader("GetScheduledItems");
while (dr.Read())
{
ScheduledItem item = new ScheduledItem();
item.DetailID = (int)dr["DetailID"];
item.FileDate = DateTime.Parse(dr["FileDate"].ToString());
item.FileNameMask = dr["FileNameMask"].ToString();
item.FetchTime = DateTime.Parse(dr["FetchTime"].ToString());
ret.Add(item);
}
dr.Close();
}
finally
{
db.Close();
}
return(ret);
}
public List <String> GetCurrency(int runID)
{
Utils.Log("Start GetCurrency");
string sql;
if (runID == 0)
{
sql = "SELECT Currency FROM CurveGeneratorCurrency WHERE CalcCurve = 1";
}
else
{
sql = "SELECT DISTINCT C.Currency FROM CurveGeneratorDate T JOIN CurveGeneratorCurrency C ON T.Currency = C.Currency WHERE CalcCurve = 1 AND ID = " + runID;
}
SQLServer db = new SQLServer(Settings.Default.DSN);
Utils.Log(string.Format("Calling DSN: {0} {1} ", Settings.Default.DSN, sql));
SqlDataReader dr = db.FetchDataReader(sql);
List <String> ccy = new List <string>();
while (dr.Read())
{
Utils.Log(dr["Currency"].ToString());
ccy.Add(dr["Currency"].ToString());
}
dr.Close();
db.Close();
return(ccy);
}
public static void FetchLoginInfo(string LookupValue, out string ftpSite, out string userName, out string pw)
{
ftpSite = "";
userName = "";
pw = "";
SQLServer db = new SQLServer(Settings.Default.FtpDetailServer, Settings.Default.FtpDetailDatabase, "", "");
try
{
string sql = string.Format("SELECT Address, Username, Password FROM FTPsetting Where Location = '{0}'", LookupValue);
SqlDataReader dr = db.FetchDataReader(sql);
if (dr.Read())
{
ftpSite = dr["Address"].ToString();
userName = dr["Username"].ToString();
pw = dr["Password"].ToString();
}
dr.Close();
}
finally
{
db.Close();
}
Maple.Logger.Log(string.Format("Fetched login details ({0}) for {1}", ftpSite, LookupValue));
}
private void Disconnect()
{
if (db != null)
{
db.Close();
}
db = null;
}
public InputData GetCurveData(String currency, int runID)
{
Utils.Log("Creating List");
string sql = "EXEC CurveGeneratorGetLatestData '" + currency + "', " + runID;
SQLServer db = new SQLServer(Settings.Default.DSN);
Utils.Log(string.Format("Calling DSN: {0} {1}", Settings.Default.DSN, sql));
SqlDataReader dr = db.FetchDataReader(sql);
InputData input = new InputData();
input.LiborData = new List <Rate>();
input.SwapData = new List <Rate>();
input.FutureData = new List <Rate>();
while (dr.Read())
{
input.StartDate = DateTime.Parse(dr["TimeStamp"].ToString()); // DateTime.Now;
Rate rate = new Rate();
//rate.StartDate = DateTime.Now;
rate.Ask = (double)dr["Ask"];
rate.Bid = (double)dr["Bid"];
rate.SecType = dr["TypeName"].ToString();
if (rate.SecType == "Future")
{
rate.Expiry = DateTime.Parse(dr["Maturity"].ToString());
}
else
{
rate.TermCode = dr["Type"].ToString();
}
switch (rate.SecType)
{
case "Libor":
input.LiborData.Add(rate);
break;
case "Swap":
input.SwapData.Add(rate);
break;
case "Future":
input.FutureData.Add(rate);
break;
}
Utils.Log(string.Format("{0} Curve data used: {1}", currency, rate.ToString()));
}
dr.Close();
db.Close();
return(input);
}
internal static string FetchRetrieveItems(Dictionary <int, FtpDetail> FtpDetails)
{
string ret = "";
string sql = "SELECT * FROM Retrieve WHERE State = 'New' AND TimeToRetrieve < GetDate() ORDER BY TimeToRetrieve";
foreach (FtpDetail d in FtpDetails.Values)
{
// Create the collection again so that we discard any unwanted, processed or old items
d.RetrieveItems = new Dictionary <int, RetrieveItem>();
}
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
try
{
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
int detailID = (int)dr["DetailID"];
if (FtpDetails.ContainsKey(detailID))
{
FtpDetail parent = FtpDetails[detailID];
int retrieveID = (int)dr["ID"];
string fileName = dr["Filename"].ToString();
DateTime fileDate = (DateTime)dr["FileDate"];
DateTime retrieveTime = (DateTime)dr["TimeToRetrieve"];
int attempt = int.Parse(dr["Attempt"].ToString());
bool parseOnly = (bool)dr["ParseOnly"];
RetrieveItem item = new RetrieveItem(parent, retrieveID, fileName, fileDate, retrieveTime);
item.ParseOnly = parseOnly;
item.Attempt = attempt;
parent.RetrieveItems.Add(retrieveID, item);
}
else
{
Funcs.ReportProblem(string.Format("FTP Detail ID [{0}] not found in collection.", detailID));
}
}
dr.Close();
}
finally
{
db.Close();
}
return(ret);
}
internal string RunCommand(string Server, string cmd)
{
string ret = "";
string server = Server;
if (cmd.Length > 8 && cmd.Substring(0, 9).ToLower() == ":connect ")
{
// This is targeted against a specific server
server = cmd.Substring(9, cmd.IndexOfAny(new Char[] { '\r', '\n' }) - 9);
cmd = cmd.Substring(9 + server.Length);
}
SQLServer db = new SQLServer(server, "master", "", "");
try {
db.Timeout = 300; // Some processes are long
// Split commands that are seperated by a GO
string[] lines = cmd.Split(new Char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
string sql = "";
foreach (string line in lines)
{
if (line.Trim().ToUpper() == "GO")
{
ret = db.ExecSql(sql);
sql = "";
if (ret != "")
{
break;
}
}
else
{
sql += line + "\r\n";
}
}
if (sql != "")
{
ret = db.ExecSql(sql);
}
} catch (Exception ex) {
ret = "Error: " + ex.Message;
} finally {
db.Close();
}
return(ret);
}
private List <string> GetDatabaseNames(string Server)
{
List <string> ret = new List <string>();
SQLServer comm = new SQLServer(Server, "master", "", "");
try {
string sql = "select name from sys.databases where name != 'tempdb'";
SqlDataReader dr = comm.FetchDataReader(sql);
while (dr.Read())
{
ret.Add(dr[0].ToString());
}
return(ret);
} finally {
comm.Close();
}
}
private static void ExecSql(SqlCommand cmd, string failMessage)
{
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
try
{
string ret = db.ExecSql(cmd);
if (ret != "")
{
throw new Exception(failMessage + " " + ret + "\r\n\r\n" + cmd.CommandText);
}
}
finally
{
db.Close();
}
}
private List <string> GetNonSystemObjectNames(string Server, string Database, eObjectTypes obType)
{
List <string> ret = new List <string>();
SQLServer comm = new SQLServer(Server, Database, "", "");
try
{
string sql = "SELECT Schema_Name(schema_id), name FROM sys.objects WHERE Type = '";
switch (obType)
{
case eObjectTypes.procedure:
sql += "P'";
break;
case eObjectTypes.table:
sql += "U'";
break;
case eObjectTypes.view:
sql += "V'";
break;
case eObjectTypes.function:
sql += "FN'";
break;
}
sql += " and is_ms_shipped = 0";
SqlDataReader dr = comm.FetchDataReader(sql);
while (dr.Read())
{
ret.Add(dr[0] + "." + dr[1]);
}
return(ret);
}
finally{
comm.Close();
}
}
private bool Open()
{
try
{
string strIP = GetConfigValue("Server").ToString();
string uid = GetConfigValue("uid").ToString();
string pwd = GetConfigValue("pwd").ToString();
if (svr == null)
{
svr = new SQLServerClass();
// svr.Connect(".", "sa", "sa");
svr.Connect(strIP, uid, pwd);
}
else
{
svr.Close();
svr = new SQLServerClass();
// svr.Connect(".", "sa", "sa");
svr.Connect(strIP, uid, pwd);
}
}
catch (System.Runtime.InteropServices.COMException ce)
{
if (ce.ErrorCode == -2147203048)
{
ErrorMessage(2023048, ce.StackTrace, "[DataBaseManage:Open]", ce.Message);
return(false);
}
//不存在或访问被拒绝
if (ce.ErrorCode == -2147221504)
{
ErrorMessage(2021504, ce.StackTrace, "[DataBaseManage:Open]", ce.Message);
return(false);
}
}
return(true);
}
private void Connect()
{
SQLServer db = null;
string connStr;
try
{
db = new SQLServer(Dsn);
connStr = db.ConnectionString;
}
finally
{
if (db != null)
{
db.Close();
}
db = null;
}
swiftData = new SwiftDataDataContext(connStr);
swiftData.CommandTimeout = 0; //10 mins
}
internal string GetObjectText(string Server, string Database, string ObjectName)
{
string ret = "";
string sql = "Use " + Database + ";\r\n";
sql += "EXEC sp_helptext [" + ObjectName + "]";
SQLServer db = new SQLServer(Server, Database, "", "");
try {
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
ret += dr["text"].ToString();
}
dr.Close();
} finally {
db.Close();
}
return(ret);
}
internal static string FetchFtpDetail(ref Dictionary <int, FtpDetail> details)
{
string ret = "";
#if DEBUG
SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN);
#else
SQLServer db = new SQLServer(Settings.Default.FtpDSN);
#endif
string sql = "SELECT * FROM Detail";
try
{
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
int id = (int)dr["ID"];
string name = dr["Name"].ToString();
FtpDetail detail;
bool add = false;
if (details.ContainsKey(id))
{
detail = details[id];
}
else
{
detail = new FtpDetail(id, name);
add = true;
}
if (dr["ColIdentType"].ToString().ToLower() == "index")
{
detail.ColumnIdentifierType = FtpDetail.eColumnIdentifierType.Index;
}
else if (dr["ColIdentType"].ToString().ToLower() == "name")
{
detail.ColumnIdentifierType = FtpDetail.eColumnIdentifierType.Name;
}
detail.ColumnsRequired = dr["ColumnsRequired"].ToString();
detail.Delimiter = dr["Delimiter"].ToString();
detail.Folder = dr["Folder"].ToString();
string shareType = dr["TransferType"].ToString().ToLower();
switch (shareType)
{
case "ftp":
detail.FptType = FtpDetail.eFtpType.ftp;
break;
case "sftp":
detail.FptType = FtpDetail.eFtpType.sftp;
break;
case "unixshare":
detail.FptType = FtpDetail.eFtpType.UnixShare;
break;
case "https":
detail.FptType = FtpDetail.eFtpType.Https;
break;
default:
throw new Exception(string.Format("Bad share type read from database [{0}]", shareType));
}
detail.FtpLookupValue = dr["FtpLookupName"].ToString();
detail.TargetColumnNames = dr["TargetColumnNames"].ToString();
detail.TargetColumnTypes = dr["TargetColumnTypes"].ToString();
detail.TargetTable = dr["TargetTable"].ToString();
detail.ViewName = dr["ViewName"].ToString().Trim();
detail.PGP = dr["pgp"].ToString() == "True";
detail.ZIP = dr["zip"].ToString() == "True";
detail.DES = dr["des"].ToString() == "True";
object viewCreated = dr["ViewCreated"];
if (detail.ViewName != "" && viewCreated.ToString() == "")
{
CreateViewForFtpData(detail);
}
if (add)
{
details.Add(detail.ID, detail);
}
}
dr.Close();
}
finally
{
db.Close();
}
return(ret);
}
internal List <DbObject> GetObjectsUsingLinkedServer(string server, string LinkedServerName)
{
List <DbObject> ret = new List <DbObject>();
string sql = "SET NOCOUNT ON \r\n" +
"CREATE TABLE #output(Name varchar(50), DbName varchar(50),ObjectName varchar(500),ObjectType varchar(2),LinkedServerName varchar(50) ) \r\n" +
"Declare @Sql varchar(2000) ,@BaseSql varchar(2000) ,@Db varchar(50) ,@LinkedServerName varchar(50) \r\n" +
"SET @LinkedServerName = '" + LinkedServerName + "' \r\n" +
"SET @BaseSql = ' \r\n" +
"begin \r\n" +
" SELECT DISTINCT @@Servername, db_name(), o.name,o.type,''' + @LinkedServerName + ''' \r\n" +
" FROM sys.objects o \r\n" +
" JOIN syscomments c on o.object_id = c.id \r\n" +
" WHERE TEXT LIKE ''% ' + @LinkedServerName + '.%'' \r\n" +
"end' \r\n" +
"Print @Sql \r\n" +
"Declare db cursor for \r\n" +
"Select Name from sys.databases \r\n" +
"where name not in ('master','tempdb','model','msdb','SystemInfo') \r\n" +
" and name not like '%[_]master' \r\n" +
" and name not like '%[_]msdb' \r\n" +
"Open db \r\n" +
"Fetch Next from db into @db \r\n" +
"while @@Fetch_Status = 0 \r\n" +
"begin \r\n" +
" raiserror('Querying the %s database.',10,1,@db) with nowait \r\n" +
" begin Try \r\n" +
" SET @Sql = 'Use ' + @db + '; \r\n"+
" ' + @BaseSql \r\n"+
" INSERT #output \r\n"+
" exec(@Sql) \r\n"+
" end Try \r\n" +
" begin Catch \r\n" +
" INSERT #output \r\n"+
" SELECT @@Servername, @db, 'Error: ' + error_message(),'', @LinkedServerName \r\n"+
" end Catch \r\n" +
" Fetch Next from db into @db \r\n" +
"end \r\n" +
"close db \r\n" +
"deallocate db \r\n" +
"SELECT * FROM #output \r\n" +
"DROP TABLE #output \r\n";
SQLServer db = new SQLServer(server, "master", "", "");
try {
db.Timeout = 900; // Some processes are long
SqlDataReader dr = db.FetchDataReader(sql);
while (dr.Read())
{
DbObject ob = new DbObject();
ob.Server = dr["Name"].ToString();
ob.Database = dr["DbName"].ToString();
ob.ObjectName = dr["ObjectName"].ToString();
ob.ObjectType = dr["ObjectType"].ToString().Trim().ToUpper();
ob.LinkedServerName = dr["LinkedServerName"].ToString();
ret.Add(ob);
}
dr.Close();
} finally {
db.Close();
}
return(ret);
}
/// <summary>
/// Requests data via the BERG system vertically, with one field per row
/// </summary>
/// <param name="Requests">The list of request items to fetch</param>
/// <param name="AppID">An ID used to identify the calling process</param>
/// <returns>The Reference ID with which to find the results</returns>
internal static int RequestBBdataVertically(List <BloombergDataInstrument> Requests, string AppID)
{
int ret = 0;
string sql = "SET NOCOUNT ON\r\n"
+ "CREATE TABLE #BBGRequestDetail(RequestType varchar(50),Ticker varchar(50),Fields varchar(1000),"
+ "DateFrom datetime,DateTo datetime,Periodicity VARCHAR(50),Problem varchar(250),Value1 varchar(2000) ),\r\n";
int counter = 1000;
foreach (BloombergDataInstrument ins in Requests)
{
foreach (string field in ins.BBFields.Keys)
{
string row = "('";
if (ins.RequestType == BloombergDataInstrument.eRequestType.Historical)
{
row += string.Format("historic','{0}','{1}','DAILY','", ins.Ticker, ins.DateFrom.ToString("yyyyMMdd"));
}
else
{
row += string.Format("Reference','{0}',null,null,'", ins.Ticker);
}
row += field + "'),\r\n";
if (counter == 1000)
{
counter = 0;
//need an insert and values
//remove trailing comma
sql = sql.Substring(0, sql.Length - 3) + "\r\n";
sql += "\r\nINSERT #BBGRequestDetail(RequestType,Ticker,DateFrom,Periodicity,Fields)\r\n";
sql += "VALUES \r\n";
}
sql += row;
counter++;
}
}
sql = sql.Substring(0, sql.Length - 3) + "\r\n";
int timeout = OTCOptionValuation_BBImporter.Properties.Settings.Default.BBtimeoutSeconds;
int polingDelay = OTCOptionValuation_BBImporter.Properties.Settings.Default.BBpolingSeconds;
sql += "Declare @ref int \r\n"
+ string.Format("EXEC @ref = GetBloombergData '{0}', {1}, {2}\r\n", AppID, timeout, polingDelay)
+ "SELECT @ref";
SQLServer db = GetDB();
try
{
SqlDataReader dr = db.FetchDataReader(sql);
if (dr.Read())
{
int r;
if (int.TryParse(dr[0].ToString(), out r))
{
ret = r;
}
}
dr.Close();
}
finally
{
db.Close();
}
// The data will be availble in the temp table and in the data view with the appropriate UserID and Reference
// "SELECT * FROM BloombergDataView WHERE UserID = '%APPID%' AND Reference = @ref";
// SELECT * FROM #RequestDetail
return(ret);
}
internal string AlterObject(DbObject Ob, string ObjectText)
{
string ret = "";
// In order to avoid changing the CREATE keyword to ALTER we grab the permissions, drop the object and recreate it with the
// same text that was produced by sp_helptext
string permissionSql = GetPermissions(Ob);
string dropSql = "";
switch (Ob.ObjectType)
{
case "P":
dropSql = "DROP PROC [" + Ob.ObjectName + "]";
break;
case "V":
dropSql = "DROP VIEW [" + Ob.ObjectName + "]";
break;
case "FN":
case "IF":
dropSql = "DROP FUNCTION [" + Ob.ObjectName + "]";
break;
default:
throw new Exception(string.Format("Unhandled object type of {0} in AlterObject method.", Ob.ObjectType));
}
// Maybe a temporary entry, waiting for a decision on this
bool ok = true;
if (ObjectText.ToLower().Contains("openquery") && ObjectText.ToLower().Contains("remotedb.dbo.execkondorlive"))
{
ok = false;
}
if (ok)
{
SQLServer db = new SQLServer(Ob.Server, Ob.Database, "", "");
try {
string err = db.ExecSql("BEGIN TRAN");
if (err == "")
{
err = db.ExecSql(dropSql);
}
if (err == "")
{
err = db.ExecSql(ObjectText);
}
if (err == "")
{
err = db.ExecSql("COMMIT TRAN");
}
else
{
err += "\r\n" + db.ExecSql("IF @@trancount > 0 RollBack Tran");
}
if (err != "")
{
ret += err;
}
else
{
if (permissionSql != "")
{
ret += db.ExecSql(permissionSql);
}
}
} finally {
db.Close();
}
}
return(ret);
}
/// <summary>
/// Requests data via the BERG system in tablular form
/// </summary>
/// <param name="Requests">The list of request items to fetch</param>
/// <param name="AppID">An ID used to identify the calling process</param>
/// <returns>The Reference ID with which to find the results</returns>
internal static int RequestBBdata(List <BloombergDataInstrument> Requests, string AppID)
{
int fieldCount;
if (FieldCountIsNotEqual(Requests, out fieldCount))
{
throw new Exception("The number of fields for each instrument must be equal.");
}
int ret = 0;
string sql = "SET NOCOUNT ON\r\n"
+ "CREATE TABLE #RequestDetail(RequestType varchar(50),Ticker varchar(50),Fields varchar(1000),DateFrom datetime,Problem varchar(250)";
for (int i = 1; i <= fieldCount; i++)
{
sql += string.Format(",Value{0} varchar(250)", i);
}
sql += " )";
sql += "\r\nINSERT #RequestDetail(RequestType,Ticker,DateFrom,Fields)\r\n";
sql += "VALUES \r\n";
foreach (BloombergDataInstrument ins in Requests)
{
string row = "('";
if (ins.RequestType == BloombergDataInstrument.eRequestType.Historical)
{
row += string.Format("historic','{0}','{1}','", ins.Ticker, ins.DateFrom.ToString("yyyyMMdd"));
}
else
{
row += string.Format("Reference','{0}',null,'", ins.Ticker);
}
foreach (string field in ins.BBFields.Keys)
{
row += field + ",";
}
// Take off trailing comma
row = row.Substring(0, row.Length - 1);
row += "'),\r\n";
sql += row;
}
// Take off trailing comma \r\n
sql = sql.Substring(0, sql.Length - 3) + "\r\n";
sql += "Declare @ref int \r\n"
+ string.Format("EXEC @ref = FetchBloombergValues '{0}', {1}\r\n", AppID, fieldCount)
+ "SELECT @ref";
SQLServer db = GetDB();
try
{
SqlDataReader dr = db.FetchDataReader(sql);
if (dr.Read())
{
int r;
if (int.TryParse(dr[0].ToString(), out r))
{
ret = r;
}
}
dr.Close();
}
finally
{
db.Close();
}
// The data will be availble in the temp table and in the data view with the appropriate UserID and Reference
// "SELECT * FROM BloombergDataView WHERE UserID = 'OptionValueRetrieval' AND Reference = @ref";
// SELECT * FROM #RequestDetail
return(ret);
}