개정판 2e92b956
dev issue #000 : add sql server
Change-Id: I7d64e7074737d58c91c58bfa65804458bbedf6e8
| DTI_PID/SPPIDConverter/DB/SPPID_DB.cs | ||
|---|---|---|
| 7 | 7 |
using System.Globalization; |
| 8 | 8 |
using System.IO; |
| 9 | 9 |
using Oracle.ManagedDataAccess.Client; |
| 10 |
using System.Data.SqlClient; |
|
| 10 | 11 |
|
| 11 | 12 |
namespace Converter.SPPID.DB |
| 12 | 13 |
{
|
| ... | ... | |
| 83 | 84 |
} |
| 84 | 85 |
} |
| 85 | 86 |
} |
| 86 |
else |
|
| 87 |
else if (dbInfo.DBType == "SQLSERVER")
|
|
| 87 | 88 |
{
|
| 88 | 89 |
|
| 89 | 90 |
} |
| ... | ... | |
| 159 | 160 |
} |
| 160 | 161 |
} |
| 161 | 162 |
} |
| 162 |
else |
|
| 163 |
else if (dbInfo.DBType == "SQLSERVER")
|
|
| 163 | 164 |
{
|
| 165 |
string connString = string.Format("server = {0}; uid = {1}; pwd = {2}; database = {3};", dbInfo.Service, dbInfo.DBUser, dbInfo.DBPassword, dbInfo.SQLSERVER_DBNAME);
|
|
| 166 |
using (SqlConnection conn = new SqlConnection(connString)) |
|
| 167 |
{
|
|
| 168 |
conn.Open(); |
|
| 169 |
if (conn.State == ConnectionState.Open) |
|
| 170 |
{
|
|
| 171 |
using (SqlCommand cmd = new SqlCommand()) |
|
| 172 |
{
|
|
| 173 |
cmd.Connection = conn; |
|
| 164 | 174 |
|
| 175 |
cmd.CommandText = string.Format(CultureInfo.CurrentCulture, @"SELECT SP_SCHEMA_TYPE, USERNAME |
|
| 176 |
FROM {0}.T_DB_DATA DB_DATA
|
|
| 177 |
INNER JOIN {0}.T_ROOTITEM ROOTITEM ON DB_DATA.SP_ROOTITEMID = ROOTITEM.SP_ID
|
|
| 178 |
WHERE ROOTITEM.NAME = '{1}'", dbInfo.Site, dbInfo.SelectedPlant);
|
|
| 179 |
using (SqlDataAdapter adapter = new SqlDataAdapter()) |
|
| 180 |
{
|
|
| 181 |
DataTable dt = new DataTable(); |
|
| 182 |
adapter.SelectCommand = cmd; |
|
| 183 |
adapter.Fill(dt); |
|
| 184 |
|
|
| 185 |
foreach (DataRow row in dt.Rows) |
|
| 186 |
{
|
|
| 187 |
string sType = row["SP_SCHEMA_TYPE"].ToString(); |
|
| 188 |
switch (sType) |
|
| 189 |
{
|
|
| 190 |
case "SPPIDDATA_DICTIONARY": |
|
| 191 |
dbInfo.PlantPIDDic = row["USERNAME"].ToString(); |
|
| 192 |
break; |
|
| 193 |
case "DATA_DICTIONARY": |
|
| 194 |
dbInfo.PlantDic = row["USERNAME"].ToString(); |
|
| 195 |
break; |
|
| 196 |
case "SPAPLANT": |
|
| 197 |
dbInfo.Plant = row["USERNAME"].ToString(); |
|
| 198 |
break; |
|
| 199 |
case "SPPID": |
|
| 200 |
dbInfo.PlantPID = row["USERNAME"].ToString(); |
|
| 201 |
break; |
|
| 202 |
default: |
|
| 203 |
break; |
|
| 204 |
} |
|
| 205 |
bResult = true; |
|
| 206 |
} |
|
| 207 |
} |
|
| 208 |
|
|
| 209 |
cmd.CommandText = string.Format(CultureInfo.CurrentCulture, "SELECT PATH FROM {0}.T_ROOTITEM WHERE NAME = '{1}'", dbInfo.Site, dbInfo.SelectedPlant);
|
|
| 210 |
using (SqlDataReader reader = cmd.ExecuteReader()) |
|
| 211 |
{
|
|
| 212 |
while (reader.Read()) |
|
| 213 |
{
|
|
| 214 |
dbInfo.PlantPath = reader["PATH"].ToString(); |
|
| 215 |
} |
|
| 216 |
} |
|
| 217 |
} |
|
| 218 |
} |
|
| 219 |
} |
|
| 165 | 220 |
} |
| 166 | 221 |
} |
| 167 | 222 |
catch (Exception ex) |
| ... | ... | |
| 202 | 257 |
} |
| 203 | 258 |
} |
| 204 | 259 |
} |
| 205 |
else |
|
| 260 |
else if (dbInfo.DBType == "SQLSERVER")
|
|
| 206 | 261 |
{
|
| 262 |
string connString = string.Format("server = {0}; uid = {1}; pwd = {2}; database = {3};", dbInfo.Service, dbInfo.DBUser, dbInfo.DBPassword, dbInfo.SQLSERVER_DBNAME);
|
|
| 263 |
using (SqlConnection conn = new SqlConnection(connString)) |
|
| 264 |
{
|
|
| 265 |
conn.Open(); |
|
| 266 |
if (conn.State == ConnectionState.Open) |
|
| 267 |
{
|
|
| 268 |
using (SqlCommand cmd = new SqlCommand()) |
|
| 269 |
{
|
|
| 270 |
cmd.Connection = conn; |
|
| 271 |
cmd.CommandText = string.Format(CultureInfo.CurrentCulture, "SELECT NAME, PATH FROM {0}.T_ROOTITEM", dbInfo.Site);
|
|
| 207 | 272 |
|
| 273 |
using (SqlDataReader reader = cmd.ExecuteReader()) |
|
| 274 |
{
|
|
| 275 |
while (reader.Read()) |
|
| 276 |
{
|
|
| 277 |
plantList.Add(reader["NAME"].ToString()); |
|
| 278 |
} |
|
| 279 |
} |
|
| 280 |
} |
|
| 281 |
|
|
| 282 |
} |
|
| 283 |
} |
|
| 208 | 284 |
} |
| 209 | 285 |
} |
| 210 | 286 |
catch (Exception ex) |
| ... | ... | |
| 246 | 322 |
adapter.SelectCommand = cmd; |
| 247 | 323 |
dt = new DataTable(); |
| 248 | 324 |
adapter.Fill(dt); |
| 325 |
foreach (DataColumn item in dt.Columns) |
|
| 326 |
item.ColumnName = item.ColumnName.ToUpper(); |
|
| 249 | 327 |
} |
| 250 | 328 |
} |
| 251 | 329 |
} |
| 252 | 330 |
} |
| 253 |
else |
|
| 331 |
else if (dbInfo.DBType == "SQLSERVER")
|
|
| 254 | 332 |
{
|
| 255 |
|
|
| 333 |
string connString = string.Format("server = {0}; uid = {1}; pwd = {2}; database = {3};", dbInfo.Service, dbInfo.DBUser, dbInfo.DBPassword, "plant");
|
|
| 334 |
using (SqlConnection conn = new SqlConnection(connString)) |
|
| 335 |
{
|
|
| 336 |
conn.Open(); |
|
| 337 |
if (conn.State == ConnectionState.Open) |
|
| 338 |
{
|
|
| 339 |
string sQuery = string.Format(CultureInfo.CurrentCulture, |
|
| 340 |
@"SELECT ITEM.description ItemType ,ATTR.displayname, ATTR.name FROM {0}.itemattributions ATTR
|
|
| 341 |
LEFT OUTER JOIN {0}.item ITEM
|
|
| 342 |
ON ITEM.ID = ATTR.ITEMID |
|
| 343 |
ORDER BY ATTR.displayname ASC", dbInfo.PlantPIDDic); |
|
| 344 |
//string sQuery = string.Format(CultureInfo.CurrentCulture, |
|
| 345 |
// @"SELECT DISTINCT(ATTR.displayname), ATTR.name FROM {0}.itemattributions ATTR
|
|
| 346 |
// ORDER BY ATTR.displayname ASC", dbInfo.PlantPIDDic); |
|
| 347 |
using (SqlCommand cmd = new SqlCommand(sQuery, conn)) |
|
| 348 |
using (SqlDataAdapter adapter = new SqlDataAdapter()) |
|
| 349 |
{
|
|
| 350 |
adapter.SelectCommand = cmd; |
|
| 351 |
dt = new DataTable(); |
|
| 352 |
adapter.Fill(dt); |
|
| 353 |
foreach (DataColumn item in dt.Columns) |
|
| 354 |
item.ColumnName = item.ColumnName.ToUpper(); |
|
| 355 |
} |
|
| 356 |
} |
|
| 357 |
} |
|
| 256 | 358 |
} |
| 359 |
|
|
| 257 | 360 |
} |
| 258 | 361 |
catch (Exception ex) |
| 259 | 362 |
{
|
| ... | ... | |
| 289 | 392 |
adapter.SelectCommand = cmd; |
| 290 | 393 |
dt = new DataTable(); |
| 291 | 394 |
adapter.Fill(dt); |
| 395 |
foreach (DataColumn item in dt.Columns) |
|
| 396 |
item.ColumnName = item.ColumnName.ToUpper(); |
|
| 292 | 397 |
} |
| 293 | 398 |
} |
| 294 | 399 |
} |
| 295 | 400 |
} |
| 296 |
else |
|
| 401 |
else if (dbInfo.DBType == "SQLSERVER")
|
|
| 297 | 402 |
{
|
| 298 |
|
|
| 403 |
string connString = string.Format("server = {0}; uid = {1}; pwd = {2}; database = {3};", dbInfo.Service, dbInfo.DBUser, dbInfo.DBPassword, "plant");
|
|
| 404 |
using (SqlConnection conn = new SqlConnection(connString)) |
|
| 405 |
{
|
|
| 406 |
conn.Open(); |
|
| 407 |
if (conn.State == ConnectionState.Open) |
|
| 408 |
{
|
|
| 409 |
string sQuery = string.Format(CultureInfo.CurrentCulture, |
|
| 410 |
@"SELECT * FROM {0}.CODELISTS OUTATTR
|
|
| 411 |
WHERE OUTATTR.codelist_index = |
|
| 412 |
(SELECT attr.codelist_constraint FROM {0}.CODELISTS ATTR WHERE ATTR.CODELIST_TEXT = '{1}' AND ATTR.CODELIST_NUMBER = 19) AND OUTATTR.CODELIST_NUMBER = 20", dbInfo.PlantPIDDic, operFluidCode);
|
|
| 413 |
using (SqlCommand cmd = new SqlCommand(sQuery, conn)) |
|
| 414 |
using (SqlDataAdapter adapter = new SqlDataAdapter()) |
|
| 415 |
{
|
|
| 416 |
adapter.SelectCommand = cmd; |
|
| 417 |
dt = new DataTable(); |
|
| 418 |
adapter.Fill(dt); |
|
| 419 |
foreach (DataColumn item in dt.Columns) |
|
| 420 |
item.ColumnName = item.ColumnName.ToUpper(); |
|
| 421 |
} |
|
| 422 |
} |
|
| 423 |
} |
|
| 299 | 424 |
} |
| 300 | 425 |
} |
| 301 | 426 |
catch (Exception ex) |
내보내기 Unified diff
