SQL Server with ASP.NET

Visual Studio .NET provides Web Forms controls, such as the DataGrid control that you can use to access data from various data sources, such as a SQL server or a Jet database. This chapter introduces you to the Structured Query Language (SQL), which is used to access data stored on a SQL server through Web Forms. You'll also learn how to use ADO Extensions to create and manage different schema objects, such as databases and tables.

Server-side data access is critical to all real-world applications. Therefore, these applications must address server-side data access to implement business solutions. This section introduces you to the SQL server data access through Web Forms.

Microsoft SQL Server is a Relational Database Management System (RDBMS) that is used to store and organize related data — the collection of related data is called a database. Microsoft SQL Server is based on the client/server architecture, in which data is stored on a centralized computer called a server. Other computers, called clients, can access the data stored on the server through a network. The client/server architecture prevents data inconsistency.

You can access data stored on a SQL server through Web Forms. To do so, you can create Web applications that have data access controls. These data access Web controls present the data in a consistent manner irrespective of the actual source, such as Microsoft SQL Server or MS Access. Therefore, while creating a Web application, you do not need to worry about the format of the data. However, before you can access or manipulate data from a SQL server, you need to perform the following steps in the specified sequence:

1. Establish a connection with the SQL Server.
2. Write the actual command to access or manipulate data.
3. Create a result set of the data from the data source with which the application can work. This result set is called the data set and is disconnected from the actual source. The application accesses and updates data in the data set, which is later reconciled with the actual data
source.

To achieve this functionality, you first need to import two namespaces, System.Data and System.Data.SqlClient, into your Web Forms page.

The two namespaces are described as follows:

System.Data: A collection of classes that are based on the ADO.NET architecture. The ADO.NET architecture allows for efficient data management and manipulation from multiple data sources. ADO.NET provides tools to request and update data in a data set, and reconcile data in the actual data source.

Some of the classes included in this namespace are described as follows:
  1. DataSet: Represents the data set cached in memory with which applications work.
  2. DataTable: Represents a table of data in a data set.
  3. DataRow: Represents a row of data in a data table.
  4. DataColumn: Represents a column of data in a data table.
System.Data.SqlClient: A collection of classes that are used to access SQL server data sources. Some of the classes are listed as follows:

SqlConnection: Represents a connection with a SQL server data source. The first step to access data from a SQL server database is to create an object of this class.

SqlDataAdapter: Represents a set of data commands and a database connection that are used to access or manipulate data. After creating a SqlConnection object, you need to create an object of the SqlDataAdapter class to populate the data set and update the data source.

SqlCommand: Represents the SQL command to perform data operations in a SQL server data source.

A SQL database stores data in tables, which consist of rows and columns. A column stores the information regarding properties of an item, while a row stores the complete information of an item. For example, consider a Products table. The columns store information, such as product identification number, product name, and quantity available. The rows store information about different products. Each column stores data of a specific type. Therefore, each column has a specific data type.

Retrieving data from a SQL database

You can retrieve information stored in tables by using the Select statement. The syntax is as follows:

Select Column1, Column2,..., ColumnN From Table

In this statement:

ColumnN: Represents the name of a column in the table from which the information needs to be retrieved. A comma separates the different column names.

Table: Represents the name of the table.

Inserting, updating, and deleting data in a SQL database

You might need to add a new row to a table in a SQL database. For example, suppose you need to add to the Products table a new row for a new product. To add a row to a table, use the following statement:

Insert Into Table

Values (Column1_Value, Column2_Value, ..., ColumnN_Value)

In this syntax:

Table: Represents the table in which the row needs to be inserted.

Values: Takes the column values for the new row as parameters.

ColumnN_Value: Represents the value to be inserted in the column with name ColumnN.

The values must be supplied in the same order as the columns in the table. Also, if the data type of a column is Char, VarChar, or DateTime, you need to specify values in quotes.

RELATED POST

CREATING ASSEMBLIES WITH ASP.NET

BUILDING HANDLERS IN ASP.NET

INTERFACES AND CLASSES CREATION IN ASP.NET

CACHING IN ASP.NET

CACHING IN ASP.NET PART TWO

WIRE LESS APPLICATION WITH ASP.NET

SECURITY IN ASP.NET PART ONE

SECURITY IN ASP.NET PART TWO

LOCALIZING ASP.NET APPLICATIONS

DEPLOYING ASP.NET APPLICATIONS

ASP.NET CONFIGURATION SYSTEM

WEB SERVICES IN ASP.NET

WEB SERVICES PART TWO

WEB SERVICE INFRASTRUCTURE

WEB SERVICE INFRASTRUCTURE PART TWO

EXCHANGING MESSAGES IN ASP.NET

MICROSOFT HAIL STORM

SOAP AND DOT NET PART ONE

SOAP AND DOT NET PART TWO


No comments:

Post a Comment