balexander (05/12/2010)
Can you give more details as to how to implement your MySQLDataSourceItem? We have StrataFrame source if I need to compile it in.
Any advice to point us in the right direction would be GREATLY appreciated.Hi Barrett,
Sure, this is what you need to do:
1.- Copy the MySQLDataSourceItem class anywhere in your project (Must be VB, if not you will have to convert it.
2.- In the App.Main.vb, modify the SetDataSources and manually add the code to add a new data source item to the DataSources collection based on my class, here is some code to do that:
'-- Create the MySQL connection using the MySqlConnectionStringBuilder class from the connector
Dim MySQLconnStringBuilder As New MySql.Data.MySqlClient.MySqlConnectionStringBuilder
With MySQLconnStringBuilder
.Server = "Your MySQL Server IP"
.UserID = "MySQL User ID"
.Password = "MySQL Password"
.Database = "Database Name"
'-- I found these properties useful for my project.
.PersistSecurityInfo = True
.ConvertZeroDateTime = True
.DefaultCommandTimeout = 50
End With
Dim mMySQLConnectionString As String = MySQLconnStringBuilder.ToString
'-- Add a data source item using the custom MySqlDataSourceItem.vb class and very important..
' ... pass a DataSouceKey, in this case I used "MySQL"
DataLayer.DataSources.Add(New Business.Data.MySqlDataSourceItem("MySQL", mMySQLConnectionString))
3.-To create the Business Objects, I had to use the DDT, I created a DDT project and manually defined the database and tables. In the Table Properties,
use the database name as the Schema also and keep your names using the same case as in MySql since MySql is case sensitive, I have not tested otherwise, but just to be safe I keep all names with the same case as in MySql database. Then in the BOM I mapped the BO to the DDT project and setup any null properties as appropriate, then generate the partial class.
4.- In the BO don't forget to enter the
DataSourceKey value, so the BO will know which connection to use, in my sample I use
"MySQL".
5.- Design your form and Data Access methods in the same way as MS-SQL and you should be OK.
I have to mention that in my project I am only working with 2 MySQL tables and
have not tested all possible scenarios like relations, stored procedures, cascade deletes, concurrency, etc. just basic Add/Edit/Delete and of course some data access SQL statements with parameters, here is an example of that:
Imports MySql.Data.MySqlClient '''
''' Bring all records using the Customer Number
''' '''
'''
Public Sub FillByCustomerNumber(ByVal CustomerNumber As String)
Using cmd As New
MySqlCommand cmd.CommandText = String.Format("Select * From {0} where cust_number = @CustomerNumber", Me.TableName)
cmd.Parameters.AddWithValue("@CustomerNumber", CustomerNumber).MySqlDbType = MySqlDbType.VarChar
Me.FillDataTable(cmd)
End Using
End Sub