vb.net - Generate SQL Command object dynamically

Question

Can some please help how to dynamically generate command object. See below - I need to pass database name and table name to the function which should return number of rows in that table. I'm struggling to write this query. See code which i'm try

        Using _Conn As New SqlConnection(_ConnString)
            _SQLCommand = New SqlCommand()
            _SQLCommand.CommandText = "Select count(*) from " & _
            "lvar_Database " + ".dbo." + lvar_Table
            _SQLCommand.Parameters.Add(New SqlParameter("@Database_Name", SqlDbType.NVarChar))
            _SQLCommand.Parameters.Add(New SqlParameter("@Table_Name", SqlDbType.NVarChar))
            _SQLCommand.Parameters("@Database_Name").Value = lvar_Database
            _SQLCommand.Parameters("@Table_Name").Value = lvar_Table
            Try
                _Conn.Open()
                GetNumberofRows = Convert.ToInt32(_SQLCommand.ExecuteScalar())
            Catch ex As Exception
            End Try
        End Using

Regards

Paul Sasik · Accepted Answer · 2013-01-17 16:40:31Z

1

You do not need command parameters for this. Just assemble your query dynamically with the db and table name:

    Using _Conn As New SqlConnection(_ConnString)
        _SQLCommand = New SqlCommand()
        _SQLCommand.CommandText = _
            "Select count(*) from " + _
            lvar_database + _
            ".dbo." + _
            lvar_Table

        Try
            _Conn.Open()
            GetNumberofRows = Convert.ToInt32(_SQLCommand.ExecuteScalar())
        Catch ex As Exception
        End Try
    End Using

answered Jan 17, 2013 at 16:40

Paul Sasik

81.9k23 gold badges152 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

vb.net - Generate SQL Command object dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related