ADO.NET and LINQ

ASP.NET offers two main options for data access and management:

ADO.NET – This method delivers persistent access to data sources like SQL Server. This proves critical for data-sharing web applications and enterprise applications. ADO.NET separates the manipulation of data and data access, and forms discrete components (i.e., objects) for separate use or tandem use. Its objects hold all operations, and in the display of data, controls interact with them. This secures the details of data transfer. It also offers low-level control of queries. This option is preferred for speed.

LINQ-to-SQL – This method provides a run-time environment for the management of relational data in the form of objects. It translates LINQ queries into SQL. These queries then travel to the database to execute and gather results. LINQ-to-SQL repeats translation by converting results into objects. This method dramatically reduces LOC(lines of code) and complexity.

USING ADO.NET

The first step of employing ADO.NET is creating a link to the database(Visual Basic code). 

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
sub Page_Load
dim dbconn
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("xyzcorp.mdb"))
dbconn.Open()
end sub
</script>

Next, create a dbcomm variable (of the OleDbCommand class) to specify records which will be retrieved from the database. 

<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn,sql,dbcomm
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("xyzcorp.mdb"))
dbconn.Open()
sql="SELECT * FROM partners"
dbcomm=New OleDbCommand(sql,dbconn)
end sub
</script>

Then call the ExecuteReader method (of the OleDbReader class) to create a DataReader for reading record streams. 

%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("xyzcorp.mdb"))
dbconn.Open()
sql="SELECT * FROM partners"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
end sub
</script>

Then bind the DataReader to a Repeater control. 

<%@ Import Namespace="System.Data.OleDb" %>
	<script runat="server">
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
data source=" & server.mappath("xyzcorp.mdb"))
dbconn.Open()
sql="SELECT * FROM partners"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
partners.DataSource=dbread
partners.DataBind()
dbread.Close()
dbconn.Close()
end sub
</script>
<html>
<body>
<form runat="server">
	<asp:Repeater id="partners" runat="server">
		<HeaderTemplate>
			<table border="1" width="100%">
				<tr>
					<th>Organization</th>
					<th>PrimaryContact</th>
					<th>Location</th>
					<th>Nation</th>
				</tr>
		</HeaderTemplate>
		<ItemTemplate>
			<tr>
				<td><%#Container.DataItem("Organization")%></td>
				<td><%#Container.DataItem("PrimaryContact")%></td>
				<td><%#Container.DataItem("Location")%></td>
				<td><%#Container.DataItem("Nation")%></td>
			</tr>
		</ItemTemplate>
		<FooterTemplate>
			</table>
		</FooterTemplate>
	</asp:Repeater>
</form>
</body>
</html>

Finally, close the connection. 

dbread.Close()
dbconn.Close()

USING LINQ

The first step of using LINQ-to-SQL is making a new Data Connection with the SQL server. Navigate to View > Server Explorer > Data Connections > Add Connection. Next, add a LINQ to SQL class file. Select the desired tables from the database. Then simply drag and drop the tables into the LINQ-to- SQL file. Before executing any queries, connect to the data source through the DataContext class: 

