how to handle massive amounts of data in a data table

Hi,

I have over 200,000 records in a database and need the user to be able to view the data and edit some records. I have an editable data grid setup but when I query stuff to try to show around 30,000 in a table it just crashes.

Thanks

Why not page it with like 100 per page or do they need to see all 30,000 at once?

200,000 records in 1 page... in one go.. yea that will mess up your computer.

This is due to the raw amounts of data being passed to the clients computer from your webpage, and the viewstate is probably massive as well.

What you need to do is bring in paging for your DataGrid, as well as filtering (Date Filtering would be the easiest if you have date logs for the data your returning).

You also might want to turn EnableViewState on your DataGrid to false. This will force you to REBIND after everypostback, but it will not store the datasource in the viewstate (this is a big issue for large recordsets).

Also, look into paging.

----

If you wanted to play around to get more information on this, change your select statement to only select the top 1,000 rows. Then render it and view the properties of the page (right click->properties). Look at the size of the page.

Then increase it to 5,000 rows, 10,000 rows, 20,000 rows etc. and you will see how much data is being transfered to the client. As well, think about what the browser needs to do in order to render that data.

thanks ... I have decided that it is best to only allow them to see the records week by week.

How to handle large result sets in Windows Forms

A typical szenario in a database-centered application is that a user
(accidentally or on purpose) requests data that would result in a very
large result set, may be millions of rows.
I really like the way how MS Access handles that problem. The first
records are displayed as soon as they are available. When the user
scrolls down he or she experiences the display of the further records
as if they were available locally. Jumping to record number 1,000,000
also as little delay, the same applies for jumping to the end.
Is it possible to achieve the same or a similar behavior in ADO.NET
and Windows Forms? Can I still rely on data binding?
My approach would be:
- Using a background thread for the query to keep the application
responsive and give the user an opportunity to abort the action.
- Using a DataReader to fill a DataTable with the first n records.
- Data bind the DataTable.
- Handle scrolling down or jumping to a specified record by reading
forward from the DataReader into the DataTable. That can't be done
in the background thread, I suppose.
The problem is that I need to keep open the connection all the time
(or until the user scrolles to the end) which has a strong impact on
scalability.
I have a basic idea of how to handle paging in ADO.NET.
How do I support sorting in the DataGrid?
I suppose that is a frequent requirement. There should be some kind of
a design pattern - or even a framework or third-party tool that
supports such an implementation. Does anyone know more?
Thank you in advance.
Daniel FaensenHi Dan,
This is a continual issue. I looked into it extensively about 1 and a half
years ago, and I came up with certain workarounds, but nothing too great.
First, a datareader is not the way to go - it's a fire hose cursor and
doesn't make it possible to do very much very effectively, even searching.
Second, you can always use ado through interop services to bind to a
datasource CONNECTED, not disconnected, as ado .net provides using a
datatable. But you lose a lot of the benefits of ado .net, especially it's
disconnected, non-clashing nature, and it's enormous speed when data is in
memory.
But I think the best solution may be a third party grid, like C1, which I
use (many of the others are probably fine also). C1 also has a data access
class that can do what you described re MS Access, but I haven't chosen to
use it, because my users may want to filter rows while they are in the grid
and that requires gathering the full table and manipulating it, perhaps
again and again during the same session.
Even with the third party grid, I try to get my users to select data
filtered first (both re data and/or re columns to be returned), so that is
all they have in the grid at one time - they usually can do this because
they know what they're after. If they want the whole table - my largest is
about 25 cols wide, 1.5 million rows - they wait for the load - which isn't
terrible (approx 75 seconds to load).
I long ago spoke to MS about adding a connected element to ado .net, and
they said they would, but I don't believe it will happen.
HTH,
Bernie Yaeger
"Daniel Faensen" <faensen@.inf.fu-berlin.de> wrote in message
news:e9099bc4.0411050227.40853353@.posting.google.com...
>A typical szenario in a database-centered application is that a user
> (accidentally or on purpose) requests data that would result in a very
> large result set, may be millions of rows.
> I really like the way how MS Access handles that problem. The first
> records are displayed as soon as they are available. When the user
> scrolls down he or she experiences the display of the further records
> as if they were available locally. Jumping to record number 1,000,000
> also as little delay, the same applies for jumping to the end.
> Is it possible to achieve the same or a similar behavior in ADO.NET
> and Windows Forms? Can I still rely on data binding?
> My approach would be:
> - Using a background thread for the query to keep the application
> responsive and give the user an opportunity to abort the action.
> - Using a DataReader to fill a DataTable with the first n records.
> - Data bind the DataTable.
> - Handle scrolling down or jumping to a specified record by reading
> forward from the DataReader into the DataTable. That can't be done
> in the background thread, I suppose.
> The problem is that I need to keep open the connection all the time
> (or until the user scrolles to the end) which has a strong impact on
> scalability.
> I have a basic idea of how to handle paging in ADO.NET.
> How do I support sorting in the DataGrid?
> I suppose that is a frequent requirement. There should be some kind of
> a design pattern - or even a framework or third-party tool that
> supports such an implementation. Does anyone know more?
> Thank you in advance.
> Daniel Faensen

