개정판 d2a7bef1
dev issue #1203 : MS SQL Query 수정
Change-Id: Iac05c4897ced9c43bda7cfdb4ad00efc90be8521
| DTI_PID/SPPIDConverter/AutoModeling.cs | ||
|---|---|---|
| 202 | 202 |
} |
| 203 | 203 |
} |
| 204 | 204 |
} |
| 205 |
|
|
| 206 | 205 |
private void RunSymbolModeling() |
| 207 | 206 |
{
|
| 208 | 207 |
SplashScreenManager.Default.SendCommand(SPPIDSplashScreen.SplashScreenCommand.SetAllProgress, document.SYMBOLS.Count); |
| DTI_PID/SPPIDConverter/DB/Project_DB.cs | ||
|---|---|---|
| 184 | 184 |
|
| 185 | 185 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_DB_INFO_TABLE)).Length == 0)
|
| 186 | 186 |
{
|
| 187 |
cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString varchar(255))", SPPID_DB_INFO_TABLE);
|
|
| 187 |
cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString varchar(MAX))", SPPID_DB_INFO_TABLE);
|
|
| 188 | 188 |
cmd.ExecuteNonQuery(); |
| 189 | 189 |
} |
| 190 | 190 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_SETTING_TABLE)).Length == 0)
|
| 191 | 191 |
{
|
| 192 |
cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString varchar(255), SettingType varchar(255))", SPPID_SETTING_TABLE);
|
|
| 192 |
cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString varchar(MAX), SettingType varchar(MAX))", SPPID_SETTING_TABLE);
|
|
| 193 | 193 |
cmd.ExecuteNonQuery(); |
| 194 | 194 |
} |
| 195 | 195 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_SYMBOL_MAPPING_TABLE)).Length == 0)
|
| 196 | 196 |
{
|
| 197 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, NAME varchar(255), SPPID_SYMBOL_PATH varchar(255), LEADERLINE BIT)", SPPID_SYMBOL_MAPPING_TABLE);
|
|
| 197 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, NAME varchar(MAX), SPPID_SYMBOL_PATH varchar(MAX), LEADERLINE BIT)", SPPID_SYMBOL_MAPPING_TABLE);
|
|
| 198 | 198 |
cmd.ExecuteNonQuery(); |
| 199 | 199 |
} |
| 200 | 200 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_ATTRIBUTE_MAPPING_TABLE)).Length == 0)
|
| 201 | 201 |
{
|
| 202 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, SPPID_ATTRIBUTE varchar(255))", SPPID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 202 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, SPPID_ATTRIBUTE varchar(MAX))", SPPID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 203 | 203 |
cmd.ExecuteNonQuery(); |
| 204 | 204 |
} |
| 205 | 205 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_LABEL_INFO_TABLE)).Length == 0)
|
| ... | ... | |
| 209 | 209 |
} |
| 210 | 210 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_DRAWING_INFO)).Length == 0)
|
| 211 | 211 |
{
|
| 212 |
cmd.CommandText = string.Format("CREATE TABLE {0} (ID2_DRAWING_UID varchar(255) PRIMARY KEY, PATH varchar(255), DRAWINGNUMBER varchar(255), DRAWINGNAME varchar(255))", SPPID_DRAWING_INFO);
|
|
| 212 |
cmd.CommandText = string.Format("CREATE TABLE {0} (ID2_DRAWING_UID varchar(255) PRIMARY KEY, PATH varchar(MAX), DRAWINGNUMBER varchar(MAX), DRAWINGNAME varchar(MAX))", SPPID_DRAWING_INFO);
|
|
| 213 | 213 |
cmd.ExecuteNonQuery(); |
| 214 | 214 |
} |
| 215 | 215 |
if (dt.Select(string.Format("NAME = '{0}'", SPPID_OPC_INFO)).Length == 0)
|
| 216 | 216 |
{
|
| 217 |
cmd.CommandText = string.Format("CREATE TABLE {0} (ID2_OPC_UID varchar(255) PRIMARY KEY, SPPID_OPC_MODELITEM_ID varchar(255), ID2_DRAWING_UID varchar(255), ATTRIBUTES varchar(255), PAIRED BIT)", SPPID_OPC_INFO);
|
|
| 217 |
cmd.CommandText = string.Format("CREATE TABLE {0} (ID2_OPC_UID varchar(255) PRIMARY KEY, SPPID_OPC_MODELITEM_ID varchar(MAX), ID2_DRAWING_UID varchar(MAX), ATTRIBUTES varchar(MAX), PAIRED BIT)", SPPID_OPC_INFO);
|
|
| 218 | 218 |
cmd.ExecuteNonQuery(); |
| 219 | 219 |
} |
| 220 | 220 |
} |
| ... | ... | |
| 280 | 280 |
{
|
| 281 | 281 |
if (connection != null && connection.State == ConnectionState.Open) |
| 282 | 282 |
{
|
| 283 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 284 |
{
|
|
| 285 |
cmd.CommandText = string.Format("DELETE FROM {0}", SPPID_DB_INFO_TABLE);
|
|
| 286 |
cmd.ExecuteNonQuery(); |
|
| 283 | 287 |
|
| 288 |
cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@jsonString)", SPPID_DB_INFO_TABLE);
|
|
| 289 |
cmd.Parameters.AddWithValue("@jsonString", jsonString);
|
|
| 290 |
cmd.ExecuteNonQuery(); |
|
| 291 |
} |
|
| 292 |
connection.Close(); |
|
| 293 |
} |
|
| 294 |
else |
|
| 295 |
{
|
|
| 296 |
return false; |
|
| 284 | 297 |
} |
| 285 | 298 |
} |
| 286 | 299 |
catch (Exception ex) |
| 287 | 300 |
{
|
| 288 |
|
|
| 301 |
return false; |
|
| 289 | 302 |
} |
| 290 | 303 |
finally |
| 291 | 304 |
{
|
| ... | ... | |
| 336 | 349 |
{
|
| 337 | 350 |
if (connection != null && connection.State == ConnectionState.Open) |
| 338 | 351 |
{
|
| 339 |
|
|
| 352 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 353 |
{
|
|
| 354 |
cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_DB_INFO_TABLE);
|
|
| 355 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 356 |
dt.Load(dr); |
|
| 357 |
} |
|
| 358 |
connection.Close(); |
|
| 340 | 359 |
} |
| 341 | 360 |
} |
| 342 | 361 |
catch (Exception ex) |
| ... | ... | |
| 398 | 417 |
{
|
| 399 | 418 |
if (connection != null && connection.State == ConnectionState.Open) |
| 400 | 419 |
{
|
| 420 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 421 |
{
|
|
| 422 |
cmd.CommandText = string.Format("DELETE FROM {0}", SPPID_SETTING_TABLE);
|
|
| 423 |
cmd.ExecuteNonQuery(); |
|
| 401 | 424 |
|
| 425 |
foreach (var item in dicSetting) |
|
| 426 |
{
|
|
| 427 |
cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@jsonString, @SettingType)", SPPID_SETTING_TABLE);
|
|
| 428 |
cmd.Parameters.AddWithValue("@jsonString", item.Value);
|
|
| 429 |
cmd.Parameters.AddWithValue("@SettingType", item.Key);
|
|
| 430 |
cmd.ExecuteNonQuery(); |
|
| 431 |
} |
|
| 432 |
} |
|
| 433 |
connection.Close(); |
|
| 402 | 434 |
} |
| 435 |
else |
|
| 436 |
return false; |
|
| 403 | 437 |
} |
| 404 | 438 |
catch (Exception ex) |
| 405 | 439 |
{
|
| 406 |
|
|
| 440 |
return false; |
|
| 407 | 441 |
} |
| 408 | 442 |
finally |
| 409 | 443 |
{
|
| ... | ... | |
| 453 | 487 |
{
|
| 454 | 488 |
if (connection != null && connection.State == ConnectionState.Open) |
| 455 | 489 |
{
|
| 456 |
|
|
| 490 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 491 |
{
|
|
| 492 |
cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_SETTING_TABLE);
|
|
| 493 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 494 |
dt.Load(dr); |
|
| 495 |
} |
|
| 496 |
connection.Close(); |
|
| 457 | 497 |
} |
| 458 | 498 |
} |
| 459 | 499 |
catch (Exception ex) |
| ... | ... | |
| 522 | 562 |
{
|
| 523 | 563 |
if (connection != null && connection.State == ConnectionState.Open) |
| 524 | 564 |
{
|
| 565 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 566 |
{
|
|
| 567 |
cmd.CommandText = string.Format(@" |
|
| 568 |
SELECT s.UID, s.Name, st.Type, sp.SPPID_SYMBOL_PATH, sp.LEADERLINE FROM {1} as st, {0} as s
|
|
| 569 |
LEFT OUTER JOIN {2} as sp
|
|
| 570 |
ON s.UID = SP.UID |
|
| 571 |
WHERE s.SymbolType_UID = st.UID |
|
| 572 |
ORDER BY st.TYPE ASC;", Symbol_TABLE, SymbolType_TABLE, SPPID_SYMBOL_MAPPING_TABLE); |
|
| 573 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 574 |
dt.Load(dr); |
|
| 525 | 575 |
|
| 576 |
DataTable dtClone = dt.Clone(); |
|
| 577 |
dtClone.Columns["UID"].DataType = typeof(string); |
|
| 578 |
foreach (DataRow row in dt.Rows) |
|
| 579 |
{
|
|
| 580 |
dtClone.ImportRow(row); |
|
| 581 |
} |
|
| 582 |
dt.Dispose(); |
|
| 583 |
dt = dtClone; |
|
| 584 |
} |
|
| 585 |
connection.Close(); |
|
| 526 | 586 |
} |
| 527 | 587 |
} |
| 528 | 588 |
catch (Exception ex) |
| ... | ... | |
| 615 | 675 |
else if (projectInfo.DBType == ID2DB_Type.MSSQL) |
| 616 | 676 |
{
|
| 617 | 677 |
using (SqlConnection connection = GetSqlConnection()) |
| 678 |
using (DataTable dt = new DataTable()) |
|
| 618 | 679 |
{
|
| 619 | 680 |
try |
| 620 | 681 |
{
|
| 621 | 682 |
if (connection != null && connection.State == ConnectionState.Open) |
| 622 | 683 |
{
|
| 684 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 685 |
{
|
|
| 686 |
cmd.CommandText = string.Format(@" |
|
| 687 |
SELECT AdditionalSymbol FROM Symbol"); |
|
| 688 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 689 |
dt.Load(dr); |
|
| 690 |
List<string> childList = new List<string>(); |
|
| 691 |
foreach (DataRow row in dt.Rows) |
|
| 692 |
{
|
|
| 693 |
if (row["AdditionalSymbol"] != null && !DBNull.Value.Equals(row["AdditionalSymbol"]) && !string.IsNullOrEmpty((string)row["AdditionalSymbol"])) |
|
| 694 |
{
|
|
| 695 |
string[] array = row["AdditionalSymbol"].ToString().Split(new char[] { '/' });
|
|
| 696 |
foreach (var childString in array) |
|
| 697 |
{
|
|
| 698 |
childList.Add(childString.Split(new char[] { ',' })[2]);
|
|
| 699 |
} |
|
| 700 |
} |
|
| 701 |
|
|
| 702 |
} |
|
| 703 |
|
|
| 704 |
dt.Clear(); |
|
| 705 |
cmd.CommandText = string.Format(@" |
|
| 706 |
SELECT * FROM {0}", SPPID_SYMBOL_MAPPING_TABLE);
|
|
| 707 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 708 |
dt.Load(dr); |
|
| 623 | 709 |
|
| 710 |
childList = childList.Distinct().ToList(); |
|
| 711 |
foreach (var child in childList) |
|
| 712 |
{
|
|
| 713 |
string mappingPath = string.Empty; |
|
| 714 |
DataRow[] rows = dt.Select(string.Format("UID = '{0}'", child));
|
|
| 715 |
if (rows.Length == 1) |
|
| 716 |
mappingPath = !DBNull.Value.Equals(rows[0]["SPPID_SYMBOL_PATH"]) ? (string)rows[0]["SPPID_SYMBOL_PATH"] : null; |
|
| 717 |
|
|
| 718 |
DataRow newRow = result.NewRow(); |
|
| 719 |
newRow["UID"] = child; |
|
| 720 |
newRow["Name"] = child; |
|
| 721 |
newRow["Type"] = "Child Symbol"; |
|
| 722 |
newRow["SPPID_SYMBOL_PATH"] = mappingPath; |
|
| 723 |
result.Rows.Add(newRow); |
|
| 724 |
} |
|
| 725 |
} |
|
| 726 |
connection.Close(); |
|
| 624 | 727 |
} |
| 625 | 728 |
} |
| 626 | 729 |
catch (Exception ex) |
| ... | ... | |
| 677 | 780 |
{
|
| 678 | 781 |
if (connection != null && connection.State == ConnectionState.Open) |
| 679 | 782 |
{
|
| 680 |
|
|
| 783 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 784 |
{
|
|
| 785 |
cmd.CommandText = string.Format(@" |
|
| 786 |
SELECT l.UID, l.Name, sp.SPPID_SYMBOL_PATH FROM {0} as l
|
|
| 787 |
LEFT OUTER JOIN {1} as sp
|
|
| 788 |
ON l.UID = SP.UID ;", LineTypes_TABLE, SPPID_SYMBOL_MAPPING_TABLE); |
|
| 789 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 790 |
dt.Load(dr); |
|
| 791 |
} |
|
| 792 |
connection.Close(); |
|
| 681 | 793 |
} |
| 682 | 794 |
} |
| 683 | 795 |
catch (Exception ex) |
| ... | ... | |
| 738 | 850 |
{
|
| 739 | 851 |
if (connection != null && connection.State == ConnectionState.Open) |
| 740 | 852 |
{
|
| 741 |
|
|
| 853 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 854 |
{
|
|
| 855 |
cmd.CommandText = string.Format(@" |
|
| 856 |
SELECT lp.UID, lp.DisplayName, sp.SPPID_SYMBOL_PATH, spa.SPPID_ATTRIBUTE |
|
| 857 |
FROM {0} as lp
|
|
| 858 |
LEFT OUTER JOIN {1} as sp
|
|
| 859 |
ON lp.UID = sp.UID |
|
| 860 |
LEFT OUTER JOIN {2} as spa
|
|
| 861 |
ON lp.UID = spa.UID;", LineProperties_TABLE, SPPID_SYMBOL_MAPPING_TABLE, SPPID_ATTRIBUTE_MAPPING_TABLE); |
|
| 862 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 863 |
dt.Load(dr); |
|
| 864 |
} |
|
| 865 |
connection.Close(); |
|
| 742 | 866 |
} |
| 743 | 867 |
} |
| 744 | 868 |
catch (Exception ex) |
| ... | ... | |
| 802 | 926 |
{
|
| 803 | 927 |
if (connection != null && connection.State == ConnectionState.Open) |
| 804 | 928 |
{
|
| 805 |
|
|
| 929 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 930 |
{
|
|
| 931 |
cmd.CommandText = string.Format(@" |
|
| 932 |
SELECT sa.UID, sa.DisplayAttribute, st.Type, spa.SPPID_ATTRIBUTE, sp.SPPID_SYMBOL_PATH, spl.LOCATION, spl.LEADERLINE, sa.Property |
|
| 933 |
FROM {1} as sa
|
|
| 934 |
LEFT OUTER JOIN {2} as sp
|
|
| 935 |
ON sa.UID = SP.UID |
|
| 936 |
LEFT OUTER JOIN {3} as spa
|
|
| 937 |
ON sa.UID = spa.UID |
|
| 938 |
LEFT OUTER JOIN {4} as spl
|
|
| 939 |
ON sa.UID = spl.UID |
|
| 940 |
LEFT OUTER JOIN {0} as st
|
|
| 941 |
ON sa.SymbolType_UID = st.UID |
|
| 942 |
WHERE (sa.Property != 2 OR sa.Property IS NULL);", SymbolType_TABLE, SymbolAttribute_TABLE, SPPID_SYMBOL_MAPPING_TABLE, SPPID_ATTRIBUTE_MAPPING_TABLE, SPPID_LABEL_INFO_TABLE); |
|
| 943 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 944 |
dt.Load(dr); |
|
| 945 |
} |
|
| 946 |
connection.Close(); |
|
| 806 | 947 |
} |
| 807 | 948 |
} |
| 808 | 949 |
catch (Exception ex) |
| ... | ... | |
| 858 | 999 |
{
|
| 859 | 1000 |
if (connection != null && connection.State == ConnectionState.Open) |
| 860 | 1001 |
{
|
| 861 |
|
|
| 1002 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1003 |
{
|
|
| 1004 |
cmd.CommandText = @"SELECT * FROM Symbol"; |
|
| 1005 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1006 |
dt.Load(dr); |
|
| 1007 |
} |
|
| 1008 |
connection.Close(); |
|
| 862 | 1009 |
} |
| 863 | 1010 |
} |
| 864 | 1011 |
catch (Exception ex) |
| ... | ... | |
| 913 | 1060 |
{
|
| 914 | 1061 |
if (connection != null && connection.State == ConnectionState.Open) |
| 915 | 1062 |
{
|
| 916 |
|
|
| 1063 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1064 |
{
|
|
| 1065 |
cmd.CommandText = string.Format("SELECT * FROM {0}", OPCRelations_TABLE);
|
|
| 1066 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1067 |
dt.Load(dr); |
|
| 1068 |
} |
|
| 1069 |
connection.Close(); |
|
| 917 | 1070 |
} |
| 918 | 1071 |
} |
| 919 | 1072 |
catch (Exception ex) |
| ... | ... | |
| 968 | 1121 |
{
|
| 969 | 1122 |
if (connection != null && connection.State == ConnectionState.Open) |
| 970 | 1123 |
{
|
| 971 |
|
|
| 1124 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1125 |
{
|
|
| 1126 |
cmd.CommandText = string.Format("SELECT * FROM {0}", "Drawings");
|
|
| 1127 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1128 |
dt.Load(dr); |
|
| 1129 |
} |
|
| 1130 |
connection.Close(); |
|
| 972 | 1131 |
} |
| 973 | 1132 |
} |
| 974 | 1133 |
catch (Exception ex) |
| ... | ... | |
| 1023 | 1182 |
{
|
| 1024 | 1183 |
if (connection != null && connection.State == ConnectionState.Open) |
| 1025 | 1184 |
{
|
| 1026 |
|
|
| 1185 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1186 |
{
|
|
| 1187 |
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE SPPID_OPC_MODELITEM_ID IS NOT NULL AND PAIRED = False", SPPID_OPC_INFO);
|
|
| 1188 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1189 |
dt.Load(dr); |
|
| 1190 |
} |
|
| 1191 |
connection.Close(); |
|
| 1027 | 1192 |
} |
| 1028 | 1193 |
} |
| 1029 | 1194 |
catch (Exception ex) |
| ... | ... | |
| 1078 | 1243 |
{
|
| 1079 | 1244 |
if (connection != null && connection.State == ConnectionState.Open) |
| 1080 | 1245 |
{
|
| 1081 |
|
|
| 1246 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1247 |
{
|
|
| 1248 |
cmd.CommandText = string.Format("SELECT * FROM {0}", SymbolType_TABLE);
|
|
| 1249 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1250 |
dt.Load(dr); |
|
| 1251 |
} |
|
| 1252 |
connection.Close(); |
|
| 1082 | 1253 |
} |
| 1083 | 1254 |
} |
| 1084 | 1255 |
catch (Exception ex) |
| ... | ... | |
| 1133 | 1304 |
{
|
| 1134 | 1305 |
if (connection != null && connection.State == ConnectionState.Open) |
| 1135 | 1306 |
{
|
| 1136 |
|
|
| 1307 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 1308 |
{
|
|
| 1309 |
cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_DRAWING_INFO);
|
|
| 1310 |
using (SqlDataReader dr = cmd.ExecuteReader()) |
|
| 1311 |
dt.Load(dr); |
|
| 1312 |
} |
|
| 1313 |
connection.Close(); |
|
| 1137 | 1314 |
} |
| 1138 | 1315 |
} |
| 1139 | 1316 |
catch (Exception ex) |
| DTI_PID/SPPIDConverter/Program.cs | ||
|---|---|---|
| 16 | 16 |
{
|
| 17 | 17 |
Application.EnableVisualStyles(); |
| 18 | 18 |
Application.SetCompatibleTextRenderingDefault(false); |
| 19 |
Application.Run(new Converter.SPPID.Form.ID2DBSetting());
|
|
| 19 |
Application.Run(new Converter.SPPID.ConverterForm());
|
|
| 20 | 20 |
} |
| 21 | 21 |
} |
| 22 | 22 |
} |
내보내기 Unified diff
