Few ADO.NET points for reference

FieldCount : is used to get number of colums in a row by using sqldatareader

IsClosed is used to find whether the sqldatareader is closed or not.

Good Example of stored procedure

//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Specify to CommandObject that you intend to execute a Stored Procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Create an SQL Parameter object
SqlParameter ParameterObject = new SqlParameter();
//Specify the name of the SQL Parameter
ParameterObject.ParameterName = "Parameter1";
//Assign the Parameter value
ParameterObject.Value = "Some Value";
//Specify the Database DataType of the Parameter
ParameterObject.DbType = DbType.String;
//Specify the type of parameter - input-only(default), output-only, bidirectional
ParameterObject.Direction = ParameterDirection.Input;
//Associate the Parameter to the Command Object
CommandObject.Parameters.Add(ParameterObject);
//Open the connection
ConnectionObject.Open();
//Execute the command
int Records_Affected = CommandObject.ExecuteNonQuery();
//Close the Connection
ConnectionObject.Close();


Executing and getting data from two queries using datareader

- By using NextResult() method
- Example

//Create the SQL Query with 2 Select statements
string SQLQuery = "Select * from Customers;Select * from Employees;";
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();


Uses of using Stored procedures

1) Better Performance - as they are precompiled one's
2) Security - we can specify who has the rights to execute
3) Reduce Network traffic - Just we need to specify the name of SP instead of sending a big sql query

Differences between Datareader and Dataset

DataReader
1. DatReader works on a Connection oriented architecture.
2. DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
3. Updations are not possible with DataReader.
4. As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
1. DataSet works on disconnected architecture.
2. Using a DataSet you can move in both directions. DataSet is bi directional.
3. Database can be updated from a DataSet.
4. DataSet is slower than DataReader.