inforakesha

A fine WordPress.com site

Basics of ADO.NET

Leave a comment

10/10/2012
ADO.NET
Here we will learn following things:-
-Learn what is ADO.NET is
-Understand what a data provider is
-Understand what a connection object is
-Understand what a command object is
-Understand what a DataReader object is
-Understand what a DataSet object is
-Understand what a DataAdapter is

Introduction:-
ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the datasources is a database , but it could be a text file ,  Excel spreadsheet , or an XML file.

Data Providers:-
-We know that ADO.NET allows to interact with different types of data sources and different types of databases. However, there isn’t a single set of classes that allow you to accomplish this universally. Since different data sources exposes different protocols , we need a way to communicate with the right data source using the right protocol.
-ADO.NET provides a relatively common way to interact with data a sources, but comes in different sets of libraries for each way you can talk to a data sources . These libraries are called Data Providers and are usually named for the protocols or the data source type they allow you to interact with.

Data Providers
Table 1. ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols. The library APIs have prefixes that indicate which provider they support.

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.

ADO.NET Objects
ADO.NET includes many objects you can use to work with data.
The SqlConnection Object
To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base. A connection object is used by command objects so they will know which database to execute the command on.
The SqlCommand Object
The process of interacting with a database means that you must specify the actions you want to occur. This is done with a command object. You use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.
The SqlDataReader Object
Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.
The DataSet Object
DataSet objects are in-memory representations of data. They contain multiple Datatable objects, which contain columns and rows, just like normal database tables. You can even define relations between tables to create parent-child relationships. The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.
The SqlDataAdapter Object
Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source Some situations also call for caching data in memory to minimize the number of database calls for data that does not change. The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode. The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the database for you. All you need to do is tell the data adapter when to load from or write to the database.
Summary
ADO.NET is the .NET technology for interacting with data sources. You have several Data Providers, which allow communication with different data sources, depending on the protocols they use or what the database is. Regardless, of which Data Provider used, you’ll use a similar set of objects to interact with a data source. The SqlConnection object lets you manage a connection to a data source. SqlCommand objects allow you to talk to a data source and send commands to it. To have fast forward-only read access to data, use the SqlDataReader. If you want to work with disconnected data, use a DataSet and implement reading and writing to/from the data source with a SqlDataAdapter.
Now we will see all in details:-
The SqlConnection Object
This lesson describes the SqlConnection object and how to connect to a data base. Here are the objectives of this lesson:
Know what connection objects are used for.
Learn how to instantiate a SqlConnection object.
Understand how the SqlConnection object is used in applications.
Comprehend the importance of effective connection lifetime management.

The first thing you will need to do when interacting with a data base is to create a connection. The connection tells the rest of the ADO.NET code which database it is talking to.
Creating a SqlConnection Object
A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:
SqlConnection conn = new SqlConnection(
“Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI”);
The SqlConnection object instantiated above uses a constructor with a single argument of type string This argument is called a connection string.
ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a database connection. They include the location, name of the database, and security credentials.

Connection String Parameter Name Description
Data Source Identifies the server. Could be local machine, machine domain name, or IP Address.
Initial Catalog Database name.
Integrated Security Set to SSPI to make connection with user’s Windows login
User ID Name of user configured in SQL Server.
Password Password matching SQL Server User ID.
  1.  The following shows a connection string, using the User ID and Password parameters:

SqlConnection conn = new SqlConnection(
“Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword”);
Using a SqlConnection
The purpose of creating a SqlConnection object is so you can enable other ADO.NET code to work with a database. Other ADO.NET objects, such as a SqlCommand and a SqlDataAdapter take a connection object as a parameter. The sequence of operations occurring in the lifetime of a SqlConnection are as follows:
Instantiate the SqlConnection.
Open the connection.
Pass the connection to other ADO.NET objects.
Perform database operations with the other ADO.NET objects.
Close the connection.
Listing 1. Using a SqlConnection
using System;
using System.Data;
using System.Data.SqlClient;

