Using Transaction with Linq

Hi

try the following example to use Transaction with Linq :

– First Example :-

      DataClasses1DataContext db = new DataClasses1DataContext();
       // Get all customers list
      IQueryable<Customer> list = from x in db.Customers
                                  where x.Code > 0
                                  select x;
      Customer cust1 = list.Single<Customer>(x => x.Code == 1);
      Customer cust2 = list.Single<Customer>(x => x.Code == 2);
      // Update the first customer object
      cust1.FirstName = “yasser”;
      cust1.LastName = “zaid”;
      // Delete the second customer object
      db.Customers.Remove(cust2);
      // The update & delete operation will execte
      // in the same transaction       
      db.Connection.Open();
      db.Transaction = db.Connection.BeginTransaction();
      try
      {
          db.SubmitChanges();
          db.Transaction.Commit();
      }
      catch
      {
          db.Transaction.Rollback();
          // And do some error handling…
      }
      finally
      {
          db.Connection.Close();
          db.Transaction = null;
      }

– Second Example :-

      DataClasses1DataContext db = new DataClasses1DataContext();
      // Get all customers list
      IQueryable<Customer> list = from x in db.Customers
                                  select x;
      Customer cust1 = list.Single<Customer>(x => x.Code == 1);
      Customer cust2 = list.Single<Customer>(x => x.Code == 2);
      // Update the first customer object
      cust1.FirstName = “yasser”;
      cust1.LastName = “zaid”;
      // Delete the second customer object
      db.Customers.Remove(cust2);
      using (TransactionScope ts = new TransactionScope())
      {
          // The update & delete operation will execte in
          //the same transaction       
          db.SubmitChanges();
          ts.Complete();
      }

Hope this helps

Good Luck

Multiple Delete with Linq

Hi

try this example for Multiple Delete with Linq :-

MyAppDataContext db = new MyAppDataContext();
var objCarFeature = db.CarFeatures.Where(p=> p.Car_ID == CarID);
Dt.DataTire.db.CarFeatures.DeleteAllOnSubmit(objCarFeature);
Dt.DataTire.db.SubmitChanges();

//— another way

NorthwindDataContext context = new NorthwindDataContext();
var products = context.Products.Where(p => p.CategoryID == 1);
context.Products.DeleteAllOnSubmit(products);
context.SubmitChanges();

//— another way

MyAppDataContext db = new MyAppDataContext();
var deleteRelatedRecords = from relatedRecords in db.RelatedRecords
    where relatedRecords.MyForeignKeyID == MyPrimaryKeyID
    select relatedRecords;
foreach (var relatedRecords in deleteRelatedRecords)
{
db.RelatedRecords.DeleteOnSubmit(RelatedRecords);
}
db.SubmitChanges();

Hope this helps

Good Luck

Set default date parameter in Linq DataSource

Hi

try this example

<asp:LinqDataSource ID=”LinqDataSource1″ runat=”server”
    ContextTypeName=”newDataContext” TableName=”Lists”
    Where=”startDate >= DateTime.Now”>
</asp:LinqDataSource>

another way

IF you wanted to do it completely in the codebehind, you can too (refer example)
and IF you want a Default date on a parameter:

<asp:LinqDataSource ID=”LinqDataSource1″ runat=”server”
    ContextTypeName=”newDataContext” TableName=”Lists”
    Where=”startDate &gt;= @startDate”>
    <WhereParameters>
        <asp:Parameter DefaultValue=”<%# DateTime.Now %>” Name=”startDate” Type=”DateTime” />
    </WhereParameters>
</asp:LinqDataSource>

Hope this helps

Good Luck

Use LINQ to XML to create XML document from Database

Hi

try this example to Use LINQ to XML to create XML document from database :-

1) Open VS 2008

2) Create new website

