4 Replies - 501 Views - Last Post: 17 October 2013 - 08:19 AM Rate Topic: -----

#1 unit998x  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-July 13

Creating MySQL update command without DataAdapter

Posted 15 October 2013 - 09:26 AM

Hi, I have a desktop application that connects to a ASP.NET Web Service in order to Get data from the MySql DB. A session ID and the SQL String are sent to the server and a serialized DataTable is returned. This setup is needed to prevent the Connection String from being decompiled.

The app makes heavy use of DataTables to manage data, and without directly connecting to the MySql Server, MySqlCommandBuilder seems unable to create .GetUpdateCommand.CommandText. Is there another method to get the SQL Command to make the changes in the DataTable on the MySQL Server.

Basically:
I need a SQL string to update the Server to contain values in a DataTable, without directly connecting to it.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Creating MySQL update command without DataAdapter

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8377
  • View blog
  • Posts: 31,144
  • Joined: 12-June 08

Re: Creating MySQL update command without DataAdapter

Posted 15 October 2013 - 09:28 AM

Why not just have the webservice accept an XML bundle of data, and on the sever it goes through the XML and does the appropriate updates?
Was This Post Helpful? 1
  • +
  • -

#3 unit998x  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-July 13

Re: Creating MySQL update command without DataAdapter

Posted 16 October 2013 - 08:43 AM

Thanks modi123, I never thought the solution would be that simple...
Was This Post Helpful? 0
  • +
  • -

#4 unit998x  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-July 13

Re: Creating MySQL update command without DataAdapter

Posted 17 October 2013 - 08:05 AM

View Postunit998x, on 16 October 2013 - 08:43 AM, said:

Thanks modi123, I never thought the solution would be that simple...

It wasn't...

Here is the code I am using on the server

    
    ''' <summary>
    ''' Upload the datatable to the server, replacing any old data.
    ''' </summary>
    ''' <param name="sessionID">The users current session</param>
    ''' <param name="data">The serialized XML datatable</param>
    ''' <param name="SQL">SQL String originally used to obtain the data.</param>
    ''' <param name="forceUpdate">Skip checking for duplicates.</param>
    ''' <returns>"1" if success, error message otherwise.</returns>
    ''' <remarks></remarks>
    <WebMethod()> _
    Public Function RecieveTable(sessionID As String, data As DataTable, SQL As String, forceUpdate As Boolean, ParamArray SQLParam() As String) As String
        '// Check the user is logged in
        If HasSessionExpired(sessionID) Then
            Return "The session is invalid or expired, please logout and try again."
        End If

        Try
            '// Open the connection
            Dim mySqlConn As New MySqlConnection(ConfigurationManager.ConnectionStrings("OSM").ConnectionString)
            '// Create dataadapters
            Dim da As New MySqlDataAdapter(SQL, mySqlConn)
            With da
                Dim i As Integer = 0
                For Each param As String In SQLParam
                    .SelectCommand.Parameters.AddWithValue("@" & i.ToString, SQLParam(i)) '// Add all parameters to the query
                    i += 1
                Next
            End With
            Dim cb As New MySqlCommandBuilder(da)
            Dim tempData As New DataTable
            '// Get the current data
            da.Fill(tempData)
            '// replace the local datatable with the uploaded one
            tempData.Clear()
            tempData.Merge(data)
            '// make the changes
            da.Update(tempData)
        Catch ex As Exception
            '// return the error message
            Return ex.Message
        End Try
        Return "1"
    End Function


This kind of works, this error is returned: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I believe this occurs because when I replace the old table, the links between rows in the datatable and the rows in the database are broken. How can I solve this error?

Interestingly enough, the update still seems to work, however I would rather not suppress the error.

Thanks
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8377
  • View blog
  • Posts: 31,144
  • Joined: 12-June 08

Re: Creating MySQL update command without DataAdapter

Posted 17 October 2013 - 08:19 AM

Why are you munging around with data tables and adapters, and all that jazz?

Why not have a sql command, a connection, and go?

Example:
        Dim foo As DataTable '-- your table coming in
         
         Try
			'-- set up the command query, object, connection.
            sql = "UPDATE <table name> " & _
                " SET <col1> = @col1 " & _
                " <col2> = @col2 " & _
                " <col3> = @col3 " & _
                " WHERE <id col> = @id "
            '-- 2.0  SQL statement.
            con = New MySqlConnection("<connection string>") '-- AppSettings.Get("db")
            con.Open()
            cmd = New MySqlCommand(sql, con)

			'-- since the command statement won't change (but the values do) just loop through and add them.
            For Each row As DataRow In foo.Rows
                Try
					cmd.Parameters.AddWithValue("@col1", row.Item(<val1>)) '-- example of adding the parameters
					cmd.Parameters.AddWithValue("@col2", row.Item(<val2>))
					cmd.Parameters.AddWithValue("@col3", row.Item(<val3>))
					cmd.Parameters.AddWithValue("@id", row.Item(<valID>))

					cmd.ExecuteNonQuery() '-- send them
					cmd.Parameters.Clear '-- clear them 
				Catch ex As Exception
					'-- log error and proceed to the next row
				End Try
			Next
 
        Catch ex As Exception
            '-- exception mostly for the connection string issue.
        Finally
            If con IsNot Nothing Then con.Dispose()
        End Try


Easy peasy.. you set up the basic objects, crank a loop through the table, update, and everyone wins.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1