ADO.NET Fundamentals

Overview

ADO.NET is the technology that .NET applications use to interact with a database. Examples of data at work in a web application include: E-commerce, Search engines, Knowledge bases, and Media sites.

You can use ADO.NET via SqlClient namespace classes, SqlDataSource controls, or LINQ to Entities (a higher-level model) to interact with your database.?In this tutorial, I'll cover how to use ADO.NET to connect to a database, retrieve (SELECT) information, how to store it in a DataSet, and how to apply changes using direct commands to UPDATE, INSERT, and DELETE data.?

LINQ is a higher-level model. That means it actually uses the ADO.NET classes to do its dirty work.?Although LINQ is a powerful and practical way to generate a data model for your database, it may be overkill for your application, it may be unnecessarily complex, or it may not give you all the control you need (for example, if you want to perform unusual data tasks or implement elaborate performance-optimizing techniques). For these reasons, every ASP.NET developer should start by learning the ADO.NET fundamentals.

Contents

• Quick Reference???
• Examples???
• Summary
• Tips
• Server Controls often used with ADO.NET

Tutorial

• Understanding Databases
• Creating a Connection
• DataReader?
• Adding a Record & Parameterized Commands
• using keyword
• Disconnected Data Access
• Selecting Multiple Tables (using SqlDataAdapter/DataSet)
• LINQ to Entities
• Reference

Quick Reference

Direct Data Access via: System.Data.SqlClient ? SqlConnection ? SqlCommand ? SqlDataReader??

Disconnected Data Access via: System.Data ? SqlDataAdapter ? DataSet (DataTables & DataRelations) ? DataTables (DataRows, DataColumns, Contraints, DefaultView)?

System.Data.SqlClient?

?SqlConnection
? ? .Open()?
? ? .Close()?
? ? .ServerVersion?
? ? .State?

?SqlCommand
? ? .ExecuteReader()
? ? .ExecuteNonQuery() // returns # of affected rows
? ? .Parameters.AddWithValue()

?SqlDataReader
? ? .Read() // while looping through records?
? ? .Close()?

?
System.Data?

?DataSet // container of Tables & Relations?
? ? .Tables["DataTableName"]
? ? ? ? .Columns["ColumnName"]
? ? ? ? .Rows?
? ? .Relations.Add()?
?
?DataTable // container of DataRow & DataColumn objects?

?DataRow // access via objDataRow["ColumnName"]
? ? .GetChildRows()?

?SqlDataAdapter // pass it SqlCommand object(s)
? ? .Fill() // populates DataSet object?

?DataRelation


System.Web.Configuration.WebConfigurationManager
? ? .ConnectionStrings["Integrated"].ConnectionString?
?

System.Data.SqlTypes // SQL Server data types that do not need to be converted 
?SqlDateTime
?SqlDecimal?
?SqlMoney
         

Examples

• SqlConnection (Open & Close)?
• SqlCommand (Create, Retrieve, Update, Delete)
• SqlDataReader (Retrieve List)?
• SqlDataAdapter?(Retrieve List)?
• DataSet (Retrieve List)?
• DataRelation (Table Linking)?
• using Keyword?

SqlConnection

try
{
    using (myConnection = new SqlConnection(connectionString)) // using removes need for finally block & .Close() by implicitly calling .Dispose()
    {? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? // using provides better performance because connection is closed before exception handling
        myConnection.Open();
        lblInfo.Text = "<b>Server Version:</b> " + myConnection.ServerVersion;
        lblInfo.Text += "<br /><b>Connection Is:</b> " + myConnection.State.ToString();
    }
}
catch (Exception err)?
{?
    lblInfo.Text = "Error reading the database: " + err.Message;
    lblInfo.Text += err.Message;
}        

Note: If we didn't use the using keyword (which automatically calls the Dispose method), then you need to have a 'finally' block after the 'catch' block to close the connection:?

finally?
{
    // Either way, make sur ethe connection is properly closed.
    // Even in the connection wasn't opened successfully, calling Close() won't cause an error.
    myConnection.Close();
}        

ConnectionString

• You can use SQL Authentication (User ID & Password) or Windows Integrated Authentication.
• You can retrieve the Connection String value via:?

// "Integrated" is the Name of the Connection String in this example 
string connectionString = WebConfigurationManager.ConnectionStrings["Integrated"].ConnectionString;        

