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

1.Data Access

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.

2.Data reader

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.

3.Data set

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.

XML documents

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

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.

1.Connection object

2.Command object

3.Data reader object

4.Data adapter object

Connection objects

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.

Example:

DIM conn As SqlConection

Conn=MNew SqlConnection()

Con.ConnectionString=”data source=asdf; initial catalog=stud; user id=sa; pwd=”

Conn.open()

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.

Conn.Close ()

Command objects

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.Connection=Conn

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.

Example:

DIM comm As SqlConection

DIM myReader As AqlDataReader

myReader=comm.ExecuteReader()

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

Example:

Do While myReader.Read()

MsgBox(myReader.GetString(0)&Vbcr&myReader.GetString(1))

Loop

myReader.Close()

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.

Properties:

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

Example:

DIM MYadapter As New SqlDataAdapter

Myadapter.SelectCommand=mycomm

DIM Mydataset As New DataSet()

Myadapter.Fill(MydataSet,studTable)

Conn.close()

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=New sqlConnection

Comm.=New sqlCommand

Conn.ConnectionString=”data source=asdf;initial catalog=college;User id=sa;Pwd=”

Conn.open()

Comm..connection=conn

End Sub

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&”)”

Comm.ExecuteNonQuery()

MsgBox(“Recordsinserted successfully”)

Endsub

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

)

*/

As

/* SET NOCOUNT ON*/

RETURN

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.

ADO.NET Assignment Help