You Can Do a Lot of Database Work in Visual Studio

Databases

When working with a database on a web page, you are working under the constraints of HTTP—that is your program is stateless. The typical approach is to connect to the database, read the data, and close the database.

Server Explorer

You can do a lot of database work in Visual Studio.

Open the Halloween web site from last class.
Click on the View menu.
Click on Server Explorer.
Click on Connections.
Halloween.mdb should appear under the Connections.
Click on the triangle next to Halloween.mdb.
Click on the triangle next to Tables.
Right-click on Tables.
You can examine the table data by right-clicking on the table name and clicking on Retrieve Data.

Running Queries in Visual Studio

You can run SQL queries in Visual Studio.

Right-click on the table you want to query (Customers).
Click on New Query.
A window will open where you can either write SQL directly or use the visual tools to generate a query (at the bottom of the window).
Enter: Select * From Customers Where State = 'IA'
Click on the red exclamation mark on the toolbar to execute the query.
There should be one customer from Iowa.

Review of the SELECT Statement

The format of the SELECT statement is:

SELECT [columns]

FROM [tables]

WHERE [conditions]

ORDER BY [order_expression ASC | DESC]

Where Clause

The conditions in the WHERE clause can be compound (AND, OR, NOT) and can use the relational operators: = > < >= <= >

Dates

In Access, dates are #mm/dd/yyyy#. In SQL Server, dates are a string in the format 'yyyy/mm/dd'.

Strings

In Access, use ? and * for string matching. In standard SQL, use _ and %. Note that this (standard SQL) is an option that can be turned on in Access.

Aggregate functions

SQL has the following 5 aggregate functions:

SUM(fieldname)
AVG(fieldname)
MIN(fieldname)
MAX(fieldname)
COUNT(*) or COUNT(DISTINCT fieldname)

The SQL UPDATE Statement

The UPDATE statement modifies existing records:

UPDATE [table]

SET [update_expression]

WHERE [search_condition]

Example

UPDATE Customers

SET PhoneNumber='(806) 555-3152'

WHERE Email = ''

The SQL INSERT Statement

The INSERT statement inserts new records:

INSERT INTO [table]

([Column_list])

VALUES ([value_list])

Example

Enter the following into the SQL code window and execute it:

INSERT INTO Customers

(Email, LastName, FirstName, Address, City, State, ZipCode, PhoneNumber)

VALUES ('', 'Kleen', 'Tom', '3303 Rebecca Street', 'Sioux City', 'IA', '51104', '712-279-5411')

The SQL DELETE Statement

The DELETE statement deletes existing records:

DELETE FROM [Table]

WHERE [search_condition]

Example

DELETE FROM Customers

WHERE Email=''

ADO and Access

