C#.NET Database Programming FAQ
1. Which are the main Data Providers in .Net?
There are three main Data Providers in .Net, which are
   1. SQL Data Provider
   2. OLEDB Data Provider
   3. ODBC Data Provider

2. Which is the namespace used for OLEDB?
System.Data.OLEDB namespace.

3. Which are the main classes in System.Data.OLEDB namespace?
1. OleDbConnection
2. OleDbCommand
3. OleDbDataAdapter
4. OleDbDataReader

4. What is an OleDbConnection?
OleDbConnection represents a connection with a data source such as a database server. To create an instance of OleDbConnection, its constructor has to be called with a connection string. The connection string specifies the parameters needed to connect with the data source.

5. What is an OleDbCommand?
OleDbCommand represents a command to be executed against a data source connected through an OleDbConnection instance. The command can be a SQL statement or a stored procedure. To get an instance of OleDbCommand, its constructor has to be called with an instance of the OleDbConnection class and the string containing the SQL query to be executed.

6. What is an OleDbDataAdapter?
OleDbDataAdapter represents a set of commands and a connection that is used to fill a DataSet. It is a bridge between a DataSet and the data source to retrieve and update the data. The constructor of the OleDbDataAdapter needs to be called with a SQL select statement and an OleDbConnection instance.

7. What is an OleDbDataReader?
OleDbDataReader provides a mechanism for reading forward only a stream of records and columns from the data source. To obtain an instance of OleDbDataReader, the executeReader() method of OleDbCommand has to be called.

8. What is ADO.NET?
ADO.NET is the .NET platform's new database technology, and it builds on ADO (Active Data Objects). ADO.NET defines DataSet and DataTable objects that are optimized for moving disconnected sets of data.At the same time, ADO.NET includes the traditional Connection and Command objects, as well as an object called a DataReader that resembles a forward-only, read-only ADO recordset.

9. Which are the two ADO.NET Connection objects included in the .NET Framework ?
1. OleDbConnection object, for working with OLE DB data providers
2. SqlConnection object, for optimized access to Microsoft SQL Server.

10. Which is the namespace used for SQL Data Connection?
System.Data.SqlClient.

11. Explain the procedure for connecting to a Database?
To access data in a database, you must first establish a connection using either of the ADO.NET connection object.
        OleDbConnection objOledbConnection = new OleDbConnection();
Then you must specify the data source to which you want to connect. This is done through the ConnectionString property of the ADO.NET connection object.
        objOledbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source
                =C:\temp\Sales.mdb";
After the connection string is defined, a connection to a data source is established by using the Open() method of the connection object.
         objOledbConnection.Open();

12. What is the use of ConnectionString property?
ConnectionString is used for specifying the data source to which you want to connect.The ConnectionString contains connection information such as the name of the provider, username, and password.

13. What are the parameters for the connectionString?
1. Provider - The name of the data provider (Jet, SQL, and so on) to use.
2. Data Source - The name of the data source (database) to connect to.
3. UID - A valid username to use when connecting to the data source.
4. PWD - A password to use when connecting to the data source.
5. DRIVER - The name of the database driver to use. This isn't required if a DSN is specified.
6. SERVER - The network name of the data source server.

14. Which is the method for closing a connection to a DataSource?
objOledbConnection .Close();

15. How can we manipulate Data using ADO.NET?
The easiest way to manipulate data using ADO.NET is to create a DataTable object containing the resultset of a table, query, or stored procedure. Using a DataTable, you can add, edit, delete, find, and navigate records.

16. What is a DataAdapter object?
It is an object that provides a set of properties and methods to retrieve and save data between a DataSet and its source data.

17. What is DataSet object?
The DataSet provides the basis for disconnected storage and manipulation of ralational data. We fill it from a data store, work with it while disconnected from that data store, then reconnect and flush changes back to the data store if required.

18. What are the various objects in DataSet?
DataSet has a collection of DataTable object within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects. There are also the Constraint collection, and the parent and child relationships between the tables.Finally, there is a DefaultView object for each table.

19. What is the connection string for connecting to a Microsoft Access Database?
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\Sales.mdb

20. How can we force the connection object to close after the datareader is closed?
objDataReader=objCommand.ExecuteReader(CommandBehavior.CloseConnection).

21. Which is the method used for retrieving records using an OledbReader object?
The Read() method.

22. How can we access the specific column of an OledbReader object?
Using the GetString() method of OleDbDataReder.

23. What are the two fundamental objects in ADO.NET?
DataReader and DataSet objects.

