在开发过程中免不了创建实体类,字段少的表可以手动编写,但是字段多还用手动创建的话不免有些浪费时间,假如一张表有100多个字段,手写有些不现实。
这时我们会借助一些工具,如:动软代码生成器、各种ORM框架自带的代码生成器等等,都可以使用。
我们现在就基于SqlSugar(ORM框架)自己动手制造一个轮子,以SqlServer为例。我们先看一下成品效果,
源码下载:
链接:https://pan.baidu.com/s/1745ICwMFqYHUy0Nqfk1wmg
提取码:xion
复制这段内容后打开百度网盘手机App,操作更方便哦
使用流程:
配置好数据库链接,点击【链接数据库】获取指定服务器上的数据库名,点击数据库名,动态获取数据库下面的所有表,
点击数据表,如果生成过了的会自动获取生成的实体,如果没有生成过,点击【生成实体】自动生成显示,直接复制即可使用。
注:server=xxx.xxx.x.xxx这里如果是本地没有配置的话直接server=.即可。
开发环境:
编译器:Visual Studio 2017
运行环境:windows7 x64
数据库:SqlServer2012
代码实现步骤:
一、创建一个ASP.NET Web应用,命名为GenerateEntity
二、应用SqlSugar动态链接库
三、编写代码
这里分为前端和后端,前端页面展示,后端后台逻辑(注:由于我们是代码展示,所以就不搞三层架构、工厂模式这些,直接在控制器中完成,有需要的同学可以根据项目需求进行更改)
内部实现逻辑:
这里我直接贴出代码,直接拷贝即可使用:
前端html页面
- @{
- ViewBag.Title = "Home Page";
- }
-
- <script src="~/Scripts/jquery-3.3.1.js"></script>
-
- <div style="margin-top:10px;font-family:'Microsoft YaHei';font-size:18px; ">
- <div style="height:100px;width:100%;border:1px solid gray;padding:10px">
- <div>
- <span>链接数据库:</span>
- <input style="width:800px;max-width:800px;" id="Link" value="server=xxx.xxx.x.xxx;uid=sa;pwd=xxx" />
- <a href="javascript:void(0)" onclick="LinkServer()">链接数据库</a>
- </div>
- <div style="margin-top:10px">
- <span>数据库名:</span>
- <input style="color:red;font-weight:600" id="ServerName" />
-
- <span>表名:</span>
- <input style="color:red;font-weight:600" id="TableName" />
-
- <span>生成类型:</span>
- <select id="type">
- <option value="0">生成单个表</option>
- <option value="1">生成所有表</option>
- </select>
- <a href="javascript:void(0)" onclick="GenerateEntity()" style="margin-left:20px;font-weight:600;">生成实体</a>
- <br />
-
- </div>
- </div>
- <div style="height:720px;width:100%;">
- <div style="height:100%;width:40%;float:left; border:1px solid gray;font-size:20px">
-
- <div id="leftserver" style="float:left;border:1px solid gray;height:100%;width:40%;padding:10px;overflow: auto;">
-
- </div>
- <div id="lefttable" style="float:left;border:1px solid gray;height:100%;width:60%;padding:10px;overflow: auto;">
-
- </div>
- </div>
- <div style="height:100%;width:60%;float:left;border:1px solid gray;overflow: auto;">
- <textarea style="width:100%;height:100%;max-width:10000px" id="righttable"></textarea>
- </div>
- </div>
- </div>
-
- <script type="text/javascript">
-
- //链接数据库
- function LinkServer() {
- $.ajax({
- url: "/Home/LinkServer",
- data: { Link: $("#Link").val() },
- type: "POST",
- async: false,
- dataType: "json",
- success: function (data) {
- if (data.res) {
- if (data.info != "") {
- $("#leftserver").html("");
- var leftserver = "<span>数据库名</span><hr />";
- var info = eval("(" + data.info + ")");
- for (var i = 0; i < info.length; i++) {
- leftserver += "<a onclick=\"leftserver('" + info[i].Name + "')\">" + info[i].Name + "</a><br />";
- }
-
- $("#leftserver").html(leftserver);
- }
- }
- else {
- alert(data.msg);
- }
- }
- });
- }
-
- //查询指定数据库的表
- function leftserver(Name) {
- $("#ServerName").val(Name)
- $.ajax({
- url: "/Home/GetTable",
- data: { Link: $("#Link").val(), Name: Name },
- type: "POST",
- async: false,
- dataType: "json",
- success: function (data) {
- if (data.res) {
- if (data.info != "") {
- $("#lefttable").html("");
- var lefttable = "<span>表名</span><hr />";
- var info = eval("(" + data.info + ")");
- for (var i = 0; i < info.length; i++) {
- lefttable += "<a onclick=\"lefttable('" + info[i].Name + "')\">" + info[i].Name + "</a><br />";
- }
-
- $("#lefttable").html(lefttable);
- }
- }
- else {
- alert(data.msg);
- }
- }
- });
- }
-
- //查询指定数据库的表
- function lefttable(Name) {
- $("#TableName").val(Name);
- $.ajax({
- url: "/Home/GetGenerateEntity",
- data: { TableName: Name },
- type: "POST",
- async: false,
- dataType: "json",
- success: function (data) {
- if (data.res) {
- document.getElementById("righttable").innerHTML = data.info;
- }
- else {
- alert(data.msg);
- }
- }
- });
- }
-
- //生成实体
- function GenerateEntity() {
-
- $.ajax({
- url: "/Home/GenerateEntity",
- data: {
- Link: $("#Link").val(),
- Name: $("#ServerName").val(),
- TableName: $("#TableName").val(),
- type: $("#type").val()
- },
- type: "POST",
- async: false,
- dataType: "json",
- success: function (data) {
- if (data.res) {
- document.getElementById("righttable").innerHTML = data.info;
- }
- else {
- alert(data.msg);
- }
- }
- });
- }
-
- </script>
后端控制器数据
- using SqlSugar;
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Web;
- using System.Web.Mvc;
-
- namespace GenerateEntity.Controllers
- {
- public class HomeController : Controller
- {
- public ActionResult Index()
- {
- return View();
- }
-
- public ActionResult About()
- {
- ViewBag.Message = "Your application description page.";
-
- return View();
- }
-
- public ActionResult Contact()
- {
- ViewBag.Message = "Your contact page.";
-
- return View();
- }
-
-
-
- //链接数据库
- public JsonResult LinkServer(string Link)
- {
- ResultInfo result = new ResultInfo();
- try
- {
- //配置数据库连接
- SqlSugarClient db = new SqlSugarClient(
- new ConnectionConfig()
- {
- ConnectionString = ""+ Link + ";database=master",
- DbType = DbType.SqlServer,//设置数据库类型
- IsAutoCloseConnection = true,//自动释放数据务,如果存在事务,在事务结束后释放
- InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
- });
- string sql = @"SELECT top 100000 Name FROM Master..SysDatabases ORDER BY Name"; //查询所有链接的所有数据库名
- var strList = db.SqlQueryable<databaseName>(sql).ToList();
- result.info = Newtonsoft.Json.JsonConvert.SerializeObject(strList);
- result.res = true;
- result.msg = "链接成功!";
- }
- catch (Exception ex)
- {
- result.msg = ex.Message;
- }
-
- return Json(result, JsonRequestBehavior.AllowGet);
- }
-
- //根据数据库名查询所有表
- public JsonResult GetTable(string Link,string Name)
- {
-
- ResultInfo result = new ResultInfo();
- try
- {
- //配置数据库连接
- SqlSugarClient db = new SqlSugarClient(
- new ConnectionConfig()
- {
- ConnectionString = "" + Link + ";database="+ Name + "",
- DbType = DbType.SqlServer,//设置数据库类型
- IsAutoCloseConnection = true,//自动释放数据务,如果存在事务,在事务结束后释放
- InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
- });
-
- string sql = @"SELECT top 10000 Name FROM SYSOBJECTS WHERE TYPE='U' ORDER BY Name"; //查询所有链接的所有数据库名
- var strList = db.SqlQueryable<databaseName>(sql).ToList();
- result.info = Newtonsoft.Json.JsonConvert.SerializeObject(strList);
- result.res = true;
- result.msg = "查询成功!";
- }
- catch (Exception ex)
- {
- result.msg = ex.Message;
- }
-
- return Json(result, JsonRequestBehavior.AllowGet);
- }
-
- //生成实体
- public JsonResult GenerateEntity(string Link, string Name,string TableName,string type)
- {
-
- ResultInfo result = new ResultInfo();
- try
- {
- //配置数据库连接
- SqlSugarClient db = new SqlSugarClient(
- new ConnectionConfig()
- {
- ConnectionString = "" + Link + ";database=" + Name + "",
- DbType = DbType.SqlServer,//设置数据库类型
- IsAutoCloseConnection = true,//自动释放数据务,如果存在事务,在事务结束后释放
- InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
- });
-
- string path = "C:\\Demo\\2";
-
- if (type == "0")
- {
- path = "C:\\Demo\\2";
- db.DbFirst.Where(TableName).CreateClassFile(path);
- result.info = System.IO.File.ReadAllText(@"" + path + "\\" + TableName + ".cs" + "", Encoding.UTF8);
- }
- else if (type == "1")
- {
- path = "C:\\Demo\\3";
- db.DbFirst.IsCreateAttribute().CreateClassFile(path);
- result.info = "";
- }
-
-
-
- result.res = true;
- result.msg = "生成成功!";
- }
- catch (Exception ex)
- {
- result.msg = ex.Message;
- }
-
- return Json(result, JsonRequestBehavior.AllowGet);
- }
-
- //生成全部表时查看
- public JsonResult GetGenerateEntity(string TableName)
- {
-
- ResultInfo result = new ResultInfo();
- try
- {
- string path = "C:\\Demo\\3";
- result.info = System.IO.File.ReadAllText(@"" + path + "\\" + TableName + ".cs" + "", Encoding.UTF8);
- result.res = true;
- result.msg = "查询成功!";
- }
- catch (Exception ex)
- {
- result.msg = ex.Message;
- try
- {
- if (result.msg.Contains("未能找到文件"))
- {
- string path = "C:\\Demo\\2";
- result.info = System.IO.File.ReadAllText(@"" + path + "\\" + TableName + ".cs" + "", Encoding.UTF8);
- result.res = true;
- result.msg = "查询成功!";
- }
- }
- catch (Exception)
- {
- result.msg = ex.Message;
- }
- }
-
- return Json(result, JsonRequestBehavior.AllowGet);
- }
-
- //数据库名
- public class databaseName
- {
- public string Name { get; set; }
- }
-
- //封装返回信息数据
- public class ResultInfo
- {
- public ResultInfo()
- {
- res = false;
- startcode = 449;
- info = "";
- }
- public bool res { get; set; } //返回状态(true or false)
- public string msg { get; set; } //返回信息
- public int startcode { get; set; } //返回http的状态码
- public string info { get; set; } //返回的结果(res为true时返回结果集,res为false时返回错误提示)
- }
-
- }
- }
这样一套可视化代码生成器就出来了,我们把他发布到IIS上面,然后设置为浏览器标签(收藏),这样就可以快捷使用了。
我们运行一下看看,是不是感觉很方便呀!