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 }
