Update DataSet

2004-06-17
2004-07-27
  • Vionnet Thierry

    Vionnet Thierry - 2004-06-17

    Hello !

    Ok... so... I use .Net with flash. There is a technology in flash called DeltaPacket. The deltapacket is a xml fragment who contains insert-update-delete informations about a dataGrid component.
    When I send deltaPacket to .Net, I have several methods to parse and insert-update-delete the rows concerned.
    My update method is based on the changements directly in a dataSet, and updated with a specific UpdateDataSet command, who use commandBuilder object.
    I have seen there is no commandBuilder implemented for now, so is there any solution to adapt tools for made the same think ??

    this is the code of my original function with mySQL provider (ByteFX) :

            public DataSet UpdateDataSet(string sSelectSQL, DataSet dsUpdate, string sTableName)
                {
                MySqlConnection myCn = null;
                MySqlDataAdapter myDa = null ;
                MySqlCommandBuilder build = null ;

                try
                    {
                    // return an instancied MySqlConnection object
                    myCn = Connect ();
                    myCn.Open ();

                    myDa = new MySqlDataAdapter(sSelectSQL, myCn);

                    build = new MySqlCommandBuilder(myDa) ;

                    myDa.Update(dsUpdate, sTableName) ;

                    if (dsUpdate.HasChanges())
                        dsUpdate.AcceptChanges() ;

                    return dsUpdate ;
                    }
                catch (MySqlException e1)
                    {
                    Log.WriteLog ("UpdateDataSet (String) :\n" + e1.Message + "\n" + e1.StackTrace.ToString ());
                    return null;
                    }
                finally
                    {
                    // libration des ressources
                    Disconnect (myCn);
                    build.Dispose() ;
                    myDa.Dispose() ;
                    }
            }

    Many thanks if anyone has a solution. if it's possible, I don't want to change my deltaPacket methods...

    Seeya

     
    • Alexander Gavrilov

      Unfortunately, SQLite doesn't support the metainformation about the query. This information, returned by IDataReader.GetSchemaTable, is crucial to implement CommandBuilder. I'm working with authors of SQLite to resolve this issue but don't hold your breath :-(

       
    • felix

      felix - 2004-06-21

      I have implemented a function to read the schema information, but have not had time to write it suitable for a GetSchemaTable return. The code simply parses the information returned from a call to "SELECT tbl_name, sql FROM sqlite_master WHERE type = 'table'", which essentially is the create table statement of SQLite. I can post this code (although in VB.NET) if anyone is interested.

       
      • Alexander Gavrilov

        It would be interesting to look into the code.

         
        • felix

          felix - 2004-07-27

          Sorry for the tardy reply... here is the hack I use to extract (some) of the metadata. It basically parses the create table statement. Note that I am not familiar with all the variations of the SQL create table syntax (as implemented by SQLite). Lifting the proper parser code from that project would probably do a better job.

          The code adds the schema information into a StringDictionary I have setup for the purpose.

                Private Sub ReadSchemaInformation()
                   Dim cmd As SQLiteCommand = m_db.CreateCommand
                   Dim ds As SQLiteDataReader
                   Dim sSql As String
                   Dim sTable As String
                   Dim sField As String
                   Dim sSize As String
                   Dim nPos As Integer
                   Dim nFirstPos As Integer
                   Dim nNextPos As Integer

                   Try
                      cmd.CommandText = "SELECT tbl_name, sql FROM sqlite_master WHERE type = 'table'"
                      ds = CType(cmd.ExecuteReader(CommandBehavior.SingleResult), SQLiteDataReader)
                      Do While ds.Read()
                         'Obtain schema info from SQLite master, eg table name and SQL 'Create Table (....)' string
                         sTable = ds.GetString(0)
                         sSql = ds.GetString(1)

                         'Start parsing the string
                         nPos = sSql.IndexOf("(")
                         If nPos >= 0 Then
                            nNextPos = sSql.IndexOf("primary key(", nPos + 1)
                            If nNextPos >= 0 Then
                               sSql = sSql.Substring(nPos, nNextPos - nPos - 1)
                            Else
                               sSql = sSql.Substring(nPos, sSql.Length - nPos - 1)
                            End If

                            'Create an array of fields and field types
                            Dim asFields() As String = sSql.Split(",".ToCharArray())

                            'Loop through the fields and datatypes
                            Dim i As Integer
                            For i = 0 To asFields.Length - 1
                               If (asFields(i).Length > 0) Then
                                  'Get the field from the array and trim
                                  nFirstPos = asFields(i).IndexOf(" ", 1)
                                  sField = asFields(i).Substring(1, nFirstPos).Trim
                                  nNextPos = asFields(i).IndexOf(" ", nFirstPos + 1)
                                  'Get the data type
                                  If (nNextPos > 0) Then
                                     sSize = asFields(i).Substring(nFirstPos, nNextPos - nFirstPos).Trim
                                  Else
                                     sSize = asFields(i).Substring(nFirstPos).Trim
                                  End If
                                  nFirstPos = sSize.IndexOf("char(", 0)
                                  If (nFirstPos > 0) Then
                                     'String type, return the expression in the (), eg. varchar(50) = 50
                                     sSize = sSize.Substring(nFirstPos + 5, sSize.Length - 5 - nFirstPos - 1)
                                  Else
                                     'Other type
                                     Select Case sSize
                                        Case "int" : sSize = "4"
                                        Case "integer" : sSize = "4"
                                        Case "bigint" : sSize = "8"
                                        Case "smallint" : sSize = "2"
                                        Case "decimal" : sSize = "4"
                                        Case "numeric" : sSize = "4"
                                        Case "real" : sSize = "4"
                                        Case "float" : sSize = "8"
                                        Case "money" : sSize = "8"
                                        Case "smallmoney" : sSize = "4"
                                        Case "datetime" : sSize = "8"
                                        Case "timestamp" : sSize = "8"
                                        Case "smalldatetime" : sSize = "4"
                                        Case "timestamp" : sSize = "8"
                                        Case "timestamp" : sSize = "8"
                                     End Select
                                  End If
                                  Me.m_schemas.Add(sTable & "." & sField, sSize)
                               End If
                            Next i
                         End If
                      Loop
                   Catch ex As SQLiteException
                      Me.DisplayDatabaseErrors(ex)
                      Throw New ApplicationException("Cannot obtain table information")
                   Finally
                      If Not ds Is Nothing Then
                         ds.Close()
                         ds.Dispose()
                      End If
                      cmd.Dispose()
                   End Try
                End Sub

           
    • Alexander Gavrilov

      SQLiteCommandBuilder is implemented in the version 0.18. Try it!

       

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks