개정판 0c281fe6
dev issue #1227 : end opc mappingform, opc db query, table
Change-Id: Ie53a6039c6d4182fbc7f89ea9707dce780a2ff08
| DTI_PID/APIDConverter/DB/Project_DB.cs | ||
|---|---|---|
| 81 | 81 |
} |
| 82 | 82 |
if (dt.Select(string.Format("NAME = '{0}'", APID_OPC_MAPPING_TABLE)).Length == 0)
|
| 83 | 83 |
{
|
| 84 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, IN_SYMBOL TEXT, OUT_SYMBOL TEXT, PipeType INT)", APID_OPC_MAPPING_TABLE);
|
|
| 84 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, IN_SYMBOL TEXT, OUT_SYMBOL TEXT)", APID_OPC_MAPPING_TABLE);
|
|
| 85 | 85 |
cmd.ExecuteNonQuery(); |
| 86 | 86 |
} |
| 87 | 87 |
} |
| ... | ... | |
| 123 | 123 |
} |
| 124 | 124 |
if (dt.Select(string.Format("NAME = '{0}'", APID_OPC_MAPPING_TABLE)).Length == 0)
|
| 125 | 125 |
{
|
| 126 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, IN_SYMBOL varchar(MAX), OUT_SYMBOL varchar(MAX), PipeType INT)", APID_OPC_MAPPING_TABLE);
|
|
| 126 |
cmd.CommandText = string.Format("CREATE TABLE {0} (UID varchar(255) PRIMARY KEY, IN_SYMBOL varchar(MAX), OUT_SYMBOL varchar(MAX))", APID_OPC_MAPPING_TABLE);
|
|
| 127 | 127 |
cmd.ExecuteNonQuery(); |
| 128 | 128 |
} |
| 129 | 129 |
} |
| ... | ... | |
| 240 | 240 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
| 241 | 241 |
{
|
| 242 | 242 |
cmd.CommandText = string.Format(@" |
| 243 |
SELECT s.UID, s.Name, st.Type, sp.IN_SYMBOL, sp.OUT_SYMBOL, sp.PipeType FROM {1} as st, {0} as s
|
|
| 243 |
SELECT s.UID, s.Name, st.Type, sp.IN_SYMBOL, sp.OUT_SYMBOL FROM {1} as st, {0} as s
|
|
| 244 | 244 |
LEFT OUTER JOIN {2} as sp
|
| 245 | 245 |
ON s.UID = SP.UID |
| 246 | 246 |
WHERE s.SymbolType_UID = st.UID |
| ... | ... | |
| 272 | 272 |
using (SqlCommand cmd = connection.CreateCommand()) |
| 273 | 273 |
{
|
| 274 | 274 |
cmd.CommandText = string.Format(@" |
| 275 |
SELECT CONVERT(VARCHAR(255), s.UID) AS UID, s.Name, st.Type, sp.IN_SYMBOL, sp.OUT_SYMBOL, sp.PipeType FROM {1} as st, {0} as s
|
|
| 275 |
SELECT CONVERT(VARCHAR(255), s.UID) AS UID, s.Name, st.Type, sp.IN_SYMBOL, sp.OUT_SYMBOL FROM {1} as st, {0} as s
|
|
| 276 | 276 |
LEFT OUTER JOIN {2} as sp
|
| 277 | 277 |
ON CONVERT(VARCHAR(255), s.UID) = CONVERT(VARCHAR(255), SP.UID) |
| 278 | 278 |
WHERE s.SymbolType_UID = st.UID |
| ... | ... | |
| 650 | 650 |
|
| 651 | 651 |
return true; |
| 652 | 652 |
} |
| 653 |
public static bool InsertOPCMapping(List<Tuple<string, string, string>> datas) |
|
| 654 |
{
|
|
| 655 |
Project_Info projectInfo = Project_Info.GetInstance(); |
|
| 656 |
if (projectInfo.DBType == ID2DB_Type.SQLite) |
|
| 657 |
{
|
|
| 658 |
using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))
|
|
| 659 |
{
|
|
| 660 |
try |
|
| 661 |
{
|
|
| 662 |
connection.Open(); |
|
| 663 |
using (SQLiteTransaction transaction = connection.BeginTransaction()) |
|
| 664 |
{
|
|
| 665 |
try |
|
| 666 |
{
|
|
| 667 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 668 |
{
|
|
| 669 |
foreach (var item in datas) |
|
| 670 |
{
|
|
| 671 |
cmd.Parameters.Clear(); |
|
| 672 |
cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, IN_SYMBOL, OUT_SYMBOL) VALUES (@UID, @IN_SYMBOL, @OUT_SYMBOL)", APID_OPC_MAPPING_TABLE);
|
|
| 673 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 674 |
cmd.Parameters.AddWithValue("@IN_SYMBOL", item.Item2);
|
|
| 675 |
cmd.Parameters.AddWithValue("@OUT_SYMBOL", item.Item3);
|
|
| 676 |
cmd.ExecuteNonQuery(); |
|
| 677 |
} |
|
| 678 |
} |
|
| 679 |
transaction.Commit(); |
|
| 680 |
connection.Close(); |
|
| 681 |
} |
|
| 682 |
catch (Exception ex) |
|
| 683 |
{
|
|
| 684 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 685 |
transaction.Rollback(); |
|
| 686 |
return false; |
|
| 687 |
} |
|
| 688 |
finally |
|
| 689 |
{
|
|
| 690 |
transaction.Dispose(); |
|
| 691 |
} |
|
| 692 |
} |
|
| 693 |
} |
|
| 694 |
catch (Exception ex) |
|
| 695 |
{
|
|
| 696 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 697 |
return false; |
|
| 698 |
} |
|
| 699 |
finally |
|
| 700 |
{
|
|
| 701 |
connection.Dispose(); |
|
| 702 |
} |
|
| 703 |
} |
|
| 704 |
} |
|
| 705 |
else if (projectInfo.DBType == ID2DB_Type.MSSQL) |
|
| 706 |
{
|
|
| 707 |
using (SqlConnection connection = GetSqlConnection()) |
|
| 708 |
{
|
|
| 709 |
try |
|
| 710 |
{
|
|
| 711 |
if (connection != null && connection.State == ConnectionState.Open) |
|
| 712 |
{
|
|
| 713 |
using (SqlCommand cmd = connection.CreateCommand()) |
|
| 714 |
{
|
|
| 715 |
foreach (var item in datas) |
|
| 716 |
{
|
|
| 717 |
cmd.Parameters.Clear(); |
|
| 718 |
cmd.CommandText = string.Format(@" |
|
| 719 |
IF EXISTS (SELECT * FROM {0} WHERE UID = '{1}')
|
|
| 720 |
UPDATE {0} SET IN_SYMBOL = @IN_SYMBOL, OUT_SYMBOL = @OUT_SYMBOL WHERE UID = @UID
|
|
| 721 |
ELSE |
|
| 722 |
INSERT INTO {0} (UID, IN_SYMBOL, OUT_SYMBOL) VALUES (@UID, @IN_SYMBOL, @OUT_SYMBOL)", APID_OPC_MAPPING_TABLE, item.Item1);
|
|
| 723 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 724 |
if (string.IsNullOrEmpty(item.Item2)) |
|
| 725 |
cmd.Parameters.AddWithValue("@IN_SYMBOL", DBNull.Value);
|
|
| 726 |
else |
|
| 727 |
cmd.Parameters.AddWithValue("@IN_SYMBOL", item.Item2);
|
|
| 728 |
if (string.IsNullOrEmpty(item.Item3)) |
|
| 729 |
cmd.Parameters.AddWithValue("@OUT_SYMBOL", DBNull.Value);
|
|
| 730 |
else |
|
| 731 |
cmd.Parameters.AddWithValue("@OUT_SYMBOL", item.Item3);
|
|
| 732 |
|
|
| 733 |
cmd.ExecuteNonQuery(); |
|
| 734 |
} |
|
| 735 |
} |
|
| 736 |
connection.Close(); |
|
| 737 |
} |
|
| 738 |
} |
|
| 739 |
catch (Exception ex) |
|
| 740 |
{
|
|
| 741 |
Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
|
| 742 |
return false; |
|
| 743 |
} |
|
| 744 |
finally |
|
| 745 |
{
|
|
| 746 |
if (connection != null) |
|
| 747 |
connection.Dispose(); |
|
| 748 |
} |
|
| 749 |
} |
|
| 750 |
} |
|
| 751 |
|
|
| 752 |
return true; |
|
| 753 |
} |
|
| 653 | 754 |
#endregion |
| 654 | 755 |
|
| 655 | 756 |
#region AVEVA |
내보내기 Unified diff
