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

 
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>GridView Example</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowPaging="True" PageSize="100" OnPageIndexChanging="GridView1_PageIndexChanging">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
&nbsp;</div>
</form>
</body>
</html>
 

C# ASP.NET 2.0 CODE BEHIND PAGE

 
using System; 
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
// DON'T FORGET TO ADD THIS NAMESPACE
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page 
{
// OF COURSE THIS SHOULD BE IN YOUR WEB.CONFIG FILE & ENCRYPTED
string conString = "Server=SWBC-DVLP2\\SWBCSQL;Database=Northwind;User ID=northwindtest;Password=NORTHWINDTEST;";
 
protected void Page_Load(object sender, EventArgs e)
{
Bind_GridView1();
}
 
public void Bind_GridView1()
{
SqlConnection sqlConn = new SqlConnection(conString);
SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate, ShippedDate FROM Orders", sqlConn);
DataSet ds = new DataSet();
sqlDA.Fill(ds, "Orders");
GridView1.DataSource = ds;
GridView1.DataBind();
}
 
public void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
Bind_GridView1();
}
 
}
 

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..."