<%@ Control Language="C#" AutoEventWireup="true"
CodeBehind="QueryTest.ascx.cs" Inherits="Research.QueryTest" %>
<asp:Panel ID="PanelQueryTest" runat="server"
DefaultButton="ButtonSearch" Width="100%">
<asp:TextBox ID="TextBoxSearch" runat="server"></asp:TextBox>
<asp:TextBox ID="TextBoxMaxResults" runat="server"
Text="64"></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server"
Text="Search" OnClick="ButtonSearch_Click"/>
<br />
<asp:Literal ID="LiteralRows" runat="server"></asp:Literal>
<div id="DivGridViewLayer" runat="server" style="overflow:
auto; width: 100%; height: 100%">
<br />
<asp:GridView ID="GridViewQuery" runat="server"
AutoGenerateColumns="true" EnableViewState="True"
AllowSorting="true" AllowPaging="true"
OnSorting="GridViewQuery_Sorting" OnPageIndexChanging="GridViewQuery_PageIndexChanging">
<PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" />
</asp:GridView>
</div>
<hr />
</asp:Panel>
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI.WebControls;
namespace Research {
public partial class QueryTest : System.Web.UI.UserControl {
protected global::System.Web.UI.WebControls.Panel PanelQueryTest;
protected global::System.Web.UI.WebControls.TextBox TextBoxSearch;
protected global::System.Web.UI.WebControls.TextBox TextBoxMaxResults;
protected global::System.Web.UI.WebControls.Button ButtonSearch;
protected global::System.Web.UI.WebControls.Literal LiteralRows;
protected global::System.Web.UI.HtmlControls.HtmlGenericControl DivGridViewLayer;
protected global::System.Web.UI.WebControls.GridView GridViewQuery;
public DataTable DTable { get; set; }
#region basic event handling
override protected void OnInit(EventArgs e) {
base.OnInit(e);
this.DTable = new DataTable();
}
protected void ButtonSearch_Click(object sender, System.EventArgs e) {
this.GridViewQuery.PageIndex = 0;
this.Search();
}
protected void GridViewQuery_Sorting(object sender, GridViewSortEventArgs e) {
this.Search();
}
protected void GridViewQuery_PageIndexChanging(object sender, GridViewPageEventArgs e) {
this.GridViewQuery.PageIndex = e.NewPageIndex; // bind data source only
this.GridViewQuery.DataSource = (DataTable)this.Session["DTable" + this.ID.ToString()];
this.GridViewQuery.DataBind();
}
#endregion
#region dummy search methods
protected virtual void Search() {
string searchText = string.IsNullOrWhiteSpace(this.TextBoxSearch.Text) ?
string.Empty : this.TextBoxSearch.Text;
int maxRows = (Int32.TryParse(this.TextBoxMaxResults.Text, out maxRows)) ? maxRows : 64;
this.Search(searchText, maxRows);
}
protected virtual void Search(string searchText, int maxRows) {
this.DTable.Columns.Clear();
this.DTable.Rows.Clear();
SqlCommand dbCommand = new SqlCommand();
string selectQuery = @";With MySelect As (
Select ROW_NUMBER() Over (Order By newid()) As RandomRowNr, *
From master.dbo.spt_values
Where name Like @SearchParameter + '%'
)
Select * From MySelect Where RandomRowNr < @MaxRows"; SqlParameter sqlParameterSearch = new SqlParameter("@SearchParameter", SqlDbType.VarChar, 512);
sqlParameterSearch.Value = searchText;
SqlParameter sqlParameterMaxRows = new SqlParameter("@MaxRows", SqlDbType.Int);
sqlParameterMaxRows.Value = maxRows; dbCommand.CommandText = selectQuery;
dbCommand.Parameters.Add(sqlParameterSearch);
dbCommand.Parameters.Add(sqlParameterMaxRows);
using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["CStr"]))
{
dbCommand.Connection = connection;
connection.Open();
SqlDataReader reader = dbCommand.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++) {
this.GridViewQuery.DataKeyNames = new string[1] { reader.GetName(0) };
if (!this.DTable.Columns.Contains(reader.GetName(i))) {
this.DTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
}
while (reader.Read()) {
DataRow bindableRow = DTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++) {
string name = reader.GetName(i);
bindableRow[name] = reader[name];
}
this.DTable.Rows.Add(bindableRow);
}
reader.Close();
}
this.GridViewQuery.DataSource = this.DTable;
this.GridViewQuery.DataBind();
this.Session["DTable" + this.ID.ToString()] = this.DTable;
if (DTable.Rows.Count > 0) {
this.DivGridViewLayer.Visible = true;
this.LiteralRows.Text = String.Format("{0} rows affected", this.DTable.Rows.Count);
} else {
this.DivGridViewLayer.Visible = false;
this.LiteralRows.Text = "no rows affected";
}
}
#endregion
}
}
|