topic

Job with databases.

[1 page]

To create interesting web-pages, it is necessary to fill with their dynamical, updated maintenance{contents}. It is especially necessary in business - applications - bank, Internet - shops and auctions. The important part of job which is carried out by the developer ASP.NET is a linkage of the pages with sources of the data, display of the data to page, creation of convenient means of interaction in with them.

For a data storage are used SUBD (control systems of databases) more often. As it was already spoken, in ASP.NET 2.0 job occurs to the data through ADO.NET 2.0-part .NET, razrabotanaja specially for access to databases or XML-files.

SUBD have passed a long way of development. In the beginning all data stored{kept} in simple (flat) files. In process of increase in volumes of the data there was a question on how to get fast access to the necessary information. For this purpose the data began to be indexed. Other question - how to avoid duplication - when the same data are stored{kept} in different places. That her{it} to solve, the theory of normalization of databases has been developed. Today powerful industrial SUBD it is inconceivable without systems of protection of the information, journalizing, transactions and stored{kept} procedures.

The data in SUBD are stored{kept} in tables. The table will consist of fields and recordings. Recording - unit of a data storage, a line of the table. For example, data on one person are stored{kept} in one recording. Fields is stolbcy tables for storage of a concrete kind of the information. Databases are called relational because tables in them are connected definitely.

Performances (View) are created on the basis of one or several tables with the help of kill, association, sorting and grouping.

For presentation we shall consider these concepts on an example. In due time I worked with a database of the staff of one enterprise. How to create such base? First of all the table is necessary for a data storage about employees for us.

ID the Name the Surname the Patronymic Date of a birth Date of reception the Post

1 Peter Vasechkin Ivanovich 19652001 Supply manager

2 Vasily Petrov Sidorovich 19772003 Programmer

And so on. The table can have thousand recordings.

The information on movings employees is necessary for a staff department. For this purpose the separate table is got{started}. For example, Petrov has received a post of the senior programmer. First of all it is necessary to notice, that names of posts the limited quantity{amount}. To avoid duplication, it is better to store{keep} them in the separate table.

Posts

ID the Name of a post the Minimal salary

1 Director 1

2 Supply manager 10

3 Programmer 20

4 Cleaner 100

Numbers in the left column are keys. They are necessary to connect tables with each other. In the table "Employees" the name of a post, and his{its} key will be stored{kept} in the table of posts now not. For the table "Employees" he is called as an external key, and for the table of "Post" - a primary key. The key also is necessary for entering in the table of employees. Moving of the employee on other post will be stored{kept} in the table of movings so.

Movings

ID the Employee the Post Purpose{Appointment} Date

1235123101120.06.06

The table of "Post" is connected both to the table of employees, and with the table of movings on the unique key. The database can generate primary keys itself, automatically adding values to the previous value of a key. It is called avtoinkrementirovaniem. For full confidence of uniqueness of the data of tables can hold an exact times of creation of recording (Timestamp) and GUI (the global unique identifier).

This process is called as normalization. That for the report to restore the information on moving, in search it is necessary to connect the data from different tables.

As a result of this search performance which will show the data from the interconnected tables in the form convenient for reading will be created:

SELECT Employees. LastName, Employees. FirstName, Titles. Title, Titles_1. Title, Promotions. PromotionDate FROM Titles AS Titles_1 INNER JOIN ((Promotions INNER JOIN Titles ON Promotions. TitleBefore = Titles.id) INNER JOIN Employees ON Promotions. EmployeeID = Employees. EmployeeID) ON Titles_1.id = Promotions. TitleAfter;

Set of tables of the data connected by attitudes{relations}, make a database. On SUBD server the set of databases can be stored{kept}.

More in detail about the theory of databases it is possible to read in other rates. We shall pass to concrete examples of linkage with databases on webs - pages.

Each user Windows for certain has program Access. It is one-user SUBD in which the model of safety not so is strong. In one file Access are stored{kept} both the data, and the interface as forms and reports. It is possible to create modules on VBA (Visual Basic for Application). Professional developers use more powerful programs. On "gravity" SUBD from Microsoft go in the order - Access - FoxPro - MS SQL. MS SQL does not allow to create the form, and is engaged in storage and protection of the data at a professional level. Visual Studio 2005 (and VWD) at installation establishes MS SQL Express. He will be automatically started as service Windows.

