.net core 6.0 使用sqlhelper实现简单的增删改查页面 - 开源包容

公告

一、创建数据表

　　1.根据数据结构文档创建数据表

表名：data_media
序号	字段名称	数据类型	长度	必填	主外键	描述
1	dm_id	varchar	32	是	主	主键（UUID）
2	dm_wechart	int	11	是		微信公众号数量
3	dm_blog	int	11	是		微博粉丝数量
4	del_state	int	11	是		删除状态： 0.正常 1.删除
5	create_user	varchar	500	是		创建人登录账号
6	create_time	varchar	255	是		创建时间 (yyyy-MM-dd HH:mm:ss)

　　2.数据表创建查询索引

　　因ID列为字符串，需要手动创建索引，数字自增列不需要手动创建。

二、创建实体类

根据数据表结构创建实体类。

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 
 7 namespace SSCMS.Entitys.Screen
 8 {
 9     /// <summary>
10     /// 微信微博
11     /// </summary>
12     public class DataMedia
13     {
14         /// <summary>
15         /// 主键（UUID）
16         /// </summary>
17         public string dm_id
18         {
19             set;
20             get;
21         }
22 
23         /// <summary>
24         /// 微信公众号数量
25         /// </summary>
26         public int dm_wechart
27         {
28             set;
29             get;
30         }
31 
32         /// <summary>
33         /// 微博粉丝数量
34         /// </summary>
35         public int dm_blog
36         {
37             set;
38             get;
39         }
40 
41         /// <summary>
42         /// 删除状态：0.正常  1.删除
43         /// </summary>
44         public int del_state
45         {
46             set;
47             get;
48         }
49 
50         /// <summary>
51         /// 创建人登录账号
52         /// </summary>
53         public string create_user
54         {
55             set;
56             get;
57         }
58 
59         /// <summary>
60         /// 创建时间 (yyyy-MM-dd HH:mm:ss)
61         /// </summary>
62         public string create_time
63         {
64             set;
65             get;
66         }
67 
68     }
69 }

实体类

三、创建业务类

　　1.划分业务类结构

 1 using Datory.MySqlHelper;
 2 using MySql.Data.MySqlClient;
 3 using SSCMS.Entitys;
 4 using System;
 5 using System.Collections.Generic;
 6 using System.Data;
 7 using System.Linq;
 8 using System.Text;
 9 using System.Threading.Tasks;
10 
11 namespace SSCMS.Utility.Screen
12 {
13     /// <summary>
14     /// 微信微博
15     /// </summary>
16     public class DataMediaUtility
17     {
18         #region 数据表基础操作方法
19         #endregion
20 
21         #region 业务操作方法
22         #endregion
23 
24     }
25 }

业务类

　　2.创建默认业务方法

  1 using Datory.MySqlHelper;
  2 using MySql.Data.MySqlClient;
  3 using SSCMS.Entitys;
  4 using SSCMS.Entitys.Screen;
  5 using System;
  6 using System.Collections.Generic;
  7 using System.Data;
  8 using System.Linq;
  9 using System.Text;
 10 using System.Threading.Tasks;
 11 
 12 namespace SSCMS.Utility.Screen
 13 {
 14     /// <summary>
 15     /// 微信微博
 16     /// </summary>
 17     public class DataMediaUtility
 18     {
 19         #region 数据表基础操作方法
 20         /// <summary>
 21         /// 根据ID获取实例
 22         /// </summary>
 23         /// <param name="st_id">ID</param>
 24         /// <returns>数据实例</returns>
 25         public static DataMedia GetObjById(string connection, string dm_id)
 26         {
 27             string sqlstr = "select * from data_media where dm_id = @dm_id";
 28             List<MySqlParameter> parmss = new List<MySqlParameter>();
 29             parmss.Add(new MySqlParameter("@dm_id", dm_id));
 30             try
 31             {
 32                 DataSet ds = MySqlHelperComm.ExecuteDataset(connection, CommandType.Text, sqlstr, parmss.ToArray());
 33                 DataMedia obj = DatasetHelper.DataSetToEntity<DataMedia>(ds, 0);
 34                 return obj;
 35             }
 36             catch (Exception ex)
 37             {
 38                 return null;
 39             }
 40         }
 41 
 42         /// <summary>
 43         /// 获取所有数据实例
 44         /// </summary>
 45         /// <returns>所有数据实例</returns>
 46         public static List<DataMedia> GetAllObj(string connection)
 47         {
 48             string sqlstr = "select * from data_media order by create_time desc";
 49             DataSet ds = MySqlHelperComm.ExecuteDataset(connection, CommandType.Text, sqlstr, null);
 50             List<DataMedia> objlist = DatasetHelper.DataSetToEntityList<DataMedia>(ds, 0);
 51             return objlist;
 52         }
 53 
 54         /// <summary>
 55         /// 插入一条数据
 56         /// </summary>
 57         /// <param name="obj">数据实例</param>
 58         /// <returns>成功条数</returns>
 59         public static int InsertObj(string connection, DataMedia obj)
 60         {
 61             string sqlstr = "insert into data_media (dm_id,dm_wechart,dm_blog,del_state,create_user,create_time) values (";
 62             sqlstr += "@dm_id," +
 63                       "@dm_wechart," +
 64                       "@dm_blog," +
 65                       "@del_state," +
 66                       "@create_user," +
 67                       "@create_time)";
 68 
 69             List<MySqlParameter> parmss = new List<MySqlParameter>();
 70             parmss.Add(new MySqlParameter("@dm_id", obj.dm_id));
 71             parmss.Add(new MySqlParameter("@dm_wechart", obj.dm_wechart));
 72             parmss.Add(new MySqlParameter("@dm_blog", obj.dm_blog));
 73             parmss.Add(new MySqlParameter("@del_state", obj.del_state));
 74             parmss.Add(new MySqlParameter("@create_user", obj.create_user));
 75             parmss.Add(new MySqlParameter("@create_time", obj.create_time));
 76             try
 77             {
 78                 int ids = MySqlHelperComm.ExecuteNonQuery(connection, CommandType.Text, sqlstr, parmss.ToArray());
 79 
 80                 return ids;
 81             }
 82             catch (Exception ex)
 83             {
 84                 return 0;
 85             }
 86         }
 87 
 88         /// <summary>
 89         /// 删除一条数据
 90         /// </summary>
 91         /// <param name="st_id">数据ID</param>
 92         /// <returns>成功条数</returns>
 93         public static int DeleteObj(string connection, string dm_id)
 94         {
 95             String sqlstr = "delete from data_media where st_id = @dm_id";
 96             List<MySqlParameter> parmss = new List<MySqlParameter>();
 97             parmss.Add(new MySqlParameter("@dm_id", dm_id));
 98             try
 99             {
100                 int ids = MySqlHelperComm.ExecuteNonQuery(connection, CommandType.Text, sqlstr, parmss.ToArray());
101 
102                 return ids;
103             }
104             catch (Exception ex)
105             {
106                 return 0;
107             }
108         }
109 
110         /// <summary>
111         /// 根据ID更新一条数据
112         /// </summary>
113         /// <param name="obj">数据实例</param>
114         /// <returns>成功条数</returns>
115         public static int UpdateObj(string connection, DataMedia obj)
116         {
117             string sqlstr = "update data_media set ";
118             sqlstr += " dm_wechart=@dm_wechart,";
119             sqlstr += " dm_blog=@dm_blog,";
120             sqlstr += " del_state=@del_state,";
121             sqlstr += " create_user=@create_user,";
122             sqlstr += " create_time=@create_time";
123             sqlstr += " where dm_id = @dm_id";
124             List<MySqlParameter> parmss = new List<MySqlParameter>();
125             parmss.Add(new MySqlParameter("@dm_id", obj.dm_id));
126             parmss.Add(new MySqlParameter("@dm_wechart", obj.dm_wechart));
127             parmss.Add(new MySqlParameter("@dm_blog", obj.dm_blog));
128             parmss.Add(new MySqlParameter("@del_state", obj.del_state));
129             parmss.Add(new MySqlParameter("@create_user", obj.create_user));
130             parmss.Add(new MySqlParameter("@create_time", obj.create_time));
131             try
132             {
133                 int ids = MySqlHelperComm.ExecuteNonQuery(connection, CommandType.Text, sqlstr, parmss.ToArray());
134                 return ids;
135             }
136             catch (Exception ex)
137             {
138                 return 0;
139             }
140         }
141         #endregion
142 
143         #region 业务操作方法
144         #endregion
145 
146     }
147 }

