VB.Net SQL Examples


Calling a stored procedure returning a value via passed variables
                    Try
                        Using conn As New SqlClient.SqlConnection(connectionString)
                            conn.Open()
                            cmd.Connection = conn
                            cmd.CommandType = Data.CommandType.StoredProcedure
                            cmd.CommandText = "MyStoredProcedureName"
                            cmd.Parameters.Clear()

			    'Input variable
                            cmd.Parameters.AddWithValue("SPVariable", MyProgramVariable)

			    'Input/Output Variable
                            cmd.Parameters.Add("MyIOVariable", SqlDbType.Char, 32)
                            cmd.Parameters.Item(1).Value = ""
                            cmd.Parameters.Item(1).Direction = ParameterDirection.InputOutput

                            Dim Result As Integer = -1
                            Result = cmd.ExecuteNonQuery
                            MyReplyVariable = Trim(cmd.Parameters.Item(1).Value.ToString) 'Assumes return value is numeric and needs to be string
                            conn.Close()
                        End Using
                    Catch
                        MyReplyVariable = MyDefaultValue 
                    End Try


Calling a stored procedure returning a record set
                Using conn As New SqlConnection(connectionString)
                    Dim reader As Data.SqlClient.SqlDataReader = Nothing
                    Try
                        conn.Open()
                        cmd.Connection = conn
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.CommandText = "MyStoredProcedureName"
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("SPVariable", MyProgramVariable)
                        reader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            reader.Read()
                            MyReplyVariable0 = reader.GetValue(0)
                            MyReplyVariable1 = reader.GetValue(1)
                            MyReplyVariable2 = reader.GetValue(2)
                        End If
                    Catch
                        MyExceptions = MyExceptions & MyExceptionID
                    End Try
                    reader.Close()
                    conn.Close()
                End Using
		reader = nothing


Adhoc query returning a single record
            Using conn As New SqlClient.SqlConnection(ConnectionString)
                Try
                    conn.Open()
                    Dim cmd As New Data.SqlClient.SqlCommand
                    cmd.Connection = conn
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "SELECT Column1, Column2, Column3 FROM MyTable WHERE This = That"
                    Try
                        reader = cmd.ExecuteReader()
                        If reader.HasRows Then
                            reader.Read()
                            MyReplyVariable0 = reader.GetValue(0)
                            MyReplyVariable1 = reader.GetValue(1)
                            MyReplyVariable2 = reader.GetValue(2)
                        End If
                    Catch ex As Exception
                        MyExceptions = MyExceptions & MyExceptionID
                    End Try
                    reader.Close()
                    conn.Close()
                Catch ex As Exception
                    MyExceptions = MyExceptions & MyExceptionID
                End Try
            End Using
            reader = Nothing

Adhoc query returning a record set and iterating the set
        Using conn As New SqlClient.SqlConnection(ConnectionString)
            conn.Open()
            Dim cmd As New Data.SqlClient.SqlCommand
            cmd.Connection = conn
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = "SELECT Column1, Column2, Column3 FROM MyTable WHERE This = That"
            Dim reader As Data.SqlClient.SqlDataReader = Nothing
            Try
                reader = cmd.ExecuteReader()
                If reader.HasRows Then
                    reader.Read()
                    Do
                        If reader.HasRows Then
                            MyReplyVariable0 = reader.GetValue(0)
                            MyReplyVariable1 = reader.GetValue(1)
                            MyReplyVariable2 = reader.GetValue(2)
                         End If
                    Loop While reader.Read()
                End If
                reader.Close()
            Catch
            End Try
            conn.Close()
        End Using