Introduction
This 3 article series will cover inserting data into SQL Server, without the help of Visual Studio’s drag and drop controls and configuration wizards. A special emphasis will be placed on its application within a tiered environment.
Part 1 – Part 2 – Part 3
Part 2 – Creating a Connection
Now that we have a stored procedure, we need to be able to connect to our database. I’ll cover a brief overview of a SQL connection string, some basics about the SqlConnection object, and some direction on how to expand this to an enterprise environment.
Specifying a Connection String
If you’re not familiar with connection strings, it basically indicates to ADO.NET what server to connect to and what database you want. There are many other things you can specify within a connection string, including connection pooling and authentication, although most will not be covered in this article.
Here is a sample Connection String for connecting to our database. The first item is the Data Source. This specifies the server we will be connecting to. In this case, I’m using my local machine, so I’ve specified localhost. Initial Catalog specifies the Database that we’ll be connecting to. This assumes that we’re connecting to the default instance. You’ll find that the syntax is slightly different if you’re connecting to a named instance of SQL Server. You will also see a setting turning on Integrated Security. This specifies that we will be using Windows Authentication for security. It is also possible to provide a login and password to SQL Server through the connection string.
sConnectionString = "Data Source=localhost; Initial Catalog=Northwind; Integrated Security=True"
Creating the SqlConnection
Now that we have specified our connection string, it’s quite simple to create our SqlConnection object.
Dim sConnectionString As String
sConnectionString = "Data Source=localhost; Initial Catalog=Northwind; Integrated Security=True"
Dim scInsert As New SqlConnection(sConnectionString)
Now that we have our SqlConnection object, let’s cover a few basic properties and methods. The Open(), Close() and Dispose() methods should be self explanatory. You may also need to change the ConnectionTimeout if you’re dealing with a server over a slow connection. From here, you are free to use the connection to connect to your database through any of the ADO.NET objects. We’ll cover using those in Part 3 of this series, but for now, we’ll look at how the creation of connections can be expanded in a Tiered Environment.
Applications within a Tiered Environment
In any good application, code reuse is key. I typically create what I would call a “Helper” function that creates a connection for you to avoid having to retrieve a connection string every time you want a connection to your database. In my example above, I have coded the connection string into the application. This serves its purpose for this article, but isn’t really something you should do in a business environment. How you store your connection string(s) is up to you. You may want to look at the new Application Settings available in 2005, or store them in an XML file of your own making. Also, I typically develop in ASP.NET, but if you are working with a Windows application, you may need to consider database security. If you are storing connection strings in a file, you certainly don’t want to store Usernames and Passwords in plain text. The same goes for putting them in your code. If you’re not familiar with Reflector or ILDASM, it’s pretty simple to decompile a .NET application.
What’s next?
From here, we’ll be getting into the real meat of this series, inserting into the database. I’ll be covering some ADO.NET basics, covering SqlDataAdapters as well as the SqlCommand object and how they tie together. Please see Part 3 for the rest.