public DataTable DoQuery() { _dbConnector.Connect(); // Which stored procedure to use for retrieving data from server MySqlDataAdapter adapter = new MySqlDataAdapter() { SelectCommand = new MySqlCommand(Procedure, _dbConnector.DbConnectionInstance) { CommandType = CommandType.StoredProcedure } }; DataTable serverTable = new DataTable("TempTable"); // Populates DataTable with records from the server adapter.Fill(serverTable); // Avoid getting mark for archive column which causes index out of bounds during table comparison TargetDataTable.Columns.RemoveAt(TargetDataTable.Columns.Count - 1); // Do updates serverTable = CompareTable(serverTable, TargetDataTable); MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter); builder.GetUpdateCommand(); adapter.Update(serverTable); _dbConnector.Disconnect(); return(null); }
public DataTable DoQuery() { _dbConnector.Connect(); MySqlCommand command = new MySqlCommand(Procedure, _dbConnector.DbConnectionInstance) { CommandType = CommandType.StoredProcedure }; if (Parameters.Count > 0) { foreach (var parameter in Parameters) { command.Parameters.AddWithValue(parameter.Key, parameter.Value); } } command.ExecuteNonQuery(); command.Dispose(); _dbConnector.Disconnect(); return(null); }
// Check account existence // Get all users public DataTable DoQuery() { _dbConnector.Connect(); DataSet dataset = new DataSet("MainDataset"); _dataTable = new DataTable(); _dataTable = dataset.Tables.Add("MainTable"); MySqlCommand command = new MySqlCommand(Procedure, _dbConnector.DbConnectionInstance) { CommandType = CommandType.StoredProcedure }; if (Parameters?.Count > 0) { foreach (var parameter in Parameters) { command.Parameters.AddWithValue(parameter.Key, parameter.Value); } } using (MySqlDataReader reader = command.ExecuteReader()) { if (!reader.HasRows) { return(null); } int columCount = reader.FieldCount; for (int i = 0; i < columCount; i++) { _dataTable.Columns.Add(reader.GetName(i)); } if (Procedure == "GetResident" || Procedure == "GetResidentsList") { _dataTable.Columns.Add("Mark for archive", typeof(bool)); _dataTable.Columns["Mark for archive"].DefaultValue = false; _dataTable.Columns.Add("Generate QR", typeof(bool)); _dataTable.Columns["Generate QR"].DefaultValue = false; } else if (Procedure.Contains("GetArchive")) { _dataTable.Columns.Add("Mark for restore", typeof(bool)); _dataTable.Columns["Mark for restore"].DefaultValue = false; } else if (Procedure == "GetAdmins") { _dataTable.Columns.Add("Mark for delete", typeof(bool)); _dataTable.Columns["Mark for delete"].DefaultValue = false; } while (reader.Read()) { var currentRow = _dataTable.NewRow(); for (int i = 0; i < columCount; i++) { if (reader.IsDBNull(i)) { currentRow[i] = string.Empty; continue; } currentRow[i] = reader.GetString(i); } _dataTable.Rows.Add(currentRow); } } _dbConnector.Disconnect(); return(_dataTable); }
public DataTable DoQuery() { _dbConnector.Connect(); List <string> keys = new List <string>(); if (TargetDataTable != null) { // When target table is supplied for checking for (int i = 0; i < TargetDataTable.Rows.Count; i++) { // Check marked rows and of which mark type if ((bool)TargetDataTable.Rows[i].ItemArray[TargetDataTable.Columns.Count - (int)MarkType]) { keys.Add(TargetDataTable.Rows[i][TargetDataTable.Columns[0].ColumnName].ToString()); if (MarkType == Mark.QR) { string residentQRInfo = string.Empty; for (int j = 1; j <= 7; j++) { residentQRInfo += TargetDataTable.Rows[i].ItemArray[j].ToString() + ","; } RowDatas.Add(residentQRInfo.Remove(residentQRInfo.Length - 1)); } } } } else { // Values are manually supplied and will change accordingly foreach (var value in ParameterValues) { keys.Add(value); } } // Procedure not supplied means TargetDataTable is the only needed. // No need to query from server if (Procedure == string.Empty || Procedure == null) { return(null); } // Do query for how many rows are changed foreach (string key in keys) { MySqlCommand command = new MySqlCommand(Procedure, _dbConnector.DbConnectionInstance) { CommandType = CommandType.StoredProcedure }; command.Parameters.AddWithValue(ParameterName, key); command.ExecuteNonQuery(); command.Dispose(); } return(null); }