Thursday, 13 June 2013

Connectivity Linq with Sql Server 2008 without dbml context……………

Connectivity Linq with Sql Server  2008 without dbml context……………

Open the Sql Server and execute the whole query

// create  database forlinq……………….

create database  forlinq

// create table  Student in database forlinq……………….

use  forlinq
create table  Student(RollNo int identity(1,1) primary key,Name nvarchar(50),Gender nvarchar(50),Course nvarchar(50),MobileNo nvarchar(50),Fee int)

first we will take a website after that click right choose Add References then choose .Net and take Using System.Data.Linq; version 4.0.0.0


We have to go to Web Config of Website and create a Connection String

<configuration>
<connectionStrings>
<add name="withoutdbmlclass" connectionString="Data Source=KUSH-PC\KUSH;Initial Catalog=forlinq;Integrated Security=True"/>
</connectionStrings>

******************************************************************

Take a ASP.NET Page


<%@ 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>
</head>
<body>
<div>
    <form id="form1" runat="server">
  
    <table width="100%" border="5px">
    <tr><td colspan="2"> <center>Linq without using dbml class</center> </td></tr>
    <tr><td>RollNo</td><td>
        <asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>
        </td></tr>
    <tr><td class="style1">Name</td><td class="style1">
        <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
        </td></tr>
    <tr><td class="style1">Gender</td><td class="style1">
        <asp:RadioButtonList ID="RbGender" runat="server"
            RepeatDirection="Horizontal">
            <asp:ListItem>Male</asp:ListItem>
            <asp:ListItem>Female</asp:ListItem>
        </asp:RadioButtonList>
        </td></tr>
    <tr><td>Course</td><td>
        <asp:DropDownList ID="ddlCourse" runat="server">
            <asp:ListItem Selected="True">Select</asp:ListItem>
            <asp:ListItem>Mca</asp:ListItem>
            <asp:ListItem>Bca</asp:ListItem>
            <asp:ListItem>B. Tech</asp:ListItem>
            <asp:ListItem>Mba</asp:ListItem>
        </asp:DropDownList>
        </td></tr>
    <tr><td>MobileNo</td><td>
        <asp:TextBox ID="txtmobileno" runat="server"></asp:TextBox>
        </td></tr>
    <tr><td>Fee</td><td>
        <asp:TextBox ID="txtfee" runat="server"></asp:TextBox>
        </td></tr>
    <tr><td>
        <asp:Button ID="BtnSave" runat="server" Text="Save" onclick="BtnSave_Click" />
        <asp:Button ID="btnshow" runat="server" Text="Show by RollNo"
            onclick="btnshow_Click" />
        </td><td>
            <asp:Button ID="btnupdate" runat="server" Text="Update By RollNo"
                onclick="btnupdate_Click" />
            <asp:Button ID="btnDelete" runat="server" Text="Delete By RollNo" onclick="btnDelete_Click"
             />
            <asp:Label ID="ltotalfee" runat="server" ForeColor="Lime"></asp:Label>
        </td></tr>
    <tr><td colspan="2"><center>
        <asp:Label ID="LMessage" runat="server" Text="Label" Visible="False"></asp:Label>
        </center>
        </td></tr>
    <tr><td colspan="2">
      <center> <asp:GridView ID="GridView1" runat="server" CellPadding="4"
              ForeColor="#333333" GridLines="None">
          <AlternatingRowStyle BackColor="White" />
          <EditRowStyle BackColor="#2461BF" />
          <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
          <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
          <RowStyle BackColor="#EFF3FB" />
          <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
          <SortedAscendingCellStyle BackColor="#F5F7FB" />
          <SortedAscendingHeaderStyle BackColor="#6D95E1" />
          <SortedDescendingCellStyle BackColor="#E9EBEF" />
          <SortedDescendingHeaderStyle BackColor="#4870BE" />
        </asp:GridView>
        </center>
        </td></tr>
    </table>
   </form>
   </div>

</body>
</html>

Then after  we  take  class………………….which  name  student

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;


