Page 1
Page 2
Page 3
Accessing a SQL Server Database using SqlClient
Introduction
The Compact Framework has the ability to access SQL Server 2000 databases from the handheld device. This is a very powerful tool for developers, as it allows real-time access to corporate data, in a secure environment. The handheld can be connected to the network using a wireless Ethernet connection, or using a GPRS connection. This means that corporate data can be made available to authorized employees wherever they can get access. A salesman ‘on the road’ can connect to the office, and get the latest stock figures to ensure he has enough stock to fulfill an order.
This article will show how to connect to a remote database, and retrieve data, using Visual Basic .NET to demonstrate access.
SqlClient - Connecting
Accessing remote SQL Server databases uses the System.Data.SqlClient namespace. To add the references you need, in Visual Studio, click on Project>Add Reference, and add the following references
System.Data.SqlClient
System.Data.Common
In our code, we then have to import the namespaces. As we will be checking that we are connected to a remote system, we will also need the System.Net namespace.
Imports System.Net
Imports System.Data.SqlClient
…
Ideally, when accessing a remote database, we need to ensure that we have a connection, prior to trying any access. We can do this by checking the IP address of the device. If it returns 127.0.0.1, then it is not connected. If it returns 192.168.55.101 then the device is in the cradle, otherwise the device is connected to a network. In our sample application, we use a function for this:-
Public Function Connected_To_Network() As Boolean
Dim localEndPoint As New IPEndPoint(Dns.Resolve(Dns.GetHostName()).AddressList(0), 0)
Try
If localEndPoint.Address.ToString = "127.0.0.1" Then
Return False
Else
Return True
End If
Catch ex As Exception
Return False
End Try
End Function
You also have to define a connection. This will be declared as a ‘form’ variable.
Private sqlConn As SqlConnection
There will be a button on the form, which will serve the dual purpose of connecting the application to the database, and closing the database. When trying to open the database, we will first ensure we have a connection, and if so, we will set up the connection string, and try to connect to the database.
Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
If Not Connected_To_Network() Then
MessageBox.Show("You are not connected to the network!", "No Connection")
Exit Sub
End If
Once we have confirmed we have a connection, we can now set up the connection string. A typical connection string is
"Persist Security Info=False;Integrated Security=False;Server=delldesktop,39250;initial catalog=Pubs;user id=sa;password=;"
Table1. Connection string options and descriptions
Name |
Description |
Persist Security Info |
When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no. |
Integrated Security
-or-Trusted_Connection |
When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true. |
Data Source
-or-
Server
-or-
Address
-or-
Addr
-or-
Network Address |
The name or network address of the instance of SQL Server to which to connect. In the above example, the 39250 is the port SQL Server is using. The standard port is 1433, but due to security, sites tend to use a different port. |
Initial Catalog
-or-
Database |
The name of the database. |
User ID |
The SQL Server login account (Not recommended. To maintain the highest level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.). |
Password
-or-
Pwd |
The password for the SQL Server account logging on (Not recommended. To maintain the highest level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.). |
Once we have set up the connection string, we can try to connect.
Try
sqlConn = New SqlConnection
sqlConn.ConnectionString = "Persist Security Info=False;Integrated Security=False;Server=delldesktop,39250;initial catalog=Pubs;user id=sa;password=;"
sqlConn.Open()
btnOpen.Text = "Close 'Pubs'"
pnlFunctions.Visible = True
Catch Ex As SqlClient.SqlException
DisplaySQLErrors(ex, "Open", True)
End Try
Next Page