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 object GetDataObject(string spName, params object[] paramValues)
{
return(this.GetSingleObject(SqlDatabaseHelper.RunStoredProcedure(spName, paramValues).Tables[0]));
}
public object GetObjectFromDataRow(DataRow row)
{
return(SqlDatabaseHelper.GetObjectFromDataRow(row, this.ObjectType));
}
public virtual string GetObjectNoByID(int iObjectID)
{
object objectById = this.GetObjectById(iObjectID);
if (objectById != null)
{
PropertyInfo property = this.ObjectType.GetProperty(SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName).Substring(0, SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName).Length - 2) + "No");
if (property != null)
{
return(property.GetValue(objectById, (object[])null).ToString());
}
}
return(string.Empty);
}
public DataSet GetDataSet(string strQuery)
{
return(SqlDatabaseHelper.RunQuery(SqlDatabaseHelper.GetQuery(strQuery)));
}
public virtual int CreateObject(object obj)
{
return(SqlDatabaseHelper.InsertObject(obj, this, this.GenerateInsertStoredProcedureName()));
}
public virtual void DeleteAllObjects()
{
SqlDatabaseHelper.RunStoredProcedure(this.GenerateDeleteAllStoredProcedureName());
}
private string GenerateDeleteQuery()
{
return(string.Format("DELETE FROM [dbo].[{0}] WHERE [{1}]=@{2}", (object)this.TableName, (object)SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName), (object)SqlDatabaseHelper.GetPrimaryKeyColumn(this.TableName)));
}
public void SetValueToPrimaryColumn(object obj, int iObjectID)
{
SqlDatabaseHelper.SetValueToPrimaryColumn(obj, this, iObjectID);
}
public static int InsertObject(object obj, DALBaseProvider provider, string spName, DbTransaction transaction)
{
try
{
DbCommand storedProcCommand = ((Database)SqlDatabaseHelper.database).GetStoredProcCommand(spName);
SqlDatabaseHelper.AddParameterForObject(obj, provider, storedProcCommand);
storedProcCommand.Transaction = transaction;
((Database)SqlDatabaseHelper.database).ExecuteNonQuery(storedProcCommand, transaction);
transaction.Commit();
return((int)((Database)SqlDatabaseHelper.database).GetParameterValue(storedProcCommand, SqlDatabaseHelper.GetPrimaryKeyColumn(provider.TableName)));
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
public static bool TestConnection()
{
return(SqlDatabaseHelper.TestConnection(SqlDatabaseHelper.database));
}
public static void AddParameterForObject(object obj, DALBaseProvider provider, DbCommand cmd)
{
try
{
SqlDatabaseHelper.lstTableColumnNames.Clear();
DataSet allTableColumns = SqlDatabaseHelper.GetAllTableColumns(provider.TableName);
if (allTableColumns.Tables.Count > 0)
{
foreach (DataRow row in (InternalDataCollectionBase)allTableColumns.Tables[0].Rows)
{
SqlDatabaseHelper.lstTableColumnNames.Add(row["COLUMN_NAME"].ToString());
}
}
PropertyInfo[] properties = provider.ObjectType.GetProperties();
for (int index = 0; index < properties.Length; ++index)
{
if (SqlDatabaseHelper.lstTableColumnNames.IndexOf(properties[index].Name) >= 0)
{
object obj1 = properties[index].GetValue(obj, (object[])null);
if (properties[index].PropertyType.Equals(typeof(int)))
{
if ((properties[index].Name == provider.TableName.Substring(0, provider.TableName.Length - 1) + "ID" || properties[index].Name == provider.TableName + "ID") && cmd.CommandText.Contains("_Insert"))
{
SqlDatabaseHelper.database.AddOutParameter(cmd, properties[index].Name, SqlDbType.Int, 4);
}
else
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.Int, obj1);
}
}
else if (properties[index].PropertyType.Equals(typeof(bool)))
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.Bit, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(short)))
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.SmallInt, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(byte)))
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.TinyInt, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(double)))
{
if (double.IsInfinity((double)obj1))
{
obj1 = (object)0.0;
}
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.Float, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(Decimal)))
{
((Database)SqlDatabaseHelper.database).AddParameter(cmd, properties[index].Name, DbType.Decimal, 24, ParameterDirection.Input, true, (byte)18, (byte)5, properties[index].Name, DataRowVersion.Default, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(string)) || properties[index].PropertyType.Equals(typeof(string)))
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.NVarChar, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(DateTime)))
{
if ((DateTime)obj1 == DateTime.MinValue)
{
obj1 = (object)DateTime.MaxValue;
}
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.DateTime, obj1);
}
else if (properties[index].PropertyType.Equals(typeof(byte[])))
{
SqlDatabaseHelper.database.AddInParameter(cmd, properties[index].Name, SqlDbType.VarBinary, obj1);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static int GetMaxID(string tableName)
{
int num = 0;
string str = string.Format("SELECT Max({0}) AS MaxID FROM [{1}]", (object)SqlDatabaseHelper.GetPrimaryKeyColumn(tableName), (object)tableName);
DbCommand sqlStringCommand = ((Database)SqlDatabaseHelper.database).GetSqlStringCommand(str);
DataSet dataSet = ((Database)SqlDatabaseHelper.database).ExecuteDataSet(sqlStringCommand);
if (dataSet.Tables.Count > 0)
{
DataRow row = dataSet.Tables[0].Rows[0];
if (row[0].ToString() != "")
{
num = (int)row[0];
}
}
return(num);
}
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 void CommitTransaction(DbTransaction transaction)
{
SqlDatabaseHelper.CommitTransaction(transaction);
}
public void SetValueToIDStringColumn(object obj, int iObjectID)
{
SqlDatabaseHelper.SetValueToIDStringColumn(obj, this, iObjectID);
}
public void RollbackTransaction(DbTransaction transaction)
{
SqlDatabaseHelper.RollbackTransaction(transaction);
}
public object GetPrimaryColumnValue(object obj)
{
return(SqlDatabaseHelper.GetPrimaryColumnValue(obj, this));
}
public virtual int UpdateObject(object obj, DbTransaction transaction)
{
return(SqlDatabaseHelper.InsertObject(obj, this, this.GenerateUpdateStoredProcedureName(), transaction));
}
public virtual int GetMaxID()
{
return(SqlDatabaseHelper.GetMaxID(this.TableName));
}
public virtual void DeleteByForeignColumn(string strForeignColumn, object objValue)
{
SqlDatabaseHelper.RunStoredProcedure(this.GenerateDeleteByForeignColumnStoredProcedureName(strForeignColumn), objValue);
}
public DbCommand GetStoredProcedureCommand(string spName)
{
return(SqlDatabaseHelper.GetStoredProcedure(spName));
}
public DataSet GetDataSet(string spName, params object[] paramValues)
{
return(SqlDatabaseHelper.RunStoredProcedure(spName, paramValues));
}
public void AddParameter(DbCommand cmd, string name, DbType type, ParameterDirection direction, object value)
{
SqlDatabaseHelper.AddParameter(cmd, name, type, direction, value);
}
public DataSet GetDataSet(DbCommand cmd)
{
return(SqlDatabaseHelper.RunStoredProcedure(cmd));
}
public void ExecuteStoredProcedure(string spName, params object[] parameters)
{
SqlDatabaseHelper.RunStoredProcedure(spName, parameters);
}
public object GetSingleObject(DataTable dt)
{
return(SqlDatabaseHelper.GetSingleObject(dt, this.ObjectType));
}
public DbTransaction BeginTransaction()
{
return(SqlDatabaseHelper.BeginTransaction());
}
public DataSet GetAllDataByForeignColumn(string strForeignColumnName, object objValue)
{
return(SqlDatabaseHelper.RunStoredProcedure(this.GenerateSelectByForeignColumnStoredProcedureName(strForeignColumnName), objValue));
}
public static bool IsColumnAllowNull(string strTableName, string strColumnName)
{
DataSet tableColumn = SqlDatabaseHelper.GetTableColumn(strTableName, strColumnName);
return(tableColumn.Tables.Count <= 0 || tableColumn.Tables[0].Rows.Count <= 0 || tableColumn.Tables[0].Rows[0]["IS_NULLABLE"].ToString() == "YES");
}
