c#--使用SqlSugar动态切换数据库
1，UI设置界面
2，启动界面加载的时候，设置数据库类型和连接字符串
#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
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
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
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
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,
//添加用户
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();
}
