子曰:工欲善其事,必先利其器
github:https://github.com/redAntCpp/CSharpTools
注意:以下工具用到了之前介绍到的日志类 ,详见 私人工具集2——C#日志操作类(LogHelper)
常用数据库简介
数据库操作是每一个后端都绕不过的话题,数据库种类复杂,常用的有oracle、sqlserver,mysql,sqlite。
个人经验来说:
- oracle通常用于大型,业务复杂,逻辑严谨的大型综合系统,如银行系统,医院的HIS系统。
- sqlserver适用性与oracle差不多
- mysql通常用于小型的web应用
- sqlite更多是作为一个本地数据库使用。
个人的开发习惯来讲,sqlite还有一个非常好的妙用,那就是用来存放数据日志,比如接口调用的日志,或者一些本地运行的日志。存放在本地。这样有时候比打文本Log要方便的多,在日志分析,问题排查的时候就显得好用。同时,可以创建定时任务定期清除里面的内容,不至于日志空间太大。
常见的数据库操作
常见的数据库操作,基本是增删查改,对应操作,insert
、delete
、select
、update
。有时候数据库已经写好了某个功能的语句(存储过程),我们需要的只是调用它。从操作上来看,只有select操作没有对数据库进行修改,是一种只读状态。
于是对数据库的操作进行归纳,可以大体归为以下几种:
- buildConnetionString,创建连接字符串,不同数据库的连接字符串一般都不一样。
- IsConnect,判断数据库是否成功连接。
- SelectData,从数据库中选择数据,以DataSet为返回类型。
- ChangeData,修改数据库的数据,以影响行数作为返回,可以插入,删除,更新数据。
- ExecSelectStoredProcedure,执行存储过程,存储过程以DataSet为返回类型。
- ExecChangeStoredProcedure,执行存储过程,以事务的方式修改数据库。
下面以SQL SERVER为例,介绍上述几个重要方法的实现。
引入工具集
SQL SERVER 系统自带工具集,无需下载。直接引入
using System.Data;
using System.Data.SqlClient;
buildConnetionString
public void buildConnetionString(string DataSource, string InitialCatalog, string UserID, string Password)
{
string connetStr = $"Data Source = {DataSource};"
+ $"Initial Catalog = {InitialCatalog};"
+ $"User ID = {UserID};"
+ $"Password = {Password};";
ConnetionString = connetStr;
}
IsConnect
public bool IsConnect()
{
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
try
{
conn.Open();
Log.AddTrack("连接成功!", "123");
return true;
}
catch (SqlException ex)
{
Log.AddTrack("连接失败!", ex.Message);
return false;
}
finally
{
conn.Close();
}
}
}
SelectData
从数据库中选择数据,并返回DataSet。如果需要防注入,可以使用重载函数中的参数化(关于sql注入,可以百度百科)
/// <summary>
/// 连接数据库,并选择数据,显示在dataset
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="ConnetStr">连接字符串</param>
/// <returns></returns>
public DataSet SelectData(string sqlStr)
{
Log.AddInfo("HOTApi.Lib.SqlHelper.SelectData", "Begin");
try
{
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
SqlDataAdapter sda = new SqlDataAdapter(sqlStr, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
Log.AddInfo("HOTApi.Lib.SqlHelper.SelectData", "End");
return ds;
}
}
catch (Exception SqlEx)
{
Log.AddError("HOTApi.Lib.SqlHelper.SelectData", "执行Sql异常:" + SqlEx.Message);
throw SqlEx;
}
}
/// <summary>
/// 连接数据库,并选择数据,显示在dataset
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="ConnetStr">连接字符串</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public DataSet selectData(string sqlStr, params SqlParameter[] parameters)
{
Log.AddTrack("HOTApi.Lib.selectData", "Begin");
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
conn.Open();
DataSet ds = new DataSet();
using (SqlCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = sqlStr;
cmd.Parameters.AddRange(parameters);
Log.AddTrack("执行sql:", cmd.CommandText.ToString());
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);//将适配器内容填充到dataset
Log.AddTrack("HOTApi.Lib.selectData", "End");
return ds;
}
catch (SqlException sqlex)
{
Log.AddError("执行sql出错:", sqlex.Message);
Log.AddTrack("HOTApi.Lib.selectData", "End");
throw sqlex;
}
}
}
ChangeData
修改数据库中的数据。
/// <summary>
/// 从数据库中修改数据(含增、删、查、改),可重载使用参数化
/// </summary>
/// <returns>受影响的行数</returns>
public int ChangeData(string sqlStr)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlStr;
int i = cmd.ExecuteNonQuery();
return i;
}
}
catch (SqlException SqlEx)
{
throw SqlEx;
}
}
/// <summary>
/// 重载函数:
/// 修改数据库数据,采用参数化执行,示例如下
/// SqlParameter[] par = {new SqlParameter("@n", name),//关联
/// new SqlParameter("@pwd", pwd)
/// };
///string sql = "select count(*) from dbo.student where studentno = "+ name +" and loginpwd = '"+pwd +"'";
///SqlCommand cmd = new SqlCommand(sql, con);
///cmd.Parameters.AddRange(par);//添加参数化数组到cmd中
/// </summary>
/// <param name="sqlStr">需要执行的sql语句,含参</param>
/// <param name="ConnetStr">连接字符串</param>
/// <param name="parameters">参数化数组</param>
/// <returns></returns>
public int ChangeData(string sqlStr, params SqlParameter[] parameters)
{
Log.AddTrack("HOTApi.Lib.SqlServerHelper.changeData", "Begin");
try
{
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlStr, conn);//conn.CreateCommand();
cmd.Parameters.AddRange(parameters);添加参数化数组到cmd中
Log.AddTrack("HOTApi.Lib.SqlServerHelper.changeData", "执行sql:" + cmd.CommandText);
int i = cmd.ExecuteNonQuery();
Log.AddTrack("HOTApi.Lib.SqlServerHelper.changeData", "End");
return i;
}
}
catch (SqlException SqlEx)
{
Log.AddError("HOTApi.Lib.SqlServerHelper.changeData", "执行Sql修改异常:" + SqlEx.Message);
throw SqlEx;
}
}
ExecChangeStoredProcedure
执行存储过程,存储过程需要修改数据库。
/// <summary>
/// 执行存储过程,使用事务处理,若只要有一条不通过全部回滚,避免脏数据的产生
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">参数数组,参考用例见上</param>
/// <returns></returns>
public int ExecChangeStoredProcedure(string procName, params SqlParameter[] parameters)
{
int rows = 0;
using (SqlConnection conn = new SqlConnection(ConnetionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//开始事务
SqlTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
rows = cmd.ExecuteNonQuery();
transaction.Commit(); //当上述执行没有发生异常时,提交事务
return rows;
}
catch (SqlException sqlex)
{
transaction.Rollback();//事务执行异常,则回滚并抛出异常
throw sqlex;
}
}
}
}
更多其他相关操作类,参考本人github。
其他数据库的操作
其实,上述的操作是一个通用操作。只是引用的工具集不一样,在vs中都可以通过nuget获取。下面做一个简单介绍:
mysql
- 通过nuget下载工具集
- 引入工具集
using System.Data;
- 将上述代码中的 Sql 替换成MySql即可。
Oracle
-
通过nuget下载工具集
-
引入工具集
using Oracle.ManagedDataAccess.Client;
-
将上述代码中的Sql 替换成Oracle即可。
SQLite
- 通过nuget下载工具集
- 引入工具集```using System.Data.SQLite;``
- 将上述代码中的Sql 替换成SQLite即可。
注意,SQLite通常从本地连接数据库,因此我们的buildConnetionString是本地数据库的路径。
使用示例
//创建SqlServerHelper实例
readonly SqlServerHelper ssh = new SqlServerHelper();
//设置连接字符串
ssh.ConnetionString = HOTConfig.GetConfig().GetHOTConnetionString();//这里我是获取的配置,也可以直接写字符串。
//sql语句
string sqlstr = "select Userid,PassWord,UserName,deptName from T_user where UserNo = @UserNo and status = 0";
//参数化
SqlParameter[] par = { new SqlParameter("@UserNo", UserNo) };
//调用SelectData方法获取数据
DataSet qry = ssh.SelectData(sqlstr, par);