/// <summary>
/// Add a new entry to the ZipCodeCity table
/// </summary>
public static ZipCodeCity Add(string city, int stateId, double?latitude, double?longitude)
{
ZipCodeCity result = null;
DateTime purchaseTime = DateTime.Now;
SqlConnection cn = GetConnection();
string cmd = "AddZipCodeCity";
using (SqlCommand sqlCmd = new SqlCommand(cmd, cn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
BuildSqlParameter(sqlCmd, "@ZipCodeCityName", SqlDbType.Text, city);
BuildSqlParameter(sqlCmd, "@ZipCodeCityLatitude", SqlDbType.Float, latitude);
BuildSqlParameter(sqlCmd, "@ZipCodeCityLongitude", SqlDbType.Float, longitude);
BuildSqlParameter(sqlCmd, "@ZipCodeCityZipCodeStateId", SqlDbType.Int, stateId);
BuildSqlParameter(sqlCmd, "@ZipCodeCityId", SqlDbType.Int, ParameterDirection.ReturnValue);
if (sqlCmd.ExecuteNonQuery() > 0)
{
result = new ZipCodeCity(Convert.ToInt32(sqlCmd.Parameters["@ZipCodeCityId"].Value),
city, stateId, latitude, longitude);
}
}
FinishedWithConnection(cn);
return(result);
}
/// <summary>
/// Get an entry from the ZipCodeCity table
/// </summary>
public static ZipCodeCity Get(int id)
{
ZipCodeCity result = null;
SqlConnection cn = GetConnection();
result = Get(cn, id);
FinishedWithConnection(cn);
return(result);
}
private static ZipCodeCity Get(SqlConnection cn, int id)
{
ZipCodeCity result = null;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM ZipCodeCity WHERE ZipCodeCityId=" + id, cn))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result = BuildZipCodeCity(rdr);
}
}
}
return(result);
}
/// <summary>
/// Delete an entry from the ZipCodeCity table
/// </summary>
public static bool Delete(int id)
{
Int32 rowsAffected = 0;
SqlConnection cn = GetConnection();
ZipCodeCity zipCodeCity = Get(cn, id);
if (zipCodeCity != null)
{
using (SqlCommand sqlCmd = cn.CreateCommand())
{
sqlCmd.CommandText = "DELETE FROM ZipCodeCity WHERE ZipCodeCityId=" + id;
rowsAffected = sqlCmd.ExecuteNonQuery();
}
}
FinishedWithConnection(cn);
return(rowsAffected != 0);
}
/// <summary>
/// Get all entries from the ZipCodeCity table for a city
/// </summary>
public static ZipCodeCity GetByName(int zipCodeStateId, string name)
{
ZipCodeCity result = null;
SqlConnection cn = GetConnection();
using (SqlCommand cmd = new SqlCommand("SELECT * FROM ZipCodeCity WHERE ZipCodeCityZipCodeStateId=@ZipCodeCityZipCodeStateId AND ZipCodeCityName LIKE @ZipCodeCityName", cn))
{
BuildSqlParameter(cmd, "@ZipCodeCityZipCodeStateId", SqlDbType.Int, zipCodeStateId);
BuildSqlParameter(cmd, "@ZipCodeCityName", SqlDbType.Text, name);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result = BuildZipCodeCity(rdr);
}
}
}
FinishedWithConnection(cn);
return(result);
}