C#実装操作MySqlデータ層クラスMysqlHelperインスタンス
8853 ワード
この例では、C#実装動作MySqlデータ層クラスMysqlHelperについて説明する.皆さんの参考にしてください.具体的には以下の通りです.
本稿で述べたことが皆さんのC#プログラム設計に役立つことを願っています.
using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace VideoWorks.ITmanage.DAL
{
public abstract class MySqlHelper
{
//
public static string Conn = "Database='device_manage';Data Source='localhost';User Id='root';Password='123456';charset='utf8';pooling=true;Allow Zero Datetime=True";
///
/// sql ( )
///
///
/// ( , , )
/// sql
///
///
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
///
/// sql ( )
///
///
/// ( , , )
/// sql
///
///
public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// SQL sql ( )
///
///
/// :
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///
/// ( , , )
/// sql
///
///
public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// sql
///
///
/// :
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///
/// ( , , )
/// sql
///
///
public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
// MySqlCommand
MySqlCommand cmd = new MySqlCommand();
// MySqlConnection
MySqlConnection conn = new MySqlConnection(connectionString);
// try/catch sql / , , ,
// commandBehaviour.CloseConnection
try
{
// PrepareCommand , MySqlCommand
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
// MySqlCommand ExecuteReader
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//
cmd.Parameters.Clear();
return reader;
}
catch
{
// ,
conn.Close();
throw;
}
}
///
/// DataSet
///
///
/// ( , , )
/// sql
///
///
public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
// MySqlCommand
MySqlCommand cmd = new MySqlCommand();
// MySqlConnection
MySqlConnection conn = new MySqlConnection(connectionString);
// try/catch sql / ,
// , ,
try
{
// PrepareCommand , MySqlCommand
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
// MySqlCommand ExecuteReader
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
//
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception e)
{
throw e;
}
}
///
///
///
///
/// :
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///
/// ( , , )
/// sql
///
/// Convert.To{Type}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
///
///
///
///
/// :
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///
/// ( , , )
/// sql
///
/// Convert.To{Type}
public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
///
///
///
/// sql
/// OleDb
/// OleDb
///
/// , :Select * from Products
///
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
本稿で述べたことが皆さんのC#プログラム設計に役立つことを願っています.