/// Summary description for Student

[Table(Name="Student")]

public class Student
{

//RollNo  of  Table is  primary  key  and  identity colum…………
[Column(Name = "RollNo", IsPrimaryKey = true,IsDbGenerated=true)]
    public int RollNo
    {
        get;
        set;
    }
    [Column]
    public string Name
    {
        get;
        set;
    }
    [Column]
    public string Gender
    {
        get;
        set;
    }
    [Column]
    public string Course
    {
        get;
        set;
    }
    [Column]
    public string MobileNo
    {
        get;
        set;
    }
    [Column]
    public int Fee
    {
        get;
        set;
    }
}


 **********************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.Linq;
using System.Data.Linq.Mapping;

public partial class _Default : System.Web.UI.Page
{
    DataContext dd;

    protected void Page_Load(object sender, EventArgs e)
    {
        dd = new DataContext(ConfigurationManager.ConnectionStrings["withoutdbmlclass"].ConnectionString);
        Table<Student> tb = dd.GetTable<Student>();
       ltotalfee.Text = (from m in tb select m.Fee).Sum().ToString();
       


        if (!IsPostBack)
        {
            GridViewBind();

        }

    }

    private void GridViewBind()
    {

        GridView1.DataSource = dd.GetTable<Student>();
        GridView1.DataBind();
    }

// code for  how  to  save data………………………………………..

    protected void BtnSave_Click(object sender, EventArgs e)
    {
     
        Table<Student> tb = dd.GetTable<Student>();
        Student td = new Student
        {
           // RollNo = Convert.ToInt32(txtrollno.Text),
            Name = txtname.Text,
            Gender = RbGender.SelectedValue,
            Course = ddlCourse.SelectedItem.Text,
            MobileNo = txtmobileno.Text,
            Fee=Convert.ToInt32(txtfee.Text)
        };

        dd.GetTable<Student>().InsertOnSubmit(td);
        dd.SubmitChanges();
        GridViewBind();
        LMessage.Visible = true;
        LMessage.Text = "Save Data Sucess fully";
        txtname.Text = "";
        txtmobileno.Text = String.Empty;
        ddlCourse.SelectedValue = "Select";
        txtfee.Text = "";
       
       

    }

// code for  show  data  by  RollNo………………………………………..

    protected void btnshow_Click(object sender, EventArgs e)
    {
   

        Table<Student> tb = dd.GetTable<Student>();
        var v = (from m in tb where m.RollNo == Convert.ToInt32(txtrollno.Text) select m).FirstOrDefault();
        txtname.Text = v.Name;
        txtfee.Text = v.Fee.ToString();
        txtmobileno.Text = v.MobileNo;
        RbGender.SelectedValue = v.Gender;
        ddlCourse.SelectedItem.Text = v.Course;
        GridViewBind();
    }

 
   
// code for  update  data  by  RollNo………………………………………..

    protected void btnupdate_Click(object sender, EventArgs e)
    {
        Table<Student> tb = dd.GetTable<Student>();
        var v = (from m in tb where m.RollNo == Convert.ToInt32(txtrollno.Text) select m).FirstOrDefault();
        v.Name = txtname.Text;
        v.Gender = RbGender.SelectedItem.Text;
        v.Course = ddlCourse.SelectedItem.Text;
        v.MobileNo = txtmobileno.Text;
        v.Fee = Convert.ToInt32(txtfee.Text);
        dd.SubmitChanges();
        GridViewBind();
        LMessage.Visible = true;
        LMessage.Text = "Udate  Data Sucessfully";
        GridViewBind();

    }

// code for  delete data  by  RollNo………………………………………..

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        var v = (from m in dd.GetTable<Student>() where m.RollNo == Convert.ToInt32(txtrollno.Text) select m).FirstOrDefault();
        dd.GetTable<Student>().DeleteOnSubmit(v);
        dd.SubmitChanges();
        GridViewBind();
        LMessage.Visible = true;
        LMessage.Text = "Delete  Data Sucess fully";
    }
}







0 comments:

Post a Comment