public static IdType Insert(OrgLocationsData data) { // Create and execute the command string sql = "Insert Into " + TABLE + "(" + "Description," + "Address1," + "Address2," + "City," + "State," + "Country," + "PostCode," + "Prefix," + "Image," ; sql = sql.Substring(0, sql.Length - 1) + ") values(" + "@Description," + "@Address1," + "@Address2," + "@City," + "@State," + "@Country," + "@PostCode," + "@Prefix," + "@Image," ; 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("@Description", SqlDbType.VarChar, 100, ParameterDirection.Input, false, 0, 0, "Description", DataRowVersion.Proposed, data.Description.DBValue)); cmd.Parameters.Add(new SqlParameter("@Address1", SqlDbType.VarChar, 200, ParameterDirection.Input, false, 0, 0, "Address1", DataRowVersion.Proposed, data.Address1.DBValue)); cmd.Parameters.Add(new SqlParameter("@Address2", SqlDbType.VarChar, 200, ParameterDirection.Input, false, 0, 0, "Address2", DataRowVersion.Proposed, data.Address2.DBValue)); cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "City", DataRowVersion.Proposed, data.City.DBValue)); cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "State", DataRowVersion.Proposed, data.State.DBValue)); cmd.Parameters.Add(new SqlParameter("@Country", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Country", DataRowVersion.Proposed, data.Country.DBValue)); cmd.Parameters.Add(new SqlParameter("@PostCode", SqlDbType.VarChar, 15, ParameterDirection.Input, false, 0, 0, "PostCode", DataRowVersion.Proposed, data.PostCode.DBValue)); cmd.Parameters.Add(new SqlParameter("@Prefix", SqlDbType.VarChar, 10, ParameterDirection.Input, false, 0, 0, "Prefix", DataRowVersion.Proposed, data.Prefix.DBValue)); cmd.Parameters.Add(new SqlParameter("@Image", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Image", DataRowVersion.Proposed, data.Image.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 OrgLocationsData Load(IdType orgLocationsID) { WhereClause w = new WhereClause(); w.And("OrgLocationsID", orgLocationsID.DBValue); SqlDataReader dataReader = GetListReader(DatabaseEnum.INTRANET, TABLE, w, null, true); if (!dataReader.Read()) { dataReader.Close(); throw new FinderException("Load found no rows for OrgLocations."); } OrgLocationsData data = GetDataObjectFromReader(dataReader); dataReader.Close(); return(data); }
public static void Update(OrgLocationsData data) { // Create and execute the command OrgLocationsData oldData = Load(data.OrgLocationsID); string sql = "Update " + TABLE + " set "; if (!oldData.Description.Equals(data.Description)) { sql = sql + "Description=@Description,"; } if (!oldData.Address1.Equals(data.Address1)) { sql = sql + "Address1=@Address1,"; } if (!oldData.Address2.Equals(data.Address2)) { sql = sql + "Address2=@Address2,"; } if (!oldData.City.Equals(data.City)) { sql = sql + "City=@City,"; } if (!oldData.State.Equals(data.State)) { sql = sql + "State=@State,"; } if (!oldData.Country.Equals(data.Country)) { sql = sql + "Country=@Country,"; } if (!oldData.PostCode.Equals(data.PostCode)) { sql = sql + "PostCode=@PostCode,"; } if (!oldData.Prefix.Equals(data.Prefix)) { sql = sql + "Prefix=@Prefix,"; } if (!oldData.Image.Equals(data.Image)) { sql = sql + "Image=@Image,"; } WhereClause w = new WhereClause(); w.And("OrgLocationsID", data.OrgLocationsID.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.OrgLocationsID.Equals(data.OrgLocationsID)) { cmd.Parameters.Add(new SqlParameter("@OrgLocationsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "OrgLocationsID", DataRowVersion.Proposed, data.OrgLocationsID.DBValue)); } if (!oldData.Description.Equals(data.Description)) { cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 100, ParameterDirection.Input, false, 0, 0, "Description", DataRowVersion.Proposed, data.Description.DBValue)); } if (!oldData.Address1.Equals(data.Address1)) { cmd.Parameters.Add(new SqlParameter("@Address1", SqlDbType.VarChar, 200, ParameterDirection.Input, false, 0, 0, "Address1", DataRowVersion.Proposed, data.Address1.DBValue)); } if (!oldData.Address2.Equals(data.Address2)) { cmd.Parameters.Add(new SqlParameter("@Address2", SqlDbType.VarChar, 200, ParameterDirection.Input, false, 0, 0, "Address2", DataRowVersion.Proposed, data.Address2.DBValue)); } if (!oldData.City.Equals(data.City)) { cmd.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "City", DataRowVersion.Proposed, data.City.DBValue)); } if (!oldData.State.Equals(data.State)) { cmd.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "State", DataRowVersion.Proposed, data.State.DBValue)); } if (!oldData.Country.Equals(data.Country)) { cmd.Parameters.Add(new SqlParameter("@Country", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Country", DataRowVersion.Proposed, data.Country.DBValue)); } if (!oldData.PostCode.Equals(data.PostCode)) { cmd.Parameters.Add(new SqlParameter("@PostCode", SqlDbType.VarChar, 15, ParameterDirection.Input, false, 0, 0, "PostCode", DataRowVersion.Proposed, data.PostCode.DBValue)); } if (!oldData.Prefix.Equals(data.Prefix)) { cmd.Parameters.Add(new SqlParameter("@Prefix", SqlDbType.VarChar, 10, ParameterDirection.Input, false, 0, 0, "Prefix", DataRowVersion.Proposed, data.Prefix.DBValue)); } if (!oldData.Image.Equals(data.Image)) { cmd.Parameters.Add(new SqlParameter("@Image", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Image", DataRowVersion.Proposed, data.Image.DBValue)); } // Execute the query if (cmd.Parameters.Count > 0) { cmd.ExecuteNonQuery(); } }
private static OrgLocationsData GetDataObjectFromReader(SqlDataReader dataReader) { OrgLocationsData data = new OrgLocationsData(); if (dataReader.IsDBNull(dataReader.GetOrdinal("OrgLocationsID"))) { data.OrgLocationsID = IdType.UNSET; } else { data.OrgLocationsID = new IdType(dataReader.GetInt32(dataReader.GetOrdinal("OrgLocationsID"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Description"))) { data.Description = StringType.UNSET; } else { data.Description = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Description"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Address1"))) { data.Address1 = StringType.UNSET; } else { data.Address1 = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Address1"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Address2"))) { data.Address2 = StringType.UNSET; } else { data.Address2 = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Address2"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("City"))) { data.City = StringType.UNSET; } else { data.City = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("City"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("State"))) { data.State = StringType.UNSET; } else { data.State = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("State"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Country"))) { data.Country = StringType.UNSET; } else { data.Country = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Country"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("PostCode"))) { data.PostCode = StringType.UNSET; } else { data.PostCode = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("PostCode"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Prefix"))) { data.Prefix = StringType.UNSET; } else { data.Prefix = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Prefix"))); } if (dataReader.IsDBNull(dataReader.GetOrdinal("Image"))) { data.Image = StringType.UNSET; } else { data.Image = StringType.Parse(dataReader.GetString(dataReader.GetOrdinal("Image"))); } return(data); }