EF6 Connection Strings Dynamic

[paulthepaddy]

Hey
Frm_Home is the application starting form, which before anything calls the login form(Frm_Login)

Public Class frm_Home
    Public CurrentUser As User
    Private db As ImageEntities

    Private Sub frm_Home_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim frm As New frm_Login
        If frm.ShowDialog = Windows.Forms.DialogResult.OK Then
            CurrentUser = frm.CurrentUser
            frm.Dispose()
            Try
                db = GetDB()
                Me.lbl_Welcome.Text = "Welcome " & CurrentUser.User_FirstName & " " & CurrentUser.User_LastName
            Catch ex As Exception

            End Try
        Else
            Application.Exit()
        End If
    End Sub

So the login form opens, Calls Connectionstring sub which gets the SQLServer connectiong string

Public Class frm_Login
    Public CurrentUser As User
    Private db As New ImageEntities

    Private Sub frm_Login_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        If My.Settings.SQLServer_Address = "" Then ConnectionSettings()
    End Sub
    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        If Me.UsernameTextBox.Text = "" Or Me.PasswordTextBox.Text = "" Then
            MsgBox("Please Enter Username & Password")
            Exit Sub
        End If

        If IsConnectionAvalable() = False Then Exit Sub

        Try
            db = GetDB()
        Catch ex As Exception
            MsgBox("Failed To Connect To Server" & Environment.NewLine & "Please Check Connection Settings")
            Exit Sub
        End Try

        CurrentUser = (From U As User In db.Users Where U.User_UserName = Me.UsernameTextBox.Text AndAlso U.User_Password = Me.PasswordTextBox.Text Select U).First
        Me.DialogResult = Windows.Forms.DialogResult.OK
        Me.Close()
    End Sub

The login Form then calls GetDB which gives the Entity the dynamic connection string, connects and gets the user from the User entity and passes it back to the home form

Public Sub ConnectionSettings()
        Dim SQLConnectionDialog As New DataConnectionDialog()
        SQLConnectionDialog.DataSources.Add(DataSource.SqlDataSource)
        SQLConnectionDialog.SelectedDataProvider = DataProvider.SqlDataProvider
        SQLConnectionDialog.ConnectionString = My.Settings.SQLServer_Address
        If DataConnectionDialog.Show(SQLConnectionDialog) = Windows.Forms.DialogResult.OK Then
            My.Settings.SQLServer_Address = SQLConnectionDialog.ConnectionString
        Else
            SQLConnectionDialog.Close()
        End If
        SQLConnectionDialog.Dispose()
    End Sub

Public Function GetDB() As ImageEntities
        Dim EntityBuilder As New EntityConnectionStringBuilder
        EntityBuilder.Provider = "System.Data.SqlClient"
        EntityBuilder.ProviderConnectionString = My.Settings.SQLServer_Address  
        EntityBuilder.Metadata = "res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl"
        Return New ImageEntities(EntityBuilder.ToString)
    End Function

 

[Herman]

Just a hunch, but in your "start here" example, try passing a List(Of Dealership) to the datasource. ListBox and ComboBox can only bind to ILists.

Me.ComboBox1.DataSource = db.Dealerships.ToList()

Also, do you really need the new EF6 features? It would probably be safer to design around EF5 that is mature unless you need the new features...

 

[paulthepaddy]

Hi Herman, thank you for pointing that out, your spot on, im going to try that to see if I am getting a connection or not.

TBH no i dont need the new features, the thing that started me upgrading was the Enum support in EF5, Im far more comfortable with VS2010 but it doesn't support Enum in VS2010 so i upgraded to VS2013 which had EF6 so i just started using EF6, Would you recommend EF5? I'm quite the novice at programming and very used to EF4 as its the one i have used the most, just thought may as well get used to EF6 but i can safely say not a fan of it so far LMFAO.

Thanks Herman, its quite the big post that most people would look at and think CBA reading that

 

[Herman]

I too have used EF4 plenty, EF5 some, but I have not used EF6 yet. Honestly nowadays I mostly just Linq-SQL. Essentially the same thing but much much more basic. EF4/5 were always fine though I don't remember any horrible issue. EF5 is essentially EF4.3 targetted for .NET 4.5. EF6 is the first release of the now open-source entity framework. Personally I would stick with Microsoft QA-tested version 5.

Also you might want to edit your post up there to remove the extra stuff, and rename your thread to be a bit more descriptive...

 

[paulthepaddy]

owww, EF6 is not microsoft made, yea il go for EF5. I have to look into how to setup to use that instead of EF5, tried using the .ToList in my app and it didn't make anything better, so back to the drawing board it seems

Thanks for your time Herman

 

[paulthepaddy]

Herman, I could jump out of the screen and kiss you!!!!! changed to EF5 few minor changes and its working as I had hoped. Rep for you mate.

Could I ask you to have a quick look at the way im doing the login, is their anything their thats a big No No or if it works go with it?

 

[Herman]

Well logins are a touchy subject especially if they are used to secure information. Just storing a password in any way that can be reverted back to plaintext is a big no-no. Have a look at this thread: http://www.vbdotnetforums.com/mysql/59170-i-get-system-byte-when-select-des_decrypt.html#post164461 .

Generally speaking, it is MUCH easier and more robust to use SQL integrated security than to brew your own.

 

[paulthepaddy]

I had a look at the thread and at SQL integrated security, I think im going about this the wrong way. my only reason to use users are:
1 Username, our invoices ordernumbers ect use our initals, invoice number VB13082014P1 VB13082014W1 it has happened a number of times we have sent invoices in with the same invoice number before any software or anything like that, just good old word doc and email

2 So the SQLServer has logs of who does what and so their isn't issues with 2 or 3 users logged in on the same connection string and same users, our computers are just at our houses and not networked together so it is sent over the internet.

My goal for the logins is just that each of us has our own login to the SQLserver and ofcourse our connection string needs to reflect that.

What would your advice be :S I dont know how easy/hard intergrated security would be as we are not networked to the server.

Cherz

 

[Herman]

Well that's exactly what you should do. Just add the domain or workgroup users to the list of SQL Server logins (via Management Studio), and set your connection string to Integrated Security. Now the only thing you need to do is connect to the server, no need to even ask for a username or password, Windows knows it already.

If using the Windows session logon is not acceptable (many users per workstation), then do ask for username and password, enable mixed-mode authentication on your server, and create the logons you need in management studio. Use the username and password the user supplies in the connection string.

What you should definitely NOT be doing is keeping usernames and passwords in a table in the database. If you absolutely need to, the passwords should be hashed and encrypted as per the thread I linked earlier.

 

[paulthepaddy]

I'm just carring on with making this thing leaving it as it is for now. I have actually commented out the login call so it doesn't annoy me when debugging . but I was thinking on the subject, I'm not great with networking, I understand the basic principles of it, but already attempted to set up a network from a small server I baught which didn't go well at all lol. BUT I have a fancy router that has an FTP built into it which we all use as it also has DDNS, then it just came to me, it has VPN built into it, would you know off hand could I get the Intergrated security to work over VPN, dont worry about the details il get looking into the nity grity when im at that stage, but if you know that it can work then brialliant il make a note and DEFINITELY be using the intergrated lol, seems so much handier