SQLite.Net使用入门(三)【增删改查】
发布时间:2020-12-12 19:27:27 所属栏目:百科 来源:网络整理
导读:生命之长短殊不重要,只要你活得快乐,在有生之年做些有意义的事,便已足够。 实体类: public class clsModel { protected SQLiteConnection conn; public clsModel (SQLiteConnection @conn) { this .conn = @conn; if ( this .conn != null this .conn.St
生命之长短殊不重要,只要你活得快乐,在有生之年做些有意义的事,便已足够。实体类:public class clsModel
{
protected SQLiteConnection conn;
public clsModel(SQLiteConnection @conn)
{
this.conn = @conn;
if (this.conn != null &&
this.conn.State.ToString() == "Closed")
{
conn.Open();
}
}
}
public class clsStudents : clsModel
{
public clsStudents(SQLiteConnection @conn) : base(@conn) { }
public DataTable getAllStudents()
{
DataTable dt = new DataTable();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM Students";
SQLiteDataAdapter db = new SQLiteDataAdapter(command);
db.Fill(dt);
return dt;
}
public DataTable getStudent(String @studentID)
{
DataTable dt = new DataTable();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM Students WHERE studentID = @studentID";
//为了防止SQL注入,所有的用户输入将进行参数设置。
command.Parameters.Add(new SQLiteParameter("@studentID",@studentID));
SQLiteDataAdapter db = new SQLiteDataAdapter(command);
db.Fill(dt);
return dt;
}
public void insertStudent(String @firstName,String @lastName)
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "INSERT into Students (firstName,lastName) VALUES(@firstName,@lastName)";
command.Parameters.Add(new SQLiteParameter("@firstName",@firstName));
command.Parameters.Add(new SQLiteParameter("@lastName",@lastName));
command.ExecuteNonQuery();
}
public void updateStudent(String @studentID,String @firstName,String @lastName)
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "UPDATE Students SET firstName = @firstName,lastName = @lastName WHERE studentID = @studentID";
command.Parameters.Add(new SQLiteParameter("@studentID",@studentID));
command.Parameters.Add(new SQLiteParameter("@firstName",@lastName));
command.ExecuteNonQuery();
}
public void deleteStudent(String @studentID)
{
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "DELETE FROM Students WHERE studentID = @studentID";
command.Parameters.Add(new SQLiteParameter("@studentID",@studentID));
command.ExecuteNonQuery();
}
}
Form1窗体后台代码:ublic partial class Form1 : Form
{
SQLiteConnection conn;
clsStudents students;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender,EventArgs e)
{
conn = new SQLiteConnection(@"data source = ....testdb.db");
students = new clsStudents(conn);
dataGridView1.DataSource = students.getAllStudents();
}
private void btnAdd_Click(object sender,EventArgs e)
{
new Form2(students).ShowDialog();
loadGridView();
}
private void btnEdit_Click(object sender,EventArgs e)
{
int index = dataGridView1.CurrentCell.RowIndex;
if (index > -1)
{
String studentID = dataGridView1.Rows[index].Cells["studentID"].Value.ToString();
new Form2(students,studentID).ShowDialog();
loadGridView();
}
}
private void btnDelete_Click(object sender,EventArgs e)
{
int index = dataGridView1.CurrentCell.RowIndex;
if (index > -1)
{
String studentID = dataGridView1.Rows[index].Cells["studentID"].Value.ToString();
var results = MessageBox.Show("你确定要删除此记录?","删除学生",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
if (results == DialogResult.Yes)
{
students.deleteStudent(studentID);
loadGridView();
}
}
}
private void loadGridView()
{
dataGridView1.DataSource = students.getAllStudents();
}
private void btnExit_Click(object sender,EventArgs e)
{
this.Close();
}
}
Form1窗体后台代码:public partial class Form2 : Form
{
clsStudents students;
String studentID = "";
//Add Mode
public Form2(clsStudents @students)
{
init(@students);
lblStudentID.Text = "新的学生实体";
}
//Edit Mode
public Form2(clsStudents @students,String studentID)
{
init(@students);
this.studentID = studentID;
lblStudentID.Text = this.studentID;
DataTable dt = students.getStudent(studentID);
txtFirstName.Text = dt.Rows[0]["firstName"].ToString();
txtLastName.Text = dt.Rows[0]["lastName"].ToString();
}
private void init(clsStudents students)
{
InitializeComponent();
this.students = students;
}
private void btnOK_Click(object sender,EventArgs e)
{
if (studentID == String.Empty)
{
//Add Mode
students.insertStudent(txtFirstName.Text.Trim(),txtLastName.Text.Trim());
}
else
{
//Edit Mode
students.updateStudent(this.studentID,txtFirstName.Text.Trim(),txtLastName.Text.Trim());
}
this.Close();
}
private void btnCancel_Click(object sender,EventArgs e)
{
this.Close();
}
}
运行结果如图:添加编辑删除(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |