1. Back To Blog

SQL Server Interview Question - State how can we connect to SQL SERVER, which namespace do we use?

• By Shiv Prasad Koirala in SQL
• Nov 12th, 2011
• 4424
• 0

This SQL Server interview questions is more of practical oriented. Below is the code to connect SQL Server, then we will try to understand the same in a more detailed manner.

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.config file.
' note if this gives error see where the MDB file is stored 
  in your pc and point to thastrConnectionString = AppSettings.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

Note:- 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. 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 the table. See the below figure which is using data source as Microsoft Access. While importing the database author had, give the database name as "Employees".

Figure: - Loading "Nwind.mdb" in SQL SERVER

To make it simple we will only import the employee table as that is the only thing needed in our sample code.

Figure: - 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 the 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 "Load Data" method in broader way. Following are the steps to connect to SQL SERVER:-

• First imports the namespace "System.Data.SqlClient".
• Create a connection object as shown in "Load Data" method.
  

With objConnection
.Connection String = 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 First Name from Employees")
With objCommand
.Connection = objConnection
Breeder = .Execute Reader ()
End With

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

Do while objReader.Read ()
lstData.Items.Add (objReader.Item ("First Name"))
Loop

• Do not forget to close the connection object.
  

Do not miss to watch  below SQL Server Interview Questions video with sample answers :-

More stuffs on SQL Server interview questions for interview preparation.

Go to author's blog for typical other SQL Server interview questions 

Shiv Prasad Koirala

Visit us @ www.questpond.com or call us at 022-66752917... read more