he‎ > ‎security‎ > ‎

sqlTestC#.NET


<%@ 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>&nbsp;
    <asp:TextBox ID="TextBoxMaxResults" runat="server" Text="64"></asp:TextBox>&nbsp;
    <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
    }
}
// Kind regards, Heinrich Elsigan
Comments