Display Sql Data

he DataGrid Web server control is a very flexible control for working with data. It supports advanced features to enable paging, editing of data, and sorting of data. The DataGrid control generates an HTML table (along with other HTML elements, depending on how the DataGrid is configured) to support these features. For server-side databinding, this is the most common control that you use. Without this control, you would be required to write many more lines of server side code to display data.

The process you use to work with a DataGrid resembles that of the Repeater and DataList controls if you just want to use the control for displaying data. Because this example includes a database, you need to look at a few more new objects: SQLConnection, SQLDataAdapter, and SQLDataSet.

The SQLConnection object, found in the System.Data.SqlClient namespace, is used to create a connection from the Web server to the SQL server database. The SQLDataAdapter, also in the System.Data.SqlClient namespace, represents the connection and the commands to execute on the database. The SQLDataSet, from the System.Data namespace, will use the SQLDataAdapter to retrieve data from the SQL Server data source.

DISPLAY SQL DATA

DISPLAY SQL DATA

D Open the

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user. ^0 Import the System.Data n ... . . . and System.Data.

Add the contro1 to the SqlClient namespaces. page.

0 Add the Page_Load function.

—O Create a SQLConnection object and use a connection string to connect to the database.

D Open the

GenericTemplate.aspx from the Code Templates directory.

0 Add a message to the user. ^0 Import the System.Data n ... . . . and System.Data.

Add the contro1 to the SqlClient namespaces. page.

0 Add the Page_Load function.

—O Create a SQLConnection object and use a connection string to connect to the database.

ACCESS DATA WITH ASP.NET

There are many options availabe to enhance the viewing and controlling of data with the DataGrid. To see some of these options, type the following code into a server-side form. See the full source code at

Chapter06/Code/ASPDataGrid_ai.aspx.

TYPE THIS:

<ASP:DATAGRID ID="datagridTitles" RUNAT="Server" SHOWHEADER="False" BORDERCOLOR="Black" CELLPADDING="5"

FONT-NAME=nVerdana" FONT-SIZE=n12px"

HEADERSTYLE-FORECOLOR="White"

HEADERSTYLE-FONT-BOLD="True"

HEADERSTYLE-BACKCOLOR=nNavy"

ALTERNATINGITEMSTYLE-BACKCOLOR=nLightBlue"

RESULT:

This produces a formatted HTML table that contains the results of the query to the pubs database.

% Untitled - Notepad

File Edit Fermât Help

Import Namespace-'System.Data1 %> <%@ Import Namespace-'System.Data.SqlChent" %=-cHTML> <HEAD=-

<SCRIPT LANGUAGE="C#" RUNAT="Server"> protected void Page_Luad(Object sender, EventArgs e) {

SqIConnection sqlconnectlonF'ubs - new SqlConnection("seiver=(locai)\\NetSDK;uid=QSUser;pwd=QSPassword;database=pub

SqIDataAdapter sqidataadapterTltles = new SqlDataAdapterfselect title, notes, price from titles where type-business'", sqiconnectiunPubs);

DataSet datasetTltles = new DataSeti):

soldataadapterTitles.FillfdatasetTitles. "titles"]

dataqrl duties.DataSource=datasetTi ties.Table5["titles"1.DefaultVlew, datagrldTltles.DataBind();rF

0 Create a

SQLDataAdapter and set the SQL statement to retrieve business type books using the SQLConnection object.

^httpii'/localhost/ASPDataGrid.aspK - Microsoft Internet Explorer

File Edit View Favorites Tools Help

Back - - g] [g Personal Bar Address |#j http: /VIocalhost^AS PD ataG rid. aspx

Welcome to mylifetimegoals.com

Here are the are some books that will help you reach your career goals

The Busy Executive's Database Guide

An overview of available database systems with emphasis on common business applications. Illustrated,

19.99

Cooking with Computers: Surreptitious Balance Sheets

Helpful hints on how to use your electronic resources to the best advantage,

11.95

You Can Combat Computer Stress!

The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations,

2,99

Straight Talk About Computers

Annotated analysis of what computers can do for you: a no-hype guide for the critical user.

Local intranet g] Done

