Using MySQL with .NET

[Denaes]

Can this even be done with a desktop app?

 

How easy? Is it worth it?

 

I'm pretty sure it could be used in an ASP.Net app.

 

Just curious about the possibilities beyond Jet/Access use.

[betrl8thanever]

Can this even be done with a desktop app?

 

How easy? Is it worth it?

 

I'm pretty sure it could be used in an ASP.Net app.

 

Just curious about the possibilities beyond Jet/Access use.

 

Yes you can us MySQL with .NET as a database. I'm currently using it as a data wherehouse at work. There are some pretty simple bugs with it, but other than that I think it's pretty good.

 

1. You need the right MDAC drivers for it. They are available at MySQL.com.

2. You can't use the native .NET data connection objects, but setting up the connection string isn't too difficult.

3. You need to add 2 references to your app. Microsoft.Data.Odbc and ADODB. I believe that ADODB is also available at MySQL. (It's been a while since I set it up.)

4. You need to specify the driver in the connection string. I think the current one version 3.51. When I get to work on Monday, I'll post a sample connection for you so you have it, if you're interested in using it.

[Denaes]

Oh, I'm very interested.

 

I'm using Access right now, but when I have to deal with multiple computers/users, I don't think I can get away with that.

 

Can you use MySQL on a desktop, or do you need to be running internet services?

 

I'm also starting to think that running a multiperson app as a single ASP.Net application on an intra/internet might be the easiest.

[betrl8thanever]

Oh, I'm very interested.

 

I'm using Access right now, but when I have to deal with multiple computers/users, I don't think I can get away with that.

 

Can you use MySQL on a desktop, or do you need to be running internet services?

 

I'm also starting to think that running a multiperson app as a single ASP.Net application on an intra/internet might be the easiest.

 

Use of MySQL will vary depeding on your needs. You don't need to have MySQL running on every desktop in your environment. You just need the IP address of the machine that its' running on, and the MySQL service running on the host machine. I know a lot of small businesses that don't want to invest thousands of dollars in MS SQL server use MySQL to run their web apps too. I use it for WinApps so that's what I'm most familiar with. I'm sure it would be pretty easy to translate for ASP.NET apps as well. I'll post on Monday with the code snippet.

[betrl8thanever]

Use of MySQL will vary depeding on your needs. You don't need to have MySQL running on every desktop in your environment. You just need the IP address of the machine that its' running on' date=' and the MySQL service running on the host machine. I know a lot of small businesses that don't want to invest thousands of dollars in MS SQL server use MySQL to run their web apps too. I use it for WinApps so that's what I'm most familiar with. I'm sure it would be pretty easy to translate for ASP.NET apps as well. I'll post on Monday with the code snippet.[/quote']

 

 

Here is the connection string code:

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       Try
           conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 DRIVER};" _
           & "SERVER=ipaddresshere;" _
           & "UID=root;PWD=;OPTION=3"
           'conn is also a global variable Dim conn as Connection = New Connection()

           conn.Open()
       Catch ex As Exception
       End Try
End Sub

 

Here is a query example:

 

Call wait_curs()
       '[Declare variables]

       Dim rs As Recordset = New Recordset()
       Dim da As New OleDb.OleDbDataAdapter()


       dt = New DataTable()  'Global Variable

       Try
           If conn.State = 0 Then
               conn.Open()
           End If

           If Me.rtbCommands.Text Is Nothing Then
               MessageBox.Show("You must first type the statement you wish to execute.", "SQL Statement", MessageBoxButtons.OK, MessageBoxIcon.Information)
               Return
           End If

           rs = conn.Execute(Me.rtbCommands.Text)

           da.Fill(dt, rs)

           Me.dgMain.DataSource = dt
           Me.dgMain.Refresh()

       Catch ex As Exception
           MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
           Me.rtbCommands.Focus()
           Return
       Finally

       End Try

       Call clear_cmdText()
       Call norm_curs()
       Me.rtbCommands.Focus()

 

These are your imports. You need to add references to Microsoft.Data.Odbc

and ADODB before you can import them

 

Imports System.IO
Imports ADODB
Imports Microsoft.Data.Odbc

 

[edit]I changed the PHP markup tags to VB markup tags. -Derek[/edit]

Edited March 16, 2004 by Derek Stone

[Denaes]

 

Imports System.IO
Imports ADODB
Imports Microsoft.Data.Odbc

 

Do you use the ADO Recordset because you prefer to, or is that required to use mySQL?

 

This looks like good stuff.

 

I'm looking into the mySQL as a low cost alternative to SQL Server for a future project which may be in VB.Net or possibly ASP.Net as it requires a "server" computer and many clients. I'm leaning more towards using ASP.Net over an intranet.

 

Unfortunately that's a future project, so I really can't get into it fully. I have this thread bookmarked and at least I know it's in fact possible and how to get it started. Thank You :)

 

[edit]I changed the PHP markup tags to VB markup tags. -Derek[/edit]

Edited March 16, 2004 by Derek Stone

[DieSkim]

Hallo,

 

I have been using both the MySql ODBC Driver and the MySqlDriverCS (on the mysql.com site, native .net driver writen in C#) for connecting to my MySql db.

 

I have found using the MySQLDriverCS works alot beter than the MySQL ODBC Driver, and this purely for speed. My tables contain more than 2 million records each and the the speeds is just alot beter connecting to the DB not via ODBC. Then memory usage using ODBC is alot more, and on Win98 system this caused some problems, and even on 2k/XP.

 

On the other hand the only way to get Crystal Reports to connect with the DB is with MySQL ODBC so for that you wil still need that. So my forms are filled using the MySQLDriverCS and reports use ODBC.

 

J.

[Denaes]

Hallo,

 

I have been using both the MySql ODBC Driver and the MySqlDriverCS (on the mysql.com site, native .net driver writen in C#) for connecting to my MySql db.

 

Thats something VERY good to know! :D

 

I'm not sure how many people will be up to 2 million records, but its always good practice to use the best tool for the job.

[betrl8thanever]

Do you use the ADO Recordset because you prefer to, or is that required to use mySQL?

 

This looks like good stuff.

 

I'm looking into the mySQL as a low cost alternative to SQL Server for a future project which may be in VB.Net or possibly ASP.Net as it requires a "server" computer and many clients. I'm leaning more towards using ASP.Net over an intranet.

 

Unfortunately that's a future project, so I really can't get into it fully. I have this thread bookmarked and at least I know it's in fact possible and how to get it started. Thank You :)

 

[edit]I changed the PHP markup tags to VB markup tags. -Derek[/edit]

 

 

I use the record set because that's the way I originally put the app together when trying to figure out the some of the little idiosyncracies of MySQL. I have just never converted it to a standard SQL data adapter configuration. I'm sure that will work, I've just never tried. Totally up to your whatever your preference is.

[mike55]

He guys,

 

Here's the connection string for mySQL if you are using VB.NET.

 

Dim MyConnection As OdbcConnection

Dim connString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _

"SERVER=localhost;" & "DATABASE=ivertecproject;" & "UID=root;" & _

"PASSWORD=;" & "OPTION=3"

MyConnection = New OdbcConnection(connString)

MyConnection.Open()

MyCommand.Connection = MyConnection

 

Mike55

[hungheykwun]

ysql is free. use bytefx as the wrapper if yr using vb.net

[Arch4ngel]

Just as a remark... you don't have to use & everytime to separate each item of the connection string.

 

when you make : "hi " & "World!"

You can only do : "hi World!"

no? or Am I becoming a monkey programmer ?