How to use MySqlHelper.UpdateDataSet

Question

I've searched around the web looking for samples on how to use MySqlHelper.UpdateDataSet but all I've found is:

Public Shared Sub UpdateDataSet( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ByVal ds As DataSet, _
   ByVal tablename As String _
) 

I'll be grateful if someone will give me:

• an example of commandText because I didn't understand what kind of command I need to give;
• an explanation of tablename because I need to know if is the name of a table of the DB or of the DataSet or both (with the same name);
• a vb.net code example (to start testing).

I tryed to use the command this way:

Private Sub Btn_Mod_Dataset_Click(sender As Object, e As EventArgs) Handles Btn_Mod_Dataset.Click
    Dim SqlStr$ = "SELECT * FROM MyTest.Users"
    Using DS_Test As DataSet = DB_Functions.mQuery(SqlStr)
        With DS_Test
            .Tables(0).TableName = "Users"
            Dim User$ = .Tables(0).Rows(0)("UserName").ToString
            MsgBox(User)
            .Tables(0).Rows(0)("User") = "Upd_Test"
            User = .Tables(0).Rows(0)("UserName").ToString
            MsgBox(User)
        End With
        Dim DB_Name = "MyTest"
        Dim connectionString$ = "datasource=localhost;username=" + UserDB _ 
            + ";password=" + Password + ";database=" + DB_Name
        MySqlHelper.UpdateDataSet(connectionString, _
            "Update MyTest.Users Set UserName = 'Test_Ok' WHERE UserName = 'Steve'", _
            DS_Test, "Users")
    End Using
End Sub

This gives me

System.NullReferenceException' in System.Data.dll

EDIT (to explain my code):
a) DB_Functions is a sepate class where I've stored some function to use on a MySql DataBase. mQuery is a function who extract query result into a dataset;
b) 'User' is a field Name : I've changed it to 'UserName' but same result;

d) The code between With and End With is just a test to see what happens;

NOTE that the code gives error but my DB is updated as in the commandText String. I don't understand what happens

Answer 1

This might get you part of the way.

First get rid of DB_Functions. MySQLHelper has a method to create the DataSet for you; in general, db Ops are so query-specific that there is very little that is generic and reusable. The exception to this is building the ConnectionString: MySQL has gobs of cool options you can enable/disable via the connection string. But for that you just need the standard MySqlConnectionStringBuilder.

Build a DataSet:

' form/class level vars
Private dsSample As DataSet
Private MySqlConnStr As String = "..."

...
Dim SQL = "SELECT Id, FirstName, Middle, LastName FROM Employee"

Using dbcon As New MySqlConnection(MySQLConnStr)
    dsSample = MySqlHelper.ExecuteDataset(dbcon, SQL)
    dsSample.Tables(0).TableName = "Emps"
End Using

There does not appear to be a way to specify a tablename when you build it, so that is a separate step.

Update a Record

To update a single row, you want ExecuteNonQuery; this will also allow you to use Parameters:

Dim uSQL = "UPDATE Employee SET Middle = @p1 WHERE Id = @p2"
Using dbcon As New MySqlConnection(MySQLConnStr)
    Dim params(1) As MySqlParameter

    params(0) = New MySqlParameter("@p1", MySqlDbType.String)
    params(0).Value = "Q"

    params(1) = New MySqlParameter("@p2", MySqlDbType.Int32)
    params(1).Value = 4583

    dbcon.Open()
    Dim rows = MySqlHelper.ExecuteNonQuery(dbcon, uSQL, params)
End Using

Again, this is not really any simpler than using a fully configured DataAdapter, which would be simply:

dsSample.Tables("Emps").Rows(1).Item("Middle") = "X"
daSample.Update(dsSample.Tables("Emps"))

I am not exactly sure what value the UpdateDataSet method adds. I think it is the "helper" counterpart for the above, but since it doesn't provide for Parameters, I don't have much use for it. The docs for it are sketchy.

The commandtext would appear to be the SQL for a single row. Note that the DataAdapter.Update method above would add any new rows added, delete the deleted ones and update values for any row with changed values - potentially dozens or even hundreds of db Ops with one line of code.

Answer 2

My funtion gets string query and returns datatable. So u can set dataset.tables .

Public Function mysql(ByVal str_query As String) As DataTable
   Dim adptr As New MySqlDataAdapter
   Dim filltab As New DataTable
   Try
       Using cnn As New MySqlConnection("server=" & mysql_server & _
           ";user=" & mysql_user & ";password=" & mysql_password & _
           ";database=" & mysql_database & ";Allow User Variables=True")
           Using cmd As New MySqlCommand(str_query, cnn)
               cnn.Open()
               adptr = New MySqlDataAdapter(cmd)
               adptr.Fill(filltab)
               cnn.Close()
           End Using
       End Using
   Catch ex As Exception
           'you can log mysql errors into a file here log(ex.ToString)
   End Try
   Return filltab
End Function