public void AddingParameterPreviouslyRemoved() { MySqlCommand cmd = new MySqlCommand("Insert into sometable(s1, s2) values(?p1, ?p2)"); MySqlParameter param1 = cmd.CreateParameter(); param1.ParameterName = "?p1"; param1.DbType = DbType.String; param1.Value = "Ali Gel"; cmd.Parameters.Add(param1); cmd.Parameters.RemoveAt(0); cmd.Parameters.Add(param1); }
public void AddingParameterPreviouslyRemoved() { executeSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, ts TIMESTAMP, PRIMARY KEY(id))"); MySqlCommand cmd = new MySqlCommand("Insert into sometable(s1, s2) values(?p1, ?p2)"); MySqlParameter param1 = cmd.CreateParameter(); param1.ParameterName = "?p1"; param1.DbType = DbType.String; param1.Value = "Ali Gel"; cmd.Parameters.Add(param1); cmd.Parameters.RemoveAt(0); cmd.Parameters.Add(param1); }
/// <summary> /// Adds a new parameter to the specified command. It is not recommended that /// you use this method directly from your custom code. Instead use the /// <c>AddParameter</c> method of the MySqlHelper classes. /// </summary> /// <param name="cmd">The <see cref="System.Data.IDbCommand"/> object to add the parameter to.</param> /// <param name="paramName">The name of the parameter.</param> /// <param name="value">The value of the parameter.</param> /// <param name="dataType">The DbType of the parameter.</param> /// <returns>A reference to the added parameter.</returns> public IDbDataParameter AddParameter(MySqlCommand cmd, string paramName, object value, DbType dataType) { IDbDataParameter parameter = cmd.CreateParameter(); parameter.ParameterName = CreateCollectionParameterName(paramName); parameter.DbType = dataType; if (value is DateTime) { parameter.Value = (DateTime.MinValue == DateTime.Parse(value.ToString()) ? DBNull.Value : value); } else { parameter.Value = (value ?? DBNull.Value); } cmd.Parameters.Add(parameter); return(parameter); }
public void OutputTimeParameter(bool prepare) { using var connection = CreateOpenConnection(); using var command = new MySqlCommand("GetTime", connection); command.CommandType = CommandType.StoredProcedure; var parameter = command.CreateParameter(); parameter.ParameterName = "OutTime"; parameter.Direction = ParameterDirection.Output; command.Parameters.Add(parameter); if (prepare) { command.Prepare(); } command.ExecuteNonQuery(); Assert.IsType <TimeSpan>(parameter.Value); }
public string ExecuteScalar(string sql, ArrayList opc) { string sRet = ""; if (null == _conn) { _conn = GetDBConnection(); } try { MySqlCommand cmd = new MySqlCommand(sql, _conn); cmd.Parameters.Clear(); if (null != opc) { foreach (DataPara op in opc) { MySqlParameter mp = cmd.CreateParameter(); mp.DbType = op.Type; mp.ParameterName = op.ParameterName; mp.Value = op.Value; cmd.Parameters.Add(mp); } } if (null != _st) { cmd.Transaction = _st; } sRet = cmd.ExecuteScalar().ToString(); } catch { throw; } finally { if (null == _st) { _conn.Close(); _conn.Dispose(); _conn = null; } } return(sRet); }
private void CreateParameter(MySqlCommand sqlCmd, object parmaValue, string strName, MySqlDbType dbType, System.Data.ParameterDirection paramDirect) { MySqlParameter param = null; if (!sqlCmd.Parameters.Contains(strName)) { param = sqlCmd.CreateParameter(); param.ParameterName = strName; sqlCmd.Parameters.Add(param); } else { param = sqlCmd.Parameters[strName]; } param.DbType = (System.Data.DbType)dbType; param.Value = parmaValue; param.Direction = paramDirect; }
public async Task <List <ServerScopeInfo> > GetAllServerScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction) { var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_server"; List <ServerScopeInfo> scopes = new List <ServerScopeInfo>(); var commandText = $@"SELECT sync_scope_name , sync_scope_schema , sync_scope_setup , sync_scope_version , sync_scope_last_clean_timestamp FROM `{tableName}` WHERE sync_scope_name = @sync_scope_name"; using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = scopeName; p.DbType = DbType.String; command.Parameters.Add(p); using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false)) { if (reader.HasRows) { // read only the first one while (reader.Read()) { var scopeInfo = new ServerScopeInfo(); scopeInfo.Name = reader["sync_scope_name"] as String; scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]); scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]); scopeInfo.Version = reader["sync_scope_version"] as string; scopeInfo.LastCleanupTimestamp = reader["sync_scope_last_clean_timestamp"] != DBNull.Value ? (long)reader["sync_scope_last_clean_timestamp"] : 0L; scopes.Add(scopeInfo); } } } return(scopes); } }
public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction) { var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history"; List <ServerHistoryScopeInfo> scopes = new List <ServerHistoryScopeInfo>(); var commandText = $@"SELECT sync_scope_id , sync_scope_name , scope_last_sync_timestamp , scope_last_sync_duration , scope_last_sync FROM `{tableName}` WHERE sync_scope_name = @sync_scope_name"; using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = scopeName; p.DbType = DbType.String; command.Parameters.Add(p); using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false)) { if (reader.HasRows) { // read only the first one while (reader.Read()) { var serverScopeInfo = new ServerHistoryScopeInfo(); serverScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]); serverScopeInfo.Name = reader["sync_scope_name"] as string; serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null; serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0; serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0; scopes.Add(serverScopeInfo); } } } return(scopes); } }
public void ChangeParameterNameAfterAdd() { using var cmd = new MySqlCommand(); using var cmd2 = new MySqlCommand(); var parameter = cmd.CreateParameter(); parameter.ParameterName = "@a"; cmd2.Parameters.Add(parameter); cmd.Parameters.Add(parameter); parameter.ParameterName = "@b"; Assert.Equal(parameter, cmd.Parameters["@b"]); Assert.Throws <ArgumentException>(() => cmd.Parameters["@a"]); // only works for the last collection that contained the parameter Assert.Throws <ArgumentException>(() => cmd2.Parameters["@b"]); Assert.Equal(parameter, cmd2.Parameters["@a"]); }
public static List <DataRow> ExeciteSelect(string StoredProcedure, string[] ParameterName, object[] ParameterValue) { MySqlCommand comm = CreateCommand(); comm.CommandText = StoredProcedure; MySqlParameter param; if (ParameterName.Length > 0 && ParameterValue.Length > 0 && ParameterName.Length == ParameterValue.Length) { for (int i = 0; i < ParameterName.Length; i++) { param = comm.CreateParameter(); param.ParameterName = ParameterName[i]; param.Value = ParameterValue[i]; comm.Parameters.Add(param); } } return(ExecuteSelectCommand(comm)); }
private MySqlCommand GetCommand(MySqlConnection connection, string query, SqlParameter[] parameters = null) { var command = new MySqlCommand(query, connection); command.CommandTimeout = 15 * 60; command.Prepare(); if (parameters != null) { foreach (var parameter in parameters) { var p = command.CreateParameter(); p.ParameterName = parameter.Name; p.Value = parameter.Value; p.DbType = parameter.Type; command.Parameters.Add(p); } } return(command); }
public void CallingStoredFunctionasProcedure() { executeSQL("CREATE FUNCTION fnTest(valin int) RETURNS INT " + " LANGUAGE SQL DETERMINISTIC BEGIN return valin * 2; END"); MySqlCommand cmd = new MySqlCommand("fnTest", Connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?valin", 22); MySqlParameter retVal = cmd.CreateParameter(); retVal.ParameterName = "?retval"; retVal.MySqlDbType = MySqlDbType.Int32; retVal.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retVal); if (prepare) { cmd.Prepare(); } cmd.ExecuteNonQuery(); Assert.Equal(44, cmd.Parameters[1].Value); }
/// <summary> /// PrepareParameter /// </summary> /// <param name="command"></param> /// <param name="parameters"></param> private void PrepareParameter(MySqlCommand command, IList <object> parameters) { if (parameters == null || parameters.Count == 0) { return; } string[] parts = command.CommandText.Split('?'); if (parts.Length - 1 != parameters.Count) { throw new ArgumentOutOfRangeException("参数数量与参数值数量不一致。"); } StringBuilder builder = new StringBuilder(); for (int i = 0; i < parts.Length; i++) { builder.Append(parts[i]); if (i < parts.Length - 1) { string parameterName = string.Format("{0}P{1}", Constants.ParameterPrefix, i); object value = parameters[i]; builder.Append(parameterName); MySqlParameter parameter = command.CreateParameter(); parameter.ParameterName = parameterName; parameter.Value = value == null ? DBNull.Value : value; command.Parameters.Add(parameter); } } command.CommandText = builder.ToString(); }
protected override ICSDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters) { MySqlCommand mySqlCommand = ((CSSqlCommand)Connection.CreateCommand()).Command; if (CurrentTransaction != null) { mySqlCommand.Transaction = ((CSSqlTransaction)CurrentTransaction).Transaction; } if (sqlQuery.StartsWith("!")) { mySqlCommand.CommandType = CommandType.StoredProcedure; mySqlCommand.CommandText = sqlQuery.Substring(1); } else { mySqlCommand.CommandType = CommandType.Text; mySqlCommand.CommandText = sqlQuery; } mySqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", "?${name}"); if (parameters != null && !parameters.IsEmpty) { foreach (CSParameter parameter in parameters) { IDbDataParameter dataParameter = mySqlCommand.CreateParameter(); dataParameter.ParameterName = "?" + parameter.Name.Substring(1); dataParameter.Direction = ParameterDirection.Input; dataParameter.Value = ConvertParameter(parameter.Value); mySqlCommand.Parameters.Add(dataParameter); } } return(new CSSqlCommand(mySqlCommand)); }
private static void AddParameter(MySqlCommand command, DbColumn column, DataRowVersion sourceVersion = DataRowVersion.Default) { MySqlParameter parameter = command.CreateParameter(); parameter.ParameterName = "@" + column.Column; if (column.Type != DbType.Object) { parameter.DbType = column.Type; } if (column.Size > 0) { parameter.Size = column.Size; } parameter.SourceVersion = sourceVersion; parameter.Value = column.Value; command.Parameters.Add(parameter); }
public User GetUserById(int UserId) { using (var connection = new MySqlConnection(connectionstring)) { using (var command = new MySqlCommand()) { command.CommandType = CommandType.Text; command.CommandText = "Select * from User where id='@UserId'"; command.Connection = connection; var parameter = command.CreateParameter(); parameter.ParameterName = "@UserId"; parameter.Value = UserId; command.Parameters.Add(parameter); connection.Open(); using (var reader = command.ExecuteReader()) { var user = new User(); while (reader.Read()) { user.FirstName = (Convert.IsDBNull(reader["FirstName"]) ? "" : Convert.ToString(reader["FirstName"])); } return(user); } } } }
private static void SetParameters(this MySqlCommand command, object[] parms) { if (parms != null && parms.Length > 0) { for (int i = 0; i < parms.Length; i += 2) { string name = parms[i].ToString(); if (parms[i + 1] is string && (string)parms[i + 1] == "") { parms[i + 1] = null; } object value = parms[i + 1] ?? DBNull.Value; var dbParameter = command.CreateParameter(); dbParameter.ParameterName = name; dbParameter.Value = value; command.Parameters.Add(dbParameter); } } }
// 删除指定 id 对应数据 public void Delete(IDType id) { MySqlConnection conn = null; MySqlCommand cmd = null; RunTask(() => { try { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(@"delete from {0} where where ID = {1}", tbName, NamedParam("ID")); MySqlParameter idParam = cmd.CreateParameter(); idParam.ParameterName = RealParam("ID"); idParam.DbType = GetDbType(id, "ID"); idParam.Value = id; cmd.Parameters.Add(idParam); cmd.ExecuteNonQuery(); } } } catch (Exception ex) { Logger.Error("\r\n==========\r\n" + (cmd == null ? "null" : cmd.CommandText) + "\r\n" + ex.Message + "\r\n==========\r\n" + ex.StackTrace + "\r\n==========\r\n"); throw ex; } finally { conn.Close(); } }); }
/** * Given an array of log format ids, this function * will iterate through those and delete the log * formats identified. * * @param[in] format_ids The int[] of log format ids. */ public void delete_log_formats(int[] format_ids) { string sql = "DELETE FROM cuts.log_formats WHERE lfid = ?id"; MySqlCommand command = this.dba_.get_command(sql); // Create the parameter for the command. MySqlParameter p1 = command.CreateParameter(); p1.ParameterName = "?id"; p1.DbType = DbType.Int32; // Insert the parameter into the command. command.Parameters.Add(p1); foreach (int id in format_ids) { // Set the parameter's value. p1.Value = id; // Prepare and execute the command. command.Prepare(); command.ExecuteNonQuery(); } }
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, Dictionary <string, object> cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = cmdType; if (cmdParms != null) { foreach (var param in cmdParms) { var parameter = cmd.CreateParameter(); parameter.ParameterName = param.Key; parameter.Value = param.Value; cmd.Parameters.Add(parameter); } } }
public void UsingUInt64AsParam() { ExecuteSQL(@"CREATE TABLE Test(f1 bigint(20) unsigned NOT NULL, PRIMARY KEY(f1)) ENGINE=InnoDB DEFAULT CHARSET=utf8"); ExecuteSQL(@"CREATE PROCEDURE spTest(in _val bigint unsigned) BEGIN insert into Test set f1=_val; END"); DbCommand cmd = new MySqlCommand(); DbParameter param = cmd.CreateParameter(); param.DbType = DbType.UInt64; cmd.Connection = Connection; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spTest"; param.Direction = ParameterDirection.Input; param.ParameterName = "?_val"; ulong bigval = long.MaxValue; bigval += 1000; param.Value = bigval; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); }
public bool Insert <T>(object[] param) { string sql = "INSERT INTO bet (Stake,Odd,Color,Number,Even,FirstNumber,SecondNumber) VALUES(@Stake,@Odd,@Color,@Number,@Even,@FirstNumber,@SecondNumber)"; var properties = getUnderLyingProperties <T>(); properties = RemoveUnusedProps(sql, properties); try { using (MySqlConnection conn = new MySqlConnection(ConnectionHelper.CnnVal("DemoDB"))) { conn.Open(); using (MySqlCommand cmd = new MySqlCommand(sql, conn)) { for (int i = 0; i < properties.Count; i++) { string propName = properties[i].Name; var parameter = cmd.CreateParameter(); parameter.ParameterName = "@" + propName; parameter.Value = param[i]; cmd.Parameters.Add(parameter); } if (cmd.ExecuteNonQuery() > 0) { return(true); } return(false); } } } catch (Exception ex) { throw new Exception(ex.Message, ex); } }
public void PreparedReader() { ExecuteSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL default '0', " + "val int(10) unsigned default NULL, PRIMARY KEY (id)) " + "ENGINE=InnoDB DEFAULT CHARSET=utf8"); ExecuteSQL("CREATE PROCEDURE spTest (IN pp INTEGER) " + "select * from Test where id > pp "); MySqlCommand c = new MySqlCommand("spTest", Connection); c.CommandType = CommandType.StoredProcedure; IDataParameter p = c.CreateParameter(); p.ParameterName = "?pp"; p.Value = 10; c.Parameters.Add(p); c.Prepare(); using (MySqlDataReader reader = c.ExecuteReader()) { while (reader.Read()) { } } }
/// <summary> /// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。 /// </summary> /// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param> /// <param name="batchSize">每批次写入的数据量。</param> public void BatchInsert(DataTable dataTable, int batchSize = 10000) { if (dataTable == null || dataTable.Rows.Count == 0) { return; } MySqlTransaction transcation = null; try { if (_conn.State != ConnectionState.Open) { _conn.Open(); } transcation = _conn.BeginTransaction(); using (var command = new MySqlCommand()) { command.Connection = _conn; command.Transaction = transcation; command.CommandText = GenerateInserSql(dataTable); if (command.CommandText == string.Empty) { return; } List <string> lstName = new List <string>(); List <string> lstAutoName = new List <string>(); for (int i = 0; i < dataTable.Columns.Count; i++) { if (dataTable.Columns[i].AutoIncrement) { lstAutoName.Add(dataTable.Columns[i].ToString()); } else { lstName.Add(dataTable.Columns[i].ColumnName); } } foreach (string t in lstAutoName) { dataTable.Columns.Remove(t); } MySqlParameter[] paras = new MySqlParameter[lstName.Count]; for (int i = 0; i < paras.Length; i++) { paras[i] = command.CreateParameter(); paras[i].ParameterName = lstName[i]; } command.Parameters.AddRange(paras); for (int i = 0; i < dataTable.Rows.Count; i++) { foreach (MySqlParameter t in paras) { t.Value = dataTable.Rows[i][t.ParameterName].ToString(); } command.ExecuteNonQuery(); } transcation.Commit(); } } catch (Exception) { if (transcation != null) { transcation.Rollback(); } throw; } finally { if (_conn.State != ConnectionState.Closed) { _conn.Close(); } } }
public void CallingStoredFunctionasProcedure() { if (Version < new Version(5, 0)) return; execSQL("CREATE FUNCTION fnTest(valin int) RETURNS INT " + " LANGUAGE SQL DETERMINISTIC BEGIN return valin * 2; END"); MySqlCommand cmd = new MySqlCommand("fnTest", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("?valin", 22); MySqlParameter retVal = cmd.CreateParameter(); retVal.ParameterName = "?retval"; retVal.MySqlDbType = MySqlDbType.Int32; retVal.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retVal); if (prepare) cmd.Prepare(); cmd.ExecuteNonQuery(); Assert.AreEqual(44, cmd.Parameters[1].Value); }
/// <summary> /// The execute a CRUD (select, insert, updaten delete) query /// </summary> /// <typeparam name="T">generic objet</typeparam> /// <param name="req">sql query</param> /// <param name="listOfParameters">contains a result sql query</param> /// <returns>return list of result</returns> public List <T> Execute <T>(string req, List <DbParameter> listOfParameters) { List <T> list = new List <T>(); using (mySqlConnection = new MySqlConnection(connectionString)) { MySqlCommand dbCommand = mySqlConnection.CreateCommand(); dbCommand.CommandText = req; dbCommand.CommandType = CommandType.Text; dbCommand.Connection = mySqlConnection; foreach (DbParameter param in listOfParameters) { DbParameter sqlParameter = dbCommand.CreateParameter(); sqlParameter.ParameterName = param.ParameterName; sqlParameter.Value = param.Value; dbCommand.Parameters.Add(sqlParameter); } mySqlConnection.Open(); var i = 0; if (req.IndexOf("SELECT", StringComparison.Ordinal) == 0) { MySqlDataReader dbDataReader; try { dbDataReader = dbCommand.ExecuteReader(); } catch (Exception e) { throw new DatabaseException($"An error has occured. Reason : {e.Message}"); } T obj = default(T); if (dbDataReader.HasRows) { while (dbDataReader.Read()) { obj = Activator.CreateInstance <T>(); foreach (PropertyInfo prop in obj.GetType().GetProperties()) { if (!dbDataReader[prop.Name].Equals(null)) { prop.SetValue(obj, dbDataReader[prop.Name]); } } list.Add(obj); } dbDataReader.Close(); } } else if (req.IndexOf("INSERT", StringComparison.Ordinal) == 0 || req.IndexOf("UPDATE", StringComparison.Ordinal) == 0 || req.IndexOf("DELETE", StringComparison.Ordinal) == 0) { try { i = dbCommand.ExecuteNonQuery(); } catch (Exception e) { throw new DatabaseException($"An error has occured. Reason : {e.Message}"); } } else { throw new WrongSqlRequestException("Your SQL statement is not a SELECT, INSERT, UPDATE or DELETE statement"); } mySqlConnection.Close(); mySqlConnection.Dispose(); return(list); } }
public async Task <ServerScopeInfo> InsertOrUpdateServerScopeInfoAsync(ServerScopeInfo serverScopeInfo, DbConnection connection, DbTransaction transaction) { bool exist; var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_server"; var commandText = $@"Select count(*) from `{tableName}` where sync_scope_name = @sync_scope_name"; using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = serverScopeInfo.Name; p.DbType = DbType.String; command.Parameters.Add(p); exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0; } string stmtText = exist ? $"Update `{tableName}` set sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, sync_scope_last_clean_timestamp=@sync_scope_last_clean_timestamp where sync_scope_name=@sync_scope_name" : $"Insert into `{tableName}` (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, sync_scope_last_clean_timestamp) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @sync_scope_last_clean_timestamp)"; using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = serverScopeInfo.Name; p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_schema"; p.Value = serverScopeInfo.Schema == null ? DBNull.Value : (object)JsonConvert.SerializeObject(serverScopeInfo.Schema); p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_setup"; p.Value = serverScopeInfo.Setup == null ? DBNull.Value : (object)JsonConvert.SerializeObject(serverScopeInfo.Setup); p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_version"; p.Value = serverScopeInfo.Version; p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_last_clean_timestamp"; p.Value = serverScopeInfo.LastCleanupTimestamp; p.DbType = DbType.Int64; command.Parameters.Add(p); using (DbDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { serverScopeInfo.Name = reader["sync_scope_name"] as string; serverScopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]); serverScopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]); serverScopeInfo.Version = reader["sync_scope_version"] as string; serverScopeInfo.LastCleanupTimestamp = reader["sync_scope_last_clean_timestamp"] != DBNull.Value ? (long)reader["sync_scope_last_clean_timestamp"] : 0L; } } } return(serverScopeInfo); } }
public void UsingUInt64AsParam() { if (Version < new Version(5, 0)) return; execSQL(@"CREATE TABLE Test(f1 bigint(20) unsigned NOT NULL, PRIMARY KEY(f1)) ENGINE=InnoDB DEFAULT CHARSET=utf8"); execSQL(@"CREATE PROCEDURE spTest(in _val bigint unsigned) BEGIN insert into Test set f1=_val; END"); DbCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spTest"; DbParameter param = cmd.CreateParameter(); param.DbType = DbType.UInt64; param.Direction = ParameterDirection.Input; param.ParameterName = "?_val"; ulong bigval = long.MaxValue; bigval += 1000; param.Value = bigval; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); }
public void UnTypedParameterBeingReused() { MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, dt) VALUES (?id, ?dt)", conn); cmd.Parameters.AddWithValue("?id", 1); MySqlParameter p = cmd.CreateParameter(); p.ParameterName = "?dt"; p.Value = DBNull.Value; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 2; p.Value = DateTime.Now; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; cmd.Parameters.Clear(); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); Assert.IsTrue(reader.IsDBNull(2)); reader.Read(); Assert.IsFalse(reader.IsDBNull(2)); Assert.IsFalse(reader.Read()); } }
// 保存数据 public void Update(T it) { string addCols = ""; if (cols != null && cols.Length > 0) { foreach (string c in cols) { object v = cvm(it, c); if (v != null) { addCols += ", " + c + "=" + NamedParam(c); } } } MySqlConnection conn = null; MySqlCommand cmd = null; RunTask(() => { try { using (conn = GetConn()) { using (cmd = conn.CreateCommand()) { var id = it.ID; var buff = data2Buff(it); cmd.CommandText = string.Format(@"update {0} set Data = {1}{2} where ID = {3}", tbName, NamedParam("Data"), addCols, NamedParam("ID")); MySqlParameter idParam = cmd.CreateParameter(); idParam.ParameterName = RealParam("ID"); idParam.DbType = GetDbType(id, "ID"); idParam.Value = id; cmd.Parameters.Add(idParam); MySqlParameter dataParam = cmd.CreateParameter(); dataParam.ParameterName = RealParam("Data"); dataParam.DbType = DbType.Binary; dataParam.Value = buff; cmd.Parameters.Add(dataParam); if (cols != null && cols.Length > 0) { foreach (string c in cols) { object v = cvm(it, c); if (v == null) { continue; } IDbDataParameter p = cmd.CreateParameter(); p.ParameterName = RealParam(c); p.DbType = GetDbType(v, c); p.Value = v; cmd.Parameters.Add(p); } } cmd.ExecuteNonQuery(); } } } catch (Exception ex) { Logger.Error("\r\n==========\r\n" + (cmd == null ? "null" : cmd.CommandText) + "\r\n" + ex.Message + "\r\n==========\r\n" + ex.StackTrace + "\r\n==========\r\n"); throw ex; } finally { if (conn != null) { conn.Close(); } } }); }
public IDbDataParameter CreateParameter() { return(sqlcmd.CreateParameter()); }
public IDbDataParameter CreateParameter(IDbCommand command) { MySqlCommand SQLcommand = (MySqlCommand)command; return(SQLcommand.CreateParameter()); }
public async Task <ScopeInfo> InsertOrUpdateClientScopeInfoAsync(ScopeInfo scopeInfo, DbConnection connection, DbTransaction transaction) { bool exist; var commandText = $@"Select count(*) from {scopeTableName.Quoted().ToString()} where sync_scope_id = @sync_scope_id"; using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_id"; p.Value = scopeInfo.Id.ToString(); p.DbType = DbType.String; command.Parameters.Add(p); exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0; } string stmtText = exist ? $"Update {scopeTableName.Quoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id" : $"Insert into {scopeTableName.Quoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, sync_scope_id, scope_last_server_sync_timestamp, scope_last_sync_timestamp, scope_last_sync_duration) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @sync_scope_id, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @scope_last_sync_duration)"; using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = scopeInfo.Name; p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_schema"; p.Value = scopeInfo.Schema == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Schema); p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_setup"; p.Value = scopeInfo.Setup == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Setup); p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_version"; p.Value = scopeInfo.Version; p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync"; p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value; p.DbType = DbType.DateTime; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync_timestamp"; p.Value = scopeInfo.LastSyncTimestamp; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_server_sync_timestamp"; p.Value = scopeInfo.LastServerSyncTimestamp; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync_duration"; p.Value = scopeInfo.LastSyncDuration; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_id"; p.Value = scopeInfo.Id.ToString(); p.DbType = DbType.String; command.Parameters.Add(p); using (DbDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { scopeInfo.Name = reader["sync_scope_name"] as string; scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]); scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]); scopeInfo.Version = reader["sync_scope_version"] as string; scopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]); scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L; scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_server_sync_timestamp"] : 0L; scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L; scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null; } } } return(scopeInfo); } }
public async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction) { var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history"; var commandText = $@"Select count(*) from `{tableName}` where sync_scope_id = @sync_scope_id"; bool exist; using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_id"; p.Value = serverHistoryScopeInfo.Id; p.DbType = DbType.Guid; command.Parameters.Add(p); exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0; } string stmtText = exist ? $"Update `{tableName}` set sync_scope_name=@sync_scope_name, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync=@scope_last_sync, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id" : $"Insert into `{tableName}` (sync_scope_id, sync_scope_name, scope_last_sync_timestamp, scope_last_sync, scope_last_sync_duration) values (@sync_scope_id, @sync_scope_name, @scope_last_sync_timestamp, @scope_last_sync, @scope_last_sync_duration)"; using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction)) { var p = command.CreateParameter(); p.ParameterName = "@sync_scope_name"; p.Value = serverHistoryScopeInfo.Name; p.DbType = DbType.String; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync_timestamp"; p.Value = serverHistoryScopeInfo.LastSyncTimestamp; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync"; p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value; p.DbType = DbType.DateTime; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@scope_last_sync_duration"; p.Value = serverHistoryScopeInfo.LastSyncDuration; p.DbType = DbType.Int64; command.Parameters.Add(p); p = command.CreateParameter(); p.ParameterName = "@sync_scope_id"; p.Value = serverHistoryScopeInfo.Id; p.DbType = DbType.Guid; command.Parameters.Add(p); using (DbDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { serverHistoryScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]); serverHistoryScopeInfo.Name = reader["sync_scope_name"] as string; serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L; serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L; serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null; } } } return(serverHistoryScopeInfo); } }
public void PreparedReader() { if (Version < new Version(5, 0)) return; execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL default '0', " + "val int(10) unsigned default NULL, PRIMARY KEY (id)) " + "ENGINE=InnoDB DEFAULT CHARSET=utf8"); execSQL("CREATE PROCEDURE spTest (IN pp INTEGER) " + "select * from Test where id > pp "); MySqlCommand c = new MySqlCommand("spTest", conn); c.CommandType = CommandType.StoredProcedure; IDataParameter p = c.CreateParameter(); p.ParameterName = "?pp"; p.Value = 10; c.Parameters.Add(p); c.Prepare(); using (MySqlDataReader reader = c.ExecuteReader()) { while (reader.Read()) { } } }