3) Add the following code in code behind :-

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            using (SqlConnection conn = new SqlConnection(“Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True”))
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(“select CategoryID, CategoryName, Description from Categories”, conn);
                IDataReader reader = comm.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                var xmlDoc = new XDocument(
                    new XElement(“rootElement”,
                        new XElement(“categories”,
                            from cat in dt.AsEnumerable()
                            select new XElement(“category”,
                                       new XAttribute(“CategoryID”, cat["CategoryID"]),
                                       new XAttribute(“CategoryName”, cat["CategoryName"]),
                                       new XAttribute(“Description”, cat["Description"])
                                )
                       )
                    )
                );
                Response.Clear();
                Response.ContentType = “text/xml”;
                xmlDoc.Save(Response.Output, SaveOptions.None);
                Response.End();
                reader.Close();
                comm.Dispose();
            }
        }
    }
}

Hope this helps

Good Luck

Displaying two Column Fields in DropDownList Control using Linq

Hi

try this example :-

1) create new website using VS 2008

2) In this example i use Northwind database

3) Add new Data Context Class called “Northwind.dbml” and add Employee table in it 

4) Add new web page and add DropdownList control and in code behind add the following code

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

    private void Bind()
    {
        NorthwindDataContext db = new NorthwindDataContext();
        var emp = from em in db.Employees
                  select new { EmployeeID = em.EmployeeID, FullName = em.FirstName + ” ” + em.LastName };
        DropDownList1.DataSource = emp;
        DropDownList1.DataTextField = “FullName”;
        DropDownList1.DataValueField = “EmployeeID”;
        DropDownList1.DataBind();
    }

Hope this helps

you can also check this link to Displaying two Column Fields in DropDownList Control from Here

Good Luck

Displaying Online users using Memebership with Linq

Hi

try this example to display Online users using Memebership with Linq :-

var online = from MembershipUser u in Membership.GetAllUsers()
                  where u.IsOnline == true
                  select u;
GridView1.DataSource = online;
GridView1.DataBind();

Hope this helps

Good Luck

Create RSS Feed using Linq

Hi

try this example to create Rss Feed using linq :-

1) create new website using VS 2008

2) In this example i use Northwind database

3) Add new Data Context Class called “Northwind.dbml” and add Employee table in it

4) Add new web page and in code behind add the following code

protected void Page_Load(object sender, EventArgs e)
    {
        NorthwindDataContext context = new NorthwindDataContext();
        Response.Clear();
        Response.ContentType = “text/xml”;
        XDocument document = new XDocument(
            new XDeclaration(“1.0″, “utf-8″, null),
            new XElement(“rss”,
                new XElement(“channel”,
                    new XElement(“title”, “Employees of Northwind Traders Inc.”),
                    new XElement(“link”, “http://www.yoursite.com”),
                    new XElement(“description”, “Employees of Northwind Traders Inc. ordered by last name.”),
                    from emp in context.Employees
                    orderby emp.LastName
                    select new XElement(“item”,
                        new XElement(“title”, emp.LastName + “, ” + emp.FirstName),
                        new XElement(“description”, emp.Notes),
                        new XElement(“link”, “http://www.yoursite.com/Details.aspx?id=” + emp.EmployeeID))
                        ),
                        new XAttribute(“version”, “2.0″)));
        document.Save(Response.Output);
        Response.End();
    }

Hope this helps

Good Luck

LINQ to XML to Add and Read Data from XML File

Hi

try this example to use LINQ to XML to Add  and Read Data from XML File

1) Open VS 2008 and create new website

2) Add new XML file  called :- Pepole.xml

<?xml version=”1.0″ encoding=”utf-8″?>
<Persons>
  <Person>
    <Name>Yasser</Name>
    <City>Cairo</City>
    <Age>25</Age>
  </Person>
</Persons>

3) Add new web page :-

