/// <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> /// 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> /// 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> /// 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); }
public bool ForgotPasswod(string email, string hashedPwd) { PostgreSQLDB db = new PostgreSQLDB(ConnectionString); DataSet ds; try { NpgsqlCommand Command = new NpgsqlCommand(); Command.CommandType = CommandType.StoredProcedure; Command.CommandText = "read_user"; NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = -1; 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 = email; 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); ds = db.ExecuteDataSet(Command); //return ds; } catch (Exception ex) { throw new Exception(ex.Message); } if (ds.Tables[0].Rows.Count > 0) { NpgsqlCommand Command = new NpgsqlCommand(); Command.CommandType = CommandType.StoredProcedure; Command.CommandText = "forgot_password"; NpgsqlParameter parameter = new NpgsqlParameter("emailadd", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = ds.Tables[0].Rows[0]["EMAILADDRESS"]; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("hpassword", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = hashedPwd; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); try { db.ExecuteNonQuery(Command); } catch (Exception ex) { //throw new Exception(ex.Message); return(false); } return(true); } return(false); }
/// <summary> /// Updates the user. /// </summary> /// <param name="dto">The dto.</param> /// <returns></returns> /// <exception cref="System.Exception"></exception> public bool UpdateUser(UserOrganizationDto dto) { PostgreSQLDB db = new PostgreSQLDB(ConnectionString); UserDTO User = dto.User; NpgsqlCommand Command = new NpgsqlCommand(); NpgsqlParameter parameter = null; if (User.UserEditType == UserEditType.EditingUserInfo) { Command.CommandType = CommandType.StoredProcedure; Command.CommandText = "update_user"; parameter = new NpgsqlParameter("fname", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = User.FirstName; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("lname", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = User.LastName; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("emailadd", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = User.Email; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("pnumber", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = User.Phone; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("usid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = User.UserID; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); //addUserCommand.Parameters.Add("UsrId", User.UserID); parameter = new NpgsqlParameter("orid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = dto.Organization.Id; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean); parameter.Value = dto.Active; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = dto.RoleId; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); StringBuilder DSIds = new StringBuilder(); foreach (DatasourceDto item in User.DatasourceList) { DSIds.Append(item.DatasourceId); DSIds.Append(","); } parameter = new NpgsqlParameter("dsids", NpgsqlTypes.NpgsqlDbType.Varchar); if (DSIds.ToString().Contains(",")) { parameter.Value = DSIds.ToString().Substring(0, DSIds.ToString().Length - 1); } else { parameter.Value = ""; } parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); } else { Command.CommandType = CommandType.StoredProcedure; Command.CommandText = "update_password"; parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = User.UserID; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); parameter = new NpgsqlParameter("hpassword", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = User.PasswordHash; parameter.Direction = ParameterDirection.Input; Command.Parameters.Add(parameter); } try { db.ExecuteNonQuery(Command); } catch (Exception ex) { //throw new Exception(ex.Message); return(false); } return(true); }
/// <summary> /// Updates the datasource. /// </summary> /// <param name="dsDto">The ds dto.</param> /// <returns></returns> /// <exception cref="System.Exception"></exception> public bool UpdateDatasource(DTO.DatasourceDto dsDto) { PostgreSQLDB postDb = new PostgreSQLDB(this.ConnectionString); NpgsqlCommand Command = new NpgsqlCommand(); Command.CommandType = System.Data.CommandType.StoredProcedure; Command.CommandText = "update_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("dbtype", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = dsDto.Connection.DatabaseType.ToString(); 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("icatalog", NpgsqlTypes.NpgsqlDbType.Varchar); parameter.Value = cy.Encrypt(dsDto.Connection.DatabaseName); 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("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("dsid", NpgsqlTypes.NpgsqlDbType.Integer); parameter.Value = dsDto.DatasourceId; 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 { postDb.ExecuteNonQuery(Command); } catch (Exception ex) { throw new Exception(ex.Message); //return false; } return(true); }