public int Insert(CrAddress addr)
{
var rc = -1;
const string query = "INSERT INTO dbo.Addresses (Address_Line_1,City,[State/Region],Postal_Code,Domain_ID) " +
"OUTPUT INSERTED.Address_ID " +
"VALUES (@Address,@City,@State,@Zip,@DomainID) ";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 100).Value = addr.AddressLine1;
cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = addr.City;
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = addr.State;
cmd.Parameters.Add("@Zip", SqlDbType.NVarChar).Value = addr.PostalCode;
cmd.Parameters.Add("@DomainID", SqlDbType.NVarChar).Value = 1;
// open connection, execute INSERT, close connection
cn.Open();
try
{
rc = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
// write to log
log.Error(ex.Message);
}
cn.Close();
}
return(rc);
}
public bool Exists(CrAddress addr)
{
var rc = false;
var count = 0;
const string query = "SELECT count(*) FROM dbo.Addresses WHERE address_line_1 = @Address AND City = @City AND [State/Region] = @State AND Postal_Code = @Zip";
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 75).Value = addr.AddressLine1;
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = addr.City;
cmd.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = addr.State;
cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 15).Value = addr.PostalCode;
// open connection, execute INSERT, close connection
cn.Open();
try
{
count = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
// write to log
log.Error(ex.Message);
}
cn.Close();
}
rc = count > 0 ? true : false;
return(rc);
}
public int Insert(CrProject project, bool update = false)
{
if (Exists(project.ProjectName, project.InitiativeId))
{
if (update)
{
Update(project);
}
return(GetProjectId(project.ProjectName));
}
var addrDao = new CrAddressDao();
var addr = new CrAddress
{
AddressLine1 = project.Address1,
City = project.City,
PostalCode = project.Zip,
State = project.State
};
var addressID = addrDao.Insert(addr);
var rc = -1;
const string query = "INSERT INTO dbo.cr_Projects (Project_Name,Project_Status_ID,Location_ID,Project_Type_ID," +
"Organization_ID,Initiative_ID,Minimum_Volunteers,Maximum_Volunteers," +
"Absolute_Maximum_Volunteers,Domain_ID," +
"Check_In_Floor,Check_In_Area,Check_In_Room_Number,Note_To_Volunteers_1," +
"Note_To_Volunteers_2,Project_Parking_Location,Address_ID) " +
"OUTPUT INSERTED.Project_ID " +
"VALUES (@ProjectName,@ProjectStatusID,@LocationID,@ProjectTypeID," +
"@OrganizationID,@InitiativeID,@MinimumVolunteers,@MaximumVolunteers," +
"@AbsoluteMaximumVolunteers,@DomainID," +
"@CheckInFloor,@CheckInArea,@CheckInRoomNumber,@NoteToVolunteers1," +
"@NoteToVolunteers2,@ProjectParkingLocation,@Address_ID) ";
var mp = new MpDao();
using (var cn = new SqlConnection(_connectionString))
using (var cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@ProjectName", SqlDbType.NVarChar, 100).Value = project.ProjectName;
cmd.Parameters.Add("@ProjectStatusID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@LocationID", SqlDbType.Int).Value = mp.GetLocationId(project.LocationName);
cmd.Parameters.Add("@ProjectTypeID", SqlDbType.Int).Value = mp.GetProjectTypeId(project.ProjectTypeName);
cmd.Parameters.Add("@OrganizationID", SqlDbType.Int).Value = mp.GetOrganizationId(project.OrganizationName);
cmd.Parameters.Add("@InitiativeID", SqlDbType.Int).Value = project.InitiativeId;
cmd.Parameters.Add("@MinimumVolunteers", SqlDbType.Int).Value = project.MinVol;
cmd.Parameters.Add("@MaximumVolunteers", SqlDbType.Int).Value = project.MaxVol;
cmd.Parameters.Add("@AbsoluteMaximumVolunteers", SqlDbType.Int).Value = project.AbsoluteMaxVol;
cmd.Parameters.Add("@DomainID", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@CheckInFloor", SqlDbType.NVarChar, 50).Value = project.CheckInFloor;
cmd.Parameters.Add("@CheckInArea", SqlDbType.NVarChar, 50).Value = project.CheckInArea;
cmd.Parameters.Add("@CheckInRoomNumber", SqlDbType.NVarChar, 50).Value = project.CheckInRoomNumber;
cmd.Parameters.Add("@NoteToVolunteers1", SqlDbType.NVarChar, 500).Value = project.Note1;
cmd.Parameters.Add("@NoteToVolunteers2", SqlDbType.NVarChar, 500).Value = project.Note2;
cmd.Parameters.Add("@ProjectParkingLocation", SqlDbType.NVarChar, 500).Value = project.ParkingLocation;
cmd.Parameters.Add("@Address_ID", SqlDbType.Int).Value = addressID;
// open connection, execute INSERT, close connection
cn.Open();
try
{
rc = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
// write to log
log.Error(ex.Message);
}
cn.Close();
}
return(rc);
}