GeezerVison

The World Wide Web, restored to 800 pixels of glory.

GeezerVison is showing github.com — #增加mysql数据支撑表别名设置# · JerryMouseLi/APIJSON.NET@3cfaef5

File tree

Expand file treeCollapse file tree

Open diff view settings

Filter options

APIJSON.NET/APIJSON.NET

Expand file treeCollapse file tree

Open diff view settings

Collapse file

`‎APIJSON.NET/APIJSON.NET/APIJSON.NET.csproj‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/APIJSON.NET.csproj

+1Lines changed: 1 addition & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -12,6 +12,7 @@`
`12`	`12`	`<PackageReference Include="Dapper" Version="1.50.5" />`
`13`	`13`	`<PackageReference Include="Dapper.SqlBuilder" Version="1.50.5" />`
`14`	`14`	`<PackageReference Include="Microsoft.AspNetCore.App" />`
	`15`	`+ <PackageReference Include="MySql.Data" Version="8.0.11" />`
`15`	`16`	`<PackageReference Include="Swashbuckle.AspNetCore" Version="2.5.0" />`
`16`	`17`	`<PackageReference Include="Swashbuckle.AspNetCore.Swagger" Version="2.5.0" />`
`17`	`18`	`</ItemGroup>`

Collapse file

`‎APIJSON.NET/APIJSON.NET/Controllers/ValuesController.cs‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/Controllers/ValuesController.cs

+18-17Lines changed: 18 additions & 17 deletions

Original file line number	Diff line number	Diff line change
`@@ -12,12 +12,13 @@`
`12`	`12`	`[ApiController]`
`13`	`13`	`public class JsonController : ControllerBase`
`14`	`14`	`{`
`15`		`- private DapperOptions _options;`
`16`		`- public JsonController(IOptions<DapperOptions> options)`
	`15`	`+ private DapperHelper db;`
	`16`	`+ private JsonToSql sqlbuilder;`
	`17`	`+ public JsonController(DapperHelper helper, JsonToSql jsonToSql)`
`17`	`18`	`{`
`18`		`- this._options = options.Value;`
	`19`	`+ db = helper;`
	`20`	`+ sqlbuilder = jsonToSql;`
`19`	`21`	`}`
`20`		`-`
`21`	`22`	`/// <summary>`
`22`	`23`	`/// 查询`
`23`	`24`	`/// </summary>`
`@@ -54,8 +55,8 @@ public ActionResult Query([FromBody]string json)`
`54`	`55`	`if (tables.Count > 0)`
`55`	`56`	`{`
`56`	`57`	`string table = tables[0];`
`57`		`- var template = JsonToSql.GetSqlBuilder(table, page, count, where[0], null);`
`58`		`- foreach (var dd in DapperDBHelp.Query(_options.ConnectionString, template.RawSql, template.Parameters))`
	`58`	`+ var template = sqlbuilder.GetSqlBuilder(table, page, count, where[0], null);`
	`59`	`+ foreach (var dd in db.Query(template.RawSql, template.Parameters))`
`59`	`60`	`{`
`60`	`61`	`var zht = new JObject();`
`61`	`62`	`zht.Add(table, JToken.FromObject(dd));`
`@@ -69,18 +70,18 @@ public ActionResult Query([FromBody]string json)`
`69`	`70`	`var jbb = JObject.Parse(where[i]);`
`70`	`71`	`page = jbb["page"] == null ? 0 : int.Parse(jbb["page"].ToString());`
`71`	`72`	`count = jbb["count"] == null ? 0 : int.Parse(jbb["count"].ToString());`
`72`		`- template = JsonToSql.GetSqlBuilder(subtable, page, count, jbb[subtable].ToString(), zht);`
	`73`	`+ template = sqlbuilder.GetSqlBuilder(subtable, page, count, jbb[subtable].ToString(), zht);`
`73`	`74`	`var lt = new JArray();`
`74`		`- foreach (var d in DapperDBHelp.Query(_options.ConnectionString, template.RawSql, template.Parameters))`
	`75`	`+ foreach (var d in db.Query( template.RawSql, template.Parameters))`
`75`	`76`	`{`
`76`	`77`	`lt.Add(JToken.FromObject(d));`
`77`	`78`	`}`
`78`	`79`	`zht.Add(tables[i], lt);`
`79`	`80`	`}`
`80`	`81`	`else`
`81`	`82`	`{`
`82`		`- template = JsonToSql.GetSqlBuilder(subtable, 0, 0, where[i].ToString(), zht);`
`83`		`- var df = DapperDBHelp.QueryFirstOrDefault(_options.ConnectionString, template.RawSql, template.Parameters);`
	`83`	`+ template = sqlbuilder.GetSqlBuilder(subtable, 0, 0, where[i].ToString(), zht);`
	`84`	`+ var df = db.QueryFirstOrDefault( template.RawSql, template.Parameters);`
`84`	`85`	`if (df != null)`
`85`	`86`	`{`
`86`	`87`	`zht.Add(subtable, JToken.FromObject(df));`
`@@ -103,8 +104,8 @@ public ActionResult Query([FromBody]string json)`
`103`	`104`	`jb.Remove("count");`
`104`	`105`	`foreach (var t in jb)`
`105`	`106`	`{`
`106`		`- var template = JsonToSql.GetSqlBuilder(t.Key, page, count, t.Value.ToString(), null);`
`107`		`- foreach (var d in DapperDBHelp.Query(_options.ConnectionString, template.RawSql, template.Parameters))`
	`107`	`+ var template = sqlbuilder.GetSqlBuilder(t.Key, page, count, t.Value.ToString(), null);`
	`108`	`+ foreach (var d in db.Query( template.RawSql, template.Parameters))`
`108`	`109`	`{`
`109`	`110`	`htt.Add(JToken.FromObject(d));`
`110`	`111`	`}`
`@@ -113,8 +114,8 @@ public ActionResult Query([FromBody]string json)`
`113`	`114`	`}`
`114`	`115`	`else`
`115`	`116`	`{`
`116`		`- var template = JsonToSql.GetSqlBuilder(key, 0, 0, item.Value.ToString(), ht);`
`117`		`- var df = DapperDBHelp.QueryFirstOrDefault(_options.ConnectionString, template.RawSql, template.Parameters);`
	`117`	`+ var template = sqlbuilder.GetSqlBuilder(key, 0, 0, item.Value.ToString(), ht);`
	`118`	`+ var df = db.QueryFirstOrDefault( template.RawSql, template.Parameters);`
`118`	`119`	`if (df != null)`
`119`	`120`	`{`
`120`	`121`	`ht.Add(key, JToken.FromObject(df));`
`@@ -161,7 +162,7 @@ public ActionResult Add([FromBody]string json)`
`161`	`162`	`}`
`162`	`163`	`string sql = sb.ToString().TrimEnd(',') + val.ToString().TrimEnd(',') + ");SELECT CAST(SCOPE_IDENTITY() as int);";`
`163`	`164`
`164`		`- using (var sqlConnection = new SqlConnection(_options.ConnectionString))`
	`165`	`+ using (var sqlConnection = db.Connection)`
`165`	`166`	`{`
`166`	`167`	`sqlConnection.Open();`
`167`	`168`	`int id = sqlConnection.ExecuteScalar<int>(sql, p);`
`@@ -217,7 +218,7 @@ public ActionResult Edit([FromBody]string json)`
`217`	`218`	`p.Add($"@{f.Key}", f.Value.ToString());`
`218`	`219`	`}`
`219`	`220`	`string sql = sb.ToString().TrimEnd(',') + " where id=@id;";`
`220`		`- using (var sqlConnection = new SqlConnection(_options.ConnectionString))`
	`221`	`+ using (var sqlConnection = db.Connection)`
`221`	`222`	`{`
`222`	`223`	`sqlConnection.Open();`
`223`	`224`	`sqlConnection.Execute(sql, p);`
`@@ -269,7 +270,7 @@ public ActionResult Remove([FromBody]string json)`
`269`	`270`	`p.Add($"@{f.Key}", f.Value.ToString());`
`270`	`271`	`}`
`271`	`272`	`string sql = sb.ToString().TrimEnd(',');`
`272`		`- using (var sqlConnection = new SqlConnection(_options.ConnectionString))`
	`273`	`+ using (var sqlConnection = db.Connection)`
`273`	`274`	`{`
`274`	`275`	`sqlConnection.Open();`
`275`	`276`	`sqlConnection.Execute(sql, p);`

Collapse file

`‎APIJSON.NET/APIJSON.NET/DapperDBHelp.cs‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/DapperDBHelp.cs

+30-5Lines changed: 30 additions & 5 deletions

Original file line number	Diff line number	Diff line change
`@@ -1,21 +1,46 @@`
`1`	`1`	`namespace APIJSON.NET`
`2`	`2`	`{`
`3`	`3`	`using Dapper;`
	`4`	`+ using Microsoft.Extensions.Options;`
	`5`	`+ using MySql.Data.MySqlClient;`
`4`	`6`	`using System.Collections.Generic;`
	`7`	`+ using System.Data;`
`5`	`8`	`using System.Data.SqlClient;`
`6`		`- public static class DapperDBHelp`
	`9`	`+ public class DapperHelper`
`7`	`10`	`{`
`8`		`- public static dynamic QueryFirstOrDefault(string ConnectionString, string sql, object param)`
	`11`	`+`
	`12`	`+ private DapperOptions _options;`
	`13`	`+`
	`14`	`+ public DapperHelper(IOptions<DapperOptions> options)`
`9`	`15`	`{`
`10`		`- using (var sqlConnection = new SqlConnection(ConnectionString))`
	`16`	`+ this._options = options.Value;`
	`17`	`+ }`
	`18`	`+ public IDbConnection Connection`
	`19`	`+ {`
	`20`	`+ get`
	`21`	`+ {`
	`22`	`+ if (!string.IsNullOrEmpty(_options.MySql))`
	`23`	`+ {`
	`24`	`+ return new MySqlConnection(_options.MySql);`
	`25`	`+ }`
	`26`	`+ else`
	`27`	`+ {`
	`28`	`+ return new SqlConnection(_options.SqlServer);`
	`29`	`+ }`
	`30`	`+ }`
	`31`	`+ }`
	`32`	`+`
	`33`	`+ public dynamic QueryFirstOrDefault(string sql, object param)`
	`34`	`+ {`
	`35`	`+ using (var sqlConnection = Connection)`
`11`	`36`	`{`
`12`	`37`	`sqlConnection.Open();`
`13`	`38`	`return sqlConnection.QueryFirstOrDefault(sql, param);`
`14`	`39`	`}`
`15`	`40`	`}`
`16`		`- public static IEnumerable<dynamic> Query(string ConnectionString, string sql, object param)`
	`41`	`+ public IEnumerable<dynamic> Query( string sql, object param)`
`17`	`42`	`{`
`18`		`- using (var sqlConnection = new SqlConnection(ConnectionString))`
	`43`	`+ using (var sqlConnection = Connection)`
`19`	`44`	`{`
`20`	`45`	`sqlConnection.Open();`
`21`	`46`	`return sqlConnection.Query(sql, param);`

Collapse file

`‎APIJSON.NET/APIJSON.NET/DapperOptions.cs‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/DapperOptions.cs

+2-1Lines changed: 2 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -2,6 +2,7 @@`
`2`	`2`	`{`
`3`	`3`	`public class DapperOptions`
`4`	`4`	`{`
`5`		`- public string ConnectionString { get; set; }`
	`5`	`+ public string SqlServer { get; set; }`
	`6`	`+ public string MySql { get; set; }`
`6`	`7`	`}`
`7`	`8`	`}`

Collapse file

`‎APIJSON.NET/APIJSON.NET/JsonToSql.cs‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/JsonToSql.cs

+31-4Lines changed: 31 additions & 4 deletions

Original file line number	Diff line number	Diff line change
`@@ -1,28 +1,55 @@`
`1`	`1`	`namespace APIJSON.NET`
`2`	`2`	`{`
`3`	`3`	`using Dapper;`
	`4`	`+ using Microsoft.Extensions.Options;`
`4`	`5`	`using Newtonsoft.Json.Linq;`
`5`	`6`	`using System;`
	`7`	`+ using System.Collections.Generic;`
`6`	`8`	`using System.Linq;`
`7`		`- public static class JsonToSql`
	`9`	`+ public class JsonToSql`
`8`	`10`	`{`
`9`		`- public static SqlBuilder.Template GetSqlBuilder(string subtable, int page, int count, string json, JObject dd)`
	`11`	`+ /// <summary>`
	`12`	`+ /// 对应数据表`
	`13`	`+ /// </summary>`
	`14`	`+ static Dictionary<string, string> dict = new Dictionary<string, string>`
	`15`	`+ {`
	`16`	`+ {"user", "apijson_user"},`
	`17`	`+ };`
	`18`	`+ private DapperOptions _options;`
	`19`	`+`
	`20`	`+ public JsonToSql(IOptions<DapperOptions> options)`
	`21`	`+ {`
	`22`	`+ this._options = options.Value;`
	`23`	`+ }`
	`24`	`+ public SqlBuilder.Template GetSqlBuilder(string subtable, int page, int count, string json, JObject dd)`
`10`	`25`	`{`
`11`	`26`	`if (!subtable.IsTable())`
`12`	`27`	`{`
`13`	`28`	`throw new Exception($"表名{subtable}不正确！");`
`14`	`29`	`}`
	`30`	`+ if (dict.ContainsKey(subtable.ToLower()))`
	`31`	`+ {`
	`32`	`+ subtable = dict.GetValueOrDefault(subtable.ToLower());`
	`33`	`+ }`
`15`	`34`	`JObject values = JObject.Parse(json);`
`16`	`35`	`page = values["page"] == null ? page : int.Parse(values["page"].ToString());`
`17`	`36`	`count = values["count"] == null ? count : int.Parse(values["count"].ToString());`
`18`	`37`	`values.Remove("page");`
`19`	`38`	`values.Remove("count");`
`20`	`39`	`var builder = new SqlBuilder();`
`21`		`- string pagesql = $"select /select/ from [{subtable}] /where/ /groupby/ /having/";`
	`40`	`+ string pagesql = $"select /select/ from {subtable} /where/ /groupby/ /having/";`
`22`	`41`	`if (count > 0)`
`23`	`42`	`{`
`24`		`- pagesql = $@"select * from (select row_number()over(order by id)rownumber,/select/ from [{subtable}] /where/ /groupby/ /having/) a`
	`43`	`+ if (!string.IsNullOrEmpty(_options.MySql))`
	`44`	`+ {`
	`45`	`+ pagesql = $@"select /select/ from {subtable} /where/ /groupby/ /having/ limit {(page * count) + 1},{(page * count) + count}";`
	`46`	`+ }`
	`47`	`+ else`
	`48`	`+ {`
	`49`	`+ pagesql = $@"select * from (select row_number()over(order by id)rownumber,/select/ from {subtable} /where/ /groupby/ /having/) a`
`25`	`50`	`where rownumber between {(page * count) + 1} and {(page * count) + count}";`
	`51`	`+ }`
	`52`	`+`
`26`	`53`	`}`
`27`	`54`	`var template = builder.AddTemplate(pagesql);`
`28`	`55`	`//查询字段`

Collapse file

`‎APIJSON.NET/APIJSON.NET/Startup.cs‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/Startup.cs

+5-1Lines changed: 5 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -27,14 +27,17 @@ public void ConfigureServices(IServiceCollection services)`
`27`	`27`
`28`	`28`	`services.Configure<DapperOptions>(options =>`
`29`	`29`	`{`
`30`		`- options.ConnectionString = Configuration.GetConnectionString("Default");`
	`30`	`+ options.MySql = Configuration.GetConnectionString("MySql");`
	`31`	`+ options.SqlServer = Configuration.GetConnectionString("SqlServer");`
`31`	`32`	`});`
`32`	`33`
`33`	`34`	`services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);`
`34`	`35`	`services.AddSwaggerGen(c =>`
`35`	`36`	`{`
`36`	`37`	`c.SwaggerDoc("v1", new Info { Title = "APIJSON.NET", Version = "v1" });`
`37`	`38`	`});`
	`39`	`+ services.AddSingleton<DapperHelper>();`
	`40`	`+ services.AddSingleton<JsonToSql>();`
`38`	`41`	`}`
`39`	`42`
`40`	`43`	`// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.`
`@@ -46,6 +49,7 @@ public void Configure(IApplicationBuilder app, IHostingEnvironment env)`
`46`	`49`	`}`
`47`	`50`
`48`	`51`	`app.UseMvc();`
	`52`	`+`
`49`	`53`	`app.UseSwagger();`
`50`	`54`	`app.UseSwaggerUI(c =>`
`51`	`55`	`{`

Collapse file

`‎APIJSON.NET/APIJSON.NET/appsettings.json‎`

Copy file name to clipboardExpand all lines: APIJSON.NET/APIJSON.NET/appsettings.json

+2-1Lines changed: 2 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -1,6 +1,7 @@`
`1`	`1`	`{`
`2`	`2`	`"ConnectionStrings": {`
`3`		`- "Default": "Server=liaozengbo\\sql2018; Database=Sample;User Id=sa;Password=sa123;"`
	`3`	`+ "SqlServer": "Server=liaozengbo\\sql2018; Database=Sample;User Id=sa;Password=sa123;",`
	`4`	`+ "MySql": "Server=localhost; Database=test; User Id=root;Password=password;charset=UTF8;"`
`4`	`5`	`},`
`5`	`6`	`"Logging": {`
`6`	`7`	`"LogLevel": {`

0 commit comments

Comments

0 (0)

You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.