FAQ

Question and Anser

What are the different ADO.NET namespaces are available in .NET.

The following namespaces are available in .NET.

System.Data

This namespace is the base of ADO.NET. It provides all the classes that are used by data providers. The most import class that it supports is DataSet. It also contains classes to represent tables, columns, rows, relation and the constraint class.

System.Data.Common

This namespace defines common classes that are used as base classes for data providers. These classes are used by all data providers. Examples are DbConnection and DbDataAdapter

System.Data.OleDb

This namespace provides classes that work with OLE-DB data sources using the .NET OleDb data provider.Example of these classes are as follows:

- OleDbConnection.
- OleDbCommand.

System.Data.Odbc

It defines the data provider for the SQL Server database. It contains classes such as

- SqlConnection.
- SqlCommand.
- SqlDataReader

System.Data.SqlTypes

This namespace provides classes for specific data types for the SQL Server database.

Define the data provider classes that is supported by ADO.NET.

The .NET framework data provider includes the following components for data manipulation:

Connection : Used for connectivity to the data source

Command : This executes the SQL statements needed to retrieve data, modify data or execute stored procedures. It works with connection object.

DataReader : This class is used to retrieve data. DataReader is forward only and read-only object. We cannot modify the data using DataReader.

DataAdapter : It works as bridge between dataset and data source. DataAdapter uses fill method to load the dataset.

Describe Connection object in ADO.NET

ADO.NET provides the connection object to connect with datasource. Always remember that a connection object does not fetch or update data, it does not execute sql queries, and it does not contain the results of sql queries. Connection object contains only the information about connection string.

Describe the DataSet object in ADO.NET.

The DataSet is the most important object in ADO.NET. DataSet object works as a mini database. It provides the disconnected environment. It persists data in memory which is separate from the database. A DataSet contains a collection of DataTable objects means it can store more than one table simultaneously. Each DataTable object contains collections of DataRow, DataColumn and Constraint objects. A DataSet also contains a collection of DataRelation objects that define the relationship between the DataTable objects. It belongs to “System.Data” namespace.

How will you fill the GridView by using DataTable object at runtime?

using System;
using System.Data;
public partial class Default : System.Web.UI.Page
{
     protected void Page_Load(object sender, EventArgs e)
     {
          DataTable dt = new DataTable("Employee"); // Create the table object
          DataColumn dc = new DataColumn();
          DataRow dr;
          dc.ColumnName = "ID"; //Heading of the coloumn
          dc.DataType = Type.GetType("System.Int32"); //Set the type of ID as Integer
          dt.Columns.Add(dc);
          dc = new DataColumn();
          dc.ColumnName = "ItemName";
          dc.DataType = Type.GetType("System.String"); //Set the type of ItemName as String
          dt.Columns.Add(dc);
          for (int i = 0; i <= 4; i++) //This code will create 5 new rows
          {
               dr = dt.NewRow();
               dr["id"] = i;
               dr["ItemName"] = "Item " + i;
               dt.Rows.Add(dr);
          }
          GridView1.DataSource = dt;
          GridView1.DataBind();
     }
}

Output of the above example:

Describe DataReader object of ADO.NET with example.

- The DataReader object is a forward-only and read only object
- It is simple and fast compare to dataset.
- It provides connection oriented environment.
- It needs explicit open and close the connection.
- DataReader object provides the read() method for reading the records. read() method returns Boolean type.
- DataReader object cannot initialize directly, you must use ExecuteReader() method to initialize this object.

Example:

using System;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
public partial class CareerRide : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            SqlDataReader reader;
            SqlConnection MyConnection = new SqlConnection("Data Source=name of your datasource;Initial Catalog=Employee;Integrated Security=True");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM emp";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = MyConnection; MyConnection.Open();
            reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            GridView1.DataSource = reader;
            GridView1.DataBind();
            cmd.Dispose();
            MyConnection.Dispose();
        }
    }
}

What is Serialization and De-Serialization in .Net? How can we serialize the DataSet object?

Serialization is the process of converting an object into stream of bytes that can be stored and transmitted over network. We can store this data into a file, database or Cache object. De-Serialization is the reverse process of serialization. By using de-serialization we can get the original object that is previously serialized. Following are the important namespaces for Serialization in .NET.

- System.Runtime.Serialization namespace.
- System.Runtime.Serialization.Formatters.Binary
- System.Xml.Serialization

The main advantage of serialization is that we can transmit data across the network in a cross-platform environment and we can save in a persistent or non-persistent storage medium. Serialization can be the following types:

- Binary Serialization
- SOAP Serialization
- XML Serialization
- Custom Serialization

How can you serialize the DataSet ? Explain with example.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True");
        SqlDataAdapter da = new SqlDataAdapter("select * from employee", con);
        DataSet ds = new DataSet(); da.Fill(ds);
        FileStream fileObj = new FileStream("C:\\abc.xml", FileMode.Create);
        XmlSerializer serObj = new XmlSerializer(typeof(DataSet));
        serObj.Serialize(fileObj, ds); fileObj.Close();
    }
}

In the above given example the database name is Demo and table name is employee. The data will be serialized in abc.xml file..

Describe the command object and its method.

After successful connection with database you must execute some sql query for manipulation of data or selecting the data. This job is done by command object. If you are using SQL Server as database then SqlCommand class will be used. It executes SQL statements and Stored Procedures against the data source specified in the Connection Object. It requires an instance of a Connection Object for executing the SQL statements.

ExecuteReader :
This method works on select SQL query. It returns the DataReader object. Use DataReader read () method to retrieve the rows.

ExecuteScalar :
This method returns single value. Its return type is Object

ExecuteNonQuery :
If you are using Insert, Update or Delete SQL statement then use this method. Its return type is Integer (The number of affected records).

using System;
using System.Data;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "select * from employee";
        cmd.CommandType = CommandType.Text;
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                DropDownList1.Items.Add(reader[1].ToString());
            }
        }
        else
        {
            Label1.Text="No rows found.";
        }
        reader.Close();
        con.Close();
    }
}

In the above given example a DropdownList has been taken that is filled by second column of employee table.

What is the difference between DataSet and DataReader?

The following are the main difference between DataSet and DataReader.

DataSet :

- Consumer Object
- Provides Disconnected mode
- Forward and backward scanning of data
- Slower access to data
- Can store multiple table simultaneously
- Read/Write access

DataReader :

- Provider Object
- Provides Connected mode
- Forward-only cursor
- Faster access to data
- Supports a single table based on a single SQL query
- Read Only access