public Table(DataRow row)
{
Catalog = DbCommon.ToString(row["TABLE_CATALOG"]);
Schema = DbCommon.ToString(row["TABLE_SCHEMA"]);
Name = DbCommon.ToString(row["TABLE_NAME"]);
Type = DbCommon.ToString(row["TABLE_TYPE"]);
}
public View(DataRow row)
{
Catalog = DbCommon.ToString(row["TABLE_CATALOG"]);
Schema = DbCommon.ToString(row["TABLE_SCHEMA"]);
Name = DbCommon.ToString(row["TABLE_NAME"]);
CheckOption = DbCommon.ToString(row["CHECK_OPTION"]);
IsUpdatable = DbCommon.ToString(row["IS_UPDATABLE"]) == "Yes";
}
/// <summary>
/// 获取Sqlserver2008字段信息
/// </summary>
public List <Column> GetColumns2008(string database, string owner, string tableName)
{
var cmd = _connection.CreateCommand();
cmd.CommandText = string.Format(@"
-- 必须在指定库内运行该语句
SELECT
sch.name as schema_name
,tbl.name as table_name
,col.name as column_name
,tp.name as column_type
,col.max_length
,col.precision
,col.scale
,col.is_nullable
,col.is_identity
,p.value as description
FROM sys.tables tbl
inner join sys.schemas sch on sch.schema_id=tbl.schema_id
inner JOIN sys.columns col ON col.object_id = tbl.object_id
left join systypes tp on tp.xusertype=col.user_type_id
LEFT JOIN sys.extended_properties p ON p.major_id = col.object_id AND p.minor_id = col.column_id
WHERE sch.name = '{0}'
and tbl.name = '{1}'
", owner, tableName
);
cmd.CommandType = CommandType.Text;
using (var rdr = cmd.ExecuteReader())
{
var columns = new List <Column>();
while (rdr.Read())
{
var col = new Column();
col.TableSchema = DbCommon.ToString(rdr["schema_name"]);
col.TableName = DbCommon.ToString(rdr["table_name"]);
col.Name = DbCommon.ToString(rdr["column_name"]);
col.DataType = DbCommon.ToString(rdr["column_type"]);
col.CharacterMaximumLength = DbCommon.ToInt32(rdr["max_length"]);
col.NumericPrecision = DbCommon.ToInt32(rdr["precision"]);
col.NumericScale = DbCommon.ToInt32(rdr["scale"]);
col.IsNullbable = DbCommon.ToBoolean(rdr["is_nullable"]);
col.Identity = DbCommon.ToBoolean(rdr["is_identity"]);
col.Description = DbCommon.ToString(rdr["description"]);
columns.Add(col);
}
return(columns);
}
}
public Column(DataRow row)
{
TableCatalog = DbCommon.ToString(row["TABLE_CATALOG"]);
TableSchema = DbCommon.ToString(row["TABLE_SCHEMA"]);
TableName = DbCommon.ToString(row["TABLE_NAME"]);
Name = DbCommon.ToString(row["COLUMN_NAME"]);
OrdinalPosition = DbCommon.ToInt32(row["ORDINAL_POSITION"]);
Defalut = DbCommon.ToString(row["COLUMN_DEFAULT"]);
IsNullbable = DbCommon.ToString(row["IS_NULLABLE"]) == "Yes";
DataType = DbCommon.ToString(row["DATA_TYPE"]);
CharacterMaximumLength = DbCommon.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
CharacterOctetLength = DbCommon.ToInt32(row["CHARACTER_OCTET_LENGTH"]);
NumericPrecision = DbCommon.ToInt32(row["NUMERIC_PRECISION"]);
NumericPrecisionRadix = DbCommon.ToInt32(row["NUMERIC_PRECISION_RADIX"]);
NumericScale = DbCommon.ToInt32(row["NUMERIC_SCALE"]);
DateTimePrecision = DbCommon.ToInt32(row["DATETIME_PRECISION"]);
CharacterSetCatalog = DbCommon.ToString(row["CHARACTER_SET_CATALOG"]);
CharacterSetSchema = DbCommon.ToString(row["CHARACTER_SET_SCHEMA"]);
CharacterSetName = DbCommon.ToString(row["CHARACTER_SET_NAME"]);
CollationCatalog = DbCommon.ToString(row["COLLATION_CATALOG"]);
}
public static async Task<IList<object[]>> LoadHistoricalQuotesAsync(IEnumerable<QuoteRequest> p_reqs,
DbCommon.AssetType p_at, bool? p_isAscendingDates = null, CancellationToken p_canc = default(CancellationToken))
{
var sqls = new Dictionary<string, string>(1); bool isSimulated = false;
string query = null, qHead = null, qTail = null, ticker = null; string[] qCols = null;
Action<string> parseCols = (q) => {
const string ma = "/*ColumnsBEGIN*/", mb = "/*ColumnsEND*/";
int a = q.IndexOf(ma), b = q.IndexOf(mb); System.Diagnostics.Debug.Assert(0 <= a && (a + ma.Length) <= b);
qHead = q.Substring(0, a); qTail = q.Substring(b + mb.Length);
qCols = q.Substring(a += ma.Length, b - a).Split(',');
};
Func<QuoteRequest, bool> isTickerOrSubtableIdGiven = (p_req) => {
if (!String.IsNullOrEmpty(ticker = p_req.Ticker))
{
int i = ticker.IndexOfAny("'\n".ToCharArray());
if (0 <= i) ticker = ticker.Substring(0, i); // beware of SQL injection
ticker = ticker.Trim().ToUpperInvariant();
}
isSimulated = (p_at == DbCommon.AssetType.Stock && ticker != null && ticker.EndsWith(".SQ"));
return (p_req.SubtableID.HasValue || !String.IsNullOrEmpty(ticker));
};
if ((p_reqs = p_reqs as IList<QuoteRequest> ?? p_reqs.ToList()).Count() < 20) // 20: because the number of UNION-able subSELECTs is limited:
{ // Compose a faster query: UNION of per-ticker SELECTs "Limited by available resources" -- stack space limit in query optimizer. goo.gl/MGO6Nb msdn.microsoft.com/en-us/library/ms143432.aspx
foreach (var grp in (p_reqs.Count() == 1) ? new[] { p_reqs } : p_reqs.ToLookup(qr => qr.ReturnedColumns)
as IEnumerable<IEnumerable<QuoteRequest>>)
{
parseCols("/*ColumnsBEGIN*/Ticker,[Date],[Open],High,Low,[Close],Volume,StockID/*ColumnsEND*/");
string sql = null, union = null, cols = null, vars;
foreach (QuoteRequest r in grp)
{
if (!isTickerOrSubtableIdGiven(r)) continue;
#region SqlTemplates
if (p_at == DbCommon.AssetType.BenchmarkIndex)
{
vars = @"DECLARE @ID777 INT = (SELECT ID FROM StockIndex WHERE Ticker='{0}');
DECLARE @Ticker777 VARCHAR(20) = (SELECT Ticker FROM StockIndex WHERE ID = @ID777);";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ [Date], OpenPrice AS [Open], HighPrice AS High
, LowPrice AS Low, ClosePrice AS [Close], Volume, StockIndexID AS StockID, @Ticker777 AS Ticker
FROM StockIndexQuote WHERE StockIndexID = @ID777 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
else if (p_at != DbCommon.AssetType.Stock) continue;
else if (!isSimulated)
{
vars = @"DECLARE @ID777 INT = (SELECT ID FROM Stock WHERE Ticker='{0}');
DECLARE @Ticker777 VARCHAR(20) = (SELECT Ticker FROM Stock WHERE ID = @ID777);";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ [Date], OpenPrice * adj.f AS [Open] , HighPrice * adj.f AS High
, LowPrice * adj.f AS Low, ClosePrice* adj.f AS [Close], Volume, StockID, @Ticker777 AS Ticker
FROM StockQuote
CROSS APPLY dbo.GetAdjustmentFactorAt2(StockID, [Date]) AS adj
WHERE StockID = @ID777 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
else
{
vars = @"BEGIN TRY -- avoid continue if error occurs
DECLARE @id777 INT, @idsq777 INT = (SELECT ID FROM Stock WHERE Ticker='{0}');
DECLARE @T777 VARCHAR(20), @Tsq777 VARCHAR(20) = (SELECT Ticker FROM Stock WHERE ID=@idsq777);
SELECT @T777=Ticker, @id777=ID FROM Stock WHERE Ticker=LEFT(@Tsq777,LEN(@Tsq777)-3);
IF (RIGHT(@Tsq777,3) <> '.SQ' OR @idsq777 IS NULL OR @id777 IS NULL)
-- The specified '@Tsq777' is invalid (valid values are returned by SELECT ...).
RAISERROR(14234,16,0,@Tsq777,'SELECT Ticker FROM Stock WHERE RIGHT(Ticker,3)=''.SQ''');
DECLARE @msg777 VARCHAR(MAX);
IF (EXISTS (SELECT * FROM StockSplitDividend WHERE StockID=@idsq777)) BEGIN
SET @msg777 = @Tsq777+' has nonzero StockSplitDividend records, which is not supported';
THROW 50000, @msg777, 0;
END;
DECLARE @Tbegin777 DATE = (SELECT TOP 1 [Date] FROM StockQuote WHERE StockID=@id777 ORDER BY [Date]);
IF (@Tbegin777 < (SELECT TOP 1 [Date] FROM StockQuote WHERE StockID=@idsq777 ORDER BY [Date] DESC)) BEGIN
SET @msg777 = @T777+' has quotes before the last quote of '+@Tsq777;
THROW 50000, @msg777, 1;
END;
DECLARE @adjsq777 FLOAT = (SELECT f FROM dbo.GetAdjustmentFactorAt2(@id777,@Tbegin777));
END TRY BEGIN CATCH THROW END CATCH;
";
query = @"
SELECT */*Columns*/
FROM (SELECT /*TopN*/ tt.* FROM (
SELECT Ticker=@Tsq777, StockID=@idsq777, [Date]=CAST([Date] AS DATE), Volume,
[Open] =CAST( OpenPrice*@adjsq777 AS DECIMAL(9,4)), High=CAST(HighPrice*@adjsq777 AS DECIMAL(9,4)),
[Close]=CAST(ClosePrice*@adjsq777 AS DECIMAL(9,4)), Low =CAST( LowPrice*@adjsq777 AS DECIMAL(9,4))
FROM StockQuote sq0 WHERE StockID=@idsq777
UNION ALL
SELECT Ticker=@T777, StockID=@id777, [Date]=CAST([Date] AS DATE), Volume,
[Open] =CAST( OpenPrice*adj.f AS DECIMAL(9,4)), High=CAST( HighPrice*adj.f AS DECIMAL(9,4)),
[Close]=CAST(ClosePrice*adj.f AS DECIMAL(9,4)), Low =CAST( LowPrice*adj.f AS DECIMAL(9,4))
FROM StockQuote sq1
CROSS APPLY dbo.GetAdjustmentFactorAt2(sq1.StockID,sq1.Date) adj
WHERE sq1.StockID=@id777
) AS tt WHERE 1=1 /*AND_DateRange*/ /*TopN_orderby*/) AS t
";
}
#endregion
string num = String.IsNullOrEmpty(sql) ? "0" : sql.Length.ToString("x");
vars = vars.Replace("777", num); query = query.Replace("777", num);
if (r.SubtableID.HasValue)
vars = System.Text.RegularExpressions.Regex.Replace(vars, @"(?i)\(SELECT[^)]*Ticker='\{0\}'\)", "{1}");
if (r.NonAdjusted)
query = System.Text.RegularExpressions.Regex.Replace(query,
@"(?i)(CROSS APPLY.*?dbo.GetAdjustmentFactorAt2.*?AS\s+adj\s*)|(\*\s*adj\.f\b)", "");
if ((uint)r.nQuotes < int.MaxValue || r.StartDate.HasValue || r.EndDate.HasValue)
query = query.Replace("/*TopN*/", "TOP " + (uint)r.nQuotes).Replace("/*TopN_orderby*/",
"ORDER BY [Date]" + (r.StartDate.HasValue && !r.EndDate.HasValue ? null : " DESC"));
if (r.StartDate.HasValue || r.EndDate.HasValue)
query = query.Replace("/*AND_DateRange*/", String.Format(
(r.StartDate.HasValue ? " AND '{0}'<=[Date]" : "") + (r.EndDate.HasValue ? " AND [Date]<='{1}'" : null),
r.StartDateStr, r.EndDateStr));
vars = String.Format(System.Globalization.CultureInfo.InvariantCulture, vars, ticker, r.SubtableID);
query = query.Replace("*/*Columns*/", cols ??
(cols = String.Join(",", qCols.Where((s, i) => (r.ReturnedColumns & (1 << i)) != 0))));
sql = vars + Environment.NewLine + sql + union + "(" + query + ")"; union = " UNION ALL ";
} //~ foreach(r in grp)
if (String.IsNullOrEmpty(sql)) continue;
if (p_isAscendingDates.HasValue)
sql += " ORDER BY [Date]" + (p_isAscendingDates.Value ? null : " DESC");
sqls[sql] = null;
}
}
// "Slower query": supports unlimited number of tickers at once, faster for numerous tickers
else foreach (QuoteRequest r in p_reqs)
{
if (!isTickerOrSubtableIdGiven(r)) continue;
if (query == null || isSimulated)
{
if (p_at == DbCommon.AssetType.BenchmarkIndex)
query = Sql_GetHistoricalStockIndexQuotes;
else if (p_at == DbCommon.AssetType.Stock)
query = isSimulated ? Sql_GetHistoricalSimulatedStockQuotes : Sql_GetHistoricalStockQuotes;
else
throw new NotSupportedException(p_at.ToString());
if (p_isAscendingDates.HasValue)
query += " ORDER BY [Date]" + (p_isAscendingDates.Value ? null : " DESC");
if (isSimulated) // this query does not support multiple tickers, so
query += new String(' ', sqls.Count); // make up different keys into sqls[]
parseCols(query);
if (isSimulated) query = null;
}
string sql = qHead + String.Join(",", qCols.Where((_, i) => (r.ReturnedColumns & (1 << i)) != 0)) + qTail;
string s, p = String.Join(",", ticker ?? "", r.SubtableID, r.StartDateStr, r.EndDateStr, r.nQuotes,
(p_at == DbCommon.AssetType.Stock && !r.NonAdjusted) ? "1" : null);
sqls[sql] = sqls.TryGetValue(sql, out s) ? s + "," + p : p;
}
var result = new List<object[]>();
await Task.WhenAll(sqls.Select(kv => ExecuteSqlQueryAsync(kv.Key, p_canc: p_canc,
p_params: kv.Value == null ? null : new Dictionary<string, object> { { "@p_request", kv.Value } })
.ContinueWith(t => result.AddRange(t.Result[0]))));
return result;
}