using System;
using System
.Collections
.Generic;
using System
.ComponentModel;
using System
.Data;
using System
.Drawing;
using System
.Linq;
using System
.Text;
using System
.Threading
.Tasks;
using System
.Windows
.Forms;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
private SQLiteConnection connection;
private const string dbFile = "knowledge
.db";
public Form1()
{
InitializeComponent();
InitializeDatabase();
LoadSubjects();
}
// 初始化数据库
private void InitializeDatabase()
{
if (!File
.Exists(dbFile))
{
SQLiteConnection
.CreateFile(dbFile);
}
connection = new SQLiteConnection($"Data Source={dbFile};Version=3;");
connection
.Open();
// 创建学科表
string subjectSql = @"CREATE TABLE IF NOT EXISTS Subjects (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL UNIQUE)";
// 创建知识点表
string knowledgeSql = @"CREATE TABLE IF NOT EXISTS KnowledgePoints (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
SubjectId INTEGER NOT NULL,
Title TEXT NOT NULL,
Description TEXT,
Image BLOB,
FOREIGN KEY(SubjectId) REFERENCES Subjects(Id))";
ExecuteNonQuery(subjectSql);
ExecuteNonQuery(knowledgeSql);
}
private void ExecuteNonQuery(string sql)
{
using (var cmd = new SQLiteCommand(sql, connection))
{
cmd
.ExecuteNonQuery();
}
}
// 加载所有学科
private void LoadSubjects()
{
cmbSubjects
.Items
.Clear();
string sql = "
SELECT Id, Name
FROM Subjects";
using (var cmd = new SQLiteCommand(sql, connection))
using (var reader = cmd
.ExecuteReader())
{
while (reader
.Read())
{
cmbSubjects
.Items
.Add(new Subject(
reader
.GetInt32(0),
reader
.GetString(1)
));
}
}
}
private void 添加新学科ToolStripMenuItem_Click(object sender, EventArgs e)
{
string subjectName = txtNewSubject
.Text
.Trim();
if (string
.IsNullOrEmpty(subjectName))
{
MessageBox
.Show("请输入学科名称");
return;
}
string sql = "
INSERT INTO Subjects (Name)
VALUES (@name)";
using (var cmd = new SQLiteCommand(sql, connection))
{
cmd
.Parameters
.AddWith
Value("@name", subjectName);
cmd
.ExecuteNonQuery();
}
txtNewSubject
.Clear();
LoadSubjects();
MessageBox
.Show("学科添加成功！");
}
private void 添加知识点ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (cmbSubjects
.SelectedItem == null)
{
MessageBox
.Show("请先选择学科");
return;
}
var subject = (Subject)cmbSubjects
.SelectedItem;
string title = txtTitle
.Text
.Trim();
string description = txtDescription
.Text;
if (string
.IsNullOrEmpty(title))
{
MessageBox
.Show("请输入知识点标题");
return;
}
// 处理图片
byte[] imageBytes = null;
if (picImage
.Image != null)
{
using (var ms = new MemoryStream())
{
picImage
.Image
.Save(ms, picImage
.Image
.RawFormat);
imageBytes = ms
.ToArray();
}
}
// 插入数据库
string sql = @"
INSERT INTO KnowledgePoints
(SubjectId, Title, Description, Image)
VALUES (@subjectId, @title, @desc, @image)";
using (var cmd = new SQLiteCommand(sql, connection))
{
cmd
.Parameters
.AddWith
Value("@subjectId", subject
.Id);
cmd
.Parameters
.AddWith
Value("@title", title);
cmd
.Parameters
.AddWith
Value("@desc", description);
cmd
.Parameters
.AddWith
Value("@image", imageBytes ?? (object)
DBNull.Value);
cmd
.ExecuteNonQuery();
}
ClearKnowledgeForm();
MessageBox
.Show("知识点添加成功！");
}
private void 上传图片ToolStripMenuItem_Click(object sender, EventArgs e)
{
using (OpenFileDialog dlg = new OpenFileDialog())
{
dlg
.Filter = "图片文件|*
.jpg;*
.jpeg;*
.png;*
.bmp";
if (dlg
.ShowDialog() == DialogResult
.OK)
{
picImage
.Image = new Bitmap(dlg
.FileName);
}
}
}
private void ClearKnowledgeForm()
{
txtTitle
.Clear();
txtDescription
.Clear();
picImage
.Image = null;
}
private void 查看知识点ToolStripMenuItem_Click(object sender, EventArgs e)
{
var viewForm = new KnowledgeViewer(connection);
viewForm
.ShowDialog();
}
}
public class Subject
{
public int Id { get; }
public string Name { get; }
public Subject(int id, string name)
{
Id = id;
Name = name;
}
public override string ToString() => Name;
}
}using System;
using System
.Collections
.Generic;
using System
.ComponentModel;
using System
.Data;
using System
.Drawing;
using System
.Linq;
using System
.Text;
using System
.Threading
.Tasks;
using System
.Windows
.Forms;
namespace KnowledgeViewer
{
public partial class Form1 : Form
{
private TreeView treeView;
private WebBrowser browser;
private SQLiteConnection connection;
public KnowledgeViewer(SQLiteConnection conn)
{
connection = conn;
InitializeUI();
LoadData();
}
private void InitializeUI()
{
this
.Size = new Size(800, 600);
this
.Text = "知识点浏览器";
SplitContainer split = new SplitContainer();
split
.Dock = DockStyle
.Fill;
split
.SplitterDistance = 200;
treeView = new TreeView();
treeView
.Dock = DockStyle
.Fill;
treeView
.After
Select += TreeView_After
Select;
browser = new WebBrowser();
browser
.Dock = DockStyle
.Fill;
split
.Panel1
.Controls
.Add(treeView);
split
.Panel2
.Controls
.Add(browser);
this
.Controls
.Add(split);
}
private void LoadData()
{
// 加载学科
string subjectSql = "
SELECT Id, Name
FROM Subjects";
using (var cmd = new SQLiteCommand(subjectSql, connection))
using (var reader = cmd
.ExecuteReader())
{
while (reader
.Read())
{
var subjectNode = new TreeNode(reader
.GetString(1))
{
Tag = $"Subject_{reader
.GetInt32(0)}"
};
treeView
.Nodes
.Add(subjectNode);
LoadKnowledgePoints(subjectNode, reader
.GetInt32(0));
}
}
}
private void LoadKnowledgePoints(TreeNode parentNode, int subjectId)
{
string sql = "
SELECT Id, Title
FROM KnowledgePoints WHERE SubjectId = @subjectId";
using (var cmd = new SQLiteCommand(sql, connection))
{
cmd
.Parameters
.AddWith
Value("@subjectId", subjectId);
using (var reader = cmd
.ExecuteReader())
{
while (reader
.Read())
{
var pointNode = new TreeNode(reader
.GetString(1))
{
Tag = $"Point_{reader
.GetInt32(0)}"
};
parentNode
.Nodes
.Add(pointNode);
}
}
}
}
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void treeView1_After
Select(object sender, TreeViewEventArgs e)
{
if (e
.Node
.Tag == null) return;
var parts = e
.Node
.Tag
.ToString()
.Split('_');
if (parts[0] == "Point")
{
int pointId = int
.Parse(parts[1]);
DisplayKnowledgePoint(pointId);
}
}
private void DisplayKnowledgePoint(int pointId)
{
string sql = "
SELECT Title, Description, Image
FROM KnowledgePoints WHERE Id = @id";
using (var cmd = new SQLiteCommand(sql, connection))
{
cmd
.Parameters
.AddWith
Value("@id", pointId);
using (var reader = cmd
.ExecuteReader())
{
if (reader
.Read())
{
string title = reader
.GetString(0);
string description = reader
.GetString(1);
byte[] imageBytes = reader
.Is
DBNull(2) ? null : (byte[])reader[2];
string html = $"<h1>{title}</h1><p>{description
.Replace("\n", "<br>")}</p>";
if (imageBytes != null)
{
string base64 = Convert
.ToBase64String(imageBytes);
html += $"<img src='data:image/png;base64,{base64}' style='max-width:100%'/>";
}
browser
.DocumentText = html;
}
}
}
}
}
怎么改
本文讨论了在使用SQL的INSERT INTO SELECT语句时，如果SELECT部分返回空结果集的情况。通常需要进行DBNull.Value检查来确保数据操作的正确性。了解如何在查询中适当地处理这种情况对于防止数据库异常至关重要。
扫一扫
884
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
