Jump to content





Recent Status Updates

  • Photo
      16 Apr
    Kadence

    If you're reading this, you're on my profile and I know you're on my profile because I'm probably viewing yours.

    Show comments (6)
  • Photo
      10 Apr
    Poe

    Finally (and hopefully) i'm getting a team together that knows a little of this and a little of that; and maybe all my open source projects that are half written can begin to be released. :)

View All Updates
Photo
* * * * * 1 votes

Connecting to a MySQL Database in C#

Database Connection Query Manipulate C# MySql in C# MySql Connection in C# mysql

  • Please log in to reply
No replies to this topic

#1 NexusNemesis

NexusNemesis

    CC Lurker

  • New Member
  • Pip
  • 5 posts
  • Programming Language:C, Java, C++, C#, (Visual) Basic, Visual Basic .NET, Assembly
  • Learning:PHP, JavaScript

Posted 21 August 2012 - 03:11 AM

Introduction
This tutorial will guide you on how to connect and query a MySql Database using SELECT, INSERT, UPDATE, and DELETE in C#.

Things Needed:
  • You should have installed MySQL and MySQL Connector/NET. You can download installers from http://dev.mysql.com/downloads
  • Microsoft Visual C# or Visual Studio.
To use the methods in the MySQL Connector/NET you should add a reference to it. Right click your project in the Solution Explorer and click Add Reference… In the .NET tab, chose MySql.Data and click ok.
Posted Image
Posted Image

MySql Connector/NET enables developers to develop database applications using MySql in .NET.

In your code, you should add the line using MySql.Data.MySqlClient; in order to be able to use methods for accessing MySql.

To Connect to a MySql Database:
String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
MySqlConnection con = null;
try
{
con = new MySqlConnection(str);
con.Open(); //open the connection
}
catch (MySqlException err) //We will capture and display any MySql errors that will occur
{
Console.WriteLine("Error: " + err.ToString());
}
finally
{
if (con != null)
{
	 con.Close(); //safely close the connection
}
} //remember to safely close the connection after accessing the database


To code some MySQL commands using Prepared Statements:
String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
MySqlConnection con = null;
try
{
con = new MySqlConnection(str);
con.Open(); //open the connection
//This is the mysql command that we will query into the db.
//It uses Prepared statements and the Placeholder is @name.
//Using prepared statements is faster and secure.
String cmdText = "INSERT INTO myTable(name) VALUES(@name)";
MySqlCommand cmd = new MySqlCommand(cmdText,con);
cmd.Prepare();
//we will bound a value to the placeholder
cmd.Parameters.AddWithValue("@name", "your value here");
cmd.ExecuteNonQuery(); //execute the mysql command
}
catch (MySqlException err)
{
Console.WriteLine("Error: " + err.ToString());
}
finally
{
if (con != null)
{
	 con.Close(); //close the connection
}
} //remember to close the connection after accessing the database

*Note: You can also try this kind of cmdText, “INSERT INTO myTable VALUES(@name,@age,@contact)”;
Then add the cmd.Parameters.AddWithValue for each placeholder @name,@age,@contact.
If you wan't a column in your table to be bypassed you may use NULL. e.g. “INSERT INTO myTable VALUES(NULL,@name,@age,@contact)”;

To Retrieve Data using MySqlDataReader:
String str = @"server=localhost;database=yourDBname;userid=root;password=yourDBpassword;";
MySqlConnection con = null;
//MySqlDataReader Object
MySqlDataReader reader = null;
try
{
con = new MySqlConnection(str);
con.Open(); //open the connection
//We will need to SELECT all or some columns in the table
//via this command
String cmdText = "SELECT * FROM myTable";
MySqlCommand cmd = new MySqlCommand(cmdText,con);
reader = cmd.ExecuteReader(); //execure the reader
/*The Read() method points to the next record It return false if there are no more records else returns true.*/
while (reader.Read())
{
/*reader.GetString(0) will get the value of the first column of the table myTable because we selected all columns using SELECT * (all); the first loop of the while loop is the first row; the next loop will be the second row and so on...*/
	 Console.WriteLine(reader.GetString(0));
}
}
catch (MySqlException err)
{
Console.WriteLine("Error: " + err.ToString());
}
finally
{
if (reader != null)
{
	 reader.Close();
}
if (con != null)
{
	 con.Close(); //close the connection
}
} //remember to close the connection after accessing the database


To Execute Some MySql Statements:
SELECT:
//This is the simple code of executing MySql Commands in C#
String cmdText = "SELECT id,name,contact FROM myTable"; //This line is the MySql Command
MySqlCommand cmd = new MySqlCommand(cmdText, con);
cmd.ExecuteNonQuery(); //Execute the command

UPDATE:
//example on how to use UPDATE
cmd = new MySqlCommand("UPDATE ab_data SET banned_from='" + from + "' , banned_until='" + until + "' WHERE name='" + name + "'", con);
cmd.ExecuteNonQuery();

DELETE:
//example on how to use DELETE
cmd = new MySqlCommand("DELETE FROM tbName WHERE colName = someValue",con);
cmd.ExecuteNonQuery();

*Note: before executing some Sql statements or accessing/manipulating the database, make sure that the connection to the MySql Database server is opened via con.Open(); Remember also to safely close it using con.Close();

Accessing the MySql database is made easier in .NET using MySQL Connector/NET. It should be installed in your system and should be an added reference in your project. Also, Do not forget to import or use it in your code using the statement in C#:
using MySql.Data.MySqlClient;

Attached is a sample project that you may try and learn from it.
Attached File  CSharpMySql.rar   23.21KB   2461 downloads

Questions and Comments are welcome!
Thanks!
  • 0





Also tagged with one or more of these keywords: Database, Connection, Query, Manipulate, C#, MySql in C#, MySql Connection in C#, mysql