c#关于操作数据库(通用 | 此实例以sqlite举例)

   一、简易操作数据库方法

        /// <summary>
        /// 查询一张表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            SQLiteConnection con1 = new SQLiteConnection(con);
            SQLiteCommand cmd = new SQLiteCommand(sql, con1);
            con1.Open();
            SQLiteDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            SQLiteDataAdapter dat = new SQLiteDataAdapter(sql, con1);
            dat.Fill(dt);
            dr.Close();
            con1.Close();
            return dt;
        }
        /// <summary>
        /// 通过sql语句修改更新表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool SetDtRow(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            if (conn.State != ConnectionState.Open || conn.State == ConnectionState.Closed)
                conn.Open();
            SQLiteTransaction ts = conn.BeginTransaction();
            cmd.Transaction = ts;
            int a = cmd.ExecuteNonQuery();
            if (a > 0)
            {
                ts.Commit();
                conn.Close();
                return true;
            }
            else
            {
                ts.Rollback();
                conn.Close();
                return false;
            }
        }

二、通过表名得到表，以及保存修改整张表

(1).通过表名获取表

        /// <summary>
        /// 通过表名获取表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable GetTableByName(string tableName, SQLiteConnection conn, bool isEmpty = false)
        {
            DataTable dt = ExecuteDataSet(conn, tableName, isEmpty).Tables[0];
            dt.TableName = tableName;
            return dt;
        }


        public static DataSet ExecuteDataSet(SQLiteConnection conn, string tableName, bool isEmpty = false)
        {
            DataSet ds = new DataSet();
            SQLiteTransaction trans = null;
            SQLiteCommand cmd = new SQLiteCommand();
            string commandText = "select * from " + tableName + (isEmpty ? " where 1=0" : "");
            PrepareCommand(cmd, conn, ref trans, false, CommandType.Text, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }



        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="useTrans">是否使用事务</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">SQL Text</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (useTrans)
            {
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = trans;
            }


            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

(2).修改一张表(表的行状态RowState改变后)

        public static bool Update(SQLiteConnection conn, DataTable tb)
        {
            string sSql = "select * from " + tb.TableName;
            SQLiteTransaction trans = null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            SQLiteDataAdapter adp = new SQLiteDataAdapter(sSql, conn);
            SQLiteCommandBuilder cmb = new SQLiteCommandBuilder(adp);
            cmb.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                adp.Update(tb);
                trans.Commit();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("执行数据错出错：", ex);
                trans.Rollback();
                return false;
            }
            finally
            {
                cmb.Dispose();
                adp.Dispose();

                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

三、sqlhelper公共类

using System;
using System.Data;
using System.Data.SQLite;

namespace Demo
{
    /// <summary>
    /// SQL公共方法
    /// </summary>
    static class SQLHelper
    {

       public static SQLiteConnection  conn =new SQLiteConnection(@"Data Source=C:\Users\Mark\Desktop\new9-4路桥.lqcb;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10");

        /// <summary>
        /// 通过表名获取表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable GetTableByName(string tableName, SQLiteConnection conn, bool isEmpty = false)
        {
            DataTable dt = ExecuteDataSet(conn, tableName, isEmpty).Tables[0];
            dt.TableName = tableName;
            return dt;
        }


        public static DataSet ExecuteDataSet(SQLiteConnection conn, string tableName, bool isEmpty = false)
        {
            DataSet ds = new DataSet();
            SQLiteTransaction trans = null;
            SQLiteCommand cmd = new SQLiteCommand();
            string commandText = "select * from " + tableName + (isEmpty ? " where 1=0" : "");
            PrepareCommand(cmd, conn, ref trans, false, CommandType.Text, commandText);
            try
            {
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(ds, tableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="useTrans">是否使用事务</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">SQL Text</param>
        /// <param name="cmdParms">SQLiteParameters to use in the command</param>
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (useTrans)
            {
                trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = trans;
            }


            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 通过改变表的行状态来修改整张表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static bool Update(SQLiteConnection conn, DataTable tb)
        {
            string sSql = "select * from " + tb.TableName;
            SQLiteTransaction trans = null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            SQLiteDataAdapter adp = new SQLiteDataAdapter(sSql, conn);
            SQLiteCommandBuilder cmb = new SQLiteCommandBuilder(adp);
            cmb.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                adp.Update(tb);
                trans.Commit();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("执行数据错出错：", ex);
                trans.Rollback();
                return false;
            }
            finally
            {
                cmb.Dispose();
                adp.Dispose();

                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 通过sql语句修改更新表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool SetDtRow(string sql)
        {
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            if (conn.State != ConnectionState.Open || conn.State == ConnectionState.Closed)
                conn.Open();
            SQLiteTransaction ts = conn.BeginTransaction();
            cmd.Transaction = ts;
            int a = cmd.ExecuteNonQuery();
            if (a > 0)
            {
                ts.Commit();
                conn.Close();
                return true;
            }
            else
            {
                ts.Rollback();
                conn.Close();
                return false;
            }
        }
    }
}