C#封装对SQL Server和SQLite数据库的操作类

应用程序配置文件存放数据库连接字符串的信息

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
  <connectionStrings>
    <!--SQL Server数据库连接字符串信息-->
    <add name="connectionStr" connectionString="server=localhost;uid=sa;pwd=YUNWEN0305;datasource=Student"/>
    <!--SQLite数据库连接字符串信息-->
    <add name="conStr" connectionString="data source=D:\项目工程文件夹\尚云网络项目开发\尚云网络\SQLiteData\Sudons.db;version=3"/>
  </connectionStrings>
</configuration>

SQL Server 2014.jpg

对SQL Server数据库操作方法的封装

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

namespace FuHello
{
    public static class sqlHelper
    {
        //获取数据库连接字符串
        public static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
        }

        #region 封装一个执行SQL返回受影响的行数
        public static int ExecuteNoQuery(string sqlText, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters.ToArray());
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion

        #region 封装一个执行SQL返回查询结果中第一行第一列的值
        public static object ExecuteScalar(string sqlText, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    con.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters.ToArray());
                    return cmd.ExecuteScalar();
                }
            }
        }
        #endregion

        #region 封装一个执行SQL返回一个DataTable
        public static DataTable GetDataTable(string sqlText, params SqlParameter[] parameters)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText, GetConnectionString()))
            {

                DataTable dt = new DataTable();
                adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
                adapter.Fill(dt);
                return dt;
            }
        }
        #endregion

        #region 封装一个执行SQL返回一个SqlDataReader
        public static SqlDataReader ExecutedReader(string sqlText, params SqlParameter[] parameters)
        {
            //SqlDataReader要求独占SqlConnection对象，并且SqlConnection必须是Open状态
            SqlConnection con = new SqlConnection(GetConnectionString());
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sqlText;
            cmd.Parameters.AddRange(parameters.ToArray());
            //SqlDataReader执行完成后顺便关闭数据库连接
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        #endregion
    }
}

SQLite.jpg

对SQLite数据库操作方法的封装

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

namespace FuHello
{
    public static class sqliteHelper
    {
        //从配置文本中读取连接字符串的方法封装
        public static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

        public static int ExecuteNoNQuery(string sql,params SQLiteParameter[] sParameters)
        {
            using (SQLiteConnection con = new SQLiteConnection(conStr))
            {
                con.Open();
                SQLiteCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(sParameters);
                return cmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string sql,params SQLiteParameter[] sParameters)
        {
            using (SQLiteConnection con = new SQLiteConnection(conStr))
            {
                con.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                cmd.Parameters.AddRange(sParameters);
                return cmd.ExecuteScalar();
            }
        }

        public static DataTable GetDataTable(string sql,params SQLiteParameter[] sParameters)
        {
            using (SQLiteConnection con = new SQLiteConnection(conStr))
            {
                con.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql,con);
                adapter.SelectCommand.Parameters.AddRange(sParameters);
                DataTable td = new DataTable();
                adapter.Fill(td);
                return td;
                
            }
        }

        public static SQLiteDataReader ExecuteReader(string sql,params SQLiteParameter[] sParameters)
        {
            SQLiteConnection con = new SQLiteConnection(conStr);
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            cmd.Parameters.AddRange(sParameters);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}