?
Solved

MySQL.data with .ExecuteNonQuery()

Posted on 2007-07-20
24
Medium Priority
?
469 Views
Last Modified: 2013-11-26
Hi.
I use VB.Net and VS 2005 with MySQL 5 with the MySQL.data dll

I'm trying to make an function for inserting data to MySQL with the command function in MySQL.data

I have 3 functions
1: Open connection
2: Close connecion
3: Insert data using the command functions

I have problem too write the insert data function, any tips how I can do that ? :)

I dont know if the open/close functions are 100% ok....?


Here is the functions

Private m_MyConnectionMySQLOpen As MySqlClient.MySqlConnection
Private m_strMyConString As String = &  _
                                "Persist Security Info=False;" & _
                                "DATABASE=test;" & _
                                "PORT=3307;" & _
                                "SERVER=127.0.0.1;" & _
                                "Connect Timeout=30;" & _
                                "user id=test;" & _
                                "pwd=test"

       Public Sub OpenConnection()
            Try
                m_MyConnectionMySQLOpen = New MySqlClient.MySqlConnection
                m_MyConnectionMySQLOpen.ConnectionString = m_strMyConString
                m_MyConnectionMySQLOpen.Open()
            Catch OdbcException As OdbcException
                Throw OdbcException
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        Public Sub CloseConnection()
            Try
                m_MyConnectionMySQLOpen.Close()
                Throw MyException
            End Try
        End Sub

       Public Sub OpenExcecuteSql(ByVal SqlQuery As String)
            Try
           ' here I need some help, I can't get this function to work :(
          ' I want to use this type of commands, but I don't know how to write the code
            'CommandText = SqlQuery
            '.ExecuteNonQuery()
            Catch MyException As Exception
                Throw MyException
            End Try
        End Sub
0
Comment
Question by:AWestEng
  • 17
  • 6
24 Comments
 
LVL 1

Author Comment

by:AWestEng
ID: 19534036
This is what I have now..

        Public Sub OpenExcecuteSql(ByVal SqlQuery As String)
            Try
                m_Command.Connection = m_MySQLConnection
                m_Command.CommandText = SqlQuery
                m_Command.ExecuteNonQuery()
            Catch MyException As Exception
                Throw MyException
            End Try
        End Sub

The stange thing is that my old code with odbc was faster..

the "same" connection functions an this command fucntion

       Public Sub OpenExcecuteSql(ByVal SqlQuery As String)
            Try
                _MyCommandOdbcOpen.CommandText = SqlQuery
                _MyCommandOdbcOpen.ExecuteNonQuery()
            Catch OdbcException As OdbcException
                Throw OdbcException
            Catch MyException As Exception
                Throw MyException
            End Try
        End Sub

I inserted 8000 rows

with Odbc:  3,703125 s
and with MySQL.data : 4,53125

Any tips why?
0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 19534043
Dear AWestEng,
Perhaps you can code in this way?

Code
===============================
Protected ConnectionString As String
Protected ITransaction As IDbTransaction

Protected Function GetConnection() As MySqlConnection
            Dim objConnection As New MySqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) 'get value from web.config
            objConnection.Open()
            Return objConnection
        End Function

Protected Sub ExcecuteSql(ByVal strSQL As String)
            Dim objConnection As MySqlConnection = GetConnection()
            Dim objTrans As MySqlTransaction = objConnection.BeginTransaction()
            Dim objCommand As New MySqlCommand(strSQL, objConnection, objTrans)
            Try
                objCommand.ExecuteNonQuery()
                objTrans.Commit()
            Catch e As Exception
                objTrans.Rollback()
            Finally
                objConnection.Close()
                objCommand.Dispose()
            End Try
        End Sub



