SqlConnection problem - MySQL

[Alan T]

I have installed ODBC driver for MySQL.
This is my Web.Config:

<add name="spotitConnectionString"
connectionString="DSN=MySQL_server;UID=spotit;description=Connection to
remote MySQL;server=access;database=spotit;port=3306;pwd=traffic54"
providerName="System.Data.Odbc"/>


I had an exception of the connection string of the DSN unrecognized.



This is my method:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Runtime.InteropServices;
using System.Web.UI.WebControls;
using System.Data.Odbc;
using System.Data;
using System.Data.SqlClient;

protected DataSet ExecuteDataSet(string query)
{
SqlConnection connection;
SqlCommand cmd;
SqlDataAdapter da;

connection = null;
cmd = null;
DataSet ds = new DataSet();
da = new SqlDataAdapter();

try
{
cmd = new SqlCommand(query);
cmd.CommandType = CommandType.Text;

da.SelectCommand = (SqlCommand)cmd;

connection = new SqlConnection(GetConnectionString());
cmd.Connection = connection;
connection.Open();

// fill the dataset
da.Fill(ds);
}
catch
{
throw;
}
finally
{
if (da != null)
da.Dispose();
if (cmd != null)
cmd.Dispose();
// implicitly calls close()
connection.Dispose();
}
return ds;
}


So I think I cannot us SqlConnection for MySQL?
What object should I use instead of SqlConnection?

 

[Man T]

Use either MySqlConnection or OdbcConnection.

I think the MySqlConnection is a download from the mysql site, but I'm not
sure as I don't use it myself.

Personally I'd advocate using OdbcConnection (and OdbcDataReader etc) as
you can then later change the type of database you are connected to
without any change other than the connection string. I use OdbcConnection
to connect to both MySQL and SQL Server (plus a few others).

Hi,

Could you give me some code that I can use to replace in my original coding?

 

[Simon Whale]

http://dev.mysql.com/downloads/connector/net/ - that is the download site

there is not much to change with this connector, but mainly sqlconnection to
mysqlconnection etc

 

[Alan T]

Hi,

I have installed both .NET connector and ODBC driver.
Since the connecting string format for MySQL is the same, how does ASP.NET
knows which one to use to connect?

 

[Cubaman]

Hi,

I have installed both .NET connector and ODBC driver.
Since the connecting string format for MySQL is the same, how does ASP.NET
knows which one to use to connect?

Hello:
I think your're confused with connectors. Odbc connections is a
diferent technology than .Net data providers.
Aspnet knows wich class to instantiate basically because you explicity
write it in code.

MySqlConnection class is diferent from OdbcConnection class.
Take a look at here:
http://www.geekpedia.com/tutorial139_Connecting-to-MySQL-with-Csharp-and-ODBC.html

Best regards.

 

[Leon Peon]

Hi,

use the MySQL - Connector/Net

http://dev.mysql.com/downloads/connector/


With something like that you can access the DB.
----------------------------------------------------------------

public void check_DB_Daten(string dbserver, string dbuser, string dbpass, string dbport, string dbname, string dbtabelle)
{
List<string> ausgabe_dbcheck = new List<string>();
string MyConString = "server=" + dbserver + ";" +
"uid=" + dbuser + ";" +
"database=" + dbname + ";" +//"database=devsql;" +
"port=" + dbport + ";" +
"pwd=" + dbpass + ";";
MySqlConnection connection = new MySqlConnection(MyConString);
try
{
MySqlDataReader Reader;
MySqlCommand mycommand = new MySqlCommand("SHOW DATABASES", connection);
connection.Open();
Reader = mycommand.ExecuteReader();
while (Reader.Read())
{
for (int i = 0; i < Reader.FieldCount; i++)
{
ausgabe_dbcheck.Add(Reader.GetValue(i).ToString());
}
}
Reader.Close();
connection.Close();
//---------------------------------------------------------------------
//List<string> myList f?ttert hier einen string per ForEach Funktion
string dbausgabe = "Die Verbindung ist OK!\n\nSHOW DATABASES:\n\n";
ausgabe_dbcheck.ForEach(delegate(String name)
{
dbausgabe += name + "\n";
});
//MessageBox.Show(dbausgabe, "Verbindung OK");
//---------------------------------------------------------------------
dbverbindung_ok = true;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message, "DB Fehler aufgetreten!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
dbverbindung_ok = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "DB Fehler aufgetreten!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
dbverbindung_ok = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}

----------------------------------------------------------

Leon-Peon



Alan T wrote:

SqlConnection problem - MySQL
19-Jul-10

I have installed ODBC driver for MySQL
This is my Web.Config

<add name="spotitConnectionString
connectionString="DSN=MySQL_server;UID=spotit;description=Connection t
remote MySQL;server=access;database=spotit;port=3306;pwd=traffic54
providerName="System.Data.Odbc"/

I had an exception of the connection string of the DSN unrecognized


This is my method

using System
using System.Collections.Generic
using System.Linq
using System.Web
using System.Configuration
using System.Runtime.InteropServices
using System.Web.UI.WebControls
using System.Data.Odbc
using System.Data
using System.Data.SqlClient

protected DataSet ExecuteDataSet(string query

SqlConnection connection
SqlCommand cmd
SqlDataAdapter da

connection = null
cmd = null
DataSet ds = new DataSet()
da = new SqlDataAdapter()

tr

cmd = new SqlCommand(query)
cmd.CommandType = CommandType.Text

da.SelectCommand = (SqlCommand)cmd

connection = new SqlConnection(GetConnectionString())
cmd.Connection = connection
connection.Open()

// fill the datase
da.Fill(ds)

catc

throw

finall

if (da != null
da.Dispose()
if (cmd != null
cmd.Dispose()
// implicitly calls close(
connection.Dispose()

return ds


So I think I cannot us SqlConnection for MySQL
What object should I use instead of SqlConnection?

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Six Free Visual Studio 2010 MSDN Memberships Giveaway
http://www.eggheadcafe.com/tutorial...al-studio-2010-msdn-memberships-giveaway.aspx