<div>
    <strong>Add to XML</strong><br />
 Name:<br />
 <asp:TextBox ID=”txtName” runat=”server” /><br />
 City:<br />
 <asp:TextBox ID=”txtCity” runat=”server” /><br />
 Age:<br />
 <asp:TextBox ID=”txtAge” runat=”server” /><br />
 <asp:Button ID=”butAdd” runat=”server” Text=”Add” onclick=”butAdd_Click” /><br />
 <asp:Label ID=”lblStatus” runat=”server” />
 <br /><br />
 <strong>Read XML:</strong><br />
 <asp:Button ID=”butRead” runat=”server” Text=”Read” onclick=”butRead_Click” /><br />
 <asp:Literal ID=”litResults” runat=”server” />
    </div>

and in code behind :-

protected void butRead_Click(object sender, EventArgs e)
    {
        readXML();
        lblStatus.Text = “”;
    }

    private void readXML()
    {
        XDocument xDoc = XDocument.Load(Server.MapPath(“~/People.xml”));
        var persons = from person in xDoc.Descendants(“Person”)
                      select new
                      {
                          Name = person.Element(“Name”).Value,
                          City = person.Element(“City”).Value,
                          Age = person.Element(“Age”).Value,
                      };
        litResults.Text = “”;
        foreach (var person in persons)
        {
            litResults.Text = litResults.Text + “Name: ” + person.Name + “<br />”;
            litResults.Text = litResults.Text + “City: ” + person.City + “<br />”;
            litResults.Text = litResults.Text + “Age: ” + person.Age + “<br /><br />”;

        }
    }

    protected void butAdd_Click(object sender, EventArgs e)
    {
        try
        {
            if (txtName.Text == “” || txtCity.Text == “” || txtAge.Text == “”)
                lblStatus.Text = “Please complete the form.”;
            else
            {
                XDocument xmlDoc = XDocument.Load(Server.MapPath(“People.xml”));

                xmlDoc.Element(“Persons”).Add(new XElement(“Person”,
                    new XElement(“Name”, txtName.Text),
                    new XElement(“City”, txtCity.Text),
                    new XElement(“Age”, txtAge.Text)));

                xmlDoc.Save(Server.MapPath(“People.xml”));
                lblStatus.Text = “Data successfully added to XML file.”;
                readXML();
            }
        }
        catch
        {
            lblStatus.Text = “Sorry, unable to process request. Please try again.”;
        }
    }

Hope this helps

Good Luck

Get Last Inserted Identity with Stored Procedure using Linq

Hi

try this example :-

let we have a stored procedure which will return the last inserted Record

1) Create new Project and add Linq to SQL  Classes and name it DataContext.dbml

2) Add your table and your Stored Procedure inside DataContext.dbml

Create PROCEDURE [proc_CountriesInsert]
(
 @CountryID smallint = NULL output,
 @CountryName nvarchar(50) = NULL,
 @xCurrency_ID smallint = NULL
)
AS
BEGIN
 
 INSERT
 INTO [Countries]
 (
  [CountryName],
  [xCurrency_ID]
 )
 VALUES
 (
  @CountryName,
  @xCurrency_ID
 )
 SELECT @CountryID = SCOPE_IDENTITY()
END
in code in button click :-

short ? CountryId=0;
DataContext.proc_CountriesInsert(ref CountryId,”xx”,1);
 if (countryid.HasValue)
    {
        Label1.Text = “Inserted Value –> ” + Convert.ToString(CountryId);
    }
    else
     {
       Label1.Text = “No Value ..”;
     }

Hope this helps

Good Luck

Bind DropdownList using Linq

Hi

try this example to bind dropdownlist using Linq with Northwind database

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

        }
    }

    private void BindCategory()
    {
        NorthwindDataContext db = new NorthwindDataContext();
        var query = from p in db.Categories
                    select new { p.CategoryID, p.CategoryName };

        DropDownList1.DataSource = query;
        DropDownList1.DataTextField = “CategoryName”;
        DropDownList1.DataValueField = “CategoryID”;
        DropDownList1.DataBind();
    }

Hope this helps

Good Luck