Ado .Net InterView Questions

What is Ado .Net?

Ado.Net is a Technique or an API that is used to Access any Database. ADO.Net enables a Developer to Connect to a Database, Fetch data from database, Insert, Modify or Delete Data from Database.

ADO.NET Components

1- DataSet-
2- Data provider - which is a set of components including the Connection, Command,    DataReader,and DataAdapter objects.

1- DataSet- is the disconnected Cashed set of Records of one or more tables.
2- Data provider- are used to Connect to a Database, Fetch data from database, Insert, Modify or  Delete Data from Database.

Component of DataProvider
The Connection object provides connectivity to a data source.
The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information.
The DataReader object Retreives Data from Database in Read Only and Farword only Mode.

The DataAdapter object provides the bridge between the DataSet object and the data source.

Which property is used to check whether a DataReader is closed or opened?

The IsClosed  property is used to check whether a DataReader is closed or opened. This property returns a true value if a Data Reader is closed, otherwise a false value is returned.


What is the use of the CommandBuilder class?

The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet.


The Command object provides three methods to execute commands on the database:
•ExecuteNonQuery() executes the commands and returns the number of rows affected.
•ExecuteScalar() executes the commands, and returns the first column of the first row in the result set .
•ExecuteReader() method returns a result set by using the DataReader object.

Connection pooling- Connection pooling increases the performance of the applications by reusing the active database connections instead of creating a new connection for every request.

Connection pooling behaviour is controlled by the connection string parameters.

The following 4 parameters control most of the connection pooling behavior.

1. Connect Timeout
2. Max Pool Size
3. Min Pool Size
4. Pooling

More about Connection Pooling : - Connection pooling refers to the task of grouping database connections in cache to make them reusable because opening new connections every time to a database is a time-consuming process. Therefore, connection pooling enables you to reuse already existing and active database connections, whenever required, and increasing the performance of your application. 

SQL Server Reporting Services (SSRS) is a server-based report generation software system. it can be used to prepare and deliver a variety of interactive and printed reports.

SSRS allows reports to be exported in various formats (Excel, PDF, word etc) 

Adding DataTable in Dataset : 
addedd two DataTable in One DataSet

 DataTable dt = new DataTable();
 dt.Columns.Add("ID", typeof(int));

 dt.Columns.Add("Name", typeof(string));

 DataTable dt2 = new DataTable();
 dt2.Columns.Add("SID", typeof(int));

 dt2.Columns.Add("SName", typeof(string));
 DataSet ds = new DataSet(); 

 ds.Tables.Add(dt);
 ds.Tables.Add(dt2);

Adding Rows in DataTable:

DataRow row = ds.Tables[0].NewRow();

//Tables[0] indicate first Table in Dataset 

row["ID"] = 1;

row["Name"] = "Suraj";

ds.Tables[0].Rows.Add(row);

Update  data in DataTable

ds.Tables[0].Rows[0]["Name"] = "Updated Name";

if Row index is unknown:
DataRow[] dRow = ds.Tables[0].Select("ID = 1");
dRow[0]["Name"] = "Updated Name";

Delete row from datatable

ds.Tables[0].Rows[0].Delete(); // deletes first Row of First DataTable

Binding Grid with DataTable

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">

        <Columns>

            <asp:BoundField DataField="ID"  HeaderText="Company ID"/>

            <asp:BoundField DataField="Name" HeaderText="Company Name"/>

       </Columns>

</asp:GridView>

        DataTable dt = new DataTable();

string sqlStatement = "SELECT * FROM CompanyInfo";

          SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);

          SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

          sqlDa.Fill(dt);

              if (dt.Rows.Count > 0)

              {

                GridView1.DataSource = dt;

                GridView1.DataBind();

              }

DataTable and DataView(Sorting and Filtering)

A datatable is an in-memory representation of a single database table.datatable stores physical data. So we can not perform filtering or Sorting in DataTable. For this we have to Create a Dataview. DataView enables you to sort or Filter Data.

EX: 

Sorting and Filtering in Dataview

_dataview.Sort = "Name ASC";
_dataview.RowFilter = "CustomerStatus = 'Active'";

Sorting and Filtering in DataTableSyntax: datatable.Select(filterExp, sortExp, rowstatevalue)

EX:  
string filterExp = "Status = 'Active'";
string sortExp = "City";
DataRow[] drarray;
drarray = dataSet1.Customers.Select(filterExp, sortExp,DataViewRowState.CurrentRows);