Best way to communicate from Desktop to SQL DB

  • Thread starter Thread starter Anil Gupte
  • Start date Start date
A

Anil Gupte

Hi All:

I am looking for general directions on "the right way" to do something,
which is very critical to my application.

I want to communicate with a SQL server database on a Windows Server (in the
future it could be a Linux MYSQL server, but I don't know for sure). My
application is a desktop application and at various times it may need to
communicate with the server to select, update records etc.

Now the question is, should I build ASP pages which perform the queries on
the server and have the desktop application read the pages and parse them?
If so how? Are there any samples (I have done something similar before in
VB 6.0 where I embedded IE in the app and "Screen-scraped").

Or should I communicate with SQL queries directly with the server? If so
how? Again, any tutorials etc online would help.

Thanx,
 
You should communicate with the database directly, not through
ASP pages.

Here's an example I have posted before. You can probably find
gazillions others by searching these newsgroups from google groups.
This one opens the database, reads the Product table, then
iterates through it and shows the results.

Dim ds As DataSet
'open the connection
'DBConnectionString is in the Project Settings
Using cnn As New SqlConnection(My.Settings.DBConnectionString)
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT * FROM Product"
'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Product")
End Using

For Each dr As DataRow In ds.Tables("Product").Rows
Dim ProductID As Integer = CType(dr.Item("ProductID"), Integer)
Dim ProductName As String = dr.Item("ProductName").ToString
Dim ProductNumber As String = dr.Item("ProductNumber").ToString
'Dim Price As Nullable(Of Decimal)
'If .Item("Price") IsNot DBNull.Value Then
' Price = CType(.Item("Price"), Decimal)
'End If
Dim Description As String = dr.Item("Description").ToString
'Dim ProductType As Integer = CType(.Item("ProductType"), Integer)
'Dim StockType As String = .Item("StockType").ToString
Console.WriteLine(String.Format("ProductID {0}, " & _
"ProductName {1}, {2}ProductNumber {3}, " & _
"Description {4}", ProductID, ProductName, _
ControlChars.CrLf, ProductNumber, Description))
Next

Robin S.
 
Thanx, but the critical step that I do not know how to do is to connect to
the remote SQL Server, in other words, how do I create a connection string
to a remote server (I know the URL and IP address). Do I need to make any
changes to the remote SQL server?

Thanx,
 
The easiest way to create a connection string is to create an empty
file with .udl extension

doubleclick on it, u will get a wizard to create and test connection
string
once u are finished open the same file with notpad ....Connection
string will be there
 
That is a great tip! Thanx, although I have not got it to work yet with my
remote SQL server - working on it.
 
Back
Top