How to handle insert apostrophe from textbox to database?

Hi experts,

Databases reserve the ' sign to start and end strings. So if user enters a ' (e.g., 'McDonald's') in the company field, the database will give me an error and the insert into is failed. I have lots of textboxes in my asp.net application and how can I handle this exception?

First of all, I retrieved the textbox value from a stored session value:
Dim AppCompany As String = CStr(Session("Company"))

Then I have a SQL commandtext for my Insert Into statement:

Dim dbComm As New SqlClient.SqlCommand
dbComm.Connection = conHRISDb
dbComm.CommandText = "Insert Into Crew_Applicant_Table (First_Name, Last_Name,Main_Phone,App_Street,App_City,App_State,App_Zip,App_Company) Values ( '" & FName & "','" & LName & "','" & AppPhone & "','" & AppStreet & "','" & AppCity & "','" & AppState & "','" & AppZip & "','" & AppCompany & "')
dbComm.CommandType = CommandType.Text

Try usingParametrized queries
Yes, use parameterized queries. But to answer your questionregarding apostrophe, simply append another apostrophe next to yourstring. For example MacDonald's = MacDonald''s.
Insert into company(companyName) value 'MacDonald''s'
will work.
Good Luck

I am trying to insert as a parameterize way, but I got another problem:

On the first page, I stored a textbox value into session value : Session("FromDate1") = txtFromDate1.Text

Then on this page, I retrieve the stored value and take care the null value :

Dim AppFromDate1 As Date
If Session("FromDate1") Is DBNull.Value Then
AppFromDate1 = Nothing
Else
AppFromDate1 = CDate(Session("FromDate1"))
End If

The inserting data part:
dbDetail.Parameters.Add(New SqlParameter("@.AppFromDate1", SqlDbType.SmallDateTime))
sqldatenull = SqlDateTime.Null
If AppFromDate1 = "" Then
dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull
Else
dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)
End If

But it throws me an error when AppFromDate1 is null: Cast from string "" to type 'Date' is not valid.
(on Else
AppFromDate1 = CDate(Session("FromDate1"))
End If)

any suggestions?

How is AppFromDate1 declared in code?
It looks as though your Session variable isn't getting populated in thefirst form. For now I would just pass the FromDate value as a URLparameter and extract it via a Request.QueryString. If this worksyou will at least know that your SQL is valid.

I would try the following:
1) Load Session variables into String variables
dim fromDate1Str as String = Session("FromDate1")
2) Cast to Date only if String variable is not empty
if fromDate1Str <> String.Empty then
dim AppFromDate1 = CType(Session("FromDate1"),Date)
end if
Good Luck

I have took care of the seesion variable in the first form:

If txtFromDate1.Text.Trim().Length = 0Then

Session("FromDate1") = DBNull.Value

Else

Session("FromDate1") = txtFromDate1.Text

EndIf

In the 2nd form, I retrieve the value:

If Session("FromDate1")Is DBNull.ValueThen

AppFromDate1 =Nothing

Else

AppFromDate1 =CDate(Session("FromDate1"))

EndIf

HOWEVER, I found the null session date value becomes "#12:00:00AM#" even AppFromDate1=Nothing dosen't set the AppFromDate1 to null but to a string value.

Then I take care the parameter value by:

sqldatenull = SqlDateTime.Null

If AppFromDate1 =NothingThen

dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull

Else

dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)

EndIf

But dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull doesn't do the job either, it still try to insert "#12:00:00AM #"

Please someone help me!

If I try to load session variable into String variable: dim fromDate1Str as String = Session("FromDate1")
, I will get an error:

Cast from type 'DBNull' to type 'String' is not valid

you can also do an

If fromDate1Str.Trim.Length = 0 then

'pass the sqldatenull

else

'pass the actual value

end if

In the first form, I stored session variable to:
If txtFromDate1.Text.Trim().Length = 0 Then
Session("FromDate1") = DBNull.Value
Else
Session("FromDate1") = txtFromDate1.Text
End If

