.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;

        }

修改信息

        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();
            }
        }

插入信息

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();
            }
        }

删除数据

        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();
            }
        }
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值