有时候,一个项目只有一个数据库,比如只有SQLite,或者MySQL数据库,那么我们只需要使用一个固定的数据库即可。但是一个项目如果写好了,有多个用户使用,但是多个用户使用不同的数据库,这个时候,我们就需要把软件设计成可以连接多个数据库的模式,用什么数据库,就配置什么数据库即可。
本例子分别使用
sqlserver
mysql
SQLite
1.建立一个wpf程序,使用.net5
2.安装SqlSugar
3.建立HelperDB
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarDemo
{public static class HelperDB{public static SqlSugarClient SqlSugarClient{get{return new SqlSugarClient(new ConnectionConfig(){DbType = SetDBType(ConfigurationManager.AppSettings["DBType"]), //配置数据库类型ConnectionString = ConfigurationManager.AppSettings["ConnectionString"], //数据库连接字符串IsAutoCloseConnection = true,//设置为true无需使用using或者Close操作,自动关闭连接,不需要手动关闭数据链接InitKeyType = InitKeyType.SystemTable//默认SystemTable, 字段信息读取, 如:该属性是不是主键,是不是标识列等等信息});}}public static DbType SetDBType(string dbType){DbType DBType;switch (dbType.ToLower()){case "sqlserver":DBType = DbType.SqlServer;break;case "sqlite":DBType = DbType.Sqlite;break;case "mysql":DBType = DbType.MySql;break;default:DBType = DbType.MySql;break;}return DBType;}}
}
4.Users实体类,这个实体类要和数据库一样的,形成一一对应的关系。
数据库
此处,我们是先有数据库,然后手动创建的实体类。也可以使用SqlSugar工具箱或者代码自动生成实体类
Db First 实体生成 - SqlSugar 5x - .NET果糖网
代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarDemo
{public class Users{public int Id { get; set; }public string Name { get; set; }public int age { get; set; }public string aa { get; set; }public string bb { get; set; }}
}
5. MainWindow.xaml界面
<Window x:Class="SqlSugarDemo.MainWindow"xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"xmlns:d="http://schemas.microsoft.com/expression/blend/2008"xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"xmlns:local="clr-namespace:SqlSugarDemo"mc:Ignorable="d"Title="MainWindow" Height="450" Width="800"><Grid><Button x:Name="btn" Content="确定" Click="btn_Click" Width="100" Height="50"/></Grid>
</Window>
6.MainWindow.cs界面
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;namespace SqlSugarDemo
{/// <summary>/// Interaction logic for MainWindow.xaml/// </summary>public partial class MainWindow : Window{public MainWindow(){InitializeComponent();}private void btn_Click(object sender, RoutedEventArgs e){List<Users> users = HelperDB.SqlSugarClient.Queryable<Users>().Where(c => c.Name == "张三").ToList();}}
}
7.修改App.config配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration><appSettings><!--<add key="DBType" value="sqlserver" /><add key="ConnectionString" value="server=192.168.20.245;uid=sa;pwd=123456;database=mysqlCodeFirst" />--><add key="DBType" value="mysql" /><add key="ConnectionString" value="server=localhost;Database=mysqlCodeFirst;Uid=root;Pwd=123456" /></appSettings>
</configuration>
8.我们开启MySQL数据库,然后进行调试,看程序的结果
数据库中表的值
9. 我们修改配置,换成SqlServer数据库
<?xml version="1.0" encoding="utf-8" ?>
<configuration><appSettings><add key="DBType" value="sqlserver" /><add key="ConnectionString" value="server=192.168.20.245;uid=sa;pwd=123456;database=mysqlCodeFirst" /><!--<add key="DBType" value="mysql" /><add key="ConnectionString" value="server=localhost;Database=mysqlCodeFirst;Uid=root;Pwd=123456" />--></appSettings>
</configuration>
10.启动SqlServer数据库,然后进行调试,看程序的结果
数据库中表的值
11.Sqlite数据库,需要在代码里面创建数据库,建立表,再建立数据
配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration><appSettings><!--<add key="DBType" value="sqlserver" /><add key="ConnectionString" value="server=192.168.20.245;uid=sa;pwd=123456;database=mysqlCodeFirst" />--><!--<add key="DBType" value="mysql" /><add key="ConnectionString" value="server=localhost;Database=mysqlCodeFirst;Uid=root;Pwd=123456" />--><add key="DBType" value="sqlite" /><add key="ConnectionString" value="DataBase\test.db" /></appSettings>
</configuration>
HelperDB类文件
using Microsoft.Data.Sqlite;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarDemo
{public static class HelperDB{public static string DBConnectionString { get; set; } = ConfigurationManager.AppSettings["ConnectionString"];public static SqlSugarClient SqlSugarClient{get{return new SqlSugarClient(new ConnectionConfig(){DbType = SetDBType(ConfigurationManager.AppSettings["DBType"]), //配置数据库类型ConnectionString = DBConnectionString, //数据库连接字符串IsAutoCloseConnection = true,//设置为true无需使用using或者Close操作,自动关闭连接,不需要手动关闭数据链接InitKeyType = InitKeyType.Attribute//默认SystemTable, 字段信息读取, 如:该属性是不是主键,是不是标识列等等信息});}}public static DbType SetDBType(string dbType){DbType DBType;switch (dbType.ToLower()){case "sqlserver":DBType = DbType.SqlServer;break;case "sqlite":DBType = DbType.Sqlite;DBConnectionString = @"DataSource=" + AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.AppSettings["ConnectionString"];//DBConnectionString = new SqliteConnectionStringBuilder()//{// DataSource = @"E:\project\测试\SqlSugarDemo\bin\Debug\net5.0-windows\1.db" ,// Password = "123456" //高版本不支持密码,需要低版本//}.ToString();break;case "mysql":DBType = DbType.MySql;break;default:DBType = DbType.MySql;break;}return DBType;}}
}
MainWindow.cs文件夹
using Microsoft.Data.Sqlite;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;namespace SqlSugarDemo
{/// <summary>/// Interaction logic for MainWindow.xaml/// </summary>public partial class MainWindow : Window{public MainWindow(){InitializeComponent();}private void btn_Click(object sender, RoutedEventArgs e){HelperDB.SqlSugarClient.DbMaintenance.CreateDatabase(); //建库HelperDB.SqlSugarClient.CodeFirst.InitTables<Users>(); //建表Users users1 = new Users();users1.Id = 1;users1.Name = "张三";users1.age = 20;users1.aa = "111";users1.bb = "sqlite";HelperDB.SqlSugarClient.Insertable(users1).ExecuteCommand(); //插入数据var users = HelperDB.SqlSugarClient.Queryable<Users>().Where(c => c.Name == "张三").ToList(); //查询数据}}
}
数据库中表的值
代码调试的值