public ContractExportViewModel(WebApplication1.Models.Contract c) { IntContractNum = c.IntContractNum; ExtContractNum = c.ExtContractNum; Description = c.Description; Signer = c.Signer.UserName; Owner = c.Owner.UserName; Department = c.Department.DepartmentName; SupervisorDepartment = c.SupervisorDepartment.DepartmentName; }
public void GetContracts(string CmdQueryText, ContractAdvancedSearchViewModel AdvancedSearchViewModel) { Nullable <int> T_Id; string U_Id; MyDbContext db = new MyDbContext(); //-- For creating the SqlConnection we’ll use the constructor that takes a (connection)string as input parameter. //--I’ve wrapped the SqlConnection in a using block. This ensures that the connection is actually closed once we’re done with it. //--Make sure you actually open the connection only when needed. using (SqlConnection connection = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\DefaultConnection.mdf;Initial Catalog=DefaultConnection;Integrated Security=True;MultipleActiveResultSets=True")) using (SqlCommand cmd = new SqlCommand(CmdQueryText, connection)) { connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { // Check is the reader has any rows at all before starting to read. if (reader.HasRows) { // Read advances to the next row. while (reader.Read()) { WebApplication1.Models.Contract Contract = new WebApplication1.Models.Contract(); // To avoid unexpected bugs access columns by name. Contract.Id = reader.GetInt32(reader.GetOrdinal("Id")); Contract.IntContractNum = reader.IsDBNull(reader.GetOrdinal("IntContractNum")) ? null : reader.GetString(reader.GetOrdinal("IntContractNum")); Contract.ExtContractNum = reader.IsDBNull(reader.GetOrdinal("ExtContractNum")) ? null : reader.GetString(reader.GetOrdinal("ExtContractNum")); Contract.ContractValue = reader.IsDBNull(reader.GetOrdinal("ContractValue")) ? (Nullable <double>)null : reader.GetDouble(reader.GetOrdinal("ContractValue")); Contract.Tax = reader.IsDBNull(reader.GetOrdinal("Tax")) ? (Nullable <double>)null : reader.GetDouble(reader.GetOrdinal("Tax")); Contract.AnnualValue = reader.IsDBNull(reader.GetOrdinal("AnnualValue")) ? (Nullable <double>)null : reader.GetDouble(reader.GetOrdinal("AnnualValue")); Contract.PaymentBegin = reader.IsDBNull(reader.GetOrdinal("PaymentBegin")) ? (Nullable <DateTime>)null : reader.GetDateTime(reader.GetOrdinal("PaymentBegin")); Contract.PaymentInterval = reader.IsDBNull(reader.GetOrdinal("PaymentInterval")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("PaymentInterval")); Contract.ContractBegin = reader.IsDBNull(reader.GetOrdinal("ContractBegin")) ? (Nullable <DateTime>)null : reader.GetDateTime(reader.GetOrdinal("ContractBegin")); Contract.MinContractDuration = reader.IsDBNull(reader.GetOrdinal("MinContractDuration")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("MinContractDuration")); Contract.ContractEnd = reader.IsDBNull(reader.GetOrdinal("ContractEnd")) ? (Nullable <DateTime>)null : reader.GetDateTime(reader.GetOrdinal("ContractEnd")); Contract.Description = reader.GetString(reader.GetOrdinal("Description")); Contract.AutoExtension = reader.IsDBNull(reader.GetOrdinal("AutoExtension")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("AutoExtension")); Contract.Remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? null : reader.GetString(reader.GetOrdinal("Remarks")); //Contract.CancellationPeriod = reader.IsDBNull(reader.GetOrdinal("CancellationPeriod")) ? (Nullable<int>)null : reader.GetInt32(reader.GetOrdinal("CancellationPeriod")); Contract.PrePayable = reader.IsDBNull(reader.GetOrdinal("PrePayable")) ? (Nullable <bool>)null : reader.GetBoolean(reader.GetOrdinal("PrePayable")); Contract.VarPayable = reader.IsDBNull(reader.GetOrdinal("VarPayable")) ? (Nullable <bool>)null : reader.GetBoolean(reader.GetOrdinal("VarPayable")); Contract.Adaptable = reader.IsDBNull(reader.GetOrdinal("Adaptable")) ? (Nullable <bool>)null : reader.GetBoolean(reader.GetOrdinal("Adaptable")); Contract.IsFrameContract = reader.IsDBNull(reader.GetOrdinal("IsFrameContract")) ? (Nullable <bool>)null : reader.GetBoolean(reader.GetOrdinal("IsFrameContract")); Contract.DepartmentId = reader.IsDBNull(reader.GetOrdinal("DepartmentId")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("DepartmentId")); Contract.SupervisorDepartmentId = reader.IsDBNull(reader.GetOrdinal("SupervisorDepartmentId")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("SupervisorDepartmentId")); //Owner U_Id = reader.IsDBNull(reader.GetOrdinal("OwnerId")) ? null : reader.GetString(reader.GetOrdinal("OwnerId")); Contract.Owner = db.Users.SingleOrDefault(s => s.Id == U_Id); U_Id = null; //Dispatcher U_Id = reader.IsDBNull(reader.GetOrdinal("DispatcherId")) ? null : reader.GetString(reader.GetOrdinal("DispatcherId")); Contract.Dispatcher = db.Users.SingleOrDefault(s => s.Id == U_Id); U_Id = null; //Signer U_Id = reader.IsDBNull(reader.GetOrdinal("SignerId")) ? null : reader.GetString(reader.GetOrdinal("SignerId")); Contract.Signer = db.Users.SingleOrDefault(s => s.Id == U_Id); U_Id = null; //ContractStatus T_Id = (reader.IsDBNull(reader.GetOrdinal("ContractStatus_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("ContractStatus_Id"))); Contract.ContractStatus = db.ContractStatuses.SingleOrDefault(s => s.Id == T_Id); T_Id = null; //ContractKind T_Id = reader.IsDBNull(reader.GetOrdinal("ContractKind_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("ContractKind_Id")); Contract.ContractKind = db.ContractKinds.SingleOrDefault(s => s.Id == T_Id); T_Id = null; // ContractType T_Id = reader.IsDBNull(reader.GetOrdinal("ContractType_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("ContractType_Id")); Contract.ContractType = db.ContractTypes.SingleOrDefault(s => s.Id == T_Id); T_Id = null; //ContractSubType T_Id = reader.IsDBNull(reader.GetOrdinal("ContractSubType_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("ContractSubType_Id")); ContractSubType Subtype = db.ContractSubTypes.SingleOrDefault(s => s.Id == T_Id); Contract.ContractSubType = Subtype; T_Id = null; //CostCenter //T_Id = reader.IsDBNull(reader.GetOrdinal("CostCenter_Id")) ? (Nullable<int>)null : reader.GetInt32(reader.GetOrdinal("CostCenter_Id")); //Contract.CostCenter = db.CostCenters.SingleOrDefault(s => s.Id == T_Id); //T_Id = null; // CostKind T_Id = reader.IsDBNull(reader.GetOrdinal("CostKind_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("CostKind_Id")); Contract.CostKind = db.CostKinds.SingleOrDefault(s => s.Id == T_Id); T_Id = null; //ContractPartner T_Id = reader.IsDBNull(reader.GetOrdinal("ContractPartner_Id")) ? (Nullable <int>)null : reader.GetInt32(reader.GetOrdinal("ContractPartner_Id")); Contract.ContractPartner = db.ContractPartners.SingleOrDefault(s => s.Id == T_Id); T_Id = null; Contracts.Add(Contract); } } } } AdvancedSearchViewModel.Contracts = Contracts; }