How do we connect to SQL SERVER in ASP.NET? Dot Net Interview Questions

punjabtechnicaluniversity.blogspot.in


First below is the code , after the code i have given the explanation for it.For this sample we will also need a SQL Table setup which i have imported using the DTS wizard.

Private Sub LoadData()
‘ note :- with and end with makes your code more readable
Dim strConnectionString As String
Dim objConnection As New SqlConnection
Dim objCommand As New SqlCommand
Dim objReader As SqlDataReader
Try
‘ this gets the connectionstring from the app.configfile.
‘ note if this gives error see where the MDB file is stored in your pc and point to that
strConnectionString =  ppSettings.Item(“ConnectionString”)
‘ take the connectiostring and initialize the connection
object
With objConnection
.ConnectionString = strConnectionString
.Open()
End With
objCommand = New SqlCommand(“Select FirstName from
Employees”)
With objCommand
.Connection = objConnection
objReader = .ExecuteReader()
End With
‘ looping through the reader to fill the list box
Do While objReader.Read()
lstData.Items.Add(objReader.Item(“FirstName”))
Loop
Catch ex As Exception
Throw ex
Finally
objConnection.Close()
End Try
<appSettings>
<add key=”Connectionstring” value=”Server=ERMBOM1-IT2;User
ID=sa;Database=Employees”/>
</appSettings>

Note:- The above code is provided in CD in folder WindowsAppSqlClient”.Comments in the code do explain a lot but we will again iterate through the whole code later..”LoadData” is the main method which loads the data from SQL SERVER.Before running this code you have to install SQL SERVER in your machine.As we are dealing with SQLCLIENT we need to setup database in SQL SERVER.For this sample i have imported access “Nwind.mdb” in “SampleAccessDatabase” folder in CD in to SQlSERVER.Depending on computer you will also have to change the connectionstring in

Web.config file.

For setting up the sample SQL table we can use the DTS import wizard to import.See the below figure which is using data source as Microsoft Access.While importing the database author had give the database name as “Employees”.

Loading “Nwind.mdb” in SQL SERVER for the sample

For simplicity sake we will only import the employee table as thats the only thing needed
in our sample code.

View of loaded Employee table

Now from interview point of view definitely you are not going to say the whole source code which is given in book.Interviewer expects only the broader answer of what are the steps needed to connect to SQL SERVER.For fundamental sake author has explained the whole source code.In short you have to explain the “LoadData” method in broader way.Following are the steps to connect to SQL SERVER :-

1.First is import the namespace “System.Data.SqlClient”.
2.Create a connection object as shown in “LoadData” method.

With objConnection
.ConnectionString = strConnectionString
.Open()
End With
√ Create the command object with the SQL.Also assign the created connection
object to command object. and execute the reader.
objCommand = New SqlCommand(“Select FirstName from Employees”)
With objCommand
.Connection = objConnection
objReader = .ExecuteReader()
End With

3. Finally loop through the reader and fill the list box.If old VB programmers are expecting the movenext command it’s replaced by Read() which returns true if there is any data to be read.If the .Read() return’s false that means that it’s end of datareader and there is no more data to be read.

Do While objReader.Read()
lstData.Items.Add(objReader.Item(“FirstName”))
Loop

4.Finally do not forget to close the connection object.

Note:- In “LoadData” you will see that connectionstring is stored in Web.config file and is loaded using “AppSettings.Item(“ConnectionString”)”.While running this sample live on your database do not forget to change this connectionstring accordingly to your machine name and SQL SERVER or else the source code will not run.