using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Collections;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Configuration;
using System.IO;
namespace MSCL
{
///
/// 先引用官网MySql.Data.dll文件
///
public abstract class MYSQLHelper
{
///
/// a valid database connectionstring
///
public static string connectionStringManager = ConfigurationManager.AppSettings["MySqlConnStr"].ToString();
///
/// a valid database connectionstring
///
public static string ConnectionStringManager
{
get { return connectionStringManager; }
}
//hashtable to store the parameter information, the hash table can store any type of argument
//Here the hashtable is static types of static variables, since it is static, that is a definition of global use.
//All parameters are using this hash table, how to ensure that others in the change does not affect their time to read it
//Before ,the method can use the lock method to lock the table, does not allow others to modify.when it has readed then unlocked table.
//Now .NET provides a HashTable's Synchronized methods to achieve the same function, no need to manually lock, completed directly by the system framework
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
///
/// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
/// The parameter list using parameters that in array forms
///
///
/// Usage example:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,
/// "PublishOrders", new MySqlParameter("@prodid", 24));
///
/// a valid database connectionstring
/// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
/// stored procedure name or T-SQL statement
/// MySqlCommand to provide an array of parameters used in the list
/// Returns a value that means number of rows affected
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;
}
}
///
/// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
/// The parameter list using parameters that in array forms
///
///
/// Usage example:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,
/// "PublishOrders", new MySqlParameter("@prodid", 24));
///
/// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
/// a valid database connectionstring
/// stored procedure name or T-SQL statement
/// MySqlCommand to provide an array of parameters used in the list
/// Returns true or false
public static bool ExecuteNonQuery(CommandType cmdType, string connectionString, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
try
{
int val = cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
finally
{
cmd.Parameters.Clear();
}
}
}
///
/// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
/// Array of form parameters using the parameter list
///
/// connection
/// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
/// stored procedure name or T-SQL statement
/// MySqlCommand to provide an array of parameters used in the list
/// Returns a value that means number of rows affected
public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
/// Array of form parameters using the parameter list
///
/// sql Connection that has transaction
/// SqlCommand command type (stored procedures, T-SQL statement, and so on.)
/// stored procedure name or T-SQL statement
/// MySqlCommand to provide an array of parameters used in the list
/// Returns a value that means number of rows affected
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;
}
///
/// Call method of sqldatareader to read data
///
/// connectionstring
/// command type, such as using stored procedures: CommandType.StoredProcedure
/// stored procedure name or T-SQL statement
/// parameters
/// SqlDataReader type of data collection
public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
MySqlConnection conn = new MySqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
///
/// use the ExectueScalar to read a single result
///
/// connectionstring
/// command type, such as using stored procedures: CommandType.StoredProcedure
/// stored procedure name or T-SQL statement
/// parameters
/// a value in object 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;
}
}
///
/// Call method of dataset to read data
///
/// connectionstring
/// stored procedure name or T-SQL statement
/// parameters
/// DataSet
public static DataSet GetDataSet(string connectionString, string cmdText, params MySqlParameter[] commandParameters)
{
DataSet retSet = new DataSet();
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString))
{
msda.Fill(retSet);
}
return retSet;
}
///
/// Call method of datatable to read data
///
/// connectionstring
/// stored procedure name or T-SQL statement
/// parameters
/// DataTable
public static DataTable GetDataTable(string connectionString, string cmdText, params MySqlParameter[] commandParameters)
{
DataSet retSet = new DataSet();
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString))
{
msda.Fill(retSet);
}
return retSet.Tables[0];
}
///
/// cache the parameters in the HashTable
///
/// hashtable key name
/// the parameters that need to cached
public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
///
/// get parameters in hashtable by cacheKey
///
/// hashtable key name
/// the parameters
public static MySqlParameter[] GetCachedParameters(string cacheKey)
{
MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
///
///Prepare parameters for the implementation of the command
///
/// mySqlCommand command
/// database connection that is existing
/// database transaction processing
/// SqlCommand command type (stored procedures, T-SQL statement, and so on.)
/// Command text, T-SQL statements such as Select * from Products
/// return the command that has parameters
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);
}
#region parameters
///
/// Set parameters
///
/// parameter name
/// data type
/// type size
/// input or output
/// set the value
/// Return parameters that has been assigned
public static MySqlParameter CreateParam(string ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
MySqlParameter param;
if (Size > 0)
{
param = new MySqlParameter(ParamName, DbType, Size);
}
else
{
param = new MySqlParameter(ParamName, DbType);
}
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
///
/// set Input parameters
///
/// parameter names, such as:@ id
/// parameter types, such as: MySqlDbType.Int
/// size parameters, such as: the length of character type for the 100
/// parameter value to be assigned
/// Parameters
public static MySqlParameter CreateInParam(string ParamName, MySqlDbType DbType, int Size, object Value)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// Output parameters
///
/// parameter names, such as:@ id
/// parameter types, such as: MySqlDbType.Int
/// size parameters, such as: the length of character type for the 100
/// parameter value to be assigned
/// Parameters
public static MySqlParameter CreateOutParam(string ParamName, MySqlDbType DbType, int Size)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
///
/// Set return parameter value
///
/// parameter names, such as:@ id
/// parameter types, such as: MySqlDbType.Int
/// size parameters, such as: the length of character type for the 100
/// parameter value to be assigned<
/// Parameters
public static MySqlParameter CreateReturnParam(string ParamName, MySqlDbType DbType, int Size)
{
return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}
///
/// Generate paging storedProcedure parameters
///
/// CurrentPageIndex
/// pageSize
/// query Condition
/// tableName
/// columns to query
/// sort
/// MySqlParameter collection
public static MySqlParameter[] GetPageParm(int CurrentIndex, int PageSize, string WhereSql, string TableName, string Columns, Hashtable Sort)
{
MySqlParameter[] parm = {
MYSQLHelper.CreateInParam("@CurrentIndex", MySqlDbType.Int32, 4, CurrentIndex ),
MYSQLHelper.CreateInParam("@PageSize", MySqlDbType.Int32, 4, PageSize ),
MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 2500, WhereSql ),
MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ),
MYSQLHelper.CreateInParam("@Column", MySqlDbType.VarChar, 2500, Columns ),
MYSQLHelper.CreateInParam("@Sort", MySqlDbType.VarChar, 50, GetSort(Sort) ),
MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 )
};
return parm;
}
///
/// Statistics data that in table
///
/// table name
/// Statistics column
/// conditions
/// Set of parameters
public static MySqlParameter[] GetCountParm(string TableName, string Columns, string WhereSql)
{
MySqlParameter[] parm = {
MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ),
MYSQLHelper.CreateInParam("@CountColumn", MySqlDbType.VarChar, 20, Columns ),
MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 250, WhereSql ),
MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 )
};
return parm;
}
///
/// Get the sql that is Sorted
///
/// sort column and values
/// SQL sort string
private static string GetSort(Hashtable sort)
{
string str = "";
int i = 0;
if (sort != null && sort.Count > 0)
{
foreach (DictionaryEntry de in sort)
{
i++;
str += de.Key + " " + de.Value;
if (i != sort.Count)
{
str += ",";
}
}
}
return str;
}
///
/// execute a trascation include one or more sql sentence(author:donne yin)
///
///
///
///
///
/// execute trascation result(success: true | fail: false)
public static bool ExecuteTransaction(string connectionString, CommandType cmdType, string[] cmdTexts, params MySqlParameter[][] commandParameters)
{
MySqlConnection myConnection = new MySqlConnection(connectionString); //get the connection object
myConnection.Open(); //open the connection
MySqlTransaction myTrans = myConnection.BeginTransaction(); //begin a trascation
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = myConnection;
cmd.Transaction = myTrans;
try
{
for (int i = 0; i < cmdTexts.Length; i++)
{
PrepareCommand(cmd, myConnection, null, cmdType, cmdTexts[i], commandParameters[i]);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
myTrans.Commit();
}
catch
{
myTrans.Rollback();
return false;
}
finally
{
myConnection.Close();
}
return true;
}
#endregion
}
}
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
