M$ no like MySQL

November 25, 2005 8:23 PM

I need to get MySQL working with Visual Studio 2005. I come from the PHP world, and while I know C, C# is a new language to me.

There's two issues. First, VS2k5 doesn't seem to allow me to add a MySQL (using Connector/Net 1.07) database as a project data source. Additionally, when attempting to use a ODBC connection, I get an error (10001 if I remember correctly) when trying to use the ODBC provider, even though the system data source is working correctly and tests out just peachy when I'm testing the data source from the OS level. This is keeping me from using data grid views, which are absolutely essential to this project. This is also barring me, AFAIK, from using the more advanced features of ADO.net ... which I don't understand, really.

(If I may take a moment to whine, ain't the whole principle of .net to make it easier on programmers? I've encounted about a dozen things in the language so far that I could accomplish in a single line of code in any other language I know ... Ruby, Python, Java, PHP ... yet are insanely difficult in C#. In fact, if it weren't for the visual editor's ability to spam out code at an alarming rate, I would probably have thrown my hands up in frustration at this point. Oh, and compared to most languages, the documentation SUCKS. In fact, it's on par with Perl for usability... which means it ain't.)

Even if I can't get the project data source thingie working, I can still accomplish most of the things I need to do by being able to access the database. It is possible for me to use the MySQL Connector/Net object to access the database manually, and I can successfully send queries and check for rows. I'm having problems accessing data, though.

I have an exceptionally cool polymorphic object toolset in PHP that creates data objects based upon the table definition. It allows me to query a single row, update a single row, and insert a row on the fly, without having to build a query each time I want to access data. I thought there was something similar in .Net, but I can't seem to figure out what it is or how to make it work with MySQL. If I can't do that, that's Ok, but is there a way to create differently named variables at runtime in C#? I don't think I've found one.

How can I get similar functionality with C#? Barring that, how can I get the base functionality that I'm used to, which is the ability to use hashes, aka associative arrays, with a result row?

P.S... I'm gonna punch the next smug bastard that says, "Use MSSQL instead. It works OK with that." Just sayin'.

posted by SpecialK to Computers & Internet (13 answers total)

Response by poster: Oh, and the reason that I can't switch to MsSQL is that this is a tool to do some important stuff with an already-existing web-based database, and we needed to be able to do system-level stuff that could only be done from a desktop app when using a windows computer (i.e., send a particular signal to a serial interface).
posted by SpecialK at 8:26 PM on November 25, 2005

Does this help?

Basically it goes like this..

1. Install MySQL ODBC driver
2. Setup MySQL DB as ODBC connection
3. Convert code shown on that page from VB.Net to C# (put ; at EOL, use brackets instead of "End..." statements.
posted by SirOmega at 9:42 PM on November 25, 2005

I think you're looking for typed datasets. Also, it looks like you're used to using various scripting languages. I'm not going to make a comment on what's better because I think that different tools have different benefits and drawbacks. I think though that there is a bit of a paradigm shift when using the .NET stuff. Give it some time, and I think you'll start to really appreciate it.
posted by blue_beetle at 11:06 PM on November 25, 2005

I assume you've read the MySQL Connector/Net installation, architecture, and usage documentation? In particular, the usage documentation is full of VB and C# example code.

Also, are you talking about PHP's DB_Table? I took a glance at that project and it looks like a port of Perl's Class::DBI. Since it's already been done in two languages, I'd be surprised if C# didn't already have something similar (not sure if typed datasets are it though).
posted by sbutler at 12:11 AM on November 26, 2005

I'm not quite sure I understand the exact nature of your problem. You can't connect to the database from inside of VS? This is the (basic) code:

private System.Data.Odbc.OdbcConnection OdbcCon;
private System.Data.Odbc.OdbcCommand OdbcCom;
private System.Data.Odbc.OdbcDataReader OdbcDR;
private string ConStr;

private void btnConnect_Click(object sender, EventArgs e) {

// Make sure you get the ODBC Driver String correct to the letter and version number!
ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + ";PORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + ";PWD=" + txtPassword.Text + ";OPTION=3";

OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);

try {

txtLog.AppendText("Openning connection...\r\n");
if (OdbcCon.State == ConnectionState.Closed) {
OdbcCon.Open();
txtLog.AppendText("Connection opened\r\n");
}
}

catch (System.Data.Odbc.OdbcException Ex) {
txtLog.AppendText(Ex.Message + "\r\n");
MessageBox.Show("Error accessing database.\r\n\r\nDetails:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
posted by Civil_Disobedient at 1:19 AM on November 26, 2005

Also, your ADO.NET provider might be wonky; you might want to try Byte FX's driver.
posted by Civil_Disobedient at 1:21 AM on November 26, 2005

Response by poster: I'm using connector/net. I can connect to the database and retreive results fine; it's when I try and hook up something like a data grid WinForms element that I get tripped up. Oh, and I'm using C#.

I think I'm mostly confused because I don't know the lingo yet. It's confusing to come from a scripting world (and yes, I consider Java a scripting language) to a machine-specific world. My biggest problem right now is that using connector/Net with Visual Studio 2k5, I can't figure out how to get the MySQL database that's showing up on localhost (or the one on another LAN machine, for that matter) to show up in the Server Explorer. If I could get it to show up there, I could link data into the data grid views, etc. automatically.

I eschewed ODBC because it's an extra step to install it when I'm setting up a client's computer. This software's going to be distributed to about 50k computers, and to be honest, I don't want to have to go through the extra step.

sbutler; No, I rolled my own that works better. ;) It's kind of proprietary, though, in that it hasn't been released to the wild yet ... but it's *cool*. Took our development time from hours for a particular data task to minutes.

ByteFX's driver is looking better and better. I've been impressed for about five, six years now with MySQL's databases, but I have not been impressed by their third-party language driver support.
posted by SpecialK at 2:56 AM on November 26, 2005

Response by poster: I can't figure out how to get the MySQL database that's showing up on localhost (or the one on another LAN machine, for that matter)

That IS ON localhost or another LAN machine. Sorry, I drowned m c# sorrows in very good beer this evening.
posted by SpecialK at 2:57 AM on November 26, 2005

Response by poster: *my
posted by SpecialK at 2:57 AM on November 26, 2005

you might be interested in linq if you're using c# and sql. i've not used it myself, but hear good things about it.
posted by andrew cooke at 3:40 AM on November 26, 2005

it's when I try and hook up something like a data grid WinForms element that I get tripped up

Aaah. Ok. I (think) I understand what you're trying to do. Well, first you have to create an OdbcCommand object for your SQL command. This then goes through the OdbcDataAdapter. The SELECT method must be executed by the Fill method in OdbcDataAdapter, and the results are then stored in your DataSet.

So, new OdbcCommand blahCommand
New OdbcDataAdapter(com) blahAdapter
New DataSet() blahDS
blahAdapter.Fill(blahDS, whatever)
New blahTable as DataTable = blahDS.Tables(whatever)

You can then access all the data in blahTable.Row(#)!colname form (or blahTable.Row(#)[colname], I forget the exact syntax).

Since DataSets are created in local ADO.NET memory, you have to remember to commit the changes when you're finished.

New OdbcCommandBuilder(blahAdapter)
blahAdapter.Update(blahDS, whatever)

There are a couple of limitations, however -- you can't use SELECT queries based on JOINs or GROUP BYs, for instance, because the OdbcCommandBuilder will balk.

and yes, I consider Java a scripting language

Funny that Microsoft would basically try and rip Sun off and model C# off a "scripting language." /java developer here
posted by Civil_Disobedient at 7:14 AM on November 26, 2005

Also, DataGrids are created very easily after you've 'Fill'ed your data...
DataGridx.DataSource = blahDS.Tables("column whatever")

Also also, make sure you're connecting to your database correctly (make absolutely certain the name string is the same, and make sure OPTIONS=3 is selected so you don't optimize column width or return rows).
posted by Civil_Disobedient at 7:22 AM on November 26, 2005

the ByteFX driver is simply the predecessor to Connector/Net. it would be very surprising if it worked any better.

your question would probaby be better asked on the MySQL .NET Forum or the MySQL .NET mailing list.

reggie (the author of the ByteFX driver and maintainer of the Connector/Net driver) hangs out in both places, as do lots of other people who know stuff on the subject.
posted by jimw at 9:19 AM on November 26, 2005