In this post I explained How to insert a record using linq to Sql and how to get the data and append to grid using StoredProcedures.
For this I have taken two tables named Department and Employee
Please Create tables and stored procedures as follows.
Department Table
CREATE TABLE [dbo].[Dept](
[DeptId] [int] IDENTITY(1,1) CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED ,
[DeptName] [nvarchar](20) NULL)
Insert into Dept Values('Software')
Insert into Dept Values('HR')
Employee Table
CREATE TABLE [dbo].[Employee](
[EmpId] [int] CONSTRAINT [PK_Employee] PRIMARY KEY IDENTITY(1,1) ,
[EmpName] [nvarchar](50) NULL,
[Designation] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[Deptid] int CONSTRAINT FK_Employee_Dept FOREIGN KEY REFERENCES dbo.[Dept](DeptId))
CREATE PROCEDURE GetDepartmentDetails
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select * from Dept
END
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT E.EmpName,E.Designation,E.Location,D.DeptName
from Employee E
Inner Join
Dept D
on D.DeptId=E.DeptId
END
CREATE PROCEDURE InsertEmployeeDetails
-- Add the parameters for the stored procedure here
@EmpName nvarchar(50),
@Designation nvarchar(50),
@Location nvarchar(50),
@DeptId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Insert into Employee values(@EmpName,@Designation,@Location,@DeptId)
Return @@RowCount
END
Take new project with name LinqtoSql
Click on ok button to create new project
Right click on Solution explorer click on add new item. Here you will find dbml classes add dbml class named as EmployeeDept
Now we need to connect sqlserver through Server Explorer, if you do not find server explorer on left side corner please go to view and select server explorer
right click on dataconnections in Server explorer click on add connection.
Here we need select Server name and Database Name once you have selected Server name and Database Name test connection weather succeed.
Once finished the connection with SqlServer you can get all the tables and Stored procedures.
Just drag and drop Tables and Stored Procedures on the dbml Class Surface.visual studio automatically creates properties for table and methods for stored procedures.
Inserting Record
copy and paste flowing html code in asp page.here i have taken three textboxes, one dropdownlist and one button.
<div>
<table>
<tr>
<td colspan="2">
<asp:Label ID="lblMessage" runat="server" Font-Bold="True" ForeColor="Blue"
></asp:Label>
</td>
</tr>
<tr>
<td>
EmpName
</td>
<td>
<asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Designation</td>
<td>
<asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Location</td>
<td>
<asp:TextBox ID="txtLocation" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Department</td>
<td>
<asp:DropDownList ID="ddlDepartment" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
Code Here
public partial class _Default : System.Web.UI.Page
{
EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDatatoDropDown();
}
}
void BindDatatoDropDown()
{
var v = objEmp.GetDepartmentDetails();
ddlDepartment.DataTextField = "DeptName";
ddlDepartment.DataValueField = "DeptId";
ddlDepartment.DataSource = v;
ddlDepartment.DataBind();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
int iCount=objEmp.InsertEmployeeDetails(txtEmpName.Text, txtDesignation.Text, txtLocation.Text, Convert.ToInt32(ddlDepartment.SelectedValue));
if (iCount > 0)
{
lblMessage.Text = "Record Inserted Successfully";
}
else
{
lblMessage.Text = "Failed";
}
}
}
after finishing writing the code click on F5 to run your Project
Binding Data to Grid
Please copy and paste the following code in another Asp page named as Grid
<div>
<asp:GridView ID="grdEmployees" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None">
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="EmpName" HeaderText="Emp Name" />
<asp:BoundField DataField="Designation" HeaderText="Designation" />
<asp:BoundField DataField="Location" HeaderText="Location" />
<asp:BoundField DataField="DeptName" HeaderText="Dept Name" />
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</div>
Code Here
public partial class Grid : System.Web.UI.Page
{
EmployeeDeptDataContext objEmp = new EmployeeDeptDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
void BindGrid()
{
DataTable dt = new DataTable();
var v = objEmp.GetEmployeeDetails();
dt = ObtainDataTableFromIEnumerable(v);
grdEmployees.DataSource = dt;
grdEmployees.DataBind();
}
private DataTable ObtainDataTableFromIEnumerable(System.Data.Linq.ISingleResult<GetEmployeeDetailsResult> result)
{
DataTable dt = new DataTable();
dt.Columns.Add("EmpName",typeof(string));
dt.Columns.Add("Designation", typeof(string));
dt.Columns.Add("Location", typeof(string));
dt.Columns.Add("DeptName", typeof(string));
foreach (var v in result)
{
DataRow dr = dt.NewRow();
dr["EmpName"] = v.EmpName;
dr["Designation"] = v.Designation;
dr["Location"] = v.Location;
dr["DeptName"] = v.DeptName;
dt.Rows.Add(dr);
}
return dt;
}
}
Once you added above code click on F5.
Nice Article.... Thanks
ReplyDelete