Local intranet

, Add the SQLDataAdapter to populate the DataSet.

Set the DataGrid Data Source property to the DataSet.

L-E Bind the DataGrid to the DataSet.

E Save the file and request the file from the Web server.

ERT DATA INTO A SQL DATABASE

You can use ASP.NET to create Web pages that insert data into your SQL databases. To do this, you need to work with a couple of .NET framework objects. The first object that you need to use is the Connection object, which will be used to establish a connection to your database. The most important property for this object is ConnectionString, which can specify the server, the user ID and password, and the database to connect to when creating the connection.

When you have a connection, you can use the Command object to execute a SQL statement that inserts a row of data into the database. You normally read this data from a form control on your ASP.NET Web page. After you set the SQL statement, you open the connection, execute the command, and then close the connection.

After you have inserted the data into the database, to confirm that the data insert was successful, you can use a bound DataGrid control. After the data is inserted, you will excute the Command object to select data that contains the new data (see page 132 for details on how to do this).

^NSERT^ATAbNBOABQLDAIABAIB

% Untitled - Notepad

^NSERT^ATAbNBOABQLDAIABAIB

% Untitled - Notepad

% Untitled - Notepad

File Edit Format Help

<%@Import Namespace-'System.Data" %> «%@ Import Namespace-'System Data.SqICIIent" cHTML>

«SCRIPT LANGUAGE-"C#" RUNAT="Seiw"=-protected void Page_Load(Object sender, EventArqs e) {

D Open the

GenericTemplate.aspx from the Code Templates directory.

□ Add a DataGrid control to the page and set its properties.

% Untitled - Notepad

File Edit Format Help

D Open the

GenericTemplate.aspx from the Code Templates directory.

_0 Add a heading for the page.

<%@Import Namespace-'System.Data" %> «%@ Import Namespace-'System Data.SqICIIent" cHTML>

«SCRIPT LANGUAGE-"C#" RUNAT="Seiw"=-protected void Page_Load(Object sender, EventArqs e) {

■^qiL-onnecnon sqiconnecnont-'uos - new SqlConnectlon("5erver=(local)\\NetSDK;uld=QSU5er:pwd=QSPassword;database=pub

SqIDataAdapter sqldataadapterTitles - new SqDataAdapter("select title, notes, price from titles where type-business1", sqlconnectionPubs);

String insertCmd = "INSERT INTO titles(titlejd, title, type, pubjd, price, advance, royalty, ytd_sales notes, pubdate) VALUESCBU9999', 'Howto Reach Your Business Goals', 'business', '0736', 25 00, 1000.00,10,1000, 'A practical how-to book on reaching even the most difficult business goals. Full of helpful tips, examples, and case studies.', '2001-06-12 00:00:00.000')";|

□ Add a DataGrid control to the page and set its properties.

^0 Import the System.Data and System.Data. SqlClient namespaces.

-0 Create the Page_Load event.

-0 Create a SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter object and set the SQL statement to retrieve business type books using the SQLConnection object.

^0 Create an insert command for the titles table and read it into a string variable.

ACCESS DATA WITH ASP.NET

You are most likely inserting data based on what a user fills out on a form. To do this, you need to read this data from the form and put it in your INSERT SQL string. The following code shows how to read one of the parameters for an insert from a form control. This code executes in an event where the user clicks a Submit button. See Chapter06/Code/ ASPInsert_ai.aspx for the full source.

TYPE THIS:

String insertCmd = "INSERT INTO titles(title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate) VALUES(@Id, 'How to Reach Your Business Goals', 'business', '0736', 25.00, 1000.00, 10, 1000, 'A practical how-to book on reaching even the most difficult business goals. Full of helpful tips, examples, and case studies.', '2001-06-12 00:00:00.000')"; SqlCommand sqlcommandTitles = new SqlCommand (insertCmd, sqlconnectionPubs); sqlcommandTitles.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 6));

