/// <summary>
/// Retourne la valeur typée du champ spécifié par un nom
/// </summary>
/// <typeparam name="T">Type de la valeur attendue</typeparam>
/// <param name="NomChamp">Nom du champ pour lequel on veut récupérer sa valeur</param>
/// <param name="ValeurParDefaut">Valeur par défaut à retourner si le champ n'existe pas ou si sa valeur n'est pas du type attendu</param>
/// <returns>Valeur du champ si il existe, sinon null</returns>
public T ValeurChamp <T>(string NomChamp, T ValeurParDefaut = default(T))
{
object Resultat = this[NomChamp];
if (Resultat == null)
{
return(ValeurParDefaut);
}
if (Resultat is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime Valeur = (MySql.Data.Types.MySqlDateTime)Resultat;
Resultat = Valeur.IsValidDateTime ? (object)Valeur.GetDateTime() : ValeurParDefaut;
}
else if (Resultat is MySql.Data.Types.MySqlDecimal)
{
MySql.Data.Types.MySqlDecimal Valeur = (MySql.Data.Types.MySqlDecimal)Resultat;
Resultat = !Valeur.IsNull ? (object)Valeur.Value : ValeurParDefaut;
}
return((Resultat is T) ? (T)Resultat : ValeurParDefaut);
}
/// <summary>
/// Permet de récupérer la valeur typée du premier champ du premier enregistrement résultant d'une requête de consultation
/// </summary>
/// <typeparam name="T">Type de la valeur attendue</typeparam>
/// <param name="Requete">Requête SQL à exécuter (doit être de type SELECT ou SHOW)</param>
/// <param name="Valeurs">Valeurs des parties variables</param>
/// <returns>Valeur typée du premier champ du premier enregistrement si possible, sinon null</returns>
public T ValeurDe <T>(string Requete, params object[] Valeurs)
{
object Resultat = ValeurDeAvecDefaut(default(T), Requete, Valeurs);
if (Resultat is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime Valeur = (MySql.Data.Types.MySqlDateTime)Resultat;
Resultat = Valeur.IsValidDateTime ? (object)Valeur.GetDateTime() : null;
}
else if (Resultat is MySql.Data.Types.MySqlDecimal)
{
MySql.Data.Types.MySqlDecimal Valeur = (MySql.Data.Types.MySqlDecimal)Resultat;
Resultat = !Valeur.IsNull ? (object)Valeur.Value : null;
}
if (Resultat is T)
{
return((T)Resultat);
}
if (SurErreur != null)
{
SurErreur(this, MethodeExecutantRequeteSql.ValeurDe_Typee, Requete, Valeurs, string.Format("Imcompatibilité entre le résultat qui est de type {0} et le type attendu qui est {1} !", (Resultat != null) ? Resultat.GetType().FullName : "null", typeof(T).FullName));
}
return(default(T));
}
private DataTable ConvertDataTable(DataTable dataTable, BulkCopyInfo bulkCopyInfo)
{
var columns = dataTable.Columns.Cast <DataColumn>();
if (!columns.Any(item => item.DataType == typeof(MySql.Data.Types.MySqlDateTime)))
{
return(dataTable);
}
Dictionary <int, Type> changedColumnTypes = new Dictionary <int, Type>();
Dictionary <(int RowIndex, int ColumnIndex), object> changedValues = new Dictionary <(int RowIndex, int ColumnIndex), object>();
DataTable dtChanged = dataTable.Clone();
int rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
object value = row[i];
if (value != null)
{
Type type = value.GetType();
if (type != typeof(DBNull))
{
if (type == typeof(MySql.Data.Types.MySqlDateTime))
{
MySql.Data.Types.MySqlDateTime mySqlDateTime = (MySql.Data.Types.MySqlDateTime)value;
TableColumn tableColumn = bulkCopyInfo.Columns.FirstOrDefault(item => item.Name == dataTable.Columns[i].ColumnName);
string dataType = tableColumn.DataType.ToLower();
Type columnType = null;
if (dataType.Contains("date") || dataType.Contains("timestamp"))
{
DateTime dateTime = mySqlDateTime.GetDateTime();
columnType = typeof(DateTime);
changedValues.Add((rowIndex, i), dateTime);
}
if (columnType != null && !changedColumnTypes.ContainsKey(i))
{
changedColumnTypes.Add(i, columnType);
}
}
}
}
}
rowIndex++;
}
if (changedColumnTypes.Count == 0)
{
return(dataTable);
}
for (int i = 0; i < dtChanged.Columns.Count; i++)
{
if (changedColumnTypes.ContainsKey(i))
{
dtChanged.Columns[i].DataType = changedColumnTypes[i];
}
}
rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
DataRow r = dtChanged.NewRow();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var value = row[i];
if (changedValues.ContainsKey((rowIndex, i)))
{
r[i] = changedValues[(rowIndex, i)];
}
public static Block newFromDataRow(object[] values)
{
Block b = null;
try
{
b = new Block( );
b.ipb_id = (int)(values[0]);
b.ipb_address = Utility.Net.IPAddress.newFromEncodedString((byte[])values[1]);
b.ipb_user = (uint)values[2];
b.ipb_by = (uint)values[3];
b.ipb_reason = ASCIIEncoding.ASCII.GetString((byte[] )values[4]);
//System.OverflowException was unhandled
//Message="Value was either too large or too small for an Int32."
//Source="mscorlib"
//StackTrace:
// at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
// at MySql.Data.Types.MySqlDateTime.ParseMySql(String s, Boolean is41)
// at MySql.Data.Types.MySqlDateTime.Parse(String s)
// at MySql.Data.Types.MySqlDateTime..ctor(String s)
// at BlockedOpenProxyReviewerBot.Block.newFromDataRow(Object[] values) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Block.cs:line 37
// at BlockedOpenProxyReviewerBot.Database.getProxyBlocks() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Database.cs:line 44
// at BlockedOpenProxyReviewerBot.Program.runBot() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 37
// at BlockedOpenProxyReviewerBot.Program..ctor(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 24
// at BlockedOpenProxyReviewerBot.Program.Main(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 12
// at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
// at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
// at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
// at System.Threading.ThreadHelper.ThreadStart()
//InnerException:
byte[] x = (byte[])values[5];
string y = ASCIIEncoding.ASCII.GetString(x);
MySql.Data.Types.MySqlDateTime z = new MySql.Data.Types.MySqlDateTime(
int.Parse(y.Substring(0, 4)),
int.Parse(y.Substring(4, 2)),
int.Parse(y.Substring(6, 2)),
int.Parse(y.Substring(8, 2)),
int.Parse(y.Substring(10, 2)),
int.Parse(y.Substring(12, 2))
);
b.ipb_timestamp = z.GetDateTime( );
// b.ipb_timestamp = new MySql.Data.Types.MySqlDateTime( ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 5 ] ) ).GetDateTime( );
b.ipb_auto = (bool)values[6];
b.ipb_anon_only = (bool)values[7];
b.ipb_create_account = (bool)values[8];
b.ipb_expiry = ASCIIEncoding.ASCII.GetString((byte[] )values[9]);
if (((byte[])values[10]).Length == 4)
{
b.ipb_range_start = new IPAddress((byte[])values[10]);
}
if (((byte[])values[11]).Length == 4)
{
b.ipb_range_end = new IPAddress((byte[])values[11]);
}
b.ipb_enable_autoblock = (bool)values[12];
b.ipb_deleted = (bool)values[13];
b.ipb_block_email = (bool)values[14];
b.ipb_by_text = ASCIIEncoding.ASCII.GetString((byte[] )values[15]);
}
catch (OverflowException)
{
}
return(b);
}
public static Block newFromDataRow( object[ ] values )
{
Block b = null;
try
{
b = new Block( );
b.ipb_id = (int)( values[ 0 ] );
b.ipb_address = Utility.Net.IPAddress.newFromEncodedString( (byte[ ])values[ 1 ] );
b.ipb_user = (uint)values[ 2 ];
b.ipb_by = (uint)values[ 3 ];
b.ipb_reason = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 4 ] );
//System.OverflowException was unhandled
//Message="Value was either too large or too small for an Int32."
//Source="mscorlib"
//StackTrace:
// at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
// at MySql.Data.Types.MySqlDateTime.ParseMySql(String s, Boolean is41)
// at MySql.Data.Types.MySqlDateTime.Parse(String s)
// at MySql.Data.Types.MySqlDateTime..ctor(String s)
// at BlockedOpenProxyReviewerBot.Block.newFromDataRow(Object[] values) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Block.cs:line 37
// at BlockedOpenProxyReviewerBot.Database.getProxyBlocks() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Database.cs:line 44
// at BlockedOpenProxyReviewerBot.Program.runBot() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 37
// at BlockedOpenProxyReviewerBot.Program..ctor(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 24
// at BlockedOpenProxyReviewerBot.Program.Main(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 12
// at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
// at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
// at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
// at System.Threading.ThreadHelper.ThreadStart()
//InnerException:
byte[ ] x = (byte[ ])values[ 5 ];
string y = ASCIIEncoding.ASCII.GetString( x );
MySql.Data.Types.MySqlDateTime z = new MySql.Data.Types.MySqlDateTime(
int.Parse( y.Substring( 0, 4 ) ),
int.Parse( y.Substring( 4, 2 ) ),
int.Parse( y.Substring( 6, 2 ) ),
int.Parse( y.Substring( 8, 2 ) ),
int.Parse( y.Substring( 10, 2 ) ),
int.Parse( y.Substring( 12, 2 ) )
);
b.ipb_timestamp = z.GetDateTime( );
// b.ipb_timestamp = new MySql.Data.Types.MySqlDateTime( ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 5 ] ) ).GetDateTime( );
b.ipb_auto = (bool)values[ 6 ];
b.ipb_anon_only = (bool)values[ 7 ];
b.ipb_create_account = (bool)values[ 8 ];
b.ipb_expiry = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 9 ] );
if( ( (byte[ ])values[ 10 ] ).Length == 4 )
{
b.ipb_range_start = new IPAddress( (byte[ ])values[ 10 ] );
}
if( ( (byte[ ])values[ 11 ] ).Length == 4 )
{
b.ipb_range_end = new IPAddress( (byte[ ])values[ 11 ] );
}
b.ipb_enable_autoblock = (bool)values[ 12 ];
b.ipb_deleted = (bool)values[ 13 ];
b.ipb_block_email = (bool)values[ 14 ];
b.ipb_by_text = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 15 ] );
}
catch( OverflowException )
{
}
return b;
}
// genreal purpose slelect statement genrator and caller
public List <string>[] SelectQuery(string tableName, List <string> fieldNames)
{
//RESULTS LIST CONSTRUCTION
//creates main list object (rows) to hold indivaidual values(columns)
List <string>[] results = new List <string> [fieldNames.Count];
// generic counter to use for the loop
int counter = 0;
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
//rest the counter
counter = 0;
//SELECT QUERY CONSRUCTION
//query initaliastion - SELECT part
string query = "SELECT ";
//loop through each fieldname and add to select statement
foreach (var field in fieldNames)
{
// add comma to seprate around filed name and add field to quey (and a space)
if (counter > 0)
{
query += ",";
}
// add backquotes around filed name and add field to query
query += "`" + field + "` ";
counter++;
}
//reset counter
counter = 0;
// now add FROM part - table name with backquotes around it
query += "FROM " + "`" + tableName + "`";
// comment / uncomment to debug - this message box will display the query
MessageBox.Show(query);
//CONNECT, RUN QUERY and POPULATE RESULTS
// open connection
if (this.OpenConnection())
{
//create MySQL command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a 'data reder' to get the results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop though each row off the results
while (dataReader.Read())
{
//loop through each fieldname and add to select statement
foreach (var field in fieldNames)
{
//in the square brackets, the string must match
//the column / filed on the databaase
//step through to check values
//if this is a data value then we need to conver to prevent issues
if (dataReader[field].GetType().ToString() ==
"MySql.Data.Types.MySqlDateTime")
{
//get the current mysql data object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//convert to c# data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in the MySQL style before adding results
results[counter].Add(csFormat.ToString("yyyy-MM-dd"));
}
else
{
//add the value to the results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
// reset couner before geting new row of data
counter = 0;
}
// close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return the results
return(results);
}
//genral purpose select query for searching strings
public List <string>[] SearchLikeQuery(string tableName, List <string> fieldNames,
string searchField, string searchValue)
{
//RESULTS LIST CONSTRUCTION------------------------------------
//create main list object (rows) to hold individual lists (column)
List <string>[] results = new List <string> [fieldNames.Count];
//genric counter to be used for loops
int counter = 0;
//create the indivual list to hold the results for each field / column
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
//reset counter
counter = 0;
//SELECT QUERY CONSTUCTION--------------------------------------------
//query initialisation - SElECT part
string query = "SELECT ";
//loop tough each field name and add to select statement
foreach (var field in fieldNames)
{
//add comma to separate list items not first item
if (counter > 0)
{
query += ",";
}
//add backquotes around fieldname and add field to query and a space
query += "`" + field + "` ";
counter++;
}
//rest counter
counter = 0;
//now add FROM part - table name woth backquotes around it
query += "FROM " + "`" + tableName + "`";
//now add where clause with Like for search
query += " WHERE " + "`" + searchField + "`" +
" LIKE " + "'" + "%" + searchValue + "%" + "'";
//comment / uncomment to debug - this messagebox will display the query
MessageBox.Show(query);
//CONNECT, RUN QUERY AND POPULATE RESULTS------------------------------------
//open connection
if (this.OpenConnection())
{
//create MYSQL command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a 'data reader' to get results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop thorugh each row of results
while (dataReader.Read())
{
//loop through each field name and add to select statemnts
foreach (var field in fieldNames)
{
//in the square brackets, the string must match
//the column / field name on the data base
// step through to check values
if (dataReader[field].GetType().ToString() ==
"MySql.Data.Types.MySqlDataTime")
{
//get the current my squl object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//conver to c# data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in style of my sql
results[counter].Add(csFormat.ToString("yyyy-MM--dd"));
}
else
{
//add the value to results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
//reset counter
counter = 0;
}
//close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return results
return(results);
}
// general purpose statement genrator and caller for single row from id
public List <string>[] SelectRowWithId(string tableName, List <string> fieldNames,
string idFieldName, string idValue)
{
//RESULTS LIST CONSTRUCTION--------------------------------------------------
//create main list object (rows) to hold individual lists (columns)
List <string>[] results = new List <string> [fieldNames.Count];
// generic counter to be used for loop
int counter = 0;
//create the individual lists to hold the results for each field/column
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
// reset counnter
counter = 0;
// SELECT QUERY CONSTRUCTION
string query = "SELECT ";
//loop thorugh each fieldname and add to select statemnt
foreach (var field in fieldNames)
{
//add comma to each field name and add to select statement
if (counter > 0)
{
query += ",";
}
// add backquotes around field name and add field to quetu ( and a space)
query += "`" + field + "`";
counter++;
}
//reset counter
counter = 0;
// now add from part
query += "FROM " + "`" + tableName + "`";
// now add where part to select the row based on ID
query += " WHERE " + "`" + idFieldName + "`" + "=" + idValue;
//comment / uncomment to debug
MessageBox.Show(query);
//connect, run query and populate results --------------------------------------
// open connection
if (this.OpenConnection())
{
//create me Sql command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a data reader to get the results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop thorugh each row of results
while (dataReader.Read())
{
foreach (var field in fieldNames)
{
//in the square brackets, the string must macth
//the column/ field name on the database
//step through to check valeus
//if this is a data value then we need to convert to pervent issues
if (dataReader[field].GetType().ToString() == "MySql.Data.Types.MySqlDateTime")
{
// get the current mysql date object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//convert to csharp data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in the MySQL style before adding results
results[counter].Add(csFormat.ToString("yyyy-MM-dd"));
}
else
{
//add values to the results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
//reset the counter before getting next row of data
counter = 0;
}
//close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return results
return(results);
}