c#--使用SqlSugar动态切换数据库

1，UI设置界面

 

 

2，启动界面加载的时候，设置数据库类型和连接字符串

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#region Initial()，读取Config配置文件，给Global的变量赋值
 
/// <summary>
/// 读取Config配置文件，给Global的变量赋值【放在启动界面加载的事件中】
/// </summary>
void Initial()
{
    CommonMethods.SetPropertiesFromXml(ConfigFilePath + "ParameterList.xml", GlobalVariable.myParameter);//读取配置文件赋值给储存类的属性
 
    //设置数据库类型和连接字符串
    SqlSugarService.SetDBType(GlobalVariable.myParameter.DBType);
    SqlSugarService.SetConnectionString(GlobalVariable.myParameter.DBConStr);
}
 
#endregion

 

3，SqlSugarHelper

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
/*
 * 手动添加SqlSugar.dll引用
 */
 
namespace _009_TPS.DAL.Helper
{
    public static class SqlSugarHelper
    {
        //连接字符串
        public static string ConnectionString = string.Empty;
        //数据库类型
        public static DbType DBType = DbType.SqlServer;
 
        public static SqlSugarClient SqlSugarClient
        {
            get
            {
                return new SqlSugarClient(new ConnectionConfig()
                {
                     
                    ConnectionString = ConnectionString,//必填, 数据库连接字符串
                    //DbType = DbType.Sqlite,//必填，选择数据库类型                   
                    //DbType = DbType.SqlServer ,//必填，选择数据库类型
                    DbType = DBType,
                    IsAutoCloseConnection = true,//设置为true无需使用using或者Close操作，自动关闭连接，不需要手动关闭数据链接
                    InitKeyType = InitKeyType.SystemTable//默认SystemTable, 字段信息读取, 如：该属性是不是主键，是不是标识列等等信息
 
                });
            }
 
        }
    }
}

　

4,SqlSugarService

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
 
namespace _009_TPS.DAL.Helper
{
    /// <summary>
    /// 设置数据库的连接字符串
    /// </summary>
    public static class SqlSugarService
    {
        /// <summary>
        /// 设置数据库的类型
        /// </summary>
        /// <param name="dbType"></param>
        public static void SetDBType(string dbType)
        {
            switch (dbType.ToLower())
            {
                case "sqlserver":
                    SqlSugarHelper.DBType = DbType.SqlServer;
                    break;
                case "sqlite":
                    SqlSugarHelper.DBType = DbType.Sqlite;
                    break;
                case "mysql":
                    SqlSugarHelper.DBType = DbType.MySql;
                    break;
                default:
                    SqlSugarHelper.DBType = DbType.SqlServer;
                    break;
            }           
        }
 
        /// <summary>
        /// 设置数据库的连接字符串
        /// </summary>
        /// <param name="ConnectionString"></param>
        public static void SetConnectionString(string ConnectionString)
        {
            SqlSugarHelper.ConnectionString = ConnectionString;
        }
    }
}

　

比如：用户的增删改查

1，SysAdmin

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace _009_TPS.Models
{
 
    /// <summary>
    /// 登录用户类
    /// </summary>
    public class SysAdmin
    {
        //登录ID
        public int LoginId { get; set; }
 
        //登录名称
        public string LoginName { get; set; }
 
        //登录密码
        public string LoginPwd { get; set; }
 
        /// <summary>
        /// 角色 0：操作工 1：工程师 2：主管
        /// </summary>
        public int UserRole { get; set; }
 
        /// <summary>
        /// 用户卡号
        /// </summary>
        public string UserCardNo { get; set; }
 
    }
 
}

　　

