Table Script
USE [TrainingManagement]
GO
CREATE TABLE [dbo].[Attendance](
[AttendanceId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[FacultyId] [int] NULL,
[Date] [date] NULL,
[HoursTaken] [int] NULL,
[CoveredModules] [varchar](200) NULL,
[Remarks] [varchar](200) NULL,
CONSTRAINT [PK_BatchSchedule] PRIMARY KEY CLUSTERED
(
[AttendanceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Batch] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Batch](
[BatchId] [int] IDENTITY(1,1) NOT NULL,
[CourseId] [int] NULL,
[BatchName] [varchar](40) NULL,
[StartDate] [date] NULL,
[TentativeEndDate] [date] NULL,
[BatchTiming] [varchar](100) NULL,
[Hours] [int] NULL,
[HoursTaken] [int] NULL,
[Fees] [int] NULL,
[FeesPaid] [int] NULL,
[Status] [varchar](15) NULL,
[Remarks] [varchar](300) NULL,
CONSTRAINT [PK__Batch__5D55CE58980177C0] PRIMARY KEY CLUSTERED
(
[BatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Course] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course](
[CourseId] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [varchar](40) NULL,
[DurationInHours] [int] NULL,
[Fees] [int] NULL,
[Remarks] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[CourseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Faculty] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Faculty](
[FacultyId] [int] IDENTITY(1,1) NOT NULL,
[FacultyName] [varchar](40) NULL,
[Specialization] [varchar](40) NULL,
[Phone] [varchar](15) NULL,
[Email] [varchar](40) NULL,
PRIMARY KEY CLUSTERED
(
[FacultyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Fees] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Fees](
[FeesId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[Date] [date] NULL,
[Amout] [numeric](8, 2) NULL,
[Description] [varchar](200) NULL,
CONSTRAINT [PK_Fees] PRIMARY KEY CLUSTERED
(
[FeesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Student] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[StudentId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[StudentName] [varchar](50) NULL,
[YearsOfExperience] [int] NULL,
[TechnologiesKnown] [varchar](100) NULL,
[Location] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Email] [varchar](200) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Attendance] ON
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (1, 1, 2, NULL, 2, N'JQuery Ajax', NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (2, 1, 1, NULL, 5, N'Angular Service', N'Given enough assignments for two weeks.')
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (3, 1, 1, NULL, 5, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (4, 2, 1, NULL, 3, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (5, 2, 1, NULL, 3, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (6, 2, 1, CAST(0xB03C0B00 AS Date), 5, N'Entity Framework', N'Entity Framework Database First')
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (7, 5, 1, NULL, 5, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Attendance] OFF
SET IDENTITY_INSERT [dbo].[Batch] ON
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (1, 2, N'Gemini Softwares ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (2, 2, N'.Net Morning', CAST(0x843C0B00 AS Date), CAST(0x46BD0B00 AS Date), N'Mornings 6.30 - 8.30 am and Sundays', 60, 38, 15000, 4, N'Ongoing', N'Slow batch')
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (3, 3, N'State Syllabus', NULL, NULL, NULL, 60, NULL, 6000, NULL, NULL, NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (4, 2, N'Technobay ', CAST(0x6C3C0B00 AS Date), NULL, N'Sundays', 60, 30, 15000, 4000, N'Ongoing', NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (5, 1, N'Palnar Transmedia', NULL, NULL, NULL, 60, 0, 70000, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Batch] OFF
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (1, N'Asp .Net MVC', 60, 10000, N'Asp .Net MVC 5')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (2, N'Asp .Net MVC and Angular', 100, 15000, N'Asp .Net MVC 5, Angular and TypeScript')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (3, N'State Plus 2', 60, 60000, N'State Syllabus Plus 2')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (5, N'Asp .Net Core', 60, 10000, N'If the candidates already know asp .Net MVC, then only 40 hours is required')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (6, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Course] OFF
SET IDENTITY_INSERT [dbo].[Faculty] ON
INSERT [dbo].[Faculty] ([FacultyId], [FacultyName], [Specialization], [Phone], [Email]) VALUES (1, N'Shalvin', N'MVC, Angular, TypeScript, C#', N'974580051w', N'shalvin@gmail.com')
INSERT [dbo].[Faculty] ([FacultyId], [FacultyName], [Specialization], [Phone], [Email]) VALUES (2, N'Mahin', N'MVC, Angular, TypeScript, C#, JQuery', N'78787878787', N'mahinna@gmail.com')
SET IDENTITY_INSERT [dbo].[Faculty] OFF
SET IDENTITY_INSERT [dbo].[Fees] ON
INSERT [dbo].[Fees] ([FeesId], [BatchId], [Date], [Amout], [Description]) VALUES (1, 1, CAST(0xDD3C0B00 AS Date), CAST(20000.00 AS Numeric(8, 2)), N'Fees paid')
SET IDENTITY_INSERT [dbo].[Fees] OFF
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([StudentId], [BatchId], [StudentName], [YearsOfExperience], [TechnologiesKnown], [Location], [Phone], [Email]) VALUES (1, 2, N'arun', 3, N'asp.net,sql,html', N'alappuzha', N'8943781437', NULL)
SET IDENTITY_INSERT [dbo].[Student] OFF
ALTER TABLE [dbo].[Attendance] WITH CHECK ADD CONSTRAINT [FK_BatchSchedule_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_BatchSchedule_Batch]
GO
ALTER TABLE [dbo].[Attendance] WITH CHECK ADD CONSTRAINT [FK_BatchSchedule_Faculty] FOREIGN KEY([FacultyId])
REFERENCES [dbo].[Faculty] ([FacultyId])
GO
ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_BatchSchedule_Faculty]
GO
ALTER TABLE [dbo].[Batch] WITH CHECK ADD CONSTRAINT [FK_Batch_Course] FOREIGN KEY([CourseId])
REFERENCES [dbo].[Course] ([CourseId])
GO
ALTER TABLE [dbo].[Batch] CHECK CONSTRAINT [FK_Batch_Course]
GO
ALTER TABLE [dbo].[Fees] WITH CHECK ADD CONSTRAINT [FK_Fees_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Fees] CHECK CONSTRAINT [FK_Fees_Batch]
GO
ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Batch]
GO
DbContext
namespace TrainingManagementExistingDBCodeFirstShalvin.Models
{
using System.Data.Entity;
public partial class TrainingManagementContext : DbContext
{
public Model1()
: base("name=Model1")
{
}
public virtual DbSet<Attendance> Attendances { get; set; }
public virtual DbSet<Batch> Batches { get; set; }
public virtual DbSet<Course> Courses { get; set; }
public virtual DbSet<Faculty> Faculties { get; set; }
public virtual DbSet<Fee> Fees { get; set; }
public virtual DbSet<Student> Students { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Attendance>()
.Property(e => e.CoveredModules)
.IsUnicode(false);
modelBuilder.Entity<Attendance>()
.Property(e => e.Remarks)
.IsUnicode(false);
modelBuilder.Entity<Batch>()
.Property(e => e.BatchName)
.IsUnicode(false);
modelBuilder.Entity<Batch>()
.Property(e => e.BatchTiming)
.IsUnicode(false);
modelBuilder.Entity<Batch>()
.Property(e => e.Status)
.IsUnicode(false);
modelBuilder.Entity<Batch>()
.Property(e => e.Remarks)
.IsUnicode(false);
modelBuilder.Entity<Course>()
.Property(e => e.CourseName)
.IsUnicode(false);
modelBuilder.Entity<Course>()
.Property(e => e.Remarks)
.IsUnicode(false);
modelBuilder.Entity<Faculty>()
.Property(e => e.FacultyName)
.IsUnicode(false);
modelBuilder.Entity<Faculty>()
.Property(e => e.Specialization)
.IsUnicode(false);
modelBuilder.Entity<Faculty>()
.Property(e => e.Phone)
.IsUnicode(false);
modelBuilder.Entity<Faculty>()
.Property(e => e.Email)
.IsUnicode(false);
modelBuilder.Entity<Fee>()
.Property(e => e.Amout)
.HasPrecision(8, 2);
modelBuilder.Entity<Fee>()
.Property(e => e.Description)
.IsUnicode(false);
modelBuilder.Entity<Student>()
.Property(e => e.StudentName)
.IsUnicode(false);
modelBuilder.Entity<Student>()
.Property(e => e.TechnologiesKnown)
.IsUnicode(false);
modelBuilder.Entity<Student>()
.Property(e => e.Location)
.IsUnicode(false);
modelBuilder.Entity<Student>()
.Property(e => e.Phone)
.IsUnicode(false);
modelBuilder.Entity<Student>()
.Property(e => e.Email)
.IsUnicode(false);
}
}
}
Course Model Class
namespace TrainingManagementExistingDBCodeFirstShalvin.Models
{
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
[Table("Course")]
public partial class Course
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Course()
{
Batches = new HashSet<Batch>();
}
public int CourseId { get; set; }
[StringLength(40, ErrorMessage ="Course name should not exceed 40 characters")]
[Required(ErrorMessage = "Course is required")]
[Display(Name = "Course")]
public string CourseName { get; set; }
[Display(Name ="Duration")]
[RegularExpression("([0-9]+)", ErrorMessage ="Hours should be numeric")]
public int? DurationInHours { get; set; }
public int? Fees { get; set; }
[StringLength(200)]
public string Remarks { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Batch> Batches { get; set; }
}
}
Batcch Model Class
namespace TrainingManagementExistingDBCodeFirstShalvin.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
[Table("Batch")]
public partial class Batch
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Batch()
{
Attendances = new HashSet<Attendance>();
Fees1 = new HashSet<Fee>();
Students = new HashSet<Student>();
}
public int BatchId { get; set; }
public int? CourseId { get; set; }
[StringLength(40)]
public string BatchName { get; set; }
[Column(TypeName = "date")]
public DateTime? StartDate { get; set; }
[Column(TypeName = "date")]
public DateTime? TentativeEndDate { get; set; }
[StringLength(100)]
public string BatchTiming { get; set; }
public int? Hours { get; set; }
public int? HoursTaken { get; set; }
public int? Fees { get; set; }
public int? FeesPaid { get; set; }
[StringLength(15)]
public string Status { get; set; }
[StringLength(300)]
public string Remarks { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Attendance> Attendances { get; set; }
public virtual Course Course { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Fee> Fees1 { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Student> Students { get; set; }
}
}
Attendance Model Class
namespace TrainingManagementExistingDBCodeFirstShalvin.Models
{
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
[Table("Attendance")]
public partial class Attendance
{
public int AttendanceId { get; set; }
public int? BatchId { get; set; }
public int? FacultyId { get; set; }
[Column(TypeName = "date")]
public DateTime? Date { get; set; }
public int? HoursTaken { get; set; }
[StringLength(200)]
public string CoveredModules { get; set; }
[StringLength(200)]
public string Remarks { get; set; }
public virtual Batch Batch { get; set; }
public virtual Faculty Faculty { get; set; }
}
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "AttendanceId,BatchId,FacultyId,Date,HoursTaken,CoveredModules,Remarks")] Attendance attendance)
{
if (ModelState.IsValid)
{
db.Attendances.Add(attendance);
int intBatchId = Convert.ToInt32(Request.Form["BatchId"]);
var Batch = (db.Batches.Where(b => b.BatchId == intBatchId)).FirstOrDefault();
int intBatchHoursTaken = (int)Batch.HoursTaken;
int intHoursTaken = Convert.ToInt32(Request.Form["HoursTaken"]);
int intUpdatedHours = intBatchHoursTaken + intHoursTaken;
Batch.HoursTaken = intUpdatedHours;
db.SaveChanges();
return RedirectToAction("Index");
}
