3 Replies - 10043 Views - Last Post: 20 January 2008 - 02:46 PM Rate Topic: -----

#1 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Connecting to MySQL from C# Web App

Post icon  Posted 20 January 2008 - 09:59 AM

I'm struggling to get my C# web app to "talk" to MySQL (in Visual Studio).
I set up the MySQL tables and inserted a test row to try and get them synching.

I've been told that it is possible to use OleDb connections to access MySQL.
All I want to do is get a DataGrid to display what is in the Suppliers table.

Here's my Code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page 
{
	protected void Page_Load(object sender, EventArgs e)
	{
		string strCon;
		strCon = "Provider=MySQLProv;" +
				 "Data Source=afp;" +
				 "Initial Catalog=Suppliers;";
		
		OleDbConnection conn = new OleDbConnection(strCon);
				
		conn.Open();
		DataGrid1.DataBind();
		conn.Close();
			
	}
}



The error it gives me is that MySQLProv is, "not registered on the local machine."

Any pointers would be really great!
Thanks!

Is This A Good Question/Topic? 1

Replies To: Connecting to MySQL from C# Web App

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Connecting to MySQL from C# Web App

Posted 20 January 2008 - 10:27 AM

Well to connection to a mySQL database follow the following procedure:
  • First, you need to install the mysql connector/net, it is located here

  • Next add reference to: MySql.Data

  • Next add "using MySql.Data.MySqlClient;"

  • Finally try some code (in your test application we just created) like this:


/// <summary>
	/// Method for connection to a mySQL database. You
	/// need to download the mySQL Connector located at
	/// http://dev.mysql.com/downloads/connector/net/1.0.html]
	/// 
	/// NOTE: I am a big supporter of having the connection
	/// stored in the web.config, not inline like this
	/// </summary>
	/// <param name="server"></param>
	private void connectoToMySql(string server)
	{
		//set your connection string. 
		//NOTE: I am a big supporter of having the connection
		//stored in the web.config, not inline like this
		string connString = "SERVER=" + server + ";" +
			"DATABASE=mydatabase;" +
			"UID=testuser;" +
			"PASSWORD=testpassword;";
		//create your mySQL connection
		MySqlConnection cnMySQL = new MySqlConnection(connString);
		//create your mySql command object
		MySqlCommand cmdMySQL = cnMySQL.CreateCommand();	
		//create your mySQL reeader object
		MySqlDataReader reader;
		//set the command text (query) of the
		//mySQL command object
		cmdMySQL.CommandText = "select * from mycustomers";
		//open the mySQL connection
		cnMySQL.Open();
		//execute the reader, thus retrieving the data
		reader = cmdMySQL.ExecuteReader();
		//while theres data keep reading
		while (reader.Read())
		{
			string thisrow = "";
			for (int i= 0;i<reader.FieldCount;i++)
					thisrow+=reader.GetValue(i).ToString() + ",";
			listBox1.Items.Add(thisrow);
		}
		cnMySQL.Close();
	}



Hope that helps :)

This post has been edited by PsychoCoder: 20 January 2008 - 10:37 AM

Was This Post Helpful? 0
  • +
  • -

#3 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Connecting to MySQL from C# Web App

Posted 20 January 2008 - 11:29 AM

I downloaded that connector, thanks...but:
How would I know what my SERVER name and UID is? I haven't set any of that up in the DB yet.
All I have is a password that I enter on the console command line once I click the "MySQL command line client" off my start bar.

Thanks, Psycho.
Was This Post Helpful? 0
  • +
  • -

#4 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Connecting to MySQL from C# Web App

Posted 20 January 2008 - 02:46 PM

Never mind guys.

I worked out that my UID was "root" SERVER "localhost" and fortunately I knew my password. (perseverence pays off I suppose...)

All working fine now.

thanks again! :)

This post has been edited by Footsie: 20 January 2008 - 02:47 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1