inventorydataservice.vb

its it tracker and used for track

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text

Public Class InventoryDataService

    Public Shared Function Save(ByVal ID As Integer, ByVal Description As String, _
        ByVal PartNumber As String, ByVal MFGNumber As String, ByVal VendorID As Integer, _
        ByVal Notes As String, ByVal ReorderPoint As Integer, ByVal QTY As Integer) As Integer
        If Not ID = Nothing Then
            ' update
            Return Update(ID, Description, PartNumber, MFGNumber, VendorID, Notes, ReorderPoint, QTY)
        Else
            ' insert 
            Return Insert(Description, PartNumber, MFGNumber, VendorID, Notes, ReorderPoint, QTY)
        End If
    End Function

    Public Shared Function Fetch(ByVal id As Integer) As DataSet
        Dim ds As New DataSet
        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings.Item("dfs_ITracker_dbConnectionString").ToString)
        cn.Open()
        Dim sb As New StringBuilder
        With sb
            .Append("select ")
            .Append("inventory.* ")
            .Append("from inventory ")
            .Append("where inventory.inv_id = " & id)
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.CommandType = CommandType.Text
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        cn.Close()
        Return ds
    End Function
    Public Shared Function FetchList() As DataSet
        Dim ds As New DataSet
        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings.Item("dfs_ITracker_dbConnectionString").ToString)
        cn.Open()
        Dim sb As New StringBuilder
        With sb
            .Append("select ")
            .Append("inventory.* ")
            .Append("from inventory ")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.CommandType = CommandType.Text
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        cn.Close()
        Return ds
    End Function
    Public Shared Function Search(ByVal id As Integer, ByVal desc As String, ByVal partnum As String, ByVal mfgnum As String, ByVal vendorid As Integer) As DataSet
        Dim ds As New DataSet
        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings.Item("dfs_ITracker_dbConnectionString").ToString)
        cn.Open()
        Dim sb As New StringBuilder
        With sb
            .Append("select ")
            .Append("inventory.* ")
            .Append("from inventory ")
            .Append("where ")
            Dim _added As Boolean = False
            If id > 0 Then
                If _added Then
                    .Append("or ")
                End If
                .Append("inv_id = " & id & " ")
                _added = True
            End If
            If desc.Trim.Length > 0 Then
                If _added Then
                    .Append("or ")
                End If
                .Append("inv_desc like '%" & desc & "%' ")
                _added = True
            End If
            If partnum.Trim.Length > 0 Then
                If _added Then
                    .Append("or ")
                End If
                .Append("inv_partnum like '%" & partnum & "%' ")
                _added = True
            End If
            If mfgnum.Trim.Length > 0 Then
                If _added Then
                    .Append("or ")
                End If
                .Append("inv_mfgnum like '%" & mfgnum & "%' ")
                _added = True
            End If
            If vendorid > 0 Then
                If _added Then
                    .Append("or ")
                End If
                .Append("vendor_id = " & vendorid & " ")
                _added = True
            End If
            If Not _added Then
                .Append("inv_id = inv_id ")
            End If
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.CommandType = CommandType.Text
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        cn.Close()
        Return ds
    End Function

    Private Shared Function Insert(ByVal Description As String, _
        ByVal PartNumber As String, ByVal MFGNumber As String, ByVal VendorID As Integer, _
        ByVal Notes As String, ByVal ReorderPoint As Integer, ByVal QTY As Integer) As Integer
        Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings.Item("dfs_ITracker_dbConnectionString").ToString)
        cn.Open()
        Dim sb As New StringBuilder
        With sb
            .Append("Insert Into Inventory ")
            .Append("(inv_desc,inv_partnum,inv_mfgnum,vendor_id,inv_notes,inv_reorderpt,inv_qty) ")
            .Append(" values ")
            .Append("( ")
            .Append("'" & Description.Trim.Replace("'", "''") & "',")
            .Append(IIf(PartNumber.Trim.Length > 0, "'" & PartNumber.Replace("'", "''") & "',", "null,"))
            .Append(IIf(MFGNumber.Trim.Length > 0, "'" & MFGNumber.Replace("'", "''") & "',", "null,"))
            .Append(VendorID & ", ")
            .Append(IIf(Notes.Trim.Length > 0, "'" & Notes.Replace("'", "''") & "',", "null,"))
            .Append(ReorderPoint & ", ")
            .Append(QTY & " ")
            .Append(") ")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.CommandType = CommandType.Text
        Dim i As Integer = cmd.ExecuteNonQuery()
        cn.Close()
        Return i
    End Function

    Private Shared Function Update(ByVal ID As Integer, ByVal Description As String, _
        ByVal PartNumber As String, ByVal MFGNumber As String, ByVal VendorID As Integer, _
        ByVal Notes As String, ByVal ReorderPoint As Integer, ByVal QTY As Integer) As Integer
        Dim cn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings.Item("dfs_ITracker_dbConnectionString").ToString)
        cn.Open()
        Dim sb As New StringBuilder
        With sb
            .Append("Update Inventory ")
            .Append("set ")
            .Append("inv_desc = '" & Description.Trim.Replace("'", "''") & "',")
            .Append("inv_partnum = " & IIf(PartNumber.Trim.Length > 0, "'" & PartNumber.Replace("'", "''") & "',", "null,"))
            .Append("inv_mfgnum = " & IIf(MFGNumber.Trim.Length > 0, "'" & MFGNumber.Replace("'", "''") & "',", "null,"))
            .Append("vendor_id = " & VendorID & ", ")
            .Append("inv_notes = " & IIf(Notes.Trim.Length > 0, "'" & Notes.Replace("'", "''") & "',", "null,"))
            .Append("inv_reorderpt = " & ReorderPoint & ", ")
            .Append("inv_qty = " & QTY & " ")
            .Append("where inv_id = " & ID)
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.CommandType = CommandType.Text
        Dim i As Integer = cmd.ExecuteNonQuery()
        cn.Close()
        Return i
    End Function

End Class