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 SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("PubsString")) Cmd = New SqlDataAdapter("select * from Authors", MyConnection) MyDS = new DataSet() Cmd.Fill(ds, "Authors") AuthorGrid.DataSource=MyDS.Tables("Authors").DefaultView AuthorGrid.DataBind() 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!).
Dig Deeper on Microsoft .NET Web services
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.