Fundamentals Of ADO.NET &

LINQ To SQL

AGENDA

1.Fundamentals of ADO.NET2.Components of ADO.NET3.More about LINQ4.Creating connections from

Database5.Queries

FUNDAMENTALS OF ADO.NET

What is ADO.NET ?

 ADO is ActiveX Data Object. ADO.NET is the .NET technology for interacting with data sources(such as Microsoft SQL Server, and XML, as per your requirements.)  ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.

ADO.NET(Cont.)

ADO.NET Object Model is based upon the objects of the System.Data namespace.Supports both connected and disconnected architecture:Connected:Application is constantly connected to data source.Provided by DataReader Object. Advantages: Updated data and data concurrency.Disconnected:Connection not constant.Provided by DataSet Object.Advantages: Scalability and performance.

ADO.NET Architecture

Data processing has traditionally been a connection-based, two-tier model.

As data processing increasingly uses multi-tier architectures, programmers are switching to a disconnected approach to provide better scalability for their applications.

Two central data access components:.NET Framework Data ProvidersThe DataSet

Architecture of ADO.NET

Connected Architecture of ADO.NET

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture.

DataReader : > Forward only > one record at a time > read() :return type bool  

Disconnected Architecture of ADO.NET

The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture. 

DataAdapter : DataAdapter is used to transfer the data between database and dataset. It has commands like select, insert, update and delete. Select command is used to retrieve data from database and insert, update and delete commands are used to send changes to the data in dataset to database. It needs a connection to transfer the data.

USE OF CommandBuilder

CommandBuilder : by default dataadapter contains only the select command and it doesn’t contain insert, update and delete commands.

> To create insert, update and delete commands for the dataadapter, commandbuilder is used. It is used only to create these commands for the dataadapter and has no other purpose.

DataSet

•DataSet : Dataset is used to store the data retrieved from database by dataadapter and make it available for .net application.

•To fill data in to dataset fill() method of dataadapter is used and has the following syntax.Da.Fill(Ds,”TableName”); 

Connected Disconnected

It is connection oriented. It is dis_connection oriented.

Datareader DataSet

Connected methods gives faster performance

Disconnected get low in speed and performance.

connected can hold the data of single table

disconnected can hold multiple tables of data

connected you need to use a read only forward only data reader

disconnected you cannot

Data Reader can't persist the data Data Set can persist the data

It is Read only, we can't update the data.

We can update data

DIFFERENCE B/W CONNECTED AND DISSCONECTED

Data Providers (contd…)

The following table lists the data providers that are included in the .NET Framework:

Provider Name API Prefix

Data Source Description

ODBC Data Provider Odbc Data Sources with an ODBC interface.  Normally older data bases.

OleDb Data Provider OleDb Data Sources that expose an OleDb interface, i.e. Access or Excel.

Oracle Data Provider Oracle For Oracle Databases.

SQL Data Provider Sql For interacting with Microsoft SQL Server.

Borland Data Provider Bdp Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.

Data Providers

ADO.NET allows us to interact with different types of data sources.

HOW? ADO.NET comes in different sets of libraries for each type of a data source.  These libraries are called Data Providers.

Mainly responsible for providing and maintaining connection to the database.

Data Providers (Cont.)

The following are the four core objects that make up a .NET Framework data provider:

The Connection object provides connectivity to a data source.

The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information.

The DataReader provides a high-performance stream of data from the data source.

The DataAdapter provides the bridge between the DataSet object and the data source.

Accessing SQL Database using ADO.NET

Getting started… Namespace:Using System.Data.SqlClient;

ADO.NET includes many objects you can use to work with data:The SqlConnection Object The SqlCommand ObjectThe SqlDataReader ObjectThe DataSet ObjectThe SqlDataAdapter Object

The SqlConnection OBJECT 

To interact with a database, you must have a connection to it.Before you can create a connection to a database, you must have a connection string: contain all of the information that you need to establish a database connection, including the server name, the database name, the user ID, and the password and other parameters that are required for connecting to the data base.

String str = “Initial Catalog = unit4;Data Source=.\SQLEXPRESS ;Integrated Security=True”

Using this connection string, create an SqlConnection object, and call its Open() method to establish a connection to your database:

For example, the following connection string points to a local computer that is running SQL Server:

SqlConnection con = new SqlConnection(str);

con.Open();

SqlConnection Object(contd…)

Properties and methods:ConnectionStringDatabaseConnectionTimeoutProviderDataSourceOpenCloseBeginTransaction

SqlCommand OBJECT

