 |
|
ASP.Net Tutorial #7
At last! We finally get to databases. Hurrah! Each time a new version of a programming language comes out, they change the way you deal with databases. We've had DAO, RDO, ADO and now we get ADO.Net which hopefully will be the only one we need from now on. So all we need now is some code to create a database in ASP.Net. Ah...ummm...well it seems that you can't. Sorry. No, its true. You cannot create access databases in ASP.net although you can create them fine on SQLServer. It is possible using the compiled version of C# using ADOX but I haven't got it to work with ASP.Net as yet. You cold also create a database using Access. But has it actually been created? You could look in the current directory of course but we would perhaps like to be able to do that from within a program so here is a program that seeks to find out if a database has been created:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Connecting to an Access Database</title>
</head>
<body>
<H3>Connecting to an Access Database</H3>
</body>
</html>
<script language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
String connectionString = "Provider=Microsoft.Jet.OleDb.4.0;" +
"data source=c:\\inetpub\\wwwroot\\newmdb.mdb;";
OleDbConnection conn = new OleDbConnection(connectionString);
// connect to access
try
{
conn.Open();
Response.Write("Data source opened" + "<BR>");
}
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>
This is where our error code comes in because if the database hasn't been created then we want to know about it without the program crashing terminally. Now we want to create a table inside it. A table is much like an array holding information in boxes which can be added to, overwritten or deleted. So how to create a table. Is it as fiddly as creating a database? Luckily no. Take a look:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Creating a table in an Access Database</title>
</head>
<body>
<H3>Creating a table in 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 = "CREATE TABLE fone (strName varchar(20), strNumb varchar(20))";
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>
Don't look so worried - it isn't that bad. First we need to include a reference to the OLEDB library which is what ADO.Net uses to do all the hard work. Then we are into Page_Load and setup two variables and assign them as containing a null character (effectively empty) and then comes this big long string:-
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" + "data source = c:\\inetpub\\wwwroot\\newmdb.mdb";
This is just saying that we want to use a database called newmdb.mdb and the service we need to be able to open it (OLEDB). Next we create a connection to the database:-
OleDbConnection conn = new OleDbConnection(connectionString);
Then we get into the try code where we attempt to open a connection the database and if all is well doesn't create an error. Now to ask it to allow us to create a table:-
strSQL = "CREATE TABLE fone (strName varchar(20), strNumb varchar(20))";
This involves using a well known database language called SQL which in this case is asking to create a table in the target database with the name fone (it is a simple phonebook app) with two fields of name and number (I'm using a string to handle the phine number as there is no maths to be calcualted using it). Again, if no errors are generated then the table will be created. The actual SQL command is invoked by ExecuteNonQuery which is used when we don't expect to get any information back from the SQL command (we'll get to those later). All being well, we get a connection closed message and the program ends. How do we know it has been created? Well, we could try and write some data to it:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Inserting data into a table in an Access Database</title>
</head>
<body>
<H3>Inserting data into a table in 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 = "INSERT INTO fone (strName, strNumb) VALUES ('Dave', '123456')";
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 much different here apart from the SQL statement entering values into the table. This code could easily be a class or function where the SQL is passed to it as well as the data to be inserted. Again, if there are no errors, all will be well. But do we know if the data is in there or not? More code:-
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<html>
<head>
<title>Selecting data from an Access Database</title>
</head>
<body>
<H3>Selecting data from an Access Database</H3>
<asp:DataGrid id="dgFone" runat="server" />
</body>
</html>
<script language="c#" runat="server">
private void Page_Load(object sender, System.EventArgs e)
{
String connectionString = "Provider=Microsoft.Jet.OleDb.4.0;" +
"data source=c:\\inetpub\\wwwroot\\newmdb.mdb;";
OleDbConnection conn = new OleDbConnection(connectionString);
String strSQL = "SELECT strName, StrNumb FROM fone";
OleDbCommand objCommand = new OleDbCommand(strSQL, conn);
try
{
// connect to access
conn.Open();
dgFone.DataSource = objCommand.ExecuteReader();
dgFone.DataBind();
conn.Close();
}
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>
This time because we are getting data out of the database then we use a datareader that is assigned as the source for a datagrid (does away with messing about trying to write a table around the data). The datagrid is displayed in the HTML section and is assigned a name we can use to access it. We also use and SQL statement using SELECT * from fone where the asterisk means everything from the table fone. The program then finishes hopefully with no errors. Well, I think thats quite enough to be getting on with. Try inputting different data (or even the same date) or creating new tables. Note also what happens when the page is refreshed. I'll finish up on databases next time. Stay tuned...