web.config:

<?xml version="1.0"?>
<configuration>
? <connectionStrings>

? ? <add name="SqlAuth"
? ? ? ? ?connectionString="Data Source=HARRISR\SQL2014;Initial Catalog=Pubs;User ID=sa;Password=12345678"
? ? ? ? ?providerName="System.Data.SqlClient" />	

? ? <add name="Integrated"
? ? ? ? ?connectionString="Data Source=HARRISR\SQL2014;Initial Catalog=Pubs;Integrated Security=SSPI"
? ? ? ? ?providerName="System.Data.SqlClient" />??
		?
? </connectionStrings>
	
? ...

</configuration>        

SqlCommand

To use a Command, you define it, specify the SQL statement, specify an available Connection, and Execute the Command.

Tip: Do NOT use pasted-together SQL string.?Instead use Parameters to sanatize User input.?

Retrieving Single Record (SqlCommand.ExecuteReader):?

In this example, we select an Author record by last name using a DropDownList control (renders as <select> HTML tag).?The full record is then retrieved and displayed.

if (lstAuthor.SelectedItem.Value == "0") // "Select Author"
    return;


// Tip: Do NOT use pasted-together SQL string.??
// Instead use Parameters to sanatize User input.?
string selectSQL;
selectSQL = "SELECT * FROM Authors ";
selectSQL += "WHERE au_id='" + lstAuthor.SelectedItem.Value + "'";??

// Define ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;


// Try to open database and read information.
try
{
    con.Open();
    reader = cmd.ExecuteReader();
    reader.Read();


    // Fill the controls.? ? ? ? ? ?
    txtID.Text = reader["au_id"].ToString();
    txtFirstName.Text = reader["au_fname"].ToString();
    txtLastName.Text = reader["au_lname"].ToString();
    txtPhone.Text = reader["phone"].ToString();
    txtAddress.Text = reader["address"].ToString();
    txtCity.Text = reader["city"].ToString();
    txtState.Text = reader["state"].ToString();
    txtZip.Text = reader["zip"].ToString();
    chkContract.Checked = (bool)reader["contract"];
 
    lblResults.Text = "";
   
    reader.Close();
}
catch (Exception err)
{
    lblResults.Text = "Error getting author. ";
    lblResults.Text += err.Message;
}
finally
{
    // If you are using a class that implements the IDisposable interface,?
    // you can call its Dispose when you are finished using the class?
    // https://msdn.microsoft.com/en-us/library/system.idisposable.dispose.aspx)
    
    // Tip: Instead utilize using keyword
    // cmd.Dispose();?
    
    con.Close();
}


cmdInsert.Enabled = false;?        

Create a Record (SqlCommand.ExecuteNonQuery):

// Tip: Do NOT use pasted-together SQL string.? 
// Instead use parameters to sanatize User input.?
string insertSQL;
insertSQL = "INSERT INTO Authors (";
insertSQL += "au_id, au_fname, au_lname, ";
insertSQL += "phone, address, city, state, zip, contract) ";
insertSQL += "VALUES (";
insertSQL += "@au_id, @au_fname, @au_lname, ";
insertSQL += "@phone, @address, @city, @state, @zip, @contract)";


// TO DO: Change to 'using'
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(insertSQL, con);? ? ? ? ? ? ? ??


// Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_Lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", chkContract.Checked);


// Try to open the database and execute the update.
int added = 0;? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
try
{
    con.Open();
    added = cmd.ExecuteNonQuery(); // (# rows affected returned)? ?
    lblResults.Text = added.ToString() + " record inserted.";? ? ? ? ? ? ??
}
catch (Exception err)
{
    lblResults.Text = "Error inserting record. ";?
    lblResults.Text += err.Message;?
}
finally
{
    con.Close();
}


// If the insert succeeded, refresh the author list.
if (added > 0)
{
    FillAuthorList();

    lstAuthor.SelectedValue = txtID.Text;?
}
        

Update a Record (SqlCommand.ExecuteNonQuery):?

string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city, state=@state, ";
updateSQL += "zip=@zip, contract=@contract ";
updateSQL += "WHERE au_id=@au_id_original";? ? ? ? ??


SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);


