.jpg)
Connectivity Asp.Net with Sql Server using DataSet Control (Using
CheckBoxList and Split Function)
First
we Create Database CollegeDetails and table StudentInfo with autoincreatement and primary Key
create database CollegeDetails
use CollegeDetails
create table StudentInfo (SrNo int identity(1,1) primary key ,Name nvarchar(50) unique,Gender nvarchar(50),Qulification nvarchar(100), Address nvarchar(100),City nvarchar(50))
then after create
form
<%@ Page Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width:
100%;
}
.style2
{
height:
25px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="style1" border="5px" >
<tr>
<td colspan="3" align="center">
<asp:Label ID="Label8" runat="server"
Text="Connectivity with Sql Server
using DataSet Control"></asp:Label>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="Label7" runat="server" Text="Roll No"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td><center>
<asp:Label ID="Label2" runat="server" Text="Name"></asp:Label></center>
</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="Label3" runat="server" Text="Gender"></asp:Label></td>
<td>
<asp:RadioButtonList ID="RbGender" runat="server"
RepeatDirection="Horizontal">
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:RadioButtonList>
</td>
<td>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="Label6" runat="server" Text="Qulification"></asp:Label>
</td>
<td>
<asp:CheckBoxList ID="CbQulification" runat="server"
RepeatDirection="Horizontal">
<asp:ListItem>High
School</asp:ListItem>
<asp:ListItem>Intermidiate</asp:ListItem>
<asp:ListItem>Graduation</asp:ListItem>
<asp:ListItem>Post
Graduation</asp:ListItem>
</asp:CheckBoxList>
</td>
<td>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="Label4" runat="server" Text="Address"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtAddress" runat="server" Height="53px" TextMode="MultiLine"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td align="center" class="style2">
<asp:Label ID="Label5" runat="server" Text="Current City"></asp:Label>
</td>
<td class="style2">
<asp:DropDownList ID="DdlCity" runat="server">
<asp:ListItem>Select</asp:ListItem>
<asp:ListItem>Noida</asp:ListItem>
<asp:ListItem>Gr.
Noida</asp:ListItem>
<asp:ListItem>Delhi</asp:ListItem>
<asp:ListItem>Faridabad</asp:ListItem>
<asp:ListItem>Other</asp:ListItem>
</asp:DropDownList>
</td>
<td class="style2">
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="BInsert" runat="server" onclick="BInsert_Click" Text="Save" />
<asp:Button ID="BtnRitrive" runat="server" onclick="BtnRitrive_Click"
Text="Ritrive" />
<asp:Button ID="BtnDelete" runat="server"
Text="Delete"
onclick="BtnDelete_Click"
/>
<asp:Button ID="BtnUpdate" runat="server" onclick="BtnUpdate_Click"
Text="Update" />
<asp:Button ID="BtnClear" runat="server" onclick="BtnClear_Click"
Text="Clear" />
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Label ID="Message" runat="server" Text="Message"></asp:Label>
</td>
<td>
</td>
</tr>
<tr>
<td align="center" colspan="3">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataKeyNames="RollNo"
DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White"
/>
<Columns>
<asp:BoundField DataField="RollNo"
HeaderText="RollNo"
InsertVisible="False"
ReadOnly="True"
SortExpression="RollNo"
/>
<asp:BoundField DataField="Name"
HeaderText="Name"
SortExpression="Name"
/>
<asp:BoundField DataField="Gender"
HeaderText="Gender"
SortExpression="Gender"
/>
<asp:BoundField DataField="City"
HeaderText="City"
SortExpression="City"
/>
<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address"
/>
<asp:BoundField DataField="Qulification"
HeaderText="Qulification"
SortExpression="Qulification" />
</Columns>
<FooterStyle BackColor="#990000"
Font-Bold="True"
ForeColor="White"
/>
<HeaderStyle BackColor="#990000"
Font-Bold="True"
ForeColor="White"
/>
<PagerStyle BackColor="#FFCC66"
ForeColor="#333333"
HorizontalAlign="Center"
/>
<RowStyle BackColor="#FFFBD6"
ForeColor="#333333"
/>
<SelectedRowStyle BackColor="#FFCC66"
Font-Bold="True"
ForeColor="Navy"
/>
<SortedAscendingCellStyle BackColor="#FDF5AC"
/>
<SortedAscendingHeaderStyle BackColor="#4D0000"
/>
<SortedDescendingCellStyle BackColor="#FCF6C0"
/>
<SortedDescendingHeaderStyle BackColor="#820000"
/>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CollegeDetailsConnectionString %>"
SelectCommand="SELECT [RollNo],
[Name], [Gender], [City], [Address], [Qulification] FROM [StudentInfo] ORDER BY
[RollNo]">
</asp:SqlDataSource>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Add DataSet Control in Website......................................
Add Connection Server Explore with Sql Server......................................
When drag table in Dataset Contol ,It create Connection String in web.Config by Default..................................................................
<configuration>
<connectionStrings>
<add name="CollegeDetailsConnectionString" connectionString="Data
Source=KUSHTIWARI-PC\KUSH;Initial Catalog=CollegeDetails;Integrated
Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Dataset Control
provide all commad by default Like inset,delete,update,select if user want to add
it own query then
use...........................
After
After give name this function
//Code for
Default.aspx.cs ............................................
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default :
System.Web.UI.Page
{
string qul;
protected void Page_Load(object
sender, EventArgs e)
{
}
//Code for inserting data in database....................................................
protected void BInsert_Click(object
sender, EventArgs e)
{
// How to use CheckBoxList ...........................
foreach
(ListItem li in
CbQulification.Items)
{
if
(li.Selected)
{
qul += li.Value.ToString() + ",";
}
}
qul = qul.Remove(qul.Length - 1);
DataSet1TableAdapters.StudentInfoTableAdapter da = new
DataSet1TableAdapters.StudentInfoTableAdapter();
da.Insert(txtname.Text,
RbGender.SelectedValue, qul, txtAddress.Text, DdlCity.SelectedValue);
GridView1.DataBind();
Message.Text = "Data
Inserted";
}
//Code for Retrieving data from database....................................................
protected void BtnRitrive_Click(object
sender, EventArgs e)
{
DataSet1TableAdapters.StudentInfoTableAdapter da = new DataSet1TableAdapters.StudentInfoTableAdapter();
DataSet1.StudentInfoDataTable dt=da.GetDataByRollNo(Convert.ToInt32(txtrollno.Text));
if
(dt.Rows.Count <= 0)
return;
DataSet1.StudentInfoRow dr = (DataSet1.StudentInfoRow)dt.Rows[0];
txtname.Text = ""
+ dr.Name;
RbGender.SelectedValue = "" + dr.Gender;
txtAddress.Text = "" + dr.Address;
DdlCity.SelectedValue = "" + dr.City;
// How to retrive data from data base in CheckBox
List with using split function
string[]
qul= dr["Qulification"].ToString().Split(',');
// string cb =
"" + qul[0];
int i =
0;
foreach
(ListItem li in
CbQulification.Items)
{
if
(qul[i] == li.Text)
{
li.Selected = true;
i++;
}
}
GridView1.DataBind();
}
//Code for Clear data from Asp.Net Control...................................................
protected void BtnClear_Click(object
sender, EventArgs e)
{
txtrollno.Text = "";
txtname.Text = String.Empty;
//txtAddress.Clear();
txtAddress.Text = "";
DdlCity.SelectedValue = "Select";
foreach
(ListItem li in
CbQulification.Items)
{
li.Selected = false;
}
}
//Code for Update data in Sql Server...................................................
protected void BtnUpdate_Click(object
sender, EventArgs e)
{
foreach
(ListItem li in
CbQulification.Items)
{
if
(li.Selected)
{
qul += li.Value.ToString() + ",";
}
}
DataSet1TableAdapters.StudentInfoTableAdapter da = new DataSet1TableAdapters.StudentInfoTableAdapter();
da.UpdateQuery(txtname.Text,
RbGender.SelectedValue, qul, txtAddress.Text,DdlCity.SelectedValue, Convert.ToInt32(txtrollno.Text));
GridView1.DataBind();
Message.Text = "Data
Updated";
}
//Code for Delete data in Sql Server...................................................
//Code for Delete data in Sql Server...................................................
protected void BtnDelete_Click(object
sender, EventArgs e)
{
DataSet1TableAdapters.StudentInfoTableAdapter da = new DataSet1TableAdapters.StudentInfoTableAdapter();
da.Delete(Convert.ToInt32(txtrollno.Text));
GridView1.DataBind();
Message.Text = "Data
Deleted";
}
}
