Find solution
How to make a MySql query faster?

M$ no like MySQL

  • 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'.

  • Answer:

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

SpecialK at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Are you running the MySQL ODBC interface on the server? If you don't have one already, http://www.unixodbc.org/ works well.

b1tr0t

Does http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1288&lngWId=10 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.

SirOmega

I think you're looking for http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconadonetdatasetcreationinvisualstudionet.asp. 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.

blue_beetle

I assume you've read the MySQL Connector/Net http://dev.mysql.com/doc/refman/5.0/en/connector-net-installation.html, http://dev.mysql.com/doc/refman/5.0/en/connector-net-architecture.html, and http://dev.mysql.com/doc/refman/5.0/en/connector-net-using.html 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 http://wiki.class-dbi.com/wiki/Main_Page. 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).

sbutler

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); } }

Civil_Disobedient

Also, your ADO.NET provider might be wonky; you might want to try http://sourceforge.net/projects/mysqlnet/.

Civil_Disobedient

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.

SpecialK

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.

SpecialK

*my

SpecialK

Load more

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.