CREATE TABLE demo_test (
tid INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT (0),
title TEXT,
notes TEXT,
dateline INTEGER
);
using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Data;
namespace SqliteDemoManager
{
// 记录数据类
public class RecordData
{
public long Tid { get; set; }
public string Title { get; set; }
public string Notes { get; set; }
public long Dateline { get; set; }
}
public class Form1 : Form
{
// 配置
private const string TABLE_NAME = "demo_test";
private const int PAGE_SIZE = 10;
private const string DB_FILE = "data.db";
// 使用 WAL 模式提升并发性能
private const string CONN_STR = $"Data Source={DB_FILE};Version=3;Journal Mode=WAL;Pooling=true;";
private int currentPage = 1;
private int totalPages = 1;
private int totalRecords = 0;
// 控件
private DataGridView dgvData;
private Button btnFirst, btnPrev, btnNext, btnLast, btnAdd, btnEdit, btnDelete, btnRefresh;
private Label lblPageInfo;
private NumericUpDown nudJumpPage;
private Button btnJump;
private MenuStrip menuStrip;
private ToolStripMenuItem menuAdd, menuEdit, menuDelete, menuRefresh, menuView;
public Form1()
{
InitializeDatabase();
InitializeManualComponents();
LoadData();
}
private void InitializeDatabase()
{
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string createTableSql = $@"
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
tid INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
notes TEXT NOT NULL,
dateline INTEGER NOT NULL DEFAULT 0
)";
using (var cmd = new SQLiteCommand(createTableSql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
private void InitializeManualComponents()
{
this.Text = "Demo_Test 数据管理 (SQLite)";
this.Size = new Size(1000, 600);
this.StartPosition = FormStartPosition.CenterScreen;
this.BackColor = SystemColors.Window; // 白色背景
// 菜单栏
menuStrip = new MenuStrip();
menuAdd = new ToolStripMenuItem("新增");
menuEdit = new ToolStripMenuItem("编辑");
menuDelete = new ToolStripMenuItem("删除");
menuRefresh = new ToolStripMenuItem("刷新");
menuView = new ToolStripMenuItem("显示");
menuAdd.Click += (s, e) => OpenEditWindow(false, null);
menuEdit.Click += (s, e) => EditRecord();
menuDelete.Click += (s, e) => DeleteRecord();
menuRefresh.Click += (s, e) => LoadData();
menuView.Click += (s, e) => ViewRecord();
menuStrip.Items.AddRange(new ToolStripItem[] { menuAdd, menuEdit, menuDelete, menuRefresh, menuView });
this.MainMenuStrip = menuStrip;
this.Controls.Add(menuStrip);
// DataGridView 样式
dgvData = new DataGridView
{
Dock = DockStyle.Fill,
AllowUserToAddRows = false,
AllowUserToDeleteRows = false,
ReadOnly = true,
SelectionMode = DataGridViewSelectionMode.FullRowSelect,
MultiSelect = false,
AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
BackgroundColor = Color.White, // 白色背景
BorderStyle = BorderStyle.Fixed3D,
GridColor = SystemColors.ControlLight,
RowHeadersVisible = false
};
// 设置交替行颜色（浅灰色，但仍以白色为主）
dgvData.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(240, 240, 240);
dgvData.DefaultCellStyle.BackColor = Color.White;
dgvData.CellDoubleClick += (s, e) => ViewRecord();
this.Controls.Add(dgvData);
// 底部按钮面板
Panel bottomPanel = new Panel { Dock = DockStyle.Bottom, Height = 45, BackColor = SystemColors.Control };
btnFirst = new Button { Text = "首页", Width = 60 };
btnPrev = new Button { Text = "上一页", Width = 60 };
lblPageInfo = new Label { Text = "第1页/共1页", Width = 120, TextAlign = ContentAlignment.MiddleCenter };
btnNext = new Button { Text = "下一页", Width = 60 };
btnLast = new Button { Text = "末页", Width = 60 };
nudJumpPage = new NumericUpDown { Width = 60, Minimum = 1, DecimalPlaces = 0, ThousandsSeparator = false };
btnJump = new Button { Text = "跳转", Width = 50 };
btnAdd = new Button { Text = "新增", Width = 60 };
btnEdit = new Button { Text = "编辑", Width = 60 };
btnDelete = new Button { Text = "删除", Width = 60 };
btnRefresh = new Button { Text = "刷新", Width = 60 };
btnFirst.Click += (s, e) => { currentPage = 1; LoadData(); };
btnPrev.Click += (s, e) => { if (currentPage > 1) { currentPage--; LoadData(); } };
btnNext.Click += (s, e) => { if (currentPage < totalPages) { currentPage++; LoadData(); } };
btnLast.Click += (s, e) => { currentPage = totalPages; LoadData(); };
btnJump.Click += (s, e) => { int p = (int)nudJumpPage.Value; if (p >= 1 && p <= totalPages) { currentPage = p; LoadData(); } };
btnAdd.Click += (s, e) => OpenEditWindow(false, null);
btnEdit.Click += (s, e) => EditRecord();
btnDelete.Click += (s, e) => DeleteRecord();
btnRefresh.Click += (s, e) => LoadData();
bottomPanel.Controls.Add(btnFirst);
bottomPanel.Controls.Add(btnPrev);
bottomPanel.Controls.Add(lblPageInfo);
bottomPanel.Controls.Add(btnNext);
bottomPanel.Controls.Add(btnLast);
bottomPanel.Controls.Add(nudJumpPage);
bottomPanel.Controls.Add(btnJump);
bottomPanel.Controls.Add(btnAdd);
bottomPanel.Controls.Add(btnEdit);
bottomPanel.Controls.Add(btnDelete);
bottomPanel.Controls.Add(btnRefresh);
int x = 10;
foreach (Control c in bottomPanel.Controls)
{
c.Location = new Point(x, 10);
x += c.Width + 5;
}
this.Controls.Add(bottomPanel);
menuStrip.Dock = DockStyle.Top;
dgvData.Top = menuStrip.Height;
dgvData.Height = this.ClientSize.Height - menuStrip.Height - bottomPanel.Height;
}
private int GetTotalCount()
{
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string sql = $"SELECT COUNT(*) FROM {TABLE_NAME}";
using (var cmd = new SQLiteCommand(sql, conn))
{
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
}
private void LoadData()
{
try
{
totalRecords = GetTotalCount();
totalPages = (int)Math.Ceiling((double)totalRecords / PAGE_SIZE);
if (totalPages == 0) totalPages = 1;
if (currentPage > totalPages) currentPage = totalPages;
if (currentPage < 1) currentPage = 1;
int offset = (currentPage - 1) * PAGE_SIZE;
DataTable dt = new DataTable();
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string sql = $"SELECT tid, title, notes, dateline FROM {TABLE_NAME} ORDER BY tid DESC LIMIT @limit OFFSET @offset";
using (var cmd = new SQLiteCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@limit", PAGE_SIZE);
cmd.Parameters.AddWithValue("@offset", offset);
using (var adapter = new SQLiteDataAdapter(cmd))
{
adapter.Fill(dt);
}
}
}
// 转换时间戳
if (dt.Columns.Contains("dateline"))
{
dt.Columns.Add("日期", typeof(string));
foreach (DataRow row in dt.Rows)
{
long unix = Convert.ToInt64(row["dateline"]);
DateTime dtTime = DateTimeOffset.FromUnixTimeSeconds(unix).LocalDateTime;
row["日期"] = dtTime.ToString("yyyy-MM-dd");
}
dt.Columns.Remove("dateline");
}
dgvData.DataSource = dt;
if (dgvData.Columns.Count > 0)
{
dgvData.Columns["tid"].HeaderText = "序号";
dgvData.Columns["title"].HeaderText = "标题";
dgvData.Columns["notes"].HeaderText = "内容";
dgvData.Columns["日期"].HeaderText = "日期";
}
lblPageInfo.Text = $"第{currentPage}页/共{totalPages}页";
nudJumpPage.Maximum = totalPages;
nudJumpPage.Value = currentPage;
}
catch (Exception ex)
{
MessageBox.Show($"加载数据失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private int? GetSelectedTid()
{
if (dgvData.SelectedRows.Count == 0)
{
MessageBox.Show("请先选择一条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return null;
}
object val = dgvData.SelectedRows[0].Cells["tid"].Value;
if (val == null || val == DBNull.Value) return null;
return Convert.ToInt32(val);
}
private void EditRecord()
{
int? tid = GetSelectedTid();
if (tid == null) return;
try
{
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string sql = $"SELECT tid, title, notes, dateline FROM {TABLE_NAME} WHERE tid=@tid";
using (var cmd = new SQLiteCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@tid", tid.Value);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
RecordData record = new RecordData
{
Tid = Convert.ToInt64(reader["tid"]),
Title = reader["title"].ToString(),
Notes = reader["notes"].ToString(),
Dateline = Convert.ToInt64(reader["dateline"])
};
OpenEditWindow(true, record);
}
else
{
MessageBox.Show("记录不存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show($"读取记录失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void OpenEditWindow(bool isEdit, RecordData record)
{
Form editForm = new Form();
editForm.Text = isEdit ? "编辑记录" : "新增记录";
editForm.Size = new Size(500, 400);
editForm.StartPosition = FormStartPosition.CenterParent;
editForm.FormBorderStyle = FormBorderStyle.FixedDialog;
editForm.MaximizeBox = false;
editForm.MinimizeBox = false;
editForm.BackColor = SystemColors.Window;
Label lblTitle = new Label { Text = "标题：", Location = new Point(20, 20), AutoSize = true };
TextBox txtTitle = new TextBox { Location = new Point(100, 18), Width = 300 };
Label lblNotes = new Label { Text = "内容：", Location = new Point(20, 60), AutoSize = true };
TextBox txtNotes = new TextBox { Location = new Point(100, 58), Width = 300, Height = 150, Multiline = true, ScrollBars = ScrollBars.Vertical };
Label lblDate = new Label { Text = "日期：", Location = new Point(20, 230), AutoSize = true };
DateTimePicker dtpDate = new DateTimePicker { Location = new Point(100, 228), Width = 150, Format = DateTimePickerFormat.Custom, CustomFormat = "yyyy-MM-dd" };
Button btnSave = new Button { Text = "保存", Location = new Point(100, 280), Width = 80 };
Button btnCancel = new Button { Text = "取消", Location = new Point(200, 280), Width = 80 };
if (isEdit && record != null)
{
txtTitle.Text = record.Title;
txtNotes.Text = record.Notes;
DateTime date = DateTimeOffset.FromUnixTimeSeconds(record.Dateline).LocalDateTime;
dtpDate.Value = date;
}
else
{
dtpDate.Value = DateTime.Now;
}
btnSave.Click += (s, e) =>
{
string title = txtTitle.Text.Trim();
string notes = txtNotes.Text.Trim();
if (string.IsNullOrEmpty(title) || string.IsNullOrEmpty(notes))
{
MessageBox.Show("标题和内容不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
long timestamp;
try
{
timestamp = ((DateTimeOffset)dtpDate.Value).ToUnixTimeSeconds();
}
catch (Exception ex)
{
MessageBox.Show($"日期转换错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
try
{
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
if (isEdit)
{
string updateSql = $"UPDATE {TABLE_NAME} SET title=@title, notes=@notes, dateline=@dateline WHERE tid=@tid";
using (var cmd = new SQLiteCommand(updateSql, conn))
{
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@notes", notes);
cmd.Parameters.AddWithValue("@dateline", timestamp);
cmd.Parameters.AddWithValue("@tid", record.Tid);
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
MessageBox.Show("更新成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
else
MessageBox.Show("未找到要更新的记录", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
string insertSql = $"INSERT INTO {TABLE_NAME} (title, notes, dateline) VALUES (@title, @notes, @dateline)";
using (var cmd = new SQLiteCommand(insertSql, conn))
{
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@notes", notes);
cmd.Parameters.AddWithValue("@dateline", timestamp);
cmd.ExecuteNonQuery();
MessageBox.Show("新增成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
editForm.Close();
LoadData();
}
catch (SQLiteException ex) when (ex.Message.Contains("locked"))
{
// 数据库锁定，重试一次
MessageBox.Show("数据库繁忙，请稍后重试", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
catch (Exception ex)
{
MessageBox.Show($"保存失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
};
btnCancel.Click += (s, e) => editForm.Close();
editForm.Controls.AddRange(new Control[] { lblTitle, txtTitle, lblNotes, txtNotes, lblDate, dtpDate, btnSave, btnCancel });
editForm.ShowDialog(this);
}
private void DeleteRecord()
{
int? tid = GetSelectedTid();
if (tid == null) return;
if (MessageBox.Show($"确定要删除序号为 {tid} 的记录吗？", "确认删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string sql = $"DELETE FROM {TABLE_NAME} WHERE tid=@tid";
using (var cmd = new SQLiteCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@tid", tid.Value);
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("删除成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadData();
}
catch (Exception ex)
{
MessageBox.Show($"删除失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void ViewRecord()
{
int? tid = GetSelectedTid();
if (tid == null) return;
using (var conn = new SQLiteConnection(CONN_STR))
{
conn.Open();
string sql = $"SELECT tid, title, notes, dateline FROM {TABLE_NAME} WHERE tid=@tid";
using (var cmd = new SQLiteCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@tid", tid.Value);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string title = reader["title"].ToString();
string notes = reader["notes"].ToString();
long unix = Convert.ToInt64(reader["dateline"]);
DateTime date = DateTimeOffset.FromUnixTimeSeconds(unix).LocalDateTime;
string dateStr = date.ToString("yyyy-MM-dd");
Form viewForm = new Form();
viewForm.Text = $"查看记录 - 序号 {tid}";
viewForm.Size = new Size(500, 450);
viewForm.StartPosition = FormStartPosition.CenterParent;
viewForm.FormBorderStyle = FormBorderStyle.FixedDialog;
viewForm.MaximizeBox = false;
viewForm.MinimizeBox = false;
viewForm.BackColor = SystemColors.Window;
Label lblTitle = new Label { Text = $"标题：{title}", Location = new Point(20, 20), AutoSize = true, Font = new Font("微软雅黑", 10, FontStyle.Bold) };
Label lblDate = new Label { Text = $"日期：{dateStr}", Location = new Point(20, 60), AutoSize = true };
Label lblNotesLabel = new Label { Text = "内容：", Location = new Point(20, 100), AutoSize = true, Font = new Font("微软雅黑", 10, FontStyle.Bold) };
TextBox txtNotes = new TextBox { Location = new Point(20, 130), Width = 440, Height = 220, Multiline = true, ReadOnly = true, ScrollBars = ScrollBars.Vertical, Text = notes };
Button btnClose = new Button { Text = "关闭", Location = new Point(200, 370), Width = 80 };
btnClose.Click += (s, e) => viewForm.Close();
viewForm.Controls.AddRange(new Control[] { lblTitle, lblDate, lblNotesLabel, txtNotes, btnClose });
viewForm.ShowDialog(this);
}
else
{
MessageBox.Show("记录不存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
}
}HACMS进销存管理系统V1.0(桌面版)采用的是Python+MySQL开发，开发初衷仅为自己所用，主要包括采购管理、采购退货管理、销售管理、销售退货管理、库存预警、库存报表、报盈管理、报损管理、流水导出等功能 ...
PHP is a popular general-purpose scripting language that is especially suited to web development. Fast, flexible and pragmatic, PHP powers everything from your blog to the most popular websites in the world.
CodeIgniter is a powerful PHP framework with a very small footprint, built for developers who need a simple and elegant toolkit to create full-featured web applications.