From 124102d5db2a2ace2f2d8484ceda658829657552 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期六, 06 一月 2024 14:14:52 +0800
Subject: [PATCH] 图片表的sql操作
---
JiangSu/JiangSu.csproj | 4
JiangSu/index.html | 41 ++++++
JiangSu/Web.config | 4
JiangSu/cs/GridDAL.cs | 4
JiangSu/cs/ModelDAL.cs | 36 -----
JiangSu/cs/Tools.cs | 96 ++++++++++++++++
JiangSu/Controllers/ImgController.cs | 90 +++++++++++++++
JiangSu/Models/Img.cs | 2
JiangSu/cs/ImgDAL.cs | 66 ++++++++++
9 files changed, 303 insertions(+), 40 deletions(-)
diff --git a/JiangSu/Controllers/ImgController.cs b/JiangSu/Controllers/ImgController.cs
new file mode 100644
index 0000000..a2e0a22
--- /dev/null
+++ b/JiangSu/Controllers/ImgController.cs
@@ -0,0 +1,90 @@
+锘縰sing JiangSu.cs;
+using JiangSu.Models;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Net;
+using System.Net.Http;
+using System.Web.Http;
+
+namespace JiangSu.Controllers
+{
+ public class ImgController : ApiController
+ {
+ [HttpGet]
+ public List<Img> SelectByPage(string name, int pageSize = 10, int pageIndex = 1)
+ {
+ try
+ {
+ return ImgDAL.SelectByPage(name, pageSize, pageIndex);
+ }
+ catch (Exception ex)
+ {
+ LogOut.Error(ex.Message + "\r\n" + ex.StackTrace);
+ return null;
+ }
+ }
+
+ [HttpGet]
+ public Img SelectById(long id)
+ {
+ try
+ {
+ return ImgDAL.SelectById(id);
+ }
+ catch (Exception ex)
+ {
+ LogOut.Error(ex.Message + "\r\n" + ex.StackTrace);
+ return null;
+ }
+ }
+
+ [HttpGet]
+ public int DeleteByIds([FromUri] List<int> ids)
+ {
+ try
+ {
+ if (null == ids || ids.Count == 0) return 0;
+
+ return ImgDAL.DeleteByIds(ids);
+ }
+ catch (Exception ex)
+ {
+ LogOut.Error(ex.Message + "\r\n" + ex.StackTrace);
+ return 0;
+ }
+ }
+
+ [HttpPost]
+ public int Insert([FromBody] Img img)
+ {
+ try
+ {
+ if (null == img) return 0;
+
+ return ImgDAL.Insert(img);
+ }
+ catch (Exception ex)
+ {
+ LogOut.Error(ex.Message + "\r\n" + ex.StackTrace);
+ return 0;
+ }
+ }
+
+ [HttpPost]
+ public int UpdateById([FromBody] Img img)
+ {
+ try
+ {
+ if (null == img) return 0;
+
+ return ImgDAL.UpdateById(img);
+ }
+ catch (Exception ex)
+ {
+ LogOut.Error(ex.Message + "\r\n" + ex.StackTrace);
+ return 0;
+ }
+ }
+ }
+}
diff --git a/JiangSu/JiangSu.csproj b/JiangSu/JiangSu.csproj
index 0c6c6b0..3b68141 100644
--- a/JiangSu/JiangSu.csproj
+++ b/JiangSu/JiangSu.csproj
@@ -138,19 +138,23 @@
<Compile Include="App_Start\RouteConfig.cs" />
<Compile Include="App_Start\WebApiConfig.cs" />
<Compile Include="Controllers\HomeController.cs" />
+ <Compile Include="Controllers\ImgController.cs" />
<Compile Include="Controllers\OpController.cs" />
<Compile Include="Controllers\ValuesController.cs" />
<Compile Include="Controllers\GridController.cs" />
<Compile Include="cs\GridDAL.cs" />
+ <Compile Include="cs\ImgDAL.cs" />
<Compile Include="cs\LogOut.cs" />
<Compile Include="cs\ModelDAL.cs" />
<Compile Include="cs\ModelHandler.cs" />
<Compile Include="cs\PostgreHelper.cs" />
<Compile Include="cs\SQLiteHelper.cs" />
+ <Compile Include="cs\Tools.cs" />
<Compile Include="Global.asax.cs">
<DependentUpon>Global.asax</DependentUpon>
</Compile>
<Compile Include="Models\Grid.cs" />
+ <Compile Include="Models\Img.cs" />
<Compile Include="Models\Model.cs" />
<Compile Include="Properties\AssemblyInfo.cs" />
</ItemGroup>
diff --git a/JiangSu/Models/Img.cs b/JiangSu/Models/Img.cs
index baa866b..16c3c03 100644
--- a/JiangSu/Models/Img.cs
+++ b/JiangSu/Models/Img.cs
@@ -15,6 +15,8 @@
public string path { set; get; }
+ public string json { set; get; }
+
public string geom { set; get; }
}
}
diff --git a/JiangSu/Web.config b/JiangSu/Web.config
index 44255b2..aab6157 100644
--- a/JiangSu/Web.config
+++ b/JiangSu/Web.config
@@ -1,8 +1,5 @@
锘�<?xml version="1.0" encoding="utf-8"?>
<configuration>
- <appSettings>
- <add key="pgConn" value="Server=127.0.0.1;Port=5433;Database=etown;User Id=postgres;Password=postgres;"/>
- </appSettings>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
@@ -11,6 +8,7 @@
<add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnet-JiangSu-20231222105949.mdf;Initial Catalog=aspnet-JiangSu-20231222105949;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
<appSettings>
+ <add key="pgConn" value="Server=127.0.0.1;Port=5433;Database=etown;User Id=postgres;Password=postgres;"/>
<add key="webpages:Version" value="2.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="PreserveLoginUrl" value="true" />
diff --git a/JiangSu/cs/GridDAL.cs b/JiangSu/cs/GridDAL.cs
index c28ec92..8805682 100644
--- a/JiangSu/cs/GridDAL.cs
+++ b/JiangSu/cs/GridDAL.cs
@@ -54,7 +54,7 @@
public static int Insert(Grid grid)
{
string sql = "insert into grid (json) values (@json);select last_insert_rowid();";
- SQLiteParameter[] sqlParams = ModelDAL.GetParams<Grid>(sql, grid);
+ SQLiteParameter[] sqlParams = Tools.GetSQLiteParams<Grid>(sql, grid);
object obj = SQLiteHelper.ExecuteScalar(sql, sqlParams);
return null == obj ? 0 : Convert.ToInt32(obj);
@@ -63,7 +63,7 @@
public static int UpdateById(Grid grid)
{
string sql = "update grid set json = @json where id = @id";
- SQLiteParameter[] sqlParams = ModelDAL.GetParams<Grid>(sql, grid);
+ SQLiteParameter[] sqlParams = Tools.GetSQLiteParams<Grid>(sql, grid);
return SQLiteHelper.ExecuteNonQuery(sql, sqlParams);
}
diff --git a/JiangSu/cs/ImgDAL.cs b/JiangSu/cs/ImgDAL.cs
index ea345d8..62d3ebf 100644
--- a/JiangSu/cs/ImgDAL.cs
+++ b/JiangSu/cs/ImgDAL.cs
@@ -1,5 +1,9 @@
-锘縰sing System;
+锘縰sing JiangSu.Models;
+using Npgsql;
+using System;
using System.Collections.Generic;
+using System.Data;
+using System.Data.Common;
using System.Linq;
using System.Web;
@@ -7,5 +11,65 @@
{
public class ImgDAL
{
+ public static List<Img> SelectByPage(string name, int pageSize = 10, int pageIndex = 1)
+ {
+ bool flag = string.IsNullOrWhiteSpace(name);
+ string sql = string.Format("select id, name, path, json, st_astext(geom) geom from sd.img {0} order by id limit {1} offset {2}", flag ? "" : "where upper(name) like @name", pageSize, pageSize * (pageIndex - 1));
+
+ DataTable dt = flag ? Tools.PGHelper.GetDataTable(sql) : Tools.PGHelper.GetDataTable(sql, new NpgsqlParameter("@name", "%" + name.Trim().ToUpper() + "%"));
+ if (null == dt || dt.Rows.Count == 0)
+ {
+ return null;
+ }
+
+ List<Img> list = ModelHandler.FillModel<Img>(dt);
+
+ return list;
+ }
+
+ public static Img SelectById(long id)
+ {
+ string sql = "select id, name, path, json, st_astext(geom) geom from sd.img where id = @id";
+
+ DbParameter param = new NpgsqlParameter("@id", id);
+
+ DataTable dt = Tools.PGHelper.GetDataTable(sql, param);
+ if (null == dt || dt.Rows.Count == 0)
+ {
+ return null;
+ }
+
+ List<Img> list = ModelHandler.FillModel<Img>(dt);
+
+ return null == list || list.Count == 0 ? null : list[0];
+ }
+
+ public static int DeleteByIds(List<int> ids)
+ {
+ string str = string.Join(",", ids.ToArray());
+ string sql = string.Format("delete from sd.img where id in ({0})", str);
+
+ return Tools.PGHelper.ExecuteNonQuery(sql);
+ }
+
+ public static int Insert(Img img)
+ {
+ string geom = string.IsNullOrWhiteSpace(img.geom) ? "null" : string.Format("ST_GeomFromText('{0}')", img.geom);
+ string sql = string.Format("insert into sd.img (name, path, json, geom) values (@name, @path, @json, {0}) returning id", geom);
+ DbParameter[] sqlParams = Tools.GetPGParams<Img>(sql, img);
+
+ object obj = Tools.PGHelper.GetScalar(sql, sqlParams);
+
+ return null == obj ? 0 : Convert.ToInt32(obj);
+ }
+
+ public static int UpdateById(Img img)
+ {
+ string geom = string.IsNullOrWhiteSpace(img.geom) ? "" : string.Format(", geom = ST_GeomFromText('{0}')", img.geom);
+ string sql = string.Format("update sd.img set name = @name, path = @path, json = @json{0} where id = @id", geom);
+ DbParameter[] sqlParams = Tools.GetPGParams<Img>(sql, img);
+
+ return Tools.PGHelper.ExecuteNonQuery(sql, sqlParams);
+ }
}
}
diff --git a/JiangSu/cs/ModelDAL.cs b/JiangSu/cs/ModelDAL.cs
index e21ad70..9267467 100644
--- a/JiangSu/cs/ModelDAL.cs
+++ b/JiangSu/cs/ModelDAL.cs
@@ -11,38 +11,6 @@
{
public class ModelDAL
{
- /// <summary>
- /// 鑾峰彇鍙傛暟鍒楄〃
- /// </summary>
- public static SQLiteParameter[] GetParams<T>(string sql, T t)
- {
- List<SQLiteParameter> list = new List<SQLiteParameter>();
- Type tType = typeof(T);
- BindingFlags flags = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
-
- int start = sql.IndexOf("@");
- while (start != -1)
- {
- int end = sql.IndexOf(",", start);
- if (end == -1) end = sql.IndexOf(")", start);
- if (end == -1) end = sql.IndexOf(" ", start);
- if (end == -1) end = sql.Length;
-
- string name = sql.Substring(start + 1, end - start - 1);
- PropertyInfo pi = tType.GetProperty(name, flags);
- if (pi != null)
- {
- object value = pi.GetValue(t, null);
- SQLiteParameter dp = new SQLiteParameter("@" + name, value);
- list.Add(dp);
- }
-
- start = sql.IndexOf("@", end);
- }
-
- return list.ToArray();
- }
-
public static List<Model> SelectByPage(string name, int pageSize = 10, int pageIndex = 1)
{
bool flag = string.IsNullOrWhiteSpace(name);
@@ -88,7 +56,7 @@
public static int Insert(Model model)
{
string sql = "insert into model (name, json) values (@name, @json);select last_insert_rowid();";
- SQLiteParameter[] sqlParams = GetParams<Model>(sql, model);
+ SQLiteParameter[] sqlParams = Tools.GetSQLiteParams<Model>(sql, model);
//return SQLiteHelper.ExecuteNonQuery(sql, sqlParams);
@@ -99,7 +67,7 @@
public static int UpdateById(Model model)
{
string sql = "update model set name = @name, json = @json where id = @id";
- SQLiteParameter[] sqlParams = GetParams<Model>(sql, model);
+ SQLiteParameter[] sqlParams = Tools.GetSQLiteParams<Model>(sql, model);
return SQLiteHelper.ExecuteNonQuery(sql, sqlParams);
}
diff --git a/JiangSu/cs/Tools.cs b/JiangSu/cs/Tools.cs
new file mode 100644
index 0000000..69e3b2e
--- /dev/null
+++ b/JiangSu/cs/Tools.cs
@@ -0,0 +1,96 @@
+锘縰sing Npgsql;
+using System;
+using System.Collections.Generic;
+using System.Data.Common;
+using System.Data.SQLite;
+using System.Linq;
+using System.Reflection;
+using System.Web;
+
+namespace JiangSu.cs
+{
+ public class Tools
+ {
+ private static PostgreHelper _pgHelper;
+
+ /// <summary>
+ /// DB甯姪绫�
+ /// </summary>
+ public static PostgreHelper PGHelper
+ {
+ get
+ {
+ if (null == _pgHelper)
+ {
+ _pgHelper = new PostgreHelper();
+ }
+
+ return _pgHelper;
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇鍙傛暟鍒楄〃
+ /// </summary>
+ public static DbParameter[] GetPGParams<T>(string sql, T t)
+ {
+ List<DbParameter> list = new List<DbParameter>();
+ Type tType = typeof(T);
+ BindingFlags flags = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
+
+ int start = sql.IndexOf("@");
+ while (start != -1)
+ {
+ int end = sql.IndexOf(",", start);
+ if (end == -1) end = sql.IndexOf(")", start);
+ if (end == -1) end = sql.IndexOf(" ", start);
+ if (end == -1) end = sql.Length;
+
+ string name = sql.Substring(start + 1, end - start - 1);
+ PropertyInfo pi = tType.GetProperty(name, flags);
+ if (pi != null)
+ {
+ object value = pi.GetValue(t, null);
+ NpgsqlParameter dp = new NpgsqlParameter("@" + name, value);
+ list.Add(dp);
+ }
+
+ start = sql.IndexOf("@", end);
+ }
+
+ return list.ToArray();
+ }
+
+ /// <summary>
+ /// 鑾峰彇鍙傛暟鍒楄〃
+ /// </summary>
+ public static SQLiteParameter[] GetSQLiteParams<T>(string sql, T t)
+ {
+ List<SQLiteParameter> list = new List<SQLiteParameter>();
+ Type tType = typeof(T);
+ BindingFlags flags = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
+
+ int start = sql.IndexOf("@");
+ while (start != -1)
+ {
+ int end = sql.IndexOf(",", start);
+ if (end == -1) end = sql.IndexOf(")", start);
+ if (end == -1) end = sql.IndexOf(" ", start);
+ if (end == -1) end = sql.Length;
+
+ string name = sql.Substring(start + 1, end - start - 1);
+ PropertyInfo pi = tType.GetProperty(name, flags);
+ if (pi != null)
+ {
+ object value = pi.GetValue(t, null);
+ SQLiteParameter dp = new SQLiteParameter("@" + name, value);
+ list.Add(dp);
+ }
+
+ start = sql.IndexOf("@", end);
+ }
+
+ return list.ToArray();
+ }
+ }
+}
diff --git a/JiangSu/index.html b/JiangSu/index.html
index f353162..0b7ab2a 100644
--- a/JiangSu/index.html
+++ b/JiangSu/index.html
@@ -69,6 +69,35 @@
return JSON.stringify(model);
}
+ function InsertImg() {
+ var data = getImg();
+
+ ajax("Img/Insert", "POST", data, null, null, function (rs) {
+ console.log(rs);
+ alert(rs);
+ });
+ }
+
+ function UpdateImgById() {
+ var data = getImg();
+
+ ajax("Img/UpdateById", "POST", data, null, null, function (rs) {
+ console.log(rs);
+ alert(rs);
+ });
+ }
+
+ function getImg() {
+ var model = {
+ id: $("#iid").val(),
+ name: $("#iname").val(),
+ path: $("#ipath").val(),
+ json: $("#ijson").val()
+ };
+
+ return JSON.stringify(model);
+ }
+
function ajax(url, type, data, dataType, contentType, fn) {
$.ajax({
url: url,
@@ -111,6 +140,18 @@
json: <textarea id="gjson" style="width: 1024px;height: 70px;">{ "type": "Feature", "properties": { "ID": "12", "NAME": "Area8", "REMARK": "" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 120.24179180542139, 32.005615085853492, 13.770 ], [ 120.24175240823769, 32.005610442630996, 13.770 ], [ 120.24173428512847, 32.005722106674966, 13.770 ], [ 120.24177368235908, 32.005726749902777, 13.770 ], [ 120.24179180542139, 32.005615085853492, 13.770 ] ] ] } }</textarea> <br />
<input type="button" value="InsertGrid" onclick="InsertGrid();" />
<input type="button" value="UpdateGridById" onclick="UpdateGridById();" /><br /> <br />
+
+
+ 鍥剧墖锛�<a href="http://localhost/JiangSu/Img/SelectByPage?pageSize=10&pageIndex=1&name=">SelectByPage?pageSize=10&pageIndex=1&name=</a> <span style="width: 50px;"> </span>
+ <a href="http://localhost/JiangSu/Img/SelectById?id=1">SelectById?id=1</a> <span style="width: 50px;"> </span>
+ <a href="http://localhost/JiangSu/Img/DeleteByIds?ids=3&ids=0">DeleteByIds?ids=3&ids=0</a> <br />
+
+ id: <input id="iid" value="1" /> <br />
+ name: <input id="iname" value="1.jpg"/> <br />
+ path: <input id="ipath" value="Images\1.jpg"/> <br />
+ json: <input id="ijson" value="{}"/> <br />
+ <input type="button" value="InsertImg" onclick="InsertImg();" />
+ <input type="button" value="UpdateImgById" onclick="UpdateImgById();" /><br /> <br />
</form>
</body>
</html>
--
Gitblit v1.9.3