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

Posted by asp.net videos on Sunday, December 13, 2009 · Leave a Comment

Premium .NET Training Video Tutorials

Other Premium Videos Tutorials

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

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

Prerequistes:

Install C# (Express or Standard Edition)
Install SQL Server Express
Download Northwind and Pubs Database
Attach Northwind Database to Databases in Sql Express
Attach pubs 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 add new web forms to it.

Instructions:

Use Visual Web Developer 2008
Create new web site;
- Click File/New Web Site
- Select ASP.NET Website Template
- Select C-Sharp for Language
- name of Web Site could be CSharp_ASPNET_Syntax.
Add New folder named "Database_ADONET"
- Right-click project name in solution explorer;
- add new folder;
- name of folder could be: Database_ADONET
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
Click on copy code in code below to copy code into web form SqlCommandSelect.aspx
Click on copy code in second set of code below to copy code into code-behind SqlCommandSelect.aspx.cs
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

 XML |   copy code | ?  
01 
02 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlCommandSelect.aspx.cs" Inherits="Database_ADONET_SqlCommandSelect" %>
03  
04 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
05  
06 <html>
07 <head>
08 <title>Sql Command Select C-Sharp ASP.NET</title>
09 </head>
10 <body>
11     <form id="Form1" runat="server">
12         <p>
13             Find: 
14             <asp:TextBox id="TextBox1" runat="server" Text="*"></asp:TextBox>
15              <asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Go"></asp:Button>
16         </p>
17         <p>
18             <asp:DataGrid id="DataGrid1" runat="server" BorderWidth="5px" BorderColor="Black" CellPadding="5">
19                 <HeaderStyle font-size="X-Small" font-names="Arial Narrow" font-bold="True" forecolor="White" backcolor="Black"></HeaderStyle>
20                 <AlternatingItemStyle backcolor="Silver"></AlternatingItemStyle>
21                 <ItemStyle font-size="X-Small" font-names="Arial Narrow"></ItemStyle>
22             </asp:DataGrid>
23         </p>
24     </form>
25 </body>
26 </html>
27  
28

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

  |   copy code | ?  
01 
02 using System;
03 using System.Data;
04 using System.Data.SqlClient;
05 using System.Configuration;
06  
07 partial class Database_ADONET_SqlCommandSelect : System.Web.UI.Page
08 {
09  
10     System.Data.DataSet FindByTitle(string search)
11     {
12         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Pubs_ConnectionString"].ConnectionString);
13         string queryString = "";
14  
15         if (search == "*")
16         {
17             queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles]";
18         }
19         else
20         {
21             queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles] WHERE ([titles].[title] like @search + '%')";
22         }
23  
24  
25  
26         System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(queryString, con);
27         if (search != "*")
28         {
29             sqlCmd.Parameters.Add("@search", System.Data.SqlDbType.NVarChar).Value = search;
30         }
31  
32  
33         System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
34         System.Data.DataSet dataSet = new System.Data.DataSet();
35         dataAdapter.Fill(dataSet);
36  
37         return dataSet;
38     }
39  
40     public void Button1_Click(object sender, EventArgs e)
41     {
42         string sTitle = TextBox1.Text;
43         DataGrid1.DataSource = FindByTitle(sTitle);
44         DataGrid1.DataBind();
45     }
46  
47 }
48  
49

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

 XML |   copy code | ?  
1 
2 <connectionStrings>
3     <add name="Nortwind_ConnectionString"
4       connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />
5     <add name="Pubs_ConnectionString"
6       connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" />
7   </connectionStrings>
8  
9

VB.NET ASP.NET Sql Command Select Statement Source Code Example VB.NET ASP.NET Sql Command Select Statement Source Code Example that...
VB.NET ASP.NET Sql Command Insert Statement Source Code Example VB.NET ASP.NET Sql Command Insert Statement Source Code Example...
C# ASP.NET Sql Command Delete Statement Source Code Example C# ASP.NET Sql Command Delete Statement Source Code Example...
C# ASP.NET Sql Command Insert Statement Source Code Example C# ASP.NET Sql Command Insert Statement Source Code Example...
C# ASP.NET Sql Command Update Statement Source Code Example C# ASP.NET Sql Command Update Statement Source Code Example...

Related posts brought to you by Yet Another Related Posts Plugin.

Filed under Code Samples · Tagged with asp.net, C#, example, Select Statement, Source code, Sql Command

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

WP-Hashcash by Elliott Back protects you from spam. Please enable javascript and reload this page to add your comment.

.NET	.NET Programming (C# or VB) (Web or Desktop)
Key Supporting Languages	HTML	CSS	JavaScript	SQL Server

AJAX	ASP	Blogging	ColdFusion	mySQL
Perl	PHP	Ruby	Web Design	Web Dev

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

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

Speak Your Mind

Sponsored Links

Categories

Archives

01
02	<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlCommandSelect.aspx.cs" Inherits="Database_ADONET_SqlCommandSelect" %>
03
04	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
05
06	<html>
07	<head>
08	<title>Sql Command Select C-Sharp ASP.NET</title>
09	</head>
10	<body>
11	<form id="Form1" runat="server">
12	<p>
13	Find:
14	<asp:TextBox id="TextBox1" runat="server" Text="*"></asp:TextBox>
15	<asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Go"></asp:Button>
16	</p>
17	<p>
18	<asp:DataGrid id="DataGrid1" runat="server" BorderWidth="5px" BorderColor="Black" CellPadding="5">
19	<HeaderStyle font-size="X-Small" font-names="Arial Narrow" font-bold="True" forecolor="White" backcolor="Black"></HeaderStyle>
20	<AlternatingItemStyle backcolor="Silver"></AlternatingItemStyle>
21	<ItemStyle font-size="X-Small" font-names="Arial Narrow"></ItemStyle>
22	</asp:DataGrid>
23	</p>
24	</form>
25	</body>
26	</html>
27
28

01
02	using System;
03	using System.Data;
04	using System.Data.SqlClient;
05	using System.Configuration;
06
07	partial class Database_ADONET_SqlCommandSelect : System.Web.UI.Page
08	{
09
10	System.Data.DataSet FindByTitle(string search)
11	{
12	SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Pubs_ConnectionString"].ConnectionString);
13	string queryString = "";
14
15	if (search == "*")
16	{
17	queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles]";
18	}
19	else
20	{
21	queryString = "SELECT [titles].[title], [titles].[price], [titles].[notes], [titles].[pubdate] FROM [titles] WHERE ([titles].[title] like @search + '%')";
22	}
23
24
25
26	System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(queryString, con);
27	if (search != "*")
28	{
29	sqlCmd.Parameters.Add("@search", System.Data.SqlDbType.NVarChar).Value = search;
30	}
31
32
33	System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
34	System.Data.DataSet dataSet = new System.Data.DataSet();
35	dataAdapter.Fill(dataSet);
36
37	return dataSet;
38	}
39
40	public void Button1_Click(object sender, EventArgs e)
41	{
42	string sTitle = TextBox1.Text;
43	DataGrid1.DataSource = FindByTitle(sTitle);
44	DataGrid1.DataBind();
45	}
46
47	}
48
49

1
2	<connectionStrings>
3	<add name="Nortwind_ConnectionString"
4	connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />
5	<add name="Pubs_ConnectionString"
6	connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" />
7	</connectionStrings>
8
9