+91-90427 10472
         
Dot net training in Chennai

Windows Application CRUD Operation with ADO.net

Document by Ganesan – Ganesanva@hotmail.com – +919600370429

Create TestDB Database in Server Explorer
Create table UserDetails in TestDB with the below code snippet,

CREATE TABLE [dbo].[UserDetails] (
[UserId] INT IDENTITY (1, 1) NOT NULL,
[UserName] VARCHAR (100) NULL,
[Password] VARCHAR (100) NULL,
[City] VARCHAR (100) NULL,
PRIMARY KEY CLUSTERED ([UserId] ASC)
);


Add the below Connection string in app.config,

<connectionStrings>
<add name=”TestConnection” connectionString=”Data Source=(LocalDb)\v11.0;Initial Catalog=TestDB;Integrated Security=True;Pooling=False” providerName=”System.Data.SqlClient” />
</connectionStrings>


Design Form1 as below,

Textbox Properties
UserName Name – txtUserName
Password Name – txtPassword
City Name – txtCity
Save -btnSave
Show Data Link Name -linkLabel1

Events
btnSave OnClick =btnSave_Click
Show Data Link onClick= linkLabel1_LinkClicked
Add below Namespace in Form1.cs.

Using System.Data.SqlClient;
Using System.Configuration;

Add Reference System.Configuration.DLL in the Project.
In Form1.cs (Replace) put the below Snippet,

public partial class Form1 : Form
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestConnection”].ConnectionString);
public Form1()
{
InitializeComponent();
}
private void btnSave_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“insert into UserDetails values(‘”+txtUserName.Text+”‘”+”,'”+txtPassword.Text +”‘,'”+txtCity.Text+”‘)”,con);
int results=cmd.ExecuteNonQuery();
con.Close();
if (results > 0)
{
MessageBox.Show(“Inserted Successfully”);
}
}
private void Clear()
{
txtUserName.Text = string.Empty;
txtPassword.Text = string.Empty;
txtCity.Text = string.Empty;
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
ShowData objShowData = new ShowData();
objShowData.Show();
this.Hide();
}
}

Add ShowData.cs form as below design,

Properties
DataGrid Name – dgvGridData
Update Data Name – lnkUpdate

Events
lnkUpdate Link onClick Event – lnkUpdate_LinkClicked
ShowData Form OnLoad Event – ShowData_Load
Add below Namespace in ShowData .cs.

Using System.Data.SqlClient;
Using System.Configuration;

In ShowData.cs (Replace) put the below Snippet,

public partial class ShowData : Form
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestConnection”].ConnectionString);
public ShowData()
{
InitializeComponent();
}
private void ShowData_Load(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand(“select * from UserDetails”, con);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ada.Fill(ds);
dgvGridData.DataSource = ds.Tables[0];
}
private void lnkUpdate_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
UpdateUserData objUpdateUserData = new UpdateUserData();
objUpdateUserData.Show();
this.Hide();
}
}

Add UpdateUserData.cs Form as below Design,

Properties
UserId textbox Name – txtUserId
UserName Textbox -txtUserName
Password Textbox – txtPassword
City Textbox – txtCity
Update button name -btnUpdate
Get Data button Name – btnGet
Clear button Name – btnClear
Show Data Link Name -linkLabel1

Event :
btnUpdate onClick =btnUpdate_Click
btnGet onClick= btnGet_Click
btnClear onClick =btnClear_Click
linkLabel1 onClick – linkLabel1_LinkClicked

Add below Namespace in UpdateUserData .cs.

Using System.Data.SqlClient;
Using System.Configuration;

Put (Replace) the below snippet in UpdateUserData.cs ,

public partial class UpdateUserData : Form
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestConnection”].ConnectionString);
public UpdateUserData()
{
InitializeComponent();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“Update UserDetails Set UserName='” + txtUserName.Text + “‘,Password='” + txtPassword.Text + “‘,City='” + txtCity.Text + “‘ where UserId=” + txtUserId.Text, con);
int results = cmd.ExecuteNonQuery();
con.Close();
if (results > 0)
{
MessageBox.Show(“Updated Successfully”);
}
Clear();
}
private void Clear()
{
txtUserId.Text = string.Empty;
txtUserName.Text = string.Empty;
txtPassword.Text = string.Empty;
txtCity.Text = string.Empty;
btnGet.Enabled = true;
}
private void btnGet_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“select * from UserDetails where UserId=”+txtUserId.Text, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
txtUserName.Text = dr[“UserName”].ToString();
txtPassword.Text = dr[“Password”].ToString();
txtCity.Text = dr[“City”].ToString();
}
con.Close();
btnGet.Enabled = false;
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
ShowData objShowData = new ShowData();
objShowData.Show();
this.Hide();
}
private void btnClear_Click(object sender, EventArgs e)
{
Clear();
}
}

