C#-Winform-DataGridView-数据库(SQLite)-SQLSugar

DataGridView-数据库(SQLite)-SQLSugar

前提

需要引用sqlsugar、system.data.sqlite的包

一、界面搭建

二、初始化数据

private void btnInitData_Click(object sender, EventArgs e)
{
    string[] idArray = new string[] { "1", "2", "3", "4", "5" };
    string[] nameArray = new string[] { "赵一", "钱二", "孙三", "李四", "周五" };
    string[] addressArray = new string[] { "黑龙江", "江苏", "西藏", "新疆", "内蒙古" };
    string[] phoneArray = new string[] { "86-451", "86-025	", "0891", "0995", "0477" };
    string[] genderArray = new string[] { "1", "0", "1", "0", "1" };

    gridToDatabase.Rows.Add(idArray.Length);
    for (int i = 0; i < idArray.Length; i++)
    {
        gridToDatabase.Rows[i].Cells[0].Value = idArray[i];
        gridToDatabase.Rows[i].Cells[1].Value = nameArray[i];
        gridToDatabase.Rows[i].Cells[2].Value = addressArray[i];
        gridToDatabase.Rows[i].Cells[3].Value = phoneArray[i];
        gridToDatabase.Rows[i].Cells[4].Value = genderArray[i];
    }
}

三、数据库操作

3.1 SqlSugar创建数据库

sqlite是文件类型的数据库，需要一个文件路径

SqlSugarClient db;
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
    string filePath = System.Windows.Forms.Application.StartupPath + "user11.db";
    db = new SqlSugarClient(new ConnectionConfig(){
        ConnectionString = "data source = " + filePath,
        DbType = SqlSugar.DbType.Sqlite,
        IsAutoCloseConnection = true,
        InitKeyType = InitKeyType.Attribute
    });
    db.Open();
    MessageBox.Show("创建成功：" + filePath);
}

3.2 SqlSugar创建表

1. 准备一个实体类

class User
{
    //定义主键
    [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
    public int Id { get; set; }
    public string name { get; set; }
    public string address { get; set; }
    public string phone { get; set; }
    public string gender { get; set; }

}

2. 创建

private void btnCreateTable_Click(object sender, EventArgs e)
{
    db.CodeFirst.InitTables(typeof(User));
}

3.3 DataGridView数据导出到数据库

private void btnExport_Click(object sender, EventArgs e)
{
    List<User> userList = new List<User>();
    List<int> idList = new List<int>();
    List<string> nameList = new List<string>();
    List<string> addressList = new List<string>();
    List<string> phoneList = new List<string>();
    List<string> genderList = new List<string>();

    for (int i = 0; i < gridToDatabase.Rows.Count; i++)
    {
        idList.Add(int.Parse(gridToDatabase.Rows[i].Cells[0].Value.ToString()));
        nameList.Add(gridToDatabase.Rows[i].Cells[1].Value.ToString());
        addressList.Add(gridToDatabase.Rows[i].Cells[2].Value.ToString());
        phoneList.Add(gridToDatabase.Rows[i].Cells[3].Value.ToString());
        genderList.Add(gridToDatabase.Rows[i].Cells[4].Value.ToString());
    }

    for (int j = 0; j < idList.Count; j++)
    {
        User user = new User();
        user.id = idList[j];
        user.name = nameList[j];
        user.address = addressList[j];
        user.phone = phoneList[j];
        user.gender = genderList[j];
        userList.Add(user);
    }
    db.Insertable(userList).ExecuteCommand();
}

3.4 数据库导入到DataGridView

private void button1_Click(object sender, EventArgs e)
{
    databaseToGrid.Rows.Clear();
    List<User> users = new List<User>();

    users = db.Queryable<User>().ToList();

    databaseToGrid.Rows.Add(users.Count);

    for (int i = 0; i < users.Count; i++)
    {
        databaseToGrid.Rows[i].Cells[0].Value = users[i].id + "-sql";
        databaseToGrid.Rows[i].Cells[1].Value = users[i].name + "sql";
        databaseToGrid.Rows[i].Cells[2].Value = users[i].address + "sql";
        databaseToGrid.Rows[i].Cells[3].Value = users[i].phone + "sql";
        databaseToGrid.Rows[i].Cells[4].Value = users[i].gender + "sql";
    }
}

四、扩展(一个实体类创建多个表)

四、扩展(一个实体类创建多个表)

string tableName;
/* 点击button按钮"一个实体创建多个表"根据对应的文本框的“文本”生成User结构的数据表，“User”+“文本”作为数据  	* 表的名字
 */
private void btnOneToMultiple_Click(object sender, EventArgs e)
{
    tableName = txtTableName.Text;
    // 数据库动态添加设备数据对照表
    db.MappingTables.Add(typeof(User).Name, typeof(User).Name + tableName);
    db.CodeFirst.InitTables(typeof(User));

    ThreadStart thStart = new ThreadStart(refresh);
    Thread th = new Thread(thStart);
    th.Start();
}
/*
* 在多行的文本框中显示查询的数据库中查询到表的内容
*/
public void refresh()
{
    List<User> list = new List<User>();
    list = db.Queryable<User>().AS("User" + tableName).ToList();
    this.Invoke(new Action(() =>{
        txtTableSet.Text += list.ToString();
    }));
}