MYSQLHELPER
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
namespace WindowsFormsApplication9
{
public class MYSQLHELPER
{
public DataSet Ds = new DataSet();
public DataTable Dt = new DataTable();
public String Table_Name = "";
/// <summary>
/// 共用错误提示信息变量
/// </summary>
public String ErrorMsg = "";
/// <summary>
/// 执行SQL语句标志 True为可调试状态 其他为不可调试（执行错误SQL语句不会提示信息，页面其他代码继续执行）
/// </summary>
public String ExecuteFlag = "";
public String ConnectionStr = "";
public MYSQLHELPER()
{
ConnectionStr = ConfigurationSettings.AppSettings["MYSQLCONFIG"].ToString();
}
public MYSQLHELPER( String ConStr)
{
if (ConStr.ToString().Trim().Length > 30)
{
ConnectionStr = ConStr;
}
else
{
ConnectionStr = ConfigurationSettings.AppSettings["MYSQLCONFIG"].ToString();
}
}
private MySqlConnection GetConn()
{
return new MySqlConnection(ConnectionStr);
}
/// <summary>
/// 通过ＳＱＬ语句获取ＤＡＴＡＳＥＴ数据
/// </summary>
/// <param name="sqlQuery">ＳＱＬ语句参数</param>
/// <returns>返回ＤＡＴＡＳＥＴ类型数据</returns>
public DataSet GetDataSetByExecuteSQL(string sqlQuery)
{
DataSet ds = new DataSet();
MySqlCommand cmd = new MySqlCommand();
try
{
using (MySqlConnection conn = GetConn())
{
using (MySqlDataAdapter sda = new MySqlDataAdapter(sqlQuery,conn))
{
sda.Fill(ds);
}
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message.ToString();
return null;
}
return ds;
}
/// <summary>
/// 通过ＳＱＬ语句获取ＤＡＴＡＴＡＢＬＥ
/// </summary>
/// <param name="sqlQuery">ＳＱＬ语句参数</param>
/// <returns>返回ＤＡＴＡＴＡＢＬＥ数据</returns>
public DataTable GetDataTableByExecuteSQL(string sqlQuery)
{
DataSet ds = new DataSet();
MySqlCommand cmd = new MySqlCommand();
try
{
using (MySqlConnection conn = GetConn())
{
using (MySqlDataAdapter sda = new MySqlDataAdapter(sqlQuery, conn))
{
sda.Fill(ds);
}
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message.ToString();
return null;
}
return ds.Tables[0];
}
// <summary>
// 通过SQL语句返回操作影响的行数
// </summary>
// <param name="StrSQL">要执行的SQL语句</param>
// <returns>返回整形数据</returns>
public int CommonExecute(string StrSQL)
{
MySqlCommand cmd = new MySqlCommand();
int rtn = 0;
using (MySqlConnection SQLCONN = GetConn())
{
if (SQLCONN.State != ConnectionState.Open)
{
SQLCONN.Open();
}
cmd.Connection = SQLCONN;
cmd.CommandType = CommandType.Text;
cmd.CommandText = StrSQL;
try
{
rtn = cmd.ExecuteNonQuery();
return rtn;
}
catch (Exception ex)
{
ErrorMsg = ex.Message.ToString();
return -1;
}
finally
{
SQLCONN.Close();
}
}
}
/// <summary>
/// 获取SQL语句执行结果信息
/// </summary>
/// <returns>返回SQL语句执行结果信息</returns>
public String GetErrorMessage()
{
return ErrorMsg.ToString();
}
/*******************************************************************************
*
*
*
* 宋吉峰项目中使用的过程函数部分结束
*
*
*
* *****************************************************************************/
public String SqlInsertBuilder(DataTable dt)
{
String SQL_STR_INSERT_HEADER = "insert into mytablename(";
string SQL_STR_INSERT_FIELDS = "";
string SQL_STR_INSERT_VALUES = "";
string SQL_STR = "";
if (dt.Rows.Count > 0)
{
SQL_STR = "";
foreach (DataRow dr in dt.Rows)
{
SQL_STR_INSERT_HEADER = "insert into mytablename(";
SQL_STR_INSERT_HEADER = SQL_STR_INSERT_HEADER.Replace("mytablename", Table_Name.ToString());
SQL_STR_INSERT_FIELDS = "";
SQL_STR_INSERT_VALUES = "";
foreach (DataColumn dc in dt.Columns)
{
if (dr[dc.ColumnName].ToString().Trim().Length > 0)
{
SQL_STR_INSERT_FIELDS = SQL_STR_INSERT_FIELDS + dc.ColumnName.ToLower().ToString() + ",";
SQL_STR_INSERT_VALUES = SQL_STR_INSERT_VALUES + "', '" + dr[dc.ColumnName].ToString().Trim();
}
}
if (SQL_STR_INSERT_FIELDS.ToString().Trim().Length > 3)
{
SQL_STR = SQL_STR + SQL_STR_INSERT_HEADER + SQL_STR_INSERT_FIELDS.Substring(0, SQL_STR_INSERT_FIELDS.Length - 1) + ")values(" + SQL_STR_INSERT_VALUES.Substring(2, SQL_STR_INSERT_VALUES.Length - 2) + "');";
}
}
}
return SQL_STR;
}
public String CommonInsert(DataTable dt)
{
String SQL_STR = "";
SQL_STR = SqlInsertBuilder(dt);
String[] Sql_Str_ARR;
Sql_Str_ARR = SQL_STR.Split(';');
for (int i = 0; i < Sql_Str_ARR.Length - 1; i++)
{
if (Sql_Str_ARR[i].ToString().Trim().Length > 15)
{
SQL_STR = SQL_STR + "$" + CommonExecute(Sql_Str_ARR[i].ToString());
}
}
return SQL_STR;
}
public String CommonUpdate(String SQL_STR)
{
String[] Sql_Str_ARR;
Sql_Str_ARR = SQL_STR.Split(';');
for (int i = 0; i < Sql_Str_ARR.Length - 1; i++)
{
if (Sql_Str_ARR[i].ToString().Trim().Length > 15)
{
SQL_STR = SQL_STR + "$" + CommonExecute(Sql_Str_ARR[i].ToString());
}
}
return SQL_STR;
}
public DataSet Get_EXECUTE_Log(String MSGS)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.TableName = System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString() + System.DateTime.Now.Day.ToString() + System.DateTime.Now.Hour.ToString() + System.DateTime.Now.Minute.ToString() + System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
dt.Columns.Add("SQLSTR", typeof(System.String));
dt.Columns.Add("RESULT", typeof(System.String));
String[] SQL_RESULT_ARR;
String[] SQL_Arr;
SQL_RESULT_ARR = MSGS.Split('$');
for (int i = 0; i < SQL_RESULT_ARR.Length - 1; i++)
{
SQL_Arr = SQL_RESULT_ARR[i].ToString().Split(';');
DataRow dr = dt.NewRow();
dr["SQLSTR"] = SQL_Arr[0].ToString();
dr["RESULT"] = SQL_RESULT_ARR[i].ToString().Replace(SQL_Arr[0].ToString(), "");
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
return ds;
}
}
}
