 |
|
ASP.Net Tutorial #8
And now we get to finish databases. So now we can create tables, insert data and get data that we have put in back out again. Now we need to be able to update exisiting information with new information (perhaps the name we have in the database is wrong. Here's how we update that information:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Updating data from an Access Database</title>
</head>
<body>
<H3>Updating data from an Access Database</H3>
</body>
</html>
<script language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
string answer = null;
string strSQL = "UPDATE fone SET strName='Fred', strNumb='567890123'"
+ " WHERE strName='Dave'";
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = c:\\inetpub\\wwwroot\\newmdb.mdb";
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Response.Write("Connected to data source" + "<BR>");
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText=strSQL;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write("Failed to connect to data source" + "<BR>");
Response.Write(ex.Message);
}
finally
{
conn.Close();
Response.Write("Data source closed" + "<BR>");
}
}
</script>
There isn't anything really new here just a modified SQL statement where we update the fields where we find the name Dave. You might like to try some changes to test for various things. Is the where case sensitive? What happens if there are two (or more) Daves in the database. Now how about if we want to delete an entry from the database entirely. We do this:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Deleting data from an Access Database</title>
</head>
<body>
<H3>Deleting data from an Access Database</H3>
</body>
</html>
<script language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
string answer = null;
string strSQL = null;
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = c:\\inetpub\\wwwroot\\newmdb.mdb";
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Response.Write("Connected to data source" + "<BR>");
strSQL = "DELETE FROM fone WHERE strName = 'Dave'";
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText=strSQL;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write("Failed to connect to data source" + "<BR>");
Response.Write(ex.Message + "<BR>");
}
finally
{
conn.Close();
Response.Write("Data source closed" + "<BR>");
}
}
</script>
Again, the only thing that has really changed is the SQL statement this time deleting where we find Dave (poor Dave!). For completeness, here is what we do to remove the table:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Dropping tables from an Access Database</title>
</head>
<body>
<H3>Dropping tables from an Access Database</H3>
</body>
</html>
<script language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
string answer = null;
string strSQL = null;
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" +
"data source = c:\\inetpub\\wwwroot\\newmdb.mdb";
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
conn.Open();
Response.Write("Connected to data source" + "<BR>");
strSQL = "DROP TABLE fone";
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText=strSQL;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write("Failed to connect to data source" + "<BR>");
Response.Write(ex.Message + "<BR>");
}
finally
{
conn.Close();
Response.Write("Data source closed" + "<BR>");
}
}
</script>
Again, all that has really changed is the SQL statement. And that just about wraps up databases in C# and ASP.Net. Of course, there are still problems to be overcome. The main one is duplication of data. When data is first entered, you need to check that the database doesn't already have that entry in it. How? For this particular example where we have only two fields then read the database into a dataset (using the SELECT statement) and loop through all the entires looking for both name (case insensitive) and number and if they both match then chances are it is already in the database and you warn the user that thiese entires already exist. You maybe have to be aware that the phone number has been entered with a space in it so you either have to scan for spaces or insist that the user only enter phone numbers with (or without) spaces so you can be sure what format the data is in when searching. In databases with rather more fields in it, you need to single out a single field as being a primary key. A primary key should be wherever possible unique (Post/Zip Code or Car Registration number for example) so again you can be reasonably sure that data duplication doesn't happen. Then again, what do you do when two people live at the same address or both use the same car (husband and wife for example). You will have to make a decision based on experience or what your client prefers before writing the system and make sure you make it clear in the user manual for the system (ALWAYS provide a user manual either on paper or electronically). You might also have to consider what will happen when the database is being used across a network by multiple users. How you deal with these things are beyond the remit of this tutorial but they are real world problems that need to be considered and solved.
That's it for databases...almost... The final tutorial will focus on creating Forms for use with databases. In the meantime, enjoy messing around with databases. Catch you later...