For job with databases language of the structured searches - SQL (Structured Query Language) is used. Commands of this language are called as searches. Searches serve for data acquisition, for creation and change of structure of tables, addition, removal{distance} and updating of recordings and much another. The sequence of commands can be stored{kept} directly on SUBD server as stored{kept} procedure. It is necessary to try to use always stored{kept} procedures, instead of to write commands. Their main advantage - speed of job and inkapsuljacija business - logic. They are stored{kept} on the server in already otkompilirovannom a kind while the simple transferred{handed} set of SQL commands passes through a stage of compilation.

For the reference{manipulation} to databases from external programs there are special mechanisms. In Windows it ODBC - the open interface of interaction with databases. He allows the applications working under Windows or other OS, to communicate with various servers of relational databases.

For konfigurirovanija sources of the data on your computer go in Control Panel, Administrative Tools, Data Sources (ODBC).

We see, that ODBC at presence of the necessary driver allows to contact various databases - Access, FoxPro, Oracle, Microsoft SQL, MySQL, SAP, DB2. If in file Excel to create the called table, ODBC it is capable to distinguish and work her{it} as with the table of a database.

The web - project in Visual Studio 2005 contains predetermined folder App_Data. Files can be stored{kept} in her with the data which are used in the application. It can be files .mdf (MS SQL).mdb (Microsoft Access).xml and others.

ADO.NET 2.0

ADO.NET is a set of classes for job with the external data. In the new version .NET 2.0 he has been expanded with new properties and tozhet has received number{room} 2.0.

Connection in ADO.NET can occur to the help of various providers. Now it is recommended to work with the help of provider MS SQL or Oracle. These the provider are written on a controlled code .NET. One more provider OleDb, allows to get access to other sources of the data - Access, Excel, MySql, SAP. Provider OleDb is written on a unguided code, but can work together with .NET.

Classes ADO.NET are incorporated into some spaces of names.

System. Data is a nucleus ADO.NET. It contains the classes necessary for communication{connection} by means of any providers of the data. These classes represent tables, lines, stolbcy, DataSet (set of the interconnected tables). There interfaces (in sense of language C *) connections with databases, commands, adapters of the data are determined.

System. Data. Common - base classes for all providers of the data - DbConnection, DbCommand, DbDataAdapter.

In System. Data. OleDb there are the classes, allowing to work with sources of data OleDb, including with MS SQL versions 6.0 and are lower. There there are such classes, as OleDbConnection, OleDbDataAdapter and OleDbCommand.

System. Data. Odbc contains classes which work with sources of data ODBC by means of the provider .NET ODBC. Classes have similar names with prefix Odbc.

System. Data. SqlClient. Here the provider of the data for SUBD SQL Server versions 7.0 is determined and is higher. Classes SqlConnection, SqlTransaction, SqlCommand and others contain.

In System. Data. SqlTypes there are the classes representing types of data SUBD SQL Server.

Classes ADO.NET share on 3 types. Classes such as Disconnected define{determine} base structure of the data, for example DataTable. They are independent of any providers of the data and can be created and occupied by the data directly in the program. Classes Shared base and the general{common} for all providers. Classes Data Provider specific to different providers.

Programming ADO.NET

All providers of the data contain classes of connections, adapters, commands. The circuit of the typical program in ADO.NET is those.

1. In the beginning connection with a database - class Connection which is provided with the necessary information - line of connection is created.

2. Object Command is created and the command which is necessary for executing in given SUBD is set. This command can be search SQL or executed procedure. It is necessary to set parameters of this command if they are available.

3. If the command does not return the data, she is simply carried out with the help of one of methods Execute. For example, it can be removal{distance} or updating of the data of the table.

4. If the command returns sample of the data, they are necessary for placing somewhere. Decide, whether it is necessary for you to obtain the data for subsequent use without communication{connection} with a database or it is necessary to execute the command simply quickly. In the first case it is necessary to create class DataAdapter and with his{its} help to save the data in DataSet or in DataTable. In the second case class DataReader which demands is created to save connection on all operating time, stores{keeps} sample only for reading and allows to move only forward. But reading with help DataReader is carried out in some times faster, than in DataAdapter.