// Add the parameters.? ? ? ??
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_Lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", chkContract.Checked);
cmd.Parameters.AddWithValue("@au_id_original", lstAuthor.SelectedItem.Value);?


int updated = 0;
try
{
    con.Open();
    updated = cmd.ExecuteNonQuery();? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
    lblResults.Text = updated.ToString() + " record updated.";
}
catch (Exception err)
{
    lblResults.Text = "Error updating author. ";
    lblResults.Text += err.Message;?
}
finally
{
    con.Close();
}


// If the updated succeeded, refresh drop down list
if (updated > 0)
{
    FillAuthorList();

    lstAuthor.SelectedValue = txtID.Text;
}        

Delete a Record (SqlCommand.ExecuteNonQuery):

string deleteSQL;
deleteSQL = "DELETE FROM Authors ";
deleteSQL += "WHERE au_id=@au_id";? ??


SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(deleteSQL, con);
cmd.Parameters.AddWithValue("@au_id ", lstAuthor.SelectedItem.Value);? ?


int deleted = 0;
try
{
    con.Open();
    deleted = cmd.ExecuteNonQuery();? ?
    lblResults.Text = "Record deleted.";
}
catch (Exception err)
{
    lblResults.Text = "Error deleting author. ";
    lblResults.Text += err.Message;?
}
finally
{
    con.Close();
}


// If the delete succeeded, refresh the author list.
if (deleted > 0)
{
    FillAuthorList();

    ClearFields(); // Customization Added
}        

SqlDataReader

Retrieve Multiple Records (List):

SqlConnection con;?
SqlCommand cmd;??
SqlDataReader reader;?


// Alternative (if we weren't utilizing the using keyword for the SqlConnection & SqlCommand)
// SqlConnection con = new SqlConnection(connectionString);? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
// SqlCommand cmd = new SqlCommand("SELECT au_lname, au_fname, au_id FROM Authors", con);??


// Alternative:
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = con;
// cmd.CommandText = "SELECT au_lname, au_fname, au_id FROM Authors"; 	


try // Try to open database and read information.
{
    using (con = new SqlConnection(connectionString))
    {
        con.Open();


        using (cmd = new SqlCommand("SELECT au_lname, au_fname, au_id FROM Authors", con))
        using (reader = cmd.ExecuteReader()) // The Command creates the DataReader
        {
            while (reader.Read()) // retrieve a single row
            {
                // Filling the DropDownList
                // For each item, add the author name to the displayed
                // list box text, and store the unique ID in the Value property.
                ListItem newItem = new ListItem();
                newItem.Text = reader["au_lname"] + ", " + reader["au_fname"];? ??
                newItem.Value = reader["au_id"].ToString();

                lstAuthor.Items.Add(newItem);
            }
        }
        // reader.Close();? // unnessary, switched to using??

        lstAuthor.Items.Insert(0, new ListItem("Select Author", "0")); // *
    }
}
catch (Exception err)
{
    lblResults.Text = "Error reading list of names. ";
    lblResults.Text += err.Message;
}
        

Tip: For such a simple task, you could use a SqlDataSource, then Data Bind to a DropDownList control via its DataSourceID property. Note, Data source controls requery the database on every postback that occurs. This is a great performance hit. It is possible to enable caching on SqlDataSource control. But the limitation is it only works if the DataSourceMode property is set to DataSet (default). Caching results with DataReader mode ?is not effective given a DataReader uses a live connection to the database.

List Controls:

List Controls are typically used to make a selection and often get populated during the Page.Load event. The information is usually only retrieved once, the first time the page is loaded (Page.IsPostBack == false), because the values (DropDownList ListItem objects) persist in the ViewState.

Use the AutoPostBack property if you are using a DropDownList to make a selection and subsequently display a record based on the selection.?

<asp:dropdownlist id="lstAuthor" runat="server" AutoPostBack="True"
onselectedindexchanged="lstAuthor_SelectedIndexChanged">
</asp:dropdownlist>?        

SqlDataSource:

A simple alternative to the above examples (e.g. C.R.U.D.) that requires no C# database code would be to use 2 SqlDataSource controls with a DropDownList (w/ DataSourceID) and a DetailsView control. The 1st SqlDataSource would populate the DrowDownList & the 2nd SqlDataSource would have UpdateCommand & DeleteCommand. Refer to Data Binding examples.?

