FIROZ DOT NET: Q. Listview Control in asp.net c# and Sql Server

Step 1. Create Table using sql server database?

CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 2. Now Insert some record?

INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders', N'Sales Representative', N'Obere Str. 57')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo', N'Owner', N'Avda. de la Constitución 2222')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno', N'Owner', N'Mataderos 2312')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'AROUT', N'Around the Horn', N'Thomas Hardy', N'Sales Representative', N'120 Hanover Sq.')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'BERGS', N'Berglunds snabbköp', N'Christina Berglund', N'Order Administrator', N'Berguvsvägen 8')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'BLAUS', N'Blauer See Delikatessen', N'Hanna Moos', N'Sales Representative', N'Forsterstr. 57')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'BLONP', N'Blondesddsl père et fils', N'Frédérique Citeaux', N'Marketing Manager', N'24')
INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address]) VALUES (N'BOLID', N'Bólido Comidas preparadas', N'Martín Sommer', N'Owner', N'C/ Araquil, 67')

Step 3.Now add aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListView.aspx.cs" Inherits="ListView" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css" />
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js" type="text/javascript"></script>
<link href="Scripts/StyleSheet.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div class="container py-5">
<h2 class="text-center text-uppercase">List View Control</h2>
<div class="card">
<asp:ListView ID="lvCustomers" runat="server" GroupPlaceholderID="groupPlaceHolder1"
ItemPlaceholderID="itemPlaceHolder1" OnPagePropertiesChanging="OnPagePropertiesChanging" HeaderStyle-CssClass="bg-primary text-white">
<LayoutTemplate>
<table class="TableCSS">
<tr>
<th>CustomerId</th>
<th>CompanyName</th>
<th>ContactName</th>
<th>ContactTitle</th>
<th>Address</th>
</tr>
<asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>
<tr>
<td colspan="3">
<asp:DataPager ID="DataPager1" runat="server" PagedControlID="lvCustomers" PageSize="10">
<Fields>
<asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="false" ShowPreviousPageButton="true" ShowNextPageButton="false" />
<asp:NumericPagerField ButtonType="Link" />
<asp:NextPreviousPagerField ButtonType="Link" ShowNextPageButton="true" ShowLastPageButton="false" ShowPreviousPageButton="false" />
</Fields>
</asp:DataPager>
</td>
</tr>
</table>
</LayoutTemplate>
<GroupTemplate>
<tr>
<asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>
</tr>
</GroupTemplate>

<ItemTemplate>

<tr class="TableData">

<td>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("CustomerId")%>'></asp:Label></td>
<td>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("CompanyName")%>'></asp:Label></td>
<td>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("ContactName")%>'></asp:Label></td>
<td>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("ContactTitle")%>'></asp:Label></td>
<td>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("Address")%>'></asp:Label></td>
</tr>

</ItemTemplate>
</asp:ListView>
</div>
</div>
</form>
</body>
</html>

Step 4.Now write the below c# code into code behind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

public partial class ListView : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if(!Page.IsPostBack)

{

this.BindListView();

}

private void BindListView()

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = "SELECT CustomerId, CompanyName,ContactName, ContactTitle,Address FROM Customers";

cmd.Connection = con;

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

{

DataTable dt = new DataTable();

sda.Fill(dt);

lvCustomers.DataSource = dt;

lvCustomers.DataBind();

}

protected void OnPagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)

{

(lvCustomers.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, false);

this.BindListView();

}

Note:- Write the following connection string into web.config

<add name="constr" connectionString="Data Source=HOME-PC;Persist Security Info=False;Integrated Security=SSPI; Initial Catalog=Northwind;User Id=sa;Password=dna;

Connect Timeout=30;"/>

</connectionStrings>

FIROZ DOT NET

ASP DOT NET

Thursday, August 9, 2018

Q. Listview Control in asp.net c# and Sql Server

No comments:

Post a Comment

How to to select duplicate rows from sql server?

Search This Blog