Create a Microsoft Access database
To create a database in Access and then create a table in the database, follow these steps:
1. Start Access.
2. On the File menu, click New.
3. Click Blank database on the task pane.
4. In the File name box, type testdb, and then click Create.
5. Right-click Create table in Design view, and then click Open.
6. In the Field Name text box, type SName.
7. In the Data Type list, click Text.
8. In the Field Name text box, type SNo.
9. In the Data Type list, click Number.
10. Right-click SNo, and then click Primary Key.
11. On the File menu, click Save As.
12. In the Save As dialog box, type Student in the text box and then click OK. Close the design view.
13. Right-click the Student table, and then click Open.
14. Type ABC in the SName column.
15. Type 101 in the SNo column.
16. Type XYZ in the SName column.
17. Type 102 in the SNo column.
18. Type several more records in the Student table, and then close the Student:Table window.
19. Close the testdb database.
back to the top
Create a connection to the Access database by using Visual Basic .NET The following step-by-step example describes how to create a connection to the Access database by using the Microsoft Visual Studio .NET Server Explorer. The following example also describes how to use the OleDbDataAdapter class to retrieve data from the database and to insert data into a DataSet object. This example also describes how to create new rows, how to add these rows to the table, how to modify the data in the rows, and how to remove rows from the table in the Access database.
Create a Windows application in Visual Basic .NET 1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual Basic Projects.
4. Under Templates, click Windows Application, and then click OK.
By default, Form1 is created.
Open a connection to the Access database
1. On the View menu, click Server Explorer.
2. In Server Explorer, right-click Data Connections, and then click Add Connection.
3. In the Data Link Properties dialog box, click the Provider tab.
4. In the OLE DB Provider(s) list, click Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. Click the Connection tab, and then click the ellipses button (...).
6. Locate the Access database testdb.mdb file that you created by following the corresponding path on your computer.
7. Select the testdb.mdb file, and then click Open.
8. In the Data Link Properties dialog box, click OK.
Retrieve data from the Access database by using the OleDbDataAdapter class 1. On the toolbox, click the Data tab.
2. Drag an OleDbDataAdapter control to Form1.
3. In the Data Adapter Configuration Wizard, click Next three times.
4. In the Generate the SQL statements panel, type the following Microsoft SQL Server statement, and then click Next:
Select * from Student
5. In the View Wizard Results panel, click Finish.
Note In the Do you want to include the password in the connection string? dialog box, click Don't include password.
6. Right-click OleDbDataAdapter1, and then click Generate Dataset.
7. In the Generate Dataset dialog box, click OK.
8. Add the following code to the Form1_Load event handler:
9. 'Fill retrieves rows from the data source by using the SELECT statement
OleDbDataAdapter1.Fill(DataSet11)
back to the top
Display records that are retrieved from the Access database
1. Add a DataGrid control to Form1.
By default, DataGrid1 is created.
2. Right-click DataGrid1, and then click Properties.
3. In the Properties dialog box, set the DataSource property to DataSet11 and set the DataMember property to Student.
back to the top
Add a row to a table in the Access database 1. Add a Button control to Form1.
2. Right-click Button1, and then click Properties.
3. In the Properties dialog box, set the Text property to Add.
4. Add the following code to the Button1_Click event handler:
5. Dim i, sno As Integer 6. Dim sname As String
7. Dim rw As DataRow
8. 'Add a new row to the Student table.
9. rw = DataSet11.Tables(0).NewRow
10. sno = InputBox("Enter the Roll no of the Student:") 11. sname = InputBox("Enter the Name of the Student:") 12. rw.Item("SNo") = sno
13. rw.Item("SName") = sname 14. Try
15. DataSet11.Tables(0).Rows.Add(rw)
16. 'Update the Student table in the testdb database.
17. i = OleDbDataAdapter1.Update(DataSet11) 18. Catch ex As Exception
19. MessageBox.Show(ex.Message) 20. End Try
21. 'Displays number of rows updated.
MessageBox.Show("no of rows updated=" & i)
back to the top
Update the rows of a table in the Access database
1. Add a Button control to Form1.
By default, Button2 is created.
2. Right-click Button2, and then click Properties.
3. In the Properties dialog box, set the Text property to Update.
4. Add the following code to the Button2_Click event handler:
5. Dim i, rwno As Integer 6. Dim colname As String 7. Dim value As Object
8. colname = InputBox("Enter the name of the Column to be updated") 9. rwno = InputBox("Enter the Row Number to be updated: Row No starts
from 0")
10. value = InputBox("Enter the value to be entered into the Student table")
11. Try
12. 'Update the column in the Student table.
13. DataSet11.Tables(0).Rows(rwno).Item(colname) = value 14. 'Update the Student table in the testdb database.
15. i = OleDbDataAdapter1.Update(DataSet11) 16. Catch ex As Exception
17. MessageBox.Show(ex.Message) 18. End Try
19. 'Displays number of rows updated.
20. MessageBox.Show("no of rows updated=" & i)
back to the top
Delete rows from a table in the Access database
1. Add a Button control to Form1.
By default, Button3 is created.
2. Right-click Button3, and then click Properties.
3. In the Properties dialog box, set the Text property to Delete.
4. Add the following code to the Button3_Click event handler:
5. Dim i As Integer 6. Dim rno As Integer
7. rno = InputBox("Enter the Row no to be deleted: Row no starts from 0")
8. Try
9. 'Delete a row from the Student table.
10. DataSet11.Tables(0).Rows(rno).Delete()
11. 'Update the Student table in the testdb database.
12. i = OleDbDataAdapter1.Update(DataSet11) 13. Catch ex As Exception
14. MsgBox(ex.Message) 15. End Try
16. 'Displays number of rows updated.
MessageBox.Show("no of rows updated=" & i)
back to the top
Verify that it works
1. On the Build menu, click Build Solution.
2. On the Debug menu, click Start.
3. Click Add, and then type the data in the input box to add a row to the Student table.
Note: You receive an error if you click Cancel in the input boxes.
4. Click Update, and then type the data in the input boxes to update a column in the Student table.
5. Click Delete, and then type the data in the corresponding input boxes to delete a row from the Student table.
What is ADO.NET?
ADO stands for ActiveX Data Objects
ADO.NET is a database technology of .NET Framework used to connect application system and database server.
ADO.NET is a part of the .NET Framework
ADO.NET consists of a set of classes used to handle data access
ADO.NET uses XML to store and transfer data among applications, which is not only an industry standard but also provide fast access of data for desktop and distributed applications.
ADO.NET is scalable and interoperable.
ADO.NET Architecture
Advantages of ADO.NET
ADO.NET offers several advantages over previous Microsoft data access technologies, including ADO. Few advantages are listed below:
Single Object-oriented API
ADO.NET provides a single object-oriented set of classes. There are different data providers to work with different data sources but the programming model for all these data providers work in the same way. You should be aware of only one data provider. You just need to change class names and connection strings.
The ADO.NET classes are easy to use and understand, as they are object-oriented in nature.
Managed Code
The ADO.NET classes are managed classes. CLR takes care of language independency and automatic resource management.
Deployment
Microsoft uses MDAC (Microsoft Data Access Component), which is used as ActiveX component in .NET Framework (X is extensible component, when X is written after a term means extensible). .NET components takes care of deployment which was difficult
previous technologies used in deployment.
XML Support
ADO.NET data is cached and transferred in XML (EXtensible Markup Language) format.
XML provide fast access of data for desktop and distributed applications. XML is plain text designed to transport and store data and is self-descriptive.
Visual Data Components
.NET offers ADO.NET components and data-bound control to work in visual form. You can use these components without writing long codes and can achieve result in no time.
Performance and scalability
Performance and scalability are two major factors when developing web-based application and services. Disconnected cached data in XML help in performance and scalability.
Difference between ADO and ADO.NET
ADO ADO.NET
ADO has one main object that is used to reference data, called the RecordSet object.
ADO.NET provides objects that allow you to access data in various ways. The
DataSetobject allows you to store the relational model of your database. MARS (Multiple Active Result Sets) is implemented in
ADO.NET
You can only work on connected manner.
This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. This is barring, of course, you programming special routines to pull all your data into temporary tables.
In connected model you always get refreshed data.
ADO.NET uses data in a disconnected fashion.
When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It's also decent for desktop applications.
You can work on connected and disconnected manner.
In disconnected model you will get old data as you are editing it. Outlook is an example of disconnected model. We work on offline object model and when connection is required it is connected.
Connected object can be used on disconnected object.
Whereas ADO allows you to persist records in XML format.
ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.
ADO allows you to create client-side cursors only.
ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET, classes actually handle the work of cursors.
The developer has the freedom of choice in internet development, for creating efficient applications.
Conclusion
Hope the article would have helped you in understanding ADO.NET, its advantages and its architecture.
