Start Free Trial

asked on

MySQL.data with .ExecuteNonQuery()

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

ASKER

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?

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

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.

ASKER

Now I got 11,515625 with MySQL.data

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

ASKER

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.

Is that the amount of time it took to insert the records?

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.

ASKER

yepp that's what i'm doing now.

ASKER

and ops.. it's not 800 it's 8000.. :)

ASKER

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

ASKER

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()

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?

ASKER

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")

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:

ASKER

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;

ASKER

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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?

ASKER

Can I read the data from the csv file faster then I do now?

ASKER

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

ASKER

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)

ASKER

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"}

ASKER

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"