sqlcornmandTitles.Parameters["@Id"].Value = inputTitleId.Text;

RESULT:

A page that asks for a book ID. When a valid ID is provided, you get a DataGrid control with the row that was added to the database.

Untitled - Notepad

File Edit Format Help notes, price from titles where type-business"', sqlconnectionPubs),

String InsertCmd = "INSERT INTO tltles(tltle_ld, title, type, pubjd, price advance, royalty, ytd_sales, notes, pubdate) VALUES('BU9999', 'Howto Reach Your Business Goals', 'business', '0736', 25,00,1000.00,10,1000, 'A practical how-to book on reaching even the most difficult business goals Full of helpful tips, examples, and case studies.', '2001-06-12 00:00:00.000']",

SqlCommand sqlcommandTitles - new SqlComrnand(inset1Cmd sqlconnectionPubs), sqicommanq I ities L.onnection.upen(); sqlcommandTitles. ExecuteNonGueryQ sqlcommandTitles.Connection.Close();

DataSet datasetTitles - new DataSet(); sqldataadapterTitles.Fill(datasetTitles, "titles")

dataqridTltles.DataSource-datasetTltles Tables["tltles"] DefaultView; datagrldTitles.DataBind():|

—, Use the insert command string and the connection object to create a SQLCommand object.

L-— Open, execute, and then close the connection to the database with the SQLCommand object.

Populate the DataSet object.

L-E Set the DataSource and DataBind properties of the DataGrid on the page.

^ http://localhostMSPInsert.aspx

Microsoft Internet Explo

rei

jsjxjl

File Edit View Favorites Tools

Help

1

ta

^ Back - * - @ [?] | ES

Personal Bar ^Search [^Favorites ^ | ® H3 Q ^

I J7

Address http:/7localhost/ASPInsert.a

SP,

£>Go Links w

Here are the are some 1

oooks that will help you reach your career goals,

The Busy Executive's Database Guide

|An overview of available database systems with emphasis on common business applications, Illustrated.

19.99

Cooking with Computers: Surreptitious Balance Sheets

Helpful hints on how to use your electronic resources to the best advantage,

11.95

You Can Combat Computer Stress!

The latest medical and psychological techniques for living with the electronic office, Easy-to-understand explanations.

2.99

Straight Talk About Computers

[Annotated analysis of what computers can do for you: a no-hype guide for the critical user.

19.99

How to Reach Your Business Goals

A practical how-to book on reaching even the most difficult business goals. Fuii of helpful tips, examples, and case studies.

25

1

w\

Done

Local intranet |

™ Save the file and request it from the Web server.

■ A new record appears in the titles table.

UPDATE DATA FROM A SQL DATABASE

You can use ASP.NET to create Web pages that can update data in a SQL database. Most of your applications require updating data that is persisted in a SQL Database. One way of updating data is by executing SQL update statements.

SQL update statements are typically built from information the user provides. The current data that is in the SQL Database is retrieved and displayed to the user. The user changes the values that need to be updated and then submits the information for updating. For example, you can update a price in a book database through a Web page.

To update data in a SQL database, you use the

SQLConnection and SQLCommand objects. The

SQLConnection object creates a connection to the database. After you have a connection, you create an SQLCommand object and specify the SQL string to be executed against the database. Because you are most likely building this SQL string from user input, you can read the information off of an HTML or Web server control. After your SQL string is constructed, you can then open a connection using the SQLConnection object. To send your custom SQL statement to the database, you can use the SQLCommand object. After completing your database access code, make sure you close the connection to the database.

UPDATE DATA FROM A SQL DATABASE

UPDATE DATA FROM A SQL DATABASE

D Open the

GenericTemplate.aspx from the Code Templates directory.

> Add a DataGrid control to the page and set its properties.

_0 Create the Page_Load event.

SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set the SQL statement to retrieve business type books using the SQLConnection object.

D Open the

GenericTemplate.aspx from the Code Templates directory.

^0 Add a message to the user.

> Add a DataGrid control to the page and set its properties.

—0 Import the System.Data and

System.Data.SqlClient namespaces.

_0 Create the Page_Load event.

SQLConnection object and use a connection string to connect to the database.

Create a SQLDataAdapter and set the SQL statement to retrieve business type books using the SQLConnection object.

—0 Create an update command for the titles table and read it into a string variable.

ACCESS DATA WITH ASP.NET

You can update data based on what a user fills out on a form. To do this, you should read this data from the form and put it in your SQL UPDATE string. The following code shows how to read one of the parameters for an insert off a form control. This code executes in an event when the user clicks a Submit button. Please see Chapter06/Code/ASPUpdate_ai.aspx for the full source.

TYPE THIS:

String updateCmd = "UPDATE titles SET price = 35.00 WHERE title_id = @Id";

SqlCommand sqlcommandTitles = new SqlCommand(updateCmd, sqlconnectionPubs);

sqlcommandTitles.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 6));

