© satyam computer services ltd. 2008 poc architecture 1 confidential – limited circulation
TRANSCRIPT
© Satyam Computer Services Ltd. 2008
POC ArchitecturePOC Architecture
1 Confidential – Limited Circulation
© Satyam Computer Services Ltd. 2008
AgendaAgenda
LINQ TO SQL Architecture
Objects in LINQ-TO-SQL
Examples
Transactions
POC Demo
2
© Satyam Computer Services Ltd. 2008
What is LINQ-TO-SQL?What is LINQ-TO-SQL?
4 Confidential – Limited Circulation
Provides a runtime infrastructure for managing relational data as objects without losing the ability to query.
Application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically
Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns.
Together, the LINQ to SQL run-time infrastructure and design-time tools significantly reduce the workload for the database application developer.
© Satyam Computer Services Ltd. 2008
LINQ ArchitectureLINQ Architecture
5 Confidential – Limited Circulation
LINQ-enabled data sourcesLINQ-enabled data sources
LINQ LINQ To ObjectsTo Objects
LINQ LINQ To XMLTo XML
LINQ-enabled ADO.NETLINQ-enabled ADO.NET
Visual BasicVisual BasicVisual BasicVisual Basic OthersOthersOthersOthers
LINQ LINQ To EntitiesTo Entities
LINQ LINQ To SQLTo SQL
LINQ LINQ To DatasetsTo Datasets
.Net Language Integrated Query (LINQ).Net Language Integrated Query (LINQ)
Visual C#Visual C#Visual C#Visual C#
ObjectsObjects DatabasesDatabases
© Satyam Computer Services Ltd. 2008
LINQ TO SQL ArchitectureLINQ TO SQL Architecture
6 Confidential – Limited Circulation
ApplicationApplicationApplicationApplication
LINQ to SQLLINQ to SQLLINQ to SQLLINQ to SQL
from d in db.Doctorswhere d.City == “Bangalore"select c.doctorName
from d in db.Doctorswhere d.City == “Bangalore"select c.doctorName
EnumerateEnumerate
SELECT doctorNameFROM DoctorsWHERE City = ‘Bangalore'
SELECT doctorNameFROM DoctorsWHERE City = ‘Bangalore'
SQL query SQL query or stored procor stored proc
RowsRows
ObjectsObjects
Db.Doctors.Add(d1);c2.City = “bangalore”;db.Doctors.Remove(d3);
Db.Doctors.Add(d1);c2.City = “bangalore”;db.Doctors.Remove(d3);
SubmitChanges()SubmitChanges()
INSERT INTO Doctors …UPDATE Doctors …DELETE FROM Doctors …
INSERT INTO Doctors …UPDATE Doctors …DELETE FROM Doctors …
DML DML or SProcsor SProcs
© Satyam Computer Services Ltd. 2008
BenefitsBenefits
7 Confidential – Limited Circulation
Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support.
We can write queries against strongly typed collections of objects by using language keywords and familiar operators.
LINQ applies the principles of object-oriented programming to relational data.
It provides a unified programming model for querying data from different types of Data sources, XML documents, ADO.NET Datasets, and any collection of objects that supports Ienumerable
LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging
Fully supports transactions, views, and stored procedures.
© Satyam Computer Services Ltd. 2008
Entity ClassesEntity Classes
8 Confidential – Limited Circulation
Apply a custom attribute to the top of the class declaration. LINQ to SQL defines the Table attribute for this purpose.
Example:
[Table(Name="Customers")]
public class Customer
{
[Column(Storage=“_Cust_Id" ,DbType="Int NOT NULL“,IsPrimaryKey=true)]
public string CustomerID;
[Column(Storage="_City" ,DbType=“VarChar(50) NOT NULL")]
public string City;
}
© Satyam Computer Services Ltd. 2008
Data ContextData Context
9 Confidential – Limited Circulation
A channel or platform by which you retrieve objects from the database and resubmit changes
It is initialized with a connection or connection string
Purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results.
public partial class Northwind : DataContext
{
public Table <Customer> Customers;
public Table<Order> Orders;
public Northwind(string connection): base(connection) {}
}
© Satyam Computer Services Ltd. 2008
Data Context ExamplesData Context Examples
10 Confidential – Limited Circulation
// DataContext takes a connection string
DataContext db = new DataContext("c:\\northwind\\northwnd.mdf");
// Get a typed table to run queries
Table<Customer> Customers = db.GetTable<Customer>();
// Query for customers from London
var q = from c in Customers where c.City == "London"
select c;
foreach (var cust in q)
Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);
© Satyam Computer Services Ltd. 2008
RelationshipsRelationships
11 Confidential – Limited Circulation
LINQ to SQL defines an Association attribute you can apply to a member used to represent a relationship.
An association relationship is one like a foreign-key to primary-key relationship that is made by matching column values between tables.
© Satyam Computer Services Ltd. 2008
Relationships(Master Table)Relationships(Master Table)
12 Confidential – Limited Circulation
[Table(Name="City")]
public class City
{
[Column(Id=true)]
public int city_ID;
...
private EntitySet<Doctor> _Doctors;
[Association(Storage="_Doctors", OtherKey="city_id")]
public EntitySet<Doctor> Doctors {
get { return this.Doctors; }
set { this.Doctors.Assign(value); }
}
}
© Satyam Computer Services Ltd. 2008
Relationships (Child Table)Relationships (Child Table)
13 Confidential – Limited Circulation
[Table(Name="Doctors")]
public class Doctor {
[Column(Id=true)]
public int doctorID;
[Column]
public string city_ID;
private EntityRef<City> _city;
[Association(Storage="_city", ThisKey="cityID")]
public Customer Customer {
get { return this._city.Entity; }
set { this._city.Entity = value; }
}
}
© Satyam Computer Services Ltd. 2008
LINQ Query ExamplesLINQ Query Examples
14 Confidential – Limited Circulation
public class Doctor { … }
public class DBHelperDataContext: DataContext{ public Table<Doctor> Doctors; …}
public class Doctor { … }
public class DBHelperDataContext: DataContext{ public Table<Doctor> Doctors; …}
DBHelperDataContext db = new DBHelperDataContext(…);var contacts = from d in db.Doctors where d.City == “London" select new { d.doctorName };
DBHelperDataContext db = new DBHelperDataContext(…);var contacts = from d in db.Doctors where d.City == “London" select new { d.doctorName };
Classes describe data
Classes describe data
Strongly typed connections
Strongly typed connections
Integrated query syntax
Integrated query syntax
Strongly typed results
Strongly typed results
Tables are like collections
Tables are like collections
© Satyam Computer Services Ltd. 2008
Query ExamplesQuery Examples
15 Confidential – Limited Circulation
Ex 1:
var q = from c in db.city
from d in c.Doctors
where c.City == “Bangalore"
select new { c, d };
Ex 2:
var q = from d in db.Doctors
where d.city.cityName == "London"
select new { c = d.Doctors, d };
© Satyam Computer Services Ltd. 2008
Query ExamplesQuery Examples
16 Confidential – Limited Circulation
Ex: 4
var q = from d in db.Doctors
where d.City == “London"
select d;
foreach (Doctor d in q)
Console.WriteLine(d.doctorName);
The actual type of q in this instance is IQueryable<Customer>.
It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.
© Satyam Computer Services Ltd. 2008
Query ExamplesQuery Examples
17 Confidential – Limited Circulation
var q = from d in db.Doctors
Where d.city == ‘Bangalore’
Select d;
foreach (Doctor d in q)
{
if (d.Speciality ==‘ENT’)
getAppointment(d.Doctor);
}
© Satyam Computer Services Ltd. 2008
Deferred ExecutionDeferred Execution
18 Confidential – Limited Circulation
\\Each foreach causes the SQL to be \\executed
var q = from d in db.Doctors Where d.City == “London"
select d;
foreach (Doctor d in q) Console.WriteLine(d.doctorName);
foreach (Doctor d in q) Console.WriteLine(d.CompanyName);
var q = from d in db.Doctors Where d.City == “London" select d;
// Execute once using ToList() or //ToArray() var list = q.ToList();
foreach (Doctor d in list) Console.WriteLine(d.doctorName);
foreach (Doctor d in list) Console.WriteLine(d.CompanyName);
Actual SQL query is executed when foreach statement is executed
© Satyam Computer Services Ltd. 2008
Deferred Execution BenefitsDeferred Execution Benefits
19 Confidential – Limited Circulation
var q = from d in db.Doctors
where d.City == "London"
select d;
if (orderByLocation) {
q = from d in q
orderby d.Country, d.City
select d; }
else if (orderByName) {
q = from d in q
orderby d.DoctorName
select d; }
foreach (Doctor d in q) Console.WriteLine(d.DoctorName);
© Satyam Computer Services Ltd. 2008
Object IdentityObject Identity
20 Confidential – Limited Circulation
DataContext manages object identity.
Whenever a new row is retrieved from the database, it is logged in an identity table by its primary key and a new object is created.
Whenever that same row is retrieved again, the original object instance is handed back to the application.
In this way, the DataContext translates the databases concept of identity (keys) into the languages concept (instances).
In the case that the database contains a table without a primary key, LINQ to SQL allows queries to be submitted over the table, but it doesn't allow updates.
© Satyam Computer Services Ltd. 2008
DML OperationsDML Operations
21 Confidential – Limited Circulation
Inserting InsertOnSubmit(entity) InsertAllOnSubmit(entityCollection)
Constraints are not checked early, queries are always sent to the database.
Updating SubmitChanges()
Deleting DeleteOnSubmit(entity) DeleteAllOnSubmit(entityCollection)
© Satyam Computer Services Ltd. 2008
Updating DataUpdating Data
22 Confidential – Limited Circulation
After SubmitChanges() is called
Modified objects in the object graph are identified.
New objects to be inserted
Retrieved objects to be deleted
Retrieved objects whose persistent members have changed
Changes are ordered based on foreign key constraints.
Insert, update and delete statements are constructed with modified current values in the set clause and original values in the where clause for optimistic concurrency conflict detection
Database connection is opened and a transaction is started.
If there are any errors, the transaction is rolled back.
If the transaction succeeds, the internal state of the DataContext is changed accordingly.
Database-generated columns like autoincrement / GUID keys and timestamps are propagated back to the objects
© Satyam Computer Services Ltd. 2008
Exception HandlingException Handling
23 Confidential – Limited Circulation
DBHelperdataContext db = new DBHelperdataContext();
// make changes here
try
{
db.SubmitChanges();
}
catch (Exception e)
{
// make some adjustments ... // try again
db.SubmitChanges();
}
© Satyam Computer Services Ltd. 2008
LINQ Query to SQL Translation LINQ Query to SQL Translation
24 Confidential – Limited Circulation
LINQ to SQL translates the queries you wrote into equivalent SQL queries and sends them to the server for processing.
Because execution is deferred, LINQ to SQL is able to examine your entire query even if assembled from multiple parts.
Queries are not transmitted to the server as IL They are in fact transmitted as parameterized SQL queries in text form.
Example:
var q = from c in db.Contact
where c.DateOfBirth.AddYears(35) > DateTime.Now
orderby c.DateOfBirth descending select c;
Translates to
SELECT [t0].[ContactId], [t0].[FirstName], [t0].[LastName],[t0].[DateOfBirth],[t0].[Phone]
FROM [Contact] AS [t0]
WHERE DATEADD(YEAR, @p0, [t0].[DateOfBirth]) > @p1
ORDER BY [t0].[DateOfBirth] DESC
© Satyam Computer Services Ltd. 2008
Transactions Transactions
25 Confidential – Limited Circulation
A transaction is a service provided by databases or any other resource manager that can be used to guarantee that a series of individual actions occur automatically; meaning either they all succeed or they all don't.
If no transaction is already in scope, the DataContext will automatically start a database transaction to guard updates when you call SubmitChanges().
Product prod = q.Single(p => p.ProductId == 15);
if (prod.UnitsInStock > 0)
prod.UnitsInStock--;
db.Transaction = db.Connection.BeginTransaction();
try {
db.SubmitChanges();
db.Transaction.Commit();
} catch {
db.Transaction.Rollback(); throw;
}
finally { db.Transaction = null; }
© Satyam Computer Services Ltd. 2008 27 Confidential – Limited Circulation
Exception Handling
Application Block
© Satyam Computer Services Ltd. 2008
What is Exception Handling Application Block?What is Exception Handling Application Block?
28 Confidential – Limited Circulation
It is a part of Patterns & Practices Enterprise Library. Patterns & Practices Enterprise Library is a collection of Application Blocks. Application Blocks are provided in the form of source code as a guidance which can be used as it is, can be extended or can be modified as per the requirements.
It allows developers and policy makers to create a consistent strategy for handling exceptions in the application.
© Satyam Computer Services Ltd. 2008
What It Offers?What It Offers?
29 Confidential – Limited Circulation
It allows administrators and policy makers to create policies to handle exceptions. They can maintain and modify policies without changing application code.
It supports exception handling in all layers of an application
It provides common tasks which are required at the time of exception handling. Like logging exception information, replacing original exception with another exception and wrapping original exception inside another exception.
It allows developers to create their own exceptions.
© Satyam Computer Services Ltd. 2008
Enterprise Library Installation InformationEnterprise Library Installation Information
30 Confidential – Limited Circulation
Latest version : Enterprise Library 3.1 – May 2007
Download Size : 22.9 MB
Link to download : http://www.microsoft.com/downloads/details.aspx?FamilyId=4C557C63-708F-4280-8F0C-637481C31718&displaylang=en
System Requirements :
Operating Systems: Windows Server 2003, Windows Vista, Windows XP
Microsoft Framework 2.0 or 3.0
Microsoft Visual Studio 2005
© Satyam Computer Services Ltd. 2008
Hierarchy of ObjectsHierarchy of Objects
31 Confidential – Limited Circulation
Configuration
Policies
Exception Types
Exception Handlers
Logging Handler
Wrap Handler
Replace Handler
Custom Handler
Fault Contract Exception Handler
© Satyam Computer Services Ltd. 2008
ConfigurationConfiguration
32 Confidential – Limited Circulation
It is a configuration file of the application which is created to define policies for exception handling by policy makers. This can be maintained with the help of Enterprise Library Configuration tool. One can also modify the configuration file manually.
© Satyam Computer Services Ltd. 2008
PolicyPolicy
33 Confidential – Limited Circulation
Policy is a collection of one or more exception types with a given name. In your policy you include one or more type of exceptions to handle.
Only specified exceptions can be handled by the policy and not others. But if you pass the inherited exception types then they are handled. If you pass other exception types to the policy then they will not be handled and same exception will be returned.
© Satyam Computer Services Ltd. 2008
Exception TypesException Types
34 Confidential – Limited Circulation
It is a list of Exception Types to be handled by a policy.
You can specify Post Handling Action for each exception. Possible values are:
None: You don’t want to take any action when this type of exception occurs. Normally you can take this action with Logging Handler. You log the exception and do nothing.
NotifyRethrow: You want to throw the same exception back to the application. Normally you can take this action with Logging Handler. You log the exception and throw back the same exception. Calling method/component can take further action on the exception.
ThrowNewException: You want to throw a new exception to the application. Normally you can take this action with Wrap or Replace Handler. You wrap or replace original exception and throw a new exception.
© Satyam Computer Services Ltd. 2008
Exception HandlersException Handlers
35 Confidential – Limited Circulation
Logging Handler:
Logging exception information is a frequently required task as a part of exception handling.
With the help of this handler we can log the exception information in locations specified in the configuration file.
To use Logging Handler, Logging Application Block must be included in the application.
Logging Handler passes the information to the Logging Application Block to log the exception information.
© Satyam Computer Services Ltd. 2008
Exception HandlersException Handlers
36 Confidential – Limited Circulation
Replace Handler:
You may want to replace the original exception, which contains sensitive or unnecessary information, with another more appropriate exception. For example original exception may contain any URL, any database information etc.
New specified exception will be thrown in place of the original exception.
New exception can have a customized message.
© Satyam Computer Services Ltd. 2008
Exception HandlersException Handlers
37 Confidential – Limited Circulation
Wrap Handler:
You may want to wrap the original exception inside another exception. Wrapping will throw a new exception of the specified type and sets the original exception as InnerException.
Use wrapping in situations where the calling method/component can take decisions on the basis of new exception type. And if required it can use the underlying original exception as well.
© Satyam Computer Services Ltd. 2008
Exception HandlersException Handlers
38 Confidential – Limited Circulation
Custom Handler:
You may want to throw your own customized exception type in place of original exception. For that create an exception class which implements IExceptionHandler and use it in the Custom Handler.
© Satyam Computer Services Ltd. 2008
How It WorksHow It Works
39 Confidential – Limited Circulation
Add the reference of necessary Exception Handling Application Block DLLs from Enterprise Library into the application. If you are using Logging Handlers then also take the reference of necessary Logging Application Block DLLs.
Create a config file & Define policies into the config file
© Satyam Computer Services Ltd. 2008
How It WorksHow It Works
41 Confidential – Limited Circulation
Deploy the configuration file at all layers of the application where you want to have Exception Handling functionality.
Call the policy to handle the exception where you want to use the policy. You call the policy into the Catch block and pass the exception to the policy. Policy will execute all handlers one by one assigned for the passed the exception.
© Satyam Computer Services Ltd. 2008
How It WorksHow It Works
42 Confidential – Limited Circulation
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Public Class clsDAL
Public Function GetData() As DataSet
Dim DS As New DataSet
Try
' Data access code will come here.
Catch ex As Exception
Call HandleExceptions(ex)
End Try
Return DS
End Function
Private Sub HandleExceptions(ByVal Ex As Exception)
Dim showErr As Boolean
Dim HandlerEx As New Exception
showErr = ExceptionPolicy.HandleException(Ex, "DALPolicy",
HandlerEx)
If Not HandlerEx Is Nothing Then
Throw HandlerEx
Else
If showErr = True Then
Throw Ex
End If
End If
End Sub
End Class
© Satyam Computer Services Ltd. 2008
Multi-tier architecture ViewMulti-tier architecture View
43 Confidential – Limited Circulation
© Satyam Computer Services Ltd. 2008
Multi-tier architecture ViewMulti-tier architecture View
44 Confidential – Limited Circulation
Exceptions that occur in the data access layer are logged and then wrapped inside another exception that provides more meaningful information to the calling layer. Within the business component layer, the exceptions are logged before they are propagated.
Any exceptions that occur in the business component layer and that contain sensitive information are replaced with exceptions that no longer contain this information. These are sent to the user interface (UI) layer and displayed to the user.