自用SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Windows;

namespace PaperTool.DAL
{
    class SqlHerlper
    {
        public static void DataTableToDB(DataTable dt, string tablename)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source = dbExcel.db"))
            {
                conn.Open();
                List<string> colNames = new List<string>();
                List<string> colNamesParameter = new List<string>();
                foreach (DataColumn col in dt.Columns)
                {
                    colNames.Add(col.ColumnName);
                    colNamesParameter.Add("@" + col.ColumnName);
                }
                string colNameStr = string.Join(@",", colNames);
                string colNameStrParameter = string.Join(@",", colNamesParameter);
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = string.Format("create  table {0} ({1})", tablename, colNameStr);
                    cmd.ExecuteNonQuery();
                }
                using (SQLiteTransaction tran = conn.BeginTransaction())
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        using (SQLiteCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = string.Format("insert into {0} values({1})", tablename, colNameStrParameter);
                            cmd.Transaction = tran;
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                cmd.Parameters.Add(new SQLiteParameter(colNamesParameter[i], (object)row[i]));
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                }
                
            }
        
        }

        public static DataTable Querry(string sql,params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source=dbExcel.db"))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    DataSet ds = new DataSet();
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }

        public static void Reset()
        {
            DataTable dt = Querry(@"select name from sqlite_master where type = 'table'");
            foreach (DataRow row in dt.Rows)
            {
                ExecuteNonQuerry("drop table " + row["name"].ToString());
            }
        }

        public static void Reset(string tablename)
        {
            object count = ExecuteScalar("select count(*) from sqlite_master where type = 'table' and name =@name", new SQLiteParameter[] { new SQLiteParameter("@name", tablename) });
            int cnt = Convert.ToInt32(count);
            if (cnt <= 0)
            {
                return;
            }
            else
            {
                ExecuteNonQuerry("drop table " + tablename);
            }
        }

        public static int ExecuteNonQuerry(string sql,params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source = dbExcel.db"))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source= dbExcel.db"))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static List<string> TableNames()
        {
            DataTable dtTableName = SqlHerlper.Querry("select name from sqlite_master where type='table'");
            List<string> list = new List<string>();
            foreach (DataRow row in dtTableName.Rows)
            {
                list.Add(row["name"].ToString());
            }
            return list;
        }
    }
}