vendordataservice.vb

its it tracker and used for track

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

Public Class VendorDataService

    Public Shared Function Save(ByVal ID As Integer, ByVal Name As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal State As String, ByVal Zip As String) As Integer
        If Not ID = Nothing Then
            ' update
            Return Update(ID, Name, Address1, Address2, City, State, Zip)
        Else
            ' insert 
            Return Insert(Name, Address1, Address2, City, State, Zip)
        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("vendor.* ")
            .Append("from vendor ")
            .Append("where vendor.vendor_id = @id")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id
        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("vendor.* ")
            .Append("from vendor ")
        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 vendorName As String) 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("vendor.* ")
            .Append("from vendor ")
            .Append("where vendor_name like '%' + @vendorname + '%' ")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.Parameters.Add("@vendorname", SqlDbType.VarChar).Value = vendorName
        cmd.CommandType = CommandType.Text
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        cn.Close()
        Return ds
    End Function

    Private Shared Function Insert(ByVal Name As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal State As String, ByVal Zip As String) 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 Vendor ")
            .Append("(vendor_name,vendor_address1,vendor_address2,vendor_city,vendor_state,vendor_zip) ")
            .Append(" values ")
            .Append("( ")
            .Append("@name, ")
            .Append("@address1, ")
            .Append("@address2, ")
            .Append("@city, ")
            .Append("@state, ")
            .Append("@zip ")
            .Append(")")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Name.Trim
        cmd.Parameters.Add("@address1", SqlDbType.VarChar).Value = IIf(Address1.Trim.Length > 0, Address1, DBNull.Value)
        cmd.Parameters.Add("@address2", SqlDbType.VarChar).Value = IIf(Address2.Trim.Length > 0, Address2, DBNull.Value)
        cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = IIf(City.Trim.Length > 0, City, DBNull.Value)
        cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = IIf(State.Trim.Length > 0, State, DBNull.Value)
        cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = IIf(Zip.Trim.Length > 0, Zip, DBNull.Value)
        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 Name As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal State As String, ByVal Zip As String) 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 Vendor ")
            .Append("set ")
            .Append("vendor_name = @name, ")
            .Append("vendor_address1 = @address1, ")
            .Append("vendor_address2 = @address2, ")
            .Append("vendor_city = @city, ")
            .Append("vendor_state = @state, ")
            .Append("vendor_zip = @zip ")
            .Append("where vendor_id = @ID ")
        End With
        Dim cmd As New SqlCommand(sb.ToString, cn)
        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Name.Trim
        cmd.Parameters.Add("@address1", SqlDbType.VarChar).Value = IIf(Address1.Trim.Length > 0, Address1, DBNull.Value)
        cmd.Parameters.Add("@address2", SqlDbType.VarChar).Value = IIf(Address2.Trim.Length > 0, Address2, DBNull.Value)
        cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = IIf(City.Trim.Length > 0, City, DBNull.Value)
        cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = IIf(State.Trim.Length > 0, State, DBNull.Value)
        cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = IIf(Zip.Trim.Length > 0, Zip, DBNull.Value)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID

        cmd.CommandType = CommandType.Text
        Dim i As Integer = cmd.ExecuteNonQuery()
        cn.Close()
        Return i
    End Function
End Class