new TestDBDataContext("Data Source=.;Initial
Catalog=TestDB;Integrated Security=True"); /*Connection String*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace XyzSys
{
	public class Data
	{
		SqlConnectionStringBuilder connStringBuilder;

		public Data(string ServerNameHere, string DatabaseNameHere)
		{
			connStringBuilder = new SqlConnectionStringBuilder();
			connStringBuilder.DataSource = ServerNameHere;
			connStringBuilder.InitialCatalog = DatabaseNameHere;
			connStringBuilder.IntegratedSecurity = true; //Windows
authentication
		}
		public void Import()
		{
			TestDbDataContext db = new
TestDbDataContext(connStringBuilder.ConnectionString);
		}
	}
}

Queries in LINQ-to-SQL execute immediately or are delayed. Three main components manage the process of query execution:

  1. LINQ-to-SQL API – This component requests the execution of queries as an agent of an application, and then passes to LINQ-to-Provider.
  2. ADO Provider – This component sends the results of query execution as a DataReader to LINQ-to-SQL Provider. The Provider converts the data into a user object.
  3. LINQ-to-SQL Provider – This component converts a query into TSQL (Transact SQL), and then passes the query to the ADO Provider for the purpose of execution.

OPERATING ON THE DATABASE

Review the code below for adding records: 

using System;
using System.Linq;

namespace XyzCorp
{
	class XyzCRUD
	{
		static void Main(string[] args)
		{
			string connectString =
System.Configuration.ConfigurationManager.ConnectionStrings["XyzDbCon
nectionString"].ToString();
			XyzCorpDataContext db = new
XyzCorpDataContext(connectString);

			//Add a partner

			Partner newPartner = new Partner();
			newPartner.Organization = "LMNO Inc.";
			newPartner.Email = "[email protected]";
			newPartner.PhoneNum = "1-555-555-5555";
			newPartner.Location = "Podunktown, USA";

			//Add a partner to the database
			db.Partners.InsertOnSubmit(newPartner);

			//Save
			db.SubmitChanges();

			//Retrieve the new partner
			Partner insertedPartner = db.Partners.FirstOrDefault(e
=>e.Organization.Equals("LMNO Inc."));
			Console.WriteLine("Organization = {0}, Email = {1}, PhoneNum
= {2}, Location = {3}", insertedPartner.Organization,
insertedPartner.Email, insertedPartner.PhoneNum,
insertedPartner.Location;

			Console.WriteLine("\nHit space to proceed.");
			Console.ReadKey();
		}
	}
}

Review the code below for updating records: 

using System;
using System.Linq;
namespace XyzCorp
{
	class XyzCRUD
	{
		static void Main(string[] args)
		{
			string connectString =
System.Configuration.ConfigurationManager.ConnectionStrings["XyzDbCo
nnectionString"].ToString();

			XyzCorpDataContext db = new
XyzCorpDataContext(connectString);

			//Retrieve partner to update
			Partner partner = db.Partners.FirstOrDefault(e
=>e.Organization.Equals("LMNO Inc."));
			employee.Organization = "LMNO & P Inc.";
			employee.Email = "[email protected]";
			employee.PhoneNum = "1-554-555-4555";
			employee.Location = "Podunk City, USA";

			//Save
			db.SubmitChanges();

			//Retrieve the updated record
			Partner updatedPartner = db.Partners.FirstOrDefault(e
=>e.Organization.Equals("LMNO & P Inc."));

			Console.WriteLine("Organization = {0} , Email = {1},
PhoneNum = {2}, Location = {3}",
updatedPartner.Organization, updatedPartner.Email,
updatedPartner.PhoneNum,
updatedPartner.Location);

			Console.WriteLine("\nHit space to proceed.");
			Console.ReadKey();
		}
	}
}

Review the code below for deleting records: 

using System;
using System.Linq;
namespace XyzCorp
{
	class XyzCRUD
	{
		static void Main(string[] args)
		{
			string connectString =
System.Configuration.ConfigurationManager.ConnectionStrings["XyzDbCon
nectionString"].ToString();

			XyzCorpDataContext db = new
XyzCorpDataContext(connectString);

			//Retrieve partner for deletion
			Partner deletePartner = db.Partners.FirstOrDefault(e
=>e.Organization.Equals("LMNO & P Inc."));

			//Delete the partner
			db.Partners.DeleteOnSubmit(deletePartner);

			//Save
			db.SubmitChanges();

			//Retrieve all partners
			var partnersList = db.Partners;
			foreach (Partner partner in partnerList)
			{
				Console.WriteLine("Organization = {0} , Email = {1},
PhoneNum = {2}, Location = {3}",
					partner.Organization, partner.Email,
partner.PhoneNum, partner.Location);
			}

			Console.WriteLine("\nHit space to proceed.");
			Console.ReadKey();
		}
	}
}
« Previous
Next »