.net操作SQLite数据库_.net获取sqlite数据库视图代码

1 查询数据库并存在DataTable

public DataTable queryDept(string filePath)
        {


            SQLiteConnection conn = new SQLiteConnection("Data Source=" + filePath + ";");
            DataTable dt = new DataTable();
            string sqlstr = "SELECT DISTINCT GUEST_DEPT_ID FROM GUEST_USER";
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            cmd.CommandText = sqlstr;
            cmd.CommandType = CommandType.Text;

            SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
            sda.SelectCommand = cmd;
            sda.SelectCommand.Connection = conn;
            sda.Fill(dt);

            return dt;

        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

修改信息

        public int updateGuestUser(GuestUser guestUser, string filePath)
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + filePath + ";");
            try
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UPDATE GUEST_USER SET USER_NAME=@USER_NAME,SEX=@SEX,GUEST_DEPT_ID=@GUEST_DEPT_ID,USER_CARD_NO=@USER_CARD_NO,REMARK=@REMARK,IC_CARD_NO=@IC_CARD_NO,USER_TEL=@USER_TEL,AUTH_START_DATE=@AUTH_START_DATE,AUTH_END_DATE=@AUTH_END_DATE WHERE GUEST_USER_ID=@GUEST_USER_ID";
                cmd.Parameters.Add(new SQLiteParameter("USER_NAME", guestUser.user_name));
                cmd.Parameters.Add(new SQLiteParameter("SEX", guestUser.sex));
                cmd.Parameters.Add(new SQLiteParameter("GUEST_DEPT_ID", guestUser.guest_dept_id));
                cmd.Parameters.Add(new SQLiteParameter("USER_CARD_NO", guestUser.user_card_no));
                cmd.Parameters.Add(new SQLiteParameter("REMARK", guestUser.remark));
                cmd.Parameters.Add(new SQLiteParameter("IC_CARD_NO", guestUser.ic_card_no));
                cmd.Parameters.Add(new SQLiteParameter("USER_TEL", guestUser.user_tel));
                cmd.Parameters.Add(new SQLiteParameter("AUTH_START_DATE", guestUser.auth_start_date));
                cmd.Parameters.Add(new SQLiteParameter("AUTH_END_DATE", guestUser.auth_end_date));

                cmd.Parameters.Add(new SQLiteParameter("GUEST_USER_ID", guestUser.guest_user_id));


                return cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }
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

插入信息

public int insertGuestUser(GuestUser guestUser, string filePath)
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + filePath + ";");
            try
            {
                string start_date = guestUser.auth_start_date.ToString("yyyy-MM-dd HH:mm:ss.fff");
                string end_date = guestUser.auth_end_date.ToString("yyyy-MM-dd HH:mm:ss.fff");
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO GUEST_USER(GUEST_USER_ID,GUEST_DEPT_ID,USER_NAME,HEAD_IMG,SEX,USER_CARD_NO,IC_CARD_NO,USER_TEL,AUTH_START_DATE,AUTH_END_DATE,PINYIN,USER_STATUS,REMARK) VALUES(@GUESR_USER_ID,@GUEST_DEPT_ID,@USER_NAME,@HEAD_IMG,@SEX,@USER_CARD_NO,@IC_CARD_NO,@USER_TEL,@AUTH_START_DATE,@AUTH_END_DATE,@PINYIN,@USER_STATUS,@REMARK)";
                cmd.Parameters.Add(new SQLiteParameter("GUESR_USER_ID", System.Guid.NewGuid().ToString()));
                cmd.Parameters.Add(new SQLiteParameter("GUEST_DEPT_ID", guestUser.guest_dept_id));
                cmd.Parameters.Add(new SQLiteParameter("USER_NAME", guestUser.user_name));
                cmd.Parameters.Add(new SQLiteParameter("HEAD_IMG", guestUser.head_img));
                cmd.Parameters.Add(new SQLiteParameter("SEX", guestUser.sex));
                cmd.Parameters.Add(new SQLiteParameter("USER_CARD_NO", guestUser.user_card_no));
                cmd.Parameters.Add(new SQLiteParameter("IC_CARD_NO", guestUser.ic_card_no));
                cmd.Parameters.Add(new SQLiteParameter("USER_TEL", guestUser.user_tel));
                cmd.Parameters.Add(new SQLiteParameter("AUTH_START_DATE", guestUser.auth_start_date));
                cmd.Parameters.Add(new SQLiteParameter("AUTH_END_DATE", guestUser.auth_end_date));
                cmd.Parameters.Add(new SQLiteParameter("PINYIN", guestUser.pinyin));
                cmd.Parameters.Add(new SQLiteParameter("USER_STATUS", guestUser.user_status));
                cmd.Parameters.Add(new SQLiteParameter("REMARK", guestUser.remark));
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }
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

删除数据

        public int deleteGuestUser(GuestUser guestUser, string filePath)
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + filePath + ";");
            try
            {
                conn.Open();
                SQLiteCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM  GUEST_USER WHERE GUEST_USER_ID=@GUEST_USER_ID";
                cmd.Parameters.Add(new SQLiteParameter("GUEST_USER_ID", guestUser.guest_user_id));
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20