ADO.NET
What is ADO.NET?
ADO.NET is a set of classes (a framework) to interact with data sources such as databases and XML files. ADO is the acronym for ActiveX Data Objects. It allows us to connect to underlying data or databases. It has classes and methods to retrieve and manipulate data.
The following are a few of the .NET applications that use ADO.NET to connect to a database, execute commands, and retrieve data from the database.
- ASP.NET Web Applications
- Console Applications
- Windows Applications.
Various Connection Architectures
There are the following two types of connection architectures:
- Connected architecture: the application remains connected with the database throughout the processing.
- Disconnected architecture: the application automatically connects/disconnects during the processing. The application uses temporary data on the application side called a DataSet.
Understanding ADO.NET and it's class library
In this diagram, we can see that there are various types of applications (Web Application, Console Application, Windows Application and so on) that use ADO.NET to connect to databases (SQL Server, Oracle, OleDb, ODBC, XML files and so on).
Important Classes in ADO.NET
We can also observe various classes in the above figure. They are:
- Connection Class
- Command Class
- DataReader Class
- DataAdaptor Class
- DataSet.Class
1. Connection Class
In ADO.NET, we use these connection classes to connect to the database. These connection classes also manage transactions and connection pooling.
2. Command Class
The Command class provides methods for storing and executing SQL statements and Stored Procedures. The following are the various commands that are executed by the Command Class.
- ExecuteReader: Returns data to the client as rows. This would typically be an SQL select statement or a Stored Procedure that contains one or more select statements. This method returns a DataReader object that can be used to fill a DataTable object or used directly for printing reports and so forth.
- ExecuteNonQuery: Executes a command that changes the data in the database, such as an update, delete, or insert statement, or a Stored Procedure that contains one or more of these statements. This method returns an integer that is the number of rows affected by the query.
- ExecuteScalar: This method only returns a single value. This kind of query returns a count of rows or a calculated value.
- ExecuteXMLReader: (SqlClient classes only) Obtains data from an SQL Server 2000 database using an XML stream. Returns an XML Reader object.
3. DataReader Class
The DataReader is used to retrieve data. It is used in conjunction with the Command class to execute an SQL Select statement and then access the returned rows.
4. DataAdapter Class
The DataAdapter is used to connect DataSets to databases. The DataAdapter is most useful when using data-bound controls in Windows Forms, but it can also be used to provide an easy way to manage the connection between your application and the underlying database tables, views and Stored Procedures.
5. DataSet Class
The DataSet is the heart of ADO.NET. The DataSet is essentially a collection of DataTable objects. In turn each object contains a collection of DataColumn and DataRow objects. The DataSet also contains a Relations collection that can be used to define relations among Data Table Objects.
How to Connect to a Database using ADO.NET
Now let us learn how to connect to a database using ADO.NET. To create a connection, you must be familiar with connection strings. A connection string is required as a parameter to SQLConnection. A ConnectionString is a string variable (not case sensitive).
This contains key and value pairs, like provider, server, database, userid and word as in the following:
Server="name of the server or IP Address of the server"
Database="name of the database"
userid="user name who has permission to work with database"
word="the word of userid"
Database="name of the database"
userid="user name who has permission to work with database"
word="the word of userid"
SQL Authentication
String constr="server=.;database=institute;user id=rakesh;word=abc@123";
Or:
String constr="data source=.;initial catalog=institute;uid=rakesh;pwd=abc@213";
Windows Authentication
String constr="server=.;database=institute;trusted_connection=true"
Or:
String constr="server=.;initial catalog=institute;integrated security=true"
String constr="server=.;database=institute;user id=rakesh;word=abc@123";
Or:
String constr="data source=.;initial catalog=institute;uid=rakesh;pwd=abc@213";
Windows Authentication
String constr="server=.;database=institute;trusted_connection=true"
Or:
String constr="server=.;initial catalog=institute;integrated security=true"
How to retrieve and display data from a database
1. Create a SqlConnection object using a connection string.
2. Handle exceptions.
3. Open the connection.
4. Create an SQLCommand. To represent an SQLCommand like (select * from studentdetails) and attach the existing connection to it. Specify the type of SQLCommand (text/storedprocedure).
5. Execute the command (use executereader).
6. Get the Result (use SqlDataReader). This is a forwardonly/readonly dataobject.
7. Close the connection
8. Process the result
9. Display the Result
The following is code for connecting to a SQL Database:
We must use the System.Data.SqlClient namespace to connect to a SQL
Database. In the preceding code, we use the SqlConnection class, SqlCommand
class, and SqlDataReader class because our application is talking to SQL
Server. SQL Server only understands SQL.
Example Program
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConnectDbPractice
{
internal class Program
{
static void Main(string[] args)
{
SqlConnection connect = new SqlConnection("Data Source =.; Initial Catalog = RegistrationDB; Integrated Security = True"); //connection classes to connect to the database
char decision;
do
{
Console.WriteLine("1.Add New");
Console.WriteLine("2.Delete Record");
Console.WriteLine("3.View Records");
Console.WriteLine("4.View By ID");
Console.Write("Enter your choice: ");
int numb = Convert.ToInt32(Console.ReadLine());
decision = 'N';
switch (numb)
{
case 1:
do
{
Console.Write("Enter your ID:");
int id = Convert.ToInt32(Console.ReadLine());
Console.Write("Enter your Name:");
string name = Convert.ToString(Console.ReadLine());
Console.Write("Enter your Age:");
int age = Convert.ToInt32(Console.ReadLine());
Console.Write("Enter your DOB:");
string dob = Convert.ToString(Console.ReadLine());
Console.Write("Enter your location:");
string locate = Convert.ToString(Console.ReadLine());
Console.Write("Are you sure,you want to save(Y/N):");
decision = Convert.ToChar(Console.ReadLine());
if ('Y' == decision || 'y' == decision)
{
connect.Open();
SqlCommand cmd = new SqlCommand(@"Insert into [dbo].[StudentProfile] ([Id] ,[Name] ,[Age] ,[Dob] ,[Location]) Values(@id,@name, @age, @dob, @locate)", connect); //Command class provides methods for storing and executing SQL statements and Stored Procedures
cmd.Parameters.Add("@id", SqlDbType.VarChar);
cmd.Parameters["@id"].Value = id;
cmd.Parameters.Add("@name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = name;
cmd.Parameters.Add("@age", SqlDbType.VarChar);
cmd.Parameters["@age"].Value = age;
cmd.Parameters.Add("@dob", SqlDbType.Date);
cmd.Parameters["@dob"].Value = dob;
cmd.Parameters.Add("@locate", SqlDbType.VarChar);
cmd.Parameters["@locate"].Value = locate;
cmd.ExecuteNonQuery(); // Executes a command that changes the data in the database
connect.Close();
}
} while ('N' == decision || 'n' == decision);
break;
case 2:
do
{
Console.Write("Enter ID number to delete record:");
int id = Convert.ToInt32(Console.ReadLine());
Console.Write("Are you sure,you want to continue(Y/N):");
decision = Convert.ToChar(Console.ReadLine());
if ('Y' == decision || 'y' == decision)
{
connect.Open();
SqlCommand cmd = new SqlCommand(@"DELETE FROM [dbo].[StudentProfile] WHERE Id =@id",connect);
cmd.Parameters.Add("@id",SqlDbType.VarChar);
cmd.Parameters["@id"].Value =id;
cmd.ExecuteNonQuery();
connect.Close();
}
} while ('N' == decision || 'n' == decision);
break;
case 3:
do
{
SqlDataAdapter sda = new SqlDataAdapter("select * from StudentProfile", connect); //DataAdapter is used to connect DataSets to databases.
DataTable dt = new DataTable();
sda.Fill(dt);
Console.WriteLine("Records :-");
Console.WriteLine("---------------------------------------------------------------------------------------");
Console.WriteLine("ID | Name |Age | DOB | Location ");
Console.WriteLine("----------------------------------------------------------------------------------------");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} |{1} |{2} |{3} |{4}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(), item[4].ToString()));
}
Console.Write("Are you sure,you want to continue(Y/N):");
decision = Convert.ToChar(Console.ReadLine());
} while ('N' == decision || 'n' == decision);
break;
case 4:
do
{
Console.Write("Enter the ID to view:");
int id = Convert.ToInt32(Console.ReadLine());
Console.Write("Are you sure,you want to continue(Y/N):");
decision = Convert.ToChar(Console.ReadLine());
Console.WriteLine("---------------------------------------------------");
Console.WriteLine("ID | Name |Age | Location | DOB ");
Console.WriteLine("---------------------------------------------------");
if ('Y' == decision || 'y' == decision)
{
SqlDataAdapter sda = new SqlDataAdapter("select * from StudentProfile where Id="+id, connect);
DataTable dt = new DataTable();
sda.Fill(dt);
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} |{1} |{2} |{3} |{4}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(), item[4].ToString()));
}
}
} while ('N' == decision || 'n' == decision);
break;
default:
Console.WriteLine("Wrong Choice");
break;
}
}
while ('Y' == decision || 'y' == decision);
Console.ReadLine();
}
}
}
Output For the above C# Program
Initially data's Present in Database
StudentProfile(TableName):
Case 1:Inserting new records
Case 3: Viewing All records
Case 4: Viewing single record using Id
Case 2: Deleting a record by Id
Updated StudentProfile Table in database
