Sunday 9 June 2013

In Linq How To Use Sql Query:

 

 

1-Add a new item in your site. Add a LINQ to SQL Classes and name it

DataClasses.dbml.Place this file in the App_Code special folder.

2-From the Server Explorer / Data Explorer window drag the CustomerInfo table and drop it on the DataClasses.dbml.

3-Drag a Gridview web server control on the Default.aspx page. 

4-Write Code Behind button Button6_Click( ).

 

protected void Button6_Click(object sender, EventArgs e)
        {
            try
            {
                DataClassesDataContext dcc = new DataClassesDataContext();
                IEnumerable<CustomerInfo> results1 = dcc.ExecuteQuery<CustomerInfo>  (@"select CustomerId,CustomerName from CustomerInfo");
                GridView2.DataSource = results1;
                GridView2.DataBind();

            }
            catch (Exception ex)
            {
                Response.Redirect(ex.Message);
            }
        }


Tuesday 7 May 2013

Select,Insert,Update and Delete data with LINQ to SQL in an ASP.Net application

For Select-

1-Start Or Open Visual Studio 2010..

2-Add a new item in your site. Add a LINQ to SQL Classes and name it

DataClasses.dbml.Place this file in the App_Code special folder

3- From the Server Explorer / Data Explorer window drag the CustomerInfo table and drop it on the DataClasses.dbml.

4-Have a look at the generated code from the Linq engine in DataClasses.designer.cs

5-Drag a Gridview web server control on the Default.aspx page.

6- In the Page_Load event handling routine of the Default.aspx page type

   

   DataClassesDataContext dcc = new DataClassesDataContext();

      var emps = from myemp in dcc.CustomerInfos
      select myemp;
                       

        GridView1.DataSource = emps;
        GridView1.DataBind();

7- Run your application and see the results printed in the screen.

 

For Insert

1-Now let's try and insert a new record in our CustomerInfo table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

2-Write Code Behind button Insert_Click( )-

 

  DataClassesDataContext dcc = new DataClassesDataContext();
  CustomerInfo myemp = new CustomerInfo();
   myemp.CustomerName = txtCustomerName.Text;
   myemp.CustomerAddress = txtCustomerAddress.Text;

          
    dcc.CustomerInfos.InsertOnSubmit(myemp);
     try
            {
                dcc.SubmitChanges();
                Response.Write("Inserted");
            }
     catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

3-Run your application and see the results printed in the screen.

 

For Delete-

1-Now let's try to delete the newly added record from our CustomerInfo table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Delete".

2-write Code Behind Button Delete_Click( )-

3-TextBox3 is a field in which whatever CustomerId Or CustomerName Or, CustomerAddress you want to delete,you enter that value and press delete button. 

DataClassesDataContext dcc = new DataClassesDataContext();                                                      var myemp= dcc.CustomerInfos.Single(emp=> (emp.CustomerId.ToString() == TextBox3.Text)|| (emp.CustomerName == TextBox3.Text) || (emp.CustomerAddress == TextBox3.Text));
                dcc.CustomerInfos.DeleteOnSubmit(myemp);
                try
                {
                    dcc.SubmitChanges();
                    Response.Write("Deleted");
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }

4-Run your application and see the results printed in the screen.