Ado.net Pdf C Free Download

byNorman Elumbaugh -
0

Introduction to ADO.NET

ADO.NET is a technology used for data and is provided by the Microsoft .NET Framework. It is a part of the .NET framework that supports the communication between relational and non-relational systems with the help of a set of software components. It supports disconnected architecture using which programmers are allowed to access data and data services from a database without depending on the data source.

ADO.NET is comprised of a group of built-in classes that are useful for establishing the database connection, for gaining access to XML, relational data, and application data, and for retrieval of a result. It can be used in various programming languages such as Visual Basic.NET, Visual C++, etc., that are supported by the .NET framework.

Advantages of ADO.NET

ADO.NET has various advantages which can be categorized into the following categories:

  • Interoperability: It provides the ability to communicate across heterogeneous environments, once the connection has been established between them.
  • Scalability: It provides the ability to serve an increasing number of clients without reducing the performance of the system. So we can say that ADO.NET is highly scalable because it is flexible enough to be easily expanded when there is a requirement for the same.
  • Productivity: It provides the ability to rapidly develop robust applications for data access using rich and extensible component object models provided by the ADO.NET.
  • Performance: An improvement over earlier ADO.NET versions because of the disconnected data model. It can establish connections quickly to fetch data without any delay.

Scope of ADO.NET

ADO.NET being one of the products of Microsoft, it is good enough to position itself strongly in the market. ADO.NET has massive community support, so it is definitely having a large scope ahead. You could learn ADO.NET along with hands-on experience on the .Net framework in order to have a good scope. Any full-stack developer who has a better grip over both front-end and back-end technology can precisely learn ADO.NET.

ADO.NET Interview Questions for Freshers

1. What is ADO.NET?

  • ADO.NET stands for ActiveX Data Object, it is a part of the .NET Framework by Microsoft. ADO.NET framework provides a set of classes that are used to handle data communication with data sources such as XML files and databases (such as SQL, Oracle, MySQL, MS Access, etc.).
  • ADO.NET can separate mechanisms for data connectivity, data access, and data manipulation.
  • It has introduced the disconnected architecture, in which data can be stored in a DataSet. ADO.NET has providers for database connection, commands for execution, and result retrieval.
  • The ADO.NET classes are stored in the DLL named System.Data.dll.
  • Various applications like ASP.NET applications, console applications, windows applications, etc., will use ADO.NET for database connection, command execution, and retrieval of data.

2. What is DataSet in ADO.NET?

  • The DataSet is a collection of database tables(row and column format) that contain the data. It is helpful for fetching the data without any need for Data Source interaction, that is why it is called a disconnected data access method.
  • It is an in-memory data store that can contain multiple tables at the same time. DataRelation objects can be used to relate these tables.
  • For creating a DataSet object, ADO.NET provides a DataSet class that consists of constructors and methods to carry out data-related operations.
  • It can be used with various data sources, with XML data, or to manage the application's local data. The DataSet will include related tables, data constraints, and relationships among the tables.

3. Give the differences between ADO and ADO.NET.

ADO ADO.NET
It is Component Object Modelling(COM) based. It is Common Language Runtime(CLR) based.
It works in connected mode to access the data store. It does require an active connection, works in disconnected mode to access the data store.
It uses the RecordSet object to access and store data from the data sources. It uses a DataSet object to access and store data from the data sources.
It provides a feature of locking. It does not provide a feature of locking.
Data is stored in binary form. Data is stored in XML.
It does not support XML integration. It supports XML integration.
Using a single connection instance, it is not possible to send multiple transactions. Using a single connection instance you can send multiple transactions.
We can create only client-side cursors. Both client-side and server-side cursors can be created.
It supports sequential row access in a RecordSet. Non-sequential data access is supported in DataSet by using a collection-based hierarchy.
It will make use of SQL JOINs and UNIONs for combining data from multiple tables. It is not possible to fetch records from multiple tables independently. It will make use of DataRelational objects to combine data from multiple tables without the help of JOINs and UNIONs. Therefore records from multiple tables are maintained independently.

