• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Intro Help to C# vs MySQL

Hello, i have been trying to find some information all over the internet about connecting programmaticly to my MySQL Database Server.  I want to see if someone could show me some examples

1) Create a database qry "CREATE DATABASE mynewdb;"
2) Get from the database "SELECT u_phone, u_address FROM mynewdb WHERE u_id = 'john';", and get that data.
3) How to get a specfic field from a select query performed above,
4) How to add, update and delete records from a table.

Thanks for your help in advanced.  

PS: I want to use ADO.NET but i don't want to use any 3rd party drivers except mysql odbc.
0
dawnyd01
Asked:
dawnyd01
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
TheAvengerCommented:
You can find info here, including requirements for software components, different objects, etc.:

http://dev.mysql.com/tech-resources/articles/dotnet/
0
 
WebSpecialsCommented:
Yes... but dont use ODBC
there is such a lovly .NET MySQL Component available.
And the best of all.. its open source and state: Mature

http://sourceforge.net/projects/mysqldrivercs/

I ve used it for several applications. Its stabel and its as easy to use as System.Data.Sql for MsSQL
And it has more preformance than ODBC

mfg WebSpecials
0
 
WebSpecialsCommented:
And about the usage:

MySQLConnection con=new MySQLConnection(connectionString);

MySQLCommand cmd=new MySQLCommand("SELECT * FROM table;",con);

con.Open();
MySQLDataReader dr=cmd.ExecuteReader();

while (dr.Read())
{
  Console.WriteLine(dr[0]);
  Console.WriteLine(dr.GetValue(1)); //just an example
}
dr.Close();
con.Close();
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WebSpecialsCommented:
and for Create Database

MySQLConnection con=new MySQLConnection(connectionString);

MySQLCommand cmd=new MySQLCommand("CREATE DATABASE mydatabase;",con);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

you can also work with transactions or with parameters.

Everything you want to know is documented in the Documentation

mfg WebSpecials
0
 
WebSpecialsCommented:
AND if you want to use ODBC because you want to work with several databases, than what about using interfaces IDbConnection and those, or use the Mimer Provider:

http://developer.mimer.se/mpm/

mfg WebSpecials
0
 
dawnyd01Author Commented:
Hi, when i use your example
      string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
        "SERVER=localhost;" +
        "DATABASE=test;" +
        "UID=;" +
        "PASSWORD=;" +
        "OPTION=3";
      MySQLConnection con=new MySQLConnection(connectionString);

      MySQLCommand cmd=new MySQLCommand("SELECT * FROM test;",con);

      con.Open();
      MySQLDataReader dr=cmd.ExecuteReader();

      while (dr.Read())
      {
        Console.WriteLine(dr[0]);
        Console.WriteLine(dr.GetValue(1)); //just an example
      }
      dr.Close();
      con.Close();
i get this message
 d:\Visual Studio Projects\ConsoleApplication12\Class1.cs(32): Cannot implicitly convert type 'System.Data.IDataReader' to 'MySQLDriverCS.MySQLDataReader'
0
 
YurichCommented:
I really don't see anything wrong with using ODBC and in this case you most likely wouldn't have your error either... just try to substitute all MySQL stuff in your code to ODBC:

      string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
        "SERVER=localhost;" +
        "DATABASE=test;" +
        "UID=;" +
        "PASSWORD=;" +
        "OPTION=3";
      OdbcConnection con=new OdbcConnection(connectionString);

      OdbcCommand cmd=new OdbcCommand("SELECT * FROM test;",con);

      con.Open();
      OdbcDataReader dr=cmd.ExecuteReader();

      while (dr.Read())
      {
        Console.WriteLine(dr[0]);
        Console.WriteLine(dr.GetValue(1)); //just an example
      }
      dr.Close();
      con.Close();

regards
0
 
dawnyd01Author Commented:
using System;
using System.Data;
using System.Data.Odbc;

namespace ConsoleApplication1
{
      /// <summary>
      /// Summary description for Class1.
      /// </summary>
      class Class1
      {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main(string[] args)
            {
                  string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                        "SERVER=localhost;" +
                        "DATABASE=test;" +
                        "UID=root;" +
                        "PASSWORD=wd006451;" +
                        "OPTION=3";
                  OdbcConnection con=new OdbcConnection(connectionString);

                  OdbcCommand cmd=new OdbcCommand("SELECT * FROM test;",con);

                  con.Open();
                  OdbcDataReader dr=cmd.ExecuteReader();

                  while (dr.Read())
                  {
                        Console.WriteLine(dr[0]);
                        Console.WriteLine(dr.GetValue(1)); //just an example
                  }
                  dr.Close();
                  con.Close();
            }
      }
}


This still does not work... Can someone show me more??
0
 
dawnyd01Author Commented:
ps, right after i open the connection, when executing OdbcDataReader dr = cmd.ExecuteReader() i get this message...
An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll

Additional information: System error.
0
 
YurichCommented:
first, place your code in the try-catch-finally block to handle this unhandled exception, do something like:

          static void Main(string[] args)
          {
               string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                    "SERVER=localhost;" +
                    "DATABASE=test;" +
                    "UID=root;" +
                    "PASSWORD=wd006451;" +
                    "OPTION=3";
               try
               {
                    OdbcConnection con=new OdbcConnection(connectionString);
       
                    con.Open();
                    OdbcCommand cmd=new OdbcCommand("SELECT * FROM test",con);

                     OdbcDataReader dr=cmd.ExecuteReader();

                      while (dr.Read())
                      {
                             Console.WriteLine(dr[0]);
                             Console.WriteLine(dr.GetValue(1)); //just an example
                       }
                  }
                  catch( Exception ex )
                  {
                           Console.WriteLine( ex.Message );
                   }
                   finally
                   {
                         if( dr != null )
                         {
                                dr.Close();
                                dr = null;
                          }
                          if( con.State = ConnectionState.Open )
                                 con.Close();
                     }
          }

2. !!! look in my code, first open connection, con.Open(), then create a command passing it a connection:
                    con.Open();
                    OdbcCommand cmd=new OdbcCommand("SELECT * FROM test;",con);

3. !!! remove ';' in your sql command after test !!

4. everything else seems to be fine.

regards,
yurich
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now