ADO.net Subjective Questions And Answers
Define methods provided by the dataset object to generate XML in ADO.Net?
1.ReadXML: Read’s a XML document in to Dataset. 2.GetXML: This is a function, which returns the string containing XML document. 3.Writexml: This writes a XML data to disk.
| More interview questions and answers |
|---|
ADO.NET is data access layer |
ActiveX Data Objects |
Microsoft developed ActiveX Data Objects (ADO) as a COM( Component Object Model ) wrapper around OLE DB for Databases. |
using System;
using System.Data;
using System.Data.OleDb;
// Step 1.Open Database Connection
OleDbConnection conn = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\inventory\\inventory\\db\\invent.mdb;");
// Step 2.Connection database opened
conn.Open();
// Step 3.Create DataSet and Command objects
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM empr4r",
conn);
// Step 4.Execute the command
cmd.ExecuteScalar();
// Step 5.Closed this connection
conn.Close();
|
The System.Data is main namespace of ADO.Net and it has DataSet and classes (DataTable, DataColumn, DataRow, DataRelation, Constraint,etc ). System.Data has a namespace System.Data.Common. |
ADO.NET has three different ways of accessing database information directly which are: 1.Commands :-have classes SqlCommand and OleDbCommand , used directly to retrieve results from database queries.Command classes always support the IDbCommand interface.Commands classesare used to get a scalar result (the first column of the first row of a result set) or out parameters of a stored procedure.sort of data is retrieved using IDbCommand.ExecuteScalar() and IDbCommand.ExecuteNonQuery() 2.DataReaders has SqlDataReader and OleDbDataReader.These classes are provide something similar to ADO's Recordset using a forward-only cursor. 3.DataSets classes Microsoft uses DataReaders within the managed providers' DataAdapters to fill DataSet |
Define ADO.NET provides data access services in the Microsoft .NET platform? |
ADO.NET to access data by using the new .NET Framework data providers which are given below: 1.Data Provider for SQL Server (System.Data.SqlClient) 2.Data Provider for OLEDB (System.Data.OleDb) 3.Data Provider for ODBC (System.Data.Odbc). 4.Data Provider for Oracle (System.Data.OracleClient). |
The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll. ADO.NET is a set of classes that expose data access services to the .NET developer. There are two central components of ADO.NET classes: the DataSet, and the .NET Framework Data Provider. |
Data Provider is a set of components including: 1.The Connection object (SqlConnection, OleDbConnection, OdbcConnection, OracleConnection) 2.The Command object (SqlCommand, OleDbCommand, OdbcCommand, OracleCommand) 3.The DataReader object (SqlDataReader, OleDbDataReader, OdbcDataReader, OracleDataReader) 4.The DataAdapter object (SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter). DataSet object represents a disconnected cache of data which is made up of DataTables and DataRelations that represent the result of the command. |
Dataset is: 1.Disconnected Recordset objects similar to an array. 2.Supports disconnected data access and operations. 3.Scalability, Provide greater scalability because users no longer have to be connected to the database all the time. DataSet object is made up of two objects: 1.DataTableCollection object containing null or multiple DataTable objects 2.DataRelationCollection object containing null or multiple DataRelation objects which establish a parent/child relation between two DataTable objects. |
There are two type of dataset in Ado.net: 1.Typed DataSet: Typed DataSet is derived from the base DataSet class and then uses information in an XML Schema file (.xsd file) in order to generate a new class. Create a typed DataSet without designer - manually a.Call the command prompt (cmd) at the location of the XSD schema file. b.Use the XSD.EXE utility to create the class for the typed DataSet. 2.Untyped DataSet : Untyped DataSet is not defined by a schema, instead, you have to add tables, columns and other elements to it yourself, either by setting properties at design time or by adding them at run time. |
There are following way to populate Dataset: a.By using DataAdapter objects and Fill method. b.By creating DataTable, DataColumn and DataRow objects programmatically. c.Read an XML document or stream into the DataSet. d.Merge (copy) the contents of another DataSet, with the Merge method. |
DataAdapter object is links the database and a Connection object with the ADO.NET-managedDataSet object through its SELECT and action query Commands. Means it works like a bridge DataAdapter specified that which data is to move into and out of the DataSet. DataAdapter provide references to SQL statements or stored procedures that are invoked to read or write to a database. The DataAdapter provides four properties that allow us to control how updates are made to the server: SelectCommand UpdateCommand InsertCommand DeleteCommand |
The DataAdapter includes three main methods: 1.Fill (populates a DataSet with data). 2.FillSchema (queries the database for schema information that is necessary to update). 3.Update (to change the database, DataAdapter calls the DeleteCommand, the InsertCommand and the UpdateCommand properties). |
To bind some elements of a data source with some graphical elements of an application, this ability known as DataBinding. The data in Windows Forms is bound by calling DataBindings. Windows Forms allows you to bind easily to almost any structure that contains data. Windows Forms Controls support two types of data binding: a.Simple Data Binding: To display a single data element, means to display a column value from a DataSet table, in a control. It is possible to bind any property of a control to a given data value. Simple Data Binding can be performedby two ways: 1.At design time using DataBindings property of a control 2.Dynamically at run time. This is the type of binding typical for controls such as a TextBox control or Label control that displays typically only a single value. b.Complex Data Binding: To bind more than one data element, typically more than one record in a database, or to more than one of any other type of bindable data element. DataGrid, ListBox and ErrorProvider controls support complex data binding. |
ADO.net includes many objects you can use to work with data. Some of the primary objects are: 1. The SqlConnection Object 2. The SqlCommand Object 3. The SqlDataReader Object 4. The DataSet Object 5. The SqlDataAdapter Object |
SqlConnection object is like a c# object.The object declare like that: SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"); This argument is called a connection string. It is define as: Data Source: Identifies the server. Integrated Security: Set to SSPI to make connection with user's Windows login User ID: Name of user configured in SQL Server. Password: Password matching SQL Server User ID. Using a SqlConnetion the connection creates as: 1.Instantiate the SqlConnection. 2.Open the connection. 3.Pass the connection to other ADO.NET objects. 4.Perform database operations with the other ADO.NET objects. 5.Close the connection. |
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
Above syntax is written to instantiating a SqlCommand object. It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object.
|
Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
Passing the command string and connection object to the constructor. Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd.
|
How to Inserting Data when using SqlCommand Object in ADO.Net? |
To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object. Instantiate a new command with a query and connection SqlCommand cmd = new SqlCommand(insertString, conn); Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); |
How to Updating Data when using SqlCommand Object in ADO.Net? |
The ExecuteNonQuery method is also used for updating data. The following code shows how to update data: 1.Prepare command string 2.Instantiate a new command with command text only SqlCommand cmd = new SqlCommand(updateString) 3.Set the Connection property cmd.Connection = conn; 4.Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); |
How to Deleting Data when using SqlCommand Object in ADO.Net? |
To deleting data also using the ExecuteNonQuery method. The following example shows how to delete a record from a database with the ExecuteNonQuery method: 1.Prepare command string 2.Instantiate a new command SqlCommand cmd = new SqlCommand(); 3.Set the CommandText property cmd.CommandText = deleteString; 4.Set the Connection property cmd.Connection = conn; 5.Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); |
There is a little different way to creating object of a SqlDataReader like other object creation in C#. Necessary call ExecuteReader on a command object. SqlDataReader rdr = cmd.ExecuteReader(); The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance. |
SqlDataReader rdr = cmd.ExecuteReader();
The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop.
while (rdr.Read())
{
string conta = (string)rdr["ContactName"];
string comp = (string)rdr["CompanyName"];
string cty = (string)rdr["City"];
Console.Write("{0,-25}", conta);
Console.Write("{0,-20}", cty);
Console.Write("{0,-25}", comp);
Console.WriteLine();
}
|
Dataset object create as: DataSet dsStudents = new DataSet(); DataSet constructor doesn't require parameters. However there is one overload that accepts a string for the name of the DataSet, which is used if you were to serialize the data to XML. Since that isn't a requirement for this example, I left it out. Right now, the DataSet is empty and you need a SqlDataAdapter to load it. |
SqlDataAdapter holds the SQL commands and connection object for reading and writing data. It initializes with a SQL select statement and connection object:
SqlDataAdapter daStudent = new SqlDataAdapter(
"select StudentID, CollegeName from Students", conn);
The SQL select statement specifies what data will be read into a DataSet.
The connection object, conn, should have already been instantiated, but not opened.
SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daStudent, instance.
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daStudent);
SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands.
|
Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet. Here's how to do it, by using the Fill method of the SqlDataAdapter:
SqlDataAdapter daStudent = new SqlDataAdapter
("select StudentID, CollegeName from Students", conn);
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daStudent);
daStudent.Fill(dsStudent, "Students");
Fill method, takes two parameters: a DataSet and a table name.
|
Some basic namespaces are: 1.System.Data (basics types) 2.System.Data.OleDb (OLEDB Provider) 3.System.Data.SqlClient (Microsoft Sql Server provider) 4.System.Data.Common 5.System.Data.SqlTypes 6.System.Data.Odbc (ODBC Provider) 7.System.Data.Odbc.OracleClient (Oracle Provider) 8.System.Data.SqlServerCe (Compact framework) |
Compare the Connection-Oriented and Disconnected Scenario in ADO.Net? |
Connection-Oriented Scenario: 1.Always accessing current data 2.Low number of concurrent data accesses 3.Many write accesses situation 4.IDbConnection, IDbCommand, IDataReader Disconnected Scenario: 1.Modification in Dataset is not equal to modification in data source 2.Many concurrent read accesses situation 3.Dataset, Data table, DbDataAdapter |
1.To cache data and pass to a different tier Dataset forms the best choice and it has decent XML support 2.Dataset is a disconnected architecture; DataReader has live connection while reading data. 3.To access data from more than one table Dataset forms the best choice. 4.If we need to move back while reading records, data reader does not support this functionality. 5.A biggest drawback of Dataset is speed. As Dataset carry considerable overhead because of relations, multiple table etc speed is slower than DataReader. Always try to use DataReader wherever possible, as it is meant especially for speed performance. |
objCommand.CommandText = "Tab1"
objDataAdapter.Fill(objDataSet, "Tab1")
objCommand.CommandText = "Tab2"
objDataAdapter.Fill(objDataSet, "Tab2")
Above code shows how to load multiple Data Table objects in one Dataset object. Sample code shows two tables Tab1 and Tab2 in object ObjDataSet.
lstdata.DataSource = objDataSet.Tables("Tab1").DefaultView
In order to refer Tab1 Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.
|
Explain the difference between an ADO.NET Dataset and an ADO Recordset? |
1.Using dataset retrieve data from two databases like Oracle and SQL server and merge them in one dataset, with recordset this is not possible. 2.Recordset uses COM. But all representation of Dataset is using XML. 3.Dataset can be transmitted on HTTP while Recordset cannot be transmitted. |
1.We have recordset in ADO and in ADO.NET we have dataset. 2.In recordset we can only have one table. If we want to accommodate more than one table we need to do inner join and fill the recordset. Dataset can have multiple tables. 3.All data persist in XML as compared to classic ADO where data persisted in Binary format also. |
Connection Pooling make a single connection instance, which allows that instance to connect to all the databases. It does not open and close the connection object multiple times. |
Define methods provided by the dataset object to generate XML in ADO.Net? |
1.ReadXML: Read’s a XML document in to Dataset. 2.GetXML: This is a function, which returns the string containing XML document. 3.Writexml: This writes a XML data to disk. |
ADO stands for ActiveX Data Object .ADO.NET is an object-oriented set of libraries that allows to interact with data sources. The data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.ADO .NET consists of classes that allow a .NET application to connect to the data source, executes commands and manage disconnected data. One of the key Differences between ADO.NET and other database technologies is how it deals with Challenge with different data sources, that means, the code you use to connect to an SQL Database will not differ that much to the one connecting to an Oracle Database. |