4. What is a DataAdapter in ADO.NET?

  • A DataAdapter is used to access data from a data source by functioning as a bridge between DataSet and a data source. DataAdapter class includes an SQL command set and a database connection. It is helpful to fill the DataSet and resolve changes to the data source.
  • The DataAdapter will make use of the Connection object that belongs to the .NET Framework data provider for connecting with a data source. Along with that, it will also use Command objects to retrieve data from the data source as well as to resolve changes to the data source.
  • DataAdapter properties that permit the user to control the database are the Select command, Update command, Insert command, and Delete command.
  • Example code for the usage of DataAdapter:
                  using System;   using System.Data.SqlClient;   using System.Data;   namespace DataAdapterExample   {       public partial class DataAdapterDemo : System.Web.UI.Page       {           protected void Page_Load(object sender, EventArgs e)           {               using (SqlConnection conn = new SqlConnection("data source=.; database=items; integrated security=SSPI"))               {                   SqlDataAdapter da = new SqlDataAdapter("Select * from items", conn);                   DataSet s = new DataSet();                   da.Fill(s);                   GridView1.DataSource = s;                   GridView1.DataBind();               }           }       }   }

Here, DataAdapter will receive the data from the items table and fill the DataSet, which will be later used to display the information retrieved from the items database.

5. Explain the difference between ADO.NET and ASP.NET.

ADO.NET(ActiveX Data Objects) ASP.NET(Active Server Pages)
ADO.NET is a Library within the .NET framework. ASP.NET is a Framework.
It is a technology useful for accessing data from databases. It is a technology useful for the creation of dynamic web pages.
Here, data can be converted into XML format. Here, We can write our code into VB.Net, C#, ASP.Net, etc.
It is used to develop reliable and scalable database applications with high performance for client-server applications. It is used to create dynamic web pages, web applications, websites, and web services.

6. Explain about DataSet types in ADO.NET.

DataSet can be said as a collection of database tables(row and column format) that holds the data. There are two types of DataSet in ADO.NET. They are:

  1. Typed DataSet: A typed DataSet is derived from the DataSet base class and can be created by selecting the DataSet option provided by Visual Studio. It will be created as an XML schema(.xsd file) that contains DataSet structure information such as rows, columns, and tables. Data from the database is moved into a dataset and from the dataset to another component in the XML format.
  2. Untyped DataSet: Untyped DataSet does not have an associated XML schema with it. Users are supposed to add columns, tables, and other elements to it. Properties can be set during design time or can add them during run time.

Example program for the usage of DataSet:

                  using System;   using System.Data.SqlClient;   using System.Data;   namespace DataSetDemo   {       public partial class DataSetExample : System.Web.UI.Page       {           protected void Page_Load(object sender, EventArgs e)           {               using (SqlConnection conn = new SqlConnection("data source=.; database=employee; integrated security=SSPI"))               {                   SqlDataAdapter da = new SqlDataAdapter("Select * from employee", conn);                   DataSet d = new DataSet();                   da.Fill(d);                   GridView1.DataSource = d;                   GridView1.DataBind();               }           }       }   }

Here, DataSet will be filled by DataAdapter that receives data from the employee table. This DataSet will be used to display the information received from the employee database.

7. Explain the difference between DataTable and DataSet.

DataTable DataSet
DataTable consists of a single database table that is placed within a memory. DataSet consists of a collection of multiple database tables which is placed within a memory.
It has a row and column collection. It has a database table collection.
It allows fetching only a single TableRow at a time. It allows fetching multiple TableRows at a time.
It is a single database table, so there will not be any relation with other tables. It represents a collection of DataTable objects, so there might be a relation between them to obtain a particular result.
In this, DataSource objects are not serialized. In this, DataSource objects are serialized.
UniqueConstraint and ForeignKeyConstraint objects are not available enforcing data integrity. UniqueConstraint and ForeignKeyConstraint objects are available for enforcing data integrity.

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

Various namespaces available under ADO.NET is given below:

  1. System.Data : It contains the definition for rows, columns, relations, views, tables, constraints, and databases.
  2. System.Data.SqlClient : It is a collection of classes that are helpful in connecting to a Microsoft SQL Server database such as SqlConnection, SqlCommand, SqlDataAdapter, etc.
  3. System.Data.Odbc : It consists of classes that are required for connecting with most Odbc Drivers. These classes include OdbcConnection, OdbcCommand.
  4. System.Data.OracleClient : It has classes required for connection with an Oracle database, OracleConnection, OracleCommand.