Enables access to the database commands such as SQL statements or stored procedures.After creating a connection, create a SqlCommand object, and pass in the following information:The command that you want to run, andThe connection object that you created in the previous step.The following sample code passes in the INSERT statement:string cmd = "INSERT INTO Employee(empId, empName, EmpHireDate)“ + "VALUES ('MSD12923F', 'Duncan', '2001-01-01')“;

SqlCommand objCmd = new SqlCommand( cmd, con );

SqlCommand(contd…)

• Properties & Methods: CommandText Connection Parameters

ExecuteNonQuery() ExecuteReader() ExecuteScalar()

SqlCommand(contd…)

Methods:

Item Description

ExecuteReader Executes the query, and returns a SqlDataReader object.

ExecuteNonQuery Executes the query, and does not collect any results. Generally used for queries such as UPDATE and DELETE.

ExecuteScalar Executes the query, and returns a single value (from the first column of the first row).

METHODS

1. ExecuteNonQuery()

If we're not interested in the result of a query (other than whether it executed successfully or not).

Returns an integer value specifying the rows affected by the insert, delete and update.For example,// create the command object 

SqlCommand sqlComm = new SqlCommand( "DELETE FROM users WHERE userid=1", sqlConn );sqlComm.ExecuteNonQuery();

2. ExecuteScalar()

Returns the value of the first column of the first row returned.We need to cast the result to the appropriate data type.

For example,// create the command object

SqlCommand sqlComm = new SqlCommand("SELECT COUNT(*) FROM users", sqlConn);int userCount = (int)sqlComm.ExecuteScalar();

3. ExecuteReader()

When we're retrieving multiple rows and columns of data.Returns an instance of a SqlDataReader object using Connection.Provides read-only forward access to rows returned by

the SqlCommand object.

ExecuteReader() Procedure

1. Call the read() method: which advances the reader to the first row (and then to the next row on subsequent calls), and returns a boolean value indicating whether there was actually a row to read.

2. Access any columns that we need to. If you want to access them by name - probably the most common and readable option - you can use the reader's indexer:

1. Call close() on the DataReader(and the database connection, if need be).

Example:SqlCommand sqlComm = new SqlCommand("SELECT userid, username FROM users ORDER BY username", sqlConn);SqlDataReader r = sqlComm.ExecuteReader();while ( r.Read() ){  string username = (string)r["username"];

int userID = (int)r["userid"];ConsoleWriteLine(username + "(" + userID + ")");

}r.Close();

How to use parameters?

• Commands such as such as the UPDATE, the INSERT, and the DELETE statements or calls to stored procedures against a database are frequently parameterized.

• This allows the command to be created one time but executed multiple times with different values that are inserted instead of parameters.

• Consider the corresponding DELETE statement to the INSERT statement that is used in the previous section:

Example: string sSQL = "DELETE FROM Employee WHERE emp_id

= @emp_id“The parameter name ("@emp_id") in this DELETE statement

represents a parameter than you can replace with different values each time you run the command.

Using Parameters(contd…)

• To use parameters with your command, follow these steps:• Create your SqlConnection object.• Replace values with placeholders (for example, "@emp_id" or

"@fname") so that your command text uses parameters.• Create your SqlCommand object, and pass in the connection

object that you created in the first step and the command text that contains the parameter placeholders.

• For each parameter, add a parameter object to the command object's parameters collection. For each parameter, you must specify a name and data type:

objCmd.Parameters.Add("@emp_id", SqlDbType.Char, 9);

•  You must also set a value for each input parameter before you can run the query:

objCmd.Parameters["@emp_id"].Value = "MSD12923F";

• Run the query as follows:

objCmd.ExecuteNonQuery();

System.Data.SqlClient Namespace

.NET Framework Data Provider for SQL Server.

The .NET Framework Data Provider for SQL Server describes a collection of classes used to access a SQL Server database in the managed space.

Using the SqlDataAdapter, you can fill a memory-resident DataSet, which you can use to query and update the database.Main classes:SqlDataAdapter SqlDataReader SqlCommand SqlConnection

HOW TO CONNECT DATABASE?

To create your database:

•FILE > NEW > PROJECT>select windows form•From the menu bars at the top of C# .NET, click Project. From the Project menu, select Add New Item:

• When you click on Add New Item, you should see the following dialogue box appear.•Select Service-based database (or SQL Database for older versions).•Give your database a name. Call it MyWorkers.mdf. We'll create a database of fictitious people who work for us, and give them job descriptions.•Click the Add button.

•In C# 2010 you're then asked to choose a Database model. Leave it on the default of Dataset and click Next. In earlier versions of C#, you'll see the following

