C# Web-App: Sql query way tiring for sync()

[zonar00]

I wanna help on the following aspect of SQL data retrieval which requires one to retrieve data from remote SQL server site asynchronously.

Im building a c# application. I want to retrieve record (network packets info) from the remote computer that is located on the same subnet as the client (from which it receives the request). Since due to the large volume of data which may include more than 20,000 rows in each table .....

void results()
        {
         
            dataGridView1.DataSource = null;

           
          
            DataSet ds = new DataSet();
            
           
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + state, rc);

	     // where rc is the SQL connection point that exists b/w my sql client and SQL server (remote machine) meaning the connection is already established when i execute the command
            
          
            adapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        
        }

Is simply too slow for comfort and more importantly this function halts the execution of more user controlled events like clicking other buttons and moving cursor. The whole control on the panel is lost during the sql command execution and its bloody annoying .

So does anyone know how could i make fun() behave asynchronously.

thxs.....

[aaronsandoval]

Here are a couple of things to consider first before we continue figuring out the best way to resolve your issue...

1.) Are you trying to fill a gridview control with 20,000 items on the same page?

2.) Have you considered using grid view paging with maybe 100 items per page?

I ask you this because if you are trying to bind 20,000 rows of data from a SQL database into an ASP.NET Server Control it's going to take a while and may lock up your app (which seems to be happening).

Also, I recommend using a stored procedure rather than in-line SQL statements because it will enhance the speed of your query dramatically.

If you would like sample code on gridview paging, SQL stored procedure example or more information about any of this please let me know and I will be more than willing to write a sample.

[zonar00]

Here are a couple of things to consider first before we continue figuring out the best way to resolve your issue...

1.) Are you trying to fill a gridview control with 20,000 items on the same page?

2.) Have you considered using grid view paging with maybe 100 items per page?

I ask you this because if you are trying to bind 20,000 rows of data from a SQL database into an ASP.NET Server Control it's going to take a while and may lock up your app (which seems to be happening).

Also, I recommend using a stored procedure rather than in-line SQL statements because it will enhance the speed of your query dramatically.

If you would like sample code on gridview paging, SQL stored procedure example or more information about any of this please let me know and I will be more than willing to write a sample.

the second option(2) seems more probable but firstly PL\SQL kinda thing is not retired in my case as im not supposed to do any datamining on the record. I just have to retrieve and display them in datagrid thats it so i think simple SQL query should do the trick.

im interested in knowing that like in case of restricting the record for 100 count can be done with the help of sql query using the count keyword but dnt u think going in viewing total records in breaks of 100 would take too long again......

[aaronsandoval]

Okay so paging is the way you would like to have the users view the records on the page as well as use in-line sql.

Here is a small example of how you can accomplish this task using C# ASP.NET 2.0 Visual Studio 2005 (or Visual Web Developer - free download from http://msdn2.microsoft.com/en-us/express/aa700797.aspx) and the SQL Northwind Database. This example also uses post backs on the page, so if you need it to be asyncronous we can add this to the example.

Quick Note: my team and I would have used a different approach, we would have used SQL Stored Procedure or Strongly Typed DataSets.

ASPX Page
[code=html]
<%@ Page Language="C#" AutoEventWireup ="true" CodeFile="Defau lt.aspx.cs" Inherits="_Defa ult" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>GridVi ew Example</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptMana ger ID="ScriptManag er1" runat="server" />
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333 333" GridLines="None " AllowPaging="Tr ue" PageSize="100" OnPageIndexChan ging="GridView1 _PageIndexChang ing">
<FooterStyle BackColor="#5D7 B9D" Font-Bold="True" ForeColor="Whit e" />
<RowStyle BackColor="#F7F 6F3" ForeColor="#333 333" />
<EditRowStyle BackColor="#999 999" />
<SelectedRowSty le BackColor="#E2D ED6" Font-Bold="True" ForeColor="#333 333" />
<PagerStyle BackColor="#284 775" ForeColor="Whit e" HorizontalAlign ="Center" />
<HeaderStyle BackColor="#5D7 B9D" Font-Bold="True" ForeColor="Whit e" />
<AlternatingRow Style BackColor="Whit e" ForeColor="#284 775" />
</asp:GridView>
&nbsp;</div>
</form>
</body>
</html>
[/code]

C# ASP.NET 2.0 CODE BEHIND PAGE
[CODE=cpp]
using System;
using System.Data;
using System.Configur ation;
using System.Web;
using System.Web.Secu rity;
using System.Web.UI;
using System.Web.UI.W ebControls;
using System.Web.UI.W ebControls.WebP arts;
using System.Web.UI.H tmlControls;
// DON'T FORGET TO ADD THIS NAMESPACE
using System.Data.Sql Client;

public partial class _Default : System.Web.UI.P age
{
// OF COURSE THIS SHOULD BE IN YOUR WEB.CONFIG FILE & ENCRYPTED
string conString = "Server=SWB C-DVLP2\\SWBCSQL; Database=Northw ind;User ID=northwindtes t;Password=NORT HWINDTEST;";

protected void Page_Load(objec t sender, EventArgs e)
{
Bind_GridView1( );
}

public void Bind_GridView1( )
{
SqlConnection sqlConn = new SqlConnection(c onString);
SqlDataAdapter sqlDA = new SqlDataAdapter( "SELECT OrderID, CustomerID, OrderDate, ShippedDate FROM Orders", sqlConn);
DataSet ds = new DataSet();
sqlDA.Fill(ds, "Orders");
GridView1.DataS ource = ds;
GridView1.DataB ind();
}

public void GridView1_PageI ndexChanging(ob ject sender, GridViewPageEve ntArgs e)
{
GridView1.PageI ndex = e.NewPageIndex;
Bind_GridView1( );
}

}
[/CODE]


Please let me know if this example is enough, I can provide more details for you if need be. There is a whole lot of functionality when it comes to gridviews and how you can use them (Hierarchical GridViews, Paging, Sorting, Tabbed GridViews using AJAX ToolKit Tab Control, Asyncronous PostBacks & MORE) so let us know exactly what your looking for. As for the example, hope it helps!

- Aaron Sandoval
"remember: There's a thin line between good | great..."