Example #1
1
    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;
    }
Example #2
0
        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);
        }
Example #3
0
        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();
            }
        }
Example #9
0
        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);
        }
Example #10
0
        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();
            }
        }
Example #11
0
        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();
            }
        }
Example #12
0
        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();
            }
        }
Example #14
0
        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);
        }
Example #15
0
        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);
        }
Example #19
0
        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();
            }
        }
Example #21
0
        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();
            }
        }
Example #22
0
        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);
        }
Example #23
0
        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);
        }
Example #26
0
 private int ejecutarComando(string sql)
 {
     MySqlCommand myCommand = new MySqlCommand(sql,this.myConnection);
         int afectadas = myCommand.ExecuteNonQuery();
         myCommand.Dispose();
         myCommand = null;
         return afectadas;
 }
Example #27
0
 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();
            }
        }
Example #30
0
 private void Dispose(MySqlConnection c, MySqlCommand com, MySqlDataReader dr)
 {
     try {
         com?.Dispose();
         dr?.Close();
         dr?.Dispose();
         c.Close();
         listConnections.Remove(c);
         c.Dispose();
     } catch {
     }
 }
Example #31
0
        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();
            }
        }
Example #32
0
        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);
                }
            }
        }
Example #33
0
    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;
    }
Example #34
0
    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();
    }
Example #35
0
 /// <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();
             }
         }
     }
 }
Example #36
0
 /// <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();
             }
         }
     }
 }
Example #37
0
            /// <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();
                        }
                    }
                }
            }
Example #38
0
 /// <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();
             }
         }
     }
 }
Example #39
0
 /// <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();
         }
     }
 }
Example #40
0
 /// <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();
             }
         }
     }
 }
Example #41
0
    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;
    }
Example #42
0
 /// <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();
         }
     }
 }
Example #43
0
 /// <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();
         }
     }
 }
Example #44
0
 /// <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();
         }
     }
 }
Example #45
0
 /// <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;
         }
     }
 }