•Select Tables, and then Finish. (The Dataset is important, and you'll see how they work later).In C# 2010, you'll see this:

Just click Finish to return to the C# design environment.

• However, your database is empty at the moment. We need to add a table to it.• So right click on MyWorkers.mdf. From the menu that appears, click Open.• You should see the Database Explorer appear:

Right click on Tables to see the following menu:

Select Add New Table from the menu, and a new table will appear in your main window:

•Click File > Save All to save your work. You will be prompted to enter a name for your new table. Call it tblWorkers:

•Click OK and you'll be returned to the main screen and the Database Explorer.•Expand the Tables section and you should see your new columns appear:

QUERIES

DATA READER:

1.Create connection•Close down the project you have open, and click File > New Project to create a new one.

•To find the MDF database you created in the previous section, look in the Projects folder of Visual Studio 2010. Double click the name you gave to your project and you should see the MyWorkers.mdf file.•Copy that mdf database anywhere \Copy it somewhere like the root of C:\

How to Connect to a SQL Server Express Database

•To connect to a database using SQL Server Express, you first need to set up a SQL Connection object.

•Now that we have a connection object, we can access the ConnectionString property. •To see what the string should be, click the Data menu item at the top of the C# .NET software. •Then select Show Data Sources. •This will display a new tab where the Solution Explore is:

•Click Add New Data Source and you'll see a Wizard appear. •On the first screen, make sure Database is selected and then click the Next button get to the Choose your Data Connection step. •Click the New Connection button, and you'll see the following:

•The Data Source area has a Change button. Click this to see the following:

Select Microsoft SQL Server Database File (SqlClient). Then click OK

•Click the Browse button and browse to the location where you saved your database. •The Add Connection box will then look something like this:

•Click the Test Connection button to see if everything is working. •Then click OK to get back to the Choose your Data Connection step. •Expand the Connection Stringarea, and the dialogue box should look like this:

Code snippet:

1.Creating connection:

SqlConnection con=new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\dell\\Documents\\Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

2. Insert command=new SqlCommand("SET IDENTITY_INSERT studentrecord ON insert into studentrecord(student_name,student_rollno) values('" + textBox1.Text + "','" + textBox2.Text + "')SET IDENTITY_INSERT studentrecord OFF ", con);

command.Parameters.Clear(); command.Parameters.AddWithValue("@student_name", textBox1.Text); command.Parameters.AddWithValue("@student_rollno", textBox2.Text); try { con.Open();

int result = command.ExecuteNonQuery();

if (result > 0) MessageBox.Show("record inserted."); else MessageBox.Show("Failed to add record."); } catch (SqlException ex) { MessageBox.Show(ex.Message); } finally { con.Close(); }

3.Updatecommand = new SqlCommand("Update studentrecord SET student_name = '" + textBox1.Text + "' WHERE student_rollno = '" + textBox2.Text + "'", con);

command.Parameters.Clear(); command.Parameters.AddWithValue("@student_name", textBox1.Text); command.Parameters.AddWithValue("@student_rollno", textBox2.Text); try { con.Open();

int result = command.ExecuteNonQuery();

if (result > 0) MessageBox.Show("record updated."); else MessageBox.Show("Failed to update record."); } catch (SqlException ex) { MessageBox.Show(ex.Message); } finally { con.Close(); }

4.Deletecommand=new SqlCommand("Delete studentrecord WHERE student_rollno=' "+textBox2.Text+" ' ",con);

command.Parameters.Clear(); command.Parameters.AddWithValue("@student_rollno", textBox2.Text); try { con.Open();

int result = command.ExecuteNonQuery();

if (result > 0) MessageBox.Show("record deleted."); else MessageBox.Show("Failed to delete record."); } catch (SqlException ex) { MessageBox.Show(ex.Message); } finally { con.Close(); }

5.Grid view

SqlDataAdapter da1 = new SqlDataAdapter("Select * from studentrecord", con);

DataSet ds1 = new DataSet(); da1.Fill(ds1); dataGridView1.DataSource = ds1.Tables[0]

Data Adapter

SqlDataAdapter da1 = new SqlDataAdapter("Select * from tblEmployee", con); DataSet ds1 = new DataSet(); da1.Fill(ds1);

LINQ TO SQL

What was the Reason for LINQ To SQL ?Most programs written today manipulate data in one way or another and often this data is stored in a relational database.

Yet there is a huge difference between modern programming languages and databases in how they represent and manipulate information.

Modern programming languages define information in the form of objects. Relational databases use rows. Objects have unique identity as each instance is physically different from another. Rows are identified by primary key values.

Solution: LINQ to SQL?

LINQ to SQL is an O/RM (Object Relational Mapping) implementation that ships in the .NET Framework "Orcas" release.

Allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.

Supports transactions, views, and stored procedures.

Provides an easy way to integrate data validation and business logic rules into your data model.

LINQ To SQL (Cont..)

LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query.

Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.

LINQ to SQL is a powerful tool which allows developers to access databases as objects in C#

Getting Started:

We will be creating a Windows Forms Application that allows as to you to query and view records from a particular table using LINQ to SQL classes

Creating LINQ to SQL Classes

• Create a new Windows Forms Application and name it LinqToSqlDemo. (FILE > NEW > PROJECT)• In solution explorer right click on lintosqldemo > add new item > linq to sql classes(name it as student.dbml)

CREATE A CONNECTION

Create a connection to the studentrecord database:• Choose the Server Explorer from the View menu (Ctrl + W, L). In Express editions of Visual Studio, this tool is called the Database Explorer.

•Right click on the Data Connections node shown in Figure 3 and choose Add Connection.

Cont..

•In the Add Connection dialog select the Browse button and navigate to and select your copy of database1.mdf.•Select the OK button.At this stage database1.mdf should appear in your server or database explorer, as shown in Figure on the next slide.

Select the Browse button in the Add Connection dialog and locate your copy of database1.mdf

The Server Explorer provides a view of the let say Northwind database.

ADDING DATA SOURCE

After you create entity classes by using the O/R Designer, you can create an object data source and populate the Data Sources Window with the entity classes.

To create an object data source based on LINQ to SQL entity classes

On the Build menu, click Build Solution to build your project.

On the Data menu, click Show Data Sources.

In the Data Sources window, click Add New Data Source.Click Object on the Choose a Data Source Type page and then click Next.

Note: If the Customer class is not available, cancel out of the wizard, build the project, and run the wizard again.

Click Finish to create the data source and add the Customer entity class to the Data Sources window.

Drag items from the Data Sources window onto a form.

Using the SQL Designer

The LINQ to SQL Designer allows you to configure and view the metadata of the tables from the database that you want to query. 

SQL Designer(contd…)

Drag the Customer table from the Server Explorer onto the SQL designer.

Several things happened as you completed the steps outlined above:

When you added the SQL Designer to your project, a new node in the Solution Explorer called DataClasses1.dbml was added to your project. As shown in Figure, it contains two files, called DataClasses1.dbml.layout and DataClasses1.designer.cs.

When you dragged the student table onto the designer, an object relational mapping (ORM) was created between the student table in the database and a studentclass generated by the SQL Designer and placed in DataClasses1.designer.cs. This object is called an entity class and it allows you to access the data and fields of the student tables as if they were an object in your project.

A second class, referred to as a DataContext, was also created in DataClasses1.designer.cs. This class can be used to automatically connect to the database and to easily access the data and fields in the Customer table.

A file called app.config was added to your project. It contains an automatically generated connection string for your database.

Data Context

From the developer’s perspective, the architecture for LINQ to SQL is quite simple. Working primarily with a single class called the DataContext, LINQ developers can:1.Connect to a database2.Access data3.Submit changes back to the server

DataContext

After you add a LINQ to SQL file to your project, the empty design surface represents a DataContext ready to be configured.

Drag database items from Server Explorer/Database Explorer onto the O/R Designer to create data classes and DataContext methods.

The data connection of a  DataContext is created based on the first item added to the designer form Server Explorer/Database Explorer.

DataContext Class

The DataContext is the main conduit by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection.The 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. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select.

Code snippet

Table<studentrecord> stud = dc.GetTable<studentrecord>(); studentrecord s1 = new studentrecord();

s1.student_rollno = Convert.ToInt16(textBox2.Text); s1.student_name = textBox1.Text; stud.InsertOnSubmit(s1); stud.Context.SubmitChanges();

INSERT

UPDATE

var st = (from s in dc.GetTable<studentrecord>() where s.student_rollno == Convert.ToInt16(textBox2.Text) select s).SingleOrDefault();

if (st == null) { System.Data.Linq.Table<studentrecord> stud = dc.GetTable<studentrecord>(); studentrecord objStud = new studentrecord(); objStud.student_rollno = Convert.ToInt16(textBox2.Text); objStud.student_name= textBox1.Text; stud.InsertOnSubmit(objStud); dc.SubmitChanges(); } else { st.student_rollno = Convert.ToInt16(textBox2.Text); st.student_name= textBox1.Text;

dc.SubmitChanges();

}

DELETE

var st = (from s in dc.GetTable<studentrecord>() where s.student_rollno == Convert.ToInt16(textBox2.Text) select s).SingleOrDefault();

dc.studentrecords.DeleteOnSubmit(st); dc.SubmitChanges();

GRID VIEW

Table<studentrecord> stud = dc.GetTable<studentrecord>();dataGridView1.DataSource = dc.GetTable<studentrecord>();