/DTI_PID/SPPIDConverter/DB/Project_DB.cs - Diff - HYTOS - 일정관리

개정판 a2c803a0

ID	a2c803a03881615b89fe0b02d6c556365cd5b44a
상위	90b0daad
하위	29e77ce5

gaqhf 이(가) 5년 이상 전에 추가함

dev issue #1203 : Symbol Mapping Table Query 완료

Change-Id: Ic31c4058a286d91520dfd5a7ab371d0d83468123

DTI_PID/SPPIDConverter/DB/Project_DB.cs
565	565	using (SqlCommand cmd = connection.CreateCommand())
566	566	{
567	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
	568	SELECT CONVERT(VARCHAR(255), s.UID) AS UID, s.Name, st.Type, sp.SPPID_SYMBOL_PATH, sp.LEADERLINE FROM {1} as st, {0} as s
569	569	LEFT OUTER JOIN {2} as sp
570		ON s.UID = SP.UID
	570	ON CONVERT(VARCHAR(255), s.UID) = CONVERT(VARCHAR(255), SP.UID)
571	571	WHERE s.SymbolType_UID = st.UID
572	572	ORDER BY st.TYPE ASC;", Symbol_TABLE, SymbolType_TABLE, SPPID_SYMBOL_MAPPING_TABLE);
573	573	using (SqlDataReader dr = cmd.ExecuteReader())
574	574	dt.Load(dr);
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	575	}
585	576	connection.Close();
586	577	}
...	...
1361	1352	catch (Exception ex)
1362	1353	{
1363	1354	transaction.Rollback();
	1355	return false;
1364	1356	}
1365	1357	finally
1366	1358	{
...	...
1386	1378	{
1387	1379	if (connection != null && connection.State == ConnectionState.Open)
1388	1380	{
1389
	1381	using (SqlCommand cmd = connection.CreateCommand())
	1382	{
	1383	foreach (var item in datas)
	1384	{
	1385	cmd.Parameters.Clear();
	1386	cmd.CommandText = string.Format(@"
	1387	IF EXISTS (SELECT * FROM {0} WHERE UID = '{1}')
	1388	UPDATE {0} SET NAME = @NAME, SPPID_SYMBOL_PATH = @SPPID_SYMBOL_PATH, LEADERLINE = @LEADERLINE WHERE UID = @UID
	1389	ELSE
	1390	INSERT INTO {0} (UID, NAME, SPPID_SYMBOL_PATH, LEADERLINE) VALUES (@UID, @NAME, @SPPID_SYMBOL_PATH, @LEADERLINE)", SPPID_SYMBOL_MAPPING_TABLE, item.Item1);
	1391	cmd.Parameters.AddWithValue("@UID", item.Item1);
	1392	cmd.Parameters.AddWithValue("@NAME", item.Item2);
	1393	if (string.IsNullOrEmpty(item.Item3))
	1394	cmd.Parameters.AddWithValue("@SPPID_SYMBOL_PATH", DBNull.Value);
	1395	else
	1396	cmd.Parameters.AddWithValue("@SPPID_SYMBOL_PATH", item.Item3);
	1397	cmd.Parameters.AddWithValue("@LEADERLINE", item.Item4);
	1398	cmd.ExecuteNonQuery();
	1399	}
	1400	}
	1401	connection.Close();
1390	1402	}
1391	1403	}
1392	1404	catch (Exception ex)
1393	1405	{
1394
	1406	return false;
1395	1407	}
1396	1408	finally
1397	1409	{
...	...
1460	1472	{
1461	1473	if (connection != null && connection.State == ConnectionState.Open)
1462	1474	{
1463
	1475	using (SqlTransaction transaction = connection.BeginTransaction())
	1476	{
	1477	using (SqlCommand cmd = connection.CreateCommand())
	1478	{
	1479	foreach (var item in datas)
	1480	{
	1481	cmd.Parameters.Clear();
	1482	cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, SPPID_ATTRIBUTE) VALUES (@UID, @SPPID_ATTRIBUTE)", SPPID_ATTRIBUTE_MAPPING_TABLE);
	1483	cmd.Parameters.AddWithValue("@UID", item.Item1);
	1484	cmd.Parameters.AddWithValue("@SPPID_ATTRIBUTE", item.Item2);
	1485	cmd.ExecuteNonQuery();
	1486	}
	1487	}
	1488	connection.Close();
	1489	}
1464	1490	}
1465	1491	}
1466	1492	catch (Exception ex)
1467	1493	{
1468
	1494	return false;
1469	1495	}
1470	1496	finally
1471	1497	{
...	...
1509	1535	catch (Exception ex)
1510	1536	{
1511	1537	transaction.Rollback();
	1538	return false;
1512	1539	}
1513	1540	finally
1514	1541	{
...	...
1534	1561	{
1535	1562	if (connection != null && connection.State == ConnectionState.Open)
1536	1563	{
1537
	1564	using (SqlCommand cmd = connection.CreateCommand())
	1565	{
	1566	foreach (var item in datas)
	1567	{
	1568	cmd.Parameters.Clear();
	1569	cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, LOCATION, LEADERLINE) VALUES (@UID, @LOCATION, @LEADERLINE)", SPPID_LABEL_INFO_TABLE);
	1570	cmd.Parameters.AddWithValue("@UID", item.Item1);
	1571	cmd.Parameters.AddWithValue("@LOCATION", item.Item2);
	1572	cmd.Parameters.AddWithValue("@LEADERLINE", item.Item3);
	1573	cmd.ExecuteNonQuery();
	1574	}
	1575	}
	1576	connection.Close();
1538	1577	}
1539	1578	}
1540	1579	catch (Exception ex)
1541	1580	{
1542
	1581	return false;
1543	1582	}
1544	1583	finally
1545	1584	{
...	...
1596	1635	catch (Exception ex)
1597	1636	{
1598	1637	transaction.Rollback();
	1638	return false;
1599	1639	}
1600	1640	finally
1601	1641	{
...	...
1621	1661	{
1622	1662	if (connection != null && connection.State == ConnectionState.Open)
1623	1663	{
	1664	using (SqlCommand cmd = connection.CreateCommand())
	1665	{
	1666	cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (ID2_DRAWING_UID, PATH, DRAWINGNUMBER, DRAWINGNAME) VALUES (@ID2_DRAWING_UID, @PATH, @DRAWINGNUMBER, @DRAWINGNAME)", SPPID_DRAWING_INFO);
	1667	cmd.Parameters.AddWithValue("@ID2_DRAWING_UID", document.UID);
	1668	cmd.Parameters.AddWithValue("@PATH", path);
	1669	cmd.Parameters.AddWithValue("@DRAWINGNUMBER", drawingNumber);
	1670	cmd.Parameters.AddWithValue("@DRAWINGNAME", drawingName);
	1671	cmd.ExecuteNonQuery();
	1672	}
1624	1673
	1674	List<Symbol> OPCs = document.SYMBOLS.FindAll(x => x.TYPE == "Piping OPC's" \|\| x.TYPE == "Instrument OPC's");
	1675	foreach (var item in OPCs)
	1676	{
	1677	using (SqlCommand cmd = connection.CreateCommand())
	1678	{
	1679	cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (ID2_OPC_UID, SPPID_OPC_MODELITEM_ID, ID2_DRAWING_UID, ATTRIBUTES, PAIRED) VALUES (@ID2_OPC_UID, @SPPID_OPC_MODELITEM_ID, @ID2_DRAWING_UID, @ATTRIBUTES, @PAIRED)", SPPID_OPC_INFO);
	1680	cmd.Parameters.AddWithValue("@ID2_OPC_UID", item.UID);
	1681	cmd.Parameters.AddWithValue("@SPPID_OPC_MODELITEM_ID", item.SPPID.ModelItemID);
	1682	cmd.Parameters.AddWithValue("@ID2_DRAWING_UID", document.UID);
	1683	cmd.Parameters.AddWithValue("@ATTRIBUTES", JsonConvert.SerializeObject(item.SPPID.Attributes));
	1684	cmd.Parameters.AddWithValue("@PAIRED", false);
	1685	cmd.ExecuteNonQuery();
	1686	}
	1687	}
	1688	connection.Close();
1625	1689	}
1626	1690	}
1627	1691	catch (Exception ex)
1628	1692	{
1629
	1693	return false;
1630	1694	}
1631	1695	finally
1632	1696	{
...	...
1668	1732	catch (Exception ex)
1669	1733	{
1670	1734	transaction.Rollback();
	1735	return false;
1671	1736	}
1672	1737	finally
1673	1738	{
...	...
1693	1758	{
1694	1759	if (connection != null && connection.State == ConnectionState.Open)
1695	1760	{
1696
	1761	using (SqlCommand cmd = connection.CreateCommand())
	1762	{
	1763	cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (ID2_OPC_UID, SPPID_OPC_MODELITEM_ID, ID2_DRAWING_UID, PAIRED) VALUES (@ID2_OPC_UID, @SPPID_OPC_MODELITEM_ID, @ID2_DRAWING_UID, @PAIRED)", SPPID_OPC_INFO);
	1764	cmd.Parameters.AddWithValue("@ID2_OPC_UID", UID);
	1765	cmd.Parameters.AddWithValue("@SPPID_OPC_MODELITEM_ID", ModelItemID);
	1766	cmd.Parameters.AddWithValue("@ID2_DRAWING_UID", drawingUID);
	1767	cmd.Parameters.AddWithValue("@PAIRED", Paired);
	1768	cmd.ExecuteNonQuery();
	1769	}
	1770	connection.Close();
1697	1771	}
1698	1772	}
1699	1773	catch (Exception ex)
1700	1774	{
1701
	1775	return false;
1702	1776	}
1703	1777	finally
1704	1778	{

내보내기 Unified diff

프로젝트

일반

사용자정보

HYTOS

개정판 a2c803a0