Get Help with ADO.NET Assignments
Are you in need ADO.Net Assignment Solution, Tutorspoint programming tutors with several years of industry experience can provide help in various fields of Computer Science and Programming.
Our programming tutors can analyze your problem document in which you need help in your assignments and provide with a detailed step-by-step explanation along with quality ADO.Net solutions.
Please do email us the topic in which you need help with programming homework along with a deadline.
Introduction to Ado.NET
Every organization maintains data storage for its business, employees, and clients. This data must be easily accessible and updatable. The machine in which the back end database is stored is known as a server. This database can be accessed and used by front end applications through some connectivity. The machine in which the applications run is known as the client.
Microsoft introduced a set of programmable objects known as Active Data Objects (ADO) that provides data access services such that all types of data can be accessed. ADO is defined as an object model that uses a set of objects, methods, and properties. The methods in ADO are used to access and update the data source. Properties are used to control the behavior of methods.
Features of ADO.NET
In the earlier client-server applications, a connection to the database is kept open till the application is running. This decreases the overall application performance. ADO.NET allows working with data in a connected or disconnected mode. In the connected mode, the application issues a query and process the result. In the disconnected mode, the application issues a query and stores the result for later process.
Data reader allows communicating with the database in the connected mode. The function of data reader includes running queries, running procedure, creating database objects and performance DDL commands.
Dataset is a temporary storage of records. It works like a virtual data storage that contains the frequently used records from the database. It allows the application to work with these records.
4.Independent of database
Dataset does not have any relationship with the database. So it can contain data from multiple databases.
5. In ADO.NET data is transferred from database to dataset in the form of XML documents. The user needs nit know XML in ADO.NET. It automatically converts data into XML and vice versa.
Architecture of ADO.NET
ADO.NET is a collection of classes, objects, methods and properties that manage data access from relational databases within the.NET platform. These collections are organized in the namespaces System. Data, System.Data.OleDb and System. Data. Sqlclient. Ado.NET provides two central components.NET Data Provider and Dataset.
.NET Data Provider
It is a set of components that include connection command, Data Reader and Data Adapter objects.
Connection object provides connectivity to the database.
Command object allows accessing database commands
Data Reader objects provide data streams from the database.
Data Adaptor object provides the bridge between Dataset and database
Dataset is a virtual storage of records retrieved from the database. It contains a collection of tables, primary and other related information.
.NET Data Providers
Data provider acts as a bridge between application and database. It is used to retrieve data from a database and send back to the database and send back to the database after changes.
Two types of data providers are
1.SQL server -.NET data providers for Microsoft SQL server data sources.
2.OLEDB -.NET Data provider for OLEDB data sources.
3.Data reader object
4.Data adapter object
These objects are used to create a connection to the database for moving data between the database and user application. This connection can be created using oledbcommand or sqlcommand object.open(), close() and dispose() are the methods of connection object used to open and close the defined connection.
DIM conn As SqlConection
Con.ConnectionString=”data source=asdf; initial catalog=stud; user id=sa; pwd=”
The data source is the name of the SQL server instance which is to be connected, the initial catalog is the name of the database, user id is the SQL server login account and PWD is the password. The user must close the connection after using it.
These objects are to read, add, update and delete records in a database. These are done by.
The objects oledbcommand or sqlcommand and the method Execute reader () or ExecuteNonQuery (). Also connection and command text properties are used.
ExecuteNonQuery () - method is used to execute the command without retrieving any row.
DIM conn As SqlConection
DIM comm As SqlConection
Comm =New sqlcommand ()
Comm.Command Text=”select * from stud Table”()
Data Reader Objects
These objects are used to read row-wise data in read-only format from databases. Since only one row is in memory at a time, it increases the performance of applications.
DataReader is an abstract class and cannot be used directly in an application. Instead, the SqlDataReader or OledbDataReader object is used depending on the database.
The ExecuteReader() method is used to retrieve a row from the database.
DIM comm As SqlConection
DIM myReader As AqlDataReader
Read, can Getstring and Close methods can be used in the SqlDataReader object.Read method is used to obtain a row from the result of the query. It returns False if no more rows to read.
GetString methods are used to access a column value in a selected row.
Close method must be called after the use of DataReader object
Do While myReader.Read()
When a DataReader is open, it is not possible to create another DataReader or connection until it is closed
Data Adapter objects
This object is used to exchange data between a database and dataset. It reads data from the database to the data set and then writes the changed data back to the database.
SelectCommand for accessing rows in a database
Insercommand for inserting rows in to the database
Updatecommand command for modifiying rows in the database
DeleteCommand for deleting rows
The Fill() method is used to write the result of select command into the dataset
DIM MYadapter As New SqlDataAdapter
DIM Mydataset As New DataSet()
Dataset is a temporary storage of records. It is a relational data structure that can be read from or write to using XML Data.
Create an ADO.Net application
Ado.NET application can be created in any one of the .NET framework language like VB.Net and C#
The following example shows to establish a connection with the college database of SQl server and insert a record in the staff table.
1.Create a new windows application project in Visual Studio.NET.
2.Design a form with label staff id,Department and Age
3.Import the System.Data..SqlClient namespace at the beginning of the code in the code editor window as below.
4.Design a form with label staff id,Department and Age Import System.Data.SqlClient
5.Declare two class-level variables of type SqlConnection and Sql command respectively in the code editor
Public class form1
DIM conn As SqlConnection
Dim Comm As SqlCommand
6.Write the code for connecting to the database and creating the command in the form load event
Private sub Form1_load()
Conn.ConnectionString=”data source=asdf;initial catalog=college;User id=sa;Pwd=”
7.Write the following code in the click event of the insert button
Private sub Button1_click()
Comm.CommandText=”Insert into staff values(‘”&TextBox1.Text&”,’”&TextBox1.Text&”,”&TextBox3.Text&”)”
8.Run the application. Enter the staffed, dept and age values in the corresponding text boxes and click the insert button. Now a new record is inserted with these values into the staff table and a message is displayed in the message box
Using stored procedures
The stored procedure is a set of SQL commands which are compiled and stored in the database. Every time a SQL command is executed, the command is parsed and optimized. Parsing and optimizing command is very expensive. So a set of commands called stored procedure are already parsed and optimized and are executed whenever it is called.
Steps to create stored procedures
1.Open visual studio.NET and create new application project
2.Open the server explorer from the view menu
3.In the server explorer, expand the server tab, expand the SQL server tab and then expand the server instance.
4.Expand the required node
5.Right click on the stored procedure node and select new stored procedure
6.Now it generated a procedure stub for the user. The user is asked to provide a name, add desired parameters and define the T-SQL. The procedure stub is shown below
CREATE PROCEDURE dbo.storedProcedure1
@Parameter1 data type=default value,
@Parameter2 data type=OUTPUT
/* SET NOCOUNT ON*/
7.Change the name of the procedure and add required parameters.
8.Edit the required commands after the AS keyword
9.Click the save icon, now the procedure is stored in the database and it can be called later.