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