개정판 faba1fc7
dev issue #1230 : edit aveva AttributeInfo
Change-Id: If3a14f65dc236eb73a3af73b0d26104ca0f91aa6
| DTI_PID/APIDConverter/DB/Project_DB.cs | ||
|---|---|---|
| 19 | 19 |
const string APID_SYMBOL_MAPPING_TABLE = "T_APID_SYMBOL_MAPPING"; |
| 20 | 20 |
const string APID_OPC_MAPPING_TABLE = "T_APID_OPC_MAPPING"; |
| 21 | 21 |
const string APID_ATTRIBUTE_MAPPING_TABLE = "T_APID_ATTRIBUTE_MAPPING"; |
| 22 |
const string APID_ATTRIBUTE_TABLE = "T_APID_ATTRIBUTE"; |
|
| 22 | 23 |
|
| 23 | 24 |
const string LineProperties_TABLE = "LineProperties"; |
| 24 | 25 |
const string LineTypes_TABLE = "LineTypes"; |
| ... | ... | |
| 87 | 88 |
} |
| 88 | 89 |
if (dt.Select(string.Format("NAME = '{0}'", APID_ATTRIBUTE_MAPPING_TABLE)).Length == 0)
|
| 89 | 90 |
{
|
| 90 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, APID_ATTRIBUTE TEXT, APID_ATTRIBUTE_TYPE)", APID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 91 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, APID_ATTRIBUTE TEXT, APID_ATTRIBUTE_TYPE TEXT)", APID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 92 |
cmd.ExecuteNonQuery(); |
|
| 93 |
} |
|
| 94 |
if (dt.Select(string.Format("NAME = '{0}'", APID_ATTRIBUTE_TABLE)).Length == 0)
|
|
| 95 |
{
|
|
| 96 |
cmd.CommandText = string.Format("CREATE TABLE {0} (LEVEL1 TEXT, LEVEL2 TEXT, ATTRIBUTE_KEY TEXT, ATTRIBUTE_NAME TEXT)", APID_ATTRIBUTE_TABLE);
|
|
| 91 | 97 |
cmd.ExecuteNonQuery(); |
| 92 | 98 |
} |
| 93 | 99 |
} |
| ... | ... | |
| 137 | 143 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, APID_ATTRIBUTE varchar(MAX), APID_ATTRIBUTE_TYPE varchar(MAX))", APID_ATTRIBUTE_MAPPING_TABLE);
|
| 138 | 144 |
cmd.ExecuteNonQuery(); |
| 139 | 145 |
} |
| 146 |
if (dt.Select(string.Format("NAME = '{0}'", APID_ATTRIBUTE_TABLE)).Length == 0)
|
|
| 147 |
{
|
|
| 148 |
cmd.CommandText = string.Format("CREATE TABLE {0} (LEVEL1 varchar(255), LEVEL2 varchar(255), ATTRIBUTE_KEY varchar(255), ATTRIBUTE_NAME varchar(255))", APID_ATTRIBUTE_TABLE);
|
|
| 149 |
cmd.ExecuteNonQuery(); |
|
| 150 |
} |
|
| 140 | 151 |
} |
| 141 | 152 |
} |
| 142 | 153 |
result = true; |
| ... | ... | |
| 863 | 874 |
|
| 864 | 875 |
return true; |
| 865 | 876 |
} |
| 877 |
public static bool InsertAPIDAttribute(List<Tuple<string, string, string, string>> datas) |
|
| 878 |
{
|
|
| 879 |
Project_Info projectInfo = Project_Info.GetInstance(); |
|
| 880 |
if (projectInfo.DBType == ID2DB_Type.SQLite) |
|
| 881 |
{
|
|
| 882 |
using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))
|
|
| 883 |
{
|
|
| 884 |
try |
|
| 885 |
{
|
|
| 886 |
connection.Open(); |
|
| 887 |
using (SQLiteTransaction transaction = connection.BeginTransaction()) |
|
| 888 |
{
|
|
| 889 |
try |
|
| 890 |
{
|
|
| 891 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 892 |
{
|
|
| 893 |
foreach (var item in datas) |
|
| 894 |
{
|
|
| 895 |
cmd.Parameters.Clear(); |
|
| 896 |
cmd.CommandText = string.Format(@" |
|
| 897 |
IF EXISTS (SELECT * FROM {0} WHERE LEVEL1 = '{1}' AND LEVEL2 = '{2}' AND ATTRIBUTE_KEY = '{3}')
|
|
| 898 |
UPDATE {0} SET ATTRIBUTE_NAME = @ATTRIBUTE_NAME WHERE LEVEL1 = '{1}' AND LEVEL2 = '{2}' AND ATTRIBUTE_KEY = '{3}'
|
|
| 899 |
ELSE |
|
| 900 |
INSERT INTO {0} (LEVEL1, LEVEL2, ATTRIBUTE_KEY, ATTRIBUTE_NAME) VALUES (@LEVEL1, @LEVEL2, @ATTRIBUTE_KEY, @ATTRIBUTE_NAME)",
|
|
| 901 |
APID_ATTRIBUTE_TABLE, item.Item1, item.Item2, item.Item3); |
|
| 902 |
cmd.Parameters.AddWithValue("@LEVEL1", item.Item1);
|
|
| 903 |
cmd.Parameters.AddWithValue("@LEVEL2", item.Item2);
|
|
| 904 |
cmd.Parameters.AddWithValue("@ATTRIBUTE_KEY", item.Item3);
|
|
| 905 |
cmd.Parameters.AddWithValue("@ATTRIBUTE_NAME", item.Item4);
|
|
| 906 |
cmd.ExecuteNonQuery(); |
|
| 907 |
} |
|
| 908 |
} |
|
| 909 |
transaction.Commit(); |
|
| 910 |
connection.Close(); |
|
| 911 |
} |
|
| 912 |
catch (Exception ex) |
|
| 913 |
{
|
|
| 914 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 915 |
transaction.Rollback(); |
|
| 916 |
return false; |
|
| 917 |
} |
|
| 918 |
finally |
|
| 919 |
{
|
|
| 920 |
transaction.Dispose(); |
|
| 921 |
} |
|
| 922 |
} |
|
| 923 |
} |
|
| 924 |
catch (Exception ex) |
|
| 925 |
{
|
|
| 926 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 927 |
return false; |
|
| 928 |
} |
|
| 929 |
finally |
|
| 930 |
{
|
|
| 931 |
connection.Dispose(); |
|
| 932 |
} |
|
| 933 |
} |
|
| 934 |
} |
|
| 935 |
else if (projectInfo.DBType == ID2DB_Type.MSSQL) |
|
| 936 |
{
|
|
| 937 |
using (SqlConnection connection = GetSqlConnection()) |
|
| 938 |
{
|
|
| 939 |
try |
|
| 940 |
{
|
|
| 941 |
if (connection != null && connection.State == ConnectionState.Open) |
|
| 942 |
{
|
|
| 943 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 944 |
{
|
|
| 945 |
foreach (var item in datas) |
|
| 946 |
{
|
|
| 947 |
cmd.Parameters.Clear(); |
|
| 948 |
cmd.CommandText = string.Format(@" |
|
| 949 |
IF EXISTS (SELECT * FROM {0} WHERE LEVEL1 = '{1}' AND LEVEL2 = '{2}' AND ATTRIBUTE_KEY = '{3}')
|
|
| 950 |
UPDATE {0} SET ATTRIBUTE_NAME = @ATTRIBUTE_NAME WHERE LEVEL1 = '{1}' AND LEVEL2 = '{2}' AND ATTRIBUTE_KEY = '{3}'
|
|
| 951 |
ELSE |
|
| 952 |
INSERT INTO {0} (LEVEL1, LEVEL2, ATTRIBUTE_KEY, ATTRIBUTE_NAME) VALUES (@LEVEL1, @LEVEL2, @ATTRIBUTE_KEY, @ATTRIBUTE_NAME)",
|
|
| 953 |
APID_ATTRIBUTE_TABLE, item.Item1, item.Item2, item.Item3); |
|
| 954 |
cmd.Parameters.AddWithValue("@LEVEL1", item.Item1);
|
|
| 955 |
cmd.Parameters.AddWithValue("@LEVEL2", item.Item2);
|
|
| 956 |
cmd.Parameters.AddWithValue("@ATTRIBUTE_KEY", item.Item3);
|
|
| 957 |
cmd.Parameters.AddWithValue("@ATTRIBUTE_NAME", item.Item4);
|
|
| 958 |
cmd.ExecuteNonQuery(); |
|
| 959 |
} |
|
| 960 |
} |
|
| 961 |
connection.Close(); |
|
| 962 |
} |
|
| 963 |
} |
|
| 964 |
catch (Exception ex) |
|
| 965 |
{
|
|
| 966 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 967 |
return false; |
|
| 968 |
} |
|
| 969 |
finally |
|
| 970 |
{
|
|
| 971 |
if (connection != null) |
|
| 972 |
connection.Dispose(); |
|
| 973 |
} |
|
| 974 |
} |
|
| 975 |
} |
|
| 976 |
|
|
| 977 |
return true; |
|
| 978 |
} |
|
| 866 | 979 |
public static DataTable GetProjectAttribute() |
| 867 | 980 |
{
|
| 868 | 981 |
DataTable dt = new DataTable(); |
| ... | ... | |
| 1132 | 1245 |
|
| 1133 | 1246 |
return dt; |
| 1134 | 1247 |
} |
| 1248 |
public static DataTable GetAPIDAttributeTable() |
|
| 1249 |
{
|
|
| 1250 |
DataTable dt = new DataTable(); |
|
| 1251 |
Project_Info projectInfo = Project_Info.GetInstance(); |
|
| 1252 |
if (projectInfo.DBType == ID2DB_Type.SQLite) |
|
| 1253 |
{
|
|
| 1254 |
using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))
|
|
| 1255 |
{
|
|
| 1256 |
try |
|
| 1257 |
{
|
|
| 1258 |
connection.Open(); |
|
| 1259 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 1260 |
{
|
|
| 1261 |
cmd.CommandText = string.Format("SELECT * FROM {0}", APID_ATTRIBUTE_TABLE);
|
|
| 1262 |
using (SQLiteDataReader dr = cmd.ExecuteReader()) |
|
| 1263 |
dt.Load(dr); |
|
| 1264 |
} |
|
| 1265 |
connection.Close(); |
|
| 1266 |
} |
|
| 1267 |
catch (Exception ex) |
|
| 1268 |
{
|
|
| 1269 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 1270 |
} |
|
| 1271 |
finally |
|
| 1272 |
{
|
|
| 1273 |
connection.Dispose(); |
|
| 1274 |
} |
|
| 1275 |
} |
|
| 1276 |
} |
|
| 1277 |
else if (projectInfo.DBType == ID2DB_Type.MSSQL) |
|
| 1278 |
{
|
|
| 1279 |
using (SqlConnection connection = GetSqlConnection()) |
|
| 1280 |
{
|
|
| 1281 |
try |
|
| 1282 |
{
|
|
| 1283 |
if (connection != null && connection.State == ConnectionState.Open) |
|
| 1284 |
{
|
|
| 1285 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1286 |
{
|
|
| 1287 |
cmd.CommandText = string.Format("SELECT * FROM {0}", APID_ATTRIBUTE_TABLE);
|
|
| 1288 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1289 |
dt.Load(dr); |
|
| 1290 |
} |
|
| 1291 |
connection.Close(); |
|
| 1292 |
} |
|
| 1293 |
} |
|
| 1294 |
catch (Exception ex) |
|
| 1295 |
{
|
|
| 1296 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 1297 |
} |
|
| 1298 |
finally |
|
| 1299 |
{
|
|
| 1300 |
if (connection != null) |
|
| 1301 |
connection.Dispose(); |
|
| 1302 |
} |
|
| 1303 |
} |
|
| 1304 |
} |
|
| 1305 |
|
|
| 1306 |
return dt; |
|
| 1307 |
} |
|
| 1135 | 1308 |
#endregion |
| 1136 | 1309 |
|
| 1137 | 1310 |
#region AVEVA |
내보내기 Unified diff
