|
|
using System;
|
|
|
using System.Collections;
|
|
|
using System.Collections.Specialized;
|
|
|
using System.Data;
|
|
|
using System.Data.SqlClient;
|
|
|
using System.Configuration;
|
|
|
using MySql.Data.MySqlClient;
|
|
|
using MySql.Data.Types;
|
|
|
namespace CommonFunc
|
|
|
{
|
|
|
|
|
|
/// <summary>
|
|
|
/// 数据访问抽象基础类(MYSQL)
|
|
|
/// Copyright (C) 04-08 NMJU.NET
|
|
|
/// All rights reserved
|
|
|
/// </summary>
|
|
|
public abstract class DbHelperToMES
|
|
|
{
|
|
|
private static Logger logger = new Logger();
|
|
|
//数据库连接字符串(web.config来配置)
|
|
|
private static string connectionString = SqlDataObject.GetMESSqlConnectionString;
|
|
|
public DbHelperToMES()
|
|
|
{
|
|
|
}
|
|
|
|
|
|
#region 公用方法
|
|
|
/// <summary>
|
|
|
/// 获取某个表的数量
|
|
|
/// </summary>
|
|
|
/// <param name="field">主键</param>
|
|
|
/// <param name="tableName">表名</param>
|
|
|
/// <param name="where">条件</param>
|
|
|
/// <returns></returns>
|
|
|
public static int GetDataRecordCount(string field, string tableName, string where)
|
|
|
{
|
|
|
|
|
|
|
|
|
string strsql = "select count(" + field + ") from " + tableName;
|
|
|
if (where != "")
|
|
|
{
|
|
|
strsql += " where " + where;
|
|
|
}
|
|
|
object obj = DbHelperSQLServer.ExecuteScalar(strsql);
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return 1;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return int.Parse(obj.ToString());
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
public static int GetMaxID(string FieldName, string TableName)
|
|
|
{
|
|
|
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
|
|
|
object obj = DbHelperSQLServer.ExecuteScalar(strsql);
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return 1;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return int.Parse(obj.ToString());
|
|
|
}
|
|
|
}
|
|
|
public static bool Exists(string strSql)
|
|
|
{
|
|
|
object obj = DbHelperSQLServer.ExecuteScalar(strSql);
|
|
|
int cmdresult;
|
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
|
{
|
|
|
cmdresult = 0;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
cmdresult = int.Parse(obj.ToString());
|
|
|
}
|
|
|
if (cmdresult == 0)
|
|
|
{
|
|
|
return false;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return true;
|
|
|
}
|
|
|
}
|
|
|
public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
|
|
|
{
|
|
|
object obj = DbHelperSQLServer.ExecuteScalar(strSql, cmdParms);
|
|
|
int cmdresult;
|
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
|
{
|
|
|
cmdresult = 0;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
cmdresult = int.Parse(obj.ToString());
|
|
|
}
|
|
|
if (cmdresult == 0)
|
|
|
{
|
|
|
return false;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return true;
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 执行简单SQL语句
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行SQL语句，返回影响的记录数
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">SQL语句</param>
|
|
|
/// <returns>影响的记录数</returns>
|
|
|
public static int ExecuteNonQuery(string SQLString)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
int rows = cmd.ExecuteNonQuery();
|
|
|
return rows;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
connection.Close();
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return 0;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行SQL语句，设置命令的执行等待时间
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString"></param>
|
|
|
/// <param name="Times"></param>
|
|
|
/// <returns></returns>
|
|
|
public static int ExecuteNonQueryByTime(string SQLString, int Times)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
cmd.CommandTimeout = Times;
|
|
|
int rows = cmd.ExecuteNonQuery();
|
|
|
return rows;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
|
|
|
connection.Close();
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return 0;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLStringList">多条SQL语句</param>
|
|
|
public static void ExecuteNonQueryTran(ArrayList SQLStringList)
|
|
|
{
|
|
|
using (MySqlConnection conn = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
conn.Open();
|
|
|
MySqlCommand cmd = new MySqlCommand();
|
|
|
cmd.Connection = conn;
|
|
|
MySqlTransaction tx = conn.BeginTransaction();
|
|
|
cmd.Transaction = tx;
|
|
|
try
|
|
|
{
|
|
|
for (int n = 0; n < SQLStringList.Count; n++)
|
|
|
{
|
|
|
string strsql = SQLStringList[n].ToString();
|
|
|
if (strsql.Trim().Length > 1)
|
|
|
{
|
|
|
cmd.CommandText = strsql;
|
|
|
cmd.ExecuteNonQuery();
|
|
|
}
|
|
|
}
|
|
|
tx.Commit();
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
tx.Rollback();
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
// ITNB.Base.Error.showError(E.Message.ToString());
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 执行带一个存储过程参数的的SQL语句。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">SQL语句</param>
|
|
|
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
|
|
|
/// <returns>影响的记录数</returns>
|
|
|
public static int ExecuteNonQuery(string SQLString, string content)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
|
|
|
MySqlParameter myParameter = new MySqlParameter("@content", MySqlDbType.Text);
|
|
|
myParameter.Value = content;
|
|
|
cmd.Parameters.Add(myParameter);
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
int rows = cmd.ExecuteNonQuery();
|
|
|
return rows;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return 0;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
cmd.Dispose();
|
|
|
connection.Close();
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 执行带一个存储过程参数的的SQL语句。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">SQL语句</param>
|
|
|
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
|
|
|
/// <returns>影响的记录数</returns>
|
|
|
public static object ExecuteNonQueryGet(string SQLString, string content)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
|
|
|
MySqlParameter myParameter = new MySqlParameter("@content", MySqlDbType.Text);
|
|
|
myParameter.Value = content;
|
|
|
cmd.Parameters.Add(myParameter);
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
object obj = cmd.ExecuteScalar();
|
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
|
{
|
|
|
return null;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return obj;
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return null;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
cmd.Dispose();
|
|
|
connection.Close();
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
|
|
|
/// </summary>
|
|
|
/// <param name="strSQL">SQL语句</param>
|
|
|
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
|
|
|
/// <returns>影响的记录数</returns>
|
|
|
public static int ExecuteNonQueryInsertImg(string strSQL, byte[] fs)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
|
|
|
MySqlParameter myParameter = new MySqlParameter("@fs", MySqlDbType.MediumBlob);
|
|
|
myParameter.Value = fs;
|
|
|
cmd.Parameters.Add(myParameter);
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
int rows = cmd.ExecuteNonQuery();
|
|
|
return rows;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(strSQL);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return 0;
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
cmd.Dispose();
|
|
|
connection.Close();
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行一条计算查询结果语句，返回查询结果（object）。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">计算查询结果语句</param>
|
|
|
/// <returns>查询结果（object）</returns>
|
|
|
public static object ExecuteScalar(string SQLString)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
object obj = cmd.ExecuteScalar();
|
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
|
{
|
|
|
return null;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return obj;
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
connection.Close();
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行查询语句，返回MySqlDataReader(使用该方法切记要手工关闭MySqlDataReader和连接)
|
|
|
/// </summary>
|
|
|
/// <param name="strSQL">查询语句</param>
|
|
|
/// <returns>MySqlDataReader</returns>
|
|
|
public static MySqlDataReader ExecuteReader(string strSQL)
|
|
|
{
|
|
|
MySqlConnection connection = new MySqlConnection(connectionString);
|
|
|
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
MySqlDataReader myReader = cmd.ExecuteReader();
|
|
|
return myReader;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(strSQL);
|
|
|
connection.Close();
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return null;
|
|
|
}
|
|
|
//finally //不能在此关闭，否则，返回的对象将无法使用
|
|
|
//{
|
|
|
// cmd.Dispose();
|
|
|
// connection.Close();
|
|
|
//}
|
|
|
|
|
|
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 执行查询语句，返回DataSet
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">查询语句</param>
|
|
|
/// <returns>DataSet</returns>
|
|
|
public static DataSet Query(string SQLString)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
|
|
|
command.Fill(ds, "ds");
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
}
|
|
|
return ds;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 执行查询语句，返回DataSet,设置命令的执行等待时间
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString"></param>
|
|
|
/// <param name="Times"></param>
|
|
|
/// <returns></returns>
|
|
|
public static DataSet Query(string SQLString, int Times)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
try
|
|
|
{
|
|
|
connection.Open();
|
|
|
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
|
|
|
command.SelectCommand.CommandTimeout = Times;
|
|
|
command.Fill(ds, "ds");
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
connection.Close();
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
// ITNB.Base.Error.showError(ex.Message.ToString());
|
|
|
}
|
|
|
return ds;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 执行带参数的SQL语句
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行SQL语句，返回影响的记录数
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">SQL语句</param>
|
|
|
/// <returns>影响的记录数</returns>
|
|
|
public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
using (MySqlCommand cmd = new MySqlCommand())
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
|
int rows = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return rows;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return 0;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行多条SQL语句，实现数据库事务。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
|
|
|
public static void ExecuteNonQueryTran(Hashtable SQLStringList)
|
|
|
{
|
|
|
using (MySqlConnection conn = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
conn.Open();
|
|
|
using (MySqlTransaction trans = conn.BeginTransaction())
|
|
|
{
|
|
|
MySqlCommand cmd = new MySqlCommand();
|
|
|
try
|
|
|
{
|
|
|
//循环
|
|
|
foreach (DictionaryEntry myDE in SQLStringList)
|
|
|
{
|
|
|
string cmdText = myDE.Key.ToString();
|
|
|
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
|
|
|
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
|
|
|
trans.Commit();
|
|
|
}
|
|
|
}
|
|
|
catch
|
|
|
{
|
|
|
trans.Rollback();
|
|
|
throw;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行一条计算查询结果语句，返回查询结果（object）。
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">计算查询结果语句</param>
|
|
|
/// <returns>查询结果（object）</returns>
|
|
|
public static object ExecuteScalar(string SQLString, params MySqlParameter[] cmdParms)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
using (MySqlCommand cmd = new MySqlCommand())
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
|
object obj = cmd.ExecuteScalar();
|
|
|
cmd.Parameters.Clear();
|
|
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
|
|
{
|
|
|
return null;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return obj;
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行查询语句，返回MySqlDataReader (使用该方法切记要手工关闭MySqlDataReader和连接)
|
|
|
/// </summary>
|
|
|
/// <param name="strSQL">查询语句</param>
|
|
|
/// <returns>MySqlDataReader</returns>
|
|
|
public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
|
|
|
{
|
|
|
MySqlConnection connection = new MySqlConnection(connectionString);
|
|
|
MySqlCommand cmd = new MySqlCommand();
|
|
|
try
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
|
MySqlDataReader myReader = cmd.ExecuteReader();
|
|
|
cmd.Parameters.Clear();
|
|
|
return myReader;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
return null;
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行查询语句，返回DataSet
|
|
|
/// </summary>
|
|
|
/// <param name="SQLString">查询语句</param>
|
|
|
/// <returns>DataSet</returns>
|
|
|
public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
MySqlCommand cmd = new MySqlCommand();
|
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
|
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
try
|
|
|
{
|
|
|
da.Fill(ds, "ds");
|
|
|
cmd.Parameters.Clear();
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
logger.Log(SQLString);
|
|
|
logger.Error( ex.Message + "\r\n" + ex.StackTrace);
|
|
|
}
|
|
|
return ds;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, 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 = CommandType.Text;//cmdType;
|
|
|
if (cmdParms != null)
|
|
|
{
|
|
|
foreach (MySqlParameter parameter in cmdParms)
|
|
|
{
|
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
|
|
(parameter.Value == null))
|
|
|
{
|
|
|
parameter.Value = DBNull.Value;
|
|
|
}
|
|
|
cmd.Parameters.Add(parameter);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 存储过程操作
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行存储过程 (使用该方法切记要手工关闭MySqlDataReader和连接)
|
|
|
/// </summary>
|
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
|
/// <param name="parameters">存储过程参数</param>
|
|
|
/// <returns>MySqlDataReader</returns>
|
|
|
public static MySqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
|
|
|
{
|
|
|
MySqlConnection connection = new MySqlConnection(connectionString);
|
|
|
MySqlDataReader returnReader;
|
|
|
connection.Open();
|
|
|
MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
returnReader = command.ExecuteReader();
|
|
|
//Connection.Close(); 不能在此关闭，否则，返回的对象将无法使用
|
|
|
return returnReader;
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行存储过程
|
|
|
/// </summary>
|
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
|
/// <param name="parameters">存储过程参数</param>
|
|
|
/// <param name="tableName">DataSet结果中的表名</param>
|
|
|
/// <returns>DataSet</returns>
|
|
|
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
DataSet dataSet = new DataSet();
|
|
|
connection.Open();
|
|
|
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
|
|
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
|
|
sqlDA.Fill(dataSet, tableName);
|
|
|
connection.Close();
|
|
|
return dataSet;
|
|
|
}
|
|
|
}
|
|
|
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
DataSet dataSet = new DataSet();
|
|
|
connection.Open();
|
|
|
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
|
|
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
|
|
sqlDA.SelectCommand.CommandTimeout = Times;
|
|
|
sqlDA.Fill(dataSet, tableName);
|
|
|
connection.Close();
|
|
|
return dataSet;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 执行存储过程后返回执行结果（标识）
|
|
|
/// </summary>
|
|
|
/// <param name="storedProcName"></param>
|
|
|
/// <param name="parameters"></param>
|
|
|
/// <returns></returns>
|
|
|
public static string RunProcedureState(string storedProcName, IDataParameter[] parameters)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
|
|
|
connection.Open();
|
|
|
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
|
|
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
|
|
sqlDA.SelectCommand.Parameters.Add(new MySqlParameter("ReturnValue", MySqlDbType.Int32, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); //增加存储过程的返回值参数
|
|
|
sqlDA.SelectCommand.ExecuteNonQuery();
|
|
|
connection.Close();
|
|
|
return sqlDA.SelectCommand.Parameters["ReturnValue"].Value.ToString();
|
|
|
}
|
|
|
}
|
|
|
/*
|
|
|
@TableNames VARCHAR(0), --表名，可以是多个表，但不能用别名
|
|
|
@PrimaryKey VARCHAR(0), --主键，可以为空，但@Order为空时该值不能为空
|
|
|
@Fields VARCHAR(0), --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
|
|
|
@PageSize INT, --每页记录数
|
|
|
@CurrentPage INT, --当前页，0表示第1页
|
|
|
@Filter VARCHAR(0) = '', --条件，可以为空，不用填 where
|
|
|
@Group VARCHAR(0) = '', --分组依据，可以为空，不用填 group by
|
|
|
@Order VARCHAR(0) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by
|
|
|
|
|
|
*/
|
|
|
/// <summary>
|
|
|
/// 关键字，显示字段，表，条件，排序，每页显示数，当前页
|
|
|
/// </summary>
|
|
|
/// <param name="PrimaryKey">主键</param>
|
|
|
/// <param name="Fields">要取出的字段</param>
|
|
|
/// <param name="TableNames">表名</param>
|
|
|
/// <param name="Filter">条件</param>
|
|
|
/// <param name="Order">排序</param>
|
|
|
/// <param name="PageSize">每页记录数INT</param>
|
|
|
/// <param name="CurrentPage">当前页，INT</param>
|
|
|
/// <returns></returns>
|
|
|
public static DataSet GetPageDataList(string PrimaryKey, string Fields, string TableNames, string Filter, string Order, int PageSize, int CurrentPage)
|
|
|
{
|
|
|
string tableName = "viewPage";
|
|
|
string storedProcName = "P_viewPage";
|
|
|
IDataParameter[] p = new IDataParameter[8];
|
|
|
p[0] = new MySqlParameter("TableNames", TableNames);
|
|
|
p[1] = new MySqlParameter("PrimaryKey", PrimaryKey);
|
|
|
p[2] = new MySqlParameter("Fields", Fields);
|
|
|
p[3] = new MySqlParameter("PageSize", PageSize);
|
|
|
p[4] = new MySqlParameter("CurrentPage", CurrentPage - 1);
|
|
|
p[5] = new MySqlParameter("Filter", Filter);
|
|
|
p[6] = new MySqlParameter("Group", "");
|
|
|
p[7] = new MySqlParameter("Order", Order);
|
|
|
|
|
|
return RunProcedure(storedProcName, p, tableName);
|
|
|
}
|
|
|
public static DataSet GetPageDataList(string PrimaryKey, string Fields, string TableNames, string Filter, string Order, int PageSize, int CurrentPage, string Group)
|
|
|
{
|
|
|
string tableName = "viewPage";
|
|
|
string storedProcName = "P_viewPage";
|
|
|
IDataParameter[] p = new IDataParameter[8];
|
|
|
p[0] = new MySqlParameter("TableNames", TableNames);
|
|
|
p[1] = new MySqlParameter("PrimaryKey", PrimaryKey);
|
|
|
p[2] = new MySqlParameter("Fields", Fields);
|
|
|
p[3] = new MySqlParameter("PageSize", PageSize);
|
|
|
p[4] = new MySqlParameter("CurrentPage", CurrentPage - 1);
|
|
|
p[5] = new MySqlParameter("Filter", Filter);
|
|
|
p[6] = new MySqlParameter("Group", Group);
|
|
|
p[7] = new MySqlParameter("Order", Order);
|
|
|
|
|
|
return RunProcedure(storedProcName, p, tableName);
|
|
|
}
|
|
|
/*
|
|
|
@TableName VARCHAR(0), --表名
|
|
|
@FieldList VARCHAR(00), --显示列名，如果是全部字段则为*
|
|
|
@PrimaryKey VARCHAR(0), --单一主键或唯一值键
|
|
|
@Where VARCHAR(00), --查询条件 不含'where'字符，如id> and len(userid)>9
|
|
|
@Order VARCHAR(00), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
|
|
|
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
|
|
|
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
|
|
|
@RecorderCount INT, --记录总数 0:会返回总记录
|
|
|
@PageSize INT, --每页输出的记录数
|
|
|
@PageIndex INT, --当前页数
|
|
|
@TotalCount INT OUTPUT, --记返回总记录
|
|
|
@TotalPageCount INT OUTPUT --返回总页数
|
|
|
*/
|
|
|
public static DataSet GetPageDataList2(string PrimaryKey, string FieldList, string TableName, string Where, string Order, int PageSize, int PageIndex)
|
|
|
{
|
|
|
string tableName = "viewPage";
|
|
|
string storedProcName = "P_viewPage2";
|
|
|
IDataParameter[] p = new IDataParameter[11];
|
|
|
p[0] = new MySqlParameter("TableName", TableName);
|
|
|
p[1] = new MySqlParameter("FieldList", FieldList);
|
|
|
p[2] = new MySqlParameter("PrimaryKey", PrimaryKey);
|
|
|
p[3] = new MySqlParameter("Where", Where);
|
|
|
p[4] = new MySqlParameter("Order", Order);
|
|
|
p[5] = new MySqlParameter("SortType", 3);
|
|
|
p[6] = new MySqlParameter("RecorderCount", 0);
|
|
|
p[7] = new MySqlParameter("PageSize", PageSize);
|
|
|
p[8] = new MySqlParameter("PageIndex", PageIndex);
|
|
|
p[9] = new MySqlParameter("TotalCount", 0);
|
|
|
p[10] = new MySqlParameter("TotalPageCount", 0);
|
|
|
|
|
|
|
|
|
return RunProcedure(storedProcName, p, tableName);
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 构建 MySqlCommand 对象(用来返回一个结果集，而不是一个整数值)
|
|
|
/// </summary>
|
|
|
/// <param name="connection">数据库连接</param>
|
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
|
/// <param name="parameters">存储过程参数</param>
|
|
|
/// <returns>MySqlCommand</returns>
|
|
|
private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
|
|
{
|
|
|
MySqlCommand command = new MySqlCommand(storedProcName, connection);
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
if (parameters != null)
|
|
|
{
|
|
|
foreach (MySqlParameter parameter in parameters)
|
|
|
{
|
|
|
if (parameter != null)
|
|
|
{
|
|
|
// 检查未分配值的输出参数,将其分配以DBNull.Value.
|
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
|
|
(parameter.Value == null))
|
|
|
{
|
|
|
parameter.Value = DBNull.Value;
|
|
|
}
|
|
|
command.Parameters.Add(parameter);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
return command;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行存储过程，返回影响的行数
|
|
|
/// </summary>
|
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
|
/// <param name="parameters">存储过程参数</param>
|
|
|
/// <param name="rowsAffected">影响的行数</param>
|
|
|
/// <returns></returns>
|
|
|
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
|
|
|
{
|
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
|
{
|
|
|
int result;
|
|
|
connection.Open();
|
|
|
MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
|
|
|
rowsAffected = command.ExecuteNonQuery();
|
|
|
result = (int)command.Parameters["ReturnValue"].Value;
|
|
|
//Connection.Close();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 创建 MySqlCommand 对象实例(用来返回一个整数值)
|
|
|
/// </summary>
|
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
|
/// <param name="parameters">存储过程参数</param>
|
|
|
/// <returns>MySqlCommand 对象实例</returns>
|
|
|
private static MySqlCommand BuildIntCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
|
|
{
|
|
|
MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
|
|
|
command.Parameters.Add(new MySqlParameter("ReturnValue",
|
|
|
MySqlDbType.Int32, 4, ParameterDirection.ReturnValue,
|
|
|
false, 0, 0, string.Empty, DataRowVersion.Default, null));
|
|
|
return command;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region SQL语句式分页
|
|
|
/// <summary>
|
|
|
/// 智能返回SQL语句
|
|
|
/// </summary>
|
|
|
/// <param name="primaryKey">主键（不能为空）</param>
|
|
|
/// <param name="queryFields">提取字段（不能为空）</param>
|
|
|
/// <param name="tableName">表（理论上允许多表）</param>
|
|
|
/// <param name="condition">条件（可以空）</param>
|
|
|
/// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>
|
|
|
/// <param name="pageSize">分页数（不能为空）</param>
|
|
|
/// <param name="pageIndex">当前页，起始为：1（不能为空）</param>
|
|
|
/// <returns></returns>
|
|
|
|
|
|
public static DataSet GetPageDataListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)
|
|
|
{
|
|
|
string strTmp = ""; //---strTmp用于返回的SQL语句
|
|
|
string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = "";
|
|
|
//0：分页数量
|
|
|
//1:提取字段
|
|
|
//2:表
|
|
|
//3:条件
|
|
|
//4:主键不存在的记录
|
|
|
//5:排序
|
|
|
SqlSelect = " select top {0} {1} from {2} {3} {4} {5}";
|
|
|
//0:主键
|
|
|
//1:TOP数量,为分页数*(排序号-1)
|
|
|
//2:表
|
|
|
//3:条件
|
|
|
//4:排序
|
|
|
SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";
|
|
|
|
|
|
if (orderBy != "")
|
|
|
strOrderBy = " order by " + orderBy;
|
|
|
if (condition != "")
|
|
|
strWhere += " and " + condition;
|
|
|
int pageindexsize = (pageIndex - 1) * pageSize;
|
|
|
if (pageindexsize > 0)
|
|
|
{
|
|
|
strTop = " top " + pageindexsize.ToString();
|
|
|
|
|
|
SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy);
|
|
|
|
|
|
strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy);
|
|
|
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy);
|
|
|
|
|
|
}
|
|
|
return Query(strTmp);
|
|
|
}
|
|
|
#endregion
|
|
|
#region 获取安全的SQL字符串
|
|
|
/// <summary>
|
|
|
/// 获取安全的SQL字符串
|
|
|
/// </summary>
|
|
|
/// <param name="sql"></param>
|
|
|
/// <returns></returns>
|
|
|
public static string GetSafeSQLString(string sql)
|
|
|
{
|
|
|
sql = sql.Replace(",", "，");
|
|
|
sql = sql.Replace(".", "。");
|
|
|
sql = sql.Replace("(", "（");
|
|
|
sql = sql.Replace(")", "）");
|
|
|
sql = sql.Replace(">", "＞");
|
|
|
sql = sql.Replace("<", "＜");
|
|
|
sql = sql.Replace("-", "－");
|
|
|
sql = sql.Replace("+", "＋");
|
|
|
sql = sql.Replace("=", "＝");
|
|
|
sql = sql.Replace("?", "？");
|
|
|
sql = sql.Replace("*", "＊");
|
|
|
sql = sql.Replace("|", "｜");
|
|
|
sql = sql.Replace("&", "＆");
|
|
|
return sql;
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
|
|
|
} |