public static IdType Insert(DirectoriesData data) { // Create and execute the command string sql = "Insert Into " + TABLE + "(" + "Name," + "Path," + "IsPublic," + "OrgGroupID," ; sql = sql.Substring(0, sql.Length - 1) + ") values(" + "@Name," + "@Path," + "@IsPublic," + "@OrgGroupID," ; sql = sql.Substring(0, sql.Length - 1) + ");select Scope_Identity() Id"; SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT); //Create the parameters and append them to the command object cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Name", DataRowVersion.Proposed, data.Name.DBValue)); cmd.Parameters.Add(new SqlParameter("@Path", SqlDbType.VarChar, 150, ParameterDirection.Input, false, 0, 0, "Path", DataRowVersion.Proposed, data.Path.DBValue)); cmd.Parameters.Add(new SqlParameter("@IsPublic", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsPublic", DataRowVersion.Proposed, !data.IsPublic.IsValid ? data.IsPublic.DBValue : data.IsPublic.DBValue.Equals("Y") ? 1 : 0)); cmd.Parameters.Add(new SqlParameter("@OrgGroupID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "OrgGroupID", DataRowVersion.Proposed, data.OrgGroupID.DBValue)); // Execute the query SqlDataReader returnValue = cmd.ExecuteReader(); returnValue.Read(); int returnId = (int)(returnValue.GetDecimal(0)); returnValue.Close(); // Set the output paramter value(s) return(new IdType(returnId)); }
public static void Update(DirectoriesData data) { // Create and execute the command DirectoriesData oldData = Load(data.Id); string sql = "Update " + TABLE + " set "; if (!oldData.Name.Equals(data.Name)) { sql = sql + "Name=@Name,"; } if (!oldData.Path.Equals(data.Path)) { sql = sql + "Path=@Path,"; } if (!oldData.IsPublic.Equals(data.IsPublic)) { sql = sql + "IsPublic=@IsPublic,"; } if (!oldData.OrgGroupID.Equals(data.OrgGroupID)) { sql = sql + "OrgGroupID=@OrgGroupID,"; } WhereClause w = new WhereClause(); w.And("Id", data.Id.DBValue); sql = sql.Substring(0, sql.Length - 1) + w.FormatSql(); SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT); //Create the parameters and append them to the command object if (!oldData.Id.Equals(data.Id)) { cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "Id", DataRowVersion.Proposed, data.Id.DBValue)); } if (!oldData.Name.Equals(data.Name)) { cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Name", DataRowVersion.Proposed, data.Name.DBValue)); } if (!oldData.Path.Equals(data.Path)) { cmd.Parameters.Add(new SqlParameter("@Path", SqlDbType.VarChar, 150, ParameterDirection.Input, false, 0, 0, "Path", DataRowVersion.Proposed, data.Path.DBValue)); } if (!oldData.IsPublic.Equals(data.IsPublic)) { cmd.Parameters.Add(new SqlParameter("@IsPublic", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsPublic", DataRowVersion.Proposed, !data.IsPublic.IsValid ? data.IsPublic.DBValue : data.IsPublic.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.OrgGroupID.Equals(data.OrgGroupID)) { cmd.Parameters.Add(new SqlParameter("@OrgGroupID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "OrgGroupID", DataRowVersion.Proposed, data.OrgGroupID.DBValue)); } // Execute the query if (cmd.Parameters.Count > 0) { cmd.ExecuteNonQuery(); } }
private static DirectoriesData GetDataObjectFromReader(SqlDataReader dataReader) { DirectoriesData data = new DirectoriesData(); if (dataReader.IsDBNull(dataReader.GetOrdinal("Id"))) { data.Id = IdType.UNSET; } else { data.Id = new IdType(dataReader.GetInt32(dataReader.GetOrdinal("Id"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Name"))) { data.Name = StringType.UNSET; } else { data.Name = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Name"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Path"))) { data.Path = StringType.UNSET; } else { data.Path = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Path"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("IsPublic"))) { data.IsPublic = BooleanType.UNSET; } else { data.IsPublic = BooleanType.GetInstance(dataReader.GetBoolean(dataReader.GetOrdinal("IsPublic"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("OrgGroupID"))) { data.OrgGroupID = IntegerType.UNSET; } else { data.OrgGroupID = new IntegerType(dataReader.GetInt32(dataReader.GetOrdinal("OrgGroupID"))); } return(data); }
public static DirectoriesData Load(IdType id) { WhereClause w = new WhereClause(); w.And("Id", id.DBValue); SqlDataReader dataReader = GetListReader(DatabaseEnum.INTRANET, TABLE, w, null, true); if (!dataReader.Read()) { dataReader.Close(); throw new FinderException("Load found no rows for Directories."); } DirectoriesData data = GetDataObjectFromReader(dataReader); dataReader.Close(); return(data); }