개정판 a77303ca
dev issue #1227 : mapping ui
Change-Id: I9d7d32a36f6fbc20d70beba2825e4bce2d26c8ed
| DTI_PID/APIDConverter/DB/Project_DB.cs | ||
|---|---|---|
| 16 | 16 |
{
|
| 17 | 17 |
public class Project_DB |
| 18 | 18 |
{
|
| 19 |
const string SPPID_DB_INFO_TABLE = "T_SPPID_CONNECTION_INFO"; |
|
| 20 |
const string SPPID_SYMBOL_MAPPING_TABLE = "T_SPPID_SYMBOL_MAPPING"; |
|
| 21 |
const string SPPID_ATTRIBUTE_MAPPING_TABLE = "T_SPPID_ATTRIBUTE_MAPPING"; |
|
| 22 |
const string SPPID_SETTING_TABLE = "T_SPPID_SETTING_TABLE"; |
|
| 23 |
const string SPPID_LABEL_INFO_TABLE = "T_SPPID_LABEL_INFO"; |
|
| 24 |
const string SPPID_DRAWING_INFO = "T_SPPID_DRAWING_INFO"; |
|
| 25 |
const string SPPID_OPC_INFO = "T_SPPID_OPC_INFO"; |
|
| 19 |
const string APID_SYMBOL_MAPPING_TABLE = "T_APID_SYMBOL_MAPPING"; |
|
| 26 | 20 |
|
| 27 | 21 |
const string LineProperties_TABLE = "LineProperties"; |
| 28 | 22 |
const string LineTypes_TABLE = "LineTypes"; |
| ... | ... | |
| 57 | 51 |
|
| 58 | 52 |
return connection; |
| 59 | 53 |
} |
| 60 |
|
|
| 61 | 54 |
public static bool ConnTestAndCreateTable() |
| 62 | 55 |
{
|
| 63 | 56 |
bool result = false; |
| ... | ... | |
| 73 | 66 |
{
|
| 74 | 67 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
| 75 | 68 |
{
|
| 76 |
|
|
| 69 |
cmd.CommandText = "SELECT NAME FROM sqlite_master WHERE type='table'"; |
|
| 70 |
using (SQLiteDataReader dr = cmd.ExecuteReader()) |
|
| 71 |
using (DataTable dt = new DataTable()) |
|
| 72 |
{
|
|
| 73 |
dt.Load(dr); |
|
| 74 |
|
|
| 75 |
if (dt.Select(string.Format("NAME = '{0}'", APID_SYMBOL_MAPPING_TABLE)).Length == 0)
|
|
| 76 |
{
|
|
| 77 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, NAME TEXT, APID_SYMBOL TEXT)", APID_SYMBOL_MAPPING_TABLE);
|
|
| 78 |
cmd.ExecuteNonQuery(); |
|
| 79 |
} |
|
| 80 |
} |
|
| 77 | 81 |
} |
| 78 | 82 |
result = true; |
| 79 | 83 |
} |
| ... | ... | |
| 99 | 103 |
{
|
| 100 | 104 |
using (SqlCommand cmd = connection.CreateCommand()) |
| 101 | 105 |
{
|
| 102 |
|
|
| 106 |
cmd.CommandText = "SELECT TABLE_NAME AS NAME FROM INFORMATION_SCHEMA.TABLES"; |
|
| 107 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 108 |
using (DataTable dt = new DataTable()) |
|
| 109 |
{
|
|
| 110 |
dt.Load(dr); |
|
| 111 |
|
|
| 112 |
if (dt.Select(string.Format("NAME = '{0}'", APID_SYMBOL_MAPPING_TABLE)).Length == 0)
|
|
| 113 |
{
|
|
| 114 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, NAME varchar(MAX), APID_SYMBOL varchar(MAX))", APID_SYMBOL_MAPPING_TABLE);
|
|
| 115 |
cmd.ExecuteNonQuery(); |
|
| 116 |
} |
|
| 117 |
|
|
| 118 |
} |
|
| 103 | 119 |
} |
| 104 | 120 |
result = true; |
| 105 | 121 |
} |
| ... | ... | |
| 118 | 134 |
|
| 119 | 135 |
return result; |
| 120 | 136 |
} |
| 137 |
public static DataTable GetSymbolMappingTable() |
|
| 138 |
{
|
|
| 139 |
DataTable dt = new DataTable(); |
|
| 140 |
Project_Info projectInfo = Project_Info.GetInstance(); |
|
| 141 |
if (projectInfo.DBType == ID2DB_Type.SQLite) |
|
| 142 |
{
|
|
| 143 |
using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))
|
|
| 144 |
{
|
|
| 145 |
try |
|
| 146 |
{
|
|
| 147 |
connection.Open(); |
|
| 148 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 149 |
{
|
|
| 150 |
cmd.CommandText = string.Format(@" |
|
| 151 |
SELECT s.UID, s.Name, st.Type, sp.APID_SYMBOL FROM {1} as st, {0} as s
|
|
| 152 |
LEFT OUTER JOIN {2} as sp
|
|
| 153 |
ON s.UID = SP.UID |
|
| 154 |
WHERE s.SymbolType_UID = st.UID |
|
| 155 |
ORDER BY st.TYPE ASC;", Symbol_TABLE, SymbolType_TABLE, APID_SYMBOL_MAPPING_TABLE); |
|
| 156 |
using (SQLiteDataReader dr = cmd.ExecuteReader()) |
|
| 157 |
dt.Load(dr); |
|
| 121 | 158 |
|
| 159 |
DataTable dtClone = dt.Clone(); |
|
| 160 |
dtClone.Columns["UID"].DataType = typeof(string); |
|
| 161 |
foreach (DataRow row in dt.Rows) |
|
| 162 |
{
|
|
| 163 |
dtClone.ImportRow(row); |
|
| 164 |
} |
|
| 165 |
dt.Dispose(); |
|
| 166 |
dt = dtClone; |
|
| 167 |
} |
|
| 168 |
connection.Close(); |
|
| 169 |
} |
|
| 170 |
catch (Exception ex) |
|
| 171 |
{
|
|
| 172 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 173 |
} |
|
| 174 |
finally |
|
| 175 |
{
|
|
| 176 |
connection.Dispose(); |
|
| 177 |
} |
|
| 178 |
} |
|
| 179 |
} |
|
| 180 |
else if (projectInfo.DBType == ID2DB_Type.MSSQL) |
|
| 181 |
{
|
|
| 182 |
using (SqlConnection connection = GetSqlConnection()) |
|
| 183 |
{
|
|
| 184 |
try |
|
| 185 |
{
|
|
| 186 |
if (connection != null && connection.State == ConnectionState.Open) |
|
| 187 |
{
|
|
| 188 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 189 |
{
|
|
| 190 |
cmd.CommandText = string.Format(@" |
|
| 191 |
SELECT CONVERT(VARCHAR(255), s.UID) AS UID, s.Name, st.Type, sp.APID_SYMBOL FROM {1} as st, {0} as s
|
|
| 192 |
LEFT OUTER JOIN {2} as sp
|
|
| 193 |
ON CONVERT(VARCHAR(255), s.UID) = CONVERT(VARCHAR(255), SP.UID) |
|
| 194 |
WHERE s.SymbolType_UID = st.UID |
|
| 195 |
ORDER BY st.TYPE ASC;", Symbol_TABLE, SymbolType_TABLE, APID_SYMBOL_MAPPING_TABLE); |
|
| 196 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 197 |
dt.Load(dr); |
|
| 198 |
} |
|
| 199 |
connection.Close(); |
|
| 200 |
} |
|
| 201 |
} |
|
| 202 |
catch (Exception ex) |
|
| 203 |
{
|
|
| 204 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 205 |
} |
|
| 206 |
finally |
|
| 207 |
{
|
|
| 208 |
if (connection != null) |
|
| 209 |
connection.Dispose(); |
|
| 210 |
} |
|
| 211 |
} |
|
| 212 |
} |
|
| 213 |
|
|
| 214 |
return dt; |
|
| 215 |
} |
|
| 216 |
|
|
| 217 |
|
|
| 218 |
|
|
| 219 |
|
|
| 220 |
#region Only ID2 DB |
|
| 122 | 221 |
public static DataTable SelectID2SymbolTable() |
| 123 | 222 |
{
|
| 124 | 223 |
DataTable dt = new DataTable(); |
| ... | ... | |
| 299 | 398 |
|
| 300 | 399 |
return dt; |
| 301 | 400 |
} |
| 302 |
|
|
| 401 |
#endregion |
|
| 303 | 402 |
|
| 304 | 403 |
#region AVEVA |
| 305 | 404 |
public static string GetAvevaConnectionString() |
내보내기 Unified diff
