Delete Data From A Sql Database

You can use ASP.NET to create Web pages that can delete data from your SQL Databases. This is not as common as updating and inserting data, but is possible from a Web Page. The most common use of deleting data is in administrative applications that are used to maintain data in your application.

As with inserting and updating data, you use the

SQLConnection and SQLCommand objects to delete data from your SQL database. You use the SQLConnection object to create a connection to the database. After you have a connection, you create a 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 an HTML or Web server control. After your SQL string is formatted and set, you can then open a connection using the SQLConnection object. Next, you can execute the command. Finally, you should close the connection to the database.

There are alternatives when it comes to deleting rows of data from a database. Some developers will add a flag to a database table that indicates if the data is active or not. This active flag can be used to archive data when performing maintenance on your database.

DELETE DATA FROM A SQL DATABASE

DELETE DATA FROM A SQL DATABASE

Jy Untitled Notepad

File Edit Foimat Help

Import Namespace-'System.Data" %> Import Namespace-'System.Data.SqICIient" :HTML> =HEAD>

•¡SCRIPT LANGUAGE-'C#" RUNAT="Setver"> protected void Page_Load(Object sender, EventArgs e) {

<H3>Welcome to mylifetimegoals.corni/H3 Here are the are some books that will help yc <PI>

<ASP:DATAGRID ID="datagridTitles" RUN/

D Open the

GenericTemplate.aspx from the Code Templates directory.

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

Jy Untitled Notepad

File Edit Foimat Help

D Open the

GenericTemplate.aspx from the Code Templates directory.

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

—0 Import the System.Data and

System.Data.SqlClient namespaces.

Import Namespace-'System.Data" %> Import Namespace-'System.Data.SqICIient" :HTML> =HEAD>

•¡SCRIPT LANGUAGE-'C#" RUNAT="Setver"> protected void Page_Load(Object sender, EventArgs e) {

bqiconnection sqiconnecnonKUPS = new SqlConnection("server=(local)\\NetSDK;uid=GSUser,pwd=QSPassword,database=pub bqiuataAoapter sqiaataaaapteri ities = new bqiuataAqapteri'select tine notes, price from titles where type-business1", sqiconnectionPubs);

|String deleteCmd = "DELETE FROM titles WHERE titlejd = 'BU999FT

<H3>Welcome to mylifetimegoals.corni/H3 Here are the are some books that will help yc <PI>

<ASP:DATAGRID ID="datagridTitles" RUN/

_0 Create the Page_Load event.

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

u reach your career goals

.T-'Server" SHOWHEADER="False"/>

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

-0 Create a delete command for the titles table and read it into a string variable.

ACCESS DATA WITH ASP.NET

TYPE THIS:

String deleteCmd = "DELETE FROM titles WHERE title_id = @Id"; SqlCommand sqlcommandTitles = new SqlCommand(deleteCmd, sqlconnectionPubs); sqlcommandTitles.Parameters.Add(new SqlParameter("@Id", SqlDbType.NVarChar, 6)); sqlconmandTitles.Parameters["@Id"].Value = inputTitleld.Text;

RESULT:

This produces a page that asks for a title ID. When a valid ID is provided, you get an updated DataGrid control that shows a list of books that are in the pubs database (minus the record for the title that you deleted).

% Untitled - Notepad

File Edit Format Help

•¡SCRIPT LANGU AGE="C#" RUN AT="Server"3 protected void Page_Load(Object sender, EventArgs e) {

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

SglDataAdapter sqldataadapterTitles = new SqIDataAdapterC'select title notes, price from titles where type-business"', sqlconnectionPubs);

String deleteCmd = "DELETE FROM titles WHERE titleJd = 'BU9999'";

"3

SqICommand sqlcommandTitles - new SqlCommand(deleteCmd, sqlconnectionPubs);_

sqlcommandTltles.Connection.OpenO sqlcommandTitles. ExecuteNonQueiyt): sqlcommandTitles.Connection.Closet);

DataSet datasetTitles - new DataSetQ 5qldataadapterTitles.Fill(datasetTitles, "titles")

datagrid I itles.UataSource-datasetl itles. I ablesL"titles"J.DetaultView, datagridTitles.DataBindOj

—, Use the delete 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.

'H http://localhostMSPDelete.aspx - Microsoft Internet Explorer

File Edit View Favorites Tools Help 4= Back » • £) fffl | [gPersonal Bar ^Search Favorites 0 | # |3j g| % V? Address http: /VIocalhost/AS PD elete aspx

± Populate the DataSet object.

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

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.

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

■ The record disappears from the titles table.

Was this article helpful?

0 0

Post a comment