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