/// <summary>
/// Method to search for any specific Student in the database
/// </summary>
/// <param name="studID">ID of the student to search for</param>
/// <returns>Object of student class holding the details regarding the searched student.</returns>
public static Student FindStudent(int studID)
{
Student result = null;
//SqlConnection object to hold the properties of the connect
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection to the database
//query to be executed
string query = "select * from student_master where StudID=@studID";
SqlCommand comm = new SqlCommand(query, conn);
comm.Parameters.AddWithValue("StudID", studID);
SqlDataReader reader = comm.ExecuteReader(); //executing the query
if (reader.HasRows)
{
//if found any data
reader.Read();
result = new Student(Convert.ToInt32(reader["StudID"]), reader["FullName"].ToString(), reader["FathersName"].ToString(), Convert.ToInt32(reader["RollNo"]), Convert.ToInt32(reader["Age"]), Convert.ToInt32(reader["Stream"]), reader["Address"].ToString(), Convert.ToInt32(reader["State"]));
}
reader.Close(); //closing the reader
conn.Close(); //closing the connection
}
catch (SqlException ex)
{
//if found any exception
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(result); //returning the result
}
/// <summary>
/// Method to get all the states from the database
/// </summary>
/// <returns>Dictionary object holding the StateID and StateName</returns>
public static Dictionary <int, string> GetAllStates()
{
//Dictionary object to hold the result
Dictionary <int, string> states = new Dictionary <int, string>();
//SqlConnection object to hold the properties of the Database connection
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection
//query to be executed
string query = "select * from state_master";
SqlCommand comm = new SqlCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
//adding the fetched data to Dictionary object
states.Add(Convert.ToInt32(reader["StateID"]), reader["StateName"].ToString());
}
reader.Close(); //closing the reader
conn.Close(); //closing the connection
}
catch (SqlException ex)
{
//if any exception found
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(states); //returng the result
}
/// <summary>
/// A method to find Stream Name form database for a specified StreamID
/// </summary>
/// <param name="stateID">StreamID for which StreamName is searched</param>
/// <returns>Stream Name</returns>
public static string GetStreamName(int streamID)
{
//SqlConnection object to hold properties of the connection
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection
//query to be executed
string query = "select StreamName from stream_master where StreamID=@streamID";
SqlCommand comm = new SqlCommand(query, conn);
comm.Parameters.AddWithValue("streamID", streamID);
object value = comm.ExecuteScalar();
if (value != null) //if any data is fetched
{
return(value.ToString()); //returning the fetched data
}
}
catch (SqlException ex)
{
//if any exception is found while opening the connection
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(null);
}
/// <summary>
/// Method to delete student record using roll numbers
/// </summary>
/// <param name="rollNos">roll numbers of the students to delete.</param>
/// <returns>Deletion success or failure</returns>
public static bool DeleteStudent(string rollNos)
{
bool result = false;
//SqlConnection object to hold the properties of the connect
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection to the database
string[] listRollNo = rollNos.Split(',');
//traversing the list of roll numbers received
for (int i = 0; i < listRollNo.Length - 1; i++)
{
string rollNo = listRollNo[i];
//query to be excecuted
string query = "delete from student_master where RollNo=@rollNo";
//SqlCommand object to handle the execution of the query
SqlCommand comm = new SqlCommand(query, conn);
comm.Parameters.AddWithValue("rollNo", rollNo);
int count = comm.ExecuteNonQuery(); //exceuting the query
if (count > 0) //checking whether any data got deleted or not ?
{
result = true;
}
else
{
//if no item got deleted
result = false;
break;
}
}
conn.Close(); //closing the connection with database
}
catch (SqlException ex)
{
//if found any exception
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(result);
}
/// <summary>
/// Method to insert student in databse
/// </summary>
/// <param name="studentsData">List containing records of student</param>
/// <returns>result for the operation</returns>
public static bool insertStudents(List <Student> studentsData)
{
bool result = false;
//SqlConnection object to hold the properties of the connect
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
//opeining the connection
conn.Open();
//traversing the list
foreach (Student stud in studentsData)
{
//query to be executed
string query = "insert into student_master values(@fullname,@fathersName,@rollNo,@age,@stream,@address,@state)";
//SqlCommand object to handle the query execution
SqlCommand comm = new SqlCommand(query, conn);
comm.Parameters.AddWithValue("fullname", stud.fullName);
comm.Parameters.AddWithValue("fathersName", stud.fathersName);
comm.Parameters.AddWithValue("rollNo", stud.rollNo);
comm.Parameters.AddWithValue("age", stud.age);
comm.Parameters.AddWithValue("stream", stud.stream);
comm.Parameters.AddWithValue("address", stud.address);
comm.Parameters.AddWithValue("state", stud.state);
int count = comm.ExecuteNonQuery(); //executing the query
if (count > 0) //checking if any record got added or not?
{
result = true;
}
}
conn.Close(); //closing the connection with the database
}
catch (SqlException ex)
{
//if found any exception
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
//returning the result
return(result);
}
/// <summary>
/// method to update record of any student
/// </summary>
/// <returns>whether record updated or not</returns>
public bool UpdateStudent()
{
bool result = false;
//SqlConnection object to hold the properties of the connect
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection to the database
//query to be excecuted
string query = "update student_master set FullName=@fullname, FathersName=@fathersName,RollNo=@rollNo, Age=@age, Stream=@stream, Address=@address, State=@state where StudID=@studID";
//SqlCommand object to handle the execution of the query
SqlCommand comm = new SqlCommand(query, conn);
comm.Parameters.AddWithValue("fullname", this.fullName);
comm.Parameters.AddWithValue("fathersName", this.fathersName);
comm.Parameters.AddWithValue("rollNo", this.rollNo);
comm.Parameters.AddWithValue("age", this.age);
comm.Parameters.AddWithValue("stream", this.stream);
comm.Parameters.AddWithValue("address", this.address);
comm.Parameters.AddWithValue("state", this.state);
comm.Parameters.AddWithValue("studID", this.StudID);
int count = comm.ExecuteNonQuery(); //exceuting the query
conn.Close(); //closing the connection with database
if (count > 0) //checking whether any data got updated or not ?
{
result = true;
}
}
catch (SqlException ex)
{
//if found any exception
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(result); //returning the result
}
/// <summary>
/// Method to retreave all the records of the students
/// </summary>
/// <returns>list of all students</returns>
public static List <Student> ListAllStudents()
{
//list to store information of the students fetched from the database
List <Student> students = new List <Student>();
//SqlConnection object to hold the properties of the connect
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectDB"].ToString());
try
{
conn.Open(); //opening the connection with the database
//query to be executed
string query = "select * from student_master";
SqlCommand comm = new SqlCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
//reading the data from DataReader after fetching
while (reader.Read())
{
//creating object of Student
Student stud = new Student(Convert.ToInt32(reader["StudID"]), reader["FullName"].ToString(), reader["FathersName"].ToString(), Convert.ToInt32(reader["RollNo"]), Convert.ToInt32(reader["Age"]), Convert.ToInt32(reader["Stream"]), reader["Address"].ToString(), Convert.ToInt32(reader["State"]));
students.Add(stud); //adding the object to List
}
reader.Close(); //closing the reader
conn.Close(); //closing the connection
}
catch (SqlException ex)
{
//if found any exception in opening the connection
Console.WriteLine("{0} EX : {1} ", Messages.ConnToDBFailed, ex.ToString());
UtilityFunctions.LogToEventLog(ex);
}
return(students); //returning the list
}