9. What is object pooling?

Object pooling is a repository of the objects in memory that can be reused later without creating them. This object pooling reduces the burden of creating objects when it is required. Whenever there is a requirement of an object, the object pool manager will process the request and serve accordingly. It is designed for optimizing the use of limited resources so that the demands of client requests will be fulfilled.

10. Differentiate DataSet and DataReader.

DataSet DataReader
DataSet provides read/write access to data, so we can update the data. DataReader provides read-only access to data, so we can't update the data.
It has a disconnected architecture, which means the data obtained from the database can be accessed even after the database connection was closed. It has a connected architecture, which means to access the data retrieved from the database, the connection must be opened.
It supports various database tables from different databases. It supports only a single table from a single database.
It provides slower access to data due to overhead. It provides faster access to data.
Both forward and backward scanning of data is possible. Only forward scanning of data is possible.

11. What are the different execute() methods available in ADO.NET?

Different execute() methods supported by SqlCommandObject in ADO.NET is given below:

  • ExecuteScalar() : This method returns only a single value from the first row and first column of the ResultSet after the execution of the query. Even if ResultSet is having more than one row or column, all those rows and columns will be ignored. If the ResultSet is empty, it will return NULL.
  • ExecuteNonQuery() : This method returns the number of rows affected by the execution of a query. This method is not useful to return the ResultSet.
  • ExecuteReader() : This method returns an object of DataReader which is a read-only and forward-only ResultSet. It needs a live connection with the Data Source. We cannot directly instantiate the DataReader object. A valid DataReader object can be created with the help of the ExecuteReader() method.
  • ExecuteXmlReader() : This method builds an object of the XmlReader class and will return the ResultSet in the form of an XML document. This method is made available in SQL Server 2000 or later.

12. What is a transaction in ADO.NET? Explain the types of transactions available in ADO.NET.

In ADO.NET, transactions are used when you want to bind several tasks together and execute them in the form of a single unit. The transaction provides data consistency by ensuring either all of the database operations will be succeeded or all of them will be failed. For example, consider an application that performs two tasks. First, it updates an item_order table with order information. Second, it updates an item_inventory table that holds inventory information, where a number of items ordered will be debited. If any one of the tasks fails, then both updates must be rolled back.

Two types of transactions supported by ADO.NET are as follows:

  • Local Transaction:
    • A local transaction is a single-phase transaction that is directly handled by the database. Every .NET Framework data provider has its own Transaction object for bringing out local transactions.
    • For example, if we want to perform a transaction using SQL Server database, we import a System.Data.SqlClient namespace. Similarly, to perform an Oracle transaction, import the System.Data.OracleClient namespace. A DbTransaction class will be used for writing code that is independent of the provider and that requires transactions.
  • Distributed Transaction:
    • A distributed transaction is coordinated by a transaction monitor and will make use of fail-safe mechanisms like two-phase commit for transaction resolution. This transaction will affect multiple resources.
    • If the user can make use of a distributed transaction, if he wants to do a transaction across multiple data servers such as Oracle, SQL Server, etc.
    • If you want a distributed transaction to commit, all participants must guarantee that data modification made will be permanent. Changes must remain unchanged even if the system crash or other unforeseen events occur. Even if a single participant will make this guarantee fail, then the entire transaction will fail, and updates made to data within the transaction scope are rolled back.

13. Explain the difference between OLEDB (Object Linking and Embedding DataBase) and ODBC (Open DataBase Connectivity).

OLEDB ODBC
An API(Application Programming Interface) that allows accessing data from different sources in a uniform manner. It is an API for accessing DBMS (DataBase Management System).
It supports both relational and non-relational databases. It supports only relational databases.
It is procedural-based. It is component-based.
It is easier to deploy. It is difficult to deploy.
It gives a higher performance on loading and extracting the data. It performs less compared to OLE DB on loading and extraction of data.
OleDbConnection = New OleDbConnection(connetionString) is used to make connection with OLE DB data source. resource odbc_connect(string datasource , string username , string password , [int cursor_type ]) is used to make a connection to an ODBC data source. On success, this function will return a connection resource handle that is helpful in accessing the database using subsequent commands.

