1 Connection or the other

[paulthepaddy]

hi guys, i am just looking for some advise which im pretty sure will make me need to change the way im doing this.

EDIT : Option Strick Will Be Used, Wont Be Using Variables Without Types

I Am trying to get my application to decide which connection string and SOL components it is going to use.

I am using the sync framework to have a local Database which can be used while no internet connection is available, then sync the database when their is a internet connection.

But i would also like to be able to connect Directly (without the sync framework) to my SQL Server when their is a connection. the problem im facing is the select Statement(it works but cant be used like it is)

is their a better way to determine whether to use SqlClient.SqlConnection OR SqlServerCe.SqlCeConnection any idea on a proper and better way to do this would be great, thanks all

Public Function IsConnectionAvailable() As Boolean
            Return My.Computer.Network.Ping("My SQL Server")
End Function

Dim Connection
        Dim CarCmd
        Dim OrdCmd
        Dim WDCmd

        Select Case IsConnectionAvailable()
            Case False
                Connection = New SqlServerCe.SqlCeConnection(My.Settings.ClientImageConnectionString)
                CarCmd = New SqlServerCe.SqlCeCommand("SELECT * FROM Cars WHERE Reg = @Reg", Connection)
                OrdCmd = New SqlServerCe.SqlCeCommand("SELECT * FROM OrderNumbers WHERE Reg = @Reg", Connection)
                WDCmd = New SqlServerCe.SqlCeCommand("SELECT * FROM WorkDone WHERE (Reg = @Reg) AND (OrderNumber = @OrderNumber)", Connection)
            Case True
                Connection = New SqlClient.SqlConnection(My.Settings.ServerImageConnectionString)
                CarCmd = New SqlClient.SqlCommand("SELECT * FROM Cars WHERE Reg = @Reg", Connection)
                OrdCmd = New SqlClient.SqlCommand("SELECT * FROM OrderNumbers WHERE Reg = @Reg", Connection)
                WDCmd = New SqlClient.SqlCommand("SELECT * FROM WorkDone WHERE (Reg = @Reg) AND (OrderNumber = @OrderNumber)", Connection)
        End Select

 

[IanRyder]

Hi,

i haven't had much teaching in VB.Net but i really dont like dim without defining its type and i would guess alot of people would say its bad practice.

Oh, Yes!, first thing, turn Option Strict On Now and NEVER turn it off ever again. This will help you solve all type conversion errors as and when they occur, as well as picking up the fact that you have not defined your variable types as you have in your code now. To do this, use Tools->Options to change the default setting for Option Strict and to turn Option Strict on for your current project go to your Application Properties and change this on the Compile Tab.

To answer your actual question, and if I understand you correctly, it sounds like you have the option of connecting to your Database either via a LAN or via a WAN through IIS? If that's the case, then I would suggest that you always connect to the Database via the LAN to eradicate any need for connecting via the Internet which is always going to be slower. That way, you can get rid of the case statement altogether and just define everything as part of the SqlClient Namespace.

Hope that helps.

Cheers,

Ian

 

[paulthepaddy]

Hi Ian, thanks for reply and .... yea i thought so, strict was only turned off due to a fresh install of VS i did normally have it turned on but i did think that is bad codeing.

sorry i didn't explain my question well enough, ME personally will be the only one able to connect with LAN the rest will be WAN connections(will use a setting so i can use LAN while others WAN)

il explain the scenario a bit better.

I am using the sync framework to allow me to use my application when i dont have a connection to the SQL Server, so it is using the SQL Compact Edition and i then have to sync the SQL CE with the SQL Server when a connection is available.

what i am trying to achive is to get my application to run its SQL Commands directly to the SQL Server when it can access it, and to use the Local SQL Compact Database whenever it cant access the server, although my code

Public Function IsConnectionAvailable() As Boolean
            Return My.Computer.Network.Ping("www.google.com")
End Function

is checking for google it will be checking for my SQL Server IP Address.

