How do we connect to SQL SERVER, which namespace do we use
How do we connect to SQL SERVER, which namespace do we use ?
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.config file. ‘ note if this gives error see where the MDB file is stored in your pc and point to that strConnectionString = 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 <appSettings> <add key=”Connectionstring” value=”Server=ERMBOM1-IT2;User ID=sa;Database=Employees”/> </appSettings>
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”.
To make it simple we will only import the employee table as that is the only thing needed in our sample code.
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 “LoadData” method in broader way. Following are the steps to connect to SQL SERVER
? First import the namespace “System.Data.SqlClient”.
? Create a connection object as shown in “LoadData” method.
With objConnection .ConnectionString = strConnectionString
? 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”)
.Connection = objConnection
objReader = .ExecuteReader()
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()
? 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.
2007-10-24, 5060👍, 0💬
Does it matter in what order catch statements for FileNotFoundException and IOExceptipon are written...
What is cross page posting? By default, button controls in ASP.NET pages post back to the same page ...
What are the two fundamental objects in ADO.NET ? Datareader and Dataset are the two fundamental obj...
What are three ways in which a thread can enter the waiting state? A thread can enter the waiting st...
What is the difference between const char* p and char const* p? In const char* p, the character poin...