1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
| using DataLoader.Model;
| using Npgsql;
| using System;
| using System.Collections.Generic;
| using System.Data;
| using System.Data.Common;
| using System.Linq;
|
| namespace DataLoader.CS
| {
| /// <summary>
| /// 数据库帮助类
| /// </summary>
| public class DBHelper
| {
| // id, eventid, metaid, verid, name, type, guid, path, sizes, tab, rows, create_user, create_time, update_user, update_time, bak, geom, layer, depcode, dircode, ismeta, sensortype, acq_time, resolution, gridsize, coor_sys, epsg, h_datum, mata_type, bands, band_type, ct
| private const string insertMeta = "insert into lf.sys_meta (eventid, metaid, verid, name, type, guid, path, sizes, tab, rows, create_user, create_time, bak, geom, layer, depcode, dircode, ismeta, sensortype, acq_time, resolution, gridsize, coor_sys, epsg, h_datum, mata_type, bands, band_type, ct, min, max) values (@eventid, @metaid, @verid, @name, @type, @guid, @path, @sizes, @tab, @rows, @create_user, now(), @bak, {0}, @layer, @depcode, @dircode, @ismeta, @sensortype, @acq_time, @resolution, @gridsize, @coor_sys, @epsg, @h_datum, @mata_type, @bands, @band_type, @ct, @min, @max) returning id";
|
| /// <summary>
| /// 插入元数据
| /// </summary>
| public static int InsertMeta(SysMeta meta)
| {
| string sql = string.Format(insertMeta, string.IsNullOrEmpty(meta.geom) ? "null" : meta.geom);
| List<DbParameter> args = Tools.GetParams<SysMeta>(sql, meta);
|
| return Tools.DBHelper.GetIntScalar(sql, args.ToArray());
| }
|
| /// <summary>
| /// 插入元数据
| /// </summary>
| public static int InsertMetas(List<SysMeta> list)
| {
| PostgreHelper db = Tools.DBHelper;
|
| int count = 0;
| foreach (SysMeta meta in list)
| {
| string sql = string.Format(insertMeta, string.IsNullOrEmpty(meta.geom) ? "null" : meta.geom);
| List<DbParameter> args = Tools.GetParams<SysMeta>(sql, meta);
|
| int id = db.GetIntScalar(sql, args.ToArray());
| if (id > 0) count++;
| }
|
| return count;
| }
|
| /// <summary>
| /// 根据GUID查询路径
| /// </summary>
| public static string GetFilePathByGuid(string guid)
| {
| string sql = "select path from lf.sys_meta where guid = @guid limit 1";
|
| DbParameter dp = new NpgsqlParameter("@guid", guid);
| object obj = Tools.DBHelper.GetScalar(sql, dp);
|
| return obj == null ? null : obj.ToString();
| }
|
| /// <summary>
| /// 文件是/否存在
| /// </summary>
| public static bool IsFileExists(string guid)
| {
| string sql = "select count(*) from lf.sys_meta where guid = @guid";
|
| DbParameter dp = new NpgsqlParameter("@guid", guid);
| object obj = Tools.DBHelper.GetScalar(sql, dp);
|
| return obj != null && Convert.ToInt32(obj) > 0;
| }
|
| /// <summary>
| /// 获取目录列表
| /// </summary>
| public static List<SysDir> GetDirList()
| {
| string sql = "select a.*, fn_get_fullname(a.code, 2) fullName from lf.sys_dir a order by pid, order_num, id";
| DataTable dt = Tools.DBHelper.GetDataTable(sql);
| if (null == dt || dt.Rows.Count == 0) return null;
|
| List<SysDir> list = ModelHandler.FillModel<SysDir>(dt);
|
| return list;
| }
|
| /// <summary>
| /// 根据用户ID查询单位编码
| /// </summary>
| public static string SelectDepCodeByUid(int userId)
| {
| string sql = string.Format("select depcode from lf.sys_user where id = {0}", userId);
|
| object obj = Tools.DBHelper.GetScalar(sql);
|
| return null == obj ? null : obj.ToString();
| }
| }
| }
|
|
