ASP.NET Basics (Part 8): Data Overload - Hello Database!
(Page 4 of 6 )
Let's start by looking at a simple ASP.NET script that simply connects to the database. If the connection is successful, it will display a success message.
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<SCRIPT language=C# runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
output.Text = "Opening connection...
";
// open the connection
objConn.Open();
// display success message if connection opens successfully
output.Text = output.Text + "Connection successful!
";
output.Text = output.Text + "Closing connection...
";
// close the connection
objConn.Close();
// display success message if connection closes successfully
output.Text = output.Text + "Connection closed successfully!
";
}
</SCRIPT>
<?xml:namespace prefix = asp /><asp:label id=output runat="server"></asp:label>
If all goes well, you should see the following output.
There's a lot to learn in this one simple example. Let's take a look!
As you know, the .NET framework come packed with a whole set of pre-defined classes that can be easily reused in your code. ADO.NET is no different. when you install the .NET package, you will have access to a whole set of libraries ("assemblies," in .NET lingo) which you can use to access a variety of databases. I've used SQL Server 2000 above, but if you're using a database other than SQL Server 2000, fear not, for ADO.NET supports all major databases, and you can use the techniques described over the next few pages to talk to other databases too. You'll probably need to consult your database vendor's manual or Web site for information on how to obtain the necessary drivers to access the database.
<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
Say hello to the "Import" page directive!
In order to create any object, the .NET framework must know the exact location of the required classes. If you were to skip the above lines, you would need to give the full path to the objects in question. For example, to create the SqlConnection object in the example, I've only used the name "SqlConnection." If I hadn't imported the namespaces above, I would instead have had to specify the fully-qualified class name "System.Data.SqlClient.SqlConnection" each time I referenced the object.
You can use this page directive to import user-defined assemblies (the class libraries) into an ASP.NET script as required.
<%
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
%>
Next, comes the connection string. This simple little "strConn" variable stores the parameters required to access the database. These include the username and password for accessing the database (in our case, "john" and "secret" respectively), the database to be used (the de-facto "pubs" database) and the name of the server (i.e. "tatooine" in our example).
Note that the format of the connection string varies from database to database, so it's wise to consult the database manuals to obtain the right connection string to use with the database of your choice.
<%
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
%>
The SqlConnection object allows me to connect to the database. In order to make a connection, the connection string "strConn" must be passed to the database. If all the information provided in the connection string is valid, the object will be successfully created for use.
<%
output.Text = "Opening connection...
";
// open the connection
objConn.Open();
// display success message if connection opens successfully
output.Text = output.Text + "Connection successful!
";
output.Text = output.Text + "Closing connection...
";
// close the connection
objConn.Close();
// display success message if connection closes successfully
output.Text = output.Text + "Connection closed successfully!
";
%>
That's a lot of code, but it doesn't do much. The Open() method of the SqlConnection object is used to open a database connection. A message is printed to the display once the connection successfully opened. And since programming standards recommend that all objects be closed when they are no longer required, the end of the script uses the Close() method of the SqlConnection object to free up valuable memory resources by deleting the object from memory.
Wonder what will happen if you use an incorrect user name or password, or if the connection can't be made? ASP.NET will spit this ugly error message at you:
Next: Going All the Way >>
More ASP.NET Articles
More By Team Melonfire, (c) Melonfire