5. To set received DataSet or DataReader as a source of the data of an element of management or to deduce{remove} them on page in another way.

Object Connection

Object Connection for connection with a database requires a line of connection for the instruction{indication} of a way to SUBD and an input{entrance} in system. Properties of class Connection are shown in the table. OleDbConnection, SqlConnection, OdbcConnection - the successors of class Connection specific to providers OleDb, MS SQL ODBC accordingly.

Property the Description

DataSource the Way to a database in file system at use Oledb, a name of a copy of base of the server at use SqlConnection

Database Returns a name of the database used in object Connection after opening

State Returns the current status of connection. Possible{probable} values - Broken, Closed, Connecting, Executing, Fetching and Open.

ConnectionString the Line of connection with SUBD

All properties, except for ConnectionString, only for reading.

Use of object Command

Object Command executes search SQL which can be in the form of the built - in text, procedures of the server or direct access to the table. If it is search about sample of data SELECT the data usually are located in DataSet or in DataReader. Methods and properties are determined in abstract class DbCommand (through interface IDbCommand), and they are realized with private{individual} not inherited classes OleDbCommand, SqlCommand, OdbcCommand.

Property CommandType can accept values from transfer CommandType. By default it Text, that is is carried out directly the text of SQL command which is written down in property Command. TableDirect means, that as a result of performance of the command the maintenance{contents} of the table will be returned. StoredProcedure means, that in Command there is a name of procedure of the server which will be carried out.

Property CommandText stores{keeps} the text of search SQL or a name of server procedure.

CommandTimeout sets the timeout of the answer by default equal of 30 seconds. If the command will not be executed during this time, exception will be thrown out.

Procedures of the server require parameters. They are stored{kept} in collection Parameters and have type SqlParameter. Text commands also can receive parameters before which the prefix is put. For example:

SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT

Often method ExecuteNonQuery is used. With the help of him{it} it is possible to execute any operation with databases which is not connected to search and data acquisition as that updating, removal{distance} of recordings, creation and change of tables, creation of procedures of the server. She returns quantity{amount} of the changed recordings in the event that Select commands, Update, Delete are carried out.

ExecuteScalar returns result of search in case it odno-edinstvennoe. For example, it is necessary to learn{find out} quantity{amount} of orders of the concrete buyer. The search is carried out with the help of the command ” Select count * where customerid=1 “. Its{her} result - sample of one line and one column. She{it} can be executed and with the help of method ExecuteReader, but ExecuteScalar it will be carried out faster. If the search will return a lot of lines or stolbcov, they will be ignored.

ExecuteRow returns unique recording.

ExecuteReader it is carried out, if it is necessary to obtain the tabulared data. Result of performance - the cursor in which it is possible to move only from the beginning up to the end.

As a result of performance of method ExecuteReader of object Command object DataReader is created. Always close connections after use, differently it remain active and will borrow{occupy} resources. It can be made two ways. The first - to call overloaded method ExecuteReader which accepts parameter such as CommandBehavior with value CommandBehavior. CloseConnection. In that case it is necessary to look through the received sample from the beginning up to the end, and connection will be closed, when the end will be achieved. If you do not want to read all data, can independently close connection by method Close.

public void CreateMySqlDataReader (string mySelectQuery, string myConnectionString)

{

SqlConnection myConnection = new SqlConnection (myConnectionString);

SqlCommand myCommand = new SqlCommand (mySelectQuery, myConnection);

myCommand. CommandType = CommandType. Text;

myCommand. Connection. Open ();

SqlDataReader myReader = myCommand. ExecuteReader (CommandBehavior. CloseConnection);

while (myReader. Read ())

{

Response. Write (myReader. GetString (0) + "<br>");

}

myReader. Close ();

myConnection. Close ();

}

