當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

.NET MYSQL数据库操作基类( C#源码)

發(fā)布時(shí)間：2024/1/17 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 .NET MYSQL数据库操作基类( C#源码) 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

使用MySql.Data.dll，它.Net訪問(wèn)MySQL數(shù)據(jù)庫(kù)的一個(gè)驅(qū)動(dòng)　　下載

DbHelperMySql.CS

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MySql.Data.Types;
using MySql.Data.MySqlClient;

??? /// <summary>
??? /// C#操作MYSQL數(shù)據(jù)庫(kù)基類
??? /// </summary>
??? public abstract class DbHelperMySql
??? {
??????? //數(shù)據(jù)庫(kù)連接字符串(web.config來(lái)配置)
??????? public static string connectionString = ConfigurationManager.AppSettings["MyString"];

??????? public DbHelperMySql()
??????? {}

??????? #region? 執(zhí)行簡(jiǎn)單SQL語(yǔ)句

??????? /// <summary>
??????? /// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
??????? /// </summary>
??????? /// <param name="SQLString">SQL語(yǔ)句</param>
??????? /// <returns>影響的記錄數(shù)</returns>
??????? public static int ExecuteSql(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 (MySqlException E)
??????????????????? {
??????????????????????? throw new Exception(E.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行SQL語(yǔ)句，設(shè)置命令的執(zhí)行等待時(shí)間
??????? /// </summary>
??????? /// <param name="SQLString"></param>
??????? /// <param name="Times"></param>
??????? /// <returns></returns>
??????? public static int ExecuteSqlByTime(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 (MySqlException E)
??????????????????? {
??????????????????????? throw new Exception(E.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
??????? /// </summary>
??????? /// <param name="SQLStringList">多條SQL語(yǔ)句</param>??
??????? public static void ExecuteSqlTran(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 (MySqlException E)
??????????????? {
??????????????????? tx.Rollback();
??????????????????? throw new Exception(E.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? cmd.Dispose();
??????????????????? conn.Close();
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行帶一個(gè)存儲(chǔ)過(guò)程參數(shù)的的SQL語(yǔ)句。
??????? /// </summary>
??????? /// <param name="SQLString">SQL語(yǔ)句</param>
??????? /// <param name="content">參數(shù)內(nèi)容,比如一個(gè)字段是格式復(fù)雜的文章，有特殊符號(hào)，可以通過(guò)這個(gè)方式添加</param>
??????? /// <returns>影響的記錄數(shù)</returns>
??????? public static int ExecuteSql(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 (MySqlException E)
??????????????? {
??????????????????? throw new Exception(E.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? cmd.Dispose();
??????????????????? connection.Close();
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行帶一個(gè)存儲(chǔ)過(guò)程參數(shù)的的SQL語(yǔ)句。
??????? /// </summary>
??????? /// <param name="SQLString">SQL語(yǔ)句</param>
??????? /// <param name="content">參數(shù)內(nèi)容,比如一個(gè)字段是格式復(fù)雜的文章，有特殊符號(hào)，可以通過(guò)這個(gè)方式添加</param>
??????? /// <returns>影響的記錄數(shù)</returns>
??????? public static object ExecuteSqlGet(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 (MySqlException E)
??????????????? {
??????????????????? throw new Exception(E.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? cmd.Dispose();
??????????????????? connection.Close();
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 向數(shù)據(jù)庫(kù)里插入圖像格式的字段(和上面情況類似的另一種實(shí)例)
??????? /// </summary>
??????? /// <param name="strSQL">SQL語(yǔ)句</param>
??????? /// <param name="fs">圖像字節(jié),數(shù)據(jù)庫(kù)的字段類型為image的情況</param>
??????? /// <returns>影響的記錄數(shù)</returns>
??????? public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
??????? {
??????????? using (MySqlConnection connection = new MySqlConnection(connectionString))
??????????? {
??????????????? MySqlCommand cmd = new MySqlCommand(strSQL, connection);
??????????????? MySqlParameter myParameter = new MySqlParameter("@fs", MySqlDbType.Binary);
??????????????? myParameter.Value = fs;
??????????????? cmd.Parameters.Add(myParameter);
??????????????? try
??????????????? {
??????????????????? connection.Open();
??????????????????? int rows = cmd.ExecuteNonQuery();
??????????????????? return rows;
??????????????? }
??????????????? catch (MySqlException E)
??????????????? {
??????????????????? throw new Exception(E.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? cmd.Dispose();
??????????????????? connection.Close();
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行一條計(jì)算查詢結(jié)果語(yǔ)句，返回查詢結(jié)果（object）。
??????? /// </summary>
??????? /// <param name="SQLString">計(jì)算查詢結(jié)果語(yǔ)句</param>
??????? /// <returns>查詢結(jié)果（object）</returns>
??????? public static object GetSingle(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 (MySqlException e)
??????????????????? {
??????????????????????? throw new Exception(e.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行查詢語(yǔ)句，返回MySqlDataReader(使用該方法切記要手工關(guān)閉MySqlDataReader和連接)
??????? /// </summary>
??????? /// <param name="strSQL">查詢語(yǔ)句</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 (MySqlException e)
??????????? {
??????????????? throw new Exception(e.Message);
??????????? }
??????????? //finally //不能在此關(guān)閉，否則，返回的對(duì)象將無(wú)法使用
??????????? //{
??????????? //?cmd.Dispose();
??????????? //?connection.Close();
??????????? //}?

??????? }

??????? /// <summary>
??????? /// 執(zhí)行查詢語(yǔ)句，返回DataSet
??????? /// </summary>
??????? /// <param name="SQLString">查詢語(yǔ)句</param>
??????? /// <returns>DataSet</returns>
??????? public static DataSet Query(string SQLString)
??????? {
??????????? using (MySqlConnection connection = new MySqlConnection(connectionString))
??????????? {
??????????????? DataSet ds = new DataSet();
??????????????? try
??????????????? {
??????????????????? connection.Open();
??????????????????? MySqlDataAdapter da = new MySqlDataAdapter(SQLString, connection);
??????????????????? da.Fill(ds);
??????????????? }
??????????????? catch (MySqlException ex)
??????????????? {
??????????????????? connection.Close();
??????????????????? throw new Exception(ex.Message);
??????????????? }
??????????????? finally
??????????????? {????????????????????
??????????????????? connection.Close();
??????????????? }
??????????????? return ds;
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行查詢語(yǔ)句，返回DataSet,設(shè)置命令的執(zhí)行等待時(shí)間
??????? /// </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 (MySqlException ex)
??????????????? {
??????????????????? throw new Exception(ex.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? connection.Close();
??????????????? }
??????????????? return ds;
??????????? }
??????? }

??????? /// <summary>
??????? /// 獲取SQL查詢記錄條數(shù)
??????? /// </summary>
??????? /// <param name="sqlstr">SQL語(yǔ)句</param>
??????? /// <returns></returns>
??????? public static int GetRowsNum(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");
??????????????????? return ds.Tables[0].Rows.Count;
??????????????? }
??????????????? catch (MySqlException ex)
??????????????? {
??????????????????? throw new Exception(ex.Message);
??????????????? }
??????????????? finally
??????????????? {
??????????????????? connection.Close();
??????????????? }
??????????? }
??????? }

??????? #endregion

??????? #region 執(zhí)行帶參數(shù)的SQL語(yǔ)句

??????? /// <summary>
??????? /// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
??????? /// </summary>
??????? /// <param name="SQLString">SQL語(yǔ)句</param>
??????? /// <returns>影響的記錄數(shù)</returns>
??????? public static int ExecuteSql(string SQLString, params? Object[] 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 (MySqlException E)
??????????????????? {
??????????????????????? throw new Exception(E.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行多條SQL語(yǔ)句，實(shí)現(xiàn)數(shù)據(jù)庫(kù)事務(wù)。
??????? /// </summary>
??????? /// <param name="SQLStringList">SQL語(yǔ)句的哈希表（key為sql語(yǔ)句，value是該語(yǔ)句的 Object[]）</param>
??????? public static void ExecuteSqlTran(Hashtable SQLStringList)
??????? {
??????????? using (MySqlConnection conn = new MySqlConnection(connectionString))
??????????? {
??????????????? conn.Open();
??????????????? using (MySqlTransaction trans = conn.BeginTransaction())
??????????????? {
??????????????????? MySqlCommand cmd = new MySqlCommand();
??????????????????? try
??????????????????? {
??????????????????????? //循環(huán)
??????????????????????? foreach (DictionaryEntry myDE in SQLStringList)
??????????????????????? {
??????????????????????????? string cmdText = myDE.Key.ToString();
??????????????????????????? Object[] cmdParms = (Object[])myDE.Value;
??????????????????????????? PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
??????????????????????????? int val = cmd.ExecuteNonQuery();
??????????????????????????? cmd.Parameters.Clear();

??????????????????????????? trans.Commit();
??????????????????????? }
??????????????????? }
??????????????????? catch
??????????????????? {
??????????????????????? trans.Rollback();
??????????????????????? throw;
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? conn.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行一條計(jì)算查詢結(jié)果語(yǔ)句，返回查詢結(jié)果（object）。
??????? /// </summary>
??????? /// <param name="SQLString">計(jì)算查詢結(jié)果語(yǔ)句</param>
??????? /// <returns>查詢結(jié)果（object）</returns>
??????? public static object GetSingle(string SQLString, params? Object[] 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 (MySqlException e)
??????????????????? {
??????????????????????? throw new Exception(e.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????? }
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行查詢語(yǔ)句，返回MySqlDataReader (使用該方法切記要手工關(guān)閉MySqlDataReader和連接)
??????? /// </summary>
??????? /// <param name="strSQL">查詢語(yǔ)句</param>
??????? /// <returns>MySqlDataReader</returns>
??????? public static MySqlDataReader ExecuteReader(string SQLString, params? Object[] 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 (MySqlException e)
??????????? {
??????????????? throw new Exception(e.Message);
??????????? }
??????????? //finally //不能在此關(guān)閉，否則，返回的對(duì)象將無(wú)法使用
??????????? //{
??????????? //?cmd.Dispose();
??????????? //?connection.Close();
??????????? //}?

??????? }

??????? /// <summary>
??????? /// 執(zhí)行查詢語(yǔ)句，返回DataSet
??????? /// </summary>
??????? /// <param name="SQLString">查詢語(yǔ)句</param>
??????? /// <returns>DataSet</returns>
??????? public static DataSet Query(string SQLString, params? Object[] 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 (MySqlException ex)
??????????????????? {
??????????????????????? throw new Exception(ex.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? cmd.Dispose();
??????????????????????? connection.Close();
??????????????????? }
??????????????????? return ds;
??????????????? }
??????????? }
??????? }

??????? private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, Object[] 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 存儲(chǔ)過(guò)程操作

??????? /// <summary>
??????? /// 執(zhí)行存儲(chǔ)過(guò)程? (使用該方法切記要手工關(guān)閉MySqlDataReader和連接)
??????? /// 手動(dòng)關(guān)閉不了，所以少用，MySql.Data組組件還沒(méi)解決該問(wèn)題
??????? /// </summary>
??????? /// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
??????? /// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
??????? /// <returns>MySqlDataReader</returns>
??????? public static MySqlDataReader RunProcedure(string storedProcName, Object[] 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(); 不能在此關(guān)閉，否則，返回的對(duì)象將無(wú)法使用????????????
??????????? return returnReader;

??????? }

??????? /// <summary>
??????? /// 執(zhí)行存儲(chǔ)過(guò)程
??????? /// </summary>
??????? /// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
??????? /// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
??????? /// <param name="tableName">DataSet結(jié)果中的表名</param>
??????? /// <returns>DataSet</returns>
??????? public static DataSet RunProcedure(string storedProcName, Object[] 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, Object[] 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>
??????? /// 執(zhí)行存儲(chǔ)過(guò)程?
??????? /// </summary>
??????? /// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
??????? /// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
??????? /// <returns></returns>
??????? public static void RunProcedureNull(string storedProcName, Object[] parameters)
??????? {
??????????? using (MySqlConnection connection = new MySqlConnection(connectionString))
??????????? {

??????????????? connection.Open();
??????????????? MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
??????????????? command.ExecuteNonQuery();
??????????????? connection.Close();
??????????? }
??????? }

??????? /// <summary>
??????? /// 執(zhí)行
??????? /// </summary>
??????? /// <param name="CommandText">T-SQL語(yǔ)句；例如："pr_shell 'dir *.exe'"或"select * from sysobjects where?xtype=@xtype"</param>
??????? /// <param name="parameters">SQL參數(shù)</param>
??????? /// <returns>返回第一行第一列</returns>
??????? public object ExecuteScaler(string storedProcName, Object[] parameters)
??????? {
??????????? using (MySqlConnection connection = new MySqlConnection(connectionString))
??????????? {
??????????????? object returnObjectValue;
??????????????? connection.Open();
??????????????? MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
??????????????? returnObjectValue = command.ExecuteScalar();
??????????????? connection.Close();
??????????????? return returnObjectValue;
??????????? }
??????? }

??????? /// <summary>
??????? /// 構(gòu)建 SqlCommand 對(duì)象(用來(lái)返回一個(gè)結(jié)果集，而不是一個(gè)整數(shù)值)
??????? /// </summary>
??????? /// <param name="connection">數(shù)據(jù)庫(kù)連接</param>
??????? /// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
??????? /// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
??????? /// <returns>SqlCommand</returns>
??????? private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, Object[] parameters)
??????? {
??????????? MySqlCommand command = new MySqlCommand(storedProcName, connection);
??????????? command.CommandType = CommandType.StoredProcedure;
??????????? foreach (MySqlParameter parameter in parameters)
??????????? {
??????????????? if (parameter != null)
??????????????? {
??????????????????? // 檢查未分配值的輸出參數(shù),將其分配以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>
??????? /// 創(chuàng)建 MySqlCommand 對(duì)象實(shí)例(用來(lái)返回一個(gè)整數(shù)值)?
??????? /// </summary>
??????? /// <param name="storedProcName">存儲(chǔ)過(guò)程名</param>
??????? /// <param name="parameters">存儲(chǔ)過(guò)程參數(shù)</param>
??????? /// <returns>MySqlCommand 對(duì)象實(shí)例</returns>
??????? private static MySqlCommand BuildIntCommand(MySqlConnection connection, string storedProcName, Object[] 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

??????? /// <summary>
??????? /// MySqlDataReader轉(zhuǎn)換成DataTable
??????? /// </summary>
??????? /// <param name="dataReader"></param>
??????? /// <returns></returns>
??????? public static DataTable GetNewDataTable(MySqlDataReader dataReader)
??????? {
??????????? DataTable datatable = new DataTable();
??????????? DataTable schemaTable = dataReader.GetSchemaTable();

??????????? //動(dòng)態(tài)添加列
??????????? try
??????????? {
??????????????? foreach (DataRow myRow in schemaTable.Rows)
??????????????? {
??????????????????? DataColumn myDataColumn = new DataColumn();
??????????????????? myDataColumn.DataType = myRow.GetType();
??????????????????? myDataColumn.ColumnName = myRow[0].ToString();
??????????????????? datatable.Columns.Add(myDataColumn);
??????????????? }
??????????????? //添加數(shù)據(jù)
??????????????? while (dataReader.Read())
??????????????? {
??????????????????? DataRow myDataRow = datatable.NewRow();
??????????????????? for (int i = 0; i < schemaTable.Rows.Count; i++)
??????????????????? {
??????????????????????? myDataRow[i] = dataReader[i].ToString();
??????????????????? }
??????????????????? datatable.Rows.Add(myDataRow);
??????????????????? myDataRow = null;
??????????????? }
??????????????? schemaTable = null;
??????????????? dataReader.Close();
??????????????? return datatable;
??????????? }
??????????? catch (Exception ex)
??????????? {
??????????????? throw new Exception("轉(zhuǎn)換出錯(cuò)出錯(cuò)!", ex);
??????????? }
??????? }
??? }

轉(zhuǎn)載于:https://www.cnblogs.com/youlechang123/archive/2012/02/27/2369307.html

總結(jié)

以上是生活随笔為你收集整理的.NET MYSQL数据库操作基类( C#源码)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。