public List <Component> LoanTimeExceeded()
{
List <Component> nlist = GetAllComponents();
List <Component> compList = new List <Component>();
foreach (var comp in nlist)
{
LoanDataUtil loanDatautil = new LoanDataUtil();
LoanInformation lf = loanDatautil.GetLoanInformation(comp);
if (lf.ReturnDate != DateTime.MinValue && lf.LoanDate != DateTime.MinValue)
{
if (lf.ReturnDate < DateTime.Now)
{
compList.Add(comp);
}
}
}
return(compList);
}
// bool CreateComponent(Component cp)
public bool CreateComponent(Component cp)
{
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"INSERT INTO [Component] (ComponentNumber, SerieNr, ComponentName, ComponentInfo, Category, Datasheet, Image, OwnerID, ReservationID)
OUTPUT INSERTED.ComponentID
VALUES (@Data1, @Data2,@Data3,@Data4,@Data5,@Data6,@Data7,@Data8, @Data9)";
//Alternative // ; SELECT SCOPE_IDENTITY()";
object componentInfoParam = DBNull.Value;
object categoryParam = DBNull.Value;
object datasheetParam = DBNull.Value;
object imageParam = DBNull.Value;
object ownerIdParam = DBNull.Value;
object resIdParam = DBNull.Value;
object serieNrParam = DBNull.Value;
using (SqlCommand cmd = new SqlCommand(insertStringParam, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data3";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data4";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data6";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data7";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data8";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data9";
cmd.Parameters["@Data1"].Value = cp.ComponentNumber;
cmd.Parameters["@Data2"].Value = cp.SerieNr ?? serieNrParam;
cmd.Parameters["@Data3"].Value = cp.ComponentName;
// Hvis parameterne er null, så brug DBNULL.Value.
cmd.Parameters["@Data4"].Value = cp.ComponentInfo ?? componentInfoParam;
cmd.Parameters["@Data5"].Value = cp.Category ?? categoryParam;
cmd.Parameters["@Data6"].Value = cp.Datasheet ?? datasheetParam;
cmd.Parameters["@Data7"].Value = cp.Image ?? imageParam;
cmd.Parameters["@Data8"].Value = cp.OwnerID != 0 ? cp.OwnerID : ownerIdParam;
cmd.Parameters["@Data9"].Value = cp.ReservationID != 0 ? cp.ReservationID : resIdParam;
//var id
cp.ComponentID = (int)cmd.ExecuteScalar(); //Returns the identity of the new tuple/record
//hv.HID = (int)cmd.ExecuteNonQuery(); //Does not workReturns row affected and not the identity of the new tuple/record
CurrentComponent = cp;
var loanInfo = new LoanInformation();
var loan = new LoanDataUtil();
loan.CreateLoanInformation(loanInfo, cp);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return true;
}
public bool UpdateLoanInformation(int id, LoanInformation loan)
{
try
{
CurrentLoanInformationHelper(id);
// Open the connection
_conn.Open();
// prepare command string
string updateString =
@"UPDATE LoanInformation
SET Component= @Data2, AdminComment = @Data3, UserComment = @Data4, LoanDate = @Data5, ReturnDate = @Data6
WHERE LoanID = @Data1";
using (SqlCommand cmd = new SqlCommand(updateString, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data3";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data4";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data6";
// Hvis man ikke ændre på en attribut, så sættes den lig med CurrentInformation attribut.
cmd.Parameters["@Data1"].Value = loan.LoanID != 0 ? loan.LoanID : CurrentLoanInformation.LoanID;
cmd.Parameters["@Data2"].Value = loan.Component != 0 ? loan.Component : CurrentLoanInformation.Component;
cmd.Parameters["@Data3"].Value = loan.AdminComment ?? CurrentLoanInformation.AdminComment;
cmd.Parameters["@Data4"].Value = loan.UserComment ?? CurrentLoanInformation.UserComment;
cmd.Parameters["@Data5"].Value = loan.LoanDate ?? CurrentLoanInformation.LoanDate;
cmd.Parameters["@Data6"].Value = loan.ReturnDate ?? CurrentLoanInformation.ReturnDate;
cmd.ExecuteNonQuery();
}
//// 1. Instantiate a new command with command text only
//SqlCommand cmd = new SqlCommand(updateString);
//// 2. Set the Connection property
//cmd.Connection = conn;
//// 3. Call ExecuteNonQuery to send command
//cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return true;
}
public bool CreateLoanInformation(LoanInformation loan, Component c)
{
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"INSERT INTO [LoanInformation] (Component, AdminComment, UserComment, LoanDate, ReturnDate)
OUTPUT INSERTED.LoanID
VALUES (@Data1, @Data2,@Data3,@Data4,@Data5)";
object adminCommentParam = DBNull.Value;
object userCommentParam = DBNull.Value;
object loanDateParam = DBNull.Value;
object returnDateParam = DBNull.Value;
loan.Component = c.ComponentID;
using (SqlCommand cmd = new SqlCommand(insertStringParam, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data3";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data4";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters["@Data1"].Value = loan.Component;
cmd.Parameters["@Data2"].Value = loan.AdminComment ?? adminCommentParam;
cmd.Parameters["@Data3"].Value = loan.UserComment ?? userCommentParam;
cmd.Parameters["@Data4"].Value = loan.LoanDate ?? loanDateParam;
cmd.Parameters["@Data5"].Value = loan.ReturnDate ?? returnDateParam;
//var id
loan.LoanID = (int) cmd.ExecuteScalar(); //Returns the identity of the new tuple/record
//hv.HID = (int)cmd.ExecuteNonQuery(); //Does not workReturns row affected and not the identity of the new tuple/record
CurrentLoanInformation = loan;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return true;
}
private void CurrentLoanInformationHelper(int id)
{
SqlDataReader rdr = null;
try
{
// Open the connection
_conn.Open();
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("SELECT * FROM LoanInformation WHERE (Component ='" + id + "')", _conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// transfer data from result set to local model
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
CurrentLoanInformation = new LoanInformation();
CurrentLoanInformation.LoanID = (int)rdr["LoanID"];
CurrentLoanInformation.Component = (int)rdr["Component"];
if (rdr["AdminComment"] != DBNull.Value)
CurrentLoanInformation.AdminComment = (string)rdr["AdminComment"];
else
{
CurrentLoanInformation.AdminComment = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["UserComment"] != DBNull.Value)
CurrentLoanInformation.UserComment = (string)rdr["UserComment"];
else
{
CurrentLoanInformation.LoanDate = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["LoanDate"] != DBNull.Value)
CurrentLoanInformation.LoanDate = (string)rdr["LoanDate"];
else
{
CurrentLoanInformation.LoanDate = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReturnDate"] != DBNull.Value)
CurrentLoanInformation.ReturnDate = (string)rdr["ReturnDate"];
else
{
CurrentLoanInformation.ReturnDate = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
}
}
catch (Exception e)
{
Console.WriteLine("Error: LoanInformation with " + id + " does not excist");
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
}
public LoanInformation GetLoanInformation(Component cp)
{
SqlDataReader rdr = null;
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"SELECT * FROM LoanInformation WHERE (Component = @Data1)";
SqlCommand cmd = new SqlCommand(insertStringParam, _conn);
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters["@Data1"].Value = cp.ComponentID;
cmd.ExecuteNonQuery();
using (rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var loanInfo = new LoanInformation();
loanInfo.LoanID = (int) rdr["LoanID"];
loanInfo.Component = (int) rdr["Component"];
if (rdr["AdminComment"] != DBNull.Value)
loanInfo.AdminComment = (string)rdr["AdminComments"];
else
{
loanInfo.AdminComment = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["UserComment"] != DBNull.Value)
loanInfo.UserComment = (string)rdr["UserComments"];
else
{
loanInfo.UserComment = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["LoanDate"] != DBNull.Value)
loanInfo.LoanDate = (string)rdr["LoanDate"];
else
{
loanInfo.LoanDate = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
if (rdr["ReturnDate"] != DBNull.Value)
loanInfo.ReturnDate = (string)rdr["ReturnDate"];
else
{
loanInfo.ReturnDate = DBNull.Value.ToString(CultureInfo.InvariantCulture);
}
CurrentLoanInformation = loanInfo;
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return CurrentLoanInformation;
}
// bool CreateComponent(Component cp)
public bool CreateComponent(Component cp)
{
try
{
// Open the connection
_conn.Open();
// prepare command string using paramters in string and returning the given identity
string insertStringParam =
@"INSERT INTO [Component] (ComponentNumber, SerieNr, ComponentName, ComponentInfo, Category, Datasheet, Image, OwnerID, ReservationID)
OUTPUT INSERTED.ComponentID
VALUES (@Data1, @Data2,@Data3,@Data4,@Data5,@Data6,@Data7,@Data8, @Data9)";
//Alternative // ; SELECT SCOPE_IDENTITY()";
object componentInfoParam = DBNull.Value;
object categoryParam = DBNull.Value;
object datasheetParam = DBNull.Value;
object imageParam = DBNull.Value;
object ownerIdParam = DBNull.Value;
object resIdParam = DBNull.Value;
object serieNrParam = DBNull.Value;
using (SqlCommand cmd = new SqlCommand(insertStringParam, _conn))
{
// Get your parameters ready
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data1";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data2";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data3";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data4";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data5";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data6";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data7";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data8";
cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = "@Data9";
cmd.Parameters["@Data1"].Value = cp.ComponentNumber;
cmd.Parameters["@Data2"].Value = cp.SerieNr ?? serieNrParam;
cmd.Parameters["@Data3"].Value = cp.ComponentName;
// Hvis parameterne er null, så brug DBNULL.Value.
cmd.Parameters["@Data4"].Value = cp.ComponentInfo ?? componentInfoParam;
cmd.Parameters["@Data5"].Value = cp.Category ?? categoryParam;
cmd.Parameters["@Data6"].Value = cp.Datasheet ?? datasheetParam;
cmd.Parameters["@Data7"].Value = cp.Image ?? imageParam;
cmd.Parameters["@Data8"].Value = cp.OwnerID != 0 ? cp.OwnerID : ownerIdParam;
cmd.Parameters["@Data9"].Value = cp.ReservationID != 0 ? cp.ReservationID : resIdParam;
//var id
cp.ComponentID = (int)cmd.ExecuteScalar(); //Returns the identity of the new tuple/record
//hv.HID = (int)cmd.ExecuteNonQuery(); //Does not workReturns row affected and not the identity of the new tuple/record
CurrentComponent = cp;
var loanInfo = new LoanInformation();
var loan = new LoanDataUtil();
loan.CreateLoanInformation(loanInfo, cp);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
// Close the connection
if (_conn != null)
{
_conn.Close();
}
}
return(true);
}