24. What is the difference between DataSet and DataReader?
1. DataReader provides forward-only and read-only access to data. Dataset object can hold more than one table from the same data source as well as the relationships between them.
2. Dataset is a disconnected architecture while dataReader is connected architecture.
3. DataSet can persist contents while DataReader can not persist contents, they are forward only.

25. What is the major difference between ADO and ADO.NET?
1. In classic ADO we had client and server side cursors, but they are no more present in ADO.NET as it is a disconnected model.
2. Locking is not supported due to disconnected model.
3. All data persist in XML as compared to classic ADO where data persisted in Binary format also.

26. What are the methods provided by command object?
1. ExecuteNonQuery.
2. ExecuteReader.
3. ExecuteScalar.

27. What is the use of ExecuteNonQuery method?
ExecuteNonQuery method executes the command defined in the CommandText property against the connection defined in the Connection property for a query that does not return any row.

28. What is the return value of ExecuteNonQuery method?
ExecuteNonQuery returns an integer indicating the number of rows affected by the query.

29. What is the use of ExecuteReader method?
ExecuteReader method executes the command defined in the CommandText property against the connection defined in the Connection property.

30. What is the return value of ExecuteReader method?
ExecuteReader returns a "reader" object that is connected to the resulting rowset within the database,allowing the rows to be retrieved.

31. What is the use of ExecuteScalar method?
ExecuteScalar method executes the command defined in the CommandText property against the connection defined in the Connection property.

32. What is the return value of ExecuteScalar method?
ExecuteScalar returns only the first column of the first row of the resulting rowset.

33. What are basic methods of DataAdapter?
1. Fill
2. FillSchema
3. Update

34. What is the use of Fill method of DataAdapter?
It executes the SelectCommand to fill the DataSet object with data from the datasource.

35. What is the use of FillSchema method of DataAdapter?
This method uses the SelectCommand to extract just the schema for a table from the datasource, and creates an empty table in the DataSet object with all the corresponding constraints.

36. What is the use of Update method of DataAdapter?
This method calls the respective InsertCommand, UpdateCommand, or DaleteCommand for each inserted, updated or deleted row in the DataSet so as to update the original DataSource with the changes made to the content of the DataSet.

37. How can we force the datareader to return only schema of the datastore rather than data?
objDataReader=objCommand.ExecuteReader(CommandBehavior.SchemaOnly)

38. How can we fine tune the command object when we are expecting a single row?
Using "CommandBehaviour.SingleRow".

39. How can we fine tune the command object when we are expecting a single value?
Using "CommandBehaviour.SingleResult".

40. How can we save all data from dataset?
Dataset has "AcceptChanges" method which commits all the changes since last time "AcceptChanges" has been executed.

41. What is the use of "GetChanges" method of dataset?
This method returns dataset which are changed since it was loaded or since AcceptChanges was executed.

42. What is the use of "HasChanges" propert of dataset?
This property indicates that has any changes been made since dataset was loaded or "AcceptChanges" method was executed.

43. What is the use of "RejectChanges" method of dataset?
This method reverts all the changes made to the dataset since it was loaded.

44. How can we add new rows to a DataTable object?
Using the "Add" method of "DataRowCollection" object.

45. What are the methods for removing a DataRow from a DataTabel objet?
1. Remove - Removes a "DataRow" object from "DataTable".
2. RemoveAt - Removes a "DataRow" object from "DataTable" depending on index position of the "DataTable".

46. What is the basic use of "DataView"?
"DataView" represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data within "datatable".

47. What are the important methods of "DataView" object?
1. Find - It takes array of values and returns the index of the row.
2. FindRow - It also takes array of values and returns a collection of "DataRow".
3. AddNew - Adds a new row to the "DataView" object.
4. Delete - Deletes the specified row from the "DataView" object.

48. What is the differnce between Dataset.clone and Dataset.copy?
Clone - It only copies structure, does not copy data.
Copy - Copies both structure and data.

49. Why DataSet is slower than DataReader?
As DataSet carry considerable overhead because of relations, multiple tables etc speed is slower than DataReader.

50. How can we perform transactions in .Net?
The most common sequence of steps are:
      1. Open a database connection using the Open method of the connection object.
      2. Begin a transaction using the Begin Transaction method of the connection object.
      3. Execute SQL commands using command object.
      4. Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
      5. Close the database connection.

 

 
Subscribe Newsletter
Register to the site for free, and subscribe to the newsletter. You will receive new articles and special content not available elsewhere on the site, right into your e-mail box!