发布：2024/3/26 23:06:37作者：管理员 来源：本站 浏览次数：496
一、连接MySQL数据库的帮助类
引用：
using MySql.Data.MySqlClient;
using System.Data;
类：
public class MySqlHelper
{
public MySqlHelper(string constr)
{
con = new MySqlConnection(constr);
}
#region 变量
private MySqlConnection con;
private MySqlCommand cmd;
private MySqlDataAdapter adapt;
private MySqlTransaction trans;
public string error;//错误原因
#endregion
#region 方法
public void db_open()//打开数据库连接
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (con.State == ConnectionState.Broken)
{
con.Close();
con.Open();
}
}
catch (MySqlException ex)
{
throw ex;
}
}
public void db_close()
{
if (con == null)
{
return;
}
try
{
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public int ExecuteNonQuery(string sql, params MySqlParameter[] paras)//执行非查询的操作
{
cmd = null;
db_open();
cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
try
{
if (paras != null)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
int i = cmd.ExecuteNonQuery();
return i;
}
catch (MySqlException ex)
{
Log.write_log("ExecuteNonQuery错误：" + ex.Message);
error = ex.Message;
return 0;
}
finally
{
cmd.Dispose();
// db_close();
}
}
public bool ExecuteNonQuery(List<string> sqls, bool use_trans)//执行非查询的批量操作
{
cmd = null;
trans = null;
db_open();
if (use_trans)
{
trans = con.BeginTransaction();
}
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
try
{
foreach (var item in sqls)
{
cmd.CommandText = item;
cmd.ExecuteNonQuery();
}
if (trans != null)
{
trans.Commit();
}
return true;
}
catch (MySqlException ex)
{
error = ex.Message;
Log.write_log("ExecuteNonQuery错误：" + ex.Message);
if (trans != null)
{
trans.Rollback();
}
return false;
}
finally
{
trans.Dispose();
cmd.Dispose();
// db_close();
}
}
public DataTable ExecuteDataTable_reader(string sql, params MySqlParameter[] paras)//通过适配器查询数据
{
DataTable dt = null;
cmd = null;
db_open();
cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
try
{
if (paras != null)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
dt = new DataTable();
adapt = new MySqlDataAdapter(cmd);
adapt.Fill(dt);
return dt;
}
catch (MySqlException ex)
{
Log.write_log("ExecuteDataTable_reader错误：" + ex.Message);
error = ex.Message;
return null;
}
finally
{
adapt.Dispose();
cmd.Dispose();
// db_close();
}
}
public DataSet ExecuteDataTable_adapter(string sql, params MySqlParameter[] paras)//通过适配器查询数据
{
DataSet ds = null;
cmd = null;
db_open();
cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
try
{
if (paras != null)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
adapt = new MySqlDataAdapter(cmd);
ds = new DataSet();
adapt.Fill(ds);
//adapt.Dispose();
return ds;
}
catch (MySqlException ex)
{
Log.write_log("ExecuteDataTable_adapter错误：" + ex.Message);
error = ex.Message;
return null;
}
finally
{
cmd.Dispose();
// db_close();
}
}
public object ExecuteScalar(string sql, params MySqlParameter[] paras)//返回第一行第一列的值
{
object obj = null;
cmd = null;
db_open();
cmd = con.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
try
{
if (paras != null)
{
foreach (var item in paras)
{
cmd.Parameters.Add(item);
}
}
obj = cmd.ExecuteScalar();
return obj;
}
catch (MySqlException ex)
{
cmd.Cancel();
error = ex.Message;
return null;
}
finally
{
cmd.Dispose();
// db_close();
}
}
public bool update_datatable(DataTable dt)//查询出来数据修改后 批量更新回去
{
db_open();
MySqlCommandBuilder mc = null;
try
{
mc = new MySqlCommandBuilder(adapt);
adapt.Update(dt);
return true;
}
catch (MySqlException ex)
{
error = ex.Message;
return false;
}
finally
{
mc.Dispose();
// db_close();
}
}
public MySqlParameter create_parameter(string paraname, object value)//创建sql参数
{
return new MySqlParameter(paraname, value);
}
#endregion
}
调用：
private static readonly MySqlHelper da = new MySqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString);
//以查询为例
DataTable dt = da.ExecuteDataTable_reader($"SELECT * FROM ..........");
二、连接Oracle数据库的帮助类
引用：
using Oracle.ManagedDataAccess.Client;
using System.Data;
类：
public class OraDBHelper
{
public static OracleCommand cmd = null;
public static OracleConnection conn = null;
public static string connstr = ConfigurationManager.ConnectionStrings["OracleConn"].ConnectionString;
public OraDBHelper(string constr)
{
conn = new OracleConnection(constr);
}
#region 建立Oracle数据库连接对象
/// <returns>返回一个数据库连接的OracleConnection对象</returns>
public static OracleConnection init()
{
try
{
conn = new OracleConnection(connstr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception e)
{
Log.write_log("数据库错误：" + e.Message);
throw new Exception(e.Message.ToString());
}
return conn;
}
#endregion
#region 设置OracleCommand对象
/// <param name="cmd">OracleCommand对象 </param>
/// <param name="cmdText">命令文本</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
private static void SetCommand(OracleCommand cmd, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
cmd.Parameters.AddRange(cmdParms);
}
}
#endregion
/* #region 执行相应的Oracle sql语句，返回相应的DataTable对象
/// <param name="sqlstr">sql语句</param>
/// <returns>返回相应的DataSet对象</returns>
public static DataTable GetDataTable(string sqlstr)
{
DataTable dt = new DataTable();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(dt);
conn.Close();
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
return dt;
}
#endregion
*/
#region 执行相应的Oracle sql语句，返回相应的DataSet对象
/// <param name="sqlstr">sql语句</param>
/// <returns>返回相应的DataSet对象</returns>
public static DataSet GetDataSet(string sqlstr)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(set);
conn.Close();
}
catch (Exception e)
{
Log.write_log("数据库错误：" + e.Message);
throw new Exception(e.Message.ToString());
}
return set;
}
#endregion
#region 执行相应的Oracle sql语句，返回相应的DataTable对象
/// <param name="sqlstr">sql语句</param>
/// <returns>返回相应的DataSet对象</returns>
public static DataTable GetDataTable(string sqlstr)
{
DataTable dt = new DataTable();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(dt);
conn.Close();
}
catch (Exception e)
{
Log.write_log("数据库错误：" + e.Message);
throw new Exception(e.Message.ToString());
}
return dt;
}
#endregion
#region 执行sql语句，返回DataSet对象
/// <param name="sqlstr">sql语句</param>
/// <param name="tableName">表名</param>
/// <returns>返回DataSet对象</returns>
public static DataSet GetDataSet(string sqlstr, string tableName)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(set, tableName);
conn.Close();
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
return set;
}
#endregion
#region 执行不带参数的sql语句，返回受影响的行数
/// <param name="cmdstr">增，删，改sql语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdText)
{
int count;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
count = cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return count;
}
#endregion
#region 执行带参数的Oracle sql语句或存储过程，返回行数
/// <param name="cmdText">带参数的sql语句和存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery1(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
int count;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return count;
}
#endregion
#region 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
/// <param name="cmdstr">相应的sql语句</param>
/// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
public static OracleDataReader ExecuteReader(string cmdText)
{
OracleDataReader reader;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return reader;
}
#endregion
#region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
/// <param name="cmdText">sql语句或存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
OracleDataReader reader;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return reader;
}
#endregion
#region 执行不带参数sql语句,返回结果集首行首列的值object
/// <param name="cmdstr">相应的sql语句</param>
/// <returns>返回结果集首行首列的值object</returns>
public static object ExecuteScalar(string cmdText)
{
object obj;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
obj = cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return obj;
}
#endregion
#region 执行带参数sql语句或存储过程,返回结果集首行首列的值object
/// <param name="cmdText">sql语句或存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">返回结果集首行首列的值object</param>
/// <returns></returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
object obj;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
obj = cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
Log.write_log("数据库错误：" + ex.Message);
throw new Exception(ex.Message.ToString());
}
return obj;
}
#endregion
}
调用：
//以查询为例
DataTable dt = OraDBHelper.GetDataTable($"SELECT ..........");
© Copyright 2014 - 2024 柏港建站平台 ejk5.com. 渝ICP备16000791号-4