Then I retrieve the variable in the 2nd form:

Dim AppFromDate1 As Object
Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null
If AppFromDate1.Trim.Length = 0 Then
AppFromDate1 = sqldatenull
Else
AppFromDate1 = (Session("FromDate1"))
End If

I received the error:
Object variable or With block variable not set on

If AppFromDate1.Trim.Length=0

Any Suggestions?

>>Dim AppFromDate1 As Object
what kind of object is it ? a string, an int ?
you might want to checkthis article

Here is what I would do:
1) In form 1, initialize Session("FromDate1") to string.empty and setSession("FromDate1") to txtFromDate1.Text.ToString only iftxtFromDate1.text <> string.empty
2) In form 2 replace your parameterized Insert SQL statement witha parameterized Insert store procedure. This will providebenefits in the area of performance, security and flexibility as youwill see.
3) In your store procedure indicate which parameters are optional in TSQL by initializing them to null
4) In form 2 check whether Session("FromDate1") = string.empty. If it does, do not add the FromDate1 parameter to the parameterlist ofyour Command object. If it is not equal to string.empty then addthe FromDate1 parameter to the parameterlist.
I've done this before and it should work - provided you properlyidentified your optional paramaters in your TSQL store procedure. In a nutshell here's what should happen: If the FromDate1 parameter isnot added to the parameterlist, your store procedure will use thedefault value that you defined otherwise it should read the value fromyour Session variable and process normally.
Good Luck

How to Handle Error Message: "Login failed for user (null). Reason: Not associated with a

I'm running WinXP and using "Visual Studio 2005". To make a long story short, my "Default.aspx" page is using a GridView to display data which is being read from a Windows 2000 DB server. When I run the app under the IDE all works fine.

I copied the 1 file and put it into my folder as, "C:\Inetpub\wwwroot\default.aspx". When it loads I receive the error message, "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I'm frustrated with Studio Express 05 and will not bother with it again until Microsoft fixes this deployment issue. Can any one PLEASE help me???As you surely understand, this is a question of Access Premissions. When you write "Windows 2000 DB server", do you actually mean SQL 2000 Server? Assigning permissions correctly to the '<machinename>\ASPNET' account can be a bit difficult to grasp sometimes. It would help if you described your exact setup (what is installed on which machine). Example:
- Asp.Net 2.0 version 2.xxxxxxxxxx installed on machine YYYY running OS Windows XP Pro with 'IIS X.X'
- SQL Server version XXXX running on machine ZZZZ running OS QQQQ.

swep wrote:

I'm frustrated with Studio Express 05 and will not bother with it again until Microsoft fixes this deployment issue. Can any one PLEASE help me???

Your error SCREAMS "bad/improper connection string", so i wouldn't be so quick to blame a "deployment issue"
Application created on (my machine):
- Asp.Net 2.0 version 2.0.50215.44 running OS Windows XP Pro SP 2 with 'IIS 5.1'

Accessing database on:
- SQL Server 2000 Enterprise Edition running OS Windows Server 2000.

I'm really stuck...Any help you can offer would be greatly appreciated.
You must put in a valid connection string, because that is what the error is telling you
http://www.connectionstrings.com

I'm really lost right now and need some expect advice. You mentioned in the ASP.NET forum,

"You must put in a valid connection string, because that is what the error is telling you"

If my connection string is wrong then why can I connect to the db server within Visual Web 2005 just fine? My web.config file contains my connection as,

add name="WebConnectionString" connectionString="Data Source=NAME OF SQL SERVER;Initial Catalog=NAME OF TABLE;"

Could it be that I created this in .net framework 2.0 and it is querying a database in the .net framework 1.1?

hello.

maybe the problem is related with the fact that the asp.net user hasn't enough permissions to access the database (you might not have faced this problem because when you use the internal server it'll use the credentials of the active user to process the request - this does not happen when you use IIS: in this case, i't use a specific account for accessing the db).

How to handle concurrency problem!

HI ALL,

I have taken the employee table to dataset and updated the designation for the employee 111 and the same time another user deleted the 111 record from the database.while update my dataset what woudl happen whether my changes are stored or exception thrown.?

This is very basic question.Any one please let me know how can i check the database with values of the dataset or is there way to handle?

its wise to give me url of concepts to solve the concurrency problem.

.

Please visit following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcatchingconcurrencyerror.asp

http://msdn.microsoft.com/msdnmag/issues/03/04/DataConcurrency/

Regards

Kuldeep Deokule