The Output as below,



Table Data as below,

Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6khuXmrBnLklntXTA

Consume WCF application in MVC

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

Create a new MVC application.
Create Connection string in Web.config as below.

<connectionStrings>
<add name=”TestConnection” connectionString=”Data Source=(LocalDb)\v11.0;Initial Catalog=TestDB;Integrated Security=True;Pooling=False” providerName=”System.Data.SqlClient” />
</connectionStrings>

Create StudentDetails.cs Class file as below.

[DataContract]
public class StudentDetails
{
[DataMember]
public int Id { get; set; }
[DataMember]
public string StudentName { get; set; }
[DataMember]
public int? Age { get; set; }
}

Create DBContext.Cs file as below

public class UsersContext : DbContext
{
public UsersContext()
: base(“TestConnection”)
{
}
public DbSet<StudentDetails> studentDbset { get; set; }
}

Create ScafFolding and autogenerate Views and controllers.
Make sure the Code now works in DB first approach. After this we are going to change the
data source to consume From WCF.
Create Service Reference as below,


Click OK. Now the service reference will be added as below,

Replace the Controller methods for CRUD as below,

public ActionResult Index()
{
SampleMVC1.ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client();
List<SampleMVC1.ServiceReference1.StudentDetails> objStudentDetails= objService.GetStudentData();
return View(objStudentDetails);
}
[HttpPost]
public ActionResult Create(SampleMVC1.ServiceReference1.StudentDetails studentdetails)
{
if (ModelState.IsValid)
{
SampleMVC1.ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client();
string result= objService.CreateStudentData(studentdetails);
return RedirectToAction(“Index”);
}
return View(studentdetails);
}
//
// GET: /Student/Edit/5
public ActionResult Edit(int id = 0)
{
SampleMVC1.ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client();
SampleMVC1.ServiceReference1.StudentDetails studentdetails = objService.GetStudentDatabyID(id);
if (studentdetails == null)
{
return HttpNotFound();
}
return View(studentdetails);
}
//
// POST: /Student/Edit/5
[HttpPost]
public ActionResult Edit(SampleMVC1.ServiceReference1.StudentDetails studentdetails)
{
if (ModelState.IsValid)
{
SampleMVC1.ServiceReference1.Service1Client objService = new ServiceReference1.Service1Client();
string result = objService.UpdateStudentData(studentdetails);
return RedirectToAction(“Index”);
}
return View(studentdetails);
}

Replace the view First line I.E model reference to point to Service model

Similar for other pages too.


Now the pages will be like below



Click on the Link below to get the sample solution
https://1drv.ms/u/s!ArddhCoxftkQg6gdCJeDrlqBAJE0vg

Create wcf Application for CRUD operation

Document by Ganesan – Ganesanva@hotmail.com – + 919600370429

-Create StudentDetails Table in Local DB

Right Click on the DB and Click Properties.

Connection String can be seen in Property window.

– Create a new WCF Service Application.
Put the connection string above in Web.config as in Snippet.

<connectionStrings>
<add name=”TestConnection” connectionString=”Data Source=(LocalDb)\v11.0;Initial Catalog=TestDB;Integrated Security=True;Pooling=False” providerName=”System.Data.SqlClient” />
</connectionStrings>


Create StudentDetails.cs Class file as below.

[DataContract]
public class StudentDetails
{
[DataMember]
public int Id { get; set; }
[DataMember]
public string StudentName { get; set; }
[DataMember]
public int? Age { get; set; }
}

Create DBContext.Cs file as below

public class UsersContext : DbContext
{
public UsersContext()
: base(“TestConnection”)
{
}
public DbSet<StudentDetails> studentDbset { get; set; }
}

Open the default interface file IService1.cs

Insert the Snippet as below

[OperationContract]
List<StudentDetails> GetStudentData();
[OperationContract]
string CreateStudentData(StudentDetails objStudentDetails);
[OperationContract]
string UpdateStudentData(StudentDetails objStudentDetails);
[OperationContract]
StudentDetails GetStudentDatabyID(int id = 0);

Implement the below code in Service1.Svc.cs

Code Snippet