Advanced SUBD (now and MS Access) support transactions. Transaction is a sequence of commands which is carried out as a single whole. For example, while translating money the sum is subtracted from one bill and added to another. If there will be only one of these operations, bank or his{its} clients will have in loss. Therefore it is important, that there were both operations or any would not take place. If on one of stages of transaction there was a mistake, there is a recoil (Rollback), that is all earlier made operations and base are cancelled comes back to a status prior to the beginning of transaction. If all is successful, transaction proves to be true operation Commit.

For support of transactions class SqlTransaction and to him similar is entered. Object Command has property Transaction. Method BeginTransaction of object Connection forces a database to proceed{pass} in a mode of transaction.

Besides it is necessary to conclude always the program code working with databases, in blocks try/catch as job often occurs to the removed servers, and there can be the most different mistakes both in a network. And at job of the server.

Thus exception SqlException or OleDbException is thrown out.

public void RunTransaction (string [] Queries, string myConnectionString)

{

SqlConnection conn = null;

SqlTransaction trans = null;

try

{

conn = new SqlConnection (myConnectionString);

conn. Open ();

trans = conn. BeginTransaction ();

SqlCommand cmd = new SqlCommand ();

cmd. Connection = conn;

cmd. Transaction = trans;

foreach (string Query in Queries)

{

cmd. CommandText = Query;

cmd. ExecuteNonQuery ();

}

trans. Commit ();

}

catch (SqlException SqlEx)

{

if (trans! = null)

{

trans. Rollback ();

}

throw new Exception (" An error occurred while transaction ", SqlEx);

return;

}

finally

{

if (conn! = null)

{

conn. Close ();

}

DataAdapter

DbDataAdapter is a parental class for SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter. This class contains 4 objects such as Command. Classes DataAdapter provide bilateral information interchange.

* SelectCommand - this command is used for sample of the data of base. Thus class DataTable is filled by the data.

* UpdateCommand - updates the data (editing of recordings).

* InsertCommand - addition of new recordings

* InsertCommand - the command for removal{distance} of recordings

Method Fill of class DbDataAdapter fills in objects DataSet or DataTable the data read as a result of performance of SelectCommand command. This command should be search SQL such as Select. If tables already exist, in him{it} new tables are added. In general method Fill is overloaded 8 times. For example, DbDataAdapter. Fill Method (DataSet, String) adds in DataSet the table with a name specified in the second parameter. If such table already is, she is updated. Access to the table can be received with the help of his{its} name indeksatorom.

DataTable tblProducts = data. Tables ["Products"];

Method DbDataAdapter. Update writes down all changes which have taken place in object DataSet connected to it{him} in a database.

DataSet

DataSet is the class containing in of one or several tables DataTable and communications{connections} between them. Class DataSet is a performance in memory of the information which have been read out through ADO from databases or XML. He allows to manipulate the data after disconnect from a source of the data.

The collection of tables is stored{kept} in property Tables, and attitudes{relations} - in property Relations.

Being based on tables dataseta, it is possible to create performances - DataView.

Let's write page in which the submitted classes will be used.

Base Northwind is included into complete set SDK. She{it} can be established on the server, having started command line SQLExpress.

sqlcmd-E-S (local) SQLExpress-i InstNwnd.sql

<% Page Language = " C * " AutoEventWireup = "true" CodeFile = "Default.aspx.cs" Inherits = " _ Default " %>

<! DOCTYPE html PUBLIC " - // W3C // DTD XHTML 1.0 Transitional // EN " "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<asp:DropDownList ID = "DropDownList1" runat = "server">

</asp:DropDownList>

<asp:DataGrid id = "DataGrid1" runat = "server"> </asp:DataGrid>

</form>

</body>

</html>

File with a code:

using System;

using System. Data;

using System. Web;

using System. Web. UI;

using System. Web. UI.WebControls;

using System. Data. SqlClient;

public partial class _Default: System. Web. UI.Page

{

protected void Page_Load (object sender, EventArgs e)

{

String strConnect;

String strCommand;

strConnect = " Data Source =. SQLExpress; Initial Catalog=Northwind; Integrated Security=True ";

SqlConnection myConn = new SqlConnection (strConnect);

strCommand = " SELECT CategoryName, CategoryID FROM Categories ";

SqlDataAdapter myData = new SqlDataAdapter (strCommand, myConn);

DataSet DataSet1 = new DataSet ();

myData. Fill (DataSet1, "Categories");

strCommand = " SELECT ProductName, UnitPrice, CategoryID FROM Products ";

myData. SelectCommand. CommandText = strCommand;

myData. Fill (DataSet1, "Products");

DataSet1. Relations. Add (DataSet1. Tables [0] .Columns ["CategoryID"], DataSet1. Tables [1] .Columns ["CategoryID"]);

DataView myView = new DataView (DataSet1. Tables ["Products"] ",", "ProductName", DataViewRowState. CurrentRows);

DataGrid1. DataSource = myView;

DataGrid1. DataBind ();

DropDownList1. DataSource = DataSet1. Tables [0];

DropDownList1. DataTextField = "CategoryName";

DropDownList1. DataValueField = "CategoryID";

DropDownList1. DataBind ();

myConn. Close ();

}

Window of external sources of the data.

In Visual Studio 2005 there are 3 tabs of viewing of projects: Solution Explorer, Class Explorer, Server Explorer. to first two actively used everyone who wrote programs on C * (or the other language), and the third by default is not seen, open it{him} from menu View-> Server Explorer. This window allows to work with connections of databases, to look through statistics of job of the server (In VWD Express there is no item{point} Servers).

Connection can be established both with MS SQL, and with file Access and any source ODBC, and also Oracle. It is possible to create new database MS SQL also.

When connection is created, you through the user interface can add tables, look through their maintenance{contents}, write procedures of the server and many other things.

In lecture 3 we have created pages with votings, but in any way did not process results. Now we shall save results of voting.

1. In the beginning it is necessary to create a database. In window Server Exlorer press the right key of the mouse item{point} Data Connections, in the contextual menu choose Create New Sql Database.

.SQLExpress it is equivalent (local) SQLExpress and means the server by the local machine. It is possible to be connected to the server and on a network. We have created database Polls which is on the server. It is possible to create a database in a separate file .mdf then she{it} can be transferred on other computer.

2. Creation of the table. It can be made and programmno, and through window Server Exlorer. The site of created base Polls.dbo there has already appeared. Open it{him}, with the right key of the mouse click on item{point} Tables, in the contextual menu choose Add New Table. Fill in the following values.

Column Name Data Type Allow Nulls

id int +

variant nvarchar (100)

voices int

The field id is created as the unique identifier of a variant, and it should be a primary key of the table. Click with the mouse on the first line and choose item{point} Set Primary Key. The field voices (quantity{amount} of voices) at creation should be equal 0. In Columns Properties find line Default Value or Binding and enter value 0.

Creation of the same table in a program mode:

protected void Page_Load (object sender, EventArgs e)

{

String strCreateTable = " create table poll (id int NOT NULL PRIMARY KEY, variant nvarchar (100), voices int DEFAULT 0) ";

CreateSqlTable (strCreateTable, " Data Source =.\SQLEXPRESS; Initial Catalog=Polls; Integrated Security=True ");

}

public void CreateSqlTable (string SqlQuery, string myConnectionString)

{

SqlConnection myConnection = new SqlConnection (myConnectionString);

SqlCommand myCommand = new SqlCommand (SqlQuery, myConnection);

myCommand. Connection. Open ();

myCommand. ExecuteNonQuery ();

myConnection. Close ();

}

3. Filling the table with variants. Click with the mouse on the table and choose item{point} Show Table Data. Values id should not repeat.

4. Creation of server procedure. At each voting value of a field voices one of recordings the table corresponding to the necessary item{point}, should grow. It is more convenient for making with the help of procedure which accepts argument id and updates the necessary field. Click with the mouse on Stored Procedures site and choose item{point} Add New Stored Procedure.

CREATE PROCEDURE dbo. CountVote

(

@choiceid int = 0

)

DECLARE @Count INT

SELECT @Count = voices FROM poll WHERE id = choiceid

UPDATE poll SET voices = Count+1 WHERE id = choiceid

RETURN

In this procedure are made 2 interconnected commands connected by the general{common} logic, and performance of her{it} needs only one object Command. Also notice, that for its{her} call it is not necessary to know the internal device of the table. Before variables of procedure and parameters it is put to distinguish them from fields of the table.

Operator SELECT takes recordings from the table poll which correspond{meet} to a condition after keyword WHERE. As id - the key (unique) field also gets out one field voices, one value which can be written down in a variable comes back. Then in operator UPDATE changes on the increased value of a variable @Count.

5. Binding to the data. On new page create elements Button and RadioButtonList (it is possible and CheckBoxList) dragging from Toolbox. On RadioButtonList there is the arrow{pointer} opening Smart Tag. With the help of him{it} it is possible to configure connection with the necessary table or to bring in values manually. By pressing on Configure Data Source the master of connections will appear. Choose New Data Source. On the second step the master will suggest to choose type of a source. Choose Database. On the following step from the dropping out list choose .sqlexpress. Polls.dbo. On the fourth step the master will suggest to save a line of connection in a configuration file. We shall save, she can be necessary. 2 fields are necessary for filling switches - in variant the text of a variant which will be seen in the form, and id - number{room} of a variant which is connected with DataValueField the list of switches contains, and will be passed in procedure of the server as parameter.

At desire on this step it is possible to sort values, for example, under the alphabetic order of texts pressing button ORDER BY. On a penultimate step it is possible to test the received search, if that's all right on last step we put variant as a source for show and id for values. On page it should turn out about the following:

<% Page Language = " C * " AutoEventWireup = "true" CodeFile = "Voting.aspx.cs" Inherits = "Voting" %>

<! DOCTYPE html PUBLIC " - // W3C // DTD XHTML 1.0 Transitional // EN " "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title> Voting for programmers </title>

</head>

<body>

You prefer what programming language? <br/>

<asp:RadioButtonList ID = "RadioButtonList1" runat = "server" DataSourceID = "SqlDataSource1" DataTextField = "variant" DataValueField = "id">

</asp:RadioButtonList>

<asp:SqlDataSource ID = "SqlDataSource1" runat = "server" ConnectionString = " <% $ ConnectionStrings:PollsConnectionString %> "

SelectCommand = " SELECT [id], [variant] FROM [poll] ORDER BY [variant] "> </asp:SqlDataSource>

<br/>

*nbsp; *nbsp; *nbsp; <br/>

*nbsp; <asp:Button ID = "Button1" runat = "server" Text = "Button"/> <br/>

<br/>

</form>

<asp:Label id = "Message" runat = "server"/>

</body>

</html>

6. Processing results. Procedure is necessary for calling with the parameter taken from property Value of group of switches. Property Parameters SqlCommand is a collection, in this case in it{her} it is necessary to add one element.

public partial class Voting: System. Web. UI.Page

{

public void ExecuteStoredProcedure (string ProcedureName, string myConnectionString, int id)

{

SqlConnection myConnection = new SqlConnection (myConnectionString);

SqlCommand myCommand = new SqlCommand (ProcedureName, myConnection);

myCommand. CommandType = CommandType. StoredProcedure;

SqlParameter myParm = myCommand. Parameters. Add ("@choiceid", SqlDbType. Int, 4);

myParm. Value = id;

myCommand. Connection. Open ();

myCommand. ExecuteNonQuery ();

myConnection. Close ();

}

protected void Page_Load (object sender, EventArgs e)

{

String strConnection = " Data Source =.\SQLEXPRESS; Initial Catalog=Polls; Integrated Security=True ";

if (Page. IsPostBack)

{

String strProc = "CountVote";

Message. Text = RadioButtonList1. SelectedValue;

ExecuteStoredProcedure (strProc, strConnection, Int32. Parse (RadioButtonList1. SelectedValue));

}

At job with databases it is important to observe a principle: « borrow{occupy} resources as it is possible later and release{exempt} as soon as possible ». Therefore connection is closed right after performance of procedure of the server.

The conclusion

Job with databases in ASP.NET - so extensive subject, that she{it} cannot be captured in one lecture. Though ADO.NET inkapsulirovany in more convenient classes of sources of the data, them it is necessary to know classes, that it is better to understand the new model accepted in ASP .NET 2.0.