业务类基础方法

四、创建列表页面

1 @page
2 @{ Layout = "_Layout"; }
3 
4 
5 
6 @section Scripts{
7 <script src="/sitefiles/assets/js/admin/system/media/medialist.js" type="text/javascript"></script> }

页面代码

五、创建列表页面js文件

 1 var $url = '';
 2 
 3 var data = utils.init({
 4   dictlist: {},
 5   total: 0,
 6   pageSize: 10,
 7   currentPage: 0,
 8   page: 1,
 9   uploadPanel: false,
10   panel: false,
11   form: null,
12   searchForm: {
13     
14   }
15 });
16 
17 var methods = {
18 
19 };
20 
21 var $vue = new Vue({
22   el: '#main',
23   data: data,
24   methods: methods,
25   created: function () {
26     this.apiGet(1);
27   }
28 });

JS代码

六、创建列表查询Controller接口文件

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Net.Security;
 4 using System.Runtime.CompilerServices;
 5 using System.Security.Cryptography.X509Certificates;
 6 using System.Threading.Tasks;
 7 using Datory;
 8 using Microsoft.AspNetCore.Authorization;
 9 using Microsoft.AspNetCore.Http;
10 using Microsoft.AspNetCore.Mvc;
11 using NSwag.Annotations;
12 using SqlKata;
13 using SSCMS.Configuration;
14 using SSCMS.Enums;
15 using SSCMS.Models;
16 using SSCMS.Repositories;
17 using SSCMS.Services;
18 using SSCMS.Utils;
19 
20 namespace SSCMS.Web.Controllers.Admin.Systems.Media
21 {
22     /// <summary>
23     /// 微博微信管理
24     /// </summary>
25     [OpenApiIgnore]
26     [Authorize(Roles = Types.Roles.Administrator)]
27     [Route(Constants.ApiAdminPrefix)]
28     public partial class MediaController : ControllerBase
29     {
30         private const string RouteMediaList = "system/media/medialist";
31 
32         private readonly ISettingsManager _settingsManager;
33         private readonly IAuthManager _authManager;
34         public MediaController(ISettingsManager settingsManager, IAuthManager authManager)
35         {
36             _settingsManager = settingsManager;
37             _authManager = authManager;
38         }
39 
40         /// <summary>
41         /// 返回参数，Restful结构
42         /// </summary>
43         public class SendResult
44         {
45             public bool success { get; set; }
46             public string msg { get; set; }
47             public object result { get; set; }
48         }
49 
50 
51     }
52 }

Controller类

七、业务类创建查询方法

