public static void add(string name, string type, string description)
{
client.exec(
GetConnection(),
$"INSERT INTO device_info (name, type, description) VALUES (\"{name}\",\"{type}\",\"{description}\");"
);
}
static void preInit(MySqlConnection connection, MySqlClient client)
{
client.exec( /// device info
connection,
"CREATE table IF NOT EXISTS device_info (" +
"deviceId INT NOT NULL AUTO_INCREMENT, " +
"name VARCHAR(50) NOT NULL, " +
"type VARCHAR(25) NOT NULL, " +
"description TEXT, " +
"isDeleted boolean NOT NULL DEFAULT false, " +
"PRIMARY KEY(deviceId)" +
")"
);
connection.Close();
connection.Open();
client.exec( /// user info
connection,
"CREATE table IF NOT EXISTS user (" +
"userId INT NOT NULL AUTO_INCREMENT, " +
"firstName VARCHAR(20) NOT NULL, " +
"lastName VARCHAR(20) NOT NULL, " +
"studentId INT(6) NOT NULL, " +
"class VARCHAR(10), " +
"PRIMARY KEY(userId)" +
")"
);
connection.Close();
connection.Open();
client.exec( /// lendings
connection,
"CREATE table IF NOT EXISTS lendings (" +
"lendingId INT NOT NULL AUTO_INCREMENT, " +
"deviceId INT NOT NULL, " +
"last_updated DATETIME NOT NULL, " +
"userId INT NOT NULL, " +
"isActive boolean NOT NULL, " +
"PRIMARY KEY(lendingId), " +
"FOREIGN KEY(deviceId) REFERENCES device_info(deviceId), " +
"FOREIGN KEY(userId) REFERENCES user(userId)" +
")"
);
connection.Close();
connection.Open();
client.exec( /// returns
connection,
"CREATE table IF NOT EXISTS returns (" +
"ID INT NOT NULL AUTO_INCREMENT, " +
"deviceId INT NOT NULL, " +
"userId INT NOT NULL, " +
"lendingId INT NOT NULL, " +
"description VARCHAR(200), " +
"PRIMARY KEY(ID), " +
"FOREIGN KEY(deviceId) REFERENCES device_info(deviceId), " +
"FOREIGN KEY(userId) REFERENCES user(userId), " +
"FOREIGN KEY(lendingId) REFERENCES lendings(lendingId)" +
")"
);
connection.Close();
}
/// <summary>
/// add
/// delete
/// updateAll
/// update deviceId
/// update userId
///
/// schema:
/// lendingId INT NOT NULL AUTO_INCREMENT
/// deviceId INT NOT NULL
/// last_updated DATETIME NOT NULL
/// userId INT NOT NULL
/// isActive BOOLEAN NOT NULL
/// PRIMARY KEY(lendingId)
/// FOREIGN KEY(deviceId) REFERENCES device_info(deviceId)
/// FOREIGN KEY(userId) REFERENCES user(userId)
/// </summary>
public static void add(int deviceId, int userId)
{
client.exec(
GetConnection(),
$"INSERT INTO lendings (deviceId, last_updated, userId, isActive) VALUES (\"{deviceId}\", NOW(), \"{userId}\", \"{1}\");"
);
}
/// <summary>
/// add
/// delete
/// updateAll
/// update deviceId
/// update userId
/// update ledingId
/// update description
///
/// schema:
/// ID INT NOT NULL AUTO_INCREMENT
/// deviceId INT NOT NULL
/// userId INT NOT NULL
/// lendingId INT NOT NULL
/// description VARCHAR(200)
/// PRIMARY KEY(ID)
/// FOREIGN KEY(deviceId) REFERENCES device_info(deviceId)
/// FOREIGN KEY(userId) REFERENCES user(userId)
/// FOREIGN KEY(lendingId) REFERENCES lendings(lendingId)
/// </summary>
public static void add(int deviceId, int userId, int lendingId, string description = null)
{
client.exec(
GetConnection(),
$"INSERT INTO returns " +
$"(deviceId, userId, lendingId{(description != null ? ", description" : "")}) " +
$"VALUES " +
$"(\"{deviceId}\", \"{userId}\", \"{lendingId}\"{(description != null ? $", \"{description}\"" : "")});"
);
}
/// <summary>
/// add
/// delete
/// updateAll
/// setFirstName
/// setLastName
/// setClass
/// setStudentId
///
/// schema:
/// userId INT NOT NULL AUTO_INCREMENT,
/// firstName VARCHAR(20) NOT NULL,
/// lastName VARCHAR(20) NOT NULL,
/// studentId INT (6) NOT NULL,
/// class VARCHAR(10),
/// </summary>
public static int add(string firstName, string lastName, int studentId, string Class)
{
Console.WriteLine("test");
client.exec(
GetConnection(),
$"INSERT INTO user (firstName, lastName, studentId, class) VALUES (\"{firstName}\", \"{lastName}\", \"{studentId}\", \"{Class}\");"
);
MySqlDataReader reader = client.exec(
GetConnection(),
$"SELECT * FROM user WHERE firstname = \"{firstName}\" AND lastName = \"{lastName}\" AND studentId = \"{studentId}\" AND class = \"{Class}\";"
);
while (reader.Read())
{
return(reader.GetInt16(0));
}
return(-1);
}