C# ASP.NET Sql Command Select Statement Source Code Example

Posted by on Sunday, December 13, 2009 · Leave a Comment 

C# ASP.NET Sql Command Select Statement Source Code Example

Purpose: – Illustrates using Sql Command Select Statement in C-Sharp ASP.NET.

Prerequistes:

  1. Install Visual Studio 2022
  2. Watch this video to enable web forms in Visual Studio 2022 Enable Web Forms websites in Visual Studio 2022
  3. Install SQL Server Express
  4. Download Northwind Database
  5. Attach Northwind Database to Databases in Sql Express

Notes:

  • You can build your own library of syntax examples by using same web site over and over and just adding new web forms.

Instructions:

  1. Use Visual Studio 2022
  2. Create new web site;
    • Select Create New Project
    • Select ASP.NET Web Forms Site Template
    • name of project could be CSharp_ASPNET_Syntax
    • name of solution could be CSharp_ASPNET_Syntax.
  3. Add New folder named “Database_ADONET”
    • Right-click project name in solution explorer;
    • add new folder;
    • name of folder could be: Database_ADONET
  4. Add Web Form Named SqlCommandSelect to Database_ADONET folder
    • Right-click Database_ADONET folder;
    • add new item;
    • Select Web Form
    • Check place code behind in separate file
    • Web Form name could be SqlCommandSelect
  5. Click on copy code in code below to copy code into web form SqlCommandSelect.aspx
  6. Click on copy code in second set of code below to copy code into code-behind SqlCommandSelect.aspx.cs
  7. Right-click on SqlCommandSelect.aspx in solution explorer and select view in browser

Step 1: Click on Copy Code to Cut-n-paste code into SqlCommandSelect.aspx

< %@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlCommandSelect.aspx.cs" Inherits="Database_ADONET_SqlCommandSelect" % >
<title>Sql Command Select C-Sharp ASP.NET</title>
    <form id="Form1" runat="server">        
            Find: 
            <asp:textbox id="TextBox1" runat="server" text="*"></asp:textbox>
             <asp:button id="Button1" onclick="Button1_Click" runat="server" text="Go"></asp:button>        
            <asp:datagrid id="DataGrid1" runat="server" borderwidth="5px" bordercolor="Black" cellpadding="5">
                <headerstyle font-size="X-Small" font-names="Arial Narrow" font-bold="True" forecolor="White" backcolor="Black"></headerstyle>
                <alternatingitemstyle backcolor="Silver"></alternatingitemstyle>
                <itemstyle font-size="X-Small" font-names="Arial Narrow"></itemstyle>
            </asp:datagrid>        
    </form>

Step 2: Click on Copy Code to Cut-n-paste code into SqlCommandSelect.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
partial class Database_ADONET_SqlCommandSelect : System.Web.UI.Page
{
 
    System.Data.DataSet FindByTitle(string search)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Pubs_ConnectionString"].ConnectionString);
        string queryString = "";
 
        if (search == "*")
        {
            queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles]";
        }
        else
        {
            queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles] WHERE ([titles].[title] like @search + '%')";
        }
 
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(queryString, con);
        if (search != "*")
        {
            sqlCmd.Parameters.Add("@search", System.Data.SqlDbType.NVarChar).Value = search;
        }
 
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet);
 
        return dataSet;
    }
 
    public void Button1_Click(object sender, EventArgs e)
    {
        string sTitle = TextBox1.Text;
        DataGrid1.DataSource = FindByTitle(sTitle);
        DataGrid1.DataBind();
    }
 
}

Step 3: Click on Copy Code to Cut-n-paste code into web.config right after the appSettings section 

<connectionstrings>
<add name="Nortwind_ConnectionString" connectionstring="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI">
</add><add name="Pubs_ConnectionString" connectionstring="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI">
</add></connectionstrings>

Filed under Code Samples · Tagged with , , , , ,