2 Replies - 666 Views - Last Post: 21 June 2016 - 06:13 AM Rate Topic: -----

#1 Sparky414   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 20-July 13

Unable to connect to MySQL database (DBMS agnostic)

Posted 21 June 2016 - 01:56 AM

Hello iam trying to write "DBMS agnostic" code but i fail to connect to a test database. Here is my code:

app.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
	<startup>
		<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
	</startup>
<appSettings>
	<add key="provider" value="System.Data.SqlClient" />
	<add key="connectionString" value="Data Source=localhost;Initial Catalog=mydb;User ID=root;Password=pass" />
</appSettings>
</configuration>


Program.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;

public static void Main(string[] args)
{
  string dataProvider = ConfigurationManager.AppSettings["provider"];
  string connectionString = ConfigurationManager.AppSettings["connectionString"];
  
  DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
		
			
			using (DbConnection connection = factory.CreateConnection())
			{
				if(connection == null)
				{
					Console.WriteLine("Error: connection");
					return;
				}
				
				connection.ConnectionString = connectionString;
				connection.Open();
				
				DbCommand command = factory.CreateCommand();
				if(command == null)
				{
					Console.WriteLine("Error: command");
					return;
				}
				
				command.Connection = connection;
				command.CommandText = "SELECT * FROM TestTable";
				
				using (DbDataReader DataReader = command.ExecuteReader())
				{
					
					while(DataReader.Read())
					{
						Console.WriteLine("User # {0} : {1}",DataReader["UserId"],DataReader["Name"]);
					}
					
				}
			}
								
			Console.Write("Press any key to continue . . . ");
			Console.ReadKey(true);
		}



I can not connect to the database. Any help would be greatly appreciated

Is This A Good Question/Topic? 0
  • +

Replies To: Unable to connect to MySQL database (DBMS agnostic)

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7493
  • View blog
  • Posts: 15,529
  • Joined: 16-October 07

Re: Unable to connect to MySQL database (DBMS agnostic)

Posted 21 June 2016 - 03:57 AM

This <add key="provider" value="System.Data.SqlClient" /> is MS SQL Server, not MySQL, which would generally be an issue if attempting to connect to MySQL.

Also, once you have a connection, ignore the factory. e.g.
DbConnection GetDbConnection() {
    string dataProvider = ConfigurationManager.AppSettings["provider"];
    string connectionString = ConfigurationManager.AppSettings["connectionString"];
    var factory = DbProviderFactories.GetFactory(dataProvider);
    return factory.CreateConnection();
}

DbCommand GetDbCommand() {
    return GetDbConnection().CreateCommand();
}


Was This Post Helpful? 0
  • +
  • -

#3 Sparky414   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 20-July 13

Re: Unable to connect to MySQL database (DBMS agnostic)

Posted 21 June 2016 - 06:13 AM

Thank you very much. The solution is:

Quote

<add key="provider" value="MySql.Data.MySqlClient" />


Prior to this you have to install and reference MySQL Connector/Net 6.8.x (at the time of writting this) to your project.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1