<asp:SqlDataSource ID="sourceOrders" runat="server"
    ProviderName="System.Data.SqlClient"
    ConnectionString="<%$ ConnectionStrings:Northwind %>"
    SelectCommand="SELECT * FROM Authors WHERE au_id=@AuthorID"
    OnSelecting="sourceOrders_Selecting">
    <SelectParameters>
        <asp:ControlParameter Name="AuthorID" ControlID="lstAuthor" PropertyName="SelectedValue" />
    </SelectParameters>
</asp:SqlDataSource>        

A common hybrid approach would be to use a SqlDataSource control to populate your List and then SqlClient code to perform your C.R.U.D. operations.

Note: Data source controls re-query the database on every postback that occurs, which is a considerable performance hit. It is possible to enable caching on SqlDataSource control. But the limitation is it only works if the DataSourceMode property is set to DataSet (default). Caching results with DataReader mode is not effective as data reader uses a connected architecture i.e. live connection to the database.

SqlDataAdapter

Retrieve Multiple records (List) via SqlDataAdapter + DataSet:

This example demonstrates a basic usage of a SqlDataAdapter and DataSet.?It does a foreach to the rows of the DataSet's DataTable and adds to the DropDownList.?

// Define ADO.NET objects.?
string selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";


SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dsPubs = new DataSet();??


// Try to open database and read information.?
try
{
    con.Open();


    // All the information in transferred with one command.?
    // This command creates a new DataTable (named Authors)?
    // inside the DataSet.?
    adapter.Fill(dsPubs, "Authors");??
}
catch (Exception err)
{
    lblResults.Text = "Error reading list of names. ";
    lblResults.Text += err.Message;
}
finally
{
    con.Close();
}


foreach (DataRow row in dsPubs.Tables["Authors"].Rows)? ?
{
    ListItem newItem = new ListItem();

    newItem.Text = row["au_lname"] + ", " + row["au_fname"];
    newItem.Value = row["au_id"].ToString();? ??
    lstAuthor.Items.Add(newItem);
}??
        

DataSet

Retrieve multiple records (List) from multiple tables via SqlDataAdapter + DataSet:

In this example we'll fill a DataSet with data from 3 tables (i.e. store multiple DataTables in a single DataSet) - Authors, TitleAuthor, Titles.

Classes used in the snippet below: SqlConnection, SqlCommand, SqlDataAdapter, DataSet.

string selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dsPubs = new DataSet();??


try
{
    con.Open();
    adapter.Fill(dsPubs, "Authors");?


    // This command is still linked to the data adapter, re-use Command object
    cmd.CommandText = "SELECT au_id, title_id FROM TitleAuthor";?
    adapter.Fill(dsPubs, "TitleAuthor");? ?


    // This command is still linked to the data adapter? ? ? ? ? ? ?
    cmd.CommandText = "SELECT title_id, title FROM Titles";
    adapter.Fill(dsPubs, "Titles");
}
catch (Exception err)
{
    lblList.Text = "Error reading list of names. ";
    lblList.Text += err.Message;
}
finally
{
    con.Close();
}        

DataRelation

In this example we'll add to the DataSet code found above, and use DataRelation objects to link the 3 DataTables.?

Classes used in the snippet below: DataSet, DataTable, DataRelation, DataRow.

Table Linking:

// The order of the linked fieldsd is important.? The first field is the parent, second is the child
DataRelation Titles_TitleAuthor = new DataRelation("Titles_TitleAuthor",? ? // *
	dsPubs.Tables["Titles"].Columns["title_id"],
	dsPubs.Tables["TitleAuthor"].Columns["title_id"]);


DataRelation Authors_TitleAuthor = new DataRelation("Authors_TitleAuthor",??
	dsPubs.Tables["Authors"].Columns["au_id"],
	dsPubs.Tables["TitleAuthor"].Columns["au_id"]);


dsPubs.Relations.Add(Titles_TitleAuthor);? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
dsPubs.Relations.Add(Authors_TitleAuthor);