public List<StudentDetails> GetStudentData()
{
return db.studentDbset.ToList();
}
public string CreateStudentData(StudentDetails objStudentDetails)
{
db.studentDbset.Add(objStudentDetails);
db.SaveChanges();
return “Added”;
}
public string UpdateStudentData(StudentDetails objStudentDetails)
{
db.Entry(objStudentDetails).State = EntityState.Modified;
db.SaveChanges();
return “Updated”;
}
public StudentDetails GetStudentDatabyID(int id = 0)
{
StudentDetails studentdetails = db.studentDbset.Find(id);
return studentdetails;
}

Run the Service application as below

Click on the Link below to get the sample solution
https://1drv.ms/u/s!ArddhCoxftkQg6gdCJeDrlqBAJE0vg

Asp.net Validation Controls

Document by Ganesan  -ganesanva@hotmail.com – + 919600370429
List of Asp.net Validation available are as follows,

1.Required Field validator
2.Regular Expression validator
3.Range Validator
4.Custom Validator
5.Compare Validator

Create a new Asp.net Web application
File -> New Project
b2-1
Right click on the solution and Add New web form page AddStudentDetails.aspx as below,
b2-2
Replace the HTML Tag in  AddStudentDetails.aspx with below snippet,

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title></title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table border=”0″ cellspacing=”2″ cellpadding=”2″>
<tr>
<td>Student Name
</td>
<td>
<asp:TextBox ID=”txtStudentName” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RQStudentName” runat=”server” ControlToValidate=”txtStudentName” ErrorMessage=”Please enter Student Name” ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Date of Birth
</td>
<td>
<asp:TextBox ID=”txtDOB” runat=”server”></asp:TextBox>
<asp:RangeValidator ID=”Rvalid” runat=”server”
ControlToValidate=”txtDOB” ErrorMessage=”Date of Birth should not be greater than current date” Type=”Date” ForeColor=”Red”></asp:RangeValidator>
</td>
</tr>
<tr>
<td> Age
</td>
<td>
<asp:TextBox ID=”txtAge” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RqAge” runat=”server” ControlToValidate=”txtAge” ErrorMessage=”Please enter Student Age”  ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:RangeValidator ID=”RgAge” runat=”server”  ControlToValidate=”txtAge”  ErrorMessage=”Age should be greater than 18″ MinimumValue=”18″ ForeColor=”Red”
MaximumValue=”2000″ Type=”Integer”></asp:RangeValidator>
</td>
</tr>
<tr>
<td> City
</td>
<td>
<asp:DropDownList ID=”ddlCity” runat=”server”>
<asp:ListItem Text=”–Select–” Value=”–Select–“></asp:ListItem>
<asp:ListItem Text=”Chennai” Value=”Chennai”></asp:ListItem>
<asp:ListItem Text=”Madurai” Value=”Madurai”></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID=”RqCity” runat=”server” InitialValue=”–Select–” ControlToValidate=”ddlCity”
ErrorMessage=”Please enter Student City” ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Email Id
</td>
<td>
<asp:TextBox ID=”txtEmailId” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RqEmailId” runat=”server” ControlToValidate=”txtEmailId” ErrorMessage=”Please enter Email ID”  ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID=”REVEmailId” runat=”server” ErrorMessage=”please enter correct email id” ControlToValidate=”txtEmailId”
ValidationExpression=”\w+([-+.’]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*” ForeColor=”Red”></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td> User Name
</td>
<td>
<asp:TextBox ID=”txtUserName” runat=”server”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator1″ runat=”server” ControlToValidate=”txtUserName” ErrorMessage=”Please enter UserName”
ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:CustomValidator ID=”CVUserName” runat=”server” ControlToValidate=”txtUserName” OnServerValidate=”CVUserName_ServerValidate”
ErrorMessage=”UserName should not be greater than 15 digits” ForeColor=”Red”></asp:CustomValidator>
</td>
</tr>
<tr>
<td> Password
</td>
<td>
<asp:TextBox ID=”txtPassword” runat=”server” TextMode=”Password”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator2″ runat=”server” ControlToValidate=”txtPassword” ErrorMessage=”Please enter Password”  ForeColor=”Red”>
</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td> Retype Password
</td>
<td>
<asp:TextBox ID=”txtRetypePassword” runat=”server” TextMode=”Password”></asp:TextBox>
<asp:RequiredFieldValidator ID=”RequiredFieldValidator3″ runat=”server” ControlToValidate=”txtRetypePassword” ErrorMessage=”Please enter Retype Password”  ForeColor=”Red”>
</asp:RequiredFieldValidator>
<asp:CompareValidator ID=”cmpPasswordmatch” runat=”server” ControlToCompare=”txtPassword” ControlToValidate=”txtRetypePassword”
ErrorMessage=”Password and Retype Password should not match” ForeColor=”Red”>
</asp:CompareValidator>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID=”btnSave” runat=”server” Text=”Save” CausesValidation=”true” OnClick=”btnSave_Click” />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Replace the AddStudentDetails.cs class with the below snippet,

public partial class AddStudentDetails : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Rvalid.MinimumValue = DateTime.MinValue.ToString(“dd-MM-yyyy”);
Rvalid.MaximumValue = DateTime.Now.ToString(“dd-MM-yyyy”);
}
protected void CVUserName_ServerValidate(object source, ServerValidateEventArgs args)
{
args.IsValid = (args.Value.Length < 15);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
Response.Write(“Saved”);
}
}
}