Create an Images folder and import all of the images from the previous Halloween shopping cart application (right-click on the project name in bold, click New, click .
Create an App_Data folder and import Halloween.mdb.
Create a new web form (right-click on the project name in bold, then Add, New Item, Visual C# web, Web Form). Name the new web form Default.
Go to Design view.
Add an image control and set its ImageURL property to the banner.jpg file.
Add anAccessDataSource. Leave the default name: AccessDataSource1.
Click on the AccessDataSource1's smart tag (the little arrow).
Click on Configure Data Source.
In the Choose a Database window, click on the Browse button.
Click on the App_Data folder in the left window.
Click on the Halloween.mdb database file in the right window.
Click on OK.
Click on Next.
Create the following SQL statement: Select * from Categories
Click on Next.
Click on Test Query to test the query. You should get 4 rows.
Click on Finish.
Add a drop-down list. Set its ID to ddlCategory.
Click on the drop-down list's smart tag (the little arrow) and click on Choose Data Source.
Click on Choose a data source.
Click on AccessDataSource1.
For the Field to display, click on LongName.
For the Data field, click on CategoryID.
Click on OK.

Test your project. You should get a drop-down list with the four categories listed.

Examine the code for the drop-down list. It should look like this:

asp:AccessDataSource

ID="AccessDataSource1"

runat="server"

DataFile="~/App_Data/Halloween.mdb"

SelectCommand="SELECT * FROM [Categories]">

</asp:AccessDataSource

asp:DropDownList

ID="ddlCategory"

runat="server"

DataSourceID="AccessDataSource1"

DataTextField="LongName"

DataValueField="CategoryID">

</asp:DropDownList

ADO.NET

Create a new project (File | New | Project | Visual C# Web | ASP.NET Empty Web Application). Name it ASP28-ADO.

ADO.NETis a set of computer software components that programmers can use to access data and data services based on disconnected DataSets and XML. It is a part of thebase class librarythat is included with theMicrosoft .NET Framework. It is commonly used by programmers to access and modify data stored inrelational database systems, though it can also access data in non-relational sources. ADO.NET is sometimes considered an evolution ofActiveX Data Objects(ADO) technology, but was changed so extensively that it can be considered an entirely new product. (Wikipedia)

ADO.NET is the technology that .NET applications use to interact with a database.

ADO.NET uses two types of classes: (1) those that contain and manage data (such as DataSet, DataTable, DataRow, and DataRelation) and those that are used to connect to a specific data source (such as Connection, Command, and DataReader).

That first group of classes is completely generic. They can work with any type of data source (e.g. Access, SQL Server, Oracle, DB2, etc.).

The second group of classes comes in several varieties, depending on which type of database you are using. There are several versions of the Connection class, such as OdbcConnection and SQLConnection. Which version you use depends on which type of database you are working with. There are also OdbcCommandand SqlCommandclasses. Note that the only thing that changes is the prefix.

The general process for getting data from a database is:

Create Connection, Command, and DataReader objects.
Use the DataReader to retrieve information from the database, and display it in a control on a web farm.
Close your connection.
Send your page to the user. At this point, the information your user sees and the information in the database no longer have any connection, and all of the ADO.NET objects have been destroyed.

If you are working with an Access database, include the following at the top of your code-behind C# file:

using System.Data;

using System.Data.Odbc;

And if you are working with an SQL Server database, include the following:

using System.Data;

using System.Data.SqlClient;

Connections

Connecting to a database in code:

Add a button
Name it btnConnect.
Set its text property to Connect.
Add a label.
Name it lblInfo.
Double-click on the button to open the code-behind file.
Add the following at the top:

usingSystem.Data.Odbc;

After adding the using statement above, the first thing you need to do is create a database connection.

Adatabaseconnectionisafacilityincomputersciencethatallowsclientsoftwaretocommunicatewithdatabaseserversoftware,whetheronthesamemachineornot.Aconnectionisrequiredtosendcommandsandreceiveanswers.(Wikipedia)

Ifyoudon'thaveaconnectionobject,you'renotgoingtodoanythingwithadatabase.

ConnectionsandConnectionStrings

Tocreateaconnection,weneedtoprovideaconnectionstring,whichprovidesinformationtotheobjectsothatitcanfindandaccessyourdata.

Incomputing,aconnectionstringisastringthatspecifiesinformationaboutadatasourceandthemeansofconnectingtoit.Itispassedincodetoanunderlyingdriverorproviderinordertoinitiatetheconnection.

Specificinformationthatisusuallypassed:databaseservername,databaselocation,username,andpassword.Sincewe'renotusingpassword-protecteddatabases,wewillomitthatpartfromourconnectionstring.

Continue to add the code below to your button click event procedure.

TheconnectionstringforourHalloweendatabaseis:

StringconString;

conString="Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

Theconnectionstringisusedtocreatetheconnection:

OdbcConnectioncon=newOdbcConnection(conString);

Aftertheconnectionhasbeencreated,weneedtoopenit:

con.Open();

TwooftheconnectionobjectspropertiesaretheServerVersionandtheState.AddthefollowingcodebelowtheOpenstatement.

lblInfo.Text = " Server Version: " + con.ServerVersion;

lblInfo.Text += " Connection is: " + con.State.ToString();

TheCommandobject

Theconnectionobjectsuppliesafewbasicpropertiesthatsupplyinformationabouttheconnection,buttoactuallygetdata,weneedafewmoreobjects:

ASQLstatementthatselectstheinformationwewant.
ACommandobjectthatexecutestheSQLstatement.
ADataReaderobjectthatretrievestheselectedrecords.

TheSQLstatementisjustastringthatcontainsavalidSQLstatement.

Tocreateacommand add the following:

OdbcCommandmyCommand=newOdbcCommand();

myCommand.Connection=con;

myCommand.CommandText="SELECT*FROMCustomers";

Thefirstlineabovejustcreatesanewcommandobject.

Thesecondlineassociatesthecommandwiththeconnection.

ThethirdlinesetsthevalueofthecommandintheCommandTextproperty(astring).

TheDataReaderobject

ADataReaderusesaconnectiontomoveforwardthroughyourdata,withread-onlyaccess.

BeforeyoucanuseaDataReader,youmusthaveanopenconnection.TocreateaDataReader,usetheExecuteReader()methodofthecommandobject:

OdbcDataReadermyReader;

myReader=myCommand.ExecuteReader();

AftercreatingtheDataReader,youcanuseitsReadmethodtoretrievethenextrowinthedataset:

myReader.Read();

AfterexecutingtheRead()method,thefieldsintherecordareavailableusingarray-likenotationwiththereader:

lblInfo.Text += " " + myReader[0].ToString() + "***" + myReader[1].ToString();

Howdoyouknowwhichcolumniscolumn0?Andwhichcolumniscolumn1?YoucanusetheFieldCountpropertyoftheDataReadertostepthrougheveryfield,andretrievethenameofthefield,usingtheGetName()method,likethis:

for (inti = 0; imyReader.FieldCount; i++)

lblInfo.Text += " " + myReader.GetName(i);

TheabovecodewillputthenamesofthefieldsintothelblInfolabel.

Youcanalsoretrievefieldsbynameifyouknowthenames,likethis:

lblInfo.Text+=myReader["LastName"].ToString()+","+

myReader["FirstName"].ToString());

WhenyouaredonewiththeDataReader,closeitandclosetheconnectionaswell:

myReader.Close();

con.Close();

Testyourprogram.

Readinganentiretable

Let'sreadallofthenamesintoadrop-downlistatthebeginningoftheprogram.Addadropdownlisttothe top of yourwebform.NameitddlNames.

AddthefollowingtothePageLoadprocedure:

if(!this.IsPostBack)

FillCustomerList();

TheFillCustomerListprocedurewillreadallofthedatafromthetableanduseittopopulatethedrop-downlist.Addthefollowingprocedure.

privatevoidFillCustomerList()

{

ddlNames.Items.Clear();

stringconString=

"Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

OdbcConnectionmyConn=newOdbcConnection(conString);

myConn.Open();

//Createacommandobject

OdbcCommandmyCommand=newOdbcCommand();

myCommand.Connection=myConn;

myCommand.CommandText="SELECT*FROMCustomers";

//Createareadertoreadthedata

OdbcDataReadermyReader;

myReader=myCommand.ExecuteReader();

while(myReader.Read())

{

ListItemnewItem=newListItem();

newItem.Text=myReader["LastName"]+","+myReader["FirstName"];

newItem.Value=myReader["Email"].ToString();

ddlNames.Items.Add(newItem);

}

myReader.Close();

myConn.Close();

}

Takingactionbasedonauserselection

Let'swritecodetopullupalloftheinformationonagivencustomerbasedontheuserselectingthecustomerfromthedrop-downlist.

AddeightnewTextBoxcontrolslabeledwithordinarytext:

txtFirstName
txtLastName
txtAddress
txtCity
txtState
txtZIP
txtPhone
txtEmail

Double-clickonddlNames.ThiswillopenitsSelectedIndexChangedprocedure.

Whentheuserclicksonaname,wewanttousetheemailaddresstoretrievetheappropriaterecordfromthetable.TheemailaddressisintheValuefieldoftheselecteditem.WeneedtocreateaSelectqueryusingtheemailaddressastheselectioncriteria.AddthefollowingcodetotheSelectedIndexChangedprocedure:

//Getanddisplaythecustomerinfo

stringkey=ddlNames.SelectedValue;

stringselectSQL;

selectSQL="SELECT*FROMCustomersWHEREEmail='"+key+"'";

System.Diagnostics.Debug.WriteLine(selectSQL);

//Createanewconnection,command,andreader:

stringconString=

"Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

OdbcConnectioncon=newOdbcConnection(conString);

OdbcCommandcmd=newOdbcCommand(selectSQL,con);

OdbcDataReaderreader;

con.Open();

reader=cmd.ExecuteReader();

reader.Read();

txtEmail.Text=reader["Email"].ToString();

txtFirstName.Text=reader["FirstName"].ToString();

txtLastName.Text=reader["LastName"].ToString();

txtAddress.Text=reader["Address"].ToString();

txtCity.Text=reader["City"].ToString();

txtState.Text=reader["State"].ToString();

txtZIP.Text= reader["ZipCode"].ToString();

txtPhone.Text=reader["PhoneNumber"].ToString();

reader.Close();

con.Close();

Runyourprogram.NotethatitdoesNOTwork.Thisisbecausetheselectionofacustomerfromthedrop-downlistisnotcausingapostback.WeneedtosettheAutoPostBackpropertyofthedrop-downlisttoTrue.

TheOdbcCommandclassprovidesthefollowingmethodsforexecutingcommandsagainstadatasource:

Item / Description
ExecuteReader / Executescommandsthatreturnrows.
ExecuteNonQuery / ExecutescommandssuchasSQLINSERT,DELETE,UPDATE,andSETstatements.
ExecuteScalar / Retrievesasinglevalue,forexample,anaggregatevalue,fromadatabase.

TheOdbcReader.Readmethodreadsasinglerowfromthedatabase.

AddingaRecord

Addanewbutton.

NameitbtnInsert
Text:Insert

Double-clickonthebuttontoopenitsclickeventprocedure.Addthefollowingcodetothebutton'sclickeventprocedure:

stringinsertSQL;

insertSQL="INSERTINTOCustomers";

insertSQL+="([Email],[LastName],[FirstName],";

insertSQL+="[Address],[City],[State],[ZipCode],[PhoneNumber])";

insertSQL+="VALUES";

insertSQL+="('"+txtEmail.Text;

insertSQL+="','"+txtLastName.Text;

insertSQL+="','"+txtFirstName.Text;

insertSQL+="','"+txtAddress.Text;

insertSQL+="','"+txtCity.Text;

insertSQL+="','"+txtState.Text;

insertSQL+="','"+txtZIP.Text;

insertSQL+="','"+txtPhone.Text+"');";

stringconString=

"Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

OdbcConnectioncon=newOdbcConnection(conString);

OdbcCommandcmd=newOdbcCommand(insertSQL,con);

intadded=0;

try

{

con.Open();

cmd.CommandText=insertSQL;

added=cmd.ExecuteNonQuery();

lblInfo.Text=added.ToString()+"recordinserted.";

}

catch(Exceptionerr)

{

lblInfo.Text="Errorinsertingrecord.";

lblInfo.Text+=err.Message;

}

finally

{

con.Close();

}

if(added0)

FillCustomerList();

UpdatingaRecord

Addabutton:

Name:btnUpdate
Text:Update

Addthefollowingtothebutton'sclickeventprocedure:

stringupdateSQL;

updateSQL="UPDATECustomersSET";

updateSQL+="Email='"+txtEmail.Text+"',";

updateSQL+="FirstName='"+txtFirstName.Text+"',";

updateSQL+="LastName='"+txtLastName.Text+"',";

updateSQL+="Address='"+txtAddress.Text+"',";

updateSQL+="City='"+txtCity.Text+"',";

updateSQL+="State='"+txtState.Text+"',";

updateSQL+="ZipCode='"+txtZIP.Text+"',";

updateSQL+="PhoneNumber='"+txtPhone.Text+"'";

updateSQL+="WHEREEmail='"+txtEmail.Text+"';";

stringconString=

"Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

OdbcConnectioncon=newOdbcConnection(conString);

OdbcCommandcmd=newOdbcCommand(updateSQL,con);

intupdated=0;

try

{

con.Open();

updated=cmd.ExecuteNonQuery();

lblInfo.Text=updated.ToString()+"recordupdated.";

}

catch(Exceptionerr)

{

lblInfo.Text="Errorupdatingcustomer.";

lblInfo.Text+=err.Message;

}

finally

{

con.Close();

}

if(updated0)

FillCustomerList();

Deletingarecord

Addabutton:

Name:btnDelete
Text:Delete

Addthefollowingcodetothebutton'sclickeventprocedure:

stringdeleteSQL;

deleteSQL="DELETEFROMCustomers WHEREEmail='"+txtEmail.Text+"';";

stringconString=

"Driver={MicrosoftAccessDriver(*.mdb)};DBQ=|DataDirectory|halloween.mdb";

OdbcConnectioncon=newOdbcConnection(conString);

OdbcCommandcmd=newOdbcCommand(deleteSQL,con);

intdeleted=0;

try

{

con.Open();

deleted=cmd.ExecuteNonQuery();

lblInfo.Text=deleted.ToString()+"recordwasdeleted.";

}

catch(Exceptionerr)

{

lblInfo.Text="Errordeletingcustomer.";

lblInfo.Text+=err.Message;

}

finally

{

con.Close();

}

if(deleted0)

FillCustomerList();

SQL Server

A nice thing about ADO is that it lets you write code that is (mostly) independent of the database.

To re-write this program with an SQL Server database, all you need to do is:

Change your connection string (below).
Change all "Odbc" prefixes to "Sql".
Add using System.Data.SqlClient; at the top of the program.

string conString=@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Halloween.mdf;Integrated Security=True;User Instance=True";

Day28ASP-Access.docx110/15/2018