自用SqlHelper.cs

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data;
  6 using System.Data.SQLite;
  7 using System.Windows;
  8 
  9 namespace PaperTool.DAL
 10 {
 11     class SqlHerlper
 12     {
 13         public static void DataTableToDB(DataTable dt, string tablename)
 14         {
 15             using (SQLiteConnection conn = new SQLiteConnection("data source = dbExcel.db"))
 16             {
 17                 conn.Open();
 18                 List<string> colNames = new List<string>();
 19                 List<string> colNamesParameter = new List<string>();
 20                 foreach (DataColumn col in dt.Columns)
 21                 {
 22                     colNames.Add(col.ColumnName);
 23                     colNamesParameter.Add("@" + col.ColumnName);
 24                 }
 25                 string colNameStr = string.Join(@",", colNames);
 26                 string colNameStrParameter = string.Join(@",", colNamesParameter);
 27                 using (SQLiteCommand cmd = conn.CreateCommand())
 28                 {
 29                     cmd.CommandText = string.Format("create  table {0} ({1})", tablename, colNameStr);
 30                     cmd.ExecuteNonQuery();
 31                 }
 32                 using (SQLiteTransaction tran = conn.BeginTransaction())
 33                 {
 34                     foreach (DataRow row in dt.Rows)
 35                     {
 36                         using (SQLiteCommand cmd = conn.CreateCommand())
 37                         {
 38                             cmd.CommandText = string.Format("insert into {0} values({1})", tablename, colNameStrParameter);
 39                             cmd.Transaction = tran;
 40                             for (int i = 0; i < dt.Columns.Count; i++)
 41                             {
 42                                 cmd.Parameters.Add(new SQLiteParameter(colNamesParameter[i], (object)row[i]));
 43                             }
 44                             cmd.ExecuteNonQuery();
 45                         }
 46                     }
 47                     tran.Commit();
 48                 }
 49                 
 50             }
 51         
 52         }
 53 
 54         public static DataTable Querry(string sql,params SQLiteParameter[] parameters)
 55         {
 56             using (SQLiteConnection conn = new SQLiteConnection("data source=dbExcel.db"))
 57             {
 58                 conn.Open();
 59                 using (SQLiteCommand cmd = conn.CreateCommand())
 60                 {
 61                     cmd.CommandText = sql;
 62                     cmd.Parameters.AddRange(parameters);
 63                     DataSet ds = new DataSet();
 64                     SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
 65                     adapter.Fill(ds);
 66                     return ds.Tables[0];
 67                 }
 68             }
 69         }
 70 
 71         public static void Reset()
 72         {
 73             DataTable dt = Querry(@"select name from sqlite_master where type = 'table'");
 74             foreach (DataRow row in dt.Rows)
 75             {
 76                 ExecuteNonQuerry("drop table " + row["name"].ToString());
 77             }
 78         }
 79 
 80         public static void Reset(string tablename)
 81         {
 82             object count = ExecuteScalar("select count(*) from sqlite_master where type = 'table' and name =@name", new SQLiteParameter[] { new SQLiteParameter("@name", tablename) });
 83             int cnt = Convert.ToInt32(count);
 84             if (cnt <= 0)
 85             {
 86                 return;
 87             }
 88             else
 89             {
 90                 ExecuteNonQuerry("drop table " + tablename);
 91             }
 92         }
 93 
 94         public static int ExecuteNonQuerry(string sql,params SQLiteParameter[] parameters)
 95         {
 96             using (SQLiteConnection conn = new SQLiteConnection("data source = dbExcel.db"))
 97             {
 98                 conn.Open();
 99                 using (SQLiteCommand cmd = conn.CreateCommand())
100                 {
101                     cmd.CommandText = sql;
102                     cmd.Parameters.AddRange(parameters);
103                     return cmd.ExecuteNonQuery();
104                 }
105             }
106         }
107 
108         public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
109         {
110             using (SQLiteConnection conn = new SQLiteConnection("data source= dbExcel.db"))
111             {
112                 conn.Open();
113                 using (SQLiteCommand cmd = conn.CreateCommand())
114                 {
115                     cmd.CommandText = sql;
116                     cmd.Parameters.AddRange(parameters);
117                     return cmd.ExecuteScalar();
118                 }
119             }
120         }
121 
122         public static List<string> TableNames()
123         {
124             DataTable dtTableName = SqlHerlper.Querry("select name from sqlite_master where type='table'");
125             List<string> list = new List<string>();
126             foreach (DataRow row in dtTableName.Rows)
127             {
128                 list.Add(row["name"].ToString());
129             }
130             return list;
131         }
132     }
133 }

 

转载于:https://www.cnblogs.com/bbcar/archive/2013/01/16/2862882.html