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())
{
}
}
}