public string GetDatabaseObject(string dataSourceName)
{
DataTable dtGet;
PostgreSQLDB ewavDB = new PostgreSQLDB(this.MetaDataConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_external_connec_str";
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dataSourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
dtGet = ewavDB.ExecuteDataSet(Command).Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(dtGet.Rows[0]["DatabaseObject"].ToString());
}
/// <summary>
/// Gets all available data sources
/// </summary>
/// <returns>A data table with one row of meta data for each data source</returns>
public DataTable GetAllDataSources(string userName)
{
PostgreSQLDB db = new PostgreSQLDB(this.MetaDataConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_all_datasources";
NpgsqlParameter parameter = new NpgsqlParameter("uname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Gets the type of the database.
/// </summary>
/// <param name="dataSourceName">Name of the data source.</param>
/// <returns></returns>
public DataBaseTypeEnum GetDatabaseType(string dataSourceName)
{
PostgreSQLDB ewavDB = new PostgreSQLDB(this.MetaDataConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_database_type";
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dataSourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataTable dtGet = ewavDB.ExecuteDataSet(Command).Tables[0];
return((DataBaseTypeEnum)Enum.Parse(typeof(DataBaseTypeEnum),
dtGet.Rows[0]["DatabaseType"].ToString()));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Reads the associated datasources.
/// </summary>
/// <param name="UserId">The user id.</param>
/// <param name="OrganizationId">The organization id.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataSet ReadAssociatedDatasources(int UserId, int OrganizationId)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_datasource";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = OrganizationId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = UserId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Gets the columns for datasource.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public DataTable GetColumnsForDatasource(DataRow[] dr)
{
Security.Cryptography cy = new Security.Cryptography();
string tableName = cy.Decrypt(dr[0]["DatabaseObject"].ToString());
string externalConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL, dr);
string query = "";
if (tableName.SQLTest())
{
query = string.Format("SELECT * FROM ( {0} ) as table1 limit 1; ", tableName);
}
else
{
query = string.Format("SELECT * FROM {0} limit 1;", tableName);
}
DataTable dt = new DataTable();
PostgreSQLDB pstdb = new PostgreSQLDB(externalConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandText = query;
Command.CommandType = CommandType.Text;
dt = pstdb.ExecuteDataSet(Command).Tables[0];
return(dt);
}
/// <summary>
/// Reads the name of the user by user.
/// </summary>
/// <param name="UserName">Name of the user.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataSet ReadUserByUserName(string UserName)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_by_username";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = UserName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Gets the table.
/// </summary>
/// <param name="Datasoucename">The datasoucename.</param>
/// <param name="connStr">The conn STR.</param>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
public DataTable GetTopTwoTable(string Datasoucename, string connStr, string tableName)
{
PostgreSQLDB pdb = new PostgreSQLDB(connStr);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.Text;
if (tableName.SQLTest())
{
Command.CommandText =
string.Format("SELECT * FROM ( {0} ) as f1 limit 1; ", tableName);
}
else
{
Command.CommandText =
string.Format("SELECT * FROM {0} limit 1; ", tableName);
}
NpgsqlParameter parameter = new NpgsqlParameter("tablename", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = tableName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataTable dtGet = pdb.ExecuteDataSet(Command).Tables[0];
return(dtGet);
}
/// <summary>
/// Shares the canvas.
/// </summary>
/// <param name="CanvasId">The canvas id.</param>
/// <param name="SharedUserIdList">The shared user id list.</param>
/// <exception cref="System.Exception"></exception>
public void ShareCanvas(int CanvasId, List <int> SharedUserIdList)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
for (int i = 0; i < SharedUserIdList.Count; i++)
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "sharecanvas";
NpgsqlParameter parameter = new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = CanvasId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = SharedUserIdList[i];
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// Gets the user for authentication.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <param name="passwordHash">The password hash.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataTable GetUserForAuthentication(string userName, string passwordHash)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_user_for_authentication";
NpgsqlParameter parameter = new NpgsqlParameter("uname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pwdhash", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = passwordHash;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Reads all orgs for user.
/// </summary>
/// <param name="userID">The user ID.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataTable ReadAllOrgsForUser(int userID)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_all_organization_for_user";
NpgsqlParameter parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = userID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Reads the associated users.
/// </summary>
/// <param name="orgId">The org id.</param>
/// <returns></returns>
public DataSet ReadAssociatedUsers(int orgId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_user";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = orgId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = "";
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds);
}
/// <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>
/// Gets the columns for datasource.
/// </summary>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataTable GetColumnsForDatasource()
{
DataSet ds = null;
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.Text;
if (TableName.LastIndexOf(" ") > 0)
{
Command.CommandText = "select * from (" + TableName + ") as f1 limit 1;";
}
else
{
Command.CommandText = "select * from " + TableName + " limit 1;";
}
try
{
ds = db.ExecuteDataSet(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(ds.Tables[0]);
}
/// <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>
/// Gets the records count.
/// </summary>
/// <param name="TableName">Name of the table.</param>
/// <param name="whereClause">The where clause.</param>
/// <returns></returns>
public string GetTotalRecordsCount(string connStr, string TableName)
{
PostgreSQLDB pdb = new PostgreSQLDB(connStr);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.Text;
if (TableName.SQLTest())
{
Command.CommandText =
string.Format(@"SELECT * FROM ""{0}"" as f1; ", TableName);
}
else
{
Command.CommandText =
string.Format(@"SELECT * FROM ""{0}""; ", TableName);
}
DataTable dtGet = pdb.ExecuteDataSet(Command).Tables[0];
string returnString = string.Format("{0}", dtGet.Rows.Count);
return(returnString);
}
/// <summary>
/// Loads the canvas list for user.
/// </summary>
/// <param name="UserId">The user id.</param>
/// <returns></returns>
public DataSet LoadCanvasListForUser(int UserId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
DataSet ds;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_all_canvases";
NpgsqlParameter parameter = new NpgsqlParameter("UserId", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = UserId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
ds = db.ExecuteDataSet(Command);
}
catch (Exception Ex)
{
throw;
}
return(ds);
}
/// <summary>
/// Deletes the canvas.
/// </summary>
/// <param name="canvasId">The canvas id.</param>
/// <exception cref="System.Exception">Error deleting the canvas. + ex.Message</exception>
public void DeleteCanvas(int canvasId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "delete_canvas";
NpgsqlParameter parameter = new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = canvasId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception("Error deleting the canvas. " + ex.Message);
}
}
/// <summary>
/// Tests the DB connection.
/// </summary>
/// <param name="connStr">The conn STR.</param>
/// <returns></returns>
public bool TestDBConnection(string connStr)
{
PostgreSQLDB postDb = new PostgreSQLDB(connStr);
try
{
postDb.OpenConnection();
postDb.CloseConnection();
return(true);
}
catch (Exception ex)
{
postDb.CloseConnection();
postDb = null;
return(false);
}
}
/// <summary>
/// Reads the datasource.
/// </summary>
/// <param name="orgId">The org id.</param>
/// <returns></returns>
public DataSet ReadDatasource(int orgId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_datasource";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = orgId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataSet ds = db.ExecuteDataSet(Command);
return(ds);
}
/// <summary>
/// Reads the user names.
/// </summary>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataSet ReadUserNames()
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_usernames";
DataSet ds = db.ExecuteDataSet(Command);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Reads the super admin from ewav.
/// </summary>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public string ReadSuperAdminFromEwav()
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_super_admin_from_ewav";
DataSet ds = db.ExecuteDataSet(Command);
return(ds.Tables[0].Rows[0][0].ToString());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Reads all users in my org.
/// </summary>
/// <param name="orgId">The org id.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public DataSet ReadAllUsersInMyOrg(int orgId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
DataSet ds;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_user";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = orgId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = "";
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("rid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
ds = db.ExecuteDataSet(Command);
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
return(ds);
}
/// <summary>
/// Gets the external connection string.
/// </summary>
/// <param name="dataSourceName">Name of the data source.</param>
/// <param name="tableName"></param>
/// <returns></returns>
public string GetExternalConnectionString(string dataSourceName, out string tableName) // out DataBaseTypeEnum databaseType)
{
DataTable dtGet;
PostgreSQLDB ewavDB = new PostgreSQLDB(this.MetaDataConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_external_connec_str";
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dataSourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
dtGet = ewavDB.ExecuteDataSet(Command).Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
string extConnectionString = " ";
extConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL,
new DataRow[] { dtGet.Rows[0] });
Ewav.Security.Cryptography cy = new Security.Cryptography();
tableName = cy.Decrypt(dtGet.Rows[0]["DatabaseObject"].ToString());
return(extConnectionString);
}
/// <summary>
/// Gets the connection string from DB.
/// </summary>
/// <param name="DsName">Name of the ds.</param>
/// <param name="ConnectionString">The connection string.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public string GetConnectionStringFromDB(string DsName, string ConnectionString)
{
DataSet ds = null;
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.Text;
Command.CommandText = "Select * From Datasource where DatasourceName = '" +
DsName + "' Limit 1;";
try
{
ds = db.ExecuteDataSet(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(Utilities.CreateConnectionString(DTO.DataBaseTypeEnum.PostgreSQL, new DataRow[] { ds.Tables[0].Rows[0] }));
}
/// <summary>
/// reads all the organization
/// there is no apparent reason for the user to read his organization only.
/// </summary>
/// <param name="user"></param>
/// <param name="organizationId"></param>
/// <returns></returns>
public DataSet ReadAllOrganizations()
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_all_organization_tallys";
DataSet ds = null;
string query = string.Empty;
try
{
ds = db.ExecuteDataSet(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(ds);
}
/// <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>
/// Gets the records count.
/// </summary>
/// <param name="connStr">The conn STR.</param>
/// <param name="TableName">Name of the table.</param>
/// <param name="whereClause">The where clause.</param>
/// <returns></returns>
public string GetRecordsCount(string connStr, string TableName, string whereClause)
{
PostgreSQLDB pdb = new PostgreSQLDB(connStr);
if (whereClause.Length > 0)
{
whereClause = whereClause.Replace("= missing", " is NULL ").Replace("#", "'");
}
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
NpgsqlParameter parameter = new NpgsqlParameter("TableName", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = TableName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("Test", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = TableName.SQLTest();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("whereClause", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = whereClause;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataTable dtGet;
string returnString;
dtGet = pdb.ExecuteDataSet(Command).Tables[0];
returnString = string.Format("{0}", dtGet.Rows[0][0].ToString());
return(returnString);
}
/// <summary>
/// Updates the organization Info, updates the name
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool UpdateOrganization(OrganizationDto dto)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "update_organization";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dto.Id;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("orgname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dto.Name;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dto.Active;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(false);
}
/// <summary>
/// Gets the total records.
/// </summary>
/// <param name="externalDatasourceRow">The external datasource row.</param>
/// <returns></returns>
public long GetRecordsCount()
{
string externalConnectionString = this.MetaDataConnectionString;
PostgreSQLDB pdb = new PostgreSQLDB(externalConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_records_count";
NpgsqlParameter parameter = new NpgsqlParameter("count", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = "";
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("metadataviewname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("Test", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = 0;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("whereClause", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = "";
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
DataTable dt = pdb.ExecuteDataSet(Command).Tables[0];
return(long.Parse(dt.Rows[0]["Count"].ToString()));
}
/// <summary>
/// Adds new org and Admin User object
/// </summary>
/// <param name="dto"></param>
public int AddOrganization(UserOrganizationDto userOrganizationDto)
{
OrganizationDto organizationDto = userOrganizationDto.Organization;
UserDTO userDto = userOrganizationDto.User;
if (userOrganizationDto.User == null)
{
throw new Exception("An organization cannot be added with zero users");
}
int organizationID = -1;
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "add_organization";
NpgsqlParameter parameter = new NpgsqlParameter("orgname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = organizationDto.Name;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("orgdescription", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = organizationDto.Description;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("Usid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = userDto.UserID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("UserNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.UserName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("FirstNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.FirstName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("LastNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.LastName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("EmailAdd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.Email;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("phonenbr", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.Phone;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("PwdHash", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.PasswordHash;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("IsExistingUser", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userDto.IsExistingUser;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
if (userDto.IsExistingUser)
{
parameter = new NpgsqlParameter("ResetPwd", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userDto.ShouldResetPassword;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
else
{
parameter = new NpgsqlParameter("ResetPwd", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = true;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
parameter = new NpgsqlParameter("RId", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = userOrganizationDto.RoleId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("IsActive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userOrganizationDto.Active;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
organizationID = 1000; //success
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
return(organizationID);
}