Welcome to the first of my four part series on SQL databases! This part will focus on the creation of the database itself,
as well as its tables, and how to connect to them. Please note that I use Visual Studio, so this post may be written towards VS
users, especially the bits on database and table creation. However, the connection part of the article will be useful to any ASP.NET developer (I think)
Database Creation
Creating the database is easy enough. Just make sure you have an SQL server installed. SQL Express 2005/2008 is a good, free option.
1. In the Solution Explorer of Visual Studio, right click on App_Data,
and select "Add New Item."
2. Select "SQL server database," and give it a name. Chose Visual C# as the language (unless you don't need to know how
to connect to the database, which is covered using C# in the next section)
Databases created the above way should always work when they're being connected to on a local server. If you need to connect to a remote database, you may have to go
about things a little differently. You'll want to navigate to server explorer, and then right click on "Data Connections." From there,
select "Add a New Connection." A new screen will pop up. Here, you'll need to click "Change" next to "Data Source" to modify it. If you're working with a remote SQL server, you'd select "Microsoft SQL Server" and the ".NET framework for SQL." In "Server Name," you'd
type either the URL or the IP address of your remote server.
In the "Log on to server" section, choose whatever authentication type is set up on your remote server. In "Connect to Database," select your database that's been set up on your
remote server. You shouldn't bother with "Attach a database file" unless you have to; it likely won't work.
Table Creation
1. In Server Explorer, expand the datase you just created. Right click on Tables, and select "Add New Table."
2. Now you can set up your table (these next 3 steps can be performed simultaenously). Name your columns in "Column Name." For example, if you're creating a table that stores
login informtion (guess what the next two posts will be on? hint hint), you could enter "username," "password," and "email" into
"Column Name."
3. Enter DataTypes that correspond to the columns you just named. Going into all the different types is beyond the scope of this article,
but the general format is datatype(number of characters). varchar is a datatype that is common (it allows a varying number of characters); for
our login information table example, we may add a datatype of varchar(20) to both username and password, meaning that information in those columns
can have between 0 - 20 characters in them.
4. Uncheck the "Allow Nulls" column if you don't want to allow nulls. If you allow nulls it basically means that when you're filling out a row in the table,
you can skip that box. If you don't allow nulls, then that box must be filled out.
5. Select a primary key. The primary key is the column that can uniquely identify each row, since it has no repeating values or nulls. In our example, we'll
choose username. Right click in the area to the left of the row you want, and choose "Select Primary Key."
6. Add additional constraints if necessary. We'll go over that in a future post.
7. Add data to your table by right clicking on it in the Server Explorer and selecting "Show Table Data." We won't be getting in that here, since we're going to
let our users enter the data. And don't forget to save!
Connecting to your Database - SQLDataSource
There are different ways to actually make the connection to your database from your site. Either way, I hope you know your SQL!
The first way has to do with using SQLDataSource Controls. This is the easier way to do it, but not quite as flexible.
You can bind a number of controls, such as GridViews, FormViews, and DropDownLists, to an SQLDataSource. This is most
often done through clicking on the SmartTag (the little arrow on the upper right corner of controls in design view), and selecting
"Choose data source." If you've already set up a datasource, you can choose "Configure data source" to change its set up.
Next, you'll have to make the connection string. There will be a drop down menu. You can select "New Connection," and manually set up a connection, or you can
just select one of your databases that are already in the drop down list, which Visual Studio will conveniently use to automatically generate
a connection string. Save your connection string, and now you can select what data you want your datasource to show. You can either select columns, or make a custom SQL statement.
Whichever you chose, both are belong the scope of this article. You've connected to your database and got access to some simple database using an SQLDataSource!
Now, how about coding it manually?
Connecting to your Database - The Code Behind Approach
Making connections in the code behind, while a bit more work, allows you some flexibility. If you modify a query
in an SQLDataSource that is bound to a control, for example, the bound control is completely reset, and any custom code
that you may have added to your .aspx file for said control is wiped out.
The important thing here is to create a connectionString. Writing ASP.NET in Visual Studio, the best way to do that
is to set it up in your web.config file. Here is the code you'll want to put there:
<connectionStrings/>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\yourDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Name is obviously the name that you'll be using to reference elsewhere, so feel free to chose whatever name you wish. "Data Source=.\SQLEXPRESS"
is necessary if you're using SQL Express. AttachDbFileName is what will actually connect you to the database. Replace "yourDatabase" with whatever
the title of the database is. You've got a connection string! Now let's go use it.
Open up your code behind page (.cs), and start coding in an event (a button click, for example). First thing to worry about is importing some classes.
There are two classes that we need to add, so add these two lines at the top of your file:
using System.Data.SqlClient;
using System.Web.Configuration;
Once that's taken care of, ake a string like this:
string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Now we're ready to make our connection:
SqlConnection con = new SqlConnection(connectionString);
Now, we need a try/catch. If you don't know the syntax for try/catches, check here.
In the try block, we enter con.Open();
This opens our connection. In the finally block we enter con.Close(); to close it. In the catch block, throw in any
exception handling code you'd like. Congratulations, you've just connected to an SQL Database! Now we're ready to create
some commands. We'll try that in our next article.
