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