sqlcommandTitles.Parameters["@Id"].Value = inputTitleid.Text;

RESULT:

This produces a page that asks for a title ID. When a valid ID is provided, you get an updated price in a DataGrid control.

% Untitled Notepad

File Edit Foimat Help protected void Page_Load(Object sender, EventArgs e) {

SqIConnection sqlconnectionPubs = new SqlConnection("server=(local)\\NetSDK;uid=QSUser;pwd=QSPassword;database=pub s");

SqIData Adapter sqldataadapterTitles = new SqlDataAdapterfselect title, notes, price from titles where type-business"', sqlconnectionPubs);

String updateCmd = "UPDATE titles SET price = 35.00 WHERE titlejd =

SqlCommand sqlcommandTitles = new SqlCommand(updateCmd, sqlconnectionPubs);

sqicommano i mes.connection.upeno; sqlcommandTitles.ExecuteNonQueryO; sqlcommandTitles.Connection.Closed,

DataSet datasetTltles - new DataSet(); sqldataadapterTitles Fill(datasetTitles, "titles").

datagridTltles DataSource-datasetTltles.Tables["titles"].DefaultVlew; datagridTltles DataBind()J

—, Use the insert command string and the connection object to create a SQLCommand object.

L-— Open, execute, and then close the connection to the database with the SQLCommand object.

± Populate the DataSet object.

L-E Set the DataSource and DataBind properties of the DataGrid on the page.

^ http://localhost/ASPUpdate.asp* Microsoft Internet Explorer

I File Edit View Favorites Tools

Help

1

«1

Back - «4 - ^ |?| ¿8 1 Personal Bar "^Search [

S Favorites | # {§ % ^

Address http://loc-alho.st/AS PU pdate.aspx

zJ ^

hGo 1 Links J>|

1 Here are the are some books that will help you reach your career goals,

The Busy Executive's Database Guide

An overview of available database systems with emphasis on common business applications, Illustrated.

19.99

Cooking with Computers: Surreptitious Balance Sheets

Helpful hints on how to use your electronic resources to the best advantage,

11.95

You Can Combat Computer Stress!

The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.

2.99

Straight Talk About Computers

[Annotated analysis of what computers can do for you; a no-hype guide for the critical user.

19.99

How to Reach Your Business Goals

A practical how-to book on reaching even the most difficult business goals. Full of helpful tips, examples, and case studies.

35

1_

zi

Done

¡z;.; Local intranet |

™ Save the file and request it from the Web server.

■ A record is updated from the titles table. The price is now $35, as opposed to $25.

Was this article helpful?

0 0

Post a comment