Converting from noob sql to Linq

[jwcoleman87]

I've learned how to talk to an sql database without Linq, or so I think. Well, let me show you what I've learned how to do without Linq

Public Class sqlControl
    Public sqlCon As New SqlConnection
    Public sqlCmd As SqlCommand
    Public sqlDA As SqlDataAdapter
    Public sqlDataset As DataSet
    Public Function Login(User As String, Pwd As String) As Boolean
        Try
            'us04w025
            sqlCon.ConnectionString = "Server = us04w025;Database = FastrackData; User = 'Technician'; pwd = 'Asurion1';Persist Security Info=True"
            If hasConnection() = True Then
                runQuery("SELECT UserID, UserPWD FROM Technician " & _
                         "WHERE UserID = '" & User & "' " & _
                         "AND UserPWD = '" & Pwd & "'")

                If sqlDataset.Tables(0).Rows.Count > 0 Then
                    Return 1
                Else
                    MsgBox("User name or password is incorrect")
                    Return 0
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.Message())
            Return False
        End Try
        Return False
    End Function
    Public Function hasConnection() As Boolean
        Try
            sqlCon.Open()
            sqlCon.Close()
            Return True
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try
    End Function
    Public Function runQuery(Query As String) As DataSet
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(Query, sqlCon)
            'LOAD SQL RECORDS FOR DATAGRID
            sqlDA = New SqlDataAdapter(sqlCmd)
            sqlDataset = New DataSet
            sqlDA.Fill(sqlDataset)
            sqlCon.Close()
            Return sqlDataset
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return sqlDataset
    End Function
    Public Function BeginRepair(serialNumber As String, startTime As DateTime,
                                status As String) As Integer
        Dim UpdateCmd As String = (" UPDATE Repair " & _
                                   "SET Status ='" & status & "', " & _
                                   "StartTime = '" & startTime & "'" & _
                                   "WHERE SerialNumber = '" & serialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function AssignRepair(serialNumber As String, techID As String, status As String) As Integer
        Try
            Dim strInsert As String = "INSERT INTO Repair " & _
                                      "(SerialNumber, TechnicianID, Status)" & _
                                      "VALUES (" & _
                                      "'" & serialNumber & "'" & _
                                      ",'" & techID & "'" & _
                                      ",'" & status & "')"
            sqlCon.Open()
            sqlCmd = New SqlCommand(strInsert, sqlCon)
            sqlCmd.ExecuteNonQuery() ' for insert, update, or delete

        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        End Try
        sqlCon.Close()
        Return 1
    End Function
    Public Function ChangeAssignment(SerialNumber As String, UserName As String,
                      Status As String) As Integer

        Dim UpdateCmd As String = (" UPDATE Repair " & _
                   "SET TechnicianID ='" & UserName & "', " & _
                   "Status = '" & Status & "', " & _
                   "StartTime = NULL ," & _
                   "EndTime = NULL " & _
                   "WHERE SerialNumber = '" & SerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function UpdateRepair(NewSerialNumber As String, OldSerialNumber As String) As Integer
        Dim UpdateCmd As String = (" UPDATE Repair " & _
                   "SET SerialNumber ='" & NewSerialNumber & "' " & _
                   "WHERE SerialNumber = '" & OldSerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function ChangeDisposition(SerialNumber As String, NewStatus As String) As Integer
        Dim UpdateCmd As String = (" UPDATE Repair " & _
                   "SET Status ='" & NewStatus & "' " & _
                   "WHERE SerialNumber = '" & SerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function UpdateRepair(SerialNumber As String, EndTime As DateTime,
                              Status As String, CSRQuoteFlag As Integer) As Integer

        Dim UpdateCmd As String = (" UPDATE Repair " & _
                   "SET Status ='" & Status & "', " & _
                   "EndTime = '" & EndTime & "'," & _
                   "CSRQuoteFlag = '" & CSRQuoteFlag & "' " & _
                   "WHERE SerialNumber = '" & SerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function UpdateRepair(SerialNumber As String, TechnicianId As String, StartTime As DateTime,
                                  Status As String) As Integer


        Dim UpdateCmd As String = ("UPDATE Repair " & _
                           "SET TechnicianID ='" & TechnicianId & "', " & _
                           "StartTime = '" & StartTime & "'," & _
                           "Status = '" & Status & "' " & _
                           "WHERE SerialNumber='" & SerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function SendToUnitRepair(SerialNumber As String, TechnicianId As String,
                                     Status As String, StartTime As DateTime) As Integer


        Dim UpdateCmd As String = ("UPDATE Repair " & _
                           "SET TechnicianID ='" & TechnicianId & "', " & _
                           "StartTime = '" & StartTime & "'," & _
                           "EndTime = NULL, " & _
                           "Status = '" & Status & "' " & _
                           "WHERE SerialNumber='" & SerialNumber & "'")
        Try
            sqlCon.Open()
            sqlCmd = New SqlCommand(UpdateCmd, sqlCon)
            Dim ChangeCount As Integer = sqlCmd.ExecuteNonQuery()
            sqlCon.Close()
            Return ChangeCount
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return 0
    End Function
    Public Function ListActiveRepairs(ByVal List As ListBox, TechnicianID As String) As ListBox
        List.Items.Clear()
        sqlCmd = New SqlCommand("SELECT SerialNumber FROM REPAIR " & _
                                "WHERE TechnicianID = '" & TechnicianID & "'" & _
                                "AND Status = 'Unit Repair'", sqlCon)
        sqlCon.Open()
        Dim R As SqlDataReader = sqlCmd.ExecuteReader()
        While R.Read
            List.Items.Add(R.GetString(0))
        End While
        sqlCon.Close()
        Return List
    End Function
    Public Function ListAssignedRepairs(ByVal List As ListBox, TechnicianID As String) As ListBox
        List.Items.Clear()
        sqlCmd = New SqlCommand("SELECT SerialNumber FROM REPAIR " & _
                                "WHERE TechnicianID = '" & TechnicianID & "'" & _
                                "AND Status = 'Assigned'", sqlCon)
        sqlCon.Open()
        Dim R As SqlDataReader = sqlCmd.ExecuteReader()
        While R.Read
            List.Items.Add(R.GetString(0))
        End While
        sqlCon.Close()
        Return List
    End Function
    Public Function ListInactiveRepairs(ByVal List As ListBox, TechnicianID As String, SelectedDate As Date) As ListBox
        List.Items.Clear()
        Try
            sqlCmd = New SqlCommand("SELECT SerialNumber FROM REPAIR " & _
                                              "WHERE TechnicianID = '" & TechnicianID & "'" & _
                                              "AND Status <> 'Unit Repair' " & _
                                              "AND CONVERT(date, EndTime, 101) = '" & SelectedDate & "'" & _
                                              "AND Status <> 'Paused'" & _
                                              "AND Status <> 'Assigned'", sqlCon)
            sqlCon.Open()
            Dim R As SqlDataReader = sqlCmd.ExecuteReader()
            While R.Read
                List.Items.Add(R.GetString(0))
            End While
            sqlCon.Close()
            Return List
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    Public Function GetRepairRecords(SerialNumber As String) As DataSet
        Dim RepairRecord As New DataSet
        RepairRecord = runQuery("SELECT * From Repair WHERE SerialNumber = '" & SerialNumber & "'")
        Return RepairRecord
    End Function
    Public Function GetRepairRecords(TechnicianID As String, Status As String, DateBegan As String, DateFinished As String,
                                      HourBegan As String, HourFinished As String, CSRQuoteFlag As String) As DataSet
        Dim RepairRecords As New DataSet
        RepairRecords = runQuery("SELECT * FROM Repair " & _
                                 "WHERE TechnicianID = '" & TechnicianID & "' " & _
                                 "AND Status = '" & Status & "' " & _
                                 "AND Convert(date, StartTime, 101) = '" & DateBegan & "'" & _
                                 "AND Convert(date, EndTime, 101) = '" & DateFinished & "'" & _
                                 "AND DATEPART(hour, StartTime) = = '" & HourBegan & "'" & _
                                 "AND DATEPART(hour, EndTime) = '" & HourFinished & "'" & _
                                 "AND CSRQuoteFlag = '" & CSRQuoteFlag & "' ")
    End Function
    Public Function TotalRepairs(TechnicianID As String, SelectedDate As DateTime, Status As String) As Integer

        runQuery("SELECT COUNT(ALL SerialNumber) From Repair " & _
                 "WHERE CONVERT(date, EndTime, 101) = '" & SelectedDate.Date & "'" & _
                 "AND Status = '" & Status & "' " & _
                 "AND TechnicianID = '" & TechnicianID & "'")

        Return sqlDataset.Tables(0).Rows(0).Item(0)
    End Function

End Class

This code is bulky, and ugly, and not very reuseable. Every time I want to do something different with my tables I need to write new functions, or overridden copies of old functions. I'm hoping that linq can solve my issue. The problem is, I'm not quite sure where to start. Does anyone have a resource with a basic Linq tutorial. I went to the MSDN and the first thing it had me do was Imports System.Data.Linq, and it turns out that System.Data.Linq is not found. (I thought I was already using linq in a previous example from another thread, which is also on this front page. I already have a dataset added to my project, which includes all the tables I want to work with. It is redundant for me to continue and use this old class (while adding even more mess to it) alongside that already existing data object.

Where to begin?

 

[jwcoleman87]

One of my questions, to be more specific. Is this data oject in my project realtime? If I call an update command with linq to this dataset object will the changes reflect on the actual database immediately, or is there another function that needs called?

 

[jwcoleman87]

I think what I'm trying to do is convert my project to this, minus the 'fastrackdatadataset'

 

[Herman]

If you are going to use SQL Server and Linq, I would personally go with Linq to SQL or Entity Framework and forego the dataset altogether. It's straightforward and easy to use. I can show you enough to get started.

Let's use this query as an example:

runQuery("SELECT COUNT(ALL SerialNumber) From Repair " & _
"WHERE CONVERT(date, EndTime, 101) = '" & SelectedDate.Date & "'" & _
"AND Status = '" & Status & "' " & _

"AND TechnicianID = '" & TechnicianID & "'")

- First you need to add your data model to your project. In Visual Studio, open up the Server Explorer. By default it should be docked with the Solution Explorer or the toolbox as in the image below. If not, you can open it in the View menu...



- Once it's opened, right click Data Connection and select Add Connection... Then enter your server connection settings and select your database. When you are done you should see your database listed under Data Connections and be able to browse its structure.

- Then add a new item to your project, and in the templates window select Linq to SQL Classes. Name the file MyDataModel.dbml for example.

- This will open up the DBML into a blank workspace area. From there look into the Server Explorer and drag the Repair table onto the workspace. You should see the table on the workspace, with all its fields. Save the DBML and close it.

- Now in code, you first need to instantiate a data context to your data model. You will use the context as an instance of a connection to the database. You can pass a connection string to the data context constructor also.

        Using db As New MyDataModelDataContext(connString)

        End Using

- All that is left now is to write the Linq query. I won't spend too much time here this should be pretty self-explanatory:

    Public Function TotalRepairs(TechnicianID As String, SelectedDate As DateTime, Status As String) As Integer
        Using db As New MyDataModelDataContext(connString)
            Return db.Repairs.Where(Function(r) r.CompanyId.Date = SelectedDate.Date AndAlso r.Status = Status AndAlso r.TechnicianID = TechnicianID).Count
        End Using
    End Function

- For update queries, you need to do the change to the context, and then tell the context to report the changes to the database through the .SubmitChanges() method.

        Using db As New MyDataModelDataContext(connString)
            For Each result In db.Repairs.Where(Function(r) r.SerialNumber = SerialNumber)
                result.Status = NewStatus
            Next
            db.SubmitChanges()
        End Using

Please note however that Linq to SQL does not support updating multiple rows at once, so the above code will update each row one by one, which might be slow. Entity Framework support transactions and atomic operations, and works pretty much the same way as far as code goes, so it might be better suited. However you can always execute your queries directly through the DataContext and in this case this will be dramatically faster, like so:

        Using db As New MyDataModelDataContext(connString)
            db.ExecuteCommand("UPDATE Repair SET Status = {0} WHERE SerialNumber = {1}", {NewStatus, SerialNumber})
        End Using

That's all there is to it. All you have left to do is learn the Linq syntax a bit more and rewrite your queries like this!

 

[jwcoleman87]

This is kindof blowing my mind right now. I never realized how powerful visual studio is.

 

[Herman]

Hehe unfortunately that is the case for a lot of people who feel intimidated by .NET at first, reluctant long-term VB6 users for example don't see how much everything is easier using modern tools.

 

[Herman]

Also I forgot to add, this approach is even more awesome when dealing with more complicated queries that include joins. Each table you drag into the DBML retains its relationship information, and each relationship is implemented as a navigation property. So for example if you have TableA and TableB in a one to one relationship through a foreign key, you can do something like this:

Dim r = db.TableA.Where(Function(a) a.TableB.SomeField = SomeValue)

Which is essentially the same as

SELECT a.*
FROM TableA a
INNER JOIN TableB b ON a.PKey = b.FKey
WHERE b.SomeField = SomeValue

Linq is so expressive and fun to write it trumps direct SQL any day for me...

 

[jwcoleman87]

I'm experimenting with all that you've written thus far, and I've been able to add the linq to sql classes. Although by clicking around Visual Studio I've managed to crash it. Fortunately all my data was salvaged, but what's up with that? To be specific, I tried to right click the database and click 'add table'... Crashed the studio.

Anyways. I have one question regarding This bit of code:

Using db As New MyDataModelDataContext(connString) 
End Using 

Is this using command wrapped around my entire .vb file? Or just within my class when I'm about to perform an sql command? Or wrapping everything in my class? I think this is probably an elementary noob question, but bear with me here. Oh wait, as I look at your post more closely I noticed that you are enclosing only the code which calls upon the database object (repair, in this case). One question comes to mind, what reason would you have for passing a new connection string, when the Linq (Link) to sql has already been established when we added the connection in the first place? This is also probably an elementary question, but please, understand I am gleaning much from you and jm.

 

[Herman]

To be specific, I tried to right click the database and click 'add table'... Crashed the studio.

Not normal, try to repair your installation and clean registry... No problem on 2013 Update 3 here.

One question comes to mind, what reason would you have for passing a new connection string, when the Linq (Link) to sql has already been established when we added the connection in the first place?

It's true that there is already a connection string embedded in the DBML and also one in the application settings, and you can use them on the final as it is, but in a real world situation, you would probably blank those out and pass the connection string for the database that the user is currently working on. Also in some cases you might not want the connection string to be accessible at all by the user, so you might want to remove it from the config file and embed it directly in your code as a resource.

 

[jwcoleman87]

Honestly I think the db is boogered up. It wont even let me use the designer in sql management studio, throws some error I'm not familiar with. The actual db is on my bosses computer, and I would need to convince him that his settings are all screwed up. I can use the designer at home on my copy of sql management studio. And when I create databases on my local machine at work it lets me use the designer without any trouble, but when I try to edit the database via designer when not on his pc it throws errors.

 

[jwcoleman87]

I think I actually may have identified the issue:


I could not find this in my sdk at work. This window. It was not in view at work as you said, but at home I see it there. Also the "server explorer" tab you mentioned is not present.



I got the linq to sql working by just right clicking my project and then adding a new item, and then I walked through these steps:

Once it's opened, right click Data Connection and select Add Connection... Then enter your server connection settings and select your database. When you are done you should see your database listed under Data Connections and be able to browse its structure.

So maybe it is because I missed these first steps:

First you need to add your data model to your project. In Visual Studio, open up the Server Explorer. By default it should be docked with the Solution Explorer or the toolbox as in the image below. If not, you can open it in the View menu...

Problem is, I didnt see it in the view menu, and I didn't see it in the solution explorer. (at work). I saw it the view menu in my home environment. Something is amiss.

 

[Herman]

It might be a permissions issue, most users do not have rights to edit a database normally. In any case all you really need is a mock up with some test data. You could ask the DB admin at work to get you the database's CREATE scripts, recreate the database structure locally, and insert some data to work with. The contents of the db at this stage really does not matter to you, the model is all you need to develop your code around.

 

[Herman]

Which version and edition of Visual Studio are you using?

 

[jwcoleman87]

Another problem, when I try to connect to my local instance at home it tells me "an incompatible sql server version was detected"

I have sql server 2014 management studio on my computer running sql server 12.0.2000

I dont think it is permissions, because at work I have dbcreator as a role I have access with. I can create whatever I want in management studio with this login. And now I'm having issues at home too! I'm using vs express 13 for desktop at home and also (I think) at work

 

[jwcoleman87]

I found this:

SQL Server Data Tools Download

Might need this...