//Instructs framework to ativate persistentce for mapping schema
MappingSchemaFactory.Add(Assembly.GetExecutingAssembly(), Assembly.GetExecutingAssembly(),
 "ClientApp.NorthwindPersistentLayer.xsd");

//Initialize Default DataBroker.
// Votre châine de connexion
string connectionString = @"Data Source=.\SQL2005; Initial Catalog=Northwind;User Id=sa; Password=";
DataBroker.DefaultDataBroker = new DataBroker(new SqlDataProvider(), connectionString, null);

//Enable SQL Trace
DataBroker.DefaultDataBroker.EnableTraceSql = true;

using LastComponent.PersistentDataSets.Framework;
using LastComponent.PersistentDataSets.Framework.Data.SqlServer;
using LastComponent.PersistentDataSets.Framework.Schema;

CustomerQuery listClients = new CustomerQuery();

SELECT A0.[CustomerID], A0.[CompanyName], A0.[ContactName], A0.[ContactTitle], A0.[Address], A0.[City], A0.[Region],
A0.[PostalCode], A0.[Country], A0.[Phone], A0.[Fax]
FROM [Northwind].[dbo].[Customers] A0

// Requête sur la table Customers
CustomerQuery listClients = new CustomerQuery();

// Remplissage de la table Customers du modèle de données
northwind.Customers.Fill(listClients);

cbClientList.DataSource = northwind.Customers;

using (TransactionContext ts = new TransactionContext(TransactionOption.Required))
{
	// Requête sur la table Customers
	CustomerQuery listClients = new CustomerQuery();

	// Remplissage de la table Customers du modèle de données
	northwind.Customers.Fill(listClients);

	cbClientList.DataSource = northwind.Customers;
	cbClientList.DisplayMember = northwind.Customers.ContactName.SourceColumn;
	cbClientList.ValueMember = northwind.Customers.CustomerID.SourceColumn;
}

using (new TransactionContext(TransactionOption.Required))
{
	// On intérroge la table Customers
	CustomerQuery listClients = new CustomerQuery();
	
	// On en sélectionne que ceux dont le ContactName contient la letter A
    listClients.Criteria = listClients.ContactName.Like("%A%");

	northwind.Customers.Fill(listClients);

	cbClientList.DataSource = northwind.Customers;
    cbClientList.DisplayMember = northwind.Customers.ContactName.SourceColumn;
    cbClientList.ValueMember = northwind.Customers.CustomerID.SourceColumn;
}

SELECT A0.[CustomerID], A0.[CompanyName], A0.[ContactName], A0.[ContactTitle], A0.[Address], A0.[City], A0.[Region],
A0.[PostalCode], A0.[Country], A0.[Phone], A0.[Fax]
FROM [Northwind].[dbo].[Customers] A0
WHERE (A0.[ContactName] LIKE '%A%')

using (new TransactionContext(TransactionOption.Required))
{
	// On intérroge la table Customers
	CustomerQuery listClients = new CustomerQuery();
	
	// On en sélectionne que ceux dont le ContactName contient la letter A
    listClients.Criteria = listClients.ContactName.Like("%A%") &&
	listClients.Orders.OrderDate.Between(new DateTime(1982, 06, 28), DateTime.Now);

	northwind.Customers.Fill(listClients);

	cbClientList.DataSource = northwind.Customers;
    cbClientList.DisplayMember = northwind.Customers.ContactName.SourceColumn;
    cbClientList.ValueMember = northwind.Customers.CustomerID.SourceColumn;
}

SELECT A0.[CustomerID], A0.[CompanyName], A0.[ContactName], A0.[ContactTitle], A0.[Address], A0.[City], A0.[Region],
A0.[PostalCode], A0.[Country], A0.[Phone], A0.[Fax]
FROM [Northwind].[dbo].[Customers] A0
	INNER JOIN [Northwind].[dbo].[Orders] A1 ON (A0.[CustomerID] = A1.[CustomerID])
WHERE ((A0.[ContactName] LIKE '%A%') AND (A1.[OrderDate] BETWEEN '28/06/1982' AND '11/02/2006'))

using (new TransactionContext(TransactionOption.Required))
{
	// On intérroge la table Customers
	CustomerQuery listClients = new CustomerQuery();
	
	// On en sélectionne que ceux dont le ContactName contient la letter A
    listClients.Criteria = listClients.ContactName.Like("%A%");
    listClients.OrderByList.Add(listClients.ContactName, true);

	northwind.Customers.Fill(listClients);

	cbClientList.DataSource = northwind.Customers;
    cbClientList.DisplayMember = northwind.Customers.ContactName.SourceColumn;
    cbClientList.ValueMember = northwind.Customers.CustomerID.SourceColumn;
}

