CREATE TABLE `test`.`t1` ( `ca` INT NOT NULL AUTO_INCREMENT, `cb` VARCHAR(45) NULL, PRIMARY KEY (`ca`));
Cadenas de conexión:
http://www.connectionstrings.com/mysql-connector-net-mysqlconnection/
Drivers para poder leer Bases de datos Access 2010.
Bajar el fichero AccessDatabaseEngine.exe (32 bits).
Con MySQl:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace conexionbbdd
{
class Program
{
static void Main(string[] args)
{
string cadena = "Server=127.0.0.1;Database=test;Uid=test;Pwd=;";
MySqlConnection conexion = new MySqlConnection(cadena);
try
{
Console.WriteLine("Abriendo Base de Datos ...");
conexion.Open();
//myAccessConn.Open();
}
catch (MySqlException e)
{
Console.Write("Fallo abriendo Base de Datos: ");
Console.WriteLine(e.ToString());
}
MySqlCommand cmd = new MySqlCommand("Select * from t1;",conexion);
try
{
Console.WriteLine("Leyendo tabla t1 ... ");
MySqlDataReader datos = cmd.ExecuteReader();
Console.WriteLine(datos.FieldCount.ToString());
while (datos.Read())
{
Console.WriteLine("Id :{0} - c1 : {1} c2 :{2} - c3 : {3} ",
datos[0], datos[1], datos[2], datos[3]);
}
}
catch (MySqlException e)
{
Console.Write("Fallo abriendo tabla t1: ");
Console.WriteLine(e.ToString());
}
conexion.Close();
Console.WriteLine("Fin programa");
Console.ReadKey();
}
}
}
Con Microsoft Access y MySQL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
//Objetos para guardar resultados orientado a NO conexión
DataSet myDataSetAccess = new DataSet();
DataSet myDataSetMySQL = new DataSet();
//Cadenas de conexión
string strMySQLConn = "Server=127.0.0.1;Database=test;Uid=test;Pwd=;";
string strAccessConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\inetpub\\wwwroot\\WF\\tablahash\\test.mdb";
// Creamos objetos de conexión
OleDbConnection myAccessConn = new OleDbConnection(strAccessConn);
MySqlConnection myMySQLConn = new MySqlConnection(strMySQLConn);
// Intentamos abrir ambas bases de datos
try
{
Console.WriteLine("Abriendo Base de Datos Access y MySQL...");
myAccessConn.Open();
myMySQLConn.Open();
}
catch (Exception e)
{
Console.Write("Fallo abriendo Base de Datos: ");
Console.WriteLine(e.ToString());
}
//Creamos comandos y rellenamos DataSet
Console.WriteLine("\nRellenamos DataSet Ejecutando Select contra Access");
OleDbCommand myAccessCommand = new OleDbCommand("SELECT * FROM t1", myAccessConn);
//Traemos los datos a un Dataset
OleDbDataAdapter myDataAccessAdapter = new OleDbDataAdapter(myAccessCommand);
myDataAccessAdapter.Fill(myDataSetAccess, "DataSetDeAccess");
Console.WriteLine("\nRellenamos DataSet Ejecutando Select contra MySQL");
MySqlCommand myMySQLCommand = new MySqlCommand("Select * from t1;", myMySQLConn);
//Traemos los datos a un dataset
MySqlDataAdapter myDataMySQLAdapter = new MySqlDataAdapter(myMySQLCommand);
myDataMySQLAdapter.Fill(myDataSetMySQL, "DataSetDeMySQL");
// Los dataset se almacenan en memoria y puden ser serializados son
// más lentos los accesos que con los DataReader
// A partir del dataset podemos obtener más información
DataTableCollection dtAccess = myDataSetAccess.Tables;
DataTableCollection dtMySQL = myDataSetMySQL.Tables;
foreach (DataTable dt in dtAccess)
{
Console.WriteLine("\nTablas encontradas Access {0}", dt.TableName);
}
foreach (DataTable dt in dtMySQL)
{
Console.WriteLine("\nTablas encontradas MySQL {0}", dt.TableName);
}
Console.WriteLine("\nEn Access");
Console.WriteLine("{0} tablas en data set", myDataSetAccess.Tables.Count);
Console.WriteLine("{0} tablas en data table collection", dtAccess.Count);
Console.WriteLine("{0} filas en tabla t1", myDataSetAccess.Tables["DataSetDeAccess"].Rows.Count);
Console.WriteLine("{0} columnas en tabla t1", myDataSetAccess.Tables["DataSetDeAccess"].Columns.Count);
Console.WriteLine("\nEn MySQL");
Console.WriteLine("{0} tablas en data set", myDataSetMySQL.Tables.Count);
Console.WriteLine("{0} tablas en data table collection", dtMySQL.Count);
Console.WriteLine("{0} filas en tabla t1", myDataSetMySQL.Tables["DataSetDeMySQL"].Rows.Count);
Console.WriteLine("{0} columnas en tabla t1", myDataSetMySQL.Tables["DataSetDeMySQL"].Columns.Count);
DataColumnCollection dccAccess = myDataSetAccess.Tables["DataSetDeAccess"].Columns;
DataColumnCollection dccMySQL = myDataSetMySQL.Tables["DataSetDeMySQL"].Columns;
int i = 0;
Console.WriteLine("\nEn Access");
foreach (DataColumn dc in dccAccess)
{
Console.WriteLine("La columna [{0}] es {1}, tipo {2}", i++, dc.ColumnName, dc.DataType);
}
i = 0;
Console.WriteLine("\nEn MySQL");
foreach (DataColumn dc in dccMySQL)
{
Console.WriteLine("La columna [{0}] es {1}, tipo {2}", i++, dc.ColumnName, dc.DataType);
}
//Insertar en base de datos a partir del DataAdapter
try
{
Console.WriteLine("\nHaciendo Inserts hola");
myDataAccessAdapter.InsertCommand = new OleDbCommand("Insert into t1 (c1,c2) values('hola',33);", myAccessConn);
myDataMySQLAdapter.InsertCommand = new MySqlCommand("Insert into t1 (cb) values('hola hola')", myMySQLConn);
myDataAccessAdapter.InsertCommand.ExecuteNonQuery();
myDataMySQLAdapter.InsertCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write("Fallo haciendo Insert ");
Console.WriteLine(e.ToString());
}
//Actualizar en base de datos a partir del DataAdapter
try
{
Console.WriteLine("\nHaciendo Updates hola");
myDataAccessAdapter.UpdateCommand = new OleDbCommand("Update t1 set c1='hola hola caracola', c2=3333 where c1='hola';", myAccessConn);
myDataMySQLAdapter.UpdateCommand =
new MySqlCommand("Update t1 set c1='hola hola caracola' where c1=@lacadena", myMySQLConn);
MySqlParameter lacadena;
lacadena = new MySqlParameter("@lacadena", "hola");
myDataAccessAdapter.UpdateCommand.ExecuteNonQuery();
myDataMySQLAdapter.UpdateCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write("Fallo haciendo Update ");
Console.WriteLine(e.ToString());
}
//Creamos Objetos para lecturas de las tablas método CONECTADO
OleDbDataReader myDatAccessReader = myAccessCommand.ExecuteReader();
MySqlDataReader myDataMySQLReader = myMySQLCommand.ExecuteReader();
// Recorremos la tabla a partir del Dataset
Console.WriteLine("\nRecorriendo tabla Access");
DataRowCollection dra = myDataSetAccess.Tables["DataSetDeAccess"].Rows;
foreach (DataRow dr in dra)
{
Console.WriteLine("Id [{0}] c1 {1} c2 {2} c3 {3}", dr[0], dr[1], dr[2], dr[3]);
}
// Recorremos de forma secuencial
Console.WriteLine("\nRecorriendo tabla MySQL");
while (myDataMySQLReader.Read())
{
Console.WriteLine("Dato 1 :{0} - Dato 2 : {1} ", myDataMySQLReader[0], myDataMySQLReader[1]);
}
// Borrado
//myDataAccessAdapter.DeleteCommand = new OleDbCommand("delete from t1 where ....", myAccessConn);
Console.WriteLine("\nCerrando Base de Datos");
myAccessConn.Close();
myMySQLConn.Close();
Console.ReadKey();
}
}
}