Skip to content

Different ways to pass data between a .Net app and a SQL db.

December 25, 2011

    '###########################################
    '      SQL Fill 
    '###########################################
    'With SQL Query
    Public Shared Function GetData(ByVal Parameter As String) As DataTable
        GetData = New DataTable
        Try
            Using da As New SqlDataAdapter("SELECT * FROM tblData WHERE Parameter = @Parameter ", Conn)
                da.SelectCommand.Parameters.AddWithValue("@Parameter", Parameter)
                da.Fill(GetData)
            End Using
        Catch ex As Exception
            'Error Handling
        End Try
    End Function

    'With Stored Procedure
    Public Shared Function GetDataSP(ByVal Parameter As String) As DataTable
        GetDataSP = New DataTable
        Try
            Using da As New SqlDataAdapter("spData", Conn)
                da.SelectCommand.CommandType = CommandType.StoredProcedure
                da.SelectCommand.Parameters.AddWithValue("@Parameter", Parameter)
                da.Fill(GetDataSP)
            End Using
        Catch ex As Exception
            'Error Handling
        End Try
    End Function
    '

    '###########################################
    '      SQL Update
    '###########################################
    'With SQL Query
    Public Shared Function UpdateData(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        UpdateData = False
        Try
            Using cmd As New SqlCommand("UPDATE tblData SET Parameter00 = @Parameter00 WHERE Parameter01 = @Parameter01", New SqlConnection(Conn))
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            UpdateData = True
        Catch ex As Exception
            'Error Handling
        End Try
    End Function

    'With Stored Procedure
    Public Shared Function UpdateDataSP(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        UpdateDataSP = False
        Try
            Using cmd As New SqlCommand("spUpdateData", New SqlConnection(Conn)) With {.CommandType = CommandType.StoredProcedure}
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            UpdateDataSP = True
        Catch ex As Exception
            'Error Handling
        End Try
    End Function
    '

    '###########################################
    '      SQL Insert
    '###########################################
    'With SQL Query
    Public Shared Function InsertData(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        InsertData = False
        Try
            Using cmd As New SqlCommand("INSERT INTO tblData ( Parameter00, Parameter01 ) VALUES ( @Parameter00, @Parameter01 )", New SqlConnection(Conn))
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            InsertData = True
        Catch ex As Exception
            'Error Handling
        End Try
    End Function

    'With Stored Procedure
    Public Shared Function InsertDataSP(ByVal Parameter00 As String, ByVal Parameter01 As String) As Boolean
        InsertDataSP = False
        Try
            Using cmd As New SqlCommand("spInsertData", New SqlConnection(Conn)) With {.CommandType = CommandType.StoredProcedure}
                cmd.Parameters.AddWithValue("@Parameter00", Parameter00)
                cmd.Parameters.AddWithValue("@Parameter01", Parameter01)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Dispose()
            End Using
            InsertDataSP = True
        Catch ex As Exception
            'Error Handling
        End Try
    End Function
    '
Advertisements

From → .Net, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: