Visual Studio.NET is very geared to being paired up with Microsoft SQL Server, and natively supports each other. That would make sense, since both are Microsoft products. The downside of this is that SQL Server is not exactly cheap.
The good thing though is that Visual Studio.NET can also connect to MySQL databases, which has several advantages. Most web applications which are built around PHP use MySQL, which would then make easier interaction if your system has multiple platforms. And let us not forget that MySQL is free.
You can, of course, use the OleDB components to access a MySQL database, but there is a plugin for Visual Studio.NET which allow you to connect directly to the database, much like the SQL Server data access components work. You can download the MySQL Connector .NET 6.1 here.
Once you have installed this plugin, you need to add a reference to the MySQL.Data assembly, which you can then use in your code.
Using the methods is almost exactly the same as with doing SQL Server connections. First you create a connection using the connection string, and then create a command, which will contain the query to execute.
This command can then be executed and the result read with a reader object. Nothing particularly esoteric here if you have any experience writing database code in C#.
using System;
using MySql.Data.MySqlClient;
namespace Solarius
{
public class DBAccess
{
private static string _connString = Globals.connectionString;
public static int ExecuteNonQuery(string query)
{
int retVal;
MySqlConnection connection = new MySqlConnection(DBAccess._connString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = query;
connection.Open();
retVal = command.ExecuteNonQuery();
connection.Close();
return retVal;
}
public static void ExecuteReader(string query)
{
MySqlConnection connection = new MySqlConnection(DBAccess._connString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select * from mycustomers";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
//Loop through the reader
}
connection.Close();
}
}
}
