1. What is ADO.Net?
ADO.Net is set of components (classes) that are used to access data based on disconnected dataset and XML.It is advancement of classical ADO that is (Active Data Objects).ADO was tightly coupled client server system which was connected architecture. Unlike ADO, ADO.NET was built with the disconnected architecture, using Datasets. There are two main components of ADO.NET as
· .NET Framework Data Providers
· Dataset
2. What is .Net Framework Data Providers?
A data provider is a bridge between an application and a data source. A data provider is used to querying and manipulating data from and in data source. There are several providers provided in .Net framework
· For SQL Server
· For Oracle
· For ODBC
· For OLEDB
Each provides some core elements as
· Connection: provides connectivity to a data source.
· Command: enables access to database commands, stored procedures.
· DataReader: provides a high-performance stream of data from the data source.
· DataAdapter: provides the bridge between the Dataset object and the data source.
3. What are connected and disconnect modes?
In connected Mode, your code base explicitly connects to and disconnects from the underlying data store. In this, you typically interact with the data store using connection objects, command objects, and data readerobjects.
In disconnected Mode, you can manipulate a set of DataTable objects (contained within a Dataset) that functions as a client-side copy of the external data. When you obtain a Dataset using a related data adapterobject, the connection is automatically opened and closed on your behalf. This approach helps free up connections for other callers quickly and goes a long way toward increasing the scalability of your systems.
Once a caller receives a Dataset, it is able to traverse and manipulate the contents without incurring the cost of network traffic. Also, if the caller wishes to submit the changes back to the data store, the data adapter (in conjunction with a set of SQL statements) is used to update the data source; at this point the connection is reopened for the database updates to occur, and then closed again immediately.
4. What are the core objects of ADO.Net data Provider?
Image by: Pro C# 2010 and the .NET 4 Platform, 5th Edition
The specific names of these core classes will differ among data providers (e.g., SqlConnection vs. OracleConnection vs. OdbcConnection vs. MySqlConnection).
5. What is OLEDB data provider?
The OLE DB data provider in the System.Data.OleDb namespace, allows you to access data located in any data store that supports the classic COM-based OLE DB protocol. However, the OLE DB provider interacts with various COM objects behind the scenes, which can affect the performance of your application. By and large, the OLE DB data provider is only useful if you interact with a DBMS that does not define a specific .NET data provider.
6. What are core members of System.Data namespace?
Image by: Pro C# 2010 and the .NET 4 Platform, 5th Edition
7. Why use ConnectionStringBuilder class?
Connection strings are often represented as literals which are difficult to maintain. So we use ConnectionStringBuilder class as
SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder();
cnStrBuilder.InitialCatalog = "TestDB";
cnStrBuilder.DataSource = @"192.168.10.10";
cnStrBuilder.ConnectTimeout = 30;
cnStrBuilder.IntegratedSecurity = true;
using(SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = cnStrBuilder.ConnectionString;
cn.Open();
}
8. What is SqlCommand.Prepare () method?
Creates a prepared version of the command on an instance of SQL Server.
//code for sql connection,command
// Call Prepare after setting the Commandtext and Parameters.
command.Prepare();
command.ExecuteNonQuery();
9. Can you obtain multiple result set using DataReader?
Yes, you can use reader.NextResult ();
Example:
SqlConnection con=new SqlConnection(cnstr);
con.Open();
SqlCommand cmd=new SqlCommand("Select * From Inventory;Select * from Customers”,con);
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int prodid = rdr.GetInt32(0); // assuming the first column is of type Int32
// other fields ...
}
if (rdr.NextResult())
{
while (rdr.Read())
{
int custid = rdr.GetInt32(0); // assuming the first column is of type Int32
// other fields ...
}
}
}
10. What is Transaction?
A transaction is a set of database operations that must either all work or all fail as a collective unit. As you might imagine, transactions are quite important to ensure that table data is safe, valid, and consistent. A transaction should follow ACID properties as
A: Atomic (all or nothing);
C: Consistent (data remains stable throughout the transaction) means any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
I: Isolated (transactions do not step on each other’s feet) means the effects of an incomplete transaction might not even be visible to another transaction;
D: Durable (transactions are saved and logged).
11. Can you write a simple example of transaction?
Yes.
SqlCommand cmdInsert=GetInsertCommand();
SqlCommand cmdRemove=GetDeleteCommand();
SqlTransaction tx = null;
try
{
tx = sqlCn.BeginTransaction();
// Enlist the commands into this transaction.
cmdInsert.Transaction = tx;
cmdRemove.Transaction = tx;
// Execute the commands.
cmdInsert.ExecuteNonQuery();
cmdRemove.ExecuteNonQuery();
// Commit it!
tx.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
// Any error will roll back transaction.
tx.Rollback();
}
12. What is Data Adapter?
A data provider provides a data adapter object that handles the connection automatically. You can manipulate data in DataSet in disconnected mode and then the caller explicitly passes the dataset to the data adapter for updating the database, so the your data source is connected for this shortest duration of updating time.
Image by: Pro C# 2010 and the .NET 4 Platform, 5th Edition
13. What is DataSet?
A dataset is a class that maintains three strongly typed collections
DataTableCollection: contains individual data tables that be access using Table Property of DataSet. DataRelationCollection: contains relations between DataTables. you can create a relation object between two tables to model a foreign key constraint using the DataRelation type. You can add this object to the DataRelationCollection with the Relations property.
PropertyCollection: you to associate any extra information to the DataSet as name/value pairs. This information can be literally anything, even if it has no bearing on the database data itself. For example, you can associate your company’s name to a DataSet, which can then function as in-memory metadata. The ExtendedProperties property provides access to the PropertyCollection object.
14. What are the core methods of DataSet?
Image by: Pro C# 2010 and the .NET 4 Platform, 5th Edition
15. What are extended properties of DataSet?
The ExtendedProperties property enables you to store custom information with the DataSet.Example
static void Main(string[] args)
{
DataSet dsEx = new DataSet("Extended Test");
dsEx.ExtendedProperties["TimeStamp"] = DateTime.Now;
dsEx.ExtendedProperties["DataSetID"] = Guid.NewGuid();
Console.WriteLine(dsEx.ExtendedProperties["TimeStamp"].ToString());
Console.ReadLine();
}
16. How to serialize DataTable/DataSet into XML?
Both DataTable and DataSet provides the WriteXml() and ReadXml() methods. WriteXml() allows you to persist an object’s content to a local file as well as into any System.IO.Stream as an XML document. ReadXml() allows you to hydrate the state of a DataSet (or DataTable) from a given XML document.
static void SaveAndLoadAsXml(DataSet testDS)
{
// Save this DataSet as XML.
testDS.WriteXml("testDataSet.xml");
testDS.WriteXmlSchema("testDataSet.xsd");
// Clear out DataSet.
testDS.Clear();
// Load DataSet from XML file.
testDS.ReadXml("testDataSet.xml");
}
17. How to serialize DataSet/DataTable into Binary format?
XML data is descriptive in nature that make some overhead as well as security issue when DataSet object needs to be passed across a machine, so it needs binary serialization.
To persist DataTables or DataSets in a binary format, set the RemotingFormat property to
SerializationFormat.Binary.Example to serialization and deserialization
static void SaveAndLoadAsBinary(DataSet testDS)
{
// Set binary serialization flag.
testDS.RemotingFormat = SerializationFormat.Binary;
// Save this DataSet as binary.
FileStream fs = new FileStream("BinaryCars.bin", FileMode.Create);
BinaryFormatter bFormat = new BinaryFormatter();
bFormat.Serialize(fs, testDS);
fs.Close();
// Clear out DataSet.
testDS.Clear();
// Load DataSet from binary file.
fs = new FileStream("BinaryCars.bin", FileMode.Open);
DataSet data = (DataSet)bFormat.Deserialize(fs);
}
18. What is DataView?
A view object is an alternative representation of a table (or set of tables). In ADO.NET, the DataView type allows you to extract a subset of data programmatically from the DataTable into a stand-alone object.DataView class supports a property named RowFilter, which contains the string representing the filtering criteria used to extract matching rows from DataTable.
// Set the table that is used to construct this view.
dView = new DataView(testTable);
// Now configure the views using a filter.
dView.RowFilter = "col1 = 'val'";
// Bind to the new grid.
19. What is the SqlCommandBuilder?
To simplify the construction of data adapter objects, each of the Microsoft-supplied ADO.NET data
providers provides a command builder type. The SqlCommandBuilder automatically generates the values contained within the SqlDataAdapter’s InsertCommand, UpdateCommand, and DeleteCommand properties,based on the initial SelectCommand. The benefit here is that you do not need to build all the SqlCommand and SqlParameter types by hand.A command builder is only able to autogenerate SQL commands for use by a data adapter if all of the following conditions are true:
· The SQL Select command interacts with only a single table (e.g., no joins).
· The single table has been attributed with a primary key.
· The table must have a column or columns representing the primary key that you
include in your SQL Select statement.
private void ConfigureAdapter(out SqlDataAdapter dAdapt)
{
// Create the adapter and set up the SelectCommand.
dAdapt = new SqlDataAdapter("Select * From Inventory", cnString);
// Obtain the remaining command objects dynamically at runtime
// using the SqlCommandBuilder.
SqlCommandBuilder builder = new SqlCommandBuilder(dAdapt);
}
20. What is the role of DataRowExtensions.Field<T>() Extension Method?
When we use LINQ to data tables we need numerous casting as
var cars = from car in data.AsEnumerable()
where
(string)car["Color"] == "Red"
select new
{
ID = (int)car["CarID"],
Make = (string)car["Make"]
};
To inject some strong typing into your query, you can use the Field<T>() extension method of the DataRow type. Doing so lets you increase the type safety of your query because the compatibility of data types is checked at compile time.
var cars = from car in data.AsEnumerable()
where
car.Field<string>("Color") == "Red"
select new
{
ID = car.Field<int>("CarID"),
Make = car.Field<string>("Make")
};
21. What is connection pooling?
When a application connects to data server it takes many steps that are time consuming as
· A physical channel such as a socket or a named pipe must be established.
· The initial handshake with the server
· The connection string information must be parsed
· The connection must be authenticated by the server
During application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.
Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
22. What is ADO.Net Entity Framework?
The ADO.NET Entity Framework is a programming model that reduce gap between database objects and object-oriented programming objects. Using EF, you can interact with database without seen a line of SQL (if you want).Rather can use LINQ queries to your strongly typed classes. The EF runtime generates proper SQL statements on the behalf of these classes.
Refrence book: Pro C# 2010 and the .NET 4 Platform, 5th Edition