14. What is data binding in ADO.NET?

  • Data binding in ADO.NET is the process through which user interface (UI) controls of a client application are configured to update or fetch data from data sources like a database or XML document. Using data binding, the user will be able to bind values to the particular control.
  • There are two types of data binding based on the type of binding offered:
    1. Simple data binding: It is the process of binding the control with only one value in the dataset. The controls such as label, text box will be made bound to the control using the control properties.
    2. Complex data binding: It is the method of binding the component with the Database. The controls can be a Dropdown list, GridView, or combo box. One or more than one value can be displayed from the dataset using the complex data binding.

15. What is Connection pooling?

The task of grouping database connections in the cache memory is to make them available whenever there is a requirement of connection. Opening a new database connection every time is a time-consuming process. Connection pooling allows you to reuse existing and active database connections, whenever there is a need, and thus increases the application performance.
By setting the pooling property into true or false in the connection string, we can enable or disable the connection pooling in the application. It is enabled by default in every application.

16. What is DataTable in ADO.NET?

DataTable in ADO.NET represents a single table in a DataSet that has in-memory relational data. The data within DataTable is local to the .NET framework-based application to which it belongs but can be populated using a DataAdapter from different data sources such as Microsoft SQL Server. The DataTable class belongs to the System.Data namespace within the library of .NET Framework.

DataTable can be represented in .aspx.cs code as given below:

                  protected void DataTableExample()   {       SqlConnection conn = new SqlConnection("Write the database connection string");       conn.Open();       SqlCommand cd = new SqlCommand("Write the query or procedure", conn);       SqlDataAdapter d = new SqlDataAdapter(cd);       DataTable dt = new DataTable();       d.Fill(dt);       grid.DataSource = dt;       grid.DataBind();   }

The SQL connection and SQL command object will be created. We pass the SQL query to the object of the SQL command class. A new data table object will be created by using the DataTable class and it is filled with data using a data adapter.

17. Name some of the properties and methods provided by the DataReader in ADO.NET?

Some of the properties provided by the DataReader are as follows:

  • Depth: It represents the depth of nesting for a row.
  • FieldCount: It gives the total column count in a row.
  • Item: It obtains the column value in a native format.
  • RecordsAffected: It gives the number of transaction affected rows.
  • IsClosed: It represents whether a data reader is closed.
  • VisibleFieldCount: It is used to obtain the number of unhidden fields in the SqlDataReader.

Some of the methods provided by the DataReader are as follows:

  • Read(): This method reads a record from the SQL Server database.
  • Close(): It closes a SqlDataReader object.
  • NextResult(): It moves the data reader to the next result during the time of batch transactions.
  • Getxxx(): Various types of Getxxx() methods such as GetBoolean(Int32), GetChar(Int32), GetFloat(Int32), GetDouble(Int32), etc., are provided by the DataReader. These methods will read a value of a particular data type from a column. For example, GetFloat() will return a column value as a Float and GetChar as a character.

18. What are the conditions for connection pooling?

The conditions for connection pooling are:

  • There must be several processes with the same parameters and security settings so that they can share the same connection.
  • The connection string should be identical.

19. What are the data providers in ADO.NET?

Data providers are used to transferring the data between the client application and the data store. It encapsulates the database-specific details. Data providers are helpful for database connection, data retrieval, storing the data in a dataset, reading the retrieved data, and updating the database.

The data providers that comes along with the ADO.NET Framework are:

  • OLE DB: The OLEDB provider is available under System.Data.OleDb namespace. This provider can be used to access Microsoft Access, DB2/400, SyBase, and SQL Server 6.5 and earlier.
  • ODBC: The ODBC provider is available under System.Data.Odbc namespace. This provider is used when there will not be any newer provider is available.
  • SQL Server: The Microsoft SQL Server provider is available under System.Data.SqlClient namespace. Classes available under this provider will provide the same functionality as the generic OLEDB provider.

20. Why Stored Procedure is used in ADO.NET?

