SqlHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AiVoice.DBUtility
{
public class SqlHelper
{
//需要添加引用--框架，System.Configuration，添加再using，用来读取配置文件的数据库链接字符串
private static string conStr = ConfigurationManager.ConnectionStrings["SQLiteDB"].ConnectionString;
//private static string conStr = "Data Source=AiVoiceRobot.pc.db;Version=3";//Data Source=filename;Version=3;
/// <summary>
/// 增删改受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdType">执行类型</param>
/// <param name="pms">Sql语句的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, CommandType cmdType, params SQLiteParameter[] pms)
{
using (SQLiteConnection con = new SQLiteConnection(conStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回结果是查询后的第一行第一列，如果执行的SQL语句不是一个查询语句，则会返回一个未实例化的对象，必须通过类型装换来显示
/// 查询单个结果，一般和聚合函数 一起使用
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdType">执行类型</param>
/// <param name="pms">Sql语句的参数</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType cmdType, params SQLiteParameter[] pms)
{
using (SQLiteConnection con = new SQLiteConnection(conStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 数据查询操作,查询多行
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string sql, CommandType cmdType, params SQLiteParameter[] pms)
{
SQLiteConnection con = new SQLiteConnection(conStr);
using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
try
{
con.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}
/// <summary>
/// 返回DataTable数据
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SQLiteParameter[] pms)
{
DataTable dt = new DataTable();
using (SQLiteDataAdapter adpt = new SQLiteDataAdapter(sql, conStr))
{
adpt.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adpt.SelectCommand.Parameters.AddRange(pms);
}
adpt.Fill(dt);
}
return dt;
}
}
}

 

 

 

 

 

 

app.config

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>

<log4net>
<!-- Define some output appenders -->
<appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="NewApp.log"/>
<appendToFile value="true"/>
<!--日志文件最大个数，分别为test.txt1,test.txt1...test.txt10，当10满了之后将向test.txt1中重写内容-->
<maxSizeRollBackups value="10"/>
<!--日志文件最大1M：如果超过1M，则将原有文件改名，并创建新文件test.txt-->
<maximumFileSize value="1024KB"/>
<rollingStyle value="Size"/>
<staticLogFileName value="true"/>
<layout type="log4net.Layout.PatternLayout">
<!--<conversionPattern value="%date [%thread] %-5level %logger - %message%newline"/>-->
<param name="ConversionPattern" value="[%d][%t][%p][%c:%line]%message%newline" />
</layout>
</appender>
<root>
<!--配置日志的级别，低于此级别的就不写到日志里面去-->
<level value="All"/>
<appender-ref ref="LogFileAppender"/>
</root>
</log4net>

<connectionStrings>
<!--填入数据库文件的路径即可，这里我们放在debug目录下，所有只需要直接写入文件名dbdemos.db3-->
<add name="SQLiteDB" connectionString="Data Source=AiVoiceRobot.pc.db;Version=3"/>
</connectionStrings>

<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
</configuration>