Problem solve Get help with specific problems with your technologies, process and projects.

Connecting Microsoft SQL Server with ASP.NET

As a beginner to ASP.NET, I am trying to connect to Microsoft SQL Server with ASP.NET and not having much luck. Is there a special driver or third party needed to make the connection with it?  

Everything you need comes with the .NET Framework. When you say you are trying to connect to SQL Server, my assumption is that you need to know a bit more than how to create a connection, but also how to return data and display it. Firstly, there are actually many ways to do this in .NET - some in code, some through the IDE and wizards.

I'll walk you through one way of creating a connection, returning a dataset and binding that dataset to a datagrid on a Web form. At the end, I'll give you some links for further reading. Examples are in VB.NET.

There are 3 important objects you will work with most often:

  • A connection object represents a physical connection to some data store, such as SQL Server or an XML file.
  • A command object represents a query or other command to retrieve or manipulate data ( an SQL statement ).
  • A dataset object represents the actual data an application works with.

Note that datasets are always disconnected from their source connection and data model and can be modified independently. However, changes to a dataset can be easily reconciled with the originating data model

To give your page access to the classes you will need to perform SQL data access, you must import the System.Data and System.Data.SqlClient namespaces into your page or codebehind. 

<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> 

To perform a select query to a SQL database, you create a SqlConnection to the database and pass it the connection string. Next you construct a SqlDataAdapter object that contains your query statement. To populate a DataSet object with the results from the query, you call the command's Fill method. Replace the server attribute in the following code with your database server (for a local SQL Server use (local) ) and the database with the name of your database. Place the following in a server side script in your aspx or in the codebehind:  

Sub Page_Load(Sender As Object, E As EventArgs)
   Dim MyDS As DataSet
   Dim Conn As SqlConnection
   Dim Cmd As SqlDataAdapter
   Conn = New
   Cmd = New SqlDataAdapter("select * from Authors", MyConnection)
   MyDS = new DataSet()
   Cmd.Fill(ds, "Authors")
End Sub

Change the connection string to match your database. 

Next place a Datagrid on the webform and name it "AuthorGrid". Make sure the attribute runat = server. Run the page and the datagrid will fill with data from the query. There are other ways to do this (of course!). 

Here are some links for more info: http://www.asp.net/Tutorials/quickstart.aspx http://samples.gotdotnet.com/quickstart/howto/

Dig Deeper on Topics Archive