public string SelectScalarSql(string sql, DBCatalog db)
{
object retVal = string.Empty;
try
{
CnOpen(db);
SqlCommand cmd = new SqlCommand(sql, cn);
retVal = cmd.ExecuteScalar();
CnClose();
if (retVal == null)
{
retVal = "";
}
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[SelectSql] " + sql + "\n[SelectSql Error] " + ex.Message);
CnClose();
throw ex;
}
return(retVal.ToString());
}
public String GetDBConnecitonString(DBCatalog db)
{
String result = String.Empty;
using (SqlConnection con = new SqlConnection(Encryption.Decrypt(ConfigurationManager.ConnectionStrings["Common"].ToString(), true, "Forever21MX")))
{
con.Open();
SqlCommand cmd = new SqlCommand("spConfigValue;1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@AppId", SqlDbType.VarChar, 4);
cmd.Parameters.Add("@ConfigKey", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Production", SqlDbType.Bit);
cmd.Parameters["@AppId"].Value = ConfigManager.GetAppSetting2("AppId");
cmd.Parameters["@ConfigKey"].Value = db.ToString();
cmd.Parameters["@Production"].Value = (ConfigManager.GetAppSetting2("mode").Trim().Equals("production") ? true : false);
SqlDataReader read = cmd.ExecuteReader();
if (read.HasRows)
{
read.Read();
result = read.GetString(0);
}
read.Close();
read.Dispose();
cmd.Dispose();
con.Close();
}
return(result);
}
/// <summary>
/// 커넥션 열기
/// </summary>
/// <returns>void</returns>
private void CnOpen(DBCatalog db)
{
if (transaction == null)
{
Initialize(db);
}
if (cn.State == System.Data.ConnectionState.Closed)
{
cn.Open();
System.Diagnostics.Debug.WriteLine("Connection -> " + db.ToString());
}
}
/// <summary>
/// 등록/수정/삭제 쿼리실행
/// </summary>
/// <param name="sql">쿼리</param>
/// <param name="db">DB</param>
/// <returns></returns>
public static string ExecuteSql(string sql, DBCatalog db)
{
string strReturn = "";
try
{
strReturn = ds.ExecuteSql(sql, db);
}
catch (Exception ex)
{
strReturn = ex.Message;
}
return(strReturn);
}
/// <summary>
/// 등록/수정/삭제 쿼리실행
/// </summary>
/// <param name="sql">쿼리</param>
/// <param name="nvParam">파라메터</param>
/// <param name="db">DB</param>
/// <returns></returns>
public static string ExecuteSql(string sql, NameValueCollection nvParam, DBCatalog db)
{
string strReturn = "";
try
{
strReturn = ds.ExecuteSql(sql, nvParam, db);
}
catch (Exception ex)
{
strReturn = ex.Message;
}
return(strReturn);
}
/// <summary>
/// 단일값을 가져오는 쿼리 실행
/// </summary>
/// <param name="sql">쿼리</param>
/// <param name="db">DB</param>
/// <returns></returns>
public static string SearchScalar(string sql, DBCatalog db)
{
string retVal = null;
try
{
retVal = ds.SelectScalarSql(sql, db);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("[Search Error] " + ex.Message);
}
return(retVal);
}
/// <summary>
/// 조회 쿼리실행
/// </summary>
/// <param name="sql">쿼리</param>
/// <param name="nvParam">파라메터</param>
/// <param name="db">DB</param>
/// <returns></returns>
public static DataTable Search(string sql, NameValueCollection nvParam, DBCatalog db)
{
DataTable dt = null;
try
{
dt = ds.SelectSql(sql, nvParam, db);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("[Search Error] " + ex.Message);
}
return(dt);
}
/// <summary>
/// DB커넥션 생성, 연결스트링 셋팅
/// </summary>
/// <returns>void</returns>
private void Initialize(DBCatalog db)
{
this.cn = new SqlConnection();
string dbName = "";
//if (Basic.GetRegistryKey("TestMode") == "YES")
// dbName = db.ToString() + "_TEST";
//else
// dbName = db.ToString();
dbName = db.ToString();
this.cn.ConnectionString
= Encryption.Decrypt(ConfigurationManager.ConnectionStrings[dbName].ToString(), true);
}
public void BeginTransaction(DBCatalog db)
{
if (transaction != null)
{
return;
}
try
{
CnOpen(db);
transaction = cn.BeginTransaction(IsolationLevel.ReadCommitted);
}
catch
{
CnClose();
throw;
}
}
public DataTable SelectSql(string sql, NameValueCollection nvParam, DBCatalog db)
{
DataTable dt = null;
try
{
CnOpen(db);
dt = SqlHelper.SqlSearchDataTable(cn, sql, nvParam);
nvParam.Clear();
nvParam = null;
CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[SelectSql] " + sql + "\n[SelectSql Error] " + ex.Message);
CnClose();
throw ex;
}
return(dt);
}
public string ExecuteSql(string sql, NameValueCollection nvParam, DBCatalog db)
{
string retVal = "";
try
{
CnOpen(db);
SqlHelper.SqlExecuteNonQuery(cn, sql, nvParam, transaction);
nvParam.Clear();
nvParam = null;
this.CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[ExecuteSql] " + sql + "\n[ExecuteSql Error] " + ex.Message);
this.CnClose();
throw ex;
}
return(retVal);
}
public DataSet ExecuteSpDataSet(string spName, NameValueCollection nvcParam, DBCatalog db)
{
DataSet ds = null;
try
{
CnOpen(db);
ds = SqlHelper.SqlExecuteSpDataSet(cn, spName, nvcParam);
nvcParam.Clear();
nvcParam = null;
CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[ExecuteSpDataSet] " + spName + "\n[ExecuteSpDataSet Error] " + ex.Message);
CnClose();
throw ex;
}
return(ds);
}
public string ExecuteSpScalar(string spName, NameValueCollection nvcParam, DBCatalog db)
{
string retVal = "";
try
{
CnOpen(db);
retVal = SqlHelper.SqlExecuteSpScalar(cn, spName, nvcParam, transaction);
nvcParam.Clear();
nvcParam = null;
CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[ExecuteSpScalar] " + spName + "[ExecuteSpScalar Error] " + ex.Message);
CnClose();
throw ex;
}
return(retVal);
}
public string ExecuteSql(string sql, DBCatalog db)
{
string retVal = "";
try
{
CnOpen(db);
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.CommandTimeout = 0;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
this.CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[ExecuteSql] " + sql + "[ExecuteSql Error] " + ex.Message);
this.CnClose();
throw ex;
}
return(retVal);
}
public DataTable SelectSql(string sql, DBCatalog db)
{
DataSet ds = new DataSet();
DataTable table = new DataTable();
ds.Tables.Add(table);
try
{
CnOpen(db);
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds.Tables[0]);
CnClose();
}
catch (Exception ex)
{
TextManager.WriteErrorLog("[SelectSql] " + sql + "\n[SelectSql Error] " + ex.Message);
CnClose();
throw ex;
}
return(ds.Tables[0]);
}
public static void BeginTransaction(DBCatalog db)
{
System.Diagnostics.Debug.WriteLine("[BeginTransaction] -> " + db.ToString());
ds.BeginTransaction(db);
}
/// <summary>
/// 프로시져실행후 DataTable반환
/// </summary>
/// <param name="spName">프로시져이름</param>
/// <param name="nvParam">파라메터</param>
/// <param name="db">DB</param>
/// <returns></returns>
public static DataTable ExecuteSpDataTable(string spName, NameValueCollection nvParam, DBCatalog db)
{
DataTable dt = null;
try
{
dt = ds.ExecuteSpDataTable(spName.Trim(), nvParam, db);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("[ERROR] " + ex.Message);
}
return(dt);
}
/// <summary>
/// Retrieves all Foreign keys.
/// </summary>
/// <param name="catalogMetaData">The catalog meta data.</param>
private void RetrieveForeignKeys(DBCatalog catalogMetaData)
{
#region Description of query used
//select fks.user_name AS FK_SCHEMA, fkt.table_name as FK_TABLE_NAME, fcol.column_name AS FK_COLUMN_NAME,
// pks.user_name AS PK_SCHEMA, pkt.table_name as PK_TABLE_NAME, pcol.column_name AS PK_COLUMN_NAME, sc.constraint_name as FK_NAME
//from sysfkey fk inner join systab fkt on fk.foreign_table_id = fkt.table_id
// inner join systab pkt on fk.primary_table_id = pkt.table_id
// inner join sysuser fks on fkt.creator = fks.user_id
// inner join sysuser pks on pkt.creator = pks.user_id
// inner join sysidxcol fic on fic.table_id = fk.foreign_table_id and fic.index_id = fk.foreign_index_id
// inner join sysidxcol pic on pic.table_id = fk.primary_table_id and pic.index_id = fk.primary_index_id
// and fic.primary_column_id = pic.column_id
// inner join syscolumn fcol on fic.table_id=fcol.table_id and fic.column_id = fcol.column_id
// inner join syscolumn pcol on pic.table_id=pcol.table_id and pic.column_id = pcol.column_id
// inner join sysidx si on si.table_id = fk.foreign_table_id and si.index_id = fk.foreign_index_id and si.index_category=2
// inner join sysconstraint sc on sc.ref_object_id=si.object_id
//where fks.user_name in (<schema list>) and pks.user_name in (<schema list>)
//order BY fks.user_name ASC, fkt.table_name ASC, fic.column_id ASC
#endregion
string inClause = String.Join(", ", catalogMetaData.Schemas.Select(s => string.Format("'{0}'", s.SchemaOwner)).ToArray());
string query = string.Format("select fks.user_name AS FK_SCHEMA, fkt.table_name as FK_TABLE_NAME, fcol.column_name AS FK_COLUMN_NAME, pks.user_name AS PK_SCHEMA, pkt.table_name as PK_TABLE_NAME, pcol.column_name AS PK_COLUMN_NAME, sc.constraint_name as FK_NAME from sysfkey fk inner join systab fkt on fk.foreign_table_id = fkt.table_id inner join systab pkt on fk.primary_table_id = pkt.table_id inner join sysuser fks on fkt.creator = fks.user_id inner join sysuser pks on pkt.creator = pks.user_id inner join sysidxcol fic on fic.table_id = fk.foreign_table_id and fic.index_id = fk.foreign_index_id inner join sysidxcol pic on pic.table_id = fk.primary_table_id and pic.index_id = fk.primary_index_id and fic.primary_column_id = pic.column_id inner join syscolumn fcol on fic.table_id=fcol.table_id and fic.column_id = fcol.column_id inner join syscolumn pcol on pic.table_id=pcol.table_id and pic.column_id = pcol.column_id inner join sysidx si on si.table_id = fk.foreign_table_id and si.index_id = fk.foreign_index_id and si.index_category=2 inner join sysconstraint sc on sc.ref_object_id=si.object_id where fks.user_name in ({0}) and pks.user_name in ({0}) order BY fks.user_name ASC, fkt.table_name ASC, fic.column_id ASC", inClause);
DbDataAdapter adapter = this.DriverToUse.CreateDataAdapter(query);
DataTable foreignKeys = new DataTable();
adapter.Fill(foreignKeys);
string currentPKTableName = string.Empty;
string currentFKTableName = string.Empty;
string currentFKName = string.Empty;
// traverse per FK table name the fields which are stored in an FK constraint per different PK table name.
DBForeignKeyConstraint newForeignKeyConstraint = new DBForeignKeyConstraint();
DBTable tableForeignKey = null;
DBTable tablePrimaryKey = null;
bool fkValid = false;
foreach (DataRow row in foreignKeys.AsEnumerable())
{
string previousPKTableName = currentPKTableName;
currentPKTableName = row.Value <string>("PK_SCHEMA") + row.Value <string>("PK_TABLE_NAME");
string previousFKTableName = currentFKTableName;
currentFKTableName = row.Value <string>("FK_SCHEMA") + row.Value <string>("FK_TABLE_NAME");
string previousFKName = currentFKName;
currentFKName = row.Value <string>("FK_NAME");
// if this is a new FK table, we've to start from scratch with a new FK constraint. If this isn't a new FK table, we've to check if this is a new
// PK table. if so, we've also to start from scratch with a new FK constraint. If this isn't a new PK table, we've to check whether the FK name
// changed. If so, we're dealing with a new FK constraint. Otherwise it's the same FK.
if ((previousFKTableName != currentFKTableName) || (previousPKTableName != currentPKTableName) || (previousFKName != currentFKName))
{
// create a new FK
fkValid = true;
newForeignKeyConstraint = new DBForeignKeyConstraint();
newForeignKeyConstraint.ConstraintName = "FK_" + Guid.NewGuid().ToString("N");
DBSchema schemaForeignKey = catalogMetaData.FindSchemaByName(row.Value <string>("FK_SCHEMA"));
if (schemaForeignKey == null)
{
fkValid = false;
continue;
}
tableForeignKey = schemaForeignKey.FindTableByName(row.Value <string>("FK_TABLE_NAME"));
// Get Primary Key Table, first get the schema, has to be there
DBSchema schemaPrimaryKey = catalogMetaData.FindSchemaByName(row.Value <string>("PK_SCHEMA"));
if (schemaPrimaryKey == null)
{
fkValid = false;
continue;
}
tablePrimaryKey = schemaPrimaryKey.FindTableByName(row.Value <string>("PK_TABLE_NAME"));
if ((tableForeignKey == null) || (tablePrimaryKey == null))
{
// not found. next
fkValid = false;
continue;
}
// Add to Foreign Key table.
tableForeignKey.ForeignKeyConstraints.Add(newForeignKeyConstraint);
}
// test again, if the FK is based on 2 or more fields, this test is required.
if (!fkValid)
{
// not valid, skip
if (tableForeignKey != null)
{
tableForeignKey.ForeignKeyConstraints.Remove(newForeignKeyConstraint);
}
continue;
}
newForeignKeyConstraint.AppliesToTable = tableForeignKey;
DBTableField foreignKeyField = tableForeignKey.FindFieldByName(row.Value <string>("FK_COLUMN_NAME"));
DBTableField primaryKeyField = tablePrimaryKey.FindFieldByName(row.Value <string>("PK_COLUMN_NAME"));
if ((foreignKeyField == null) || (primaryKeyField == null))
{
tableForeignKey.ForeignKeyConstraints.Remove(newForeignKeyConstraint);
fkValid = false;
continue;
}
newForeignKeyConstraint.PrimaryKeyFields.Add(primaryKeyField);
newForeignKeyConstraint.ForeignKeyFields.Add(foreignKeyField);
}
}
