Mad Asp.net ,C# ,Sql Coding : March 2017

Wednesday, 22 March 2017

Bind or Fill Dropdown list from Database in ASP.Net using C#

I.Introduction :

here i will explain how to fill or bind data in dropdownlist from the database .

II.Description:

Now create table

create table SecurityQuestion

(

SecurityQuestionId int primary key identity,

SecurityQuestion varchar(100)

)

Once the table design is complete insert the value and display the details

Once we finish creation of table in database now open your aspx page and write the code like as shown below

<!DOCTYPE html>

<title> how to bind data to dropdown from database </title>

</head>

<body>

<div>

<asp:Label ID="Label1" runat="server" Text="Select Secret Question :"></asp:Label>

<asp:DropDownList ID="drlquestion" runat="server"></asp:DropDownList><br />

</div>

</form>

</body>

</html>

After completion of aspx page add following namespaces in codebehind

web.config:

</connectionStrings>

C# code

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

After completion of adding namespaces you need to write the code like as shown below

string strCon = ConfigurationManager.ConnectionStrings["Data"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

Bind();

}

public void Bind()

{

using (SqlConnection con = new SqlConnection(strCon))

{

con.Open();

string sql = "select SecurityQuestion from PmsSecurityQuestion";

SqlCommand cmd = new SqlCommand(sql, con);

SqlDataReader dr = cmd.ExecuteReader();

drlquestion.Items.Insert(0, new ListItem("Select"));

int i = 1;

while (dr.Read())

{

drlquestion.Items.Insert(i, new ListItem(dr[0].ToString()));

i++;

}

Demo:

Hope you all understand the how to bind or fill data in dropdown list from database ..

Do have any query regarding my code you can comment below !!!

Monday, 13 March 2017

Asp.net GridView Crud Operations (Insert Select Edit Update Delete) using Single Stored Procedure Example

Asp.net GridView Crud Operations (Insert Select Edit Update Delete) using Single Stored Procedure.

I.Introduction :

Here I will explain how to implement gridview crud operations insert, select, edit, update and delete operations with single stored procedure in asp.net using C# with example.

II.Description:

Now create one new stored procedure "spCRUD" in your sql server database to perform insert, select, update and delete operations with single procedure for that follow below script.

create proc spCRUD
@Action varchar(50),
@Eid int =null,
@EName varchar(50)=null,
@Dept varchar(50)=null,
@Salary int=null
as
begin

if @Action='Select'
begin
select * from tblemployee;
end

if @Action='Insert'
begin
insert into tblemployee values(@Eid,@EName,@Dept,@Salary);
end

if @Action='Update'
begin
update tblemployee set Eid=@Eid, Ename=@EName,Dept=@Dept,Salary=@Salary
where Eid=@Eid;
end

if @Action='Delete'
begin
delete from tblemployee where Eid=@Eid;
end

end

II.In case if you have any doubts to create procedure check below article

CRUD Operations using Stored Procedure

Once we finish stored procedure creation in database now open your aspx page and write the code like as shown below

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>CRUD Operation in ASP.Net</title>
<style type="text/css">
.auto-style1 {
text-align: center;
}
</style>
</head>
<body style="text-align: center">
<form id="form1" runat="server">
<div style="text-align: center">
<table style="align-content: center; margin-left: 350px;">
<tr>
<td class="auto-style1">
<asp:Label ID="Label4" runat="server" Text="Enter EmployeeId:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtid" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label1" runat="server" Text="Enter Name:"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Label ID="Label2" runat="server" Text="Enter Department :"></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtdept" runat="server" Style="text-align: justify"></asp:TextBox>
</td>
</tr>

<tr>
<td class="auto-style1">
<asp:Label ID="Label3" runat="server" Text="Enter Salary "></asp:Label>
</td>
<td class="auto-style1">
<asp:TextBox ID="txtsalary" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" style="text-align: right">
<asp:Label ID="lblerror" runat="server" Visible="true" ForeColor="Green"></asp:Label>
</td>
</tr>
<tr>
<td class="auto-style1">
<asp:Button ID="btnSave" runat="server" Text="Submit" OnClick="btnSave_Click1" />
<asp:Button ID="btnUpdate" runat="server" Text="Update " OnClick="btnUpdate_Click" Visible="false" />
</td>
<td class="auto-style1">
<asp:Button ID="btnRest" runat="server" Text="Reset" OnClick="btnRest_Click" />
</td>
</tr>
</table>
</div>
<br />
<br />
<div style="text-align: center; margin-left: 100px;">

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Style="margin-left: 278px">
<Columns>
<asp:TemplateField HeaderText="Eid">
<ItemTemplate>
<asp:Label ID="lblid" runat="server" Text='<%#Eval("Eid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("EName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Department">
<ItemTemplate>
<asp:Label ID="lbldept" runat="server" Text='<%#Eval("Dept") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server" Text='<%#Eval("Salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>

<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />

</asp:GridView>

</div>
</form>
</body>
</html>

After completion of aspx page add following namespaces in codebehind

web.config:

<add name="CRUD" connectionString="Data Source=ZAFFER- BAIG\SQLEXPRESS;Initial Catalog=Employee;

Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

C# code

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

After completion of adding namespaces you need to write the code like as shown below

string Con = ConfigurationManager.ConnectionStrings["Info"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}

private void ClearControls()
{
txtid.Text = "";
txtname.Text = "";
txtdept.Text = "";
txtsalary.Text = "";
lblerror.Visible = false;
}

private void BindGrid()
{
using (SqlConnection con = new SqlConnection(Con))
{
SqlDataAdapter dr = new SqlDataAdapter("spCRUD", con);
dr.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
dr.SelectCommand.Parameters.AddWithValue("@Action", "Select");
DataTable dt = new DataTable();
dr.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

protected void btnSave_Click(object sender, EventArgs e)
{
if (txtid.Text == "" || txtname.Text == "" || txtdept.Text == "" || txtsalary.Text == "")
{
lblerror.Visible = true;
lblerror.Text = "Enter the required field ";
}
else
{
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Insert");
cmd.Parameters.AddWithValue("@Eid", txtid.Text);
cmd.Parameters.AddWithValue("@EName", txtname.Text);
cmd.Parameters.AddWithValue("@Dept", txtdept.Text);
cmd.Parameters.AddWithValue("@Salary", txtsalary.Text);

cmd.ExecuteNonQuery();
con.Close();
}

BindGrid();
ClearControls();
lblerror.Visible = true;
lblerror.Text = "Successfully inserted !!";

}
}

protected void btnEdit_Click(object sender, EventArgs e)
{
Button btn = sender as Button;
GridViewRow grd = btn.NamingContainer as GridViewRow;
txtid.Text = (grd.FindControl("lblid") as Label).Text;
txtname.Text = (grd.FindControl("lblname") as Label).Text;
txtdept.Text = (grd.FindControl("lbldept") as Label).Text;
txtsalary.Text = (grd.FindControl("lblsalary") as Label).Text;
btnUpdate.Visible = true;
btnRest.Visible = true;
btnSave.Visible = false;
txtid.Enabled = true;
}

protected void btnDelete_Click(object sender, EventArgs e)
{
Button btn = sender as Button;
GridViewRow grd = btn.NamingContainer as GridViewRow;

lblerror.Text = (grd.FindControl("lblid") as Label).Text;
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Delete");
cmd.Parameters.AddWithValue("@Eid", lblerror.Text);
cmd.ExecuteNonQuery();
BindGrid();
lblerror.Visible = true;
lblerror.Text = "deleted sucessfully ";

}

}

protected void btnUpdate_Click(object sender, EventArgs e)
{
if (txtname.Text == "" || txtdept.Text == "" || txtsalary.Text == "")
{
lblerror.Visible = true;
lblerror.Text = "Enter the required field ";
}
else
{
using (SqlConnection con = new SqlConnection(Con))
{
con.Open();
SqlCommand cmd = new SqlCommand("spCRUD", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Update");
cmd.Parameters.AddWithValue("@Eid", txtid.Text);
cmd.Parameters.AddWithValue("@EName", txtname.Text);
cmd.Parameters.AddWithValue("@Dept", txtdept.Text);
cmd.Parameters.AddWithValue("@Salary", txtsalary.Text);
cmd.ExecuteNonQuery();
con.Close();
}

BindGrid();
ClearControls();
lblerror.Visible = true;
lblerror.Text = " Updated Successfully inserted !!";

}
btnUpdate.Visible = false;
btnSave.Visible = true;

}

protected void btnRest_Click(object sender, EventArgs e)
{
ClearControls();
}

Demo:

Hope you all understand the Asp.net GridView CRUD Operation using Stored Procodure ..

Do have any query regarding my code you can comment below !!!

Friday, 10 March 2017

Left Outer Join , Right Outer Join and Full Outer Join

Outer Joins

Types of Outer Joins

Left Outer Joins
Right Outer Joins
Full Outer Joins

Left Outer Joins :

In case of Left Outer Joins data from left table is completely included into the result set , but only matched data from right side table is included into the result set. In place of right table data Null's are included where there is no match .

I.Create the two tables Persons and Profession

II.Display the tables with values

Resultant Set:

tblPersons tblProfession

Example of Left Outer Join Query :

Resultant Set:

Note: here by condition of left outer join , first table of Pid is compared with second table of P_id based on condition . Left Table data is completely included and right Table data will include on match condition ,remaining fields will be filled with Null's values.

Right Outer Join:

In case of Right Outer Join data from right table is completely included into the result set,but only matched data from left side table is include into the result set. In place of left table data Null's are included where there is no matched.

Example of Right Outer Join Query :

Resultant Set :

Note : Right Table data is completely included and Left Table data will include on match condition , remaining fields will be filled with Null's values.

Full Outer Join :

In case of Full Outer Join data from both the tables are included into the result set irrespective of condition Null's are included where ever there is no match.

Example of Full Outer Join Query :

Resultant Set :

Hope you all understand the Outer Join with simple example

Do have any query regarding my code you can comment below !!!

Thursday, 9 March 2017

Joins (Inner Joins)

JOINS

Joins:

Joins are used to retrieve data from two or more tables based on logical relationship between the tables

Types of Joins :

I. Inner Joins

II. Outer Joins

III. Self Joins

IV. Cross Joins

Inner Joins :

In case of inner joins matched data from both the tables are included into the result set .

Syntax : left table name alias name1 inner join | join right alias name2 on <Condition>

I. Here we have two tables in database

Resultant Set :

II.Query for Inner Joins Based on Conditions

Resultant Set :

Note :

Here by the condition of join , Sid colum of tblStudent will be compared with the second table column S_id . Sid will be compared with all the record of S_id ,

if condition true then it will take the Tname from tbltraining table

Hope you all understand the Joins and Inner Joins with simple Example

Do have any query regarding my code you can comment below !!!