Attached is a C# program which is a enhanced version of the previous article on ADO.NET.
In the previous article we talked and learned about ADODataReader alias Forward only Recordset for ADODB programmer. Microsoft previously said that they are coming with In-Memory database in COM+, but they dropped that idea in the final COM+. I think the DataSet is more like that only. ADO.NET DataSet can be considered as In-Memory Database. This can be considered as the Local Database, which when need can transfer the data back and from to the online database. There are no related object in ADODB that can do the same functionality as DataSet in ADO.NET can do. More or less you can say Disconnected Recordset, the limitation of Disconnected RecordSet is limited to a single table ( from database or virtually created with out database). The significant difference is that Database is a collection of DataTable. The DataTable are more like ADODB disconnected RecordSet and A DataSet is rather like a hierarchical Recordset that's created using Data Shaping and then disconnected. With ADO.NET DataSet Microsoft have introduced a formal separation between data and where it comes from. It means that our local database or more specific in memory database DataSet have different methods for populating and writing back the data from online database. In the previous sample the application was using a ADOConnection, ADOCommand to read the data and to dump that in the forward only stream in the form of ADODataReader. In the present sample I have made a lot of changes and added a listview which will display the details by means of dynamic contextmenu.
About the Project
The attached sample gives a good insight about using ADO.NET, Treeview, ListView WinForm Controls and dynamic generation of Contextmenu and how to use the events that is fired by these context menus.
In the present Sample what we are going to do, we will get the database tables, view and stored procedure present in the Database(which the user enter) and will display the informations in the listview more like Window Explorer. We have used SQL Server as database and sp_help stored procedure to get all the information, as this stored procedure writtens more that one recordset at a time. we have discussed that in the previous sample. This application uses the sample there method of ADODataReader and the enumerates the tablename as parameter for sp_help in the form of ADOCommand. We don't execute the ADOCommand we use a ADODataSetCommand object this is one of the object which is used to populate the data in the form of Table in DataSet.To access data via OLE DB, ADO.NET provides ADODataSetCommand objects. These objects work a bit like classic ADO Command objects, which can take a database connection and command string and return a Recordset. The ADODataSetCommand can work with ADOCommand,or we can directly specify the SQL String ADOCOnnection or ConnectString,ADODataSetCommand is not depended on the ADOConnection or ADOCommand , it is like the ADODB.Recordset , which can we used with ADODB.Connection or by connectiuon string too. The method which is used to populate the data in the form of the table in the DataSet is ADODataSetCommand.FillDataSet(DataSet), this method fills the DataSet with the records in the form of DataTable.
How it Works?
First we give a Login screen through which the user gives the information of username, password, database and SQL Server . This data is used by ADOConnection to stabilize an ADO.NET connection with the SQL Server Data base by using the OLE DB SQL Server Provider. If the connection is succesful then this ADOConnection is used for further processing, else the user is informed about some error in this connection parameters. This ADOConnection is used by ADOCommand object to return a forward only stream of Data in the form of ADODataReader, this ADODataReader is enumerated and the Table information is used as a parameter to the stored proc in the form of ADOCommand to get the result by ADODataSetCommand and the DataSet is filled. The DataSet is now a disconnected in memory database which can be literally used to do any things that are possible with online database , like creating tables adding relation defining constraints to same table and referencial constraint on other tables. In this sample application which is like a mini database explorer we use this DataSet as local storage and to retrive the Information regarding all the properties when we click the context menu . When the data is fetch the Data is shown in the TreeView with the Database Name which you are browsing and three node called as Tables,View and Stored Procedure. Expand these node and you will find all the Tables , View and Stored Proc names,to see the details of these table,view and Stored Procs right click on any of the Node , it will popup a context menu for your choice , clicking on any of the submenu will display all the relevent information in the listView.
This application at this step can be viewed as a Read Only Database Explorer , but we can make this to do more task by use of ADO.NET like creating tables..viewing all the data in the listview , giving user a SQL statement block to write SQL and get the result back in ListView or directly in XML, yes all the interface of ADO.NET in the background we will see that later in next articles.
Please drop we a line or two regarding the application and what you want more in the application.
Good luck and Enjoy C#.