private void UpdateDeletedApp(int appId)
{
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(Durados.Windows.Utilities.AzureUploader.Properties.Settings.Default.LocalConnection);
command.CommandText = "UPDATE durados_App SET [ToDelete]=1,[deleteddate] =getdate() WHERE Id=@Id";
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("Id", appId));
try
{
command.Connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update app " + appId.ToString()); }
command.CommandText = "Delete From durados_UserApp WHERE appId=@Id";
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update user app " + appId.ToString()); }
}
}
internal void DropTable(View view)
{
SqlSchema sqlSchema = new SqlSchema();
IDbCommand command = sqlSchema.GetCommand();
using (command.Connection = sqlSchema.GetConnection(view.Database.ConnectionString))
{
command.Connection.Open();
if (sqlSchema.IsViewExists(view.Name, command))
{
sqlSchema.DropView(view.Name, command);
if (!string.IsNullOrEmpty(view.EditableTableName))
{
if (sqlSchema.IsTableExists(view.EditableTableName, command))
{
sqlSchema.DropTable(view.EditableTableName, command);
}
}
}
else
{
if (sqlSchema.IsTableExists(view.Name, command))
{
sqlSchema.DropTable(view.Name, command);
}
}
logger.Log("ProductMaintenance", "RemoveApp", "DropTable", null, 3, "In Catalog " + command.Connection.Database + " view:" + view.Name + " and his editable table was droped ");
}
}
private static HashSet <string> GetReferencedTables(string tableName, string connectionString)
{
HashSet <string> referenceTables = new HashSet <string>();
string sql = @"SELECT KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME WHERE KCU1.TABLE_NAME=N'" + tableName + "'";
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(connectionString);
command.CommandText = sql;
try
{
command.Connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
referenceTables.Add(reader["REFERENCED_TABLE_NAME"].ToString());
}
return(referenceTables);
}
catch (Exception ex)
{
throw new DuradosException("<br>Failed to get referenced table for " + tableName);
}
}
}
private App GetApp(string sql, string parameterName, string parameterVal)
{
App app = new App();
SqlSchema schema = new SqlSchema();
IDbCommand command = schema.GetCommand();
command.Connection = schema.GetConnection(Maps.Instance.DuradosMap.connectionString);
ValidateSelectFunctionExists(command);
command.CommandText = sql;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(parameterName, parameterVal));
try
{
command.Connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
app.Server = reader.GetString(reader.GetOrdinal("ServerName"));
app.Catalog = reader.GetString(reader.GetOrdinal("catalog"));
app.SystemServer = reader.GetString(reader.GetOrdinal("sysServerName"));
app.SystemCatalog = reader.GetString(reader.GetOrdinal("sysCatalog"));
app.AppType = reader.GetInt32(reader.GetOrdinal("AppType"));
app.Name = reader.GetString(reader.GetOrdinal("Name"));
app.AppId = reader.GetInt32(reader.GetOrdinal("Id"));
}
reader.Close();
}
}
finally
{
command.Connection.Close();
}
return(app);
}
private void UpdateDeletedApp(int appId)
{
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(Maps.Instance.DuradosMap.connectionString);
command.CommandText = "UPDATE durados_App SET [ToDelete]=1,[deleteddate] =getdate() WHERE Id=@Id";
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("Id", appId));
try
{
command.Connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update app " + appId.ToString()); }
command.CommandText = "Delete From durados_UserApp WHERE appId=@Id";
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update user app " + appId.ToString()); }
}
}
public virtual System.Data.IDbCommand GetCommand(string connectionString = null)
{
SqlSchema schema = GetSchema();
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConnectionString;
}
return(schema.GetCommand(string.Empty, schema.GetConnection(connectionString)));
}
public static DataTable GetToDeleteAppsTable(IDbCommand command)
{
DataTable dt = null;
SqlSchema sqlSchema = new SqlSchema();
SqlAccess sqlAccess = new SqlAccess();
if (command == null)
{
command = sqlSchema.GetCommand();
}
command.CommandText = "Select * from [" + ProductMaintenance.toDeleteViewName + "]";
using (IDbConnection connection = sqlSchema.GetConnection(Durados.Windows.Utilities.AzureUploader.Properties.Settings.Default.LocalConnection))
{
command.Connection = connection;
dt = sqlAccess.ExecuteTable(((DuradosCommand)command).Command, command.CommandText, null);
}
return(dt);
}
public static DataTable GetToDeleteAppsTable(IDbCommand command)
{
DataTable dt = null;
SqlSchema sqlSchema = new SqlSchema();
SqlAccess sqlAccess = new SqlAccess();
if (command == null)
{
command = sqlSchema.GetCommand();
}
command.CommandText = "Select * from [" + ProductMaintenance.toDeleteViewName + "]";
using (IDbConnection connection = sqlSchema.GetConnection(Maps.Instance.DuradosMap.connectionString))
{
command.Connection = connection;
dt = sqlAccess.ExecuteTable(((DuradosCommand)command).Command, command.CommandText, null);
}
return(dt);
}
internal void DropTable(string tableName, string connectionString)
{
SqlSchema sqlSchema = new SqlSchema();
// SqlAccess sqlAccess= new SqlAccess();
IDbCommand command = sqlSchema.GetCommand();
try
{
using (command.Connection = sqlSchema.GetConnection(connectionString))
{
command.Connection.Open();
if (sqlSchema.IsTableExists(tableName, command))
{
sqlSchema.DropTable(tableName, command);
}
}
}
catch (System.Data.SqlClient.SqlException exception)
{
logger.Log("ProductMaintenance", "RemoveApp", "DropTable", null, 3, "In Catalog " + command.Connection.Database + " view:" + tableName + " Fail!!!! was droped ");
}
logger.Log("ProductMaintenance", "RemoveApp", "DropTable", null, 3, "In Catalog " + command.Connection.Database + " view:" + tableName + " and his editable table was droped ");
}