/// Demonstrates how to work with SqlConnection objects
class SqlConnectionDemo
{
static void Main()
{
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(
“Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI”);

SqlDataReader rdr = null;

try
{
// 2. Open the connection
conn.Open();

// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand(“select * from Customers”, conn);

// 4. Use the connection

// get query results
rdr = cmd.ExecuteReader();

// print the CustomerID of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}

// 5. Close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
open a connection by calling the Open() method of the SqlConnection instance, conn. Any operations on a connection that was not yet opened will generate an exception. So, you must open the connection before using it.

Before using a SqlCommand, you must let the ADO.NET code know which connection it needs. In Listing 1, we set the second parameter to theSqlCommand object with the SqlConnection object, conn. Any operations performed with the SqlCommand will use that connection.

The code that uses the connection is a SqlCommand object, which performs a query on the Customers table. The result set is returned as aSqlDataReader and the while loop reads the first column from each row of the result set, which is the CustomerID column.

When you are done using the connection object, you must close it. Failure to do so could have serious consequences in the performance and scalability of your application. There are a couple points to be made about how we closed the connection in Listing 1: the Close() method is called in a finally block and we ensure that the connection is not null before closing it.

Another precaution you should take when closing connections is to make sure the connection object is not null. If something goes wrong when instantiating the connection, it will be null and you want to make sure you don’t try to close an invalid connection, which would generate an exception.

SqlConnection objects let other ADO.NET code know what database to connect to and how to make the connection. They are instantiated by passing a connection string with a set of key/value pairs that define the connection. The steps you use to manage the lifetime of a connection are create, open, pass, use, and close. Be sure to close your connection properly when you are done with it to ensure you don’t have a connection resource leak.
The SqlCommand Object
This lesson describes the SqlCommand object and how you use it to interact with a database. Here are the objectives of this lesson:
Know what a command object is.
Learn how to use the ExecuteReader method to query data.
Learn how to use the ExecuteNonQuery method to insert and delete data.
Learn how to use the ExecuteScalar method to return a single value.
A SqlCommand object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table. The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone. A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data. This lesson will also show you how to retrieve a single value from a database, such as the number of records in a table.

Creating a SqlCommand Object
Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:
SqlCommand cmd = new SqlCommand(“select CategoryName from Categories”, conn);

It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object. SqlCommand has a few overloads, which you will see in the examples of this tutorial.
Querying Data
When using a SQL select command, you retrieve a data set for viewing. To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object.
// 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();
In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor. Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd.
Inserting Data
To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object. The following code shows how to insert data into a database table:
// 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();
Notice the two apostrophes (”) in the insertString text for the word “doesn”t”. This is how you escape the apostrophe to get the string to populate column properly.

Updating Data
The ExecuteNonQuery method is also used for updating data. The following code shows how to update data:
// prepare command string
string updateString = @”
update Categories
set CategoryName = ‘Other’
where CategoryName = ‘Miscellaneous'”;

// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);

// 2. Set the Connection property
cmd.Connection = conn;

// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command. In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd.
The ExecuteNonQuery method performs the update command.
Deleting Data
You can also delete data using the ExecuteNonQuery method. The following example shows how to delete a record from a database with the ExecuteNonQuery method:
// prepare command string
string deleteString = @”
delete from Categories
where CategoryName = ‘Other'”;

// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();

// 2. Set the CommandText property
cmd.CommandText = deleteString;

// 3. Set the Connection property
cmd.Connection = conn;

// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
This example uses the SqlCommand constructor with no parameters. Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd.
Getting Single values
Sometimes all you need from a database is a single value, which could be a count, sum, average, or other aggregated value from a data set. Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this. The best choice is to let the database perform the work and return just the single value you need. The following example shows how to do this with the ExecuteScalar method:
// 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();
Listing 1. SqlConnection Demo
using System;
using System.Data;
using System.Data.SqlClient;
/// Demonstrates how to work with SqlCommand objects

class SqlCommandDemo
{
SqlConnection conn;

public SqlCommandDemo()
{
// Instantiate the connection
conn = new SqlConnection(
“Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI”);
}

// call methods that demo SqlCommand capabilities
static void Main()
{
SqlCommandDemo scd = new SqlCommandDemo();

Console.WriteLine();
Console.WriteLine(“Categories Before Insert”);
Console.WriteLine(“————————“);

// use ExecuteReader method
scd.ReadData();

// use ExecuteNonQuery method for Insert
scd.Insertdata();
Console.WriteLine();
Console.WriteLine(“Categories After Insert”);
Console.WriteLine(“——————————“);

scd.ReadData();

// use ExecuteNonQuery method for Update
scd.UpdateData();

Console.WriteLine();
Console.WriteLine(“Categories After Update”);
Console.WriteLine(“——————————“);

scd.ReadData();

// use ExecuteNonQuery method for Delete
scd.DeleteData();

Console.WriteLine();
Console.WriteLine(“Categories After Delete”);
Console.WriteLine(“——————————“);

scd.ReadData();

// use ExecuteScalar method
int numberOfRecords = scd.GetNumberOfRecords();

Console.WriteLine();
Console.WriteLine(“Number of Records: {0}”, numberOfRecords);
}

/// use ExecuteReader method
public void ReadData()
{
SqlDataReader rdr = null;

try
{
// Open the connection
conn.Open();

// 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
rdr = cmd.ExecuteReader();

// print the CategoryName of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}

// Close the connection
if (conn != null)
{
conn.Close();
}
}
}

/// <summary>
/// use ExecuteNonQuery method for Insert
/// </summary>
public void Insertdata()
{
try
{
// Open the connection
conn.Open();

// 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();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}

/// <summary>
/// use ExecuteNonQuery method for Update
/// </summary>
public void UpdateData()
{
try
{
// Open the connection
conn.Open();

// prepare command string
string updateString = @”
update Categories
set CategoryName = ‘Other’
where CategoryName = ‘Miscellaneous'”;

// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);

// 2. Set the Connection property
cmd.Connection = conn;

// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}

/// use ExecuteNonQuery method for Delete
public void DeleteData()
{
try
{
// Open the connection
conn.Open();

// prepare command string
string deleteString = @”
delete from Categories
where CategoryName = ‘Other'”;

// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();

// 2. Set the CommandText property
cmd.CommandText = deleteString;

// 3. Set the Connection property
cmd.Connection = conn;

// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}

/// <summary>
/// use ExecuteScalar method
/// </summary>
/// <returns>number of records</returns>
public int GetNumberOfRecords()
{
int count = -1;

try
{
// Open the connection
conn.Open();

// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand(“select count(*) from Categories”, conn);

// 2. Call ExecuteScalar to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}
A SqlCommand object allows you to query and send commands to a database. It has methods that are specialized for different commands. The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query. For insert, update, and delete SQL commands, you use the ExecuteNonQuery method. If you only need a single aggregate value from a query, the ExecuteScalar is the best choice.

Reading Data with the SqlDataReader
Learn what a SqlDataReader is used for.
Know how to read data using a SqlDataReader.
Understand the need to close a SqlDataReader.
A SqlDataReader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. SqlDataReaders are often described as fast-forward firehose-like streams of data.

The forward only design of the SqlDataReader is what enables it to be fast. It doesn’t have overhead associated with traversing the data or writing it back to the data source. Therefore, if your only requirement for a group of data is for reading one time and you want the fastest method possible, the SqlDataReader is the best choice.

I used the term “one time” in the previous paragraph when discussing the reasons why you would use a SqlDataReade

Creating a SqlDataReader Object
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();
The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance.
Reading Data
the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available. To read that row again, you would have to create a new instance of the SqlDataReader and read through the data stream again.

The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop. The following code shows how to accomplish this:
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr[“ContactName”];
string company = (string)rdr[“CompanyName”];
string city    = (string)rdr[“City”];

// print out the results
Console.Write(“{0,-25}”, contact);
Console.Write(“{0,-20}”, city);
Console.Write(“{0,-25}”, company);
Console.WriteLine();
}
Notice the call to Read on the SqlDataReader, rdr, in the while loop condition in the code above. The return value of Read is type bool and returns true as long as there are more records to read. After the last record in the data stream has been read, Read returns false.
Regardless of the type of the indexer parameter, a SqlDataReader indexer will return type object. This is why the example above casts results to a string. Once the values are extracted, you can do whatever you want with them, such as printing them to output with Console type methods.

Finishing Up
Always remember to close your SqlDataReader, just like you need to close the SqlConnection. Wrap the data access code in a try block and put the close operation in the finally block, like this:
try
{
// data access code
}
finally
{
// 3. close the reader
if (rdr != null)
{
rdr.Close();
}

// close the connection too
}
The code above checks the SqlDataReader to make sure it isn’t null. After the code knows that a good instance of the SqlDataReader exists,it can close it.
Listing 1: Using the SqlDataReader
using System;
using System.Data;
using System.Data.SqlClient;

namespace Lesson04
{
class ReaderDemo
{
static void Main()
{
ReaderDemo rd = new ReaderDemo();
rd.SimpleRead();
}

public void SimpleRead()
{
// declare the SqlDataReader, which is used in
// both the try block and the finally block
SqlDataReader rdr = null;

// create a connection object
SqlConnection conn = new SqlConnection(
“Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI”);

// create a command object
SqlCommand cmd  = new SqlCommand(
“select * from Customers”, conn);

try
{
// open the connection
conn.Open();

// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();

// print a set of column headers
Console.WriteLine(“Contact Name City  Company Name”);
Console.WriteLine(“————             ————        ————“);

// 2. print necessary columns of each
record
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr[“ContactName”];
string company = (string)rdr[“CompanyName”];
string city    = (string)rdr[“City”];

// print out the results
Console.Write(“{0,-25}”, contact);
Console.Write(“{0,-20}”, city);
Console.Write(“{0,-25}”, company);
Console.WriteLine();
}
}
finally
{
// 3. close the reader
if (rdr != null)
{
rdr.Close();
}

// close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Summary
SqlDataReader objects allow you to read data in a fast forward-only manner. You obtain data by reading each row from the data stream. Call the Close method of the SqlDataReader to ensure there are not any resource leaks.
Working with Disconnected Data – The DataSet and SqlDataAdapter
Understand the need for disconnected data.
Obtain a basic understanding of what a DataSet is for.
Learn to use a SqlDataAdapter to retrieve and update data.
A DataSet is an in-memory data store that can hold numerous tables. DataSets only hold data and do not interact with a data source. It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:
Open connection
Retrieve data into DataSet
Close connection
and performs the following actions when updating data source with DataSet changes:
Open connection
Write changes from DataSet to data source
Close connection
you are free to read and write data with the DataSet as you need. These are the mechanics of working with disconnected data. Because the applications holds on to connections only when necessary, the application becomes more scalable.
Creating a DataSet Object
There isn’t anything special about instantiating a DataSet. You just create a new instance, just like any other object:
DataSet dsCustomers = new DataSet();
The DataSet constructor doesn’t require parameters. However there is one overload that accepts a string for the name of the DataSet, which is used if you were to serialize the data to XML
Creating A SqlDataAdapter
The SqlDataAdapter holds the SQL commands and connection object for reading and writing data. You initialize it with a SQL select statement and connection object:
SqlDataAdapter daCustomers = new SqlDataAdapter(
“select CustomerID, CompanyName from Customers”, conn);
The code above creates a new SqlDataAdapter, daCustomers. The SQL select statement specifies what data will be read into a DataSet. The connection object, conn, should have already been instantiated, but not opened. It is the SqlDataAdapter’s responsibility to open and close the connection during Fill and Update method calls.
There are two ways to add insert, update, and delete commands: via SqlDataAdapter properties or with a SqlCommandBuilder. In this lesson, I’m going to show you the easy way of doing it with the SqlCommandBuilder. In a later lesson, I’ll show you how to use the SqlDataAdapter properties, which takes more work but will give you more capabilities than what the SqlCommandBuilder does.
Here’s how to add commands to the SqlDataAdapter with the SqlCommandBuilder:
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
Notice in the code above that the SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daCustomers, instance. This tells the SqlCommandBuilder what SqlDataAdapter to add commands to. The SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter, respectively.
the SqlCommandBuilder has limitations. It works when you do a simple select statement on a single table. However, when you need a join of two or mor tables or must do a stored procedure, it won’t work.

Filling the DataSet
Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet. Here’s how to do it, by using the Fill method of the SqlDataAdapter:
daCustomers.Fill(dsCustomers, “Customers”);
The Fill method, in the code above, takes two parameters: a DataSet and a table name. The DataSet must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the DataSet. You can name the table anything you want. Its purpose is so you can identify the table with a meaningful name later on. Typically, I’ll give it the same name as the database table.
Using the DataSet
A DataSet will bind with both ASP.NET and Windows forms DataGrids. Here’s an example that assigns the DataSet to a Windows forms DataGrid:
dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = “Customers”;
The first thing we do, in the code above, is assign the DataSet to the DataSource property of the DataGrid. This lets the DataGrid know that it has something to bind to, but you will get a ‘+’ sign in the GUI because the DataSet can hold multiple tables and this would allow you to expand each available table. To specify exactly which table to use, set the DataGrid’s DataMember property to the name of the table. In the example, we set the name to Customers,
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”);
The Update method, above, is called on the SqlDataAdapter instance that originally filled the dsCustomers DataSet. The second parameter to the Update method specifies which table, from the DataSet, to update.
Implementing a Disconnected Data Management Strategy
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

class DisconnectedDataform : Form
{
private SqlConnection  conn;
private SqlDataAdapter daCustomers;

private DataSet  dsCustomers;
private DataGrid dgCustomers;

private const string tableName = “Customers”;

// initialize form with DataGrid and Button
public DisconnectedDataform()
{
// fill dataset
Initdata();

// set up datagrid
dgCustomers = new DataGrid();
dgCustomers.Location = new Point(5, 5);
dgCustomers.Size = new Size(
this.ClientRectangle.Size.Width – 10,
this.ClientRectangle.Height – 50);
dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = tableName;

// create update button
Button btnUpdate = new Button();
btnUpdate.Text = “Update”;
btnUpdate.Location = new Point(
this.ClientRectangle.Width/2 – btnUpdate.Width/2,
this.ClientRectangle.Height – (btnUpdate.Height + 10));
btnUpdate.Click += new EventHandler(btnUpdateClicked);

// make sure controls appear on form
Controls.AddRange(new Control[] { dgCustomers, btnUpdate });
}

// set up ADO.NET objects
public void Initdata()
{
// instantiate the connection
conn = new SqlConnection(

                          “Server=(local);DataBase=Northwind;Integrated Security=SSPI”);

// 1. instantiate a new DataSet
dsCustomers = new DataSet();

// 2. init SqlDataAdapter with select command and connection
daCustomers = new SqlDataAdapter(

                          “select CustomerID, CompanyName from Customers”, conn);

// 3. fill in insert, update, and delete commands
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

// 4. fill the dataset
daCustomers.Fill(dsCustomers, tableName);
}

// Update button was clicked
public void btnUpdateClicked(object sender, EventArgs e)
{
// write changes back to DataBase
daCustomers.Update(dsCustomers, tableName);
}

// start the Windows form
static void Main()
{
Application.Run(new DisconnectedDataForm());
}
}
DataSets hold multiple tables and can be kept in memory and reused. The SqlDataAdapter enables you to fill a DataSet and Update changes back to the database. You don’t have to worry about opening and closing the SqlConnection because the SqlDataAdapter does it automatically. A SqlCommandBuilder populates insert, update, and delete commands based on the SqlDataAdapter’s select statement. Use the Fill method of the SqlDataAdapter to fill a DataSet with data. Call the SqlDataAdapter’s Update method to push changes back to a database.
Adding Parameters to Commands
Understand what a parameter is.
Be informed about the benefits of using parameters.
Learn how to create a parameter.
Learn how to assign parameters to commands.
When working with data, you’ll often want to filter results based on some criteria. Typically, this is done by accepting input from a user and using that input to form a SQL query. For example, a sales person may need to see all orders between specific dates. Another query might be to filter customers by city.
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
The first step in using parameters in SQL queries is to build a command string containing parameter placeholders. These placeholders are filled in with actual parameter values when the SqlCommand executes. Proper syntax of a parameter is to use an ‘@’ symbol prefix on the parameter name as shown below:
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
“select * from Customers where city = @City”, conn);
In the SqlCommand constructor above, the first argument contains a parameter declaration, @City. This example used one parameter, but you can have as many parameters as needed to customize the query. Each parameter will match a SqlParameter object that must be assigned to this SqlCommand object.
Declaring a SqlParameter Object
Each parameter in a SQL statement must be defined. This is the purpose of the SqlParameter type. Your code must define a SqlParameter instance for each parameter in a SqlCommand object’s SQL command. The following code defines a parameter for the @City parameter from the previous section:
// 2. define parameters used in command object
SqlParameter param  = new SqlParameter();
param.ParameterName = “@City”;
param.Value         = inputCity;
Notice that the ParameterName property of the SqlParameter instance must be spelled exactly as the parameter that is used in the SqlCommand SQL command string. You must also specify a value for the command. When the SqlCommand object executes, the parameter will be replaced with this value.

Associate a SqlParameter Object with a SqlCommand Object
For each parameter defined in the SQL command string argument to a SqlCommand object, you must define a SqlParameter. You must also let the SqlCommand object know about the SqlParameter by assigning the SqlParameter instance to the Parameters property of the SqlCommand object. The following code shows how to do this:
// 3. add new parameter to command object
cmd.Parameters.Add(param);
Listing 1: Adding Parameters to Queries
using System;
using System.Data;
using System.Data.SqlClient;

class ParamDemo
{
static void Main()
{
// conn and reader declared outside try
// block for visibility in finally block
SqlConnection conn   = null;
SqlDataReader reader = null;

string inputCity = “London”;


                 try
{
// instantiate and open connection
conn =  new
SqlConnection(“Server=(local);DataBase=Northwind;Integrated Security=SSPI”);
conn.Open();

// don’t ever do this
// SqlCommand cmd = new SqlCommand(
// “select * from Customers where city = ‘” + inputCity + “‘”;

// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(“select * from Customers where city = @City”, conn);

// 2. define parameters used in command object
SqlParameter param  = new SqlParameter();
param.ParameterName = “@City”;
param.Value         = inputCity;

// 3. add new parameter to command object
cmd.Parameters.Add(param);

// get data stream
reader = cmd.ExecuteReader();

// write each record
while(reader.Read())
{
Console.WriteLine(“{0}, {1}”,
reader[“CompanyName”],
reader[“ContactName”]);
}
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}
// close connection
if (conn != null)
{
conn.Close();
}
}
}
}
The code in Listing 1 retrieves records for each customer that lives in London. This was made more secure through the use of parameters. Besides using parameters, all of the other code contains techniques you’ve learned in previous lessons.
Summary
You should use parameters to filter queries in a secure manner. The process of using parameter contains three steps: define the parameter in the SqlCommand command string, declare the SqlParameter object with applicable properties, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Using Stored Procedures
This lesson shows how to use stored procedures in your data access code. Here are the objectives of this lesson:
Learn how to modify the SqlCommand object to use a stored procedure.
Understand how to use parameters with stored procedures.
Introduction
A stored procedures is a pre-defined, reusable routine that is stored in a database. SQL Server compiles stored procedures, which makes them more efficient to use. Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures. The following sections will show you how to modify the SqlCommand object to use stored procedures. Additionally, you’ll see another reason why parameter support is an important part of the ADO.NET libraries.
Executing a Stored Procedure
In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures. There are two tasks require to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure. These two steps are shown below:
// 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;
 While declaring the SqlCommand object above, the first parameter is set to “Ten Most Expensive Products”. This is the name of a stored procedure in the Northwind database. The second parameter is the connection object, which is the same as the SqlCommand constructor used for executing query strings.
The second command tells the SqlCommand object what type of command it will execute by setting its CommandType property to theStoredProcedure value of the CommandType enum. The default interpretation of the first parameter to the SqlCommand constructor is to treat it as a query string. By setting the CommandType to StoredProcedure, the first parameter to the SqlCommand constructor will be interpreted as the name of a stored procedure (instead of interpreting it as a command string).
Sending Parameters to Stored Procedures
Using parameters for stored procedures is the same as using parameters for query string commands. The following code shows this:
// 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));
The SqlCommand constructor above specifies the name of a stored procedure, CustOrderHist, as its first parameter. This particular stored procedure takes a single parameter, named @CustomerID. Therefore, we must populate this parameter using a SqlParameter object. The name of the parameter passed as the first parameter to the SqlParameter constructor must be spelled exactly the same as the stored procedure parameter.
Then execute the command the same as you would with any other SqlCommand object.
Executing Stored Procedures
using System;
using System.Data;
using System.Data.SqlClient;

class StoredProcDemo
{
static void Main()
{
StoredProcDemo spd = new StoredProcDemo();

// run a simple stored procedure
spd.RunStoredProc();

// run a stored procedure that takes a parameter
spd.RunStoredProcParams();
}

// run a simple stored procedure
public void RunStoredProc()
{
SqlConnection conn = null;
SqlDataReader rdr  = null;

Console.WriteLine(“\nTop 10 Most Expensive Products:\n”);

try
{
// create and open a connection object
conn = new SqlConnection(“Server=(local);DataBase=Northwind;Integrated Security=SSPI”);
conn.Open();

// 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;

// execute the command
rdr = cmd.ExecuteReader();

// iterate through results, printing each to console
while (rdr.Read())
{
Console.WriteLine(“Product: {0,-25} Price: ${1,6:####.00}”,
rdr[“TenMostExpensiveProducts”],rdr[“UnitPrice”]);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
}

// run a stored procedure that takes a parameter
public void RunStoredProcParams()
{
SqlConnection conn = null;
SqlDataReader rdr  = null;

// typically obtained from user
// input, but we take a short cut
string custId = “FURIB”;
Console.WriteLine(“\nCustomer Order History:\n”);

try
{
// create and open a connection object
conn = new SqlConnection(“Server=(local);DataBase=Northwind;Integrated Security=SSPI”);
conn.Open();

// 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));
// execute the command
rdr = cmd.ExecuteReader();

// iterate through results, printing each to console
while (rdr.Read())
{
Console.WriteLine(“Product: {0,-35} Total: {1,2}”,rdr[“ProductName”],
rdr[“Total”]);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
}
}
The RunStoredProc method in Listing 1 simply runs a stored procedure and prints the results to the console. In the RunStoredProcParamsmethod, the stored procedure used takes a single parameter.
Summary
To execute stored procedures, you specify the name of the stored procedure in the first parameter of a SqlCommand constructor and then set the CommandType of the SqlCommand to StoredProcedure. You can also send parameters to a stored procedure by using SqlParameter objects, the same way it is done with SqlCommand objects that execute query strings. Once the SqlCommand object is constructed, you can use it just like any other SqlCommand object as described in previous lessons.
 
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s