Explore topic-wise InterviewSolutions in Current Affairs.

using System;using System.Data;public partial class Default : System.Web.UI.Page{ protected void Page_Load(object sender, EVENTARGS e) { GRIDVIEW gridView1=new GridView(); //Create GridView object DataTable t = new DataTable("Employee"); // Create the table object DataColumn c = new DataColumn(); //Creating table column DataRow r; //Instance of row c.ColumnName = "EmpID"; //Heading of the coloumn c.DataType = Type.GetType("System.Int32"); //Set the data type of EmpID as an Integer t.Columns.Add(c); //ADDING a column to data table c = new DataColumn(); c.ColumnName = "EmpName"; c.DataType = Type.GetType("System.String"); //Set the type of EmpName as String t.Columns.Add(c); for (int i = 0; i < 5; i++) //This code will create 5 rows { r = t.NewRow(); //Add Column values r["EmpID"] = i; r["EmpName"] = "Employee " + i; t.Rows.Add(r); } gridView1.DataSource = t; //Set gridView1 Datasource as DataTable t gridView1.DataBind(); //Bind Datasource to gridview }}

Output:

ADO.NET is a brilliant technology that was DEVELOPED by Microsoft on the framework of .NET. The primary role and responsibility of ADO.NET technology is to setup a bridge between backend language and your database. A good EXPERIENCE of this technology will be of great use from a development point of view.

ADO.NET technology will definitely help in your career growth as it has quite a good scope. Also learning this interesting technology will always be great fun. ADO.NET along with the knowledge of databases will definitely be exceptional from a growth perspective.

References

• “Microsoft® ADO.NET 4 Step by Step” by Tim Patrick
• “ADO.NET in a nutshell” by Bill Hamilton
• ADO.NET Documentation

Useful Resources

• C# Interview
• ASP.NET Interview
• Microsoft Interview
• SQL Interview

Serialization is the method of converting an object into a byte stream which can be stored as well as transmitted over the network. The advantage of serialization is that data can be transmitted in a cross-platform environment ACROSS the network and also it can be saved in a storage MEDIUM like persistent or non-persistent.

The code for SERIALIZING a DataSet is:

In the above given example, the database NAME is employee and, the table name is emp. The data in a DataSet will be serialized and stored in a demo.xml file by using Serialize() method.

Consider the below example where a CONNECTION to the SQL Server has been established. An employee database will be used to connect. The C# code will be:

Using block will be useful in closing the connection automatically. It is not required to explicitly call the CLOSE() method, because using block will do this implicitly when the code exits the block.

Output:

On execution, if the connection has been established, a message will be displayed on an output window.

If the connection is not created with the help of using a block, a connection must be closed explicitly.

The collection of rows for the DATATABLE object has been defined by the DataRowCollection class. DataRowCollection class has the method NewRow() for adding a NEW DataRow to DataTable. This method creates a new row that implements the similar SCHEMA that is applied to the DataTable.

The methods provided by the DataRowCollection object are given below:

• Add()- It adds a NEWLY created row into DataRowCollection.
• Remove()- It deletes the object DataRow from DataRowCollection.
• RemoveAt()- It deletes a row for which location is marked by an index number.

• The METHOD Clone() copies only the DataSet structure. The COPIED structure will have all the constraints, relations, as WELL as DataTable schemas used by the DataSet. It does not copy the DATA stored in the DataSet.
• The Copy() method copies the DataSet structure along with the data in the DataSet. The original data will not be affected.

The DataSet object has two methods to TRACK down the CHANGES:

• GetChanges(): It RETURNS the DataSet object that has been CHANGED since it was loaded or since the execution of the AcceptChanges() method.
• HasChanges(): It indicates if any modifications were made since from the time the DataSet object was loaded or after a method call to the AcceptChanges() was made.

Use the RejectChanges() method, if you want to reverse the entire changes since from the time the DataSet object was loaded.

• LINQ(Language Integrated Query) is a structured query syntax that helps the programmers and testers to retrieve data from various data sources such as Collections, XML Docs, ADO.NET DataSet, web service, MS SQL Server, etc.
• It is integrated with C# or VB.NET and it eliminates the mismatch between different programming languages and DATABASES. It provides a single querying interface for various data source types.
• An object will be returned as a result of LINQ query execution. It will allow you to use an object-oriented approach on the result set and there is no NEED to WORRY about the transformation of different result formats into OBJECTS.

DataSet ds=new DataSet();SQLCONNECTION con=new SqlConnection("connection_string");SqlDataAdapter da=new SqlDataAdapter("select * from Employee1",con);da.Fill(ds.Tables.Add()); da=new SqlDataAdapter("select * from Employee2",con);da.Fill(ds.Tables.Add());

After tables have been added into a DataSet, the below-given code TELLS about how to make use of the DataSet tables. If you decide to use the first table in a dataset or to copy the table data into a data table, then follow the below-given code:

The above code can be USED to add the required number of tables in a dataset. This ensures connection-less access to data. As the dataset is filled with multiple tables, every time we want to query the data the database connection is not required. It also makes sure about the reusability of data.

• Response.Expires property is specific to the minutes that a PARTICULAR page stays in the cache for the specific TIME from the time it has been requested. For example, if Response.Expires VALUE is set to 5 minutes, then the page is instructed to be in cache for 5 minutes from the time it has been requested.
• Response.ExpiresAbsolute property helps to provide the proper time at which a specific page cache has been EXPIRED. For example, Response.ExpiresAbsolute provides information LIKE 14 March 15:40:15. This time tells about when the page was in cache.

Before performing any task in the database, SQL Server will authenticate. Two types of authentication techniques are:

• WINDOWS Authentication: This default authentication is provided only through Windows domain accounts. This SQL Server security model is strongly integrated with Windows, so it is also referred to as integrated security. Particular Windows users and group accounts are allowed to login into SQL Server. Windows users who are already been authenticated or logged onto Windows do not have to PROVIDE additional credentials.
  The below-given SqlConnection.ConnectionString specifies Windows authentication without any need of providing a user name or password by the user.

• SQL Server and Windows Authentication Mode(Mixed-mode): Authentication will be provided with the help of the Windows and SQL Server Authentication combination. User name and password pair will be maintained within SQL Server. In ORDER to USE this mixed-mode authentication, you need to create SQL Server logins that are stored in SQL Server. After that, you can supply the user name and password to SQL Server at run time.

The below-given ConnectionString specifies Mixed mode authentication:

There are seven main objects in ADO.NET. They are:

1. DataSet: It is AVAILABLE under both System.DATA.ADO and the System.Data.SQL namespaces. DataSet is a database cache built-in memory for using it in disconnected operations. It holds the complete collection of tables, constraints, and relationships.
2. SQLDataSetCommand: It represents a stored procedure or a database query that can be USED to populate the DataSet object. It corresponds to the ADO’s Command object-provided functionalities.
3. SQLCommand: It represents a stored procedure or a T-SQL statement that will be executed by SQL Server. It corresponds to another set of functionalities provided by the ADO’s Command object.
4. SQLParameter: It can be used to pass parameters to the object of SQLCommand or SQLDataSetCommand class. When you are passing a parameter for SQLCommand using SQLParameter, SQLParameter will represent a parameter that can be used by T-SQL statement or stored procedure. Whenever a parameter has been passed for SQLDataSetCommand using SQLParameter, SQLParameter will represent a column from a result set.
5. SQLConnection: It represents an open connection to the data source like SQL Server. This object is SIMILAR to the standard Connection object in ADO.
6. SQLDataReader: It reads a forward-only stream of data from a SQL Server database. It works with an open database connection.
7. SQLError: It collects runtime warnings and error conditions related INFORMATION that will be encountered by an ADO.NET application. It corresponds to ADO’s Error object.

• A single value from the first row and first column of the ResultSet will be returned by ExecuteScalar() method on query execution.
• If the ResultSet is having multiple rows or columns, all those rows and columns will be ignored except the first row and first column. If the ResultSet is empty, this function will RETURN NULL.
• The best SITUATION to USE ExecuteScalar() method is when we are using functions such as COUNT(), SUM(), etc., as it uses only a few resources compared to the ExecuteReader() method.
• Example:

Here, we create an object of the class SqlConnection and SqlCommand. We pass SQL Statement to the object of SqlCommand class, which returns a single value. When ExecuteScalar() function gets executed, a single value will be returned, i.e, the total salary of employees. This value will be displayed using a message box.

CONNECTED Architecture:

• In connected architecture, the CONNECTION must be kept open for accessing the data retrieved from the database. Connected architecture is based on Connection, DataReader, Command, and Transaction classes.
• You constantly visit the database for any CRUD (Create, Read, Update, and Delete) OPERATION you want to do. This will create high traffic to the database, but this is usually faster as you are doing only smaller transactions.
• DataReader can be SAID as a Connected Architecture as it holds the connection open until it fetches all the rows one by one.

Disconnected Architecture:

• In disconnected architecture, even if the database connection is closed, data retrieved from the database can be accessed. Disconnected architecture is based on classes connection, CommandBuilder, DataAdapter, DataSet, and DataView.
• Here, we RETRIEVE and store a recordset from the database so that you can perform many CRUD (Create, Read, Update, and Delete) operations on the data within memory, it will be re-synchronized when you reconnect with the database.
• DataSet is a Disconnected Architecture because all records are brought at once and holding the database connection alive is not necessary.

ADO.NET is based on an Object Model where data residing in the database is accessed using a data provider. It is a technology of data access given by the Microsoft .Net Framework, which helps to communicate between relational and non-relational systems using a common group of components.

The components of ADO.NET architecture are:

• Data Provider: It provides data to all the applications that perform the database updates. The application can access data through the DataSet or DataReader object. A data provider is a having group of components such as Command, Connection, DataReader, and DataAdapter OBJECTS. Command and Connection objects are the necessary components irrespective of the operations like Insert, Delete, Select, and Update.
• Connection: The connection object is needed to connect with the database such as SQL Server, MySQL, Oracle, etc. To create a connection object, you must know about where the database is located(Ex: IP address or machine name, etc.) and the security credentials(Ex: user name and password-based authentication or windows authentication).
• Command: The command object is the component where you will write the SQL QUERIES. Then by using the command object, execute the queries over the connection. By using the command object and SQL queries, you will be able to fetch the data or send the data to the database.
• DataReader: DataReader is a connected read-only RecordSet that is HELPFUL in reading the records in the forward-only mode.
• DataAdapter: The DataAdapter acts as a bridge between the dataset and command object. It receives the data from the command object and puts it into the data set.
• DataSet: The DataSet is a DISCONNECTED RecordSet that can be browsed in both forward and backward directions. We can also update the data using the dataset. DataSet is filled by using DataAdapter.
• DataView Class: A DataView allows you to create various views of data from DataTable, which can be used for data-binding applications. Using this, you can display the table with different order of sorting or you can filter the data based on a filter expression or by row state, etc.
• XML: It is possible to create an XML representation of a dataset. In the dataset’s XML representation, data is represented in XML format and the database schema is represented in XML Schema Definition(XSD) language.