public int fjöldi_rada()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
return ds.Tables[0].Rows.Count;
}
public static bool ChangeRow(string laboratoryBookName, Permission permission, DataRow row, List <string> columnList)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var sqlCommand = new MySqlCommand(String.Empty, connection);
connection.Open();
string commandString = null;
foreach (var header in columnList)
{
if (string.IsNullOrEmpty(row[header].ToString()))
{
commandString = $"UPDATE `laboratory_book_{laboratoryBookName}`" +
$" SET `{header}` = NULL WHERE (`sampleID` = '{row["sampleID"]}')";
}
else if (row[header] is DateTime dateTime)
{
var dateTimeString = dateTime.ToString("yyyy-MM-dd");
commandString = $"UPDATE `laboratory_book_{laboratoryBookName}`" +
$" SET `{header}` = '{dateTimeString}' WHERE (`sampleID` = '{row["sampleID"]}')";
}
else
{
commandString = $"UPDATE `laboratory_book_{laboratoryBookName}`" +
$" SET `{header}` = '{row[header]}' WHERE (`sampleID` = '{row["sampleID"]}')";
}
sqlCommand.CommandText = commandString;
try
{
var i = sqlCommand.ExecuteNonQuery();
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Cell change error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
connection.Close();
sqlCommand?.Dispose();
return(false);
}
}
connection.Close();
sqlCommand?.Dispose();
return(true);
}
public async static Task UpdateStatistics(string laboratoryBookName, User user)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = $"UPDATE `statistics`" +
$" SET `user_name` = '{user.GetUserName()}'," +
$" `time_changed` = '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" +
$" WHERE(`db_name` = '{laboratoryBookName}');";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
await connection.OpenAsync();
var result = await sqlCommand.ExecuteNonQueryAsync();
}
finally
{
await connection.CloseAsync();
sqlCommand?.Dispose();
}
}
private static string GetDatabaseString(string CommandText, params SQLParameter[] Parameters)
{
MySqlDataReader Reader = null;
MySqlCommand Command = null;
try
{
HelperFunctions.ReplaceParameters(ref CommandText, Parameters);
Command = HelperFunctions.GetNewCommand(CommandText);
Reader = Command.ExecuteReader();
if (Reader.Read())
{
return(Reader.GetValue(0).ToString());
}
else
{
return(null);
}
}
#if !DEBUG
catch
{
return("Fehler beim Datenbankzugriff");
}
#endif
finally
{
Command?.Dispose();
Reader?.Close();
}
}
private static string[] GetThreeDatabaseStrings(string CommandText, params SQLParameter[] Parameters)
{
MySqlDataReader Reader = null;
MySqlCommand Command = null;
try
{
HelperFunctions.ReplaceParameters(ref CommandText, Parameters);
Command = HelperFunctions.GetNewCommand(CommandText);
string[] stringArray = new string[4];
Reader = Command.ExecuteReader();
if (Reader.Read())
{
stringArray[0] = Reader.GetValue(0).ToString();
stringArray[1] = Reader.GetValue(1).ToString();
stringArray[2] = Reader.GetValue(2).ToString();
stringArray[3] = Reader.GetValue(3).ToString();
}
return(stringArray);
}
#if !DEBUG
catch
{
return("Fehler beim Datenbankzugriff");
}
#endif
finally
{
Command?.Dispose();
Reader?.Close();
}
}
private static string[] GetStringArray(string CommandString)
{
MySqlDataReader Reader = null;
MySqlCommand Command = null;
try
{
Command = HelperFunctions.GetNewCommand(CommandString);
Reader = Command.ExecuteReader();
List <string> Results = new List <string>();
while (Reader.Read())
{
Results.Add(Reader.GetValue(0).ToString());
}
Results.Sort();
return(Results.ToArray());
}
#if !DEBUG
catch
{
return(new[] { "Fehler beim Datenbankzugriff" });
}
#endif
finally
{
Command?.Dispose();
Reader?.Close();
}
}
public int CreateUser(string connectionString, string userName, string password, int accessId)
{
var salt = PasswordHelper.GenerateSalt();
var hash = PasswordHelper.GenerateHash(salt, password);
var connection = new MySqlConnection(connectionString);
var commandString = "INSERT INTO `users` (`user_name`, `salt`, `password_hash`, `status_id`, `creator_id`) " +
$"VALUES ('{userName}', '{salt}', '{hash}', '{accessId}', '{this.GetUserID()}');";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
connection.Open();
var result = sqlCommand.ExecuteNonQuery();
return(result);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
public List <ModifyUserModel> GetAvailableUsers(string connectionString)
{
var connection = new MySqlConnection(connectionString);
var commandString = "SELECT user_id, user_name, status_id FROM users " +
$"WHERE status_id < '{AccessID}'; ";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
var result = new List <ModifyUserModel>();
var usersTable = new DataTable();
connection.Open();
var dbReader = sqlCommand.ExecuteReader();
usersTable.Load(dbReader);
foreach (DataRow row in usersTable.Rows)
{
var userId = (int)row[0];
var userName = (string)row[1];
var statusId = (int)((sbyte)row[2]);
var modifyUserModel = new ModifyUserModel(userId, userName, statusId);
result.Add(modifyUserModel);
}
return(result);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
public async static Task <bool> DeleteRowAsync(string laboratoryBookName, Permission permission, DataRow row)
{
if (permission == Permission.DatabaseGuest)
{
return(false);
}
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = $"DELETE FROM `laboratory_book_{laboratoryBookName}`" +
$" WHERE (`sampleID` = '{row["sampleID"]}');";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
await connection.OpenAsync();
var i = await sqlCommand.ExecuteNonQueryAsync();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Row delete error", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
await connection.CloseAsync();
sqlCommand?.Dispose();
}
return(true);
}
public static IEnumerable <ProjectInfo> GetProjectsInfo(string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
command.CommandText = "SELECT * FROM `projects`";
connection.Open();
var reader = command.ExecuteReader();
var projectsInfoDataTable = new DataTable();
projectsInfoDataTable.Load(reader);
foreach (DataRow row in projectsInfoDataTable.Rows)
{
var projectInfo = new ProjectInfo
{
Id = (int)row[0],
Name = (string)row[1],
Created = DateTime.Parse(row[2].ToString()),
Published = DateTime.Parse(row[3].ToString()),
Description = (string)row[4],
ImagesChanged = DateTime.Parse(row[5].ToString())
};
yield return(projectInfo);
}
}
finally
{
connection?.Close();
command?.Dispose();
}
}
public static bool SetNewPassword(string connectionString, int userId, string newPassword)
{
var connection = new MySqlConnection(connectionString);
var commandString = $"SELECT salt FROM users WHERE user_id = '{userId}'; ";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
connection.Open();
var salt = (string)sqlCommand.ExecuteScalar();
var hash = GenerateHash(salt, newPassword);
commandString = $" UPDATE `users` SET `password_hash` = '{hash}' WHERE(`user_id` = '{userId}'); ";
sqlCommand.CommandText = commandString;
var updatePasswordResult = (long)sqlCommand.ExecuteNonQuery();
if (updatePasswordResult == 0)
{
return(false);
}
else
{
return(true);
}
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
public static ProjectInfo GetProjectInfo(string projectName, string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
connection.Open();
command.CommandText = $"SELECT `id` FROM `projects` WHERE `name` = '{projectName}';";
var projectId = command.ExecuteScalar();
command.CommandText = $"SELECT * FROM `projects` WHERE `id` = {projectId} ";
var reader = command.ExecuteReader();
var projectsInfoDataTable = new DataTable();
projectsInfoDataTable.Load(reader);
var row = projectsInfoDataTable.Rows[0];
var projectInfo = new ProjectInfo
{
Id = (int)row[0],
Name = (string)row[1],
Created = DateTime.Parse(row[2].ToString()),
Published = DateTime.Parse(row[3].ToString()),
Description = (string)row[4]
};
return(projectInfo);
}
finally
{
connection?.Close();
command?.Dispose();
}
}
//event handler which update user preferences after changes in the view model
public async void User_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var user = sender as UserModel;
var commandString = "UPDATE `db_users` " +
$"SET `permission_id` = '{user.PermissionID}' " +
$"WHERE (`user_id` = '{user.UserID}') and (`db_id` = '{LaboratoryBookID}'); ";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
await connection.OpenAsync();
var result = await sqlCommand.ExecuteNonQueryAsync();
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"User update error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
await connection.CloseAsync();
sqlCommand?.Dispose();
}
}
public virtual DataTable GetDataFromLaboratoryBook(string LaboratoryBookName, Permission Permission)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = $"SELECT * FROM `laboratory_book_{LaboratoryBookName}` WHERE `permissionID`<={(int)Permission};";
var sqlCommand = new MySqlCommand(commandString, connection);
var result = new DataTable();
try
{
connection.Open();
var dbReader = sqlCommand.ExecuteReader();
result.Load(dbReader);
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Get data table error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
return(result);
}
public static IEnumerable <ImageInfo> GetImagesInfo(int projectId, string connectionString)
{
var connection = new MySqlConnection(connectionString);
var command = new MySqlCommand("", connection);
try
{
connection.Open();
command.CommandText = $"SELECT * FROM `projects_images` WHERE `projectid` = {projectId};";
var reader = command.ExecuteReader();
var imageInfoDataTable = new DataTable();
imageInfoDataTable.Load(reader);
foreach (DataRow row in imageInfoDataTable.Rows)
{
var imageInfo = new ImageInfo
{
Id = (int)row[1],
Extension = (string)row[3],
TimeStamp = DateTime.Parse(row[4].ToString())
};
yield return(imageInfo);
}
}
finally
{
connection?.Close();
command?.Dispose();
}
}
private async void Button_Click(object sender, RoutedEventArgs e)
{
var button = sender as Button;
if (button.Content.ToString() == "Close")
{
this.Close();
return;
}
if (button.Content.ToString() == "Add")
{
var user = CbxUsers.SelectedItem as UserModel;
if (user == null)
{
return;
}
user.PermissionID = (sbyte)CbxPermissionIDs.SelectedItem;
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = "INSERT INTO`db_users` (`user_id`, `db_id`, `permission_id`)" +
$" VALUES ('{user.UserID}', '{UsedViewModel.LaboratoryBookID}', '{user.PermissionID}');";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
await connection.OpenAsync();
var result = await sqlCommand.ExecuteNonQueryAsync();
UsedViewModel.LaboratoryBookUsers.Add(user);
user.PropertyChanged += UsedViewModel.User_PropertyChanged;
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Add user error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
this.Close();
return;
}
}
public static async Task <int> GetLaboratoryBookCreatorAsync(string connectionString, string laboratoryBook)
{
if (string.IsNullOrEmpty(connectionString) || string.IsNullOrEmpty(laboratoryBook))
{
throw new ArgumentNullException("connection string or laboratory book are empty");
}
var connection = new MySqlConnection(connectionString);
var commandString = $"SELECT creator_id FROM db_list WHERE db_name = '{laboratoryBook}';";
var command = new MySqlCommand(commandString, connection);
try
{
await connection.OpenAsync();
var result = (int)(await command.ExecuteScalarAsync());
return(result);
}
finally
{
await connection.CloseAsync();
command?.Dispose();
}
}
public virtual List <string> GetColumnNames(string LaboratoryBookName)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = $"SHOW COLUMNS FROM `laboratory_book_{LaboratoryBookName}`;";
var sqlCommand = new MySqlCommand(commandString, connection);
var columnList = new List <string>();
try
{
connection.Open();
var result = new DataTable();
var dbReader = sqlCommand.ExecuteReader();
result.Load(dbReader);
foreach (DataRow row in result.Rows)
{
columnList.Add(row[0].ToString());
}
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
return(columnList);
}
public ObservableCollection <ModifyUserModel> GetAvailableUsers()
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = "SELECT user_id, user_name, status_id FROM users " +
$"WHERE status_id <= '{AccessID}'; ";
var sqlCommand = new MySqlCommand(commandString, connection);
var result = new ObservableCollection <ModifyUserModel>();
var usersTable = new DataTable();
connection.Open();
var dbReader = sqlCommand.ExecuteReader();
usersTable.Load(dbReader);
connection.Close();
sqlCommand?.Dispose();
foreach (DataRow row in usersTable.Rows)
{
var userId = (int)row[0];
var userName = (string)row[1];
var statusId = (int)((sbyte)row[2]);
var modifyUserModel = new ModifyUserModel(userId, userName, statusId);
result.Add(modifyUserModel);
}
return(result);
}
public async Task <bool> DeleteLaboratryBookAsync(DeleteLaboratoryBookOptions options)
{
var deleteCommandString = GetDeleteBookCommandString(options.EmbeddedResourcePath);
deleteCommandString = SetProperLaboratoryBookNameAndId(
deleteCommandString,
options.LaboratoryBookName,
options.LaboratoryBookId
);
var connection = new MySqlConnection(options.ConnectionString);
var command = new MySqlCommand(deleteCommandString, connection);
try
{
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
return(true);
}
finally
{
await connection.CloseAsync();
command?.Dispose();
}
}
public bool ChangeUserStatus(ModifyUserModel changedUser)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = $"UPDATE `users` SET `status_id` = '{changedUser.UserStatusId}'" +
$" WHERE (`user_id` = '{changedUser.UserId}');";
var sqlCommand = new MySqlCommand(commandString, connection);
try
{
connection.Open();
var result = (int)sqlCommand.ExecuteNonQuery();
if (result > 0)
{
return(true);
}
return(false);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
public ObservableCollection <AccessStatusModel> GetAccessStatusList()
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = "SELECT * FROM access_status ORDER BY access_status_id; ";
var sqlCommand = new MySqlCommand(commandString, connection);
var result = new ObservableCollection <AccessStatusModel>();
var usersTable = new DataTable();
connection.Open();
var dbReader = sqlCommand.ExecuteReader();
usersTable.Load(dbReader);
connection.Close();
sqlCommand?.Dispose();
foreach (DataRow row in usersTable.Rows)
{
var accessStatusId = (int)row[0];
var accessStatusName = (string)row[1];
var accessStatusModel = new AccessStatusModel(accessStatusId, accessStatusName);
result.Add(accessStatusModel);
}
return(result);
}
string ExecQueryString(string queryString, out DataTable dt)
{
string msg = "";
dt = new DataTable();
string MSSQL = ConfigurationManager.AppSettings["MSSQL"];
MySqlConnection conn = new MySqlConnection(MSSQL);
MySqlCommand scmCmdToExecute = new MySqlCommand(queryString, conn);
scmCmdToExecute.CommandType = CommandType.Text;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
dt.Load(scmCmdToExecute.ExecuteReader());
}
catch (Exception ex)
{
msg = "SQL query error : " + ex.ToString();
}
finally
{
scmCmdToExecute?.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return(msg);
}
//get book ID of current laboratory book
private int GetLaboratoryBookID(string laboratoryBookName)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = "SELECT db_id " +
"FROM db_list " +
$"WHERE db_name = '{laboratoryBookName}';";
var sqlCommand = new MySqlCommand(commandString, connection);
int?laboratoryBookID = null;
try
{
connection.Open();
laboratoryBookID = (int)(sqlCommand.ExecuteScalar());
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Get book ID error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
return((int)laboratoryBookID);
}
//get users which have an access to this laboratory book
private ObservableCollection <UserModel> GetUsers(int laboratoryBookID)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandStringBuilder = new StringBuilder();
commandStringBuilder.Append("SELECT users.user_id, user_name, permission_id ");
commandStringBuilder.Append("FROM users ");
commandStringBuilder.Append("JOIN db_users ");
commandStringBuilder.Append("ON users.user_id = db_users.user_id ");
commandStringBuilder.Append($"WHERE db_users.db_id = '{laboratoryBookID}';");
var commandString = commandStringBuilder.ToString();
var sqlCommand = new MySqlCommand(commandString, connection);
var usersDataTable = new DataTable();
var result = new ObservableCollection <UserModel>();
try
{
connection.Open();
var dbReader = sqlCommand.ExecuteReader();
usersDataTable.Load(dbReader);
foreach (DataRow row in usersDataTable.Rows)
{
var user = new UserModel()
{
UserID = (int)row[0],
UserName = (string)row[1],
PermissionID = (sbyte)row[2]
};
if (user.UserID != LaboratoryBookUser.UserID)
{
result.Add(user);
}
}
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Search error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
return(result);
}
private int ejecutarComando(string sql)
{
MySqlCommand myCommand = new MySqlCommand(sql,this.myConnection);
int afectadas = myCommand.ExecuteNonQuery();
myCommand.Dispose();
myCommand = null;
return afectadas;
}
public void Dispose()
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection?.Dispose();
command?.Dispose();
}
//get columns which can be modified
private ObservableCollection <ColumnModel> GetColumns(int laboratoryBookID)
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var sqlCommand = new MySqlCommand()
{
Connection = connection,
CommandText = $"SELECT db_name FROM db_list WHERE db_id = '{laboratoryBookID}'; "
};
try
{
connection.Open();
var laboratoryBookName = (string)sqlCommand.ExecuteScalar();
sqlCommand.CommandText = $"SHOW columns FROM laboratory_book_{laboratoryBookName}; ";
var dataBaseReader = sqlCommand.ExecuteReader();
var columnsTable = new DataTable();
columnsTable.Load(dataBaseReader);
var ColumnList = new ObservableCollection <ColumnModel>();
foreach (DataRow row in columnsTable.Rows)
{
var column = new ColumnModel()
{
ColumnName = (string)row[0],
ColumnType = (string)row[1]
};
if (!ForbidenToModifyColumnNames.Contains(column.ColumnName))
{
ColumnList.Add(column);
}
}
return(ColumnList);
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Get columns error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
return(null);
}
private void GetUsersComboboxValues()
{
var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString;
var connection = new MySqlConnection(connectionString);
var commandString = "SELECT user_id, user_name FROM users;";
var sqlCommand = new MySqlCommand(commandString, connection);
var userTable = new DataTable();
try
{
connection.Open();
var reader = sqlCommand.ExecuteReader();
userTable.Load(reader);
var userCollection = new ObservableCollection <UserModel>();
foreach (DataRow row in userTable.Rows)
{
var user = new UserModel()
{
UserID = (int)row[0],
UserName = (string)row[1],
PermissionID = 1
};
if (UsedViewModel.LaboratoryBookUsers
.Where(_user => _user.UserID == user.UserID)
.ToArray()
.Count() == 0)
{
if (UsedViewModel.LaboratoryBookUser.UserID != user.UserID)
{
userCollection.Add(user);
}
}
}
Users = userCollection;
}
catch (Exception exception)
{
MessageBox.Show
(
exception.Message,
"Add users error",
MessageBoxButton.OK,
MessageBoxImage.Error
);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
private void Dispose(MySqlConnection c, MySqlCommand com, MySqlDataReader dr)
{
try {
com?.Dispose();
dr?.Close();
dr?.Dispose();
c.Close();
listConnections.Remove(c);
c.Dispose();
} catch {
}
}
public static LoginUserModel GetUserByName(string userName, string connectionString)
{
var connection = new MySqlConnection(connectionString);
var sqlCommand = new MySqlCommand
{
CommandText = $"SELECT `status_id` FROM `users` WHERE `user_name` = '{userName}';",
Connection = connection
};
try
{
connection.Open();
var statusSbyte = (sbyte)(sqlCommand.ExecuteScalar());
var userStatus = string.Empty;
switch (statusSbyte)
{
case 1: userStatus = "Guest";
break;
case 2: userStatus = "Laborant";
break;
case 3: userStatus = "Moderator";
break;
case 4: userStatus = "Administer";
break;
default: userStatus = "Guest";
break;
}
sqlCommand.CommandText = $"SELECT `user_id` FROM users WHERE user_name = '{userName}'; ";
var userID = (int)(sqlCommand.ExecuteScalar());
var result = new LoginUserModel()
{
UserId = userID,
UserName = userName,
UserStatus = userStatus
};
return(result);
}
finally
{
connection.Close();
sqlCommand?.Dispose();
}
}
private void _Query(string queryString, QueryAction queryAction, ResultAction resultAction, Delegate postDelegate, bool query)
{
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
connection = GetConnection();
command = new MySqlCommand(
queryString,
connection
);
queryAction?.Invoke(command);
if (query)
{
reader = command.ExecuteReader();
while (reader.Read())
{
resultAction?.Invoke(reader);
}
}
else
{
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
reader?.Dispose();
command?.Dispose();
connection?.Dispose();
try
{
postDelegate?.DynamicInvoke();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
public bool Buscarid( string id )
{
this.abrirConexion();
MySqlCommand myCommand = new MySqlCommand(this.queryBuscar( id ),myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
bool retorno = myReader.HasRows;
myReader.Close();
myReader = null;
myCommand.Dispose();
myCommand = null;
this.cerrarConexion();
return retorno;
}
public void mostrarTodos()
{
this.abrirConexion();
MySqlCommand myCommand = new MySqlCommand(this.querySelect(),myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read()){
string id = myReader["id"].ToString();
string codigo = myReader["codigo"].ToString();
string nombre = myReader["nombre"].ToString();
Console.WriteLine("ID: " + id +
" Código: " + codigo +
" Nombre: " + nombre);
}
myReader.Close();
myReader = null;
myCommand.Dispose();
myCommand = null;
this.cerrarConexion();
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params Object[] cmdParms)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的 Object[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
Object[] cmdParms = (Object[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, params Object[] cmdParms)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
MySqlParameter myParameter = new MySqlParameter("?content", MySqlDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行SQL语句，设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
public string select_from()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
String rowText = string.Empty;
int topRows = ds.Tables[0].Rows.Count;
for (int i = 0; i < topRows; i++)
{
foreach (DataColumn column in dataTable.Columns)
{
rowText += dataTable.Rows[i][column.ColumnName] + "|";
}
rowText = rowText + "-";
}
connection.Close();
return rowText;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (MySqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
MySqlParameter myParameter = new MySqlParameter("?fs", MySqlDbType.Blob);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand(SQLString, connection);
MySqlParameter myParameter = new MySqlParameter("?content", MySqlDbType.VarChar);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query2(string SQLString, params Object[] cmdParms)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (MySqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
}
}
}