C#でMySQLを操作するサンプルコード
サンプルコード：
using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Web.UI.WebControls;
using System.Web;
public class DBQuery
{
public DBQuery()
{
//
// TODO: ここではコンストラクタロジックを追加
//
}
public static void SetDataView(GridView dg)
{
if (GFDB.SysInfo.GridViewPage == 0)
{
GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings[“GridViewPage"].ToString());
}
if (GFDB.SysInfo.GridViewPage > 0)
{
dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//ページあたり15記録
}
dg.ShowHeaderWhenEmpty = false;
dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);
}
public static void SetDataView(GridView dg, bool ARowDataBound, bool AShowHeaderWhenEmpty)
{
if (GFDB.SysInfo.GridViewPage == 0)
{
GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings[“GridViewPage"].ToString());
}
if (GFDB.SysInfo.GridViewPage > 0)
{
dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//ページあたり15記録
}
dg.ShowHeaderWhenEmpty = AShowHeaderWhenEmpty;
if (ARowDataBound)
{
dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);
}
}
public static void SmartGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add(“onmouseover", “currentcolor = this.style.backgroundColor;this.style.backgroundColor=’#cccccc'");
e.Row.Attributes.Add(“onmouseout", “this.style.backgroundColor = currentcolor");
e.Row.Attributes[“style"] = “Cursor:hand;height:28px;";
}
}
#region EXECUTE DATASET
// DBConnectionPool
/// <summary>
/// This method returns the data in dataset form.
/// </summary>
/// <param name="cmdType">Command type</param>
/// <param name="cmdText">Command text</param>
/// <returns>Data in the form of Dataset.</returns>
/// <summary>
public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText)
{
DataSet dsData = new DataSet();
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = cmdType;
myCommand.CommandText = CommandText; //"set names utf8;" +
myDataAdapter.SelectCommand = myCommand;
myDataAdapter.Fill(dsData);
return dsData;
}
catch (MySql.Data.MySqlClient.MySqlException Ex)
{
ExceptLog.SetExcept(Ex);
return null;
//throw new Exception(Ex.Message);
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
/// <summary>
/// This method returns the data in dataset form.
/// </summary>
/// <param name="cmdType">Command type</param>
/// <param name="cmdText">Command text</param>
/// <param name="mysqlParams">MySqlParameters</param>
/// <returns>Data in the form of Dataset.</returns>
/// public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
{
DataSet dsData = new DataSet();
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = cmdType;
myCommand.CommandText = CommandText;
for (int i = 0; i < mysqlParams.Length; i++)
{
myCommand.Parameters.Add(mysqlParams[i]);
}
myDataAdapter.SelectCommand = myCommand;
myDataAdapter.Fill(dsData);
return dsData;
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
return null;
//throw Ex;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static bool ExecuteSQLParams(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)
{
DataSet dsData = new DataSet();
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = cmdType;
myCommand.CommandText = CommandText;
for (int i = 0; i < mysqlParams.Length; i++)
{
myCommand.Parameters.Add(mysqlParams[i]);
}
myDataAdapter.SelectCommand = myCommand;
myCommand.ExecuteNonQuery();
//myDataAdapter.Fill(dsData);
return true;
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
return false;
//throw Ex;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static MySqlDataReader ExecuteDataReader(string CommandText)
{
MySqlCommand myCommand = new MySqlCommand();
MySqlDataReader myReader;
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = CommandText;
myReader = myCommand.ExecuteReader();
return myReader;
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
return null;
//throw Ex;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static DataTable ExecuteDataTable(string CommandText)
{
MySqlCommand myCommand = new MySqlCommand();
MySqlDataReader myReader;
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = CommandText;//"set names utf8;" +
myReader = myCommand.ExecuteReader();
DataTable Table = new DataTable(“myDataTable");
Table.Load(myReader);
return Table;
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
return null;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static DataTable ExecuteSqlReDataTable(string CommandText)
{
MySqlCommand myCommand = new MySqlCommand();
MySqlDataReader myReader;
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = CommandText;//"set names utf8;" +
myReader = myCommand.ExecuteReader();
DataTable Table = new DataTable(“myDataTable");
Table.Load(myReader);
return Table;
}
catch (Exception Ex)
{
throw Ex;
return null;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static string GetParamValueMemo(string AParamName)
{
string strSQL = “select * from `sys_parameter_memo` where name ='" + AParamName + “'";
DataTable dt = DBQuery.ExecuteDataTable(strSQL);
if (dt.Rows.Count == 0)
{
return “";
}
string str = dt.Rows[0][“pvalue"].ToString().Replace(“\r\n", “\n");
string[] slist = str.Split(new char[] {'\n’});
str = “";
for (int i = 0; i < slist.Length – 1;i++ )
{
if (slist[i].Trim().IndexOf(“#") != 0)
{
str = str + slist[i];
}
}
return str;
}
/// <summary>
/// SQLを実行
/// </summary>
/// <param name="cmdType"></param>
/// <param name="CommandText"></param>
/// <returns>true/false</returns>
public static bool ExecuteSql(CommandType cmdType, string CommandText,ref string ReError)
{
if (“".Equals(CommandText))
{
ReError = “SQLは空です";
return false;
}
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = cmdType;
myCommand.CommandText = CommandText;
myCommand.ExecuteNonQuery();
return true;
}
catch (Exception Ex)
{
ReError = Ex.Message.ToString().Replace(“'","`");
ExceptLog.SetExcept(Ex);
return false;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static string GetParam(string PName)
{
try
{
DataTable dt = ExecuteDataTable(“select Name,Pvalue from `sys_parameter_list` where Name='" + PName + “';");
if ((dt == null) || (dt.Rows.Count == 0))
{
return “";
}
return dt.Rows[0][“Pvalue"].ToString();
}
catch (Exception Ex)
{
return “";
}
}
public static bool ExecuteSql( string CommandText, ref string ReError)
{
if (“".Equals(CommandText))
{
ReError = “SQLは空です！";
return false;
}
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = CommandText;
myCommand.ExecuteNonQuery();
return true;
}
catch (Exception Ex)
{
ReError = Ex.Message.ToString().Replace(“'", “`");
ExceptLog.SetExcept(Ex);
return false;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
public static bool ExecuteSql(string CommandText)
{
if (“".Equals(CommandText))
{
return false;
}
MySqlCommand myCommand = new MySqlCommand();
try
{
try
{
myCommand.Connection = DBConnectionPool.getPool().getConnection();
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = CommandText;
myCommand.ExecuteNonQuery();
return true;
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
return false;
}
}
finally
{
DBConnectionPool.getPool().closeConnection(myCommand.Connection);
}
}
#endregion
public static void ExportExcel(string ExecSQL, string FileName, HttpResponse resp)
{
try
{
string tmp = “";
int fc = 0;
string sFileName = FileName.ToLower();
if (sFileName == “")
{
sFileName = System.DateTime.Today.ToString(“yyyyMMdd");
}
if (sFileName.IndexOf(“.xls")<0){
sFileName = sFileName + “.xls";
}
resp.ContentEncoding = System.Text.Encoding.GetEncoding(“gb2312");
resp.AppendHeader(“Content-Disposition", “attachment;filename=" + HttpUtility.UrlEncode(sFileName).ToString());
//一般的なフォーマット：
//1） テキスト：vnd.ms-excel.numberformat:@
//2） 日付：vnd.ms-excel.numberformat:yyyy/mm/dd
//3） 数字：vnd.ms-excel.numberformat:#,##0.00
//4） 通貨：vnd.ms-excel.numberformat:￥#,##0.00
//5） パーセンテージ：vnd.ms-excel.numberformat: #0.00%
resp.ContentType = “vnd.ms-excel.numberformat:@";
//テーブルオブジェクトと行
//DataTable dt = DBQuery.ExecuteDataSet(CommandType.Text, ExecSQL).Tables[0];
DataTable dt = DBQuery.ExecuteDataTable(ExecSQL);
fc = dt.Columns.Count;
for (int i = 0; i < fc; i++)
{
tmp = tmp + “" + dt.Columns[i].Caption.Replace(“ ", “") + “\t";
}
resp.Write(tmp + “\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
tmp = “";
for (int j = 0; j < fc; j++)
{
tmp = tmp + “" + dt.Rows[i].ItemArray[j] + “\t";
}
resp.Write(tmp + “\n");
}
//無用リソースを強制解除
GC.Collect();
//HTTPヘッダにデータバッファを書き込む
resp.End();
resp.Flush();
}
catch (Exception Ex)
{
ExceptLog.SetExcept(Ex);
throw Ex;
}
}
//接続の方法を取得
//string strsql = “select * from Chapter";
//SqlDataAdapter sqldap = new SqlDataAdapter(strsql, DBConnectionPool.getPool().getConnection());
//DataSet set = new DataSet();
//sqldap.Fill(set);
//GridView1.DataSource = set.Tables[0].DefaultView;
//GridView1.DataBind();
}