0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19534195
I modified your sub just a bit.  With this rendition, it's not necessary to have different subs for each task,
we can include them in 1:

 Public Sub ExcecuteSql(ByVal SqlQuery As String)

        Dim strMyConString As String = _
                           "Persist Security Info=False;" & _
                           "DATABASE=test;" & _
                           "PORT=3307;" & _
                           "SERVER=127.0.0.1;" & _
                           "Connect Timeout=30;" & _
                           "user id=test;" & _
                           "pwd=test;"


        'The "Using" block will automatically dispose of the connection when we're finished
        Using MyConnectionMySQLOpen As New MySqlClient.MySqlConnection(m_strMyConString)

            Try
                MyConnectionMySQLOpen.Open()

                Dim cmd As New MySqlClient.MySqlCommand()
                With cmd
                    .Connection = MyConnectionMySQLOpen
                    .CommandType = CommandType.Text
                    .CommandText = SqlQuery
                    .ExecuteNonQuery()
                End With

                MyConnectionMySQLOpen.Close()

            Catch MyException As SqlException
                MsgBox(MyException.Message)

            Finally
                If MyConnectionMySQLOpen.State = ConnectionState.Open Then
                    MyConnectionMySQLOpen.Close()

                End If

            End Try

        End Using

    End Sub


Let me know if you have any problems with that.  I don't have MySQL, so I use SQLClient instead.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:AWestEng
ID: 19534201

Now I got 11,515625 with MySQL.data

Can't understand that odbc is faster when i ust MySQL own driver
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19534224
The problem is that i use this in a loop.
I insert 800 rows, that's why i have sperated the connection and the ExecuteNonQuery.

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19534234
Is that the amount of time it took to insert the records?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19534346
AwestEng:

Are you performing an insert every loop?  For example, if you insert 800 records, do you call the
OpenExcecuteSql method 800 times?  If that's the case, I have a different suggestion.



0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535462
yepp that's what i'm doing now.
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535471
and ops.. it's not 800 it's 8000.. :)


0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535476
8000 rows:
with Odbc:  3,703125 s
and with MySQL.data : 4,53125
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535566
I do this..

OpenConnection()

