public List <MarksRecords> SearchByID(string ID) { List <MarksRecords> MarksList = new List <MarksRecords>(); int i, length; SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MarksManagementDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "select * from MarksRecords where UserID = @uid"; cmd.Parameters.AddWithValue("@uid", ID); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); con.Open(); da.Fill(ds, "MarksRecords"); DataTable dt = ds.Tables["MarksRecords"]; length = ds.Tables["MarksRecords"].Rows.Count; if (length == 0) { return(null); } for (i = 0; i < length; i++) { MarksRecords m = new MarksRecords(); m.UserID = ds.Tables["MarksRecords"].Rows[i][0].ToString(); m.Semester = (int)ds.Tables["MarksRecords"].Rows[i][1]; m.Subject1 = (int)ds.Tables["MarksRecords"].Rows[i][2]; m.Subject2 = (int)ds.Tables["MarksRecords"].Rows[i][3]; m.Subject3 = (int)ds.Tables["MarksRecords"].Rows[i][4]; MarksList.Add(m); } return(MarksList); }
public string AddMarks(MarksRecords marks) { SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MarksManagementDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "select * from MarksRecords where UserID = @uid1 and Semester = @sem1"; cmd.Parameters.AddWithValue("@uid1", marks.UserID); cmd.Parameters.AddWithValue("@sem1", marks.Semester); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Close(); con.Close(); return(null); } else { reader.Close(); cmd.CommandText = "insert into MarksRecords(UserID,Semester,Subject1,Subject2,Subject3) values (@uid,@sem,@s1,@s2,@s3)"; cmd.Parameters.AddWithValue("@uid", marks.UserID); cmd.Parameters.AddWithValue("@sem", marks.Semester); cmd.Parameters.AddWithValue("@s1", marks.Subject1); cmd.Parameters.AddWithValue("@s2", marks.Subject2); cmd.Parameters.AddWithValue("@s3", marks.Subject3); float per = (float)(marks.Subject1 + marks.Subject2 + marks.Subject3) / 3; string result = null; if (per < 35) { result = "FAIL"; } else { result = "PASS"; } cmd.ExecuteNonQuery(); con.Close(); return(result); } }