|
Hi,
I am using the following codes to update an excel spreadsheet:
The problem is that I have to do this update for hundreds of records with different Security_IDs and it takes about 2 seconds per record to update. i.e. command.ExecuteNonQuery(); takes about 2 seconds for each update.
How can I modify this code to do all these updates quickly?
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MarketAnalytics\YieldsSpreads\YieldsSpreads.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
command.CommandText = strSQL;
connection.Open();
command.ExecuteNonQuery();
}
}
|
|
|
|
|
to start, how about reusing one DB connection instead of creating a new one for each record, and don't forget to Close your connection, either . . .
|
|
|
|
|
This is my exact code.
can you see where to change it to the way you suggeste please?
I am looping through a datagridview control and for each record in the control I am updating the excel sheet.
try
{
//get prices for securities...
int intSecurity_IDColumn = dgvSecurityPricing.Columns["Security_ID"].Index;
int intSecurity_NameColumn = dgvSecurityPricing.Columns["Security_Name"].Index;
int intBid_Price_Best_LatestColumn = dgvSecurityPricing.Columns["Bid_Price_Best_Latest"].Index;
int intAsk_Price_Best_LatestColumn = dgvSecurityPricing.Columns["Ask_Price_Best_Latest"].Index;
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MarketAnalytics\YieldsSpreads\YieldsSpreads.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
foreach (DataGridViewRow row in dgvSecurityPricing.Rows)
{
string strSecurity_ID = row.Cells[intSecurity_IDColumn].Value.ToString();
string strSecurity_Name = row.Cells[intSecurity_NameColumn].Value.ToString();
string strBid_Price_Best_Latest = row.Cells[intBid_Price_Best_LatestColumn].Value.ToString();
string strAsk_Price_Best_Latest = row.Cells[intAsk_Price_Best_LatestColumn].Value.ToString();
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
//only send prices if there is one...
string strSQL = "";
if (strBid_Price_Best_Latest.Length > 0 && strAsk_Price_Best_Latest.Length > 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
}
if (strBid_Price_Best_Latest.Length > 0 && strAsk_Price_Best_Latest.Length == 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = 0 WHERE Security_ID = " + strSecurity_ID;
}
if (strBid_Price_Best_Latest.Length == 0 && strAsk_Price_Best_Latest.Length > 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = 0, Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
}
if (strBid_Price_Best_Latest.Length == 0 && strAsk_Price_Best_Latest.Length == 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = 0, Ask_Price = 0 WHERE Security_ID = " + strSecurity_ID;
}
//command.CommandText = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
command.CommandText = strSQL;
//Console.WriteLine(strSQL);
connection.Open();
command.ExecuteNonQuery();
}
}
}
}
|
|
|
|
|
move the
arkiboys wrote: foreach (DataGridViewRow row in dgvSecurityPricing.Rows)
loop inside of the
arkiboys wrote: using (DbCommand command = connection.CreateCommand())
statement. That way you're creating a new SQL command string for each row in the datagrid and using the same DbConnection and DbCommand objects over and over again instead of destroying them after each row is updated
p.s. put < pre > tags around your code in posts to preserve the formatting, that'll make it easier on the eyes
Last modified: 21mins after originally posted --
|
|
|
|
|
that if block where you assign strSQL is pretty inefficient in itself. There are two basic conditions that are being tested:
1) strBid_Price_Best_Latest.Length > 0
2) strAsk_Price_Best_Latest.Length > 0
but each variable is compared with zero 4 different times using different operators. That whole block can be reduced to 3 comparisons (down from 8) with the use of else blocks:
if (strBid_Price_Best_Latest.Length > 0)
{
if (strAsk_Price_Best_Latest.Length > 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
}
else
{
strSQL = @"Update [Allbonds$] Set Bid_Price = '" + strBid_Price_Best_Latest + "', Ask_Price = 0 WHERE Security_ID = " + strSecurity_ID;
}
}
else
{
if (strAsk_Price_Best_Latest.Length > 0)
{
strSQL = @"Update [Allbonds$] Set Bid_Price = 0, Ask_Price = '" + strAsk_Price_Best_Latest + "' WHERE Security_ID = " + strSecurity_ID;
}
else
{
strSQL = @"Update [Allbonds$] Set Bid_Price = 0, Ask_Price = 0 WHERE Security_ID = " + strSecurity_ID;
}
}
Also, if you have "System.Data.OleDb" hardcoded in your program, then why use a Factory method to get your DB connection? You could just use OleDbConnection and OleDbCommand objects instead . . .
In other news, I'm having a very slow day at work 
|
|
|
|
|
|
Hi all.
I have to read from oracle data base large amount of data (about 5000 records), and i use for this purpose standard procedure
oraDataTableAdapter.Fill(oraDataTable)
, but this working very slow (about 15 seconds). In other side when i fill data table from MS SQL (with same number of records) its occupied mach less time. How i can to decrease time consumption for this operation for Oracle.
THANK
|
|
|
|
|
Convert to SQL Server
only two letters away from being an asset
|
|
|
|
|
5000 records isn't really that much data, even a whole second would be too slow for getting the data.
Do you have any means of running the query in a management tool in the database, so that you see if the query itself is slow, or the transfer to the DataTable.
If the query itself is slow, check if you have indexes on the appropriate fields in the database tables.
If the transfer is slow, check your connection string. On connectionstrings.com[^] you can find examples of connection strings for several different purposes.
Experience is the sum of all the mistakes you have done.
|
|
|
|
|
Thank
Query work fine in managment tool. As such as i think that problem in Oracle framework.
And how connection string can affect speed of data retriving????
|
|
|
|
|
El'Cachubrey wrote: And how connection string can affect speed of data retriving????
Different database providers perform differently in certain situations. Also, the connection string can contain settings that may affect the performance of a provider.
Have you examined the network traffic when the application runs the query? This could tell you if it's connecting to the database or transfering the data that takes time. It could also tell you if the amount of data looks reasonable, or if there is a lot of overhead.
Experience is the sum of all the mistakes you have done.
|
|
|
|
|
MS has biased .NET to work well with SQL Server. If you want to go really fast don't use fill. Instead create type objects and use the datareader as well as appropriate Where clauses. In my experience, Oracle (when done correctly) will vastly outperform SQL Server.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
Thank.
Ennis Ray Lynch, Jr. wrote: In my experience, Oracle (when done correctly) will vastly outperform SQL Server.
You mean what Oracle data reading is done correctly only through DataReader????
I just wander what internaly Fill method implemented throught DataReader isn't it????
|
|
|
|
|
El'Cachubrey wrote: You mean what Oracle data reading is done correctly only through DataReader????
Of course not. A data reader is slightly faster as there is less overhead, but the difference is far from what you describe.
El'Cachubrey wrote: I just wander what internaly Fill method implemented throught DataReader isn't it????
Yes. The difference is the overhead that is added by the DataTable.
Experience is the sum of all the mistakes you have done.
|
|
|
|
|
Hi all.
I need help converting an html file to pdf in code.
I've had a look at some trial code (Sautinsoft and ExpertPdf), it works, but it places a watermark on the generated pdf saying I should purchase the entire component. Is there a free way to do this? I'm looking at Itext# and i do not think it will cut it.
Any help would be much appreciated.
Thanks
M
|
|
|
|
|
Try SharpPDF
http://sharppdf.sourceforge.net/
|
|
|
|
|
Hi.
Im writing a program that uploads a file to a FTP server, after it has done that i want it to compare the uploaded file with the one on the harddrive.
Anyone know how to check for uploaded files and check that the files are identical in size ?
//Regards M
|
|
|
|
|
FileInfo info = new FileInfo(file_to_check)
if(info.Exists)
long len = info.Length
only two letters away from being an asset
|
|
|
|
|
i use a method that looks like this
public bool upload(string fileName)
{
FileInfo fileInf = new FileInfo(fileName);
string uri = "ftp://" + FtpServerIP + "/" + fileInf.Name;
FtpWebRequest reqFTP;
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + FtpServerIP + "/" + fileInf.Name));
reqFTP.Credentials = new NetworkCredential(FtpUserID, FtpPassword);
reqFTP.KeepAlive = false;
reqFTP.Method = WebRequestMethods.Ftp.UploadFile;
reqFTP.UseBinary = true;
reqFTP.ContentLength = fileInf.Length;
if (fileInf.Exists)
{
return false;
}
else
{
int buffLenght = 20482;
byte[] buff = new byte[buffLenght];
int contentLen;
FileStream fs = fileInf.OpenRead();
try
{
Stream strm = reqFTP.GetRequestStream();
contentLen = fs.Read(buff, 0, buffLenght);
while (contentLen != 0)
{
strm.Write(buff, 0, contentLen);
contentLen = fs.Read(buff, 0, buffLenght);
}
strm.Close();
fs.Close();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Upload Error");
}
}
return true;
}
how do i implement what u suggested correcly ?
|
|
|
|
|
Perhaps I'm misunderstanding. You have the file you are uploading, FileInfo fileInf = new FileInfo(fileName); What is the point it comparing the uploaded version of the same file?
only two letters away from being an asset
|
|
|
|
|
Well i want to be sure that the upload was completed succesfully, later i am deleting the file from the harddrive, thats why I need to do the check.
|
|
|
|
|
Generally, I'd say if you got no exceptions, then it succeeded. Usually the simple solution is the one that works best.
only two letters away from being an asset
|
|
|
|
|
hi my aim is to close a open socket ....that too perfectly
socket.close() is raising exceptions ..any methods to close it forcefully
its me sid
|
|
|
|
|
This is just a quess, but maybe try doing a socket.Disconnect() first?
I'm going to become rich when I create a device that allows me to punch people in the face over the internet.
"If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer
|
|
|
|
|
I have some questions as below :
1.I want to connect to my application which is intalled in another country,
and the clients who work with this app have low speed internet and they are very beginner
now i have this problem that how i can use some apps for connecting to them or how i can
embed such API in my app when user clicks on that it automatically send the its desktop for me
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