The Output as below,
b2-3
b2-4
b2-5
Custom Validation on server event,
b2-6
Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6kmic1tyrHNsIbXqA

Asp.net CRUD operation with Ado.net

Document by Ganesan  – Ganesanva@hotmail.com – + 919600370429

– Create Database in SQL as “StudentDB”
– Create Table “StudentDetails” with the below snippet,

CREATE TABLE [dbo].[StudentDetails] (
[Id]          INT          IDENTITY (1, 1) NOT NULL,
[StudentName] VARCHAR (50) NULL,
[Age]         INT          NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

You can see the DB created in local SQL express as below,
1
– Create a new Asp.net Web forms Application as below
File –> New Project
2
Add the Connection string in Web.config as below snippet,

 <connectionStrings>
<add name=”DefaultConnection” providerName=”System.Data.SqlClient” connectionString=”Data Source=(localdb)\v11.0;Initial Catalog=StudentDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False” />
</connectionStrings>

3
Add new Page as AddStudentDetails.aspx as below,
4
Replace the Below HTML code in AddStudentDetails.aspx file as below,

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title></title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table border=”0″ cellspacing=”2″ cellpadding=”2″>
<tr>
<td>
<asp:Label ID=”lblStudentName” runat=”server” Text=”Student Name”></asp:Label>
</td>
<td>
<asp:TextBox ID=”txtStudentName” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID=”lblAge” runat=”server” Text=”Age”></asp:Label>
</td>
<td>
<asp:TextBox ID=”txtAge” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID=”btnSubmit” runat=”server” Text=”Submit” OnClick=”btnSubmit_Click” />
<asp:Button ID=”btnUpdate” runat=”server” Text=”Update” OnClick=”btnUpdate_Click” />
<asp:Button ID=”btnClear” runat=”server” Text=”Clear” OnClick=”btnClear_Click” />
<asp:HiddenField ID=”hfId” runat=”server”></asp:HiddenField>
</td>
</tr>
<tr>
<td colspan=”2″>
<asp:GridView ID=”grvStudentDetails” runat=”server” AutoGenerateColumns=”false”>
<Columns>
<asp:BoundField DataField=”Id” HeaderText=”Id” />
<asp:BoundField DataField=”StudentName” HeaderText=”StudentName” />
<asp:BoundField DataField=”Age” HeaderText=”Age” />
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID=”btnEdit” runat=”server” Text=”Edit” OnClick=”btnEdit_Click” />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Replace the AddStudentDetails.aspx.cs file as below,

  public partial class AddStudentDetails : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“DefaultConnection”].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
btnSubmit.Visible = true;
btnUpdate.Visible = false;
SqlCommand cmd = new SqlCommand(“Select * from StudentDetails”, con);
SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ada.Fill(ds);
grvStudentDetails.DataSource = ds.Tables[0];
grvStudentDetails.DataBind();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“insert into StudentDetails values(‘”+txtStudentName.Text+”‘,”+txtAge.Text+”)”, con);
int result=cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Inserted Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
//Get the row that contains this button
GridViewRow gvr = (GridViewRow)btn.NamingContainer;
hfId.Value = gvr.Cells[0].Text;
txtStudentName.Text = gvr.Cells[1].Text;
txtAge.Text = gvr.Cells[2].Text;
btnSubmit.Visible = false;
btnUpdate.Visible = true;
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand(“Update StudentDetails set StudentName='” + txtStudentName.Text + “‘,Age=” + txtAge.Text + ” where Id=”+hfId.Value, con);
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
Response.Write(“Updated Successfully”);
}
con.Close();
BindGrid();
btnClear_Click(null, null);
}
protected void btnClear_Click(object sender, EventArgs e)
{
txtStudentName.Text = string.Empty;
txtAge.Text = string.Empty;
hfId.Value = string.Empty;
}
}

Run the solution.
The Output will be below,
5
6
Click below to download the solution,
https://1drv.ms/u/s!ArddhCoxftkQg6h98mZAX_-Zv-6reQ