2,SysAdminService

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
using _009_TPS.DAL.Helper;
using _009_TPS.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace _009_TPS.DAL
{
    /// <summary>
    /// 用户管理Service
    /// </summary>
    public class SysAdminService
    {
        /// <summary>
        /// 返回所有的用户集合（admin除外）
        /// </summary>
        /// <returns>用户对象集合</returns>
        public static List<SysAdmin> GetAllAdminDB()
        {
            return SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>().Where(c => c.LoginName.ToLower() != "admin")
                .ToList();
        }
 
        /// <summary>
        /// 验证登录用户结果（验证用户名和密码和数据库的是否一致）
        /// </summary>
        /// <param name="sysAdmin">用户对象</param>
        /// <returns>用户对象</returns>
        public static SysAdmin AdminLogin(SysAdmin sysAdmin)
        {
            var list = SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>().Where(c =>
                c.LoginName.ToLower() == sysAdmin.LoginName && c.LoginPwd == sysAdmin.LoginPwd).ToList();
            return list.Count == 0 ? null : list[0];
        }
 
        /// <summary>
        /// 验证登录用户结果（根据用户卡号返回用户对象）
        /// </summary>
        /// <param name="sysAdmin">用户对象</param>
        /// <returns>用户对象</returns>
        public static SysAdmin AdminLoginWithUserCardNo(SysAdmin sysAdmin)
        {
            var list = SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>()
                .Where(c => c.UserCardNo == sysAdmin.UserCardNo).ToList();
            return list.Count == 0 ? null : list[0];
        }
 
        /// <summary>
        /// 增：添加用户
        /// </summary>
        /// <param name="admin">用户对象</param>
        /// <returns></returns>
        public static bool AddAdminDB(SysAdmin admin)
        {
            return SqlSugarHelper.SqlSugarClient.Insertable(admin).ExecuteCommand() == 1;
        }
 
        /// <summary>
        /// 根据用户名判断用户是否存在
        /// </summary>
        /// <param name="loginName">用户名</param>
        /// <returns></returns>
        public static bool CheckLoginNameExit(string loginName)
        {
            return SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>().Where(c => c.LoginName == loginName).Count() > 0;
        }
 
        /// <summary>
        /// 根据卡号判断用户是否存在
        /// </summary>
        /// <param name="loginName">卡号</param>
        /// <returns></returns>
        public static bool CheckUserCardNoExit(string userCardNo)
        {
            return SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>().Where(c => c.UserCardNo == userCardNo).Count() >
                   0;
        }
 
        /// <summary>
        /// 改：根据用户名更新用户
        /// </summary>
        /// <param name="admin">用户对象</param>
        /// <returns></returns>
        public static bool UpdateAdminDB(SysAdmin admin)
        {
            return SqlSugarHelper.SqlSugarClient.Updateable(admin).WhereColumns(c => c.LoginName)
                       .ExecuteCommand() == 1;
        }
 
 
        /// <summary>
        /// 删：更加用户名删除用户
        /// </summary>
        /// <param name="loginName">用户名</param>
        /// <returns></returns>
        public static bool DeleteAdminDB(string loginName)
        {
            return SqlSugarHelper.SqlSugarClient.Deleteable<SysAdmin>().Where(c => c.LoginName == loginName)
                       .ExecuteCommand() == 1;
        }
 
        /// <summary>
        /// 根据用户名称返回用户对象
        /// </summary>
        /// <param name="loginName">用户名</param>
        /// <returns>用户对象</returns>
        public static SysAdmin GetSysAdminByLoginName(string loginName)
        {
            var query = SqlSugarHelper.SqlSugarClient.Queryable<SysAdmin>().Where(c => c.LoginName == loginName)
                .ToList();
            if (query.Count > 0)
            {
                return query[0];
            }
            else
            {
                return null;
            }
        }
    }
}

　　

3,

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
//添加用户
private void btn_add_Click(object sender, EventArgs e)
{
    if (this.txt_loginPwd.Text.Trim().Length > 0 && this.txt_loginName.Text.Trim().Length > 0)
    {
        SysAdmin admin = new SysAdmin()
        {
            LoginName = this.txt_loginName.Text.Trim(),
            LoginPwd = this.txt_loginPwd.Text.Trim(),
            UserRole = this.rdb_operation.Checked ? 0 : this.rdb_engineer.Checked ? 1 : 2,
            UserCardNo = this.txt_userCardNo.Text.Trim()
        };
        if (SysAdminService.AddAdminDB(admin))
        {
            MessageBox.Show("新用户:" + $"“{admin.LoginName}”" + "添加成功", "添加用户");
            UpdateAdmin();
        }
        else
        {
            MessageBox.Show("新用户:" + $"“{admin.LoginName}”" + "添加失败", "添加用户");
        }
 
    }
    else
    {
        MessageBox.Show("用户名称或用户密码不能为空","添加用户");
    }
}
 
//修改用户
private void btn_Modify_Click(object sender, EventArgs e)
{
    if (this.txt_userCardNo.Text.Trim().Length > 0 && this.txt_loginName.Text.Trim().Length > 0)
    {
        //检测用户是否存在
        if (!SysAdminService.CheckLoginNameExit(this.txt_loginName.Text))
        {
            MessageBox.Show("该用户名称不存在，请添加后再修改", "修改用户");
            return;
        }
        SysAdmin admin = new SysAdmin()
        {
            LoginName = this.txt_loginName.Text.Trim(),
            LoginPwd = this.txt_loginPwd.Text.Trim(),
            UserRole = this.rdb_operation.Checked ? 0 : this.rdb_engineer.Checked ? 1 : 2,
            UserCardNo = this.txt_userCardNo.Text.Trim()
        };
        if (SysAdminService.UpdateAdminDB(admin))
        {
            MessageBox.Show("用户:" + $"“{admin.LoginName}”" + "信息修改成功", "修改用户");
            UpdateAdmin();
        }
        else
        {
            MessageBox.Show("用户:" + $"“{admin.LoginName}”" + "信息修改失败", "修改用户");
        }
 
    }
    else
    {
        MessageBox.Show("用户名称或用户密码不能为空", "添加用户");
    }
}
 
//删除用户
private void btn_delete_Click(object sender, EventArgs e)
{
    if (this.dgv_data.SelectedRows.Count > 0)
    {
        string loginName = this.dgv_data.SelectedRows[0].Cells["LoginName"].Value.ToString();
 
        //删除确认
        DialogResult result = MessageBox.Show("确认要删除用户  [" + loginName + "]  吗？", "删除询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
        if (result == DialogResult.Cancel) return;
 
        if (SysAdminService.DeleteAdminDB(loginName))
        {
            MessageBox.Show("用户:" + $"“{loginName}”" + "删除成功", "删除用户");
            UpdateAdmin();
        }
        else
        {
            MessageBox.Show("用户:" + $"“{loginName}”" + "删除失败", "删除用户");
        }
    }
    else
    {
        MessageBox.Show("请选择要删除的用户", "删除用户");
    }
}
 
//关闭
private void btn_close_Click(object sender, EventArgs e)
{
    this.Close();
    this.Dispose();
}
 
//更新
private void UpdateAdmin()
{
    this.dgv_data.DataSource = null;
    this.dgv_data.DataSource = SysAdminService.GetAllAdminDB();
}

　　

　

 

　

posted @   包子789654  阅读(3600)  评论(0)  编辑  收藏  举报
相关博文：
·  C#-使用SqlSugar操作数据库导致的问题：托管调试助手“FatalExecutionEngineError”：运行时遇到了 错误。此错误的地址为。。。。
·  MySQL使用教程笔记（MySQL Workbench远程访问和C#远程访问）
·  SqlSugar 简易操作数据库
·  C# 动态创建类，动态创建表，支持多库的数据库维护方案
·  C# SqlSugar框架的学习使用（二）-- 类的生成及增删改查的应用
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？
点击右上角即可分享
微信分享提示
SQL AI 助手