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; }