asp.net - Search sql server database server using Textbox and button in C#

Asp.net - Search sql server database server using Textbox and button in C#

To search a SQL Server database using a textbox and button in an ASP.NET application (assuming ASP.NET Web Forms or MVC), you'll need to handle the following steps:

Step-by-Step Guide

1. Create a Database Connection

Ensure you have a connection string configured in your web.config or appsettings.json file to connect to your SQL Server database.

Example web.config for SQL Server Connection

<configuration> <connectionStrings> <add name="DefaultConnection" connectionString="Server=YourServerName;Database=YourDatabaseName;User Id=YourUsername;Password=YourPassword;" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>

Replace YourServerName, YourDatabaseName, YourUsername, and YourPassword with your actual SQL Server details.

2. Create UI Components

Add a textbox and button to your ASP.NET web form or MVC view to accept the search input from the user.

Example ASP.NET Web Forms
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
Example ASP.NET MVC
@using (Html.BeginForm("Search", "Home", FormMethod.Post)) { @Html.TextBox("searchTerm") <input type="submit" value="Search" /> }

3. Handle Search Logic in Code-Behind or Controller

Example ASP.NET Web Forms Code-Behind (Page.aspx.cs)
protected void btnSearch_Click(object sender, EventArgs e) { string searchTerm = txtSearch.Text.Trim(); using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { string query = "SELECT * FROM YourTableName WHERE ColumnToSearch LIKE @SearchTerm"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%"); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); // Process search results, e.g., bind to a GridView GridView1.DataSource = rdr; GridView1.DataBind(); rdr.Close(); } }
Example ASP.NET MVC Controller (HomeController.cs)
public class HomeController : Controller { private readonly string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; public ActionResult Index() { return View(); } [HttpPost] public ActionResult Search(string searchTerm) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT * FROM YourTableName WHERE ColumnToSearch LIKE @SearchTerm"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%"); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); // Process search results, e.g., return as JSON or bind to a view var model = new List<YourModel>(); // Replace YourModel with your actual model class while (rdr.Read()) { var item = new YourModel { // Map SqlDataReader to YourModel properties }; model.Add(item); } return View(model); } } }

4. Display Search Results

  • In ASP.NET Web Forms, you might bind the results to a GridView or similar control.
  • In ASP.NET MVC, you would typically return a view with a model containing search results.

Notes

  • Security: Always use parameterized queries (SqlCommand.Parameters.AddWithValue) to prevent SQL injection attacks.
  • Error Handling: Implement error handling and dispose of resources (SqlConnection, SqlCommand, SqlDataReader) properly, possibly with try-catch-finally blocks or using statements.
  • Model Binding: In MVC, replace YourModel with your actual model class and bind the SqlDataReader results accordingly.

This approach allows users to search the SQL Server database using a textbox and button in an ASP.NET application effectively. Adjust the specifics based on your application's architecture and requirements.

Examples

  1. "ASP.NET TextBox and Button to search SQL Server database"

    • Description: This query demonstrates how to use a TextBox and Button in ASP.NET to search a SQL Server database.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:GridView ID="GridView1" runat="server"></asp:GridView> // Code-behind (C#) using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = "your_connection_string"; string query = "SELECT * FROM YourTable WHERE YourColumn LIKE '%' + @search + '%'"; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } }

  2. "ASP.NET search SQL Server database with parameterized query"

    • Description: This query shows how to use a parameterized query to search a SQL Server database with a TextBox and Button in ASP.NET.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:GridView ID="GridView1" runat="server"></asp:GridView> // Code-behind (C#) using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = "your_connection_string"; string query = "SELECT * FROM YourTable WHERE YourColumn = @search"; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } }

  3. "ASP.NET search SQL Server database with stored procedure"

    • Description: This query demonstrates how to search a SQL Server database using a stored procedure with a TextBox and Button in ASP.NET.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:GridView ID="GridView1" runat="server"></asp:GridView> // Code-behind (C#) using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = "your_connection_string"; string storedProcedure = "sp_SearchYourTable"; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(storedProcedure, con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } } // Stored Procedure /* CREATE PROCEDURE sp_SearchYourTable @search NVARCHAR(50) AS BEGIN SELECT * FROM YourTable WHERE YourColumn LIKE '%' + @search + '%' END */

  4. "ASP.NET search SQL Server database and display results in GridView"

    • Description: This query shows how to search a SQL Server database and display the results in a GridView using a TextBox and Button in ASP.NET.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:GridView ID="GridView1" runat="server"></asp:GridView> // Code-behind (C#) using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = "your_connection_string"; string query = "SELECT * FROM YourTable WHERE YourColumn LIKE '%' + @search + '%'"; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); GridView1.DataSource = dt; GridView1.DataBind(); } } } } }

  5. "ASP.NET search SQL Server database using LINQ to SQL"

    • Description: This query demonstrates how to use LINQ to SQL to search a SQL Server database with a TextBox and Button in ASP.NET.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:GridView ID="GridView1" runat="server"></asp:GridView> // Code-behind (C#) using System; using System.Linq; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string searchText = txtSearch.Text.Trim(); using (YourDataContext dc = new YourDataContext()) { var results = from t in dc.YourTable where t.YourColumn.Contains(searchText) select t; GridView1.DataSource = results.ToList(); GridView1.DataBind(); } } }

  6. "ASP.NET search SQL Server database and bind to DropDownList"

    • Description: This query shows how to search a SQL Server database and bind the results to a DropDownList using a TextBox and Button in ASP.NET.
    • Code:
      // ASPX page <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" /> <asp:DropDownList ID="ddlResults" runat="server"></asp:DropDownList> // Code-behind (C#) using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; public partial class SearchPage : Page { protected void btnSearch_Click(object sender, EventArgs e) { string connectionString = "your_connection_string"; string query = "SELECT * FROM YourTable WHERE YourColumn LIKE '%' + @search + '%'"; using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.Parameters.AddWithValue("@search", txtSearch.Text.Trim()); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); sda.Fill(dt); ddlResults.DataSource = dt; ddlResults.DataTextField = "YourColumn"; ddlResults.DataValueField = "YourColumn"; ddlResults.DataBind(); } } } } }

More Tags

ienumerable connection spring-rest enter ipython innodb browser internet-explorer-8 innerhtml auto-generate

More Programming Questions

More Tax and Salary Calculators

More Fitness Calculators

More Chemistry Calculators

More Auto Calculators