SELECT A0.[CustomerID], A0.[CompanyName], A0.[ContactName], A0.[ContactTitle], A0.[Address], A0.[City], A0.[Region],
A0.[PostalCode], A0.[Country], A0.[Phone], A0.[Fax]
FROM [Northwind].[dbo].[Customers] A0
ORDER BY A0.[ContactName] ASC

using (new TransactionContext(TransactionOption.RequiresNew))
{
	// Chargement de la commande n°10248
    Order order = northwind.Orders.Fill(10248);

	MessageBox.Show(
    	String.Format("Nom de livraison: {0}{1}Adresse de livraison: {2}", order.ShipName,
        	Environment.NewLine, order.ShipAddress));
}

SELECT A0.[OrderID], A0.[CustomerID], A0.[EmployeeID], A0.[OrderDate], A0.[RequiredDate], A0.[ShippedDate], A0.[ShipVia],
A0.[Freight], A0.[ShipName], A0.[ShipAddress],
A0.[ShipCity], A0.[ShipRegion], A0.[ShipPostalCode], A0.[ShipCountry]
FROM [Northwind].[dbo].[Orders] A0
WHERE (A0.[OrderID] = 10248)

 using (TransactionContext ts = new TransactionContext(TransactionOption.RequiresNew))
{
    // Ajout d'une ligne dans la table Employees
    northwind.Employees.AddNew("LEBRUN", "Thomas");
                
    // On met à jour la base de données
    northwind.Employees.Update();
                
    // Validation de la transaction
	ts.Commit();
}

DECLARE @scope_identity int;
INSERT INTO [Northwind].[dbo].[Employees]([LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate],
[Address], [City], [Region],
[PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath]) 
VALUES('LEBRUN', 'Thomas', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
SELECT @scope_identity = SCOPE_IDENTITY();
SELECT @scope_identity;

using (TransactionContext ts = new TransactionContext(TransactionOption.RequiresNew))
{
    // Une requête sur la table Employees
	EmployeeQuery query = new EmployeeQuery();
	query.Criteria = query.FirstName == "Thomas" && query.LastName == "LEBRUN";

	Employee [] myself = northwind.Employees.Fill(query);

    myself[0].Title = "Monsieur";
                
    myself[0].Update();
                
	ts.Commit();
}

SELECT A0.[EmployeeID], A0.[LastName], A0.[FirstName], A0.[Title], A0.[TitleOfCourtesy], A0.[BirthDate], A0.[HireDate],
A0.[Address], A0.[City], A0.[Region], A0.[PostalCode],
A0.[Country], A0.[HomePhone], A0.[Extension], A0.[Photo], A0.[Notes], A0.[ReportsTo], A0.[PhotoPath]
FROM [Northwind].[dbo].[Employees] A0
WHERE ((A0.[FirstName] = 'Thomas') AND (A0.[LastName] = 'LEBRUN'))

UPDATE [Northwind].[dbo].[Employees] SET [Title] = 'Monsieur' 
WHERE [EmployeeID] = 11;

using (TransactionContext ts = new TransactionContext(TransactionOption.RequiresNew))
{
	// Une requête sur la table Employees
    EmployeeQuery query = new EmployeeQuery();
    query.Criteria = query.FirstName == "Thomas" && query.LastName == "LEBRUN";

	Employee[] myself = northwind.Employees.Fill(query);

	// On marque l'élément comme bon pour supprimer
	myself[0].Delete();
                
    myself[0].Update();

	ts.Commit();
}

SELECT A0.[EmployeeID], A0.[LastName], A0.[FirstName], A0.[Title], A0.[TitleOfCourtesy], A0.[BirthDate], A0.[HireDate],
A0.[Address], A0.[City], A0.[Region], A0.[PostalCode],
A0.[Country], A0.[HomePhone], A0.[Extension], A0.[Photo], A0.[Notes], A0.[ReportsTo], A0.[PhotoPath]
FROM [Northwind].[dbo].[Employees] A0
WHERE ((A0.[FirstName] = 'Thomas') AND (A0.[LastName] = 'LEBRUN'))

DELETE FROM [Northwind].[dbo].[Employees] 
WHERE [EmployeeID] = 11;

using (TransactionContext ts = new TransactionContext(TransactionOption.RequiresNew))
{
	EmployeeQuery query = new EmployeeQuery();

    northwind.Employees.Fill(query);
                
    northwind.Employees.DeleteAll();
                
    northwind.Employees.Update();
                
	ts.Commit();
}