弹来弹去跑马灯!

c# SQLite 判断表、字段是否存在的方法,新增、删除、重命名列

SQLiteHelper class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.Windows.Forms;
namespace TestSQLite
{
    public class SqliteHelper
    {
        //连接字符串
        private static string str ="Data source="+ AppDomain.CurrentDomain.BaseDirectory + "\\DATA.db";
 
        /// <summary>
        /// 增删改
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                    {
                        con.Open();
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
 
                        string sql2 = cmd.CommandText;
                        //con.Close();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException se)
            {
                return 0;
            }
        }
 
 
        /// <summary>
        /// 增删改
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                    {
                        con.Open();
                        string sql2 = cmd.CommandText;
                        con.Close();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException se)
            {
                return 0;
            }
        }
 
        /// <summary>
        /// 查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public static object ExecuteScalar(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(con))
                    {
                        con.Open();
                        cmd.CommandText = sql;
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch(Exception ex){
                   MessageBox.Show(ex.Message );
                  return null;
             
            }
 
 
        }
 
 
        /// <summary>
        /// 查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public static object ExecuteScalar(string sql, params SQLiteParameter[] param)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                {
                    con.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
 
                    return cmd.ExecuteScalar();
                }
            }
        }
 
        /// <summary>
        /// 多行查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>SQLiteDateReader</returns>
        public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] param)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    try
                    {
                        con.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        throw ex;
                    }
                }
            }
 
        }
 
        /// <summary>
        /// 查询多行数据
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>一个表</returns>
        public static DataTable ExecuteTable(string sql, params SQLiteParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
            {
                if (param != null)
                {
                    sda.SelectCommand.Parameters.AddRange(param);
                }
                sda.Fill(dt);
            }
            return dt;
        }
 
 
        /// <summary>
        /// 数据插入
        /// 20180725
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="insertData">需要插入的数据字典</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteInsert(string tbName, Dictionary<String, String> insertData)
        {
            string point = "";//分隔符号(,)
            string keyStr = "";//字段名拼接字符串
            string valueStr = "";//值的拼接字符串
 
            List<SQLiteParameter> param = new List<SQLiteParameter>();
            foreach (string key in insertData.Keys)
            {
                keyStr += string.Format("{0} `{1}`", point, key);
                valueStr += string.Format("{0} @{1}", point, key);
                param.Add(new SQLiteParameter("@" + key, insertData[key]));
                point = ",";
            }
            string sql = string.Format("INSERT INTO `{0}`({1}) VALUES({2})", tbName, keyStr, valueStr);
 
            //return sql;
            return ExecuteNonQuery(sql, param.ToArray());
 
        }
 
        /// <summary>
        /// 执行Update语句
        /// 20180725
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="where">更新条件:id=1</param>
        /// <param name="insertData">需要更新的数据</param>
        /// <returns>受影响行数</returns>
        public static int ExecuteUpdate(string tbName, string where, Dictionary<String, String> insertData)
        {
            string point = "";//分隔符号(,)
            string kvStr = "";//键值对拼接字符串(Id=@Id)
 
            List<SQLiteParameter> param = new List<SQLiteParameter>();
            foreach (string key in insertData.Keys)
            {
                kvStr += string.Format("{0} {1}=@{2}", point, key, key);
                param.Add(new SQLiteParameter("@" + key, insertData[key]));
                point = ",";
            }
            string sql = string.Format("UPDATE `{0}` SET {1} WHERE {2}", tbName, kvStr, where);
 
            return ExecuteNonQuery(sql, param.ToArray());
 
        }
 
 
 
 
 
 
        /// <summary>
        /// 查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public static DataTable  GetDataTable(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql , con))
                    {
                        con.Open(); 
                        SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
                        DataTable tb = new DataTable();
                        ad.Fill(tb);
                        con.Close();
                        return tb;
                     
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
 
            }
 
 
        }
 
 
 
 
 
 
    }
}

  

 

Test:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//see one value where .........
         MessageBox.Show("" + SqliteHelper.ExecuteScalar("select uid from members where uid<>''"));
         //see sql create syntax
         DataTable tb = SqliteHelper.GetDataTable("select  * from sqlite_master where name = 'members'");
         MessageBox.Show("" + tb.Rows[0]["sql"]);
         //or:
         //string syntax = "" + SqliteHelper.ExecuteScalar("select  sql from sqlite_master where name = 'members'");
         //MessageBox.Show(syntax);
 
         //see if column exists in table 'members'
         bool hasCol = int.Parse("" + SqliteHelper.ExecuteScalar("select count(name) from sqlite_master where name = 'members' and sql like '%sendAccount%'")) > 0;
         if (!hasCol)
         {
             //add column: ALTER  TABLE   table-name ADD COLUMN  column-name column-type
             SqliteHelper.ExecuteScalar("alter table members add COLUMN sendAccount varchar(20)");
             //alter table student drop column name // 该行在SQlite中不能用,SQlite不支持drop
             //SqliteHelper.ExecuteScalar("alter table members drop sendAccount33");
 
         }
 
         //drop table if exists A
         string s = "" + SqliteHelper.ExecuteScalar("drop table if exists members2");
         MessageBox.Show(s);

  

 参考:https://blog.csdn.net/wuyou1336/article/details/53770799

posted @   wgscd  阅读(3484)  评论(2编辑  收藏  举报
点击右上角即可分享
微信分享提示