Dovberman
asked on
Append a table from the rows returned by a SQL Statement
A SQL statement returns rows and columns. This works well.
I need to append a table from the returned rows.
Currently I am using SQLDataReader to read column values into a dataset.
Then I am parsing the dataset one row at a time and building an array.
Then I am parsing the array one row at a time and appending the values to a table.
What do I need to do to Append a table from the rows returned by a SQL Statement.
The appended table can be a table that has been cleared. Then all rows might be appended in a single statement instead of building and parsing a dataset.
//SQL statement returns rows and columns.
SqlCommand cmdShowGrid = new SqlCommand("usp_getExcepti ons", conStockSelect);
cmdShowGrid.Connection = conStockSelect;
cmdShowGrid.CommandType = System.Data.CommandType.St oredProced ure;
cmdShowGrid.CommandTimeout = 1200;
cmdShowGrid.CommandText = "usp_getExceptions";
cmdShowGrid.Parameters.Cle ar();
// cmdShowGrid.Parameters.Add WithValue( "@MarketID ", SqlDbType.Int).Value = 2;
cmdShowGrid.Parameters.Add WithValue( "@CurrFrom Date", SqlDbType.Date).Value = dteSampleBCStart;
cmdShowGrid.Parameters.Add WithValue( "@CurrToDa te", SqlDbType.Date).Value = dteSampleABStart;
cmdShowGrid.Parameters.Add WithValue( "@PrevFrom Date", SqlDbType.Date).Value = dteSampleABEnd;
cmdShowGrid.Parameters.Add WithValue( "@PrevToDa te", SqlDbType.Date).Value = dteSampleBCEnd;
cmdShowGrid.Parameters.Add WithValue( "@Min5DayA vg", SqlDbType.Decimal).Value = 1.00;
cmdShowGrid.Parameters.Add WithValue( "@Max5DayA vg", SqlDbType.Decimal).Value = 100.00;
cmdShowGrid.Parameters.Add WithValue( "@Curr5Pre v8Min", SqlDbType.Decimal).Value = 1.05;
cmdShowGrid.Parameters.Add WithValue( "@Curr5Pre v8Max", SqlDbType.Decimal).Value = lstMaxCurr5Prev8.SelectedV alue; //Decimal.Add(decIncreases , decIncreasesPlus);
//using SQLDataReader to read column values into a dataset.
SqlDataAdapter adpExceptions = new SqlDataAdapter(cmdShowGrid );
DataSet dstExceptions = new DataSet();
adpExceptions.Fill(dstExce ptions, "Exceptions");
SqlDataReader rdrStockHist;
rdrStockHist = cmdShowGrid.ExecuteReader( );
double[,] dblArrException = new double[2000, 8]; // Numerical parameter values
string[,] strArrException = new string[2000, 2]; // String parameter values
//Parsing the dataset one row at a time and building an array.
if (rdrStockHist.HasRows) // Build the array
{
while (rdrStockHist.Read())
{
// Read the col values into arrays
dblArrException[intRow, 0] = Convert.ToDouble(rdrStockH ist["Symbo lID"]);
dblArrException[intRow, 1] = Convert.ToDouble(rdrStockH ist["Curr5 DayAvg"]);
dblArrException[intRow, 2] = Convert.ToDouble(rdrStockH ist["Prev8 DayAvg"]);
//dblArrException[intRow, 3] = Convert.ToDouble(rdrStockH ist["PctCh g"]);
//dblArrException[intRow,4 ] = Convert.ToDouble(rdrStockH ist["Marke tID"]);
//dblArrException[intRow, 5] = 0; Convert.ToDouble(rdrStockH ist["PctUp "]);
//strArrException[intRow,0 ] = rdrStockHist["SymbolName"] .ToString( );
//strArrException[intRow,1 ] = rdrStockHist["SiteURL"].To String();
intRow++;
}
} // End (rdrSymbols.Read())
rdrStockHist.Close(); // closing SqlDataReader
//Parsing the array one row at a time and appending the values to a table.
SqlCommand cmdapdExceptions = new SqlCommand("usp_apdExcepti ons", conStockSelect);
cmdapdExceptions.CommandTy pe = System.Data.CommandType.St oredProced ure;
cmdapdExceptions.CommandTi meout = 1200;
for (int i = 0; i < intRow; i++)
{
cmdapdExceptions.Parameter s.Clear();
cmdapdExceptions.Parameter s.Add("@Sy mbolID", SqlDbType.Int).Value = dblArrException[i, 0];
cmdapdExceptions.Parameter s.Add("@Cu rr5DayAvg" , SqlDbType.Float).Value = dblArrException[i, 1];
cmdapdExceptions.Parameter s.Add("@Pr ev8DayAvg" , SqlDbType.Float).Value = dblArrException[i, 2];
RunProcedure(cmdapdExcepti ons, "usp_apdExceptions");
} // End for (int i = 0; i < intRow; i++)
cmdapdExceptions.Dispose() ;
I need to append a table from the returned rows.
Currently I am using SQLDataReader to read column values into a dataset.
Then I am parsing the dataset one row at a time and building an array.
Then I am parsing the array one row at a time and appending the values to a table.
What do I need to do to Append a table from the rows returned by a SQL Statement.
The appended table can be a table that has been cleared. Then all rows might be appended in a single statement instead of building and parsing a dataset.
//SQL statement returns rows and columns.
SqlCommand cmdShowGrid = new SqlCommand("usp_getExcepti
cmdShowGrid.Connection = conStockSelect;
cmdShowGrid.CommandType = System.Data.CommandType.St
cmdShowGrid.CommandTimeout
cmdShowGrid.CommandText = "usp_getExceptions";
cmdShowGrid.Parameters.Cle
// cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
cmdShowGrid.Parameters.Add
//using SQLDataReader to read column values into a dataset.
SqlDataAdapter adpExceptions = new SqlDataAdapter(cmdShowGrid
DataSet dstExceptions = new DataSet();
adpExceptions.Fill(dstExce
SqlDataReader rdrStockHist;
rdrStockHist = cmdShowGrid.ExecuteReader(
double[,] dblArrException = new double[2000, 8]; // Numerical parameter values
string[,] strArrException = new string[2000, 2]; // String parameter values
//Parsing the dataset one row at a time and building an array.
if (rdrStockHist.HasRows) // Build the array
{
while (rdrStockHist.Read())
{
// Read the col values into arrays
dblArrException[intRow, 0] = Convert.ToDouble(rdrStockH
dblArrException[intRow, 1] = Convert.ToDouble(rdrStockH
dblArrException[intRow, 2] = Convert.ToDouble(rdrStockH
//dblArrException[intRow, 3] = Convert.ToDouble(rdrStockH
//dblArrException[intRow,4
//dblArrException[intRow, 5] = 0; Convert.ToDouble(rdrStockH
//strArrException[intRow,0
//strArrException[intRow,1
intRow++;
}
} // End (rdrSymbols.Read())
rdrStockHist.Close(); // closing SqlDataReader
//Parsing the array one row at a time and appending the values to a table.
SqlCommand cmdapdExceptions = new SqlCommand("usp_apdExcepti
cmdapdExceptions.CommandTy
cmdapdExceptions.CommandTi
for (int i = 0; i < intRow; i++)
{
cmdapdExceptions.Parameter
cmdapdExceptions.Parameter
cmdapdExceptions.Parameter
cmdapdExceptions.Parameter
RunProcedure(cmdapdExcepti
} // End for (int i = 0; i < intRow; i++)
cmdapdExceptions.Dispose()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think your INSERT query can be optimized. The StockHist table is queried many times. let me know.
ASKER
Yes, the StockHist table is queried many times.
How can this be optimized?
Thanks,
How can this be optimized?
Thanks,
ASKER
That worked.
Thanks
Thanks
ASKER
Thanks
I will try this and give feedback tomorrow.
INSERT INTO TargetTableName (field1, field2, ..., fieldN)
SELECT
w.UserName
, w.SymbolID
, @FromDate AS FromDate
, @ToDate AS ToDate
, (SELECT SymbolName FROM Symbol WHERE SymbolID = w.SymbolID) AS SymbolName
, (SELECT SUBSTRING(SecName, 1, 20) FROM Symbol WHERE SymbolID = w.SymbolID) AS SecName
, w.MarketID
, (SELECT MarketName FROM Market
WHERE MarketID = w.MarketID)
AS MarketName
, w.WatchListID
, w.PickDate
, @ResearchSiteURL AS SiteURL
, @SiteEvalType AS SiteEvalType
, (SELECT Count(*) FROM DownLoadDates
WHERE QuoteDate >=(w.PickDate +1 ) AND MarketID = 1) As DaysOnList
, (SELECT PickDatePrice FROM WatchList
WHERE PickDate=w.PickDate
AND SymbolID=w.SymbolID)
AS PickPrice
, (SELECT ClosePrice FROM StockHist
WHERE QuoteDate=@FromDate
AND SymbolID=w.SymbolID)
AS BasePrice
, (SELECT COUNT(*) FROM StockHist
WHERE ClosePrice > ClosePricePrev
AND QuoteDate BETWEEN w.PickDate AND @AsOfDate
AND SymbolID=w.SymbolID )
AS PriceUpCount
--Pricechange
, (SELECT Top(1) ClosePrice FROM StockHist
WHERE QuoteDate=@AsOfDate
AND SymbolID=w.SymbolID)
-
(SELECT ClosePrice FROM StockHist
WHERE QuoteDate=@FromDate
AND SymbolID=w.SymbolID)
AS PriceChange
, (SELECT Top(1) ClosePrice FROM StockHist
WHERE QuoteDate=@AsOfDate
AND SymbolID=w.SymbolID)
AS CurrentPrice
, (SELECT 2* STDEV(HighPrice-LowPrice)
FROM StockHist WHERE SymbolID=w.SymbolID)
AS CI95
-- PctChange = ((CurrentPrice - BasePrice)/BasePrice) * 100
-- Fix -- PctChange = ((CurrentPrice - PickPrice)/PickPrice) * 100
,( ( (SELECT Top(1) ClosePrice FROM StockHist
WHERE QuoteDate=@AsOfDate
AND SymbolID=w.SymbolID)
-
(SELECT ClosePrice FROM StockHist
WHERE QuoteDate=@FromDate
AND SymbolID=w.SymbolID) )
/
(SELECT ClosePrice FROM StockHist
WHERE QuoteDate=@FromDate
AND SymbolID=w.SymbolID) ) * 100
AS PctChange
FROM Watchlist w
WHERE w.UserID='0A742A71-B5D5-4E