How to execute SQL Command for a Dataadapter

Question

Hi All,

Is there a way to execute SQL command for a Dataadapter rather than regular approach.

I i have parameters to pass to the SQLCommand. In he below model DAtaadapter at declaration itself we need to pass the SQL as string, and Conn. But i wan to send SQL Command to the adapter to execute instead command as string

Previously i have used to fill dataadapter as below

dbSource = "Database= DataBase;"

            conn.ConnectionString = dbProvider & dbPassword & dbSource

            If conn.State = ConnectionState.Open Then

                conn.Close()

            End If

            conn.Open()

            da = New MySqlDataAdapter(SQL, conn)

            da.Fill(ds, table)

            conn.Close()

is there a way like to open connection, assign SQL Statement, and execute command, then fill dataset. LIke below

            dbSource = "Database= DataBase;"

            conn.ConnectionString = dbProvider & dbPassword & dbSource

            SQLcommand = New MySql.Data.MySqlClient.MySqlCommand
            SQLcommand.Connection = conn
            SQLcommand.CommandType = CommandType.Text
            SQLcommand.CommandText = SQL

            If conn.State = ConnectionState.Open Then

                conn.Close()

            End If

            conn.Open()

            RowsEffected = SQLcommand.ExecuteNonQuery()
            SQLcommand.Parameters.Clear()

            conn.Close()

---

PBL (Visual Studio 2010 Professional, Win 7 64 bit Ultimate)

Answer 1

Hi PBLNRAO,

Thanks for your post.

Yes, you can do this, here is my code and it works well (you need to change the SQL query and connection string by yourself):

 Dim con As New SqlConnection("Data Source=.;Initial Catalog=testdb;Integrated Security=True") 'change your connection string
        Dim command As SqlCommand = New SqlCommand
        command.CommandText = "your SQL Query "
        command.Connection = con
        con.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        'add your code here
        con.Close()

Hope this helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thx Mark,

I hope this will work....

I am trying on other alternates also....

will post if i am successful...

---

PBL (Visual Studio 2010 Professional, Win 7 64 bit Ultimate)