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; } } }