public ICollection <RegionDTO> Read()
{
List <RegionDTO> regionDTOsToReturn = new List <RegionDTO>();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string realSelectQuery = @"SELECT * FROM [NORTHWND].[dbo].[Customers]";
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
regionDTOsToReturn.Add(new RegionDTO()
{
CustomerID = reader["CustomerID"].ToString(),
CompanyName = reader["CompanyName"].ToString()
});
}
}
}
sqlConnection.Close();
}
return(regionDTOsToReturn);
}
static public List <User> getAllUsers()
{
List <User> users = new List <User>();
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "select * from [User] ";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["userType"].ToString().Equals("student"))
{
users.Add(new Student(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, Convert.ToInt32(dr["classID"])));
}
else if (dr["userType"].ToString().Equals("teacher"))
{
users.Add(new Teacher(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), Convert.ToDateTime(dr["lastSeen"])));
}
else if (dr["userType"].ToString().Equals("staff"))
{
users.Add(new Staff(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, true));
}
}
return(users);
}
static public List <Message> GetMessagesBySenderId(int senderID)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "select * from Message where senderId = " + senderID;
SqlDataReader dataReader = cmd.ExecuteReader();
List <Message> messages = new List <Message>();
while (dataReader.Read())
{
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con;
cmd2.CommandText = "select recipientId from [Recieve] where messageId = " + dataReader["id"].ToString();
SqlDataReader dataReader2 = cmd2.ExecuteReader();
List <int> recieverIDs = new List <int>();
while (dataReader2.Read())
{
recieverIDs.Add(Convert.ToInt32(dataReader.GetValue(0)));
}
Message message = new Message(dataReader["subject"].ToString(),
dataReader["body"].ToString(),
Convert.ToDateTime(dataReader["time"]),
Convert.ToInt32(dataReader["senderID"]),
recieverIDs);
message.messageID = Convert.ToInt32(dataReader["id"]);
messages.Add(message);
}
cmd.Connection.Close();
return(messages);
}
public BankManagerDto Read(string id)
{
BankManagerDto bankManagerDto;
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
SqlParameter idParameter = new SqlParameter("@Id", SqlDbType.VarChar);
idParameter.Value = id;
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "ReadBankManager";
sqlCommand.Parameters.Add(idParameter);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
bankManagerDto = new BankManagerDto()
{
Id = sqlDataReader["Id"].ToString(),
FirstName = sqlDataReader["FirstName"].ToString(),
LastName = sqlDataReader["LastName"].ToString(),
Email = sqlDataReader["Email"].ToString(),
PasswordHash = sqlDataReader["PasswordHash"].ToString(),
CreatedTime = DateTime.Parse(sqlDataReader["CreatedTime"].ToString()),
EntityStatus = (EntityStatusType)Int32.Parse(sqlDataReader["EntityStatus"].ToString())
};
}
sqlConnection.Close();
}
return(bankManagerDto);
}
static public List <MySession> GetSubjectSessions(int subjectId, int classId)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = @"select Session.id, Session.scheduleId, Session.[date], Session.notesLink, Session.videoLink
from Session inner join Schedule
on Session.scheduleId = Schedule.Id
where Schedule.subjectId = " + subjectId + @"
and Schedule.classId = " + classId;
SqlDataReader dataReader = cmd.ExecuteReader();
List <MySession> sessions = new List <MySession>();
while (dataReader.Read())
{
sessions.Add(new MySession(Convert.ToInt32(dataReader["id"]),
Convert.ToDateTime(dataReader.GetDateTime(2)),
dataReader["notesLink"].ToString(),
dataReader["videoLink"].ToString(),
Convert.ToInt32(dataReader["scheduleId"])));
}
cmd.Connection.Close();
return(sessions);
}
public CurrentSessionDto Read(string id)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseSelectQuery = @"SELECT * FROM [AdoBankingSystem].[dbo].[CurrentSessions] WHERE [Id = {0}]";
string realSelectQuery = String.Format(baseSelectQuery, id.ToString());
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
currentSessionDTOToReturn = new CurrentSessionDto()
{
Id = reader["Id"].ToString(),
UserId = reader["UserId"].ToString(),
SignInTime = DateTime.Parse(reader["SignInTime"].ToString()),
LastOperationTime = DateTime.Parse(reader["LastOperationTime"].ToString()),
CreatedTime = DateTime.Parse(reader["CreatedTime"].ToString()),
EntityStatus = (EntityStatusType)Int32.Parse(reader["EntityStatus"].ToString())
};
}
}
sqlConnection.Close();
}
return(currentSessionDTOToReturn);
}
public static User getUserByID(int userID)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "Select * from [User] where id= " + userID.ToString();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
ELearn.User usr = null;
if (dr["userType"].ToString().Equals("student"))
{
usr = new Student(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, Convert.ToInt32(dr["classID"]));
}
else if (dr["userType"].ToString().Equals("teacher"))
{
usr = new Teacher(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now);
}
else if (dr["userType"].ToString().Equals("staff"))
{
usr = new Staff(Convert.ToInt32(dr["id"]), dr["userName"].ToString(), "", dr["name"].ToString(), dr["mail"].ToString(), DateTime.Now, true);
}
dr.Close();
return(usr);
}
return(null);
}
static public Teacher GetTeacherBySubjectAndClass(int subjectId, int classId)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = @"select [User].id, [User].username, [User].password, [User].name, [User].mail, [User].lastSeen
from [User] inner join Teaches
on [User].id = Teaches.teacherId
where Teaches.subjectId = " + subjectId + @"
and Teaches.classId = " + classId;
SqlDataReader dataReader = cmd.ExecuteReader();
Teacher teacher = null;
if (dataReader.Read())
{
teacher = new Teacher(Convert.ToInt32(dataReader.GetValue(0)),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetString(3),
dataReader.GetString(4),
dataReader.GetDateTime(5));
}
cmd.Connection.Close();
return(teacher);
}
public string Create(TransactionDto record)
{
record.Id = Guid.NewGuid().ToString();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string baseQuery = "INSERT INTO [dbo].[Transactions]" +
"([Id],[SenderId],[ReceiverId],[TransactionType],[TransactionAmount],[TransactionTime], [CreatedTime], [EntityStatus])" +
"VALUES ('{0}','{1}','{2}','{3}',{4}, '{5}', '{6}', {7})";
string realQuery = String.Format
(baseQuery, record.Id, record.SenderId, record.ReceiverId,
(int)record.TransactionType, record.TransactionAmount, record.TransactionTime, record.CreatedTime, (int)record.EntityStatus);
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = realQuery;
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
return(record.Id);
}
public TransactionDto Read(string id)
{
TransactionDto transactionDtoReturn = null;
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string realQuery = "SELECT * FROM dbo.Transactions where Id=" + id.ToString();
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = realQuery;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader != null)
{
transactionDtoReturn = new TransactionDto()
{
SenderId = reader["SenderId"].ToString(),
ReceiverId = reader["ReceiverId"].ToString(),
TransactionType = (TransactionType)Int32.Parse(reader["TransactionType"].ToString()),
TransactionAmount = Decimal.Parse(reader["TransactionAmount"].ToString()),
TransactionTime = DateTime.Parse(reader["TransactionTime"].ToString()),
}
}
;
}
}
sqlConnection.Close();
return(transactionDtoReturn);
}
public string GetCurrentSessionIdByUserId(string userId)
{
string idToReturn = null;
using (SqlConnection sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string baseQuery = "SELECT * FROM dbo.CurrentSessions WHERE UserId = '{0}'";
string realQuery = String.Format(baseQuery, userId);
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = realQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
idToReturn = reader["Id"].ToString();
}
sqlConnection.Close();
}
}
return(idToReturn);
}
public void Update(SupplierDTO t)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseInsertQuery = @"UPDATE [NORTHWNDSDP-162].[dbo].[Suppliers] " +
"SET CompanyName='{1}', ContactName='{2}', ContactTitle='{3}', " +
"Address='{4}', City='{5}', Region='{6}', " +
"PostalCode='{7}', Country='{8}', Phone='{9}', Fax='{10}', HomePage='{11}') " +
"WHERE CustomerID ='{0}'";
string realInsertQuery = String.Format(baseInsertQuery,
t.SupplierId,
t.CompanyName,
t.ContactName,
t.ContactTitle,
t.Address,
t.City,
t.Region,
t.PostalCode,
t.Country,
t.Phone,
t.Fax,
t.HomePage);
sqlCommand.CommandText = realInsertQuery;
sqlCommand.CommandType = CommandType.Text;
int result = sqlCommand.ExecuteNonQuery();
Console.WriteLine(result);
}
sqlConnection.Close();
}
}
static public Student GetUserById(int studentId)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "select * from [User] where id = " + studentId;
SqlDataReader dataReader = cmd.ExecuteReader();
Student student = null;
if (dataReader.Read())
{
if (!dataReader.GetValue(6).ToString().Equals("student"))
{
throw new Exception("the user with the specified id is not a student");
}
student = new Student(Convert.ToInt32(dataReader.GetValue(0)),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetString(3),
dataReader.GetString(4),
dataReader.GetDateTime(5),
Convert.ToInt32(dataReader.GetValue(8)),
dataReader.GetBoolean(9));
}
cmd.Connection.Close();
return(student);
}
public string Create(CurrentSessionDto record)
{
record.Id = Guid.NewGuid().ToString();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string baseQuery = "INSERT INTO [dbo].[CurrentSessions] " +
"([Id], [UserId], [SignInTime], [LastOperationTime], [CreatedTime], [EntityStatus])" +
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', {5})";
string realQuery = String.Format
(baseQuery, record.Id, record.UserId, record.SignInTime,
record.LastOperationTime, record.CreatedTime, (int)record.EntityStatus);
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = realQuery;
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
return(record.Id);
}
public BankClientDto Read(string id)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseSelectQuery = @"SELECT * FROM [AdoBankingSystem].[dbo].[BankClient] WHERE [Id = {0}]";
string realSelectQuery = String.Format(baseSelectQuery, id.ToString());
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
bankClientDTOToReturn = new BankClientDto()
{
Id = reader["Id"].ToString(),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
CreatedTime = DateTime.Parse(reader["CreatedTime"].ToString()),
EntityStatus = (EntityStatusType)Int32.Parse(reader["EntityStatus"].ToString())
};
}
}
sqlConnection.Close();
}
return(bankClientDTOToReturn);
}
public ICollection <CustomerDTO> Read()
{
List <CustomerDTO> regionDTOsToReturn = new List <CustomerDTO>();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string realSelectQuery = @"SELECT * FROM [NORTHWND].[dbo].[Region]";
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
regionDTOsToReturn.Add(new CustomerDTO()
{
RegionId = Int32.Parse(reader["RegionID"].ToString()),
RegionDescription = reader["RegionDescription"].ToString()
});
}
}
}
sqlConnection.Close();
}
return(regionDTOsToReturn);
}
public ICollection <BankManagerDto> Read()
{
ICollection <BankManagerDto> managersToReturn = new List <BankManagerDto>();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string realQuery = "SELECT * FROM dbo.BankManagers";
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = realQuery;
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
managersToReturn.Add(new BankManagerDto()
{
Id = reader["Id"].ToString(),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
CreatedTime = DateTime.Parse(reader["CreatedTime"].ToString()),
EntityStatus = (EntityStatusType)Int32.Parse(reader["EntityStatus"].ToString())
});
}
}
sqlConnection.Close();
}
return(managersToReturn);
}
public void Create(BankClientDto t)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = "InsertNewBankClient";
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter("Id", SqlDbType.NVarChar);
idParam.Value = t.Id;
SqlParameter emailParam = new SqlParameter("Email", SqlDbType.VarChar);
emailParam.Value = t.Email;
SqlParameter firstNameParam = new SqlParameter("FirstName", SqlDbType.VarChar);
firstNameParam.Value = t.FirstName;
SqlParameter lastNameParam = new SqlParameter("LastName", SqlDbType.VarChar);
lastNameParam.Value = t.LastName;
SqlParameter accountNumberParam = new SqlParameter("AccountNumber", SqlDbType.VarChar);
accountNumberParam.Value = t.AccountNumber;
sqlCommand.Parameters.Add(idParam);
sqlCommand.Parameters.Add(emailParam);
sqlCommand.Parameters.Add(firstNameParam);
sqlCommand.Parameters.Add(lastNameParam);
sqlCommand.Parameters.Add(accountNumberParam);
int result = sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
static public List <Message> GetMessagesByRecieverId(int recieverID)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "select messageID from Recieve where [recipientID] = " + recieverID;
SqlDataReader dataReader = cmd.ExecuteReader();
List <Message> messages = new List <Message>();
while (dataReader.Read())
{
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con;
cmd2.CommandText = "select * from Message where id = " + dataReader["messageID"].ToString();
SqlDataReader dataReader2 = cmd2.ExecuteReader();
if (dataReader2.Read())
{
Message message = new Message(dataReader2["subject"].ToString(),
dataReader2["body"].ToString(),
Convert.ToDateTime(dataReader2["time"]),
Convert.ToInt32(dataReader2["senderID"])
);
message.messageID = Convert.ToInt32(dataReader2["id"]);
messages.Add(message);
}
}
cmd.Connection.Close();
return(messages);
}
static public Teacher GetUserById(int teacherId)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "select * from [User] where id = " + teacherId;
SqlDataReader dataReader = cmd.ExecuteReader();
Teacher teacher = null;
if (dataReader.Read())
{
if (!dataReader["userType"].ToString().Equals("teacher"))
{
throw new Exception("the user with the specified id is not a teacher");
}
teacher = new Teacher(Convert.ToInt32(dataReader["id"]),
dataReader["username"].ToString(),
dataReader["password"].ToString(),
dataReader["name"].ToString(),
dataReader["mail"].ToString(),
Convert.ToDateTime(dataReader["lastSeen"]));
}
cmd.Connection.Close();
return(teacher);
}
public void Remove(string id)
{
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string realInsertQuery = String.Format(@"DELETE FROM [dbo].[BankManagers] WHERE Id = {0}", id.ToString());
sqlCommand.CommandText = realInsertQuery;
sqlCommand.CommandType = CommandType.Text;
int result = sqlCommand.ExecuteNonQuery();
Console.WriteLine(result);
}
sqlConnection.Close();
}
}
public CustomerDTO Read(int id)
{
CustomerDTO regionDTOToReturn = null;
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseSelectQuery = @"SELECT * FROM [NORTHWND].[dbo].[Region] " +
"WHERE [RegionID] = {0}";
string realSelectQuery = String.Format(baseSelectQuery, id.ToString());
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
regionDTOToReturn = new CustomerDTO()
{
RegionId = Int32.Parse(reader["RegionID"].ToString()),
RegionDescription = reader["RegionDescription"].ToString()
};
}
}
sqlConnection.Close();
}
return(regionDTOToReturn);
}
public ICollection <CurrentSessionDto> Read()
{
ICollection <CurrentSessionDto> sessions = new List <CurrentSessionDto>();
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
string realQuery = "SELECT * FROM dbo.CurrentSessions";
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = realQuery;
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
sessions.Add(new CurrentSessionDto()
{
Id = reader["Id"].ToString(),
UserId = reader["UserId"].ToString(),
SignInTime = DateTime.Parse(reader["SignInTime"].ToString()),
LastOperationTime = DateTime.Parse(reader["LastOperationTime"].ToString()),
CreatedTime = DateTime.Parse(reader["CreatedTime"].ToString()),
EntityStatus = (EntityStatusType)Int32.Parse(reader["EntityStatus"].ToString())
});
}
}
sqlConnection.Close();
}
return(sessions);
}
public override List <List <string> > GetSchedule()
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = @"select Schedule.startTime, Schedule.sessionDay, Schedule.classId, Subject.title
from (
select Teaches.subjectId, Teaches.classId
from Teaches where Teaches.teacherId = " + this.userID + @"
) as teacherAssignments
inner join Schedule
on Schedule.classId = teacherAssignments.classId
and Schedule.subjectId = teacherAssignments.subjectId
inner join Subject
on teacherAssignments.subjectId = Subject.id
order by Schedule.startTime asc";
SqlDataReader dataReader = cmd.ExecuteReader();
List <List <string> > schedule = new List <List <string> >(5);
for (int i = 0; i < 5; ++i)
{
schedule.Add(new List <string>(6));
for (int j = 0; j < 6; ++j)
{
schedule[i].Add("");
}
}
Hashtable hashTable1 = new Hashtable();
hashTable1.Add("10:00:00", 0);
hashTable1.Add("11:00:00", 1);
hashTable1.Add("12:00:00", 2);
hashTable1.Add("01:00:00", 3);
hashTable1.Add("02:00:00", 4);
Hashtable hashTable2 = new Hashtable();
hashTable2.Add("saturday", 0);
hashTable2.Add("sunday", 1);
hashTable2.Add("monday", 2);
hashTable2.Add("tuesday", 3);
hashTable2.Add("wednesday", 4);
hashTable2.Add("thursday", 5);
while (dataReader.Read())
{
String startTime = dataReader.GetValue(0).ToString();
String sessionDay = dataReader.GetString(1).ToLower();
schedule[(int)hashTable1[startTime]][(int)hashTable2[sessionDay]] = "Class: " + dataReader.GetValue(2).ToString() +
"Subject: " + dataReader.GetString(3);
}
cmd.Connection.Close();
return(schedule);
}
public void insert()
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "insert into [level] (name) OUTPUT INSERTED.id values ('" + levelName + "')";
levelID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
}
public static void assignTeacher(int classRoomID, int teacherID, int subjectID)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "insert into Teaches values (" + teacherID + "," + subjectID + "," + classRoomID + ")";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public void Insert()
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = DatabaseConnectionFactory.GetConnection();
cmd.Connection = con;
cmd.CommandText = "insert into Class (levelID) OUTPUT INSERTED.id values (" + levelID + ")";
classRoomID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
}
public string Update(BankClientDto t)
{
string res = string.Empty;
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseInsertQuery = @"UPDATE [dbo].[BankClients]
SET [Email] = '{1}'
,[FirstName] = '{2}'
,[LastName] = '{3}'
,[PasswordHash] = '{4}'
,[CreatedTime] = '{5}'
,[EntityStatus] = {6}
,[ApplicationClientType] = {7}
WHERE Id = '{0}'";
string realInsertQuery = String.Format(baseInsertQuery,
t.Id, t.Email, t.FirstName, t.LastName,
t.PasswordHash, t.CreatedTime.ToString()
, (int)t.EntityStatus, (int)t.ApplicationClientType);
sqlCommand.CommandText = realInsertQuery;
sqlCommand.CommandType = CommandType.Text;
res = sqlCommand.ExecuteNonQuery().ToString();
}
sqlConnection.Close();
}
return(res);
}
public void TestingDatabaseConnectionFactory_ShouldPass()
{
var databaseConfiguration = new DatabaseConfiguration {
ConnectionString = "Server=myServerAddress;"
};
var databaseConnectionFactory = new DatabaseConnectionFactory(databaseConfiguration);
var connection = databaseConnectionFactory.GetConnection();
Assert.NotNull(connection);
Assert.Equal("Server=myServerAddress;", connection.ConnectionString);
}
public string Update(CurrentSessionDto t)
{
string result = string.Empty;
using (sqlConnection = DatabaseConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseInsertQuery = @"UPDATE [dbo].[CurrentSessions] SET" +
",[UserId] = '{0}'" +
",[SignInTime] = '{1}'" +
",[LastOperationTime] = '{2}'" +
",[CreatedTime] = '{3}'" +
",[EntityStatus] = {4} " +
"WHERE Id = {5}";
string realInsertQuery = String.Format(baseInsertQuery,
t.UserId.ToString(),
t.SignInTime.ToString(),
t.LastOperationTime.ToString()
, t.CreatedTime.ToString()
, t.EntityStatus.ToString(), t.Id);
sqlCommand.CommandText = realInsertQuery;
sqlCommand.CommandType = CommandType.Text;
result = sqlCommand.ExecuteNonQuery().ToString();
}
sqlConnection.Close();
}
return(result);
}