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