// Loop each Author table record
// Use the DataRelation objects to branch to the other linked tables
foreach (DataRow rowAuthor in dsPubs.Tables["Authors"].Rows)? ? ? ? ? ? ? ? // *
{? ? ? ? ? ??
	// 1) Select the first record from the Author table
	lblList.Text += "<br /><b>" + rowAuthor["au_fname"];
	lblList.Text += " " + rowAuthor["au_lname"] + "</b><br />";
				
	// Get & Loop Rows from TitleAuthor table for the current author
	// 2) Find the child records by calling the DataRow.GetChildRows() method,? ? ? ? ? ? ? ? ?
	// and pass in the appropriate DataRelationship?
	foreach (DataRow rowTitleAuthor in? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? // *
		rowAuthor.GetChildRows(Authors_TitleAuthor))? ? ? ? ? ? ? ? ? ??
	{
		DataRow rowTitle =? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? // *
			rowTitleAuthor.GetParentRows(Titles_TitleAuthor)[0];? // 3) Get the linked Title record
		lblList.Text += "&nbsp;&nbsp;";
		lblList.Text += rowTitle["title"] + "<br />";
	} // 4) Move to the next record
}


// NOTE: If authors and titles have a simple one-to-many relationship, we could have used simpler code
// foreach (DataRow rowAuthor in dsPubs.Tables["Authors"].Rows)?
// {?
//? ? // Display author.?
//? ? foreach (DataRow rowTitle in rowAuthor.GetChildRows(Authors_Titles))?
//? ? {?
//? ? ? ? // Display title.?
//? ? }?
// } 					
        

using Keyword

// Saves you from having 'finally' blocks and calling Close() on ADO.NET objects?
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sqlStatement, connection))
{
? ? connection.Open();


? ? using (SqlDataReader reader = command.ExecuteReader())
? ? {
? ? ? ? while (reader.Read())
? ? ? ? {
? ? ? ? ? ? // reader[0]
? ? ? ? }
? ? }
}        

More Examples

• C# SqlClient Tutorial - Dot Net Perls
• C# SqlConnection Example: Using, SqlCommand - Dot Net Perls

Summary

ADO.NET Namespaces for SQL Server Data Access

• System.Data.SqlClient - Contains the classes SqlConnection, SqlCommand, and SqlDataReader which are used to connect to a database and execute commands.
• System.Data - Facilitates disconnected data access via data container classes that are generic / independent of the type of database you're connecting to. Classes that are used to contain and manage data include: DataSet, DataTable, DataRow, and DataRelation.
• System.Data.SqlTypes - Structures for SQL Server-specific data types like SqlMoney.

Direct Data Access via System.Data.SqlClient Namespace (used to connect to a specific data source:

• SqlConnection
• SqlCommand
• SqlDataReader

Disconnected Data Access via System.Data Namespace (used to contain and manage data):

• DataSet (Tables, Relations)
• SqlDataAdapter
• DataRelation
• DataTable (Rows, Columns, Contraints, DefaultView)
• DataRow

SqlCommand for Selecting data

To query information with simple data access, follow these steps:

1. Create SqlConnection, SqlCommand, and SqlDataReader objects. To open the connection, use the Open() method of the connection object. To create a SqlDataReader, you use the ExecuteReader() method of the command object.
2. Use the SqlDataReader to retrieve information from the database (via Read method), and display it in a control on a web form (use SqlDataReader.Read() with while loop to go through records).
3. Close your connection (if you have a using block, the Dispose method of the SqlCommand will be called and it will close the connection).
4. Send the page to the user. At this point, the information your user sees and the information in the database no longer have any connection, and all the ADO.NET objects have been destroyed.

SqlCommand for Updating, Inserting, or Deleting data

If you understand how to retrieve data (SqlCommand's ExecuteReader method), it isn’t much more complicated to perform simple insert, update, and delete operations. Once again, you use the Command object, but this time you don’t need a DataReader because no results will be retrieved, instead you'd execute your command with the ExecuteNonQuery method which returns the number of rows that were affected.

To add or update information (Update, Insert, or Delete statement), follow these steps:

1. Create new SqlConnection and SqlCommand objects.
2. Execute the SqlCommand (with the appropriate SQL statement) by calling the ExecuteNonQuery() method, which returns the number of rows that were affected. Note, typically you'll use SqlCommand with SqlCommand.Parameters.AddWithValue().

SqlDataAdapter & Dataset

To use a SqlDataAdapter:

1. Pass a SqlCommand object to a SqlDataAdapter's constructor.
2. Create a DataSet object and use SqlDataAdapter.Fill() to populate it.
3. Use DataRow to extract the records (foreach DataRow r in DataTable). If using multiple tables, you could fill the DataSet with multiple DataTable objects, and define DataRelation objects to better join data from the tables (join through ADO.NET instead of SQL join query).

In a Nutshell

Because of the DataReader’s optimized nature, it provides better performance than the DataSet. It should always be your first choice for direct data access.?

A DataSet tracks the changes you make to the records inside. This allows you to use the DataSet to update records. The basic principle is simple. You fill a DataSet in the normal way, modify one or more records, and then apply your update using a DataAdapter. However, ADO.NET’s disconnected update feature makes far more sense in a desktop application than in a web application. Desktop applications run for a long time, so they can efficiently store a batch of changes and perform them all at once. But in a web application, you need to commit your changes the moment they happen.

The great majority of ASP.NET web applications use the DataSet to store data but not to make updates. Instead, they use direct commands to commit changes.?

ADO.NET’s disconnected update feature makes far more sense in a desktop application than in a web application. Desktop applications run for a long time, so they can efficiently store a batch of changes and perform them all at once. But in a web application, you need to commit your changes the moment they happen. Furthermore, the point at which you retrieve the data (when a page is first requested) and the point at which it’s changed (during a postback) are different, which makes it very difficult to use the same DataSet object, and maintain the change tracking information for the whole process.?

LINQ to Entities features is a higher-level model. That means it actually uses the ADO.NET classes to do its dirty work. Although LINQ to Entities is a powerful and practical way to generate a data model for your database, it may be overkill for your application, it may be unnecessarily complex, or it may not give you all the control you?need (for example, if you want to perform unusual data tasks or implement elaborate performance-optimizing techniques). For these reasons, every ASP.NET developer should start by learning the ADO.NET fundamentals.

Available structures for SQL Server-specific data types via System.Data.SqlTypes namespace. You can use these types to work with SQL Server data types without needing to convert them into the standard .NET equivalents (such as System.Decimal and System.DateTime).

Tips

• Once you use the Open() method, you have a live connection to your database. One of the most fundamental principles of data access code is that you should reduce the amount of time you hold a connection open as much as possible. Imagine that as soon as you open the connection, you have a live, ticking time bomb. You need to get in, retrieve your data, and throw the connection away as quickly as possible in order to ensure your site runs efficiently.
• The DataReader uses a live connection and should be used quickly and then closed.
• When using SqlClient classes, use the using keyword to help manage resources. Specifically use it for SqlConnection, SqlCommand, SqlDataReader, to save you from having to call .Close (Connection/Command) or .Dispose (Command). It also saves you from needing a Finally block in a Try-Catch statement to call the Close method on SqllClient object(s).
• Many of the most infamous attacks on e-commerce websites weren’t fuelled by hard-core hacker knowledge but were made using simple SQL injection by modifying values in web pages or query strings.? For basic security, ALWAYS use parameterized commands. This can be done via an SqlCommand object that calls uses the SqlCommand.Parameters.AddWithValue method. This is a much more robust and convenient approach rather than sanitizing your input. Example:

string deleteSQL = "DELETE FROM Authors WHERE au_id=@au_id";

...

cmd.Parameters.AddWithValue("@au_id ", lstA.SelectedItem.Value);        

• When deleting records, the best choice is usually not to delete the record at all (after all, you may need it for tracking and reporting later). Instead, use a bit column to keep track of records that shouldn’t be displayed, like a ‘Discontinued’ column in a Products table or an Active column in an Authors table.
• Auto increment fields are indispensable.
• When you query data with direct data access, you don’t keep a copy of the information in memory. Instead, you work with it for a brief period of time while the database connection is open, and then close the connection as soon as possible. This is different than Disconnected Data Access, where you keep a copy of the data in the DataSet object so you can work with it after the database connection has been closed. DataReader holds a live connection (a page typically has a lifetime of less than a few seconds), whereas information in the DataSet is always disconnected.
• To ensure good database performance, you should open your connection just before you execute your command and close it as soon as the command is finished.
• Store your connection string in web.config:

<connectionStrings>

<!-- SQL Authentication (with sa account) -->
<add name="Pubs" connectionString="Data Source=RICHARD-PC\SQLEXPRESS;Initial Catalog=Pubs;User ID=sa;Password=123456789" providerName="System.Data.SqlClient" />

<!-- Windows Integrated Authentication -->
<add name="Integrated" connectionString="Data Source=RICHARD-PC\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

</connectionStrings>
</connectionStrings>        

• Retrieve connection string stored in web.config via:

WebConfigurationManager.ConnectionStrings["DBName"].ConnectionString        

Server Controls often used with ADO.NET

• DropDownList (select)
• Label (display record)
• Text (update/insert record)
• Button (submit - update, delete)
• Repeater, GridView, etc. (display - used with Binding)

Note: To select a record, you'll often use DropDownList w/ AutoPostBack & SelectIndexChanged.

When retrieving data to populate a List Control, rather than writing SqlClient code involving an SqlConnection, SqlCommand, and SqlReader objects, you could just use a SqlDataSource object and Data Binding. Note that Data source controls re-query the database on every postback that occurs, however, the control offers Caching.

<asp:dropdownlist id="lstAuthor" datasourceid="sourceOrders" ...>
	...


<asp:SqlDataSource ID="sourceOrders" runat="server"?
	ProviderName="System.Data.SqlClient"
	ConnectionString="<%$ ConnectionStrings:Northwind %>"
	SelectCommand="SELECT * FROM Authors WHERE au_id=@AuthorID"
	OnSelecting="sourceOrders_Selecting">
	
	<SelectParameters>
		<asp:ControlParameter Name="AuthorID" ControlID="lstAuthor" PropertyName="SelectedValue" />
	</SelectParameters>
</asp:SqlDataSource>        

Understanding Databases

• Examples of data at work in a web application: E-commerce, Search engines, Knowledge bases, Media sites.
• Almost every piece of software ever written works with data. In fact, a typical web application is often just a thin user interface shell on top of sophisticated data-driven code that reads and writes information from a database.
• Relational model is the philosophy that underlies all modern database products, including SQL Server, Oracle, and even Microsoft Access.
• A relational model breaks information down to its smallest and most concise units.
• RDBMS - Relational Database Management System (i.e. SQL Server)

Creating a Connection

• Generally, connections are limited to some fixed number, and if you exceed that number (either because you run out of licenses or because your database server can’t accommodate the user load), attempts to create new connections will fail. For that reason, you should try to hold a connection open for as short a time as possible. You should also write your database code inside a try/catch error handling structure so you can respond if an error does occur, and make sure you close the connection even if you can’t perform all your work.?
• When creating a Connection object, you need to specify a value for its ConnectionString property. This ConnectionString defines all the information the computer needs to find the data source, log in, and choose an initial database.
• The Connection String: Data source, Initial catlog (database name; "initial" because you can change it later using ChangeDatabase() method), Integrated security (Windows user) or SQL Server authentication (user ID and password; considered not as secure), Connection Timeout (optional; default is 15 seconds).
• Once you use the Open() method, you have a live connection to your database. One of the most fundamental principles of data access code is that you should reduce the amount of time you hold a connection open as much as possible. Imagine that as soon as you open the connection, you have a live, ticking time bomb. You need to get in, retrieve your data, and throw the connection away as quickly as possible in order to ensure your site runs efficiently.

DataReader?

• DataReader allows you to quickly retrieve all your results.?
• The DataReader uses a live connection and should be used quickly and then closed.?
• It supports fast-forward-only read-only access to your results, which is generally all you need when retrieving information.?
• Because of the DataReader’s optimized nature, it provides better performance than the DataSet. It should always be your first choice for direct data access.?
• To create a DataReader, you use the ExecuteReader() method of the command object.

Adding a Record & Parameterized Commands

Dynamically pasted-together SQL string is an off-the-cuff approach is great for quickly coding database logic, and it’s easy to understand. However, it has two potentially serious drawbacks:?

• Users may accidentally enter characters that will affect your SQL statement. For example, if a value contains an apostrophe ('), the pasted-together SQL string will no longer be valid.?
• Users might deliberately enter characters that will affect your SQL statement. Examples include using the single apostrophe to close a value prematurely and then following the value with additional SQL code. The second of these is known as SQL injection attack, and it facilitates an amazingly wide range of exploits. Crafty users can use SQL injection attacks to do anything from returning additional results (such as the orders placed by other customers) or even executing additional SQL statements (such as deleting every record in another table in the same database). In fact, SQL Server includes a special system stored procedure that allows users to execute arbitrary programs on the computer, so this vulnerability can be extremely serious.
• Rather than sanitizing your input, a much more robust and convenient approach is to use a parameterized command. A parameterized command is one that replaces hard-coded values with placeholders. The placeholders are then added separately and automatically encoded.?

Example:

string deleteSQL = "DELETE FROM Authors WHERE au_id=@au_id";
...
cmd.Parameters.AddWithValue("@au_id ", lstAuthor.SelectedItem.Value);?        

using keyword

• The using statement declares that you are using a disposable object for a short period of time. As soon as you finish using that object and the using block ends, the common language runtime will release it immediately by calling the Dispose() method.
• Calling the Dispose() method of a connection object is equivalent to calling Close() and then discarding the connection object from memory. That means you can shorten your database code with the help of a using block. The best part is that you don’t need to write a finally block—the using statement releases the object you’re using even if you exit the block as the result of an unhandled exception.
• Utilize the ‘using’ keyword where you would normally be creating new SqlConnection, or a new SqlCommand or doing SqlCommand.ExecuteReader (creates new SqlDataReader).

Example:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sqlStatement, connection))
{
	connection.Open();


	using (SqlDataReader reader = command.ExecuteReader())
	{
		while (reader.Read())
		{
			// reader[0]
		}
	}
}        

Disconnected Data Access

• DataReader holds a live connection, information in a DataSet is always disconnected.
• DataSet (Table(s), Relations) -> DataTable Object(s) (Rows, Columns, Contraints, DefaultView).
• When you use disconnected data access, you keep a copy of your data in memory using the DataSet. You connect to the database just long enough to fetch your data and dump it into the DataSet, and then you disconnect immediately (i.e. you need to do something time-consuming with the data, you want to use DataBinding, navigate backward or from one table to another, save data as XML for later use). DataReader holds a live connection, information in the DataSet is always disconnected.
• The DataSet tracks the changes you make to the records inside. This allows you to use the DataSet to update records. The basic principle is simple. You fill a DataSet in the normal way, modify one or more records, and then apply your update using a DataAdapter. However, ADO.NET’s disconnected update feature makes far more sense in a desktop application than in a web application. Desktop applications run for a long time, so they can efficiently store a batch of changes and perform them all at once. But in a web application, you need to commit your changes the moment they happen. The great majority of ASP.NET web applications use the DataSet to store data but not to make updates. Instead, they use direct commands to commit changes.
• However, ADO.NET’s disconnected update feature makes far more sense in a desktop application than in a web application. Desktop applications run for a long time, so they can efficiently store a batch of changes and perform them all at once. But in a web application, you need to commit your changes the moment they happen. Furthermore, the point at which you retrieve the data (when a page is first requested) and the point at which it’s changed (during a postback) are different, which makes it very difficult to use the same DataSet object, and maintain the change tracking information for the whole process.?

Selecting Multiple Tables (using SqlDataAdapter / DataSet)

• Many-to-many database relationships utilize an intermediate (bridge) table.
• To link information from different tables together, you could use a special SQL Select statement called a Join query. Alternatively, you can use the features built into ADO.NET, specifically ADO.NET table linking.
• Once your information is in your DataSet, you can create DataRelation objects to make it easier to navigate through the linked information. These DataRelation objects will typically match the foreign key restrictions that are defined in your database.

LINQ to Entities

• Code generation and data modeling tool (alternative way to access a database).
• Although LINQ to Entities is a powerful and practical way to generate a data model for your database, it may be overkill for your application, it may be unnecessarily complex, or it may not give you all the control you need (for example, if you want to perform unusual data tasks or implement elaborate performance-optimizing techniques). For these reasons, every ASP.NET developer should start by learning the ADO.NET fundamentals.
• LINQ to Entities features is a higher-level model. That means it actually uses the ADO.NET classes to do its dirty work.

Reference

• ASP.NET - Learn - Videos - SQL 2005
• Microsoft Docs > ADO.NET
• Retrieving and Modifying Data in ADO.NET
• SqlCommand.Parameters Property
• SqlParameterCollection Class
• Best practices in handling concurrency in ADO.net
• SQL Server Editions

To learn more about SQL, check out my SQL QuickStart Guide.