赞
踩
SqlSugar 是一款 老牌 .NET开源ORM框架,由果糖大数据科技团队维护和更新 ,开箱即用最易上手的ORM
优点 :【生态丰富】【高性能】【超简单】 【功能全面】 【多库兼容】【适合产品】 【SqlSugar视频教程】
支持 : .net framework .net core3.1 .ne5 .net6 .net7 .net8 .net9
特色 : 拥有全球最活跃的ORM线上论坛,比EF还要活跃,交流群人数已超过万人 ,技术支持快,口碑好。
详情请看SqlSugar 官方教程SqlSugar .Net ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网
本文描述了在.Net环境中使用SQLSugar框架连接及访问PostgreSQL数据库的详细操作过程,主要包括四个步骤:1 下载SqlSugar包 2 创建初始化并连接数据库工具类 3 创建数据操作类 4调用操作类访问数据库。
在NuGet包管理器中搜索sqlsugar。注:.Net Framework框架选择SqlSugar,如果是.Net框架下则选择SqlSugarCore。
using SqlSugar
-
- using SqlSugar;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.Windows.Forms.Design;
-
- namespace DemoSummaryZT.DatabaseOperation
- {
- /// <summary>
- /// 数据库访问基类
- /// </summary>
- public class PGSql
- {
- public static SqlSugarScope PG_db = null;
- /// <summary>
- /// 初始化数据库连接
- /// </summary>
- /// <param name="server">主机IP</param>
- /// <param name="port">端口</param>
- /// <param name="database">数据库名称</param>
- /// <param name="userId">用户名</param>
- /// <param name="passWord">密码</param>
- /// <param name="ifJiaMi">是否加密</param>
- public static void InitialPGSql(string server, string port, string database, string userId, string passWord, bool ifJiaMi)
- {
-
- if (ifJiaMi)
- {
- passWord = Encryption.DecryptStringFromBytes_Aes(passWord, "PostgreSQLExample");//解密密码
- }
- PG_db = new SqlSugarScope(new ConnectionConfig
- {
- ConnectionString = $"Server={server};Port={port};Database={database};User Id={userId};Password={passWord}",
- DbType = DbType.PostgreSQL,
- IsAutoCloseConnection = true,
- }); // 使用SqlSugarScope建立PostgreSQL数据库连接
-
-
- }
- /// <summary>
- /// 获取数据库Postgre当前时间
- /// </summary>
- /// <returns></returns>
- public DateTime GetPostgreDateTime()
- {
- return PG_db.Ado.GetDateTime("select now();");
- }
-
-
- }
- }
数据库表可以根据实际的环境创建,用SQL语句或者可视化开发工具,这里用的是DBeaver
- using SqlSugar;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
- namespace DemoSummaryZT.DataModel
- {
- /// <summary>
- /// 用户信息
- /// </summary>
- [SugarTable("user_table")]
- public class UserTable
- {
- public string ID { get; set; }
- /// <summary>
- /// 年龄
- public int Age { get; set; }
- /// <summary>
- /// 姓名
- /// </summary>
- public string Name { get; set; }
-
-
- }
- }
注意,这里的所有操作类都继承步骤2中的PGSql类
- using DemoSummaryZT.DataModel;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
- namespace DemoSummaryZT.DatabaseOperation
- {
- public class AddUser :PGSql
- {
- /// <summary>
- /// 新增用户
- /// </summary>
- public static void AddNewUser(UserTable ut)
- {
- try
- {
- var res = PG_db.Insertable(ut).ExecuteCommand();
- if (res > 0)
- {
- return ;
- }
- else
- {
- return ;
- }
- }
- catch (Exception ex)
- {
- return;
- }
- }
-
- }
-
-
- }
- using DemoSummaryZT.DataModel;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
- namespace DemoSummaryZT.DatabaseOperation
- {
- public class DeleteUser:PGSql
- {
- /// <summary>
- /// 删除用户
- /// </summary>
- public static void Delete(string id)
- {
- try
- {
-
- var res = PG_db.Deleteable<UserTable>().In(it => it.ID , id).ExecuteCommand();
- // var res = PG_db.Deleteable<UserTable>().In(100).ExecuteCommand();
-
- if (res > 0)
- {
- return;
- }
- else
- {
- return;
- }
- }
- catch (Exception ex)
- {
- return;
- }
- }
-
- }
- }
- using DemoSummaryZT.DataModel;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
- namespace DemoSummaryZT.DatabaseOperation
- {
- public class ModifyUser:PGSql
- {
-
-
- public static void Modify(UserTable ut)
- {
- var result = PG_db.Updateable(ut).Where(a=>a.ID==ut.ID ).ExecuteCommand();
- }
-
- }
- }
- using DemoSummaryZT.DataModel;
- using SqlSugar;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
-
- namespace DemoSummaryZT.DatabaseOperation
- {
-
- public class QueryUser:PGSql
- {
-
- /// <summary>
- /// 查询所有的用户
- /// </summary>
- public static List<UserTable> QueryUserName()
- {
- List<UserTable>list = PG_db.Queryable<UserTable>().ToList();
- return list;
- }
- }
- }
创建控制台程序,调用数据库访问工具类并运行
- using DemoSummaryZT.DatabaseOperation;
- using DemoSummaryZT.DataModel;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Threading.Tasks;
- using System.Windows.Forms;
-
- namespace DemoSummaryZT
- {
- internal static class Program
- {
- /// <summary>
- /// 应用程序的主入口点。
- /// </summary>
- [STAThread]
- static void Main()
- {
-
- //初始化数据库连接
- PGSql.InitialPGSql("localhost", "5432", "postgres", "postgres", "postgres", false);
- //查询数据
- Console.WriteLine("----新查询----");
- List<UserTable> userList = QueryUser.QueryUserName();
- foreach (var item in userList)
- {
- Console.WriteLine("用户名:{0}\t\t ID:{1}\t年龄:{2}\t", item.Name, item.ID, item.Age);
- }
- //新增数据
- Console.WriteLine("----新增数据----");
- UserTable userTable = new UserTable();
- userTable.ID = "100";
- userTable.Name = "新增用户";
- userTable.Age = 47;
- AddUser.AddNewUser(userTable);
- Console.WriteLine("----新查询----");
- List<UserTable> userList1 = QueryUser.QueryUserName();
- foreach (var item in userList1)
- {
- Console.WriteLine("用户名:{0}\t \t ID:{1}\t年龄:{2}\t", item.Name, item.ID, item.Age);
- }
- //修改数据
- Console.WriteLine("----修改数据----");
- UserTable userTable1 = new UserTable();
- userTable1.ID = "100";
- userTable1.Name = "新增用户名被修改";
- userTable1.Age = 47;
- ModifyUser.Modify(userTable1);
- Console.WriteLine("----新查询----");
- List<UserTable> userList2 = QueryUser.QueryUserName();
- foreach (var item in userList2)
- {
- Console.WriteLine("用户名:{0}\t \t ID:{1}\t年龄:{2}\t", item.Name, item.ID, item.Age);
- }
- //删除数据
- Console.WriteLine("----删除数据-新增用户----");
- Console.WriteLine("----删除数据-新增用户----");
- DeleteUser.Delete("100");
- Console.WriteLine("----新查询----");
- List<UserTable> userList3 = QueryUser.QueryUserName();
- foreach (var item in userList3)
- {
- Console.WriteLine("用户名:{0}\t \t ID:{1}\t年龄:{2}\t", item.Name, item.ID, item.Age);
- }
- }
- }
- }
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。