/// <summary>
/// does a soft delete on the organization
/// </summary>
/// <param name="organzationId"></param>
/// <param name="user"></param>
/// <returns></returns>
public bool RemoveOrganization(int organzationId)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "remove_organization";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = organzationId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteScalar(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(false);
}
/// <summary>
/// Tests the data.
/// </summary>
/// <param name="sqlOrTableName">Name of the SQL or table.</param>
/// <returns></returns>
public bool TestData(string sqlOrTableName)
{
PostgreSQLDB postDb = new PostgreSQLDB(this.ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.Text;
if (sqlOrTableName.LastIndexOf(" ") > 0)
{
Command.CommandText = string.Format(@"SELECT COUNT(*) FROM ({0}) as f1; ", sqlOrTableName);
}
else
{
Command.CommandText = string.Format(@"SELECT COUNT(*) FROM ""{0}"" as f1; ", sqlOrTableName);
}
int i = Convert.ToInt32(postDb.ExecuteScalar(Command));
if (i <= 0)
{
return(false);
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// Removes the user.
/// </summary>
/// <param name="userId">The user id.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public bool RemoveUser(int userId)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
int flag = 0;
try
{
flag = Convert.ToInt32(db.ExecuteScalar(null));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (flag == 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Saves the specified canvas dto.
/// </summary>
/// <param name="canvasDto">The canvas dto.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public int Save(CanvasDto canvasDto)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
string query = string.Empty;
CanvasDto canvasDtoToReturn = canvasDto;
int canvasId = -1;
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "save_canvas";
NpgsqlParameter parameter = new NpgsqlParameter("cname", NpgsqlTypes.NpgsqlDbType.Text);
parameter.Value = canvasDto.CanvasName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = canvasDto.UserId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("cdesc", NpgsqlTypes.NpgsqlDbType.Text);
parameter.Value = canvasDto.CanvasDescription;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("cdate", NpgsqlTypes.NpgsqlDbType.Date);
parameter.Value = canvasDto.CreatedDate;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("mdate", NpgsqlTypes.NpgsqlDbType.Date);
parameter.Value = canvasDto.ModifiedDate;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("did", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = canvasDto.DatasourceID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("isnewcanvas", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = canvasDto.IsNewCanvas;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = canvasDto.CanvasId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("xmlcontent", NpgsqlTypes.NpgsqlDbType.Text);
parameter.Value = canvasDto.XmlData.ToString();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
canvasId = Convert.ToInt32(db.ExecuteScalar(Command));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
canvasDtoToReturn.CanvasId = canvasId;
return(canvasId);
}
/// <summary>
/// Adds the datasource.
/// </summary>
/// <param name="dsDto">The ds dto.</param>
/// <returns></returns>
public bool AddDatasource(DTO.DatasourceDto dsDto)
{
PostgreSQLDB postDb = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
int dsId = -1;
Command.CommandType = System.Data.CommandType.StoredProcedure;
Command.CommandText = "add_datasource";
Cryptography cy = new Cryptography();
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.DatasourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dsDto.OrganizationId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dsservername", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.ServerName);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbtype", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.Connection.DatabaseType.ToString();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("icatalog", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.DatabaseName);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("psinfo", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.Connection.PersistSecurityInfo.ToString();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbuid", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.UserId);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pwd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.Password);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.DatabaseObject);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("sqlqry", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dsDto.SQLQuery();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dsDto.IsActive;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
StringBuilder sb = new StringBuilder();
try
{
foreach (Ewav.DTO.UserDTO item in dsDto.AssociatedUsers)
{
sb.Append(item.UserID);
sb.Append(",");
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
parameter = new NpgsqlParameter("userids", NpgsqlTypes.NpgsqlDbType.Varchar);
if (sb.ToString().Contains(","))
{
parameter.Value = sb.ToString().Substring(0, sb.ToString().Length - 1);
}
else
{
parameter.Value = "";
}
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pnumber", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.PortNumber.ToString());
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
dsId = Convert.ToInt32(postDb.ExecuteScalar(Command));
}
catch (Exception)
{
return(false);
}
return(true);
}