The reasons for using Stored Procedures in ADO.NET are given below:

  • For improved performance
  • For security reasons
  • Easier to use and maintain
  • Lesser Network Traffic
  • Execution time is less

ADO.NET Interview Questions for Experienced

21. Explain ADO.NET Architecture.

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.

22. Briefly explain connected and disconnected architecture of ADO.NET.

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.

23. Explain about ExecuteScalar() in ADO.NET.

  • 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:
                  public void ExecuteScalarExample()   {       SqlConnection con = new SqlConnection();       con.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;       try       {           SqlCommand cd = new SqlCommand();           cd.Connection = con;           cd.CommandText = "SELECT SUM(SALARY) FROM EMPLOYEE";           cd.CommandType = CommandType.Text;           con.Open();           Int32 SalaryTotal = Convert.ToInt32(cd.ExecuteScalar());           MessageBox.Show("Total Salary of the employee is : " + SalaryTotal.ToString());           cd.Dispose();           con.Dispose();       }       catch (Exception ex)       {           MessageBox.Show(ex.Message);       }   }

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.

24. Explain about ADO.NET objects.

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.

25. What are the different authentication techniques used to connect with MS SQL Server?

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.
                  C# "Server=MSSQL1;Database=Institute;Integrated Security=true;
  • 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:

                  C# "Persist Security Info=False;User ID=Harsh;Password=xyz@123;Initial Catalog=Institute;Server=MySqlServer"

26. What is Response.Expires and Response.ExpiresAbsolute property?

  • 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.

27. How to load multiple tables into a dataset?

                  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:

                  DataTable dt=new DataTable(); dt=ds.Tables[0];

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.

28. What is the difference between connected and disconnected architecture in ADO.NET?

Connected architecture Disconnected architecture
It is connection-oriented. It is not connection-oriented.
DataReader is a connected architecture. DataSet is a disconnected architecture.
High speed and performance are given by connected methods. Disconnected methods are low in speed and performance.
Data persistence is not possible using DataReader. Data persistence is possible using DataSet.
It carries the single table data. It carries data from multiple tables.
We can't update the data as it is read-only. Here we can update the data.

29. What is LINQ?

  • 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.

30. How can you identify whether any changes are made to the DataSet object since the time it was last loaded?

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.

31. What is the difference between Dataset.Clone() and DataSet.Copy() methods?

  • 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.

32. Which methods are provided to add or remove rows from the DataTable object?

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.

33. How to make SQL Server connection in ADO.NET?

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 (SqlConnection con = new SqlConnection(connectionString))     {       con.Open();          }

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.

                  // ConnectionExample.cs  using System;   using System.Data.SqlClient;   namespace ConsoleApplicationExample   {       class ConnectionExample       {           static void Main(string[] args)           {               new Program().ConnectingMethod();           }           public void ConnectingMethod()           {               using (                        // Creating Connection                        SqlConnection conn = new SqlConnection("data source=.; database=employee; integrated security=SSPI")                    )               {                   conn.Open();                   Console.WriteLine("Connection Has Been Successfully Established.");               }           }       }   }

Output: 

                  Connection Has Been Successfully Established. Press any key to continue...

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.

34. What is serialization? Write an example program to serialize a DataSet.

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:

                  using System; using System.Data; using System.Data.SqlClient; using System.Xml.Serialization; using System.IO; public partial class Default : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         SqlConnection conn = new SqlConnection("Data Source=data_source_name;Initial Catalog=employee;Integrated Security=True");  //Create connection object         SqlDataAdapter da = new SqlDataAdapter("select * from emp", conn);  //DataAdapter creation         DataSet s = new DataSet();         da.Fill(s);           FileStream fObj = new FileStream("C:\\demo.xml", FileMode.Create);   // Create a XML file         XmlSerializer sObj = new XmlSerializer(typeof(DataSet));         sObj.Serialize(fObj, s);  //Serialization of a DataSet         fObj.Close();     } }

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.

35. Give an example code to fill the GridView by using the object of DataTable during runtime.

                  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:

Conclusion

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

ADO.NET MCQ

Posted by: lynwoodlynwoodvanhoutene0272597.blogspot.com

Source: https://www.interviewbit.com/ado-net-interview-questions/

Post a Comment

Previous Post Next Post