/// <summary> /// Escapes characters to make a MySQL readable query. /// </summary> /// <param name="str">The string to translate</param> /// <param name="conn">A valid, open connection</param> /// <returns>The quoted escaped string</returns> /// Modified by Chris Palowitch ([email protected]) /// utilizing StringBuilder for acceptable performance with large data internal static string Escape(string str, MySQLConnection conn) { byte[] bytes = conn.CharacterEncoding.GetBytes(str); StringBuilder to = new StringBuilder(bytes.Length * 2 + 1); StringBuilder result = new StringBuilder(bytes.Length * 2 + 1); result.Append('\''); conn.NativeConnection.mysql_real_escape_string(to, str, (uint)bytes.Length); result.Append(to); result.Append('\''); return(result.ToString()); }
public BindVarQueryStatement(MySQLConnection connection, bool closeConnection, string query, MySQLParameterCollection parameterCollection, uint?fetchSize, CursorType cursorType) : base(connection, query, parameterCollection, fetchSize, cursorType) { _connection = connection; var fields = new List <MYSQL_FIELD>(); using (var resultMetadata = new NativeResultMetadata(Stmt)) { for (var i = 0; i < resultMetadata.mysql_num_fields(); i++) { IntPtr fieldPtr = resultMetadata.mysql_fetch_field_direct((uint)i); var field = Marshal.PtrToStructure <MYSQL_FIELD>(fieldPtr); fields.Add(field); } } if (Stmt.mysql_stmt_execute() != 0) { throw new MySqlException(Stmt); } _fields = fields.ToArray(); _closeConnection = closeConnection; IsClosed = false; _rowColumns = new MYSQL_BIND[_fields.Length]; for (var index = 0; index < _fields.Length; index++) { var fieldMetadata = _fields[index]; _rowColumns[index] = new MYSQL_BIND(); //else if (fieldMetadata.Type == enum_field_types.MYSQL_TYPE_NULL && parameters != null && parameters.Count > index)//Caso select distinct donde mysql_stmt_bind_param3 mapea erroneamente a NULL //{ // // TODO: case needs deep review // fieldMetadata.Type = PreparedStatement.DbtoMysqlType(parameters[index].DbType); //} _rowColumns[index].InitForBind(fieldMetadata, Stmt._nativeConnection); } sbyte code = Stmt.mysql_stmt_bind_result(_rowColumns); if (code != 0) { throw new MySqlException(Stmt); } }
/// <summary> /// A shortcut to make a simple select command with where and sorting part. (where and sorting would be null) /// </summary> /// <param name="conn">Database connection</param> /// <param name="Tables">An array of table names as strings. Examples: /// <code> /// new string[]{"trash","users"} /// /// new string[]{"trash",users} /// /// new string[]{"trash"} /// /// null /// </code> /// </param> /// <param name="Fields">An array of field names (or expressions) as strings. Examples: /// <code> /// new string[]{"id","name"} /// /// new string[]{"id",name} /// /// new string[]{"id"} /// /// null /// </code> /// </param> /// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}} /// /// new object[,]{{"myfield","=",100}} /// /// new object[,]{{"myfield","=",myvalue}} /// /// null /// </code> /// </param> /// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=","myfield2"}} /// /// null /// </code> /// </param> /// <param name="Sorting">Pairs of {field,sorttype} as an string array. For example: /// <code> /// new string[] {{"SettingID","ASC"},{"SettingValue","DESC"}} /// /// new string[] {{"SettingID","ASC"}} /// /// null /// </code></param> /// <example>Example looking for SettingValue by SettingID<code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// DataTable dt = new MySQLSelectCommand(DBConn, /// new string[] {"SettingID","SettingValue"}, /// new string[] {"Settings"}, /// new object[,] {{"SettingID","=",SettingID}}, /// null, /// null /// ).Table; /// string Value = null; /// if(dt.Rows.Count!=0) /// { /// Value = dt.Rows[0]["SettingValue] as string; /// } /// else /// { /// // Value not found /// } /// ... /// DBConn.Close(); /// </code></example> public MySQLSelectCommand( MySQLConnection conn, // Database connection string[] Fields, // Fields to show (also admit expressions) string[] Tables, // Tables object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing) string[,] WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing) string[,] Sorting // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority) ) { DoSelect( conn, // Database connection Fields, // Fields to show (also admit expressions) Tables, // Tables WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing) WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing) Sorting, // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority) false, 0, 0, false ); }
internal MySQLTransaction(MySQLConnection connection, IsolationLevel isolationLevel) { Connection = connection; var sql = ""; switch (isolationLevel) { case IsolationLevel.ReadCommitted: sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"; break; case IsolationLevel.ReadUncommitted: sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"; break; case IsolationLevel.RepeatableRead: sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ"; break; case IsolationLevel.Serializable: sql = "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE"; break; case IsolationLevel.Chaos: throw new MySqlException("MySQLDriverCS Error: Chaos isolation level is not implemented in MySQL."); } IsolationLevel = isolationLevel; using (var cmd = new MySQLCommand(sql, connection)) { cmd.ExecuteNonQuery(); } using (var cmd = new MySQLCommand("BEGIN", connection)) { cmd.ExecuteNonQuery(); } }
/// <summary> /// Extended SELECT with all settings (Except INTO OUTFILE/DUMPFILE for security reasons) /// </summary> /// <param name="conn">Database connection</param> /// <param name="PrevFlags">Flags used in SELECT: [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]</param> /// <param name="FieldsAndAlias">fields like {{"f1"},{"f2"}} or {{"f1","aliasf1"},...}</param> /// <param name="FieldsTitles">If it's null they're retrieved from FieldsAndAlias. Useful for re-aliasing and required if you use AS or explicit index using.</param> /// <param name="Tables">Table list</param> /// <param name="WhereValues">Like {{field,op,value}...}</param> /// <param name="WhereExpressions">Like {{expr1,op,expr2}}</param> /// <param name="GroupBy">Like {{field}...} or {{field,ASC|DESC}...}</param> /// <param name="GroupByHavingValues">like {{field,op,value}...}</param> /// <param name="GroupByHavingExpressions">Like {expr1,expr2}</param> /// <param name="OrderBy">Like {{field}...} or {{field,ASC|DESC}...}</param> /// <param name="Limit">Like null, new int[]{}, new int{a number} (rows), or pair of numbers new int[]{offset,rows}</param> /// <param name="LastFlags">this includes PROCEDURE, FOR UPDATE and LOCK IN SHARE MODE</param> public MySQLSelectCommand( MySQLConnection conn, // Database connection string PrevFlags, // select flags like SQL_CACHE or SQL_BUFFER_RESULT or DISTINCT string[,] FieldsAndAlias, // fields like {{"f1"},{"f2"}} or {{"f1","aliasf1"},...} string[] FieldsTitles, string[] Tables, // Table list object[,] WhereValues, // like {{field,op,value}...} string[,] WhereExpressions, // Like {{expr1,op,expr2}} string[] GroupBy, // Like {"field "+"ASC"|"DESC"}... object[,] GroupByHavingValues, // like {{field,op,value}...} string[,] GroupByHavingExpressions, // Like {{expr1,op,expr2}} string[] OrderBy, // Like {"field "+"ASC"|"DESC"}... int[] Limit, // Like null, empty, a number(rows), or pair of numbers(offset,rows) string LastFlags // this includes PROCEDURE, FOR UPDATE and LOCK IN SHARE MODE ) { string query = PrevFlags; int n; string[] Fields; if (FieldsTitles != null) { Fields = FieldsTitles; } else { Fields = new string[FieldsAndAlias.GetLength(0)]; for (n = 0; n < Fields.Length; n++) { Fields[n] = FieldsAndAlias[n, 0]; } } // FieldsAndAlias if (true) { for (int i = 0; i < FieldsAndAlias.GetLength(0); i++) { query += " " + FieldsAndAlias[i, 0]; if (FieldsAndAlias[i, 1] != null) { query += " AS " + FieldsAndAlias[i, 1]; } } } // Tables if (true) { query += " FROM"; for (n = 0; n < Tables.Length; n++) { query += " " + conn.Database + "." + Tables[n].ToString(); if (n != (Tables.Length - 1)) { query += ","; } } } // Where query += WhereQueryPart(WhereValues, WhereExpressions, "WHERE", conn); // Group by if (true) { if (GroupBy != null) { for (n = 0; n < GroupBy.Length; n++) { query += " " + GroupBy[n].ToString(); if (n != (GroupBy.Length - 1)) { query += ","; } } // Having query += WhereQueryPart(GroupByHavingValues, GroupByHavingExpressions, "HAVING", conn); } } // Order by if (OrderBy != null) { for (n = 0; n < OrderBy.Length; n++) { query += " " + OrderBy[n].ToString(); if (n != (OrderBy.Length - 1)) { query += ","; } } } // Limit if (Limit != null) { if (Limit.GetLength(0) == 1) { query += " LIMIT " + Limit[0]; } else { query += " LIMIT " + Limit[0] + "," + Limit[1]; } } query += " " + LastFlags; Exec(query, conn, Fields); }
/// Modified by Claudia Murialdo (07/24/04) in order to support /// culture-independent format of numeric values in a stmt. internal static string WhereQueryPart(object[,] WhereParamFields, string[,] WhereNoparamFields, string prefix, MySQLConnection conn) { string query = ""; int n; bool Anything = false; if ((WhereParamFields != null) && (WhereParamFields.Length != 0)) { Anything = true; n = WhereParamFields.GetLength(0); for (int m = 0; m < WhereParamFields.GetLength(0); m++) { n--; string Field = WhereParamFields[m, 0] as string; string Operand = WhereParamFields[m, 1] as string; object Value = WhereParamFields[m, 2]; if (Value == null) { query += " " + Field + " " + Operand + " NULL "; } else if ((Value.GetType() == typeof(int)) || (Value.GetType() == typeof(long)) || (Value.GetType() == typeof(short)) || (Value.GetType() == typeof(decimal)) || (Value.GetType() == typeof(float)) || (Value.GetType() == typeof(double))) { query += " " + Field + " " + Operand + " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " "; } else { query += " " + Field + " " + Operand + " " + MySQLUtils.Escape(Value.ToString(), conn) + " "; } if (n != 0) { query += " AND "; } } } if ((WhereNoparamFields != null) && (WhereNoparamFields.Length != 0)) { if (Anything) { query += " AND "; } Anything = true; n = WhereNoparamFields.GetLength(0); for (int m = 0; m < WhereNoparamFields.GetLength(0); m++) { n--; string Field = WhereNoparamFields[m, 0] as string; string Operand = WhereNoparamFields[m, 1] as string; object Value = WhereNoparamFields[m, 2]; if (Value == null) { query += " " + Field + " " + Operand + " NULL "; } else { query += " " + Field + " " + Operand + " " + Value.ToString() + " "; } if (n != 0) { query += " AND "; } } } if (Anything) { query = " " + prefix + " " + query; } return(query); }
private void Exec(string query, MySQLConnection conn, string[] Fields) { MySQLCommand command = new MySQLCommand(query, conn); //GDBUtils.WhereCommandPart(ref command,WhereParamFields); command.Prepare(); DataTable table; BindVarQueryStatement reader = null; // Execute query -> try { reader = (BindVarQueryStatement)command.ExecuteReader(/*CommandBehavior.SequentialAccess*/); // Get results -> table = new DataTable(null); for (int n = 0; n < reader.FieldCount; n++) { System.Data.DataColumn dc = new DataColumn(reader.GetName(n)); String s = dc.ToString(); System.Data.DataColumn dcNew = new System.Data.DataColumn(s); table.Columns.Add(dcNew); } while (reader.Read()) { if (reader.IsClosed) { throw new MySqlException("Reader is closed."); } DataRow row = table.NewRow(); // Bugfixed by Yann Sénécheau 2002-10-28 -> // for(n=0;n<Fields.Length;n++) for (int n = 0; n < reader.FieldCount; n++) // <- Bugfixed by Yann Sénécheau 2002-10-28 { if (n < reader.FieldCount) { if (reader.GetValue(n) == null) { row[n] = null; } else if (reader.GetFieldType(n) == typeof(string)) { row[n] = reader.GetString(n).Clone(); } else { row[n] = reader.GetValue(n); } } else { break; } } table.Rows.Add(row); } // <- Get results } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } finally { if (reader != null) { reader.Close(); } command.Dispose(); } // <- Execute query Table = table; }
internal void DoSelect( MySQLConnection conn, // Database connection string[] Fields, // Fields to show (also admit expressions) string[] Tables, // Tables object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing) string[,] WhereNoparamFields, // Tripplets of parameters, operand and expressions (or other fileds for joining) to match (null - nothing) string[,] Sorting, // Pairs of sortings and its kind of sorting EJ: Priority, ASC (sorts ascending by priority) bool useLimits, int limitStartRow, int limitRows, bool Distinct ) { string query = "SELECT "; int n; // Text Building -> if (Distinct) { query += "DISTINCT "; } for (n = 0; n < Fields.Length; n++) { query += " " + Fields[n].ToString(); if (n != (Fields.Length - 1)) { query += ","; } } if (Tables != null) { query += " FROM "; } else { Tables = new string[0]; } for (n = 0; n < Tables.Length; n++) { query += " " + conn.Database + "." + Tables[n].ToString(); if (n != (Tables.Length - 1)) { query += ","; } } query += WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn); if ((Sorting != null) && (Sorting.Length != 0)) { query += " ORDER BY "; n = Sorting.Length / 2; for (int m = 0; m < (Sorting.Length / 2); m++) { n--; query += " " + Sorting[m, 0].ToString() + " " + Sorting[m, 1].ToString(); if (n != 0) { query += ","; } } } if (useLimits) { query += " LIMIT " + limitStartRow.ToString() + "," + limitRows.ToString(); } // <- Text Building Exec(query, conn, Fields); }
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query and a MySQLConnection.</summary> /// <param name="cmdText"></param> /// <param name="connection"></param> public MySQLCommand(string cmdText, MySQLConnection connection) { CommandText = cmdText; this._connection = connection; }
public InternalNativeTracer(MySQLConnection mySqlConnection) { _mySqlConnection = mySqlConnection; }
// Update by Omar del Valle Rodríguez ([email protected]) // In order support CommandBehavior.CloseConnection internal LiteralQueryStatement(IntPtr resultPtr, MySQLConnection connection, bool closeConnection) { _nativeResult = new NativeResult(resultPtr); // Add by Omar del Valle Rodríguez ([email protected]) // Save if close connection after close MySQLDataReader m_CloseConnection = closeConnection; this._connection = connection; dt = new DataTable(); uint i; ulong j; uint num_fields = _nativeResult.mysql_num_fields(); ulong num_rows = _nativeResult.mysql_num_rows(); List <QueryFieldDescription> fields = new List <QueryFieldDescription>(); for (i = 0; i < num_fields; i++) { var field = new MYSQL_FIELD(); var ptr = _nativeResult.mysql_fetch_field_direct(i); Marshal.PtrToStructure(ptr, field); dt.Columns.Add(field.Name); //Type ftype = MySqlField.MysqltoNetType(field); var mySqlField = new QueryFieldDescription(field.Name, (enum_field_types)field.Type, field.MaxLength, field.Length, field.Flags); if (mySqlField.FieldType != null) { dt.Columns[field.Name].DataType = mySqlField.FieldType; if (dt.Columns[field.Name].DataType == System.Type.GetType("System.Byte[]")) { dt.Columns[field.Name].ExtendedProperties.Add("max_length", (int)field.MaxLength); } } fields.Add(mySqlField); } for (j = 0; j < num_rows; j++) { IntPtr myrow = _nativeResult.mysql_fetch_row(); if (myrow == IntPtr.Zero) { throw new MySqlException(connection.NativeConnection); } DataRow dr = dt.NewRow(); /* for BLOB support * "Christophe Ravier" <*****@*****.**> 2003-11-27*/ var lengths = _nativeResult.mysql_fetch_lengths((int)num_fields); for (i = 0; i < num_fields; i++) { QueryFieldDescription queryFieldDescription = fields[(int)i]; IntPtr ptr = Marshal.ReadIntPtr(myrow, (int)i * Marshal.SizeOf <IntPtr>()); if (ptr == IntPtr.Zero) { dr[(int)i] = Convert.DBNull; } else if (queryFieldDescription.FieldType == typeof(bool)) { dr[(int)i] = GetValidString(ptr) != "0"; } else if (queryFieldDescription.FieldType == typeof(byte[])) { int length = (int)lengths[i]; var val = new byte[length]; Marshal.Copy(ptr, val, 0, length); dr[(int)i] = val; } else if (queryFieldDescription.FieldType == typeof(short)) { dr[(int)i] = Convert.ToInt16(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(ushort)) { dr[(int)i] = Convert.ToUInt16(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(byte)) { dr[(int)i] = Convert.ToByte(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(sbyte)) { dr[(int)i] = Convert.ToSByte(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(decimal)) { dr[(int)i] = Convert.ToDecimal(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(double)) { dr[(int)i] = Convert.ToDouble(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(float)) { dr[(int)i] = Convert.ToSingle(GetValidString(ptr), CultureInfo.InvariantCulture.NumberFormat); } else if (queryFieldDescription.FieldType == typeof(int)) { dr[(int)i] = Convert.ToInt32(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(uint)) { dr[(int)i] = Convert.ToUInt32(GetValidString(ptr)); } else if (queryFieldDescription.FieldType == typeof(long)) { dr[(int)i] = Convert.ToInt64(GetValidString(ptr)); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_GEOMETRY) { // TODO: returns empty string string val = GetValidString(ptr); dr[(int)i] = val; } else if (queryFieldDescription.FieldType == typeof(ulong)) { if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_BIT) { { int byteCount; if (queryFieldDescription.Length <= 8) { byteCount = 1; } else if (queryFieldDescription.Length <= 16) { byteCount = 2; } else if (queryFieldDescription.Length <= 24) { byteCount = 3; } else if (queryFieldDescription.Length <= 32) { byteCount = 4; } else if (queryFieldDescription.Length <= 32 + 8) { byteCount = 5; } else if (queryFieldDescription.Length <= 32 + 16) { byteCount = 6; } else if (queryFieldDescription.Length <= 32 + 24) { byteCount = 7; } else { byteCount = 8; } var buffer = new byte[sizeof(long)]; for (int l = 0; l < byteCount; l++) { buffer[l] = Marshal.ReadByte(ptr, l); } dr[(int)i] = BitConverter.ToInt64(buffer, 0); } } else { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { dr[(int)i] = Convert.ToUInt64(val); } } } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATE || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATE) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATETIME || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_DATETIME2 || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIMESTAMP || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIMESTAMP2) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIME || queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_TIME2) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact("0001-01-01 " + val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.Type == enum_field_types.MYSQL_TYPE_YEAR) { string val = GetValidString(ptr); dr[(int)i] = DateTime.ParseExact(val, "yyyy", CultureInfo.InvariantCulture.DateTimeFormat); } else if (queryFieldDescription.FieldType == typeof(DateTime)) { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { DateTimeFormatInfo format = new DateTimeFormatInfo(); if (val.Length > 20) { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture.DateTimeFormat); } else { if (val.Length > 10) { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture.DateTimeFormat); } else { dr[(int)i] = DateTime.ParseExact(val, "yyyy-MM-dd", CultureInfo.InvariantCulture.DateTimeFormat); } } } } else if (queryFieldDescription.FieldType == typeof(string)) { string val = GetValidString(ptr); if (val == null) { dr[(int)i] = Convert.DBNull; } else { dr[(int)i] = val; } } else { throw new Exception(); } } dt.Rows.Add(dr); } }
public BindVarStatement(MySQLConnection connection, string query, MySQLParameterCollection parameterCollection, uint?fetchSize, CursorType cursorType) { Stmt = connection.NativeConnection.CreateStatement(); if (fetchSize != null) { var ptr = Marshal.AllocHGlobal(Marshal.SizeOf(fetchSize.Value)); Marshal.StructureToPtr(fetchSize.Value, ptr, false); var code1 = Stmt.mysql_stmt_attr_set(StmtAttrTypes.STMT_ATTR_PREFETCH_ROWS, ptr); Marshal.FreeHGlobal(ptr); if (code1 != 0) { throw new MySqlException(Stmt); } } if (cursorType != CursorType.NoCursor) { var value = (enum_cursor_type)cursorType; var ptr = Marshal.AllocHGlobal(Marshal.SizeOf(value)); Marshal.StructureToPtr(value, ptr, false); var code1 = Stmt.mysql_stmt_attr_set(StmtAttrTypes.STMT_ATTR_CURSOR_TYPE, ptr); Marshal.FreeHGlobal(ptr); if (code1 != 0) { throw new MySqlException(Stmt); } } // execute prepare to call mysql_stmt_param_count if (Stmt.mysql_stmt_prepare(query, query.Length) != 0) { throw new MySqlException(Stmt); } // This function will not deliver a valid result until mysql_stmt_prepare() was called. var paramCount = (int)Stmt.mysql_stmt_param_count(); if (paramCount != parameterCollection.Count) { throw new MySqlException($"Invalid parameters, stmt parameters:{paramCount} parameters count:{parameterCollection.Count}"); } // alloc & set binds _bindParams = new MYSQL_BIND[parameterCollection.Count]; for (var i = 0; i < parameterCollection.Count; i++) { _bindParams[i] = new MYSQL_BIND(); var param = parameterCollection[i]; var bindInput = param.GetBindInput(); if (bindInput != null) { _bindParams[i].buffer_type = bindInput.BufferType; if (bindInput.Buffer == null) { _bindParams[i].ResetBuffer(); } else { _bindParams[i].SetBuffer(bindInput.Buffer); } if (bindInput.IsNull.HasValue) { _bindParams[i].SetIsNull(bindInput.IsNull.Value); } else { _bindParams[i].ResetIsNull(); } if (bindInput.Length.HasValue) { _bindParams[i].SetLength((uint)bindInput.Length.Value); } else { _bindParams[i].ResetLength(); } _bindParams[i].is_unsigned = (byte)(bindInput.IsUnsigned ? 1 : 0); } else { throw new NotSupportedException(); } } // link binds to stmt if (Stmt.mysql_stmt_bind_param(_bindParams) != 0) { throw new MySqlException(Stmt); } }
/// <summary> /// Construct and Run a simple insert command. /// </summary> /// <param name="conn">Database connection</param> /// <param name="FieldsAndValues">Pairs of Fields and values as an object array. Examples: /// <code> /// new object[,]{{"SettingID",SettingID},{"SettingValue",Value}} /// /// new object[,]{{"SettingID","times"},{"SettingValue",100}} /// /// null /// </code> /// </param> /// <param name="Table">Table name</param> /// <example>Example: /// <code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// new MySQLInsertCommand( /// DBConn, /// new object[,] { /// {"SettingID",SettingID}, /// {"SettingValue",Value} /// }, /// "Settings" /// ); /// ... /// DBConn.Close(); /// </code></example> /// Modified by Claudia Murialdo (07/24/04) in order to support time /// component of the datetime values and culture-independent /// format of numeric values in a stmt. public MySQLInsertCommand( MySQLConnection conn, object[,] FieldsAndValues, string Table ) { if (FieldsAndValues == null) { throw new MySqlException("FieldsAndValues is null."); } if (FieldsAndValues.GetLength(0) == 0) { throw new MySqlException("FieldsAndValues is empty."); } string query = "insert into " + Table + " ( "; int m; for (m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; query += " " + Field.ToString() + " "; if (m != (FieldsAndValues.GetLength(0) - 1)) { query += " , "; } } query += " ) VALUES ( "; for (m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; object Value = FieldsAndValues[m, 1]; if (Value == null) { query += " NULL "; } else if ((Value.GetType() == typeof(int)) || (Value.GetType() == typeof(long)) || (Value.GetType() == typeof(short)) || (Value.GetType() == typeof(decimal)) || (Value.GetType() == typeof(float)) || (Value.GetType() == typeof(double))) { query += " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " "; } else if (Value.GetType() == typeof(bool)) { bool bValue = (bool)Value; string str = (bValue)? "1" : "0"; query += " " + str + " "; } else if (Value.GetType() == typeof(DateTime)) { DateTime dt = (DateTime)Value; query += " \"" + dt.Year.ToString("D4") + "-" + dt.Month.ToString("D2") + "-" + dt.Day.ToString("D2") + " " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + ((dt.Millisecond > 0)? "." + dt.Millisecond.ToString("D3") : "") + "\" "; } else { query += " " + MySQLUtils.Escape(Value.ToString(), conn) + " "; } if (m != (FieldsAndValues.GetLength(0) - 1)) { query += " , "; } } query += ") ;"; MySQLCommand command = new MySQLCommand(query, conn); command.Prepare(); // Execute query -> try { command.ExecuteNonQuery(); } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } // <- Execute query command.Dispose(); Query = query; bSuccess = true; }
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query, a MySQLConnection, and the IDbTransaction. /// </summary> /// <param name="cmdText"></param> /// <param name="connection"></param> /// <param name="transaction"></param> public MySQLCommand(string cmdText, MySQLConnection connection, MySQLTransaction transaction) : this(cmdText, connection) { _transaction = transaction; }
/// <summary> /// Constructor from MySQLCommand and MySQLConnection /// </summary> /// <param name="strCommand"></param> /// <param name="conn"></param> public MySQLDataAdapter(String strCommand, MySQLConnection conn) { m_selectCommand = new MySQLCommand(strCommand, conn); }
/// <summary> /// A shortcut to make a simple update command with where part. (where would be null) /// </summary> /// <param name="conn">Database connection</param> /// <param name="FieldsAndValues">Pairs of Fields and values as an object array. Examples: /// <code> /// new object[,]{{"SettingID",SettingID},{"SettingValue",Value}} /// /// new object[,]{{"SettingID","times"},{"SettingValue",100}} /// /// null /// </code> /// </param> /// <param name="Table">Table name</param> /// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}} /// /// new object[,]{{"myfield","=",100}} /// /// new object[,]{{"myfield","=",myvalue}} /// /// null /// </code> /// </param> /// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=","myfield2"}} /// /// null /// </code> /// </param> /// <example>Example updating SettingValue by SettingID in Settings table<code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// new MySQLUpdateCommand(DBConn, /// new object[,] {{"SettingValue",Value}}, /// "Settings", /// new object[,] {{"SettingID","=",SettingID}}, /// null /// ); /// ... /// DBConn.Close(); /// </code></example> /// Modified by Claudia Murialdo (07/24/04) in order to support time /// component of the datetime values. public MySQLUpdateCommand( MySQLConnection conn, // Database connection object[,] FieldsAndValues, // Pairs of Fields and values string Table, // Table object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing) string[,] WhereNoparamFields // Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing) ) { if (FieldsAndValues == null) { throw new MySqlException("FieldsAndValues is null."); } if (FieldsAndValues.GetLength(0) == 0) { throw new MySqlException("FieldsAndValues is empty."); } string query = "update " + conn.Database + "." + Table + " set "; ArrayList NewValues = new ArrayList(); for (int m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; object Value = FieldsAndValues[m, 1]; if (Value == null) { query += " " + Field + "=NULL "; } else if (Value.GetType() == typeof(DateTime)) { DateTime dt = (DateTime)Value; query += " " + Field + "=\"" + dt.Year.ToString("D4") + "-" + dt.Month.ToString("D2") + "-" + dt.Day.ToString("D2") + " " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + ((dt.Millisecond > 0) ? "." + dt.Millisecond.ToString("D3") : "") + "\" "; } else if (Value.GetType() == typeof(bool)) { bool bValue = (bool)Value; string str = (bValue)? "1" : "0"; query += " " + Field + "=" + str + " "; } else if (Value.GetType() == typeof(string)) { string str = Value as string; query += " " + Field + "=" + MySQLUtils.Escape(str, conn) + " "; } else if (Value.GetType() == typeof(int)) { query += " " + Field + "=" + ((int)Value).ToString() + " "; } if (m != (FieldsAndValues.GetLength(0) - 1)) { query += ", "; } } query += MySQLSelectCommand.WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn) + " "; MySQLCommand command = new MySQLCommand(query, conn); command.Prepare(); // Execute query -> try { command.ExecuteNonQuery(); } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } // <- Execute query command.Dispose(); Query = query; bSuccess = true; }
/// <summary>Initializes a new instance of the MySQLCommand class with the text of the query, a MySQLConnection, and the IDbTransaction. /// </summary> /// <param name="cmdText"></param> /// <param name="connection"></param> /// <param name="transaction"></param> /// <param name="usePreparedStatement"></param> public MySQLCommand(string cmdText, MySQLConnection connection, MySQLTransaction transaction, bool usePreparedStatement) : this(cmdText, connection, transaction) { this.UsePreparedStatement = usePreparedStatement; }