Thursday, January 27, 2011

Inserting Record and Binding Data to Gridview Using Stored Procedures with Linq to Sql



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.

 you will have a option to choose data source type, for our project we have selected Microsoft SqlServer then click on continue.

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>
       &nbsp;</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.




 

2 comments: