SQL express on a remote machine

[iamthwee]

Hullo daniwebbers!!

I have created a little cd database at home on my home network. And I want my machine to act as the server. I want the other pcs, i.e my sister's and mom's which are connected to my network to access it, and be able to change and update stuff. So they can add and remove music entries.

But I'm confused, has anyone used SQL express on a remote machine. If so pleasse give me a helping hand. Help.:'(

Thanx.

[Hamrick]

Are you writing a VB.NET application to do it? Is the database configured for remote access? What problems are you having right now? I need more info before I can help you. :(

[iamthwee]

Thanx Hammie,

I'm using vb.net express, with SQL express (200mb) download. I don't know where to configure the database for remote access?

Let me get all the info (my ips or computer names) together and I'll post back.

[Hamrick]

This knowledge base article takes you through the steps of configuring the server. Once you have it set up, you should be able to connect normally from another machine on the network.

When you connect through .NET, be sure to include the server machine name. A lot of examples use ".\SQLEXPRESS" or "(local)\SQLEXPRESS" instead of "<server>\SQLEXPRESS". The connection string should look like this.

"Server=<server>\SQLEXPRESS;User ID=<username>;password=<password>;Database=<databasename>;Persist Security Info=True"

I'll attach a little program that you can use to build connection strings and test the connection with your database.

[iamthwee]

Thanx jdaughtry but where do I put that connection thing in my program?

[Hamrick]

That's up to you. I like to put it in a static property for a Program class that I make for stuff that needs to be available across the entire application. Other stuff in that class would be the entry point, error logging and user notifications, or cached data.

Public Class Program
  Private _connectionString As String
  Public ReadOnly Property ConnectionString As String
    Get
      Return _connectionString
    End Get
  End Property

  Public Sub LoadSettings()
    ' Read the connection string from some storage medium
    ' Decrypt the connection string if necessary
  End Sub

  Public Sub SaveSettings()
    ' Save the connection string to some storage medium
    ' Encrypt it if you don't want it to be plain text
  End Sub
End Class

When the program loads I'll read the connection string from XML or a database, or call the ConnectionBuilder to make one for me using a public method from the same class. Then you can get to it from anywhere in the application by saying Program.ConnectionString. And if you use the ConnectionBuilder you can save it somewhere so that you don't have to rebuild the connection string every time. :)

[iamthwee]

Ok, I need to look at this more carefully. I probably post back tomorro with more questions. Thanx again Jdaughtry!

[iamthwee]

Ok I tried to change the connection string but it won't let me. I can't edit it. :( This is where I am stuck.

click_me

[Hamrick]

You don't edit it during the wizard, it saves the string in all of the generated code that gets written after you finish the wizard. But all of that stuff makes working with connections harder if you don't already know how it wokrs. You should do it manually instead so that you know how everything works and then do the generated code if you want to later. :)

You can boil it all down into a few lines manually while the generated code cranks out hundreds of lines of extra stuff. If you use a wizard, you're making generated code and life starts to suck. ;)

using System;
using System.Data;
using System.Data.SqlClient;

public class DataManager {
  public static DataTable ExecuteSql( connectionString ) {
    try {
      // A connection gets you access to the database.
      using ( SqlConnection con = new SqlConnection( connectionString ) ) {
        // A command has the query stuff for the database.
        using ( SqlCommand cmd = new SqlCommand() ) {
          // Always open the connection. :)
          // If the connection object is in a using statement, the Dispose
          // method closes it automatically.
          con.Open();

          // Set up the command and attach it to the connection.
          cmd.Connection = con;
          cmd.CommandText = "select * from mytable";

          // A data adapter links commands to disconnected datasets.
          using ( SqlDataAdapter da = new SqlDataAdapter( cmd ) ) {
            // Put the resultset in a data table and you're done!
            using ( DataTable result = new DataTable() ) {
              da.Fill( result );
              return result;
            }
          }
        }
      }
    } catch ( Exception ex ) {
      // Log the error
      throw;
    }
  }
}

[iamthwee]

You got a vb.net example, I'm struggling to convert that to vb.net? Thanx. Links would be good.

[Hamrick]

Oops, I'm sorry. I forgot what forum we were in. :D

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class DataManager
  Public Shared Function ExecuteSql( ByRef connectionString As String ) As DataTable
    Try
      ' A connection gets you access to the database.
      Using con As New SqlConnection( connectionString )
        ' A command has the query stuff for the database.
        Using cmd As New SqlCommand
          ' Always open the connection. :)
          ' If the connection object is in a using statement, the Dispose
          ' method closes it automatically.
          con.Open()

          ' Set up the command and attach it to the connection.
          cmd.Connection = con
          cmd.CommandText = "select * from mytable"

          ' A data adapter links commands to disconnected datasets.
          Using da As New SqlDataAdapter( cmd )
            ' Put the resultset in a data table and you're done!
            Using result As new DataTable
              da.Fill( result )
              Return result
            End Using
          End Using
        End Using
      End Using
    Catch ex As Exception
      ' Log the error
      Throw
    End Try
  End Sub
End Class

That's VB.NET for the 2.0 framework. For older versions you can't use the using clauses but you shouldn't be using older versions if you can get away with it anyway. ;)

[iamthwee]

Ah-ha

Okay so where do I put

"Server=<server>\SQLEXPRESS;User ID=<username>;password=<password>;Database=<databasename>;Persist Security Info=True"

in your code. If my pc name where I want to host it is \\pc239493 on my network?

[Hamrick]

You can put it anywhere you want as long as you can access it. Like I was saying, I like to have a public Program class that has that kind of stuff in it as public properties, but you could even remove the connectionString parameter and replace it in the method with just a string. That's easy but ugly and not very secure.

Public Class DataManager
  Public Shared Function ExecuteSql() As DataTable
    Try
      ' A connection gets you access to the database.
      Using con As New SqlConnection( "Server=\\pc239493\SQLEXPRESS;User ID=<username>;password=<password>;Database=<databasename>;Persist Security Info=True" )