winform资料库
界面
2023年5月29日
最新版支持图片粘贴显示（使用RichTextBox，粘贴板，RichTextBox.Rtf 来实现
sql server 创建表
USE [test] GO /****** Object: Table [dbo].[tb_data] Script Date: 2022/4/6 15:06:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tb_data]( [Id] [int] IDENTITY(100,1) NOT NULL, [Title] [nvarchar](50) NOT NULL, [Content] [nvarchar](max) NULL, [CreateTime] [datetime] NULL, [Type] [nvarchar](20) NULL, CONSTRAINT [PK_tb_data] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
dapper 版本 1.50.2.0
form1.cs
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; using System.Data.SqlClient; using Dapper; namespace Win.DataLib { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { LoadList(); } tb_data param = new tb_data() { PageIndex = 1, PageSize = 15 }; //加载列表数据 void LoadList() { string sql = "select * from tb_data where 1=1 "; if (param.Title.NotNullOrEmtpy()) { sql += $"and Title like '%{param.Title}%'"; } if (param.Type.NotNullOrEmtpy()) { sql += $"and Type like '%{param.Type}%'"; } sql += " order by id desc"; var pageData = sql.Query(param); dataGridView1.Rows.Clear(); foreach (var item in pageData.Data) { dataGridView1.Rows.Add(new object[] { dataGridView1.RowCount+1, item.Id, item.Title, item.Type, item.Content, item.CreateTime, }); } //页数 label4.Text = label4.Tag.ToString().Format(param.PageIndex,(int)Math.Ceiling( pageData.TotalCount / 1.0 / param.PageSize), pageData.TotalCount); } //搜索 private void button1_Click(object sender, EventArgs e) { param.Title = textBox1.Text.Trim(); param.Type = textBox5.Text.Trim(); param.PageIndex = 1; LoadList(); } //重置搜索 private void button6_Click(object sender, EventArgs e) { param.Title = textBox1.Text=""; param.Type = textBox5.Text = ""; param.PageIndex = 1; LoadList(); } //上一页 private void button3_Click(object sender, EventArgs e) { if (param.PageIndex > 1) { param.PageIndex--; } LoadList(); } //下一页 private void button4_Click(object sender, EventArgs e) { param.PageIndex++; LoadList(); } //点击表格 private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e) { if (e.RowIndex < 0) { return; } var row = dataGridView1.Rows[e.RowIndex]; textBox3.Text = row.Cells["Title"].Value.ToString(); textBox4.Text = row.Cells["Type"].Value.ToString(); textBox2.Text = row.Cells["Content"].Value.ToString(); button2.Tag = row.Cells["Id"].Value.ToString(); } //保存 private void button2_Click(object sender, EventArgs e) { var entity = new tb_data { Content = textBox2.Text.Trim(), CreateTime = DateTime.Now, Title = textBox3.Text.Trim(), Type = textBox4.Text.Trim() }; if (entity.Title.IsNullOrEmtpy()|| entity.Type.IsNullOrEmtpy()) { MessageBox.Show("标题，类型，内容不能为空！"); return; } if (button2.Tag == null) { var sql = "insert tb_data values(@Title,@Content,@CreateTime,@Type)"; if (sql.Execute(entity) > 0) { MessageBox.Show("保存成功"); clearData(); } } else { entity.Id = int.Parse(button2.Tag.ToString()); var sql = "update tb_data set Title=@Title,Content=@Content,Type=@Type where id=@Id"; if (sql.Execute(entity) > 0) { MessageBox.Show("更新成功"); } } LoadList(); } //清除 private void button5_Click(object sender, EventArgs e) { clearData(); } private void clearData() { textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; button2.Tag = null; } } } //扩展函数 public static class Ex { static readonly string connString = "Data Source=.;Initial Catalog=test;Integrated Security=True"; public static PageData<T> Query<T>(this string sql, T query) where T : page { using (var conn = ConnInit()) {
//这里会查询出全部数据，然后才分页
var list = conn.Query<T>(sql, query) .Skip((query.PageIndex - 1) * query.PageSize).Take(query.PageSize).ToList(); return new PageData<T> { Data = list, TotalCount = conn.Query<T>(sql, query).Count() }; } }
//dapper没有分页,自己实现一个 public static PageData<T> Query<T>(this string sql, T query) where T : page { using (var conn = ConnInit()) { int skip = (query.PageIndex - 1) * query.PageSize; string orderBy =sql.Substring( sql.LastIndexOf("order by")); sql = sql.Replace(orderBy, ""); string pagingSql = $@"select top {query.PageSize} * from (select row_number() over(order by id desc) as _sort,* from({sql})t1)t2 where _sort > {skip} {orderBy} "; var list = conn.Query<T>(pagingSql, query).ToList(); return new PageData<T> { Data = list, TotalCount = conn.QueryFirst<int>(sql.Replace("*","count(1)"), query) }; } }
public static int Execute<T>(this string sql, T data) where T : page { using (var conn = ConnInit()) { return conn.Execute(sql, data); } } static SqlConnection ConnInit() { var conn = new SqlConnection(connString); //conn.Open(); return conn; } public static bool NotNullOrEmtpy(this string str) { return !string.IsNullOrEmpty(str); } public static bool IsNullOrEmtpy(this string str) { return string.IsNullOrEmpty(str); } public static string Format(this string str, params object[] args) { return string.Format(str, args); } } //数据库实体 public class tb_data : page { public int Id { get; set; } public string Title { get; set; } public string Content { get; set; } public DateTime? CreateTime { get; set; } public string Type { get; set; } } //分页数据模型 public class page { public int PageIndex { get; set; } public int PageSize { get; set; } } public class PageData<T> { public List<T> Data { get; set; } public int TotalCount { get; set; } }
form1.Designer.cs
namespace Win.DataLib { partial class Form1 { /// <summary> /// 必需的设计器变量。 /// </summary> private System.ComponentModel.IContainer components = null; /// <summary> /// 清理所有正在使用的资源。 /// </summary> /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows 窗体设计器生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要修改 /// 使用代码编辑器修改此方法的内容。 /// </summary> private void InitializeComponent() { System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1)); this.label1 = new System.Windows.Forms.Label(); this.textBox1 = new System.Windows.Forms.TextBox(); this.button1 = new System.Windows.Forms.Button(); this.dataGridView1 = new System.Windows.Forms.DataGridView(); this.Column1 = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.Id = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.Title = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.Type = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.Content = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.CreateTime = new System.Windows.Forms.DataGridViewTextBoxColumn(); this.textBox2 = new System.Windows.Forms.TextBox(); this.label2 = new System.Windows.Forms.Label(); this.textBox3 = new System.Windows.Forms.TextBox(); this.label3 = new System.Windows.Forms.Label(); this.textBox4 = new System.Windows.Forms.TextBox(); this.button2 = new System.Windows.Forms.Button(); this.button3 = new System.Windows.Forms.Button(); this.button4 = new System.Windows.Forms.Button(); this.label4 = new System.Windows.Forms.Label(); this.label5 = new System.Windows.Forms.Label(); this.textBox5 = new System.Windows.Forms.TextBox(); this.button5 = new System.Windows.Forms.Button(); this.button6 = new System.Windows.Forms.Button(); ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit(); this.SuspendLayout(); // // label1 // this.label1.AutoSize = true; this.label1.Location = new System.Drawing.Point(21, 46); this.label1.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0); this.label1.Name = "label1"; this.label1.Size = new System.Drawing.Size(40, 16); this.label1.TabIndex = 0; this.label1.Text = "标题"; // // textBox1 // this.textBox1.Location = new System.Drawing.Point(68, 43); this.textBox1.Name = "textBox1"; this.textBox1.Size = new System.Drawing.Size(252, 26); this.textBox1.TabIndex = 1; // // button1 // this.button1.Location = new System.Drawing.Point(326, 43); this.button1.Name = "button1"; this.button1.Size = new System.Drawing.Size(75, 26); this.button1.TabIndex = 2; this.button1.Text = "搜索"; this.button1.UseVisualStyleBackColor = true; this.button1.Click += new System.EventHandler(this.button1_Click); // // dataGridView1 // this.dataGridView1.AllowUserToAddRows = false; this.dataGridView1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) | System.Windows.Forms.AnchorStyles.Left))); this.dataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill; this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize; this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { this.Column1, this.Id, this.Title, this.Type, this.Content, this.CreateTime}); this.dataGridView1.Location = new System.Drawing.Point(3, 132); this.dataGridView1.Name = "dataGridView1"; this.dataGridView1.RowHeadersVisible = false; this.dataGridView1.RowTemplate.Height = 23; this.dataGridView1.Size = new System.Drawing.Size(546, 442); this.dataGridView1.TabIndex = 3; this.dataGridView1.CellClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dataGridView1_CellClick); // // Column1 // this.Column1.FillWeight = 50F; this.Column1.HeaderText = "序号"; this.Column1.Name = "Column1"; // // Id // this.Id.HeaderText = "id"; this.Id.Name = "Id"; this.Id.Visible = false; // // Title // this.Title.HeaderText = "标题"; this.Title.Name = "Title"; // // Type // this.Type.FillWeight = 60F; this.Type.HeaderText = "类型"; this.Type.Name = "Type"; // // Content // this.Content.HeaderText = "内容"; this.Content.Name = "Content"; // // CreateTime // this.CreateTime.HeaderText = "时间"; this.CreateTime.Name = "CreateTime"; // // textBox2 // this.textBox2.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) | System.Windows.Forms.AnchorStyles.Left) | System.Windows.Forms.AnchorStyles.Right))); this.textBox2.Location = new System.Drawing.Point(608, 176); this.textBox2.MaxLength = 132767; this.textBox2.Multiline = true; this.textBox2.Name = "textBox2"; this.textBox2.ScrollBars = System.Windows.Forms.ScrollBars.Vertical; this.textBox2.Size = new System.Drawing.Size(484, 398); this.textBox2.TabIndex = 4; // // label2 // this.label2.AutoSize = true; this.label2.Location = new System.Drawing.Point(605, 80); this.label2.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0); this.label2.Name = "label2"; this.label2.Size = new System.Drawing.Size(40, 16); this.label2.TabIndex = 0; this.label2.Text = "标题"; // // textBox3 // this.textBox3.Location = new System.Drawing.Point(652, 75); this.textBox3.Name = "textBox3"; this.textBox3.Size = new System.Drawing.Size(408, 26); this.textBox3.TabIndex = 1; // // label3 // this.label3.AutoSize = true; this.label3.Location = new System.Drawing.Point(605, 127); this.label3.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0); this.label3.Name = "label3"; this.label3.Size = new System.Drawing.Size(40, 16); this.label3.TabIndex = 0; this.label3.Text = "类型"; // // textBox4 // this.textBox4.Location = new System.Drawing.Point(652, 122); this.textBox4.Name = "textBox4"; this.textBox4.Size = new System.Drawing.Size(228, 26); this.textBox4.TabIndex = 1; // // button2 // this.button2.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right))); this.button2.Location = new System.Drawing.Point(910, 589); this.button2.Name = "button2"; this.button2.Size = new System.Drawing.Size(182, 26); this.button2.TabIndex = 2; this.button2.Text = "保存"; this.button2.UseVisualStyleBackColor = true; this.button2.Click += new System.EventHandler(this.button2_Click); // // button3 // this.button3.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left))); this.button3.Location = new System.Drawing.Point(347, 589); this.button3.Name = "button3"; this.button3.Size = new System.Drawing.Size(75, 26); this.button3.TabIndex = 2; this.button3.Text = "上一页"; this.button3.UseVisualStyleBackColor = true; this.button3.Click += new System.EventHandler(this.button3_Click); // // button4 // this.button4.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left))); this.button4.Location = new System.Drawing.Point(442, 589); this.button4.Name = "button4"; this.button4.Size = new System.Drawing.Size(75, 26); this.button4.TabIndex = 2; this.button4.Text = "下一页"; this.button4.UseVisualStyleBackColor = true; this.button4.Click += new System.EventHandler(this.button4_Click); // // label4 // this.label4.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left))); this.label4.AutoSize = true; this.label4.Location = new System.Drawing.Point(30, 594); this.label4.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0); this.label4.Name = "label4"; this.label4.Size = new System.Drawing.Size(160, 16); this.label4.TabIndex = 0; this.label4.Tag = "第{0}页，共{1}页,{2}条"; this.label4.Text = "第1页，共10页,100条"; // // label5 // this.label5.AutoSize = true; this.label5.Location = new System.Drawing.Point(21, 85); this.label5.Margin = new System.Windows.Forms.Padding(4, 0, 4, 0); this.label5.Name = "label5"; this.label5.Size = new System.Drawing.Size(40, 16); this.label5.TabIndex = 0; this.label5.Text = "类型"; // // textBox5 // this.textBox5.Location = new System.Drawing.Point(68, 80); this.textBox5.Name = "textBox5"; this.textBox5.Size = new System.Drawing.Size(162, 26); this.textBox5.TabIndex = 1; // // button5 // this.button5.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right))); this.button5.BackColor = System.Drawing.Color.LightCoral; this.button5.FlatStyle = System.Windows.Forms.FlatStyle.Popup; this.button5.Location = new System.Drawing.Point(722, 589); this.button5.Name = "button5"; this.button5.Size = new System.Drawing.Size(182, 26); this.button5.TabIndex = 2; this.button5.Text = "清除"; this.button5.UseVisualStyleBackColor = false; this.button5.Click += new System.EventHandler(this.button5_Click); // // button6 // this.button6.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(192)))), ((int)(((byte)(128))))); this.button6.FlatStyle = System.Windows.Forms.FlatStyle.Popup; this.button6.Location = new System.Drawing.Point(326, 80); this.button6.Name = "button6"; this.button6.Size = new System.Drawing.Size(75, 26); this.button6.TabIndex = 2; this.button6.Text = "重置"; this.button6.UseVisualStyleBackColor = false; this.button6.Click += new System.EventHandler(this.button6_Click); // // Form1 // this.AcceptButton = this.button1; this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(1124, 627); this.Controls.Add(this.textBox2); this.Controls.Add(this.dataGridView1); this.Controls.Add(this.button4); this.Controls.Add(this.button3); this.Controls.Add(this.button5); this.Controls.Add(this.button2); this.Controls.Add(this.button6); this.Controls.Add(this.button1); this.Controls.Add(this.textBox5); this.Controls.Add(this.textBox4); this.Controls.Add(this.label5); this.Controls.Add(this.textBox3); this.Controls.Add(this.label3); this.Controls.Add(this.textBox1); this.Controls.Add(this.label4); this.Controls.Add(this.label2); this.Controls.Add(this.label1); this.Font = new System.Drawing.Font("宋体", 12F); this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon"))); this.Margin = new System.Windows.Forms.Padding(4); this.Name = "Form1"; this.Text = "资料库"; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit(); this.ResumeLayout(false); this.PerformLayout(); } #endregion private System.Windows.Forms.Label label1; private System.Windows.Forms.TextBox textBox1; private System.Windows.Forms.Button button1; private System.Windows.Forms.DataGridView dataGridView1; private System.Windows.Forms.TextBox textBox2; private System.Windows.Forms.Label label2; private System.Windows.Forms.TextBox textBox3; private System.Windows.Forms.Label label3; private System.Windows.Forms.TextBox textBox4; private System.Windows.Forms.Button button2; private System.Windows.Forms.Button button3; private System.Windows.Forms.Button button4; private System.Windows.Forms.Label label4; private System.Windows.Forms.Label label5; private System.Windows.Forms.TextBox textBox5; private System.Windows.Forms.Button button5; private System.Windows.Forms.DataGridViewTextBoxColumn Column1; private System.Windows.Forms.DataGridViewTextBoxColumn Id; private System.Windows.Forms.DataGridViewTextBoxColumn Title; private System.Windows.Forms.DataGridViewTextBoxColumn Type; private System.Windows.Forms.DataGridViewTextBoxColumn Content; private System.Windows.Forms.DataGridViewTextBoxColumn CreateTime; private System.Windows.Forms.Button button6; } }