For Each MyRow As DataRow In DataTableSQL.Rows
   ST.Append("INSERT ........)
   ST.Append(ImportDate)
    ST.Append("','")
   ST.Append(FileDate)
   ST.Append("', test,'")
    ST.Append(MyRow("K1_NR"))
    ST.Append("','")
      ST.Append(MyRow("K2_NR"))
    ST.Append("','")
      ST.Append(MyRow("K3_NR"))
    ST.Append("','")
  ................
.............
...........
 Query = ST.ToString
    ST.Length = 0
OpenExcecuteSql(Query)
Next

CloseConnection()
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19535708
Well, there's 2 ways you can do it that can make your life easier.  Both ways can insert all records at
one time.

    1.  You can do a Bulk Insert using SqlBulkCopy (if it is available in the MySqlClient namespace).
    2.  You can use a DataAdapter and do a batch update.

The approach you use depends on the constraints of the table you are loading.  If there are no
constraints, then you may want to go with the Bulk insert (if it's available).  However, if there are
constraints, or if you don't have the SqlBulkCopy available, then you will need to do a batch update
using the DataAdapter.  What kind of constraints do you have on your SQL table?

Also,
    What is the name of the SQL table you are inserting it to, and what are the names of the columns?
    Is the name of the table in your DataSet the same as your SQL table?
    Are the names of the columns in the table in the DataSet the same as your SQL table?



0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535742
2.  You can use a DataAdapter and do a batch update

How do I do this? that sounds like it would be great here

I need to have this, there are some duplicates sometimes..

 ST.Append("ON DUPLICATE KEY UPDATE DuplicateInsert = 1")
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 19535806
Ok.  Can you give me the following information:

    SQL Table Name:
    SQL Table Column Names and DataTypes:

    DataSet Table Name:
    DataSet Table Column Names and DataTypes:
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535898

this is what i insert on every row but here are some data that i create by myself
(ImportDate, FileDate, FileType)

      Dim strColumns As String = _
            "ImportDate, FileDate, FileType, KVITTONR, DAT_START, TID_START, DAT_SLUT, TID_SLUT," & _
            "FORAR_ID, MEDLEMSNR, TAXINR, ARBPASSNR, TAXAMETERBELOPP, DEBITERAS," & _
            "PALAGG, EXTRA, EGENAVGIFT, DRICKS, MOMSTYP, RESTID, KILOMETER, KREDITTYP"


This iis whay I read from the CSV file, all columns exept (ImportDate, FileDate, FileType)

KVITTONR, DAT_START, TID_START, DAT_SLUT, TID_SLUT,
FORAR_ID, MEDLEMSNR, TAXINR, ARBPASSNR, TAXAMETERBELOPP, DEBITERAS,
PALAGG, EXTRA, EGENAVGIFT, DRICKS, MOMSTYP, RESTID, KILOMETER, KREDITTYP

Here is the complete table from MySQL
CREATE TABLE `resa` (
  `ImportDate` date NOT NULL default '0000-00-00',
  `FileDate` date NOT NULL default '0000-00-00',
  `FileType` varchar(15) NOT NULL default '',
  `ManuallyChanged` tinyint(1) unsigned NOT NULL default '0',
  `ManuallyChangedDate` datetime default '0000-00-00 00:00:00',
  `DuplicateInsert` tinyint(1) unsigned NOT NULL default '0',
  `KVITTONR` varchar(10) NOT NULL default '',
  `DAT_START` date NOT NULL default '0000-00-00',
  `TID_START` varchar(4) NOT NULL default '0',
  `DAT_SLUT` date NOT NULL default '0000-00-00',
  `TID_SLUT` varchar(4) NOT NULL default '0',
  `FORAR_ID` varchar(6) NOT NULL default '',
  `MEDLEMSNR` varchar(6) NOT NULL default '',
  `TAXINR` varchar(6) NOT NULL default '',
  `ARBPASSNR` varchar(10) NOT NULL default '',
  `TAXAMETERBELOPP` decimal(10,2) NOT NULL default '0.00',
  `DEBITERAS` decimal(10,2) NOT NULL default '0.00',
  `PALAGG` decimal(10,2) NOT NULL default '0.00',
  `EXTRA` decimal(10,2) NOT NULL default '0.00',
  `EGENAVGIFT` decimal(10,2) NOT NULL default '0.00',
  `DRICKS` decimal(10,2) NOT NULL default '0.00',
  `MOMSTYP` tinyint(1) unsigned NOT NULL default '0',
  `RESTID` decimal(10,2) NOT NULL default '0.00',
  `KILOMETER` decimal(18,2) NOT NULL default '0.00',
  `KREDITTYP` char(3) NOT NULL default '',
  PRIMARY KEY  (`KVITTONR`,`DAT_START`,`TAXINR`,`FORAR_ID`,`ARBPASSNR`),
  KEY `FileDate` (`FileDate`),
  KEY `KREDITTYP` (`KREDITTYP`),
  KEY `MOMSTYP` (`MOMSTYP`),
  KEY `TAXAMETERBELOPP` (`TAXAMETERBELOPP`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535965
so I got a CSV file with 8000-20000 rows with data and all of them is going to be inserted to the table, but here are some duplicates that i need to handel or ignore

Sp I do like this

Private sub InsertAllData()
   Dim dt as new datatable
   dt = ReadCSVFile(strPath)
   InsertData(dt)
End Sub


And it the insertdata that I have the for each loop
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19535977
Here is the excat file read

 Public Function ReadCSVFile(ByVal FileName As String, _
               ByVal strDataSource As String, _
               ByVal delimiter As String, _
               ByVal IncludeFieldName As Boolean, _
               Optional ByVal StartAtLine As Integer = 0, _
               Optional ByVal StopAtLine As Integer = 0, _
               Optional ByVal ReturnNothing As Boolean = False) _
               As DataTable

        Dim intColCntr As Integer 'Counter for number of columns in the text file.
        Dim intRowCntr As Integer 'Counter row to be added.

        'stopatline is 0 then read all rows
        If StopAtLine = 0 Then
            StopAtLine = Int32.MaxValue
        End If

        Dim arrSQLCols() As String
        Dim myDataTable As New DataTable
        Dim myDataCol As New DataColumn
        Dim myDataRow As DataRow
        Dim strLine As String
        Dim sr As New StreamReader(strDataSource)
        Dim loopctr As Integer = 0

        Dim fi As New FileInfo(strDataSource)
        Dim filelength As Long = fi.Length
        Dim bytesread As Long = 0

        Try
            While sr.Peek <> -1
                strLine = sr.ReadLine
                bytesread += strLine.Length
                ' ProgressBar1.Value = (bytesread / filelength) * 100

                'Search for qualifier character.
                If strLine.Substring(0, 1) = """" Or strLine.Substring(0, 1) = "'" Then
                    strLine = Strings.Replace(strLine, strLine.Substring(0, 1), "", 1, -1)
                End If

                Dim arrColName() As String = strLine.Split(delimiter)
                If loopctr = 0 Then
                    'Check to include first row as field name.
                    If IncludeFieldName = True Then
                        'Add columns to the table.
                        For intColCntr = 0 To arrColName.Length - 1
                            myDataCol = New DataColumn(arrColName(intColCntr))
                            myDataCol.AutoIncrement = True
                            myDataCol.DataType = Type.GetType("System.String")
                            myDataTable.Columns.Add(myDataCol)
                            arrSQLCols = arrColName
                        Next
                    Else
                        For intColCntr = 0 To arrColName.Length - 1
                            myDataCol = New DataColumn("Field" & " " & intColCntr)
                            myDataCol.AutoIncrement = True
                            myDataCol.DataType = Type.GetType("System.String")
                            myDataTable.Columns.Add(myDataCol)
                        Next
                    End If
                Else
                    If loopctr >= StartAtLine AndAlso loopctr <= StopAtLine Then
                        'Add rows to the table.
                        Dim arrRow() As String = strLine.Split(delimiter)
                        myDataRow = myDataTable.NewRow
                        For intRowCntr = 0 To arrColName.Length - 1
                            myDataRow(intRowCntr) = arrRow(intRowCntr)
                        Next
                        myDataTable.Rows.Add(myDataRow)
                    End If
                End If
                loopctr += 1
            End While
            sr.Close()

            If ReturnNothing Then
                If myDataTable.Rows.Count = 0 Then
                    Return Nothing
                Else
                    Return myDataTable
                End If
            Else
                Return myDataTable
            End If

            'Error handling
              ......
        Finally
            myDataTable = Nothing
            myDataCol = Nothing
            System.GC.Collect()
        End Try
    End Function
0
 
LVL 27

Accepted Solution

by:
VBRocks earned 2000 total points
ID: 19536038
Ok, it looks like it will take me a while to work on that.  But for the mean time, I put together a little
example for you.  Perhaps you can look it over and figure out how you can apply it to your situation.

    Private Sub BatchUpdate()

        'Create DataColumns for our table
        Dim colText As New DataColumn("Text")
        Dim colForGUID As New DataColumn("forGUID")
        With colForGUID
            .AutoIncrement = True
            .AutoIncrementSeed = 1
            .AutoIncrementStep = 1
        End With

        'Create a new DataTable and add the DataColumns created above
        Dim table As New DataTable()
        table.Columns.AddRange(New DataColumn() {colForGUID, colText})


        'Add 50 rows to the DataTable
        Dim row As DataRow
        For i As Int16 = 1 To 50
            row = table.NewRow()
            row.Item("Text") = "data " & i.ToString()

            table.Rows.Add(row)

        Next


        'Create a SQLConnection, passing in the proper connection string
        Dim cn As New SqlClient.SqlConnection(AppConfig.ConnectionString)

        'Create a SQLDataAdapter
        Dim da As New SqlClient.SqlDataAdapter()

        'Create and configure a SQLCommand for the DataAdapter
        da.InsertCommand = New SqlClient.SqlCommand()
        With da.InsertCommand

            'Set the connection
            .Connection = cn

            'Must be set to none
            .UpdatedRowSource = UpdateRowSource.None

            'Set the CommandType to Text
            .CommandType = CommandType.Text

            'Set the SQL Text, including parameters
            .CommandText = _
                "INSERT INTO table2 (forGUID, Text) VALUES (@forGUID, @text);"

            'Add the parameters.  
            '    Make sure to map the source colum in our table to the parameter
            With .Parameters
                .Add("@forGUID", SqlDbType.Int, 0, "forGUID")
                .Add("@text", SqlDbType.VarChar, 50, "Text")

            End With

        End With

        'Setting the UpdateBatchSize to 0
        '    will submit all the rows in a single batch.
        da.UpdateBatchSize = 0

        'Perform the insert to the SQL table.
        da.Update(table)


        MsgBox("Done")

    End Sub

0
 
LVL 1

Author Comment

by:AWestEng
ID: 19536070
So if I understand you correct..

1: Make a datatable with all the column nmaes and the same types
2: Fill the dattable from the csv file
3: use the With da.InsertCommand to insert all data.

This is must faster because all data is inserted at the same time if I understand your code correct?
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19536081
Can I read the data from the csv file faster then I do now?

 
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19536324
Here is some code  to read the csv file into the datatable..

Your example to read the csv file. :)

But I got some problems with the types..DateTime I found but not Date


Public Function ReadCSV(ByVal FilePath As String, ByVal TableName As String, ByVal ColumnNames() As String, ByVal ColumnTypes() As String) As DataTable
        ' Create the datatable
        Dim myDataTable As New DataTable(TableName)

        'Read the CSV file
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FilePath)
            MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            MyReader.SetDelimiters(";")

            Dim currentRow As String()
            Dim myDataCol As DataColumn
            Dim myDataRow As DataRow

            'Create the columns
            For i As Integer = 1 To ColumnNames.Length - 1
                myDataCol = New DataColumn(ColumnNames(i))
                With myDataCol
                    .DataType = System.Type.GetType(ColumnTypes(i))
                    .AutoIncrement = True
                    .AutoIncrementSeed = 1
                    .AutoIncrementStep = 1
                End With
                myDataTable.Columns.Add(myDataCol)
            Next

            'Loop through all of the fields in the file.
            'If any lines are corrupt, report an error and continue parsing.
            While Not MyReader.EndOfData
                Try
                    currentRow = MyReader.ReadFields()

                    For i As Integer = 1 To ColumnNames.Length - 1
                        myDataRow = myDataTable.NewRow()

                        'No need to add a value to ColumnID, since it is an AutoIncrementing column
                        myDataRow.Item(ColumnNames(i)) = currentRow(i)

                        myDataTable.Rows.Add(myDataRow)
                    Next

                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & _
                    " is invalid.  Skipping")
                End Try
            End While
        End Using

        Return myDataTable
    End Function
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19536347
     Dim columTypes As String() = New String() { _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx"}

But I don't know how I should do this

VARCHAR(15)
DATE
TINYINT(1)
CHAR(3)
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19539795
Now I'm going to test the code..

But I can't figureout how to create the correct typ in the datatable

I need to pass the srray to my function but the types
VARCHAR(15)
DATE
TINYINT(1)
CHAR(3)

How do I create these types in the datatable?

I have ssen System.In16 an so on..

But Can I add otertypes that are nor from system, for expample can I add types from MySQL.data,?

 Dim columTypes As String() = New String() { _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", "xxxx.xxxx", _
            "xxxx.xxxx"}

0
 
LVL 1

Author Comment

by:AWestEng
ID: 19539918
what I can see is that I can't use the   MySqlDbType the datatable wants system.xxxx

Bu I can't get t to work..

I will accept you anwer now and open a new question

Title "Pass CSV file into datatable"
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question