|
hi,
a lot of data 1000000, there is no error the application takes too long to display (3hours).
|
|
|
|
|
all righty, that's already more clear.
First of all, 1 000 000 records in one go is just a big NO.
As an exercise calculate the size (in bytes) of one record and multiply that by 10^6. That amount of data is transferred through the wire... In addition your objects in code will also be too large memory wise.
Your best bet is to fetch this stuff in bits and parts. The first idea that comes to mind is to use paging, although for a million records this still a 1000 pages of a 1000 records.
In addition to that paging you'll need to filter the data to what users would like to see. You start with an empty grid and allow the user to filter on several columns before fetching anything.
3 hours is a long time. Make sure the query is optimized and the indexes on the database are properly built.
You'll probably need to fiddle with both the code part and the database in order to come to a good solution.
As rule of thumb any collection larger then let's say 10 000 is a no go. (I'm talking normal records, not blobs with huge amounts of data). (This number is not a rule, 100 can be already too much and in some cases 50 000 can still work ok)
This is not a simple problem, take your time to handle it right.
Hope this helps.
|
|
|
|
|
hello :
I am trying to develop an application in C # I made my connection string to connect to the database but when I launch an error message saying : An unhandled exception of type ' System.InvalidOperationException exception 'occurred in System.Data.dll
Additional information: ExecuteNonQuery : the Connection property has not been initialized.
Thank you
|
|
|
|
|
The error is pretty explicit:
the Connection property has not been initialized.
So look at your code: check that you have created an SqlConnection object using the correct connection string, that you have opened the connection, and that you have attached it to the SqlCommand:
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("SELECT iD, description FROM myTable", con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = (int) reader["iD"];
string desc = (string) reader["description"];
Console.WriteLine("ID: {0}\n {1}", iD, desc);
}
}
}
}
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
I put :
private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select informix.thisent.etb from informix.thisent";
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView1.DataSource = dt;
connection.Close();
|
|
|
|
|
Firstly, tell us such things when you post a question - remember that we can't see your screen, access your HDD, or read your mind!
So - you open a connection, but you don't do anything else with it?
either add:
cmd.Connection = connection; Or use the syntax I showed in my previous answer.
In the case of your code, I'd do this instead:
private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("SELECT informix.thisent.etb FROM informix.thisent", connection);
da.Fill(dt);
dataGridView1.DataSource = dt;
}
As the DataAdapter will open and close the connection for you.
BTW: Please, don;t use Visual Studio default names for controls! textBox1 doesn;t mean anything to anyone reading this code, where as searchForText makes it much more obvious what you are doing. It's not a problem when your apps are tiny, but when the number of controls starts to rise it becomes a real PITA and can cause a lot of mistakes. It's well worth getting into the habit of changing the name immediately you create the control as (compared to the five or six seconds extra it takes) it can save a heck of a lot of time later!
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Thank you for your interesting response .
I do not have to eureur .
I would change the names of the textbox
I put a textbox to get the value in the db and display in the DataGridView .
But when I run the application and I try to put a value in the text box the application starts to load and just stand .
its starts to load but without result and it crashes
Thank you
|
|
|
|
|
Have you had a good look with the debugger?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
there is no error , I think it's because he has too much data there.
|
|
|
|
|
So restrict the amount it is returning:
SELECT TOP 100 informix.thisent.etb FROM informix.thisent would at least confirm that is the problem.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
I have another error :
An unhandled exception of type ' System.Data.Odbc.OdbcException ' occurred in System.Data.dll
Additional Information : ERROR [ 42000 ] [ SCO Vision ] [ODBC Driver] SQL Server Retriever - Syntax error near '100
|
|
|
|
|
Are you actually connecting to Informix here? If you are, I think you'll probably find that you are trying to use the SQL Server data classes to access them - hence the reason the connection doesn't open.
|
|
|
|
|
I put another code :
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new OdbcConnection();
con.ConnectionString = @"Dsn=***;Driver={SCO Vision ODBC};uid=***;Pwd=****";
con.Open();
con);
adap = new OdbcDataAdapter("SELECT TOP 100 informix.thisent.etb FROM informix.thisent", con);
Ds = new System.Data.DataSet();
adap.Fill(Ds, "ibr");
dataGridView1.DataSource = Ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("ERREUR\n" + ex.Message, "ERREUR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
BUT I have an error :
error [42S22][SCO VISION][ODBC DRIVER][INFORMIX] column (top) not found in any table in the query .
|
|
|
|
|
That's because you are using illegal syntax in Informix. My Informix is a bit rusty, but I'm pretty sure you're meant to use SELECT FIRST 100 ... to get the data back. TOP is a SQL Server keyword.
|
|
|
|
|
It's good, I would give you a gift when you come in France
|
|
|
|
|
You must instantiate the connection and then open a database. Here is an example
OdbcConnection cnn = new OdbcConnection("DSN=DATABASE");
cnn.Open();
The parameter in this case would be the connection string. There are many different ways the connection string may be formed. It depends partially on which database type you are opening (SQL Server, MsAccess, Oracle, etc)
|
|
|
|
|
Hi, I want the Generate xls file in Window Service Location Generate Client Side, if anyone can help me , Thanks
|
|
|
|
|
Member 8414501 wrote: if anyone can help me Will do, as soon as you have a question.
As a side-note; a Windows-service is something that runs on the PC before the user logs on, does not show a UI and usually runs under other credentials than the current user.
Did you mean a "web service"?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
In my login form i have loaded role dropdown from database.
When user clicks on login button (while not selecting any role from dropdown.)
i have to retain same login page. but again roles dropdown is loading from database.
How can i avoid database hit another time in login page when validation fails in login page ?
Please help me.
kranthikumar2555@gmail.com
Kranthikumar
|
|
|
|
|
|
You have a valid SMS Gateway to send to, you have a valid SMS Gateway Domain id, and there is no reason for that Gateway to reject a message from the originating party based on their location or any other factor: correct ?
If your end-users have the Skype client, have you considered the Skype API's: [^].
« There is only one difference between a madman and me. The madman thinks he is sane. I know I am mad. » Salvador Dali
modified 23-Oct-14 13:18pm.
|
|
|
|
|
I found this class on a forum...
How do I use it - I can't seem to callthe Difference method in bold text?
I'm currently trying...
DateDiff dateDiff = new DateDiff();
dateDiff.Difference((DateTime)StartDate, (DateTime)EndDate); // Doesn't Work
Many thanks for any help.
public class DateDiff
{
private int _Years = 0;
private int _Months = 0;
private int _Weeks = 0;
private int _Days = 0;
private int _Hours = 0;
private int _Minutes = 0;
private int _Seconds = 0;
public int Years { get { return _Years; } }
public int Months { get { return _Months; } }
public int Weeks { get { return _Weeks; } }
public int Days { get { return _Days; } }
public int Hours { get { return _Hours; } }
public int Minutes { get { return _Minutes; } }
public int Seconds { get { return _Seconds; } }
public override string ToString()
{
return String.Format("{0} year{1}, {2} month{3}, {4} week{5}, {6} day{7}, {8} hour{9}, {10} minute{11}, {12} second{13}"
, Years, Years != 1 ? "s" : ""
, Months, Months != 1 ? "s" : ""
, Weeks, Weeks != 1 ? "s" : ""
, Days, Days != 1 ? "s" : ""
, Hours, Hours != 1 ? "s" : ""
, Minutes, Minutes != 1 ? "s" : ""
, Seconds, Seconds != 1 ? "s" : ""
);
}
static public DateDiff Difference(DateTime dt1, DateTime dt2)
{
if (dt1 > dt2)
{
DateTime dtTemp = dt1;
dt1 = dt2;
dt2 = dtTemp;
}
DateDiff dd = new DateDiff();
dd._Years = dt2.Year - dt1.Year;
if (dd._Years > 0)
{
if (dt2.Month < dt1.Month)
{
dd._Years--;
}
else if (dt2.Month == dt1.Month)
{
if (dt2.Day < dt1.Day)
{
dd._Years--;
}
else if (dt2.Day == dt1.Day)
{
if (dt2.Hour < dt1.Hour)
{
dd._Years--;
}
else if (dt2.Hour == dt1.Hour)
{
if (dt2.Minute < dt1.Minute)
{
dd._Years--;
}
else if (dt2.Minute == dt1.Minute)
{
if (dt2.Second < dt1.Second)
{
dd._Years--;
}
}
}
}
}
}
dd._Months = dt2.Month - dt1.Month;
if (dt2.Month < dt1.Month)
{
dd._Months = 12 - dt1.Month + dt2.Month;
}
if (dd._Months > 0)
{
if (dt2.Day < dt1.Day)
{
dd._Months--;
}
else if (dt2.Day == dt1.Day)
{
if (dt2.Hour < dt1.Hour)
{
dd._Months--;
}
else if (dt2.Hour == dt1.Hour)
{
if (dt2.Minute < dt1.Minute)
{
dd._Months--;
}
else if (dt2.Minute == dt1.Minute)
{
if (dt2.Second < dt1.Second)
{
dd._Months--;
}
}
}
}
}
dd._Days = dt2.Day - dt1.Day;
if (dt2.Day < dt1.Day)
{
dd._Days = DateTime.DaysInMonth(dt1.Year, dt1.Month) - dt1.Day + dt2.Day;
}
if (dd._Days > 0)
{
if (dt2.Hour < dt1.Hour)
{
dd._Days--;
}
else if (dt2.Hour == dt1.Hour)
{
if (dt2.Minute < dt1.Minute)
{
dd._Days--;
}
else if (dt2.Minute == dt1.Minute)
{
if (dt2.Second < dt1.Second)
{
dd._Days--;
}
}
}
}
dd._Weeks = dd._Days / 7;
dd._Days = dd._Days % 7;
dd._Hours = dt2.Hour - dt1.Hour;
if (dt2.Hour < dt1.Hour)
{
dd._Hours = 24 - dt1.Hour + dt2.Hour;
}
if (dd._Hours > 0)
{
if (dt2.Minute < dt1.Minute)
{
dd._Hours--;
}
else if (dt2.Minute == dt1.Minute)
{
if (dt2.Second < dt1.Second)
{
dd._Hours--;
}
}
}
dd._Minutes = dt2.Minute - dt1.Minute;
if (dt2.Minute < dt1.Minute)
{
dd._Minutes = 60 - dt1.Minute + dt2.Minute;
}
if (dd._Minutes > 0)
{
if (dt2.Second < dt1.Second)
{
dd._Minutes--;
}
}
dd._Seconds = dt2.Second - dt1.Second;
if (dt2.Second < dt1.Second)
{
dd._Seconds = 60 - dt1.Second + dt2.Second;
}
return dd;
}
}
|
|
|
|
|
Why not use the .NET DateTime Structure[^] which offers such features, and more? And. more importantly, it is fully documented and supported.
|
|
|
|
|
Try:
DateDiff diff = DateDiff.Difference(dt1, dt2);
But...You do realize there is a TimeSpan structure already in the .NET framework?
MSDN[^]
So you could just say:
TimeSpan diff = dt2 - dt1;
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Thanks for replying. I realize there is a TimeSpan but it's no use to me as it doesn't show Weeks or Years elapsed and I need them.
Your example worked (many thanks) - I hadn't noticed that the method was static...
|
|
|
|