Insert into database through loop

Question

I am using asp.net with c#
I have a confusion.see the below code.

while (itr.MoveNext()) //this loop will rotate 26 time coz the value of itr is 26
{
Hashtable quote = ((Hashtable)(itr.Current));
Response.Write(("Quote Symbol:" + quote["symbol"]));
Response.Write(("Title:" + quote["title"])); //1
Response.Write(("Time:" + quote["time"]));

if ((fxfeed.getInterval() == 1))
{
if (fxfeed.getPrice().Equals("bid,ask"))
{
Response.Write(("Bid:" + quote["bid"]));
Response.Write(("Ask:" + quote["ask"]));
}
else
{
Response.Write(("Price:" + quote["price"]));
}
}
else
{
Response.Write(("Open:" + quote["open"])); //2
Response.Write(("High:" + quote["high"])); //3
Response.Write(("Low:" + quote["low"])); //4
Response.Write(("Close:" + quote["close"])); //5
}

as i commented that the while loop will rotate 26 times. so each time lines which are represented by number comments(//1,//2 etc) these will be printed on screen. it means the at the completion of the while loop 107 values will be printed(which are represented by comments numbers). i have a database and want to store those 107 values in 107 cols in a database table. so can somebody please tell me where to put the sql command to insert the data and what do i need to write in that command. please help me.

Zoran Horvat · Answer

I've created a procedure named UpsertDailyChange which inserts or updates data to these tables. Here is the complete create database listing. CREATE DATABASE Forex; GO USE Forex; GO CREATE TABLE Currency ( CurrencyID INT IDENTITY(1 1) PRIMARY KEY Symbol VARCHAR(10) NOT NULL Description VARCHAR(200) ); CREATE TABLE DailyChange ( DailyChangeID INT IDENTITY(1 1) PRIMARY KEY CurrencyID INT FOREIGN KEY REFERENCES Currency(CurrencyID) Date DATE NOT NULL Opening MONEY NOT NULL Low MONEY NOT NULL High MONEY NOT NULL Closing MONEY NOT NULL ); GO CREATE PROCEDURE UpsertDailyChange @currencySymbol VARCHAR(10) @currencyDescrption VARCHAR(200) @date DATE @opening MONEY @low MONEY @high MONEY @closing MONEY AS BEGIN DECLARE @currencyID INT; DECLARE @dailyChangeID INT; SELECT @currencyID = CurrencyID FROM Currency WHERE Symbol=@currencySymbol; IF @currencyID IS NULL BEGIN INSERT INTO Currency(Symbol Description) VALUES (@currencySymbol @currencyDescrption); SELECT @currencyID=@@IDENTITY; END SELECT @dailyChangeID = DailyChangeID FROM DailyChange WHERE CurrencyID=@currencyID AND Date=@date; IF (@dailyChangeID IS NULL) INSERT INTO DailyChange(CurrencyID Date Opening Low High Closing) VALUES (@currencyID @date @opening @low @high @closing); ELSE UPDATE DailyChange SET Opening=@opening Low=@low High=@high Closing=@closing WHERE DailyChangeID=@dailyChangeID; END; GO The procedure accepts currency symbol and its description and then all information about the daily change for that symbol. If currency does not exist in the Currency table it will be inserted. If daily change for given currency and date does not exist it will be inserted. Otherwise if daily change does exist then only opening/closing and low/high fields will be updated in that row (therefore the name UpsertDailyChange). This procedure can be called from SQL Server Management Studio for testing purposes: EXEC UpsertDailyChange 'RSD' 'Serbian Dinar' {d '2011-07-14'} 1 2 9 6; EXEC UpsertDailyChange 'RSD' 'Serbian Dinar' {d '2011-07-15'} 6 4 5 4; EXEC UpsertDailyChange 'RSD' 'Serbian Dinar' {d '2011-07-16'} 4 1 5 3; EXEC UpsertDailyChange 'RSD' 'Serbian Dinar' {d '2011-07-15'} 6 4 8 4; These statements are inserting daily changes for period July 14-16. Last statement updates July 15 record. Resulting data are collected with query: SELECT * FROM DailyChange ORDER BY Date; And here are the data: DailyChangeID CurrencyID Date Opening Low High Closing 1 1 2011-07-14 1.00 2.00 9.00 6.00 2 1 2011-07-15 6.00 4.00 8.00 4.00 3 1 2011-07-16 4.00 1.00 5.00 3.00 Now do you need help to call this procedure from the C# code? Zoran

Anil Kumar · Answer

Gr8 work Zoran !!! I Like the answer.

saifullah khan · Answer

thank u sooo much sir. i love u sir. u are the the great for greatest. i dont no how to say thanks to u. thank u sir GOD BLESS U.

saifullah khan · Answer

yes sir up to this point it was 100% correct. i enters all the currency symbols and their discriptions. now the question is how it will it will take openhighlow and close values and store it in dailychanges table. please its the last step please help me.

Zoran Horvat · Answer

Then what you might need is this. One table named Currency would contain strong entities - one row for each currency existing which would turn out to be 26 rows in total. Second table named DailyChange would be dedicated to showing opening low high and closing prices for one currency and one day. Create table script (for MSSQL) would look like this: CREATE DATABASE Forex; GO USE Forex; GO CREATE TABLE Currency ( CurrencyID INT IDENTITY(1 1) PRIMARY KEY Symbol VARCHAR(10) NOT NULL Description VARCHAR(200) ); CREATE TABLE DailyChange ( DailyChangeID INT IDENTITY(1 1) PRIMARY KEY CurrencyID INT FOREIGN KEY REFERENCES Currency(CurrencyID) Date DATE NOT NULL Opening MONEY NOT NULL Low MONEY NOT NULL High MONEY NOT NULL Closing MONEY NOT NULL ); GO Please go through these objects with care and tell if that is what you need. Then we can continue with more details. Zoran

saifullah khan · Answer

sir actually i am creating a website that will take feeds about currency openinghighlowclose rates from forex.net. i want to take the feeds of 26 pair of currencies and store them in database. mean 26 pair of currencieseach has openinghighlowand close values(means each pair has four values) so 26*4=104 columns must be in database to store these values. that is the problem and m confuze how to solve it.

Zoran Horvat · Answer

Saifullah Depending on fxfeed.getInterval() and fxfeed.getPrice() results which seemingly doesn't change in this loop number of values printed out is either 130 (26 * 5) (getInterval() returns value other than 1) or 78 (26 * 3) (getInterval() == 1 and getPrice() == bidask) or 52 (26 * 2) (getInterval() == 1 and getPrice() != bidask). Now regarding 100+ columns in the database table that is clearly a bad and I mean very bad design. You should break these data into more convenient using some normal forms etc. If you need help about that just ask and I'll try to help you design the tables (in that case please provide detailed specification what exactly you are inserting into database). Otherwise if you still insist in creating 100+ columns then you might end up in building incredibly long and completely unreadable query. Hence again I suggest you to redesign database. Zoran