mysql DB C#封装

最新推荐文章于 2023-08-31 15:52:14 发布

原创最新推荐文章于 2023-08-31 15:52:14 发布 · 491 阅读

0 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一个用于操作MySQL数据库的类库实现，包括连接管理、查询、插入和更新等基本功能，并详细展示了如何通过该类库执行SQL语句及处理异常。

class DBAccess
{
private MySqlConnection conn;
private string connStr;
private DBConfig dbConfig;
private LogHelper log = LogHelper.GetInstance();

//private string selectSql;
//private string updateSql;
//private string insertSql;

/// <summary>
/// 構築関す
/// </summary>
/// <param name="dbConfig">DB配置</param>
public DBAccess(DBConfig dbConfig)
{
this.dbConfig = dbConfig;

connStr = String.Format("server={0};user id={1}; password={2}; database={3}; charset=utf8; pooling=false",
dbConfig.Server, dbConfig.UserId, dbConfig.Password, dbConfig.Database);

}

/// <summary>
/// DB接続オープン
/// </summary>
/// <returns></returns>
public bool IsDBConOpen()
{
bool ret = false;
if ( (conn != null) && (conn.State == ConnectionState.Open) )
{
ret = true;
}
return ret;
}

/// <summary>
/// DB接続オープン
/// </summary>
/// <returns></returns>
public bool OpenDBConnection()
{
bool ret = false;

try
{
if (conn == null)
{
conn = new MySqlConnection(connStr);
}
conn.Open();
ret = true;
}
catch (Exception ex)
{
log.WriteLog(LOG_TyPe.LOG_ERROE, "DB接続オープン失敗");
log.WriteErrorLog(ex.Message);
}
return ret;
}

/// <summary>
/// DB接続クロース
/// </summary>
/// <returns></returns>
public bool CloseDBConnection()
{
bool ret = false;

try
{
if (conn != null)
{
conn.Close();
}
conn = null;
ret = true;
}
catch (Exception ex)
{
log.WriteLog(LOG_TyPe.LOG_ERROE, "DB接続クロース失敗");
log.WriteErrorLog(ex.Message);
}

return ret;
}

public MySqlCommand GetSqlCommand(string sql)
{
MySqlCommand mySqlCommand = new MySqlCommand(sql, conn);
return mySqlCommand;
}

/// <summary>
/// DBクエリー
/// </summary>
/// <param name="selectSql">ｓｑｌ文</param>
/// <param name="field">DBテーブルのフィールド</param>
/// <returns></returns>
public string DBQuery(string selectSql, string field)
{
string ret = null;
MySqlCommand sqlCommand = GetSqlCommand(selectSql);
MySqlDataReader reader = null;
try
{
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
if (reader.HasRows)
{
ret = reader.GetString(field);
break;// データ1行だけ
}
}
}
catch (Exception ex)
{
log.WriteLog(LOG_TyPe.LOG_ERROE, "DBクエリー失敗");
log.WriteErrorLog(ex.Message);
}
finally
{
if(reader != null)
{
reader.Close();
}
sqlCommand.Dispose();
}
return ret;
}

/// <summary>
/// DBインサート
/// </summary>
/// <param name="insertSql">ｓｑｌ文</param>
/// <returns></returns>
public bool DBInsert(string insertSql)
{
bool ret = false;
MySqlCommand sqlCommand = GetSqlCommand(insertSql);
MySqlTransaction sqltran = conn.BeginTransaction();
sqlCommand.Transaction = sqltran;
try
{
sqlCommand.ExecuteNonQuery();
sqltran.Commit();
ret = true;
}
catch (Exception ex)
{
sqltran.Rollback();
log.WriteLog(LOG_TyPe.LOG_ERROE, "DBインサート失敗");
log.WriteErrorLog(ex.Message);
}
finally
{
sqlCommand.Dispose();
}
return ret;
}

/// <summary>
/// DB更新
/// </summary>
/// <param name="updateSql">ｓｑｌ文</param>
/// <returns></returns>
public bool DBUpdate(string updateSql)
{
bool ret = false;
MySqlCommand sqlCommand = GetSqlCommand(updateSql);
MySqlTransaction sqltran = conn.BeginTransaction();
sqlCommand.Transaction = sqltran;
try
{
sqlCommand.ExecuteNonQuery();
sqltran.Commit();
ret = true;
}
catch (Exception ex)
{
sqltran.Rollback();
log.WriteLog(LOG_TyPe.LOG_ERROE, "DB更新失敗");
log.WriteErrorLog(ex.Message);
}
finally
{
sqlCommand.Dispose();
}
return ret;
}