LINQ and ADO.NET

LINQ to DataSet

DataSet and DataTable store a lot of data in memory, but are limited in their query functionality.

LINQ to DataSet provides rich query functionality for them.

DataSet = in-memory relational representation of data.

Primary disconnected data object used by many ASP.NET apps.

DataSet contains DataTable and DataRelation objects.

DataSet Schema

Before working with data need to define schema.

Can do automatically, programmatically or via XML schema definition, e.g. for code

DataSet companyData = new DataSet("CompanyList");

DataTable company = companyData.Tables.Add("company");

company.Columns.Add("Id", typeof(Guid));

company.PrimaryKey = new DataColumn{} { company.columns["Id"] };

.

DataTable employee= companyData.Tables.Add("employee");

employee.Columns.Add("Id", typeof(Guid));

employee.PrimaryKey = new DataColumn{} { employee.columns["Id"] };

companyData.Relations.Add("Company_Employee", company.Columns["Id"}, employee.Columns["Id"]);

Populating DataSet

Can write code to add rows:

DataTable compnay = companyData.Tables["Company"];

company.Rows.Add(Guid.NewGuid(), "Northwind Traders");

Can use DataAdapter control to fill DataTable. The DbDataAdapter class (derived from DataAdapter) retrieved and updates DataTable from a data store. Provider specific version of DbDataAdapter exist for SQL Server, Oracle and XML.

DbDataAdapter.Select() defines how data is to be retrieved.

DataAdapter.Fill moves data from data store to DataTable.

DbConnection conn = new SqlConnection(pubs.ConnectionString);

SqlCommand cmd = (SqlCommand) conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT pub_id, pub_name FROM Publishers");

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet pubsDataSet = new DataSet("Pubs");

da.Fill(pubDataSet, "publishers");

Saving Changes

DataAdapter.Update method retrieves updates from DataTable and executes appropriate InsertCommand, UpdateCommand or DeleteCommand to send changes to data store on row-by-row basis

Update method examines RowState property for each row, if not Unchanged then changes sent to database.

For Update method to work the select, insert, update and delete commands must be assigned to DbDataAdapter. Can create these commands using DbDataAdapter configuration wizard (which starts when DbDataAdapter dropped onto webpage).

Can also populate DbDataAdapter commands via DbCommandBuilder which creates insert, update and delete commands from a select statement.

Saving Changes In Batches

Controlled by UpdateBatchSize property of DbDataAdapter.

When 0 the DbDataAdapter will use the largest possible batch size.

Otherwise set it to the number of changes to be sent to database in one batch (default is 1).

Typed Data Sets

DataSet based on strongly typed objects.

Allow programming against actual table and field schemas and not relying on strings, i.e.

DataTable cTable = salesData.Tables["Company"] vs DataTable cTable = vendorData.Company;

Typed DataSet inherits from DataSet. Provide property for each table. Do same for each field in table.

Can use XSD to generate typed DataSet class.

Can use DataSetEditor to graphically create and modify XSD file.

Querying With LINQ To DataSet

DataTable exposed as IEnumerable collections => LINQ statements like other objects:

DataTable employees = MyDataProvider.GetEmployeeData();

var query = from employe in employes.AsEnumerable() where employee.Field<Decimal>("salary") > 20 select employee;

decimal avgSal = employees.AsEnumerable().Average(employee => employee.Field<Decimal>("Salary"));

Use AsEnumerable() to provide something for LINQ to work against.

Querying With Typed Data Sets

Do not need to use Field method, instead query against Tablename.Fieldname construct:

SqlDataAdapter adp = new SqlDataAdapter("select * from publishers;" pbsCnn.ConnectionString);

adp.Fill(pubs, "publishers");

var pbsQuery = from p in pubs.punlishers where p.country == "USA" select p;

Cross-table LINQ to DataSets

First load DataSet with two tables.

Assign DataTables to variables and use these in LINQ query.

Results of query pushed to anonymous type.

Comparing Data

Compare data in one or more tables using following operators:

  • Distinct - distinct DataRows in collection
  • Union - joins two DataTables together
  • Intersect - DataRows that appear in both DataTables
  • Except - DataRows that appear in one or other (but not both) DataTables.

LINQ to SQL

Works directly with SQL Server database.

Build object-relational (O.R) map that connects .NET classes to database elements.

One map built, code against database as if coding against objects.

Note, Entity Framework provides similar functionality but not limited to SQL Server.

Part of ADO.NET so can use other ADO.NET components like transactions, existing objects written against ADO.NET, stored procedures, etc.

Mapping Objects to Relational Data

Visual Studio provides two automated code generators.

Can also manually code own O/R map for full control over mapping.

Using Designer Tool

Use Visual Studio LINQ to SQL designer.

Provides design surface on which to build classes.

Access designer via Add New Item dialogue.

Generates DBML (Database Markup Language) files - contains XML metadata, layout file for designer and code behind file which contains object used to access database code.

Build map by dragging entities from Visual Studio Server Explorer onto design surface.

Command Line Tool

Use SqlMetal command line tool to generate DBML files and O/R code.

Useful for large databases where not practical to use design surface.

Simplest option is point tool at database file / provide connection string

Via Code Editor

Granular control, but a lot of work.

Create class file and use the System.Data.Linq.Mapping namespace.

Link class to table via Table attribute:

[Table(Name="Author")]
public class Author
{

Create properties on class with Column attribute applied:

    private string _authorId;

    [Column(IsPrimaryKey=true, Storage="_authorId", Name="au_Id"]
    public string Id
    {
    get { return _authorId; }
    set { _authorId = value; }
    }

Finally create class to expose tables.

Inherit from DataContext object.

Acts as go-between for database and your objects.

public class PubsDb: DataContext
{
  public Table<Authors> Authors;

Querying Data

Much like any other LINQ query.

PubsDatabaseModelDataContext pubs = new PubsDatabaseModelDataContext();

var authQuery = from auth in pubs.authors where auth.state == "CA" select auth;

Inserting, Updating and Deleting

Simply make change to object instance and save Once complete call SubmitChanges method of DataContext to persist.

PubsDatabaseModelDataContext pubs = new PubsDatabaseModelDataContext();

author auth = new author();

auth.au_id = "000";

pubs.authors.InsertOnSubmit(auth);

pubs.SubmitChanges();

LINQ to Entities

Works in similar way to LINQ to SQL.

Define model to represent application domain, then a map between model and actual data source.

Creating Entity Model

Use the ADO.NET Entity Data Model template from Add New Item dialogue.

Generates EDMX file which is XML file that can be edited in designer together with code behind file containing actual entity objects.

Visual Studio uses Data Model Wizard to create model (either from scratch or existing database).

Can use Visual Studio tools to edit model and related mapping. Tools can also do following; generate database, validate model against data source, map to stored procedures, update model from database.

LINQ to Entities Queries

Generate new instance of entity model and write queries against data it represents.

Entity model inherits from System.Data.Objects.ObjectContext

using (PubsModel.pubsEntities pubs = new PubsModel.pubsEntities())
{  
    var authQuery = from a in pubs.Authors where a.State == "CA" select a;  
}

Download