개정판 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 |
| DTI_PID/APIDConverter/Form/MappingForm.cs | ||
|---|---|---|
| 97 | 97 |
gridViewOPC.Columns["IN_SYMBOL"].ColumnEdit = repTextEditOPC; |
| 98 | 98 |
gridViewOPC.Columns["OUT_SYMBOL"].ColumnEdit = repTextEditOPC; |
| 99 | 99 |
|
| 100 |
|
|
| 101 |
|
|
| 102 |
|
|
| 103 |
|
|
| 104 | 100 |
gridViewOPC.ExpandAllGroups(); |
| 105 | 101 |
|
| 106 | 102 |
void opcButtonEdit_ButtonClick(object sender, ButtonPressedEventArgs e) |
| ... | ... | |
| 235 | 231 |
private void btnSave_Click(object sender, EventArgs e) |
| 236 | 232 |
{
|
| 237 | 233 |
List<Tuple<string, string, string, string>> symbolDatas = new List<Tuple<string, string, string, string>>(); |
| 238 |
#region Set Datas |
|
| 234 |
#region Set symbol Datas
|
|
| 239 | 235 |
DataTable symbolDT = gridControlSymbol.DataSource as DataTable; |
| 240 | 236 |
foreach (DataRow row in symbolDT.Rows) |
| 241 | 237 |
{
|
| ... | ... | |
| 264 | 260 |
} |
| 265 | 261 |
#endregion |
| 266 | 262 |
|
| 267 |
if (Project_DB.InsertSymbolMapping(symbolDatas)) |
|
| 263 |
List<Tuple<string, string, string>> opcDatas = new List<Tuple<string, string, string>>(); |
|
| 264 |
#region OPC |
|
| 265 |
DataTable opcDT = gridControlOPC.DataSource as DataTable; |
|
| 266 |
foreach (DataRow row in opcDT.Rows) |
|
| 267 |
{
|
|
| 268 |
string uid = row["UID"].ToString(); |
|
| 269 |
string inSymbol = row["IN_SYMBOL"].ToString(); |
|
| 270 |
string outSymbol = row["OUT_SYMBOL"].ToString(); |
|
| 271 |
opcDatas.Add(new Tuple<string, string, string>(uid, inSymbol, outSymbol)); |
|
| 272 |
} |
|
| 273 |
#endregion |
|
| 274 |
|
|
| 275 |
if (Project_DB.InsertSymbolMapping(symbolDatas) && |
|
| 276 |
Project_DB.InsertOPCMapping(opcDatas)) |
|
| 268 | 277 |
MessageBox.Show("Save Success!", "APID Converter", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
| 269 | 278 |
else |
| 270 | 279 |
MessageBox.Show("Save Fail!", "APID Converter", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
내보내기 Unified diff