完善“DataMediaUtility.cs”类

 1         #region 业务操作方法
 2 
 3         /// <summary>
 4         /// 获取所有数据实例
 5         /// </summary>
 6         /// <returns>所有数据实例</returns>
 7         public static List<DataMedia> GetAllObjbyObj(string connection, string create_time_start, string create_time_end, int firstresult, int maxresults)
 8         {
 9             List<MySqlParameter> parmss = new List<MySqlParameter>();
10             string sqlstr = "select * from data_media where del_state=0 ";
11             if (!string.IsNullOrEmpty(create_time_start))
12             {
13                 sqlstr += " and create_time >= @create_time_start";
14                 parmss.Add(new MySqlParameter("@create_time_start", create_time_start));
15             }
16             if (!string.IsNullOrEmpty(create_time_end))
17             {
18                 sqlstr += " and create_time <= @create_time_end";
19                 parmss.Add(new MySqlParameter("@create_time_end", create_time_end));
20             }
21 
22             sqlstr += " ORDER BY create_time desc limit @firstresult,@maxresults";
23             parmss.Add(new MySqlParameter("@firstresult", firstresult));
24             parmss.Add(new MySqlParameter("@maxresults", maxresults));
25 
26             try
27             {
28                 DataSet ds = MySqlHelperComm.ExecuteDataset(connection, CommandType.Text, sqlstr, parmss.ToArray());
29                 List<DataMedia> objlist = DatasetHelper.DataSetToEntityList<DataMedia>(ds, 0);
30                 return objlist;
31             }
32             catch (Exception ex)
33             {
34                 return null;
35             }
36         }
37 
38         /// <summary>
39         /// 获取所有数据的总条数
40         /// </summary>
41         /// <returns></returns>
42         public static int GetAllCountbyObj(string connection, string create_time_start, string create_time_end)
43         {
44             List<MySqlParameter> parmss = new List<MySqlParameter>();
45             string sqlstr = "select count(*) from data_media where del_state=0";
46             if (!string.IsNullOrEmpty(create_time_start))
47             {
48                 sqlstr += " and create_time >= @create_time_start";
49                 parmss.Add(new MySqlParameter("@create_time_start", create_time_start));
50             }
51             if (!string.IsNullOrEmpty(create_time_end))
52             {
53                 sqlstr += " and create_time <= @create_time_end";
54                 parmss.Add(new MySqlParameter("@create_time_end", create_time_end));
55             }
56 
57             try
58             {
59                 DataSet ds = MySqlHelperComm.ExecuteDataset(connection, CommandType.Text, sqlstr, parmss.ToArray());
60                 return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
61             }
62             catch (Exception ex)
63             {
64                 return 0;
65             }
66         }
67 
68         #endregion

业务类

八、完善接口查询

创建查询接口“MediaController.List.cs”

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Net.Security;
 4 using System.Runtime.CompilerServices;
 5 using System.Security.Cryptography.X509Certificates;
 6 using System.Threading.Tasks;
 7 using Datory;
 8 using Microsoft.AspNetCore.Authorization;
 9 using Microsoft.AspNetCore.Http;
10 using Microsoft.AspNetCore.Mvc;
11 using NSwag.Annotations;
12 using SqlKata;
13 using SSCMS.Configuration;
14 using SSCMS.Enums;
15 using SSCMS.Models;
16 using SSCMS.Repositories;
17 using SSCMS.Services;
18 using SSCMS.Utils;
19 
20 namespace SSCMS.Web.Controllers.Admin.Systems.Media
21 {
22     public partial class MediaController
23     {
24         [OpenApiOperation("微博微信列表查询接口", "微博微信列表查询接口，使用POST发起请求，请求地址为 system/media/medialist")]
25         [HttpPost, Route(RouteMediaList)]
26         [ProducesResponseType(StatusCodes.Status200OK)]
27         [ProducesResponseType(StatusCodes.Status400BadRequest)]
28         public async Task<ActionResult<SendResult>> MediaListQuery(string dxstr)
29         {
30             return null;
31         }
32     }
33 }

Controller类

九、完善js接口调用

十、完善页面显示

十一、创建编辑页面

十二、创建编辑页面js文件

十三、创建编辑页面Controller接口文件

十四、业务类创建查询方法

十五、完善编辑接口文件

十六、完善js接口调用

十七、完善页面保存

posted on 2022-12-15 11:31 开源包容阅读(312) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部