Wednesday, September 3, 2008

Simple MySQL Database test

Sample code fragment:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 MySql.Data.MySqlClient;

public partial class Test_Page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Simple MySQL Database test


string MyConString = "Server=yourMySQLServer;Port=yourPort#;Database=YourDB;Uid=yourID;Pwd=yourPWD;";
MySqlConnection MyConnection = new MySqlConnection(MyConString);

string queryString = "SELECT * FROM YOUR_TABLE";

MySqlCommand MyCommand = new MySqlCommand();
MyCommand.CommandText = queryString;
MyCommand.Connection = MyConnection;

MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
dataAdapter.SelectCommand = MyCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
try
{
dataAdapter.Fill(dataSet);

GridView1.DataSource = dataSet;
GridView1.DataBind();

}
catch (Exception ex)
{
Label_Error.Text = ex.ToString();
}
}
}The sample above is a simple code fragment I used to test the MySQL connection. Very similar to the one before, but is setup to be a parameterized command eventually where I can add conditional statements like so to the queryString:

"... WHERE UserName = ?UserName"

and add the parameter like so:

MySqlParameter dbParam_UserName = new MySqlParameter();
dbParam_UserName.ParameterName = "?UserName";
dbParam_UserName.Value = UserName;
dbParam_UserName.DbType = System.Data.DbType.String;
MyCommand.Parameters.Add(dbParam_UserName);
I use parameterized commands instead of just imbeding the user input string into the SQL query string directly to help prevent SQL injection... at least I read somewhere it is supposed to help prevent that, combined with replacing any single quotes ' to double single quotes '', though I probably have a bit to learn about all the tricks to look out for.

More..
http://forums.asp.net/t/999456.aspx?PageIndex=2

ASP.Net Feeds