public DataSet DeleteMembersFromOwner(string strOwnerTable, int iOwnerID, string strMemberTable, string strSwitcherTable)
{
string primaryKeyColumn1 = SqlDatabaseHelper.GetPrimaryKeyColumn(strOwnerTable);
SqlDatabaseHelper.GetPrimaryKeyColumn(strSwitcherTable);
string primaryKeyColumn2 = SqlDatabaseHelper.GetPrimaryKeyColumn(strMemberTable);
string str = string.Format("Select [{0}] From [{1}] Where [{1}].[{2}] IN (Select [{2}] From [{3}] Where [{3}].[{2}]={4}))", (object)primaryKeyColumn2, (object)strSwitcherTable, (object)primaryKeyColumn1, (object)strOwnerTable, (object)iOwnerID);
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("Detete From [{0}] Where [{1}] IN ({2})", (object)strMemberTable, (object)primaryKeyColumn2, (object)str))));
}
public virtual DataSet GetObjectsForDataLookup(string[] arrColumns, int iMaxResults, string strCondition)
{
string str = string.Format("Select TOP({0}) ", (object)iMaxResults);
foreach (string arrColumn in arrColumns)
{
str += string.Format("[{0}],", (object)arrColumn);
}
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(str.Remove(str.Length - 1, 1) + string.Format(" FROM [{0}] WHERE [AAStatus]='Alive'", (object)this.dal.TableName) + " AND " + strCondition)));
}
public void DeleteAllObjectsByObjectParentID(int iObjectParentID)
{
string primaryKeyColumn = SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName);
string strColumnName = primaryKeyColumn.Substring(0, primaryKeyColumn.Length - 2) + "ParentID";
if (!SqlDatabaseHelper.ColumnIsExistInTable(this.TableName, strColumnName))
{
return;
}
SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("Update [{0}] Set AAStatus = 'Delete' Where ([{1}]={2}) And ({2}>0)", (object)this.TableName, (object)strColumnName, (object)iObjectParentID)));
}
public virtual int GetRecordsCount()
{
int num = -1;
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("select count(" + (this.dal.TableName.Substring(0, this.dal.TableName.Length - 1) + "ID") + ") as count from " + this.dal.TableName + " where AAStatus = 'Alive' ")));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
return(Convert.ToInt32(dataSet.Tables[0].Rows[0][0]));
}
return(num);
}
public virtual DataSet GetObjectsByIDForDataLookup(string[] arrColumns, int iObjectID)
{
string str = "Select ";
string tablePrimaryColumn = new VinaDbUtil().GetTablePrimaryColumn(this.dal.TableName);
foreach (string arrColumn in arrColumns)
{
str += string.Format("[{0}],", (object)arrColumn);
}
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(str.Remove(str.Length - 1, 1) + string.Format(" FROM [{0}] WHERE [{1}]={2} AND [AAStatus]='Alive'", (object)this.dal.TableName, (object)tablePrimaryColumn, (object)iObjectID))));
}
public static string GetQueryCommandByTableNameAndTableQueryKey(string strTableName, string strTableQueryKey)
{
string empty = string.Empty;
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("SELECT * FROM [dbo].[STTableQueries] WHERE ([STTableQueryTableName]='{0}')AND([STTableQueryKey]='{1}')", (object)strTableName, (object)strTableQueryKey)));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
empty = dataSet.Tables[0].Rows[0]["STTableQueryCommand"].ToString();
}
return(empty);
}
public static SortedList GetAllTablePrimaryColumns()
{
SortedList sortedList = new SortedList();
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery("SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE" + "(kcu.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES\tWHERE(TABLE_TYPE='BASE TABLE')AND(TABLE_NAME<>'sysdiagrams')))" + " AND(kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)AND(tc.CONSTRAINT_TYPE='PRIMARY KEY') AND kcu.TABLE_SCHEMA <> ('HangFire')"));
if (dataSet.Tables.Count > 0)
{
foreach (DataRow row in (InternalDataCollectionBase)dataSet.Tables[0].Rows)
{
string str1 = row["TABLE_NAME"].ToString();
string str2 = row["COLUMN_NAME"].ToString();
sortedList.Add((object)str1, (object)str2);
}
}
return(sortedList);
}
public static DataSet RunQuery(string strTableName, string strQueryCommandKey, string strQueryCommand, params object[] paramValues)
{
DbCommand query = SqlDatabaseHelper.GetQuery(strTableName, strQueryCommandKey, strQueryCommand);
string[] parameters = SqlDatabaseHelper.GetParameters(strQueryCommand);
if (parameters != null)
{
for (int index = 0; index < parameters.Length; ++index)
{
if (paramValues[index].GetType().Equals(typeof(int)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.Int, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(bool)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.Bit, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(short)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.SmallInt, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(double)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.Float, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(Decimal)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.Decimal, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(string)) || paramValues[index].GetType().Equals(typeof(string)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.NVarChar, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(DateTime)))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.DateTime, paramValues[index]);
}
else if (paramValues[index].GetType().Equals(typeof(byte[])))
{
SqlDatabaseHelper.database.AddInParameter(query, parameters[index], SqlDbType.VarBinary, paramValues[index]);
}
}
}
return(SqlDatabaseHelper.RunQuery(query));
}
public static string GetPrimaryKeyColumn(string strTableName)
{
string empty = string.Empty;
if (SqlDatabaseHelper.lstPrimaryColumns[(object)strTableName] != null)
{
empty = SqlDatabaseHelper.lstPrimaryColumns[(object)strTableName].ToString();
}
else
{
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery("SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " + string.Format("WHERE (kcu.TABLE_NAME='{0}')", (object)strTableName) + "AND(kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)AND(tc.CONSTRAINT_TYPE='PRIMARY KEY')"));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
empty = dataSet.Tables[0].Rows[0]["COLUMN_NAME"].ToString();
}
}
return(empty);
}
public DateTime GetLastUpdatedDateOfTable(string strTableName)
{
try
{
DateTime dateTime = DateTime.MinValue;
string strColumnName = "AAUpdatedDate";
if (this.ColumnIsExist(strTableName, strColumnName))
{
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("Select MAX([{0}]) From [{1}] Where DATEDIFF(d, [{0}], '9999-12-31') > 0", (object)strColumnName, (object)strTableName)));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
dateTime = Convert.ToDateTime(dataSet.Tables[0].Rows[0][0]);
}
}
return(dateTime);
}
catch (Exception ex)
{
return(DateTime.MinValue);
}
}
public DataSet GetAllObjectsByObjectParentID(int iObjectParentID)
{
string primaryKeyColumn = SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName);
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("Select *, (select COUNT(*) from [{0}] tbl2 where tbl2.[{1}] = tbl1.[{3}] and tbl2.AAStatus = 'Alive') as TheNumberOfChild From [{0}] tbl1 Where AAStatus = 'Alive' And ([{1}]={2}) And ({2}>0)", (object)this.TableName, (object)(primaryKeyColumn.Substring(0, primaryKeyColumn.Length - 2) + "ParentID"), (object)iObjectParentID, (object)primaryKeyColumn))));
}
public DataSet GetDataSet(string strQuery)
{
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(strQuery)));
}
public static void InsertQueryCommand(string strQueryCommand, string strTableName, string strQueryKey)
{
DbCommand query = SqlDatabaseHelper.GetQuery(string.Format("INSERT INTO [dbo].[STTableQueries] ([STTableQueryID],[STTableQueryTableName],[STTableQueryKey],[STTableQueryCommand]) VALUES({0},'{1}','{2}','{3}')", (object)(SqlDatabaseHelper.GetMaxID("STTableQueries") + 1), (object)strTableName, (object)strQueryKey, (object)strQueryCommand));
((Database)SqlDatabaseHelper.database).ExecuteNonQuery(query);
}
public static bool ColumnIsPrimaryKey(string strTableName, string strColumnName)
{
DataSet dataSet = SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("SELECT kcu.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE kcu.TABLE_NAME = '{0}' AND kcu.COLUMN_NAME = '{1}' AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'", (object)strTableName, (object)strColumnName)));
return(dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0);
}
public static DataSet GetTableColumn(string strTableName, string strColumnName)
{
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", (object)strTableName, (object)strColumnName))));
}
public DataSet GetDataFromTableNameWithTableColumnLikeColumnValue(string strTableName, string strColumnName, string strColumnValue)
{
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(string.Format("SELECT * FROM [dbo].[{0}] WHERE [{1}] LIKE '%{2}%' AND [AAStatus] = 'Alive' ORDER BY [{1}]", (object)strTableName, (object)strColumnName, (object)strColumnValue))));
}