First We need to understand the following concepts
- Data Provider
- Connection Object
- Command Object
- DataReader Object
- DataSet Object
- DataAdapter Object
ADO.NET is a object oriented set of libraries that allows to interact with database.
Data Providers:
ADO.NET provides class libraries that allow common way to interact with specific datasources.
Different Types of Providers
- ODBC Data Provider For older databases
- OleDb Data provider Access or excel
- Oracle Data provider For Oracle databases
- Sql Data provider MS Sql Server
- Borland Data provider Geeric Access like Sql Server,DB2 and Oracle.
Primary Objects of ADO.NET
- The SqlConnection Object
- The SqlCommand Object
- The SqlDataReader Object
- The DataSet Object
- The SqlDataAdapter Object
Brief Description of each above
SqlConnection:
To interact with database we must open the connection using datasource, User name,Password
It will opens the connection for required database based on parameters specified.
SqlCommand:
Command object is used to send sql commands using SqlConnection object.
SqlDataReader:
This object is used to pull the data for select statement from database, this is
farwardonly stream of data.SqlDataReader operations will be happening only in
connected architecture that means ,sqlconnection kept open when doing operations
using SqlDataReader.
DataSet:
This object uses in-memory representation of data, that means it works on disconnected
architecture.once DataSet fills there is no need of SqlConnection, data stored in
DataSet is in-memory.DataSet is common for all DataProviders that is why it is
not prefixed like other objects mentioned above and below.
SqlDataAdapter:
The DataAdapter makes it easy for you to accomplish these things by helping manage data in
disconnected mode.DataAdapter fills a Dataset object when reading and writing the data to the
database.DataAdapter contains a reference to the connection object and opens and closes the
connection automatically.The DataAdapter contains command object references for SELECT, INSERT, UPDATE and DELETE
operations on data.
Creating a SqlConnection Object:
In SqlConnection object, we need to specify the SqlServer Credentials like below
SqlConnection conn=new SqlConnection("Data Source=(local);Initial Catalog=Employee;Integrated Security=SSPI");
The above SqlConnection object is constructor with single argument.
Connection Parameters:
- Data Source:Specifies the server name
- Initial Catalog:Database name
- Integrated Security:To make connection with user's windows login
- User ID:Sql Server UserName
- Password:Sql Server Password
Sql Connection basic operations.
- Instantiate SqlConnection
- Open the Connection
- Passing the connection to the ADO.NET objects
- Perform the operations with ADO.NET objects
- Close the connection
SqlCommand:
A SqlCommand object is to specify what type of operation we need to perform with the database.
Creating SqlCommand Object:
SqlCommand cmd=new SqlCommand(commandtext,conn);
Querying Data:
ExecuteReader(): Which is used to retrive the dataset for viewing.
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
ExecuteNonQuery(): This method is used to perform INSERT,DELETE,MODIFY,SELECT operations on Database
EX:
// prepare command string
string insertString = @"
insert into Categories
(CategoryName, Description)
values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
ExecuteScalar():
To get single value from Database ,we will make use ExecuteScalar() function,Generally this is used for aggregate functions like count, sum, average etc
EX:
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
SqlDataReader:
This is mostly used for reading the data in most efficient way, we cannot use this for writing the data.This is also called fast farward streams of data.
by using this we can read the data only one time, once if you read the data , we have to save it because we cannot go back and see the data.
Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on a command object, like this:
SqlDataReader rdr = cmd.ExecuteReader();
SqlDataAdapter:
A couple scenarios illustrate why you would want to work with disconnected data: people working without network connectivity and making Web sites more scalable. Consider sales people who need customer data as they travel. At the beginning of the day, they'll need to sync up with the main database to have the latest information available. During the day, they'll make modifications to existing customer data, add new customers, and input new orders. This is okay because they have a given region or customer base where other people won't be changing the same records. At the end of the day, the sales person will connect to the network and update changes for overnight processing.
Another scenario is making a Web site more scalable. With a SqlDataReader, you have to go back to the database for records every time you show a page. This requires a new connection for each page load, which will hurt scalability as the number of users increase. One way to relieve this is to use a DataSet that is updated one time and stored in cache. Every request for the page checks the cache and loads the data if it isn't there or just pulls the data out of cache and displays it. This avoids a trip to the database, making your application more efficient.
Creating a DataSet Object
DataSet dsCustomers = new DataSet();
Creating A SqlDataAdapter
SqlDataAdapter daCustomers = new SqlDataAdapter(
"select CustomerID, CompanyName from Customers", conn);
By using fill method of SqlDataAdapter we will the dataset as below
dsCustomers.Fill(daCustomers);
/ 3. fill in insert, update, and delete commands
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
Updating Changes:
After modifications are made to the data, you'll want to write the changes back to the database. Refer to previous discussion in the Introduction of this article on update guidance. The following code shows how to use the Update method of the SqlDataAdapter to push modifications back to the database.
daCustomers.Update(dsCustomers, "Customers");
Parameters:
As you know, the SQL query assigned to a SqlCommand object is simply a string. So, if you want to filter a query, you could build the string dynamically, but you wouldn't want to. Here is a bad example of filtering a query.
// don't ever do this
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = '" + inputCity + "'";
Don't ever build a query this way! The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows form or a Web Page. Anything placed into that TextBox control will be put into inputCity and added to your SQL string. This situation invites a hacker to replace that string with something malicious. In the worst case, you could give full control of your computer away.
Instead of dynamically building a string, as shown in the bad example above, use parameters. Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.
Using parameterized queries is a three step process:
Construct the SqlCommand command string with parameters.
Declare a SqlParameter object, assigning values as appropriate.
Assign the SqlParameter object to the SqlCommand object's Parameters property.
preparing a SqlCommand Object for Parameters:
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
Declaring a SqlParameter Object:
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
Associate a SqlParameter Object with a SqlCommand Object
// 3. add new parameter to command object
cmd.Parameters.Add(param);
You should use parameters to filter queries in a secure manner.
Stored Procedures:
A stored procedures is a pre-defined, reusable routine that is stored in a database
Executing a Stored Procedure:
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"Ten Most Expensive Products", conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
Sending Parameters to Stored Procedures
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand(
"CustOrderHist", conn);
// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(
new SqlParameter("@CustomerID", custId));
