How to connect to an external SQL server database in ASP.NET / VB.NET / .NET

[Ben Stones]

Connecting to databases in .NET is different if you are coming from other languages such as PHP. To connect to a database in ASP.NET / .NET in general, you use "Connection Strings" that essentially is the connection information to the database.

First and foremost, in your code behind file within an ASP.NET application (or simply the .vb or .cs file of your .NET desktop application), you will need to first import the namespace that has the relevant database-related classes and methods, etc.

Note: All examples use the Visual Basic language, but the concept is the same for both Visual Basic and C#, for example.

Imports System.Data.SqlClient

For ASP.NET applications, you have a Web.config file with every project or application you create, which is the main configuration file of an ASP.NET application (see this). The file itself is simply made up of recognised XML markup. You can have the connection string in the Web.config file if you wish, and then reference that in your appliction code:

Web.config:

<configuration>
  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="Server=example.com;Initial Catalog=the_database;UID=the_user_of_db;Password=pw_here"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

In the application code, I made a class-level variable which gets the connection string from the Web.config file:

Dim ConnectionStringReference = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString

Note: "ApplicationServices" is my specific ConnectionString name, but you can actually call it whatever you like.

Now, in whatever method you wish to execute queries, etc., you first connect to the database and then execute the query:

 Try
            Dim FetchSubRows As New SqlConnection(ConnectionStringReference) 'the class-level variable ConnectionString

            FetchSubRows.Open() 'opens the connection

            Dim FetchQuery As String = "SELECT Submission_ID, Submission_Title, Submission_Body, Submission_IP, Submission_Date FROM Submissions ORDER BY Submission_ID DESC" 'the SQL string
            Dim ExecuteCommand As New SqlCommand(FetchQuery, FetchSubRows) 'execute the query
            Dim DataReader As SqlDataReader = ExecuteCommand.ExecuteReader() 'we want to "read" the information, since it is a SELECT statement above

            While DataReader.Read() 'the Read() method advances to the next applicable record of fetched data...so it will continue looping until all has been returned, essentially
                Return DataReader
            End While

            If DataReader.HasRows = False Then 'has it got any actual rows?
                FetchSubmissionsError = 0
            End If
        Catch ex As SqlException 'if there was an error, perhaps with the SQL statement itself, catch this error and put it in a variable to be displayed elsewhere
            FetchSubmissionsError = -1
            FetchSubmissionsErrorMessage = ex.Message
        End Try

I just want to point out the Try...Catch block is actually optional. It's simply a way in which you can handle unexpected errors that may occur in that code. In that code, the most obvious error that could occur is an error caused by the SQL query, hence it will catch all SqlException errors (SqlException being a class) and put that object in the ex variable. More information can be found on Exception Handlers here: http://forums.eukhost.com/f50/what-o...se-them-16964/ (this examples uses the PHP programming language as an example, but conceptually applies the same). You can also read the MSDN resource on Try...Catch...Finally statements on the MSDN (Microsoft Developer Network): Try...Catch...Finally Statement (Visual Basic)

Regarding the connection string

Yes, you will need a connection string obviously. If you want to include this directly in your code, here is an example:

 Try
            Dim FetchSubRows As New SqlConnection("Server=example.com;Initial Catalog=the_database;UID=the_user_of_db;Password=pw_here") ' << visible change is this
            FetchSubRows.Open() 'opens the connection

            Dim FetchQuery As String = "SELECT Submission_ID, Submission_Title, Submission_Body, Submission_IP, Submission_Date FROM Submissions ORDER BY Submission_ID DESC" 'the SQL string
            Dim ExecuteCommand As New SqlCommand(FetchQuery, FetchSubRows) 'execute the query
            Dim DataReader As SqlDataReader = ExecuteCommand.ExecuteReader() 'we want to "read" the information, since it is a SELECT statement above

            While DataReader.Read() 'the Read() method advances to the next applicable record of fetched data...so it will continue looping until all has been returned, essentially
                Return DataReader
            End While

            If DataReader.HasRows = False Then 'has it got any actual rows?
                FetchSubmissionsError = 0
            End If
        Catch ex As SqlException 'if there was an error, perhaps with the SQL statement itself, catch this error and put it in a variable to be displayed elsewhere
            FetchSubmissionsError = -1
            FetchSubmissionsErrorMessage = ex.Message
        End Try

So now you understand how to connect to external databases, this means when you are creating a local project on your machine, you can connect to an external database that may be hosted on your external Windows Server or Windows Hosting environment. You do not need to rely on a local database on your machine.

Useful websites and resources:

http://www.connectionstrings.com/

http://www.asp.net/web-forms/videos

http://www.asp.net/web-forms/tutorials

http://msdn.microsoft.com/en-us/library/bb400852

ConnectionString Keyword Reference