so im hoping to plan ahead something like this

       'If A Connection To The SQL Server 
        Select Case IsConnectionAvailable()

       'Isn't Available Then Use The SQLServer Compact Edition Componants 
            Case False 
                                                                         'Connection String For Local DataBase
                Connection = New [COLOR=#0000cd]SqlServerCe.SqlCeConnection[/COLOR](My.Settings.[COLOR=#0000cd]ClientImageConnectionString[/COLOR])
                CarCmd = New [COLOR=#0000cd]SqlServerCe.SqlCeCommand[/COLOR]("SELECT * FROM Cars WHERE Reg = @Reg", Connection)
                OrdCmd = New [COLOR=#0000cd]SqlServerCe.SqlCeCommand[/COLOR]("SELECT * FROM OrderNumbers WHERE Reg = @Reg", Connection)
                WDCmd = New [COLOR=#0000cd]SqlServerCe.SqlCeCommand[/COLOR]("SELECT * FROM WorkDone WHERE (Reg = @Reg) AND (OrderNumber = @OrderNumber)", Connection)

       ' If Their Is A Connection To The SQL Server Use Standard Componants
            Case True                                                'Server Connection String
                Connection = New [COLOR=#0000cd]SqlClient.SqlConnection[/COLOR](My.Settings.[COLOR=#0000cd]ServerImageConnectionString[/COLOR])
                CarCmd = New [COLOR=#0000cd]SqlClient.SqlCommand[/COLOR]("SELECT * FROM Cars WHERE Reg = @Reg", Connection)
                OrdCmd = New [COLOR=#0000cd]SqlClient.SqlCommand[/COLOR]("SELECT * FROM OrderNumbers WHERE Reg = @Reg", Connection)
                WDCmd = New [COLOR=#0000cd]SqlClient.SqlCommand[/COLOR]("SELECT * FROM WorkDone WHERE (Reg = @Reg) AND (OrderNumber = @OrderNumber)", Connection)
        End Select

I assume that it would be quicker for the Application to use the SQL Server directly rather than having to pass through the website hosting the WFC Service Exposing the Service that will communicate with the SQL Server.
Also the webservice is only set up for syncing the two databases together rather than running queries and the like off. hope this make it clearer of what i am hoping to do.

thanks for your time and effort ian

 

[IanRyder]

Hi,

OK, I see what you are wanting to do.

However, to make sure that you accommodate Option Strict and due to the limitations of Variable Scope you have no real option but to create two blocks of code which are effectively the same but which are interrogating two different Database types.

Here is an example of how I would approach this:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Const carSQLString As String = "SELECT * FROM Cars WHERE Reg = @Reg"
  Const ordSQLString As String = "SELECT * FROM OrderNumbers WHERE Reg = @Reg"
  Const wdSQLString As String = "SELECT * FROM WorkDone WHERE (Reg = @Reg) AND (OrderNumber = @OrderNumber)"
 
  Dim myDataSet As New DataSet
  With myDataSet.Tables
    .Add("CarData")
    .Add("OrderData")
    .Add("WorkDoneData")
  End With
 
  Select Case IsConnectionAvailable()
    Case False
      Using Connection As New SqlServerCe.SqlCeConnection(My.Settings.ServerImageConnectionString)
        Using CarCmd As New SqlServerCe.SqlCeCommand(carSQLString, Connection)
          Using OrdCmd As New SqlServerCe.SqlCeCommand(ordSQLString, Connection)
            Using WDCmd As New SqlServerCe.SqlCeCommand(wdSQLString, Connection)
              Try
                Connection.Open()
                myDataSet.Tables("CarData").Load(CarCmd.ExecuteReader)
                myDataSet.Tables("OrderData").Load(OrdCmd.ExecuteReader)
                myDataSet.Tables("WorkDoneData").Load(WDCmd.ExecuteReader)
                Connection.Close()
              Catch ex As Exception
                MsgBox("Some Error Occurred!")
              End Try
            End Using
          End Using
        End Using
      End Using
    Case True
      Using Connection As New SqlClient.SqlConnection(My.Settings.ServerImageConnectionString)
        Using CarCmd As New SqlClient.SqlCommand(carSQLString, Connection)
          Using OrdCmd As New SqlClient.SqlCommand(ordSQLString, Connection)
            Using WDCmd As New SqlClient.SqlCommand(wdSQLString, Connection)
              Try
                Connection.Open()
                myDataSet.Tables("CarData").Load(CarCmd.ExecuteReader)
                myDataSet.Tables("OrderData").Load(OrdCmd.ExecuteReader)
                myDataSet.Tables("WorkDoneData").Load(WDCmd.ExecuteReader)
                Connection.Close()
              Catch ex As Exception
                MsgBox("Some Error Occurred!")
              End Try
            End Using
          End Using
        End Using
      End Using
  End Select
End Sub

In this example, it does not matter which Database is used since the end result is a single DataSet which contains the information from one Database or the other. In addition to this, the Database objects which are used are finally Disposed when they are no longer needed to ensure resources are released back to the system.

Incidentally, I have never actually used SqlServerCe, so I have made an assumption here that the SqlServerCe objects implement the IDisposable interface so that Using blocks can be used. You may just need to check this.

Hope that helps.

Cheers,

Ian

[Edit] Ooops, forgot to add the Parameter information but I am sure you can sort that.

 

[paulthepaddy]

Hi Ian, i see where you are coming from and i will either go for the Select Case like you have used above, or i might get the select case to call a sub Local and server OR i am going to look into getting the SQL Server CE to work using the SQL Client Components im sure its not that easy otherwise they probably wouldn't have made separate components for it but we will see

Thanks Ian

 

[IanRyder]

Hi,

OR i am going to look into getting the SQL Server CE to work using the SQL Client Components im sure its not that easy otherwise they probably wouldn't have made separate components for but we will see

I did consider that but I am not in a position to test this for myself so I did not mention it. However, I definitely think its worth a stab since it will really help to cut down on your future considerations.

Please do post back your results when you have had chance to test this since this would be useful information for others who may find themselves in a similar position in the future.

Good luck.

Ian

 

[paulthepaddy]

Hi Ian, I did find this article
Accessing SQLCE :: Chapter 5. Caching Data with SQL Server CE :: Part II: Essential Architectural Concepts :: Building Solutions With the Microsoft .NET Compact Framework :: Programming :: eTutorials.org

At the very buttom of the page their is a section on a way to connect to both types of SQL Server, i cant test it because... honestly, i dont have a clue whats going on their, it would seem a little to complicated for me and tbh i know whats happening in the Select Case i used.

but as you said it might be usefull for other people.