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]

yeah I've never updated either copy, home or work, but I only downloaded them this year? Shouldn't I already have all this crap?

I'm downloading update 3 and 4 now. just to see if I need it.

 

[jwcoleman87]

After updating everything appears to be working as intended. I'll have to update everything at work as well D:

 

[jwcoleman87]

    Public Function UpdateRepair(SerialNumber As String, EndTime As DateTime,
                              Status As String, CSRQuoteFlag As Integer) As Integer
        Dim index As Integer = 0
        Using db As New ProductionDataModelDataContext
            For Each result In db.Repairs.Where(Function(r) r.SerialNumber = SerialNumber)
                result.Status = Status
                result.CSRQuoteFlag = CSRQuoteFlag
                result.EndTime = EndTime.Date
                index += 1
            Next
            If index = 1 Then
                db.SubmitChanges()
                Return 1
            Else : Return 0
            End If
        End Using
    End Function

I believe this will work, time will tell when I attempt these changes at work. I remember you mentioning about working only with one record at a time. If in fact I only want ONE record to be updated by this function (as a technician finished a single repair) is there a need for the looping? Would it be wiser to simply use the execute command function you showed?

 

[Herman]

Just as a sidenote, have a look at the GetChangetSet method of the DataContext.

DataContext.GetChangeSet Method (System.Data.Linq)

Public Function UpdateRepair(SerialNumber As String, EndTime As DateTime, Status As String, CSRQuoteFlag As Integer) As Integer
    Using db As New ProductionDataModelDataContext
        For Each result In db.Repairs.Where(Function(r) r.SerialNumber = SerialNumber)
            result.Status = Status
            result.CSRQuoteFlag = CSRQuoteFlag
            result.EndTime = EndTime.Date
        Next
        Dim changes = db.GetChangeSet
        db.SubmitChanges()
        Return changes.Updates.Count
    End Using
End Function

 

[Herman]

Also for the looping, if you expect to have only one result from the select part of the query, it really doesn't matter. If there is more than one however they will all be processed. If you only want to apply the change to the first record you find no matter how many you find do something like this:

Public Function UpdateRepair(SerialNumber As String, EndTime As DateTime, Status As String, CSRQuoteFlag As Integer) As Integer
    Using db As New ProductionDataModelDataContext
        Dim result = db.Repairs.Where(Function(r) r.SerialNumber = SerialNumber).FirstOrDefault
        If Not result Is Nothing Then
            result.Status = Status
            result.CSRQuoteFlag = CSRQuoteFlag
            result.EndTime = EndTime.Date
        End If
        Dim changes = db.GetChangeSet
        db.SubmitChanges()
        Return changes.Updates.Count
    End Using
End Function

You could also use the ExecuteCommand method. If there is only one record to update anyways it won't really make a difference in speed. It might make a difference in code size however.