public static MySqlCommand LogError(
Guid id,
string appName,
string hostName,
string typeName,
string source,
string message,
string user,
int statusCode,
DateTime time,
string xml)
{
MySqlCommand command = new MySqlCommand("elmah_LogError");
command.CommandType = CommandType.StoredProcedure;
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("ErrorId", MySqlDbType.String, 36).Value = id.ToString();
parameters.Add("Application", MySqlDbType.VarChar, _maxAppNameLength).Value = appName.Substring(0, Math.Min(_maxAppNameLength, appName.Length));
parameters.Add("Host", MySqlDbType.VarChar, 30).Value = hostName.Substring(0, Math.Min(30, hostName.Length));
parameters.Add("Type", MySqlDbType.VarChar, 100).Value = typeName.Substring(0, Math.Min(100, typeName.Length));
parameters.Add("Source", MySqlDbType.VarChar, 60).Value = source.Substring(0, Math.Min(60, source.Length));
parameters.Add("Message", MySqlDbType.VarChar, 500).Value = message.Substring(0, Math.Min(500, message.Length));
parameters.Add("User", MySqlDbType.VarChar, 50).Value = user.Substring(0, Math.Min(50, user.Length));
parameters.Add("AllXml", MySqlDbType.Text).Value = xml;
parameters.Add("StatusCode", MySqlDbType.Int32).Value = statusCode;
parameters.Add("TimeUtc", MySqlDbType.Datetime).Value = time;
return(command);
}
public void Bug16307()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (OrgNum int auto_increment, CallReportNum int, Stamp varchar(50), " +
"WasRealCall varchar(50), WasHangup varchar(50), primary key(orgnum))");
string strSQL = "INSERT INTO Test(OrgNum, CallReportNum, Stamp, WasRealCall, WasHangup) " +
"VALUES (?OrgNum, ?CallReportNum, ?Stamp, ?WasRealCall, ?WasHangup)";
MySqlCommand cmd = new MySqlCommand(strSQL, conn);
MySqlParameterCollection pc = cmd.Parameters;
pc.Add("?OrgNum", MySqlDbType.Int32, 0, "OrgNum");
pc.Add("?CallReportNum", MySqlDbType.Int32, 0, "CallReportNum");
pc.Add("?Stamp", MySqlDbType.VarChar, 0, "Stamp");
pc.Add("?WasRealCall", MySqlDbType.VarChar, 0, "WasRealCall");
pc.Add("?WasHangup", MySqlDbType.VarChar, 0, "WasHangup");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
da.InsertCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
DataRow row = ds.Tables[0].NewRow();
row["CallReportNum"] = 1;
row["Stamp"] = "stamp";
row["WasRealCall"] = "yes";
row["WasHangup"] = "no";
ds.Tables[0].Rows.Add(row);
da.Update(ds.Tables[0]);
strSQL = "SELECT @@IDENTITY AS 'Identity';";
MySqlCommand cmd2 = new MySqlCommand(strSQL, conn);
MySqlDataReader reader = null;
try
{
reader = cmd2.ExecuteReader();
reader.Read();
int intCallNum = Int32.Parse(reader.GetValue(0).ToString());
Assert.AreEqual(1, intCallNum);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// Inserts Or Update Existing Account Into your current Login Server MySql DataBase.
/// </summary>
/// <param name="account">Your Account Which you want Insert(If Not Exist) Or Update(If Exist)</param>
public static void InsertOrUpdate(Account account)
{
using (MySqlConnection con = new MySqlConnection(Settings.Default.DataBaseConnectionString))
{
try
{
con.Open();
MySqlCommand command;
if (m_DbAccounts.Contains(account))
{
command = new MySqlCommand(
"UPDATE `accounts` SET `accountid` = @accountid, `name` = @name, `token` = @token, `mainaccess` = @mainaccess," +
" `useraccess` = @useraccess, `last_ip` = @lastip, `last_online` = @lastonline, `cookie` = @cookie, `characters` = @characters" +
" WHERE `accountid` = @aid",
con);
//command.Parameters.Add("@accountid", MySqlDbType.UInt32).Value = account.AccountId;
}
else
{
command = new MySqlCommand(
"INSERT INTO `accounts`(accountid, name, token, mainaccess, useraccess, last_ip, last_online, characters, cookie)" +
"VALUES(@accountid, @name, @token, @mainaccess, @useraccess, @lastip, @lastonline, @characters, @cookie)",
con);
//command.Parameters.Add("@accountid", MySqlDbType.UInt32).Value = Program.AccountUid.Next(); //incr index key
}
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("@accountid", MySqlDbType.String).Value = account.AccountId;
parameters.Add("@name", MySqlDbType.String).Value = account.Name;
parameters.Add("@token", MySqlDbType.String).Value = account.Token;
parameters.Add("@mainaccess", MySqlDbType.Byte).Value = account.AccessLevel;
parameters.Add("@useraccess", MySqlDbType.Byte).Value = account.Membership;
parameters.Add("@lastip", MySqlDbType.String).Value = account.LastIp;
parameters.Add("@lastonline", MySqlDbType.Int64).Value = account.LastEnteredTime;
parameters.Add("@characters", MySqlDbType.Byte).Value = account.Characters;
parameters.Add("@cookie", MySqlDbType.Int32).Value = account.Session;
if (m_DbAccounts.Contains(account))
{
parameters.Add("@aid", MySqlDbType.UInt32).Value = account.AccountId;
}
command.ExecuteNonQuery();
command.Dispose();
}
catch (Exception e)
{
Logger.Trace("Cannot InsertOrUpdate template for " + account.Name + ": {0}", e);
}
finally
{
con.Close();
m_DbAccounts.Add(account);
}
}
}
/// <summary>
/// Inserts Or Update Existing Character Into your current Login Server MySql DataBase.
/// </summary>
/// <param name="character">Your Character Which you want Insert(If Not Exist) Or Update(If Exist)</param>
public static void InsertOrUpdate(Character character)
{
using (MySqlConnection con = new MySqlConnection(Settings.Default.DataBaseConnectionString))
{
try
{
con.Open();
MySqlCommand command;
if (m_DbCharacters.Contains(character))
{
command = new MySqlCommand(
"UPDATE `character_records` SET `characterid` = @characterid, `accountid` = @accountid, `worldid` = @worldid, `type` = @type, `charname` = @charname," +
" `charrace` = @charrace, `chargender` = @chargender, `guid` = @guid, `v` = @v" +
"WHERE `acharname` = @charname",
con);
//command.Parameters.Add("@characterid", MySqlDbType.UInt32).Value = character.CharacterId;
}
else
{
command = new MySqlCommand(
"INSERT INTO `character_records`(characterid, accountid, worldid, type, type, charname, charrace, chargender, guid, v)" +
"VALUES(@characterid, @accountid, @worldid, @type, @type, @charname, @charrace, @chargender, @guid, @v)",
con);
//command.Parameters.Add("@characterid", MySqlDbType.UInt32).Value = Program.CharcterUid.Next(); //incr index key
}
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("@accountid", MySqlDbType.String).Value = character.CharacterId;
parameters.Add("@accountid", MySqlDbType.UInt32).Value = character.AccountId;
parameters.Add("@worldid", MySqlDbType.Byte).Value = character.WorldId;
parameters.Add("@type", MySqlDbType.Int32).Value = character.Type;
parameters.Add("@charname", MySqlDbType.String).Value = character.CharName;
parameters.Add("@charrace", MySqlDbType.Byte).Value = character.CharRace;
parameters.Add("@chargender", MySqlDbType.Byte).Value = character.CharGender;
parameters.Add("@token", MySqlDbType.String).Value = character.GUID;
parameters.Add("@v", MySqlDbType.Int64).Value = character.V;
if (m_DbCharacters.Contains(character))
{
parameters.Add("@acharname", MySqlDbType.String).Value = character.CharName;
}
command.ExecuteNonQuery();
command.Dispose();
}
catch (Exception e)
{
Logger.Trace("Cannot InsertOrUpdate template for " + character.CharName + ": {0}", e);
}
finally
{
con.Close();
m_DbCharacters.Add(character);
}
}
}
public static string KeyToWhere(IDataStoreKey key, MySqlParameterCollection parameters)
{
string where = null;
if (key is CounterDataStoreKey)
{
where = "T1.COUNTER = ?CTR";
var par = new MySqlParameter();
par.ParameterName = "?CTR";
par.Value = ((CounterDataStoreKey)key).Counter;
parameters.Add(par);
}
else
if (key is GDID)
{
where = "T1.GDID = ?CTR";
var par = new MySqlParameter();
par.ParameterName = "?CTR";
par.Value = key;
parameters.Add(par);
}
else
if (key is NameValueDataStoreKey)
{
var dict = key as NameValueDataStoreKey;
var s = new StringBuilder();
var idx = 0;
foreach (var e in dict)
{
s.AppendFormat(" (T1.`{0}` = ?P{1}) AND", e.Key, idx);
var par = new MySqlParameter();
par.ParameterName = "?P" + idx.ToString();
par.Value = e.Value;
parameters.Add(par);
idx++;
}
if (s.Length > 0)
{
s.Remove(s.Length - 3, 3); //cut "AND"
}
where = s.ToString();
}
else
{
throw new MySQLDataAccessException(StringConsts.INVALID_KEY_TYPE_ERROR);
}
return(where);
}
public static MySqlCommand GetErrorXml(string appName, Guid id)
{
MySqlCommand command = new MySqlCommand("elmah_GetErrorXml");
command.CommandType = CommandType.StoredProcedure;
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("Id", MySqlDbType.String, 36).Value = id.ToString();
parameters.Add("App", MySqlDbType.VarChar, _maxAppNameLength).Value = appName.Substring(0, Math.Min(_maxAppNameLength, appName.Length));
return(command);
}
public static MySqlCommand GetErrorsXml(string appName, int pageIndex, int pageSize)
{
MySqlCommand command = new MySqlCommand("elmah_GetErrorsXml");
command.CommandType = CommandType.StoredProcedure;
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("App", MySqlDbType.VarChar, _maxAppNameLength).Value = appName.Substring(0, Math.Min(_maxAppNameLength, appName.Length));
parameters.Add("PageIndex", MySqlDbType.Int32).Value = pageIndex;
parameters.Add("PageSize", MySqlDbType.Int32).Value = pageSize;
parameters.Add("TotalCount", MySqlDbType.Int32).Direction = ParameterDirection.Output;
return(command);
}
/// <summary>
/// Launch request
/// </summary>
/// <param name="request">Write a request only delete, insert, update</param>
/// <param name="Timeout">duration(secondes) of the stop before request</param>
/// <returns>return the line affected </returns>
public int StoredProcedure(string procedureName, List <MySqlParameter> sqlParam, int Timeout, MySqlTransaction transaction = null)
{
int returnline;
using (MySqlConnection connection = this.getConnection())
{
if (connection.ConnectionString == "" && connection.State != ConnectionState.Open)
{
connection.ConnectionString = connectionString;
}
MySqlCommand sqlCmd = new MySqlCommand(procedureName, connection);
sqlCmd.Connection.Open();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = Timeout;
if (transaction != null)
{
sqlCmd.Transaction = transaction;
}
MySqlParameterCollection sqlParameters = (MySqlParameterCollection)sqlCmd.Parameters;
foreach (MySqlParameter item in sqlParam)
{
sqlParameters.Add(item);
}
returnline = sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();
}
return(returnline);
}
internal MySqlParameterCollection AlignParamsWithDb(MySqlParameterCollection parameterCollection)
{
var alignedParams = new MySqlParameterCollection();
var returnParam = parameterCollection?.FirstOrDefault(x => x.Direction == ParameterDirection.ReturnValue);
foreach (var cachedParam in Parameters)
{
MySqlParameter alignParam;
if (cachedParam.Direction == ParameterDirection.ReturnValue)
{
alignParam = returnParam ?? throw new InvalidOperationException($"Attempt to call stored function {FullyQualified} without specifying a return parameter");
}
else
{
var index = parameterCollection?.NormalizedIndexOf(cachedParam.Name) ?? -1;
alignParam = index >= 0 ? parameterCollection[index] : throw new ArgumentException($"Parameter '{cachedParam.Name}' not found in the collection.");
}
if (!alignParam.HasSetDirection)
{
alignParam.Direction = cachedParam.Direction;
}
if (!alignParam.HasSetDbType)
{
alignParam.MySqlDbType = cachedParam.MySqlDbType;
}
// cached parameters are oredered by ordinal position
alignedParams.Add(alignParam);
}
return(alignedParams);
}
static internal DataSet GetDataParameters(string SQL, string mySqlConnection, MySqlParameter[] Params, ref string ErrorMsg)
{
MySqlConnection myConnection = new MySqlConnection(mySqlConnection);
MySqlDataAdapter myCommand = new MySqlDataAdapter(SQL, myConnection);
DataSet ds = new DataSet();
try
{
MySqlParameterCollection pc = myCommand.SelectCommand.Parameters;
foreach (MySqlParameter param in Params)
{
pc.Add(param);
}
myCommand.Fill(ds, SQL);
return(ds);
}
catch (Exception DBerror)
{
ErrorMsg += HTMLtextMore(QueryError, DBerror.Message);
return(null);
}
finally
{
myCommand.Dispose();
myConnection.Close();
}
}
public DataSet StoredProcesureSelectDataSet(string procedureName, List <MySqlParameter> sqlParam, int Timeout = 0, MySqlTransaction transaction = null)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
using (MySqlConnection connection = this.getConnection())
{
if (connection.ConnectionString == "" && connection.State != ConnectionState.Open)
{
connection.ConnectionString = connectionString;
}
MySqlCommand sqlCmd = new MySqlCommand(procedureName, connection);
sqlCmd.Connection.Open();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = Timeout;
if (transaction != null)
{
sqlCmd.Transaction = transaction;
}
MySqlParameterCollection sqlParameters = (MySqlParameterCollection)sqlCmd.Parameters;
foreach (MySqlParameter item in sqlParam)
{
sqlParameters.Add(item);
}
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = sqlCmd;
sda.Fill(ds);
sda.Dispose();
sqlCmd.Connection.Close();
}
return(ds);
}
public void ReturnDerivedTypes()
{
using MySqlTransaction transaction = m_database.Connection.BeginTransaction();
using MySqlCommand command = m_database.Connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = "select @param + @param2";
MySqlParameter parameter = command.CreateParameter();
parameter.ParameterName = "param";
parameter.Value = 1;
MySqlParameterCollection parameterCollection = command.Parameters;
parameterCollection.Add(parameter);
MySqlParameter parameter2 = parameterCollection.AddWithValue("param2", 2);
MySqlParameter parameterB = parameterCollection[0];
Assert.Same(parameter, parameterB);
MySqlParameter parameter2B = parameterCollection["param2"];
Assert.Same(parameter2, parameter2B);
using (MySqlDataReader reader = command.ExecuteReader())
{
Assert.True(reader.Read());
Assert.Equal(3L, Convert.ToInt64(reader.GetValue(0)));
Assert.False(reader.Read());
}
transaction.Rollback();
}
public static void Concatener(this MySqlParameterCollection CollBase, MySqlParameterCollection CollConcat)
{
foreach (MySqlParameter Item in CollConcat)
{
CollBase.Add(Item);
}
}
/// <summary>
/// Inserts Or Update Existing Account Into your current Login Server MySql DataBase.
/// </summary>
/// <param name="account">Your Account Which you want Insert(If Not Exist) Or Update(If Exist)</param>
public static void InsertOrUpdate(Account account)
{
MySqlConnection con = new MySqlConnection(Settings.Default.DataBaseConnectionString);
try
{
con.Open();
MySqlCommand command = null;
if (m_DbAccounts.Contains(account))
{
command = new MySqlCommand("UPDATE `accounts` SET `id` = @id, `name` = @name, `mainaccess` = @mainaccess, `useraccess` = @useraccess, `last_ip` = @lastip, `password` = @password, `token` = @token, `last_online` = @lastonline, `characters` = @characters WHERE `id` = @aid", con);
}
else
{
command = new MySqlCommand("INSERT INTO `accounts`(id, name, mainaccess, useraccess, last_ip, password, last_online, characters) VALUES(@id, @name, @mainaccess, @useraccess, @lastip, @password, @lastonline, @characters)", con);
}
MySqlParameterCollection parameters = command.Parameters;
parameters.Add("@id", MySqlDbType.Int32).Value = account.AccountId;
parameters.Add("@name", MySqlDbType.String).Value = account.Name;
parameters.Add("@mainaccess", MySqlDbType.Byte).Value = account.AccessLevel;
parameters.Add("@useraccess", MySqlDbType.Byte).Value = account.Membership;
parameters.Add("@lastip", MySqlDbType.String).Value = account.LastIp;
parameters.Add("@password", MySqlDbType.String).Value = account.Token;
parameters.Add("@token", MySqlDbType.String).Value = account.Password;
parameters.Add("@lastonline", MySqlDbType.Int64).Value = account.LastEnteredTime;
if (m_DbAccounts.Contains(account))
{
parameters.Add("@aid", MySqlDbType.Int32).Value = account.AccountId;
}
parameters.Add("@characters", MySqlDbType.Int32).Value = account.Characters;
command.ExecuteNonQuery();
command = null;
}
finally
{
m_DbAccounts.Add(account);
con.Close();
con = null;
}
}
public static void AddWithValue(this MySqlParameterCollection collection, string paramterName, object value)
{
var para = new MySqlParameter()
{
Value = value,
ParameterName = paramterName
};
var index = collection.Add(para);
}
public static void AddWithValue(this MySqlParameterCollection collection, string parameterName, string value)
{
MySqlParameter parameter = new MySqlParameter
{
Value = value,
ParameterName = parameterName,
DbType = DbType.String
};
collection.Add(parameter);
}
public static MySqlParameterCollection AddWithValue(
this MySqlParameterCollection parameters,
string parameterName,
MySqlDbType dbType,
object value)
{
var parameter = new MySqlParameter(parameterName, dbType);
parameter.Value = value;
parameters.Add(parameter);
parameter.ResetDbType();
return(parameters);
}
/// <summary>
/// 将Request里的参数转成SqlParameter[]
/// </summary>
/// <returns></returns>
internal static void RequestParasToSqlParameters(MySqlParameterCollection oldParas)
{
var oldParaList = oldParas.Cast <MySqlParameter>().ToList();
var paraDictionarAll = SqlSugarTool.GetParameterDictionary();
if (paraDictionarAll != null && paraDictionarAll.Count() > 0)
{
foreach (KeyValuePair <string, string> it in paraDictionarAll)
{
var par = new MySqlParameter("@" + it.Key, it.Value);
if (!oldParaList.Any(oldPara => oldPara.ParameterName == ("@" + it.Key)))
{
oldParas.Add(par);
}
}
}
}
public void EnumParametersAreParsedCorrectly(MySqlDbType?type, object value, string replacedValue)
{
const string sql = "SELECT @param;";
var parameters = new MySqlParameterCollection();
var parameter = new MySqlParameter("@param", value);
if (type is not null)
{
parameter.MySqlDbType = type.Value;
}
parameters.Add(parameter);
var parsedSql = GetParsedSql(sql, parameters);
Assert.Equal(sql.Replace("@param", replacedValue), parsedSql);
}
public DataTable RequestSelect(string request, List <MySqlParameter> pms = null, int Timeout = 0, MySqlTransaction transaction = null)
{
DataTable dt = new DataTable();
using (MySqlConnection connection = this.getConnection())
{
if (connection.ConnectionString == "" && connection.State != ConnectionState.Open)
{
connection.ConnectionString = connectionString;
}
MySqlCommand sqlCmd = new MySqlCommand(request, connection);
sqlCmd.Connection.Open();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = Timeout;
if (transaction != null)
{
sqlCmd.Transaction = transaction;
}
//Ajouter des parametres
if (pms != null)
{
MySqlParameterCollection sqlParameters = (MySqlParameterCollection)sqlCmd.Parameters;
foreach (MySqlParameter item in pms)
{
sqlParameters.Add(item);
}
}
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = sqlCmd;
sda.Fill(dt);
sda.Dispose();
sqlCmd.Connection.Close();
}
return(dt);
}
/// <summary>
/// 生成一个Insert语句
/// </summary>
/// <returns></returns>
public static MySqlCommand CreateInsert(object value, string table)
{
if (value == null || string.IsNullOrEmpty(table))
{
throw new ArgumentException();
}
string sql = "INSERT INTO {0}({1}) VALUES({2})";
MySqlCommand cmd = new MySqlCommand();
MySqlParameterCollection args = cmd.Parameters;
PropertyInfo[] props = (value.GetType()).GetProperties();
int len = props.Length - 1;
string fileds = null, values = null;
for (int i = 0; i <= len; i++)
{
PropertyInfo prop = props[i];
if (i >= len)
{
values += ("@" + prop.Name);
fileds += string.Format("[{0}]", prop.Name);
}
else
{
values += string.Format("@{0},", prop.Name);
fileds += string.Format("[{0}],", prop.Name);
}
object val = prop.GetValue(value, null);
if (val == null)
{
val = DBNull.Value;
}
args.Add(new MySqlParameter(string.Format("@{0}", prop.Name), val));
}
sql = string.Format(sql, table, fileds, values);
cmd.CommandText = sql;
return(cmd);
}
/// <summary>
/// 创建更新执行语句(动态)
/// </summary>
/// <param name="value">映射的对象</param>
/// <param name="table">表</param>
/// <param name="key">主键</param>
/// <param name="where">条件</param>
/// <returns></returns>
public static MySqlCommand CreateUpdate(object value, string table, string key, string where)
{
if (value == null || string.IsNullOrEmpty(table) || string.IsNullOrEmpty(key))
{
throw new ArgumentException();
}
string when = string.Empty, sql = string.Format("UPDATE {0} SET", table);
MySqlCommand cmd = new MySqlCommand();
MySqlParameterCollection args = cmd.Parameters;
PropertyInfo[] props = (value.GetType()).GetProperties();
int len = props.Length - 1;
for (int i = 0; i <= len; i++)
{
PropertyInfo prop = props[i];
if (prop.Name != key)
{
sql += string.Format(i >= len ? "[{0}]=@{1}" : " [{0}]=@{1},", prop.Name, prop.Name);
}
else
{
when += string.Format(" WHERE {0}=@{1}", key, key);
}
object val = prop.GetValue(value, null);
if (val == null)
{
val = DBNull.Value;
}
args.Add(new MySqlParameter(string.Format("@{0}", prop.Name), val));
}
if (!string.IsNullOrEmpty(where))
{
when += string.Format(" AND {0} ", where);
}
cmd.CommandText = (sql += when);
return(cmd);
}
public static void addParameter(string parameterName, object objValue)
{
parameterObj.Add(new MySqlParameter(parameterName, objValue));
}
public void AdicionarParametros(string nomeParametro, object valorParametro)
{
sqlParameterCollection.Add(new MySqlParameter(nomeParametro, valorParametro));
}
private void WriteStoredProcedure(IMySqlCommand command, IDictionary <string, CachedProcedure> cachedProcedures, ByteBufferWriter writer)
{
var parameterCollection = command.Parameters;
var cachedProcedure = cachedProcedures[command.CommandText];
if (cachedProcedure is object)
{
parameterCollection = cachedProcedure.AlignParamsWithDb(parameterCollection);
}
MySqlParameter returnParameter = null;
var outParameters = new MySqlParameterCollection();
var outParameterNames = new List <string>();
var inParameters = new MySqlParameterCollection();
var argParameterNames = new List <string>();
var inOutSetParameters = "";
for (var i = 0; i < (parameterCollection?.Count ?? 0); i++)
{
var param = parameterCollection[i];
var inName = "@inParam" + i;
var outName = "@outParam" + i;
switch (param.Direction)
{
case ParameterDirection.Input:
case ParameterDirection.InputOutput:
var inParam = param.WithParameterName(inName);
inParameters.Add(inParam);
if (param.Direction == ParameterDirection.InputOutput)
{
inOutSetParameters += $"SET {outName}={inName}; ";
goto case ParameterDirection.Output;
}
argParameterNames.Add(inName);
break;
case ParameterDirection.Output:
outParameters.Add(param);
outParameterNames.Add(outName);
argParameterNames.Add(outName);
break;
case ParameterDirection.ReturnValue:
returnParameter = param;
break;
}
}
// if a return param is set, assume it is a function; otherwise, assume stored procedure
var commandText = command.CommandText + "(" + string.Join(", ", argParameterNames) + ");";
if (returnParameter is null)
{
commandText = inOutSetParameters + "CALL " + commandText;
if (outParameters.Count > 0)
{
commandText += "SELECT '" + OutParameterSentinelColumnName + "' AS '" + OutParameterSentinelColumnName + "', " + string.Join(", ", outParameterNames);
}
}
else
{
commandText = "SELECT " + commandText;
}
command.OutParameters = outParameters;
command.ReturnParameter = returnParameter;
var preparer = new StatementPreparer(commandText, inParameters, command.CreateStatementPreparerOptions());
preparer.ParseAndBindParameters(writer);
}
public static void AdicionarParametros(string strNomeParametro, object objValor)
{
objParametros.Add(new MySqlParameter(strNomeParametro, objValor));
}
public override async Task <DbDataReader> ExecuteReaderAsync(string commandText, MySqlParameterCollection parameterCollection,
CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken)
{
var cachedProcedure = await m_command.Connection.GetCachedProcedure(ioBehavior, commandText, cancellationToken).ConfigureAwait(false);
if (cachedProcedure != null)
{
parameterCollection = cachedProcedure.AlignParamsWithDb(parameterCollection);
}
MySqlParameter returnParam = null;
m_outParams = new MySqlParameterCollection();
m_outParamNames = new List <string>();
var inParams = new MySqlParameterCollection();
var argParamNames = new List <string>();
var inOutSetParams = "";
for (var i = 0; i < parameterCollection.Count; i++)
{
var param = parameterCollection[i];
var inName = "@inParam" + i;
var outName = "@outParam" + i;
switch (param.Direction)
{
case ParameterDirection.Input:
case ParameterDirection.InputOutput:
var inParam = param.WithParameterName(inName);
inParams.Add(inParam);
if (param.Direction == ParameterDirection.InputOutput)
{
inOutSetParams += $"SET {outName}={inName}; ";
goto case ParameterDirection.Output;
}
argParamNames.Add(inName);
break;
case ParameterDirection.Output:
m_outParams.Add(param);
m_outParamNames.Add(outName);
argParamNames.Add(outName);
break;
case ParameterDirection.ReturnValue:
returnParam = param;
break;
}
}
// if a return param is set, assume it is a funciton. otherwise, assume stored procedure
commandText += "(" + string.Join(", ", argParamNames) + ")";
if (returnParam == null)
{
commandText = inOutSetParams + "CALL " + commandText;
if (m_outParams.Count > 0)
{
m_setParamsFlags = true;
m_cancellationToken = cancellationToken;
}
}
else
{
commandText = "SELECT " + commandText;
}
var reader = (MySqlDataReader)await base.ExecuteReaderAsync(commandText, inParams, behavior, ioBehavior, cancellationToken).ConfigureAwait(false);
try
{
if (returnParam != null && await reader.ReadAsync(ioBehavior, cancellationToken).ConfigureAwait(false))
{
returnParam.Value = reader.GetValue(0);
}
return(reader);
}
catch (Exception)
{
reader.Dispose();
throw;
}
}
void AddInsertOrUpdateParams( MySqlParameterCollection paramCollection ) {
paramCollection.Add( "Name", MySqlType.VarChar, NameSize );
paramCollection.Add( "DisplayedName", MySqlType.VarChar, DisplayedNameSize );
paramCollection.Add( "LastSeen", DateType );
paramCollection.Add( "Rank", MySqlType.SmallInt );
paramCollection.Add( "PreviousRank", MySqlType.SmallInt );
paramCollection.Add( "RankChangeType", MySqlType.TinyInt );
paramCollection.Add( "RankChangeDate", DateType );
paramCollection.Add( "RankChangedBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "RankChangeReason", MySqlType.VarChar, ReasonFieldSize );
paramCollection.Add( "BanStatus", MySqlType.TinyInt );
paramCollection.Add( "BanDate", DateType );
paramCollection.Add( "BannedBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "BanReason", MySqlType.VarChar, ReasonFieldSize );
paramCollection.Add( "BannedUntil", DateType );
paramCollection.Add( "LastFailedLoginDate", DateType );
paramCollection.Add( "LastFailedLoginIP", MySqlType.Int );
paramCollection.Add( "UnbanDate", DateType );
paramCollection.Add( "UnbannedBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "UnbanReason", MySqlType.VarChar, ReasonFieldSize );
paramCollection.Add( "FirstLoginDate", DateType );
paramCollection.Add( "LastLoginDate", DateType );
paramCollection.Add( "TotalTime", MySqlType.Int );
paramCollection.Add( "BlocksBuilt", MySqlType.Int );
paramCollection.Add( "BlocksDeleted", MySqlType.Int );
paramCollection.Add( "BlocksDrawn", MySqlType.BigInt );
paramCollection.Add( "TimesVisited", MySqlType.Int );
paramCollection.Add( "MessagesWritten", MySqlType.Int );
paramCollection.Add( "TimesKickedOthers", MySqlType.Int );
paramCollection.Add( "TimesBannedOthers", MySqlType.Int );
paramCollection.Add( "TimesKicked", MySqlType.Int );
paramCollection.Add( "LastKickDate", DateType );
paramCollection.Add( "LastKickBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "LastKickReason", MySqlType.VarChar, ReasonFieldSize );
paramCollection.Add( "IsFrozen", MySqlType.TinyInt, 1 );
paramCollection.Add( "FrozenOn", DateType );
paramCollection.Add( "FrozenBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "MutedUntil", DateType );
paramCollection.Add( "MutedBy", MySqlType.VarChar, ByFieldSize );
paramCollection.Add( "Password", MySqlType.VarChar, PasswordFieldSize );
paramCollection.Add( "LastModified", DateType );
paramCollection.Add( "IsOnline", MySqlType.TinyInt, 1 );
paramCollection.Add( "IsHidden", MySqlType.TinyInt, 1 );
paramCollection.Add( "LastIP", MySqlType.Int );
paramCollection.Add( "LeaveReason", MySqlType.TinyInt );
paramCollection.Add( "BandwidthUseMode", MySqlType.TinyInt );
}
public void AddParametrosMySql(string nomeParametro, object valorParametros)
{
mySqlParameterCollection.Add(new MySqlParameter(nomeParametro, valorParametros));
}
/// <summary>
/// Inserts Or Update Existing Character Into your current Login Server MySql DataBase.
/// </summary>
/// <param name="character">Your Character Which you want Insert(If Not Exist) Or Update(If Exist)</param>
public static List <Character> InsertOrUpdate(Character character)
{
using (var conn = new MySqlConnection(Settings.Default.DataBaseConnectionString))
{
try
{
conn.Open(); //Устанавливаем соединение с базой данных
var cmd = new MySqlCommand();
cmd.Connection = conn;
if (_mDbCharacters.Contains(character))
{
cmd.CommandText =
"UPDATE `character_records` SET `characterid` = @characterid, `accountid` = @accountid, `chargender` = @chargender, `charname` = @charname," +
" `charrace` = @charrace, `decor` = @decor, `ext` = @ext, `eyebrow` = @eyebrow, `guid` = @guid, `leftPupil` = @leftPupil, `level` = @level," +
" `lip` = @lip, `modifiers` = @modifiers, `movex` = @movex, `movey` = @movey, `rightpupil` = @rightpupil, `rotate` = @rotate," +
" `scale` = @scale, `type0` = @type0, `type1` = @type1, `type2` = @type2, `type3` = @type3, `type4` = @type4, `type5` = @type5," +
" `type6` = @type6, `type7` = @type7, `type8` = @type8, `type9` = @type9, `type10` = @type10, `type11` = @type11, `type12` = @type12," +
" `type13` = @type13, `type14` = @type14, `type15` = @type15, `type16` = @type16, `type17` = @type17, `v` = @v, `Weight0` = @Weight0," +
" `Weight1` = @Weight1, `Weight2` = @Weight2, `Weight3` = @Weight3, `Weight4` = @Weight4, `Weight5` = @Weight5, `Weight6` = @Weight6," +
" `Weight7` = @Weight7, `Weight8` = @Weight8, `Weight9` = @Weight9, `Weight10` = @Weight10, `Weight11` = @Weight11, `Weight12` = @Weight12," +
" `Weight13` = @Weight13, `Weight14` = @Weight14, `Weight15` = @Weight15, `Weight16` = @Weight16, `Weight17` = @Weight17," +
" `worldid` = @worldid, `ability0` = @ability0, `ability1` = @ability1, `ability2` = @ability2," +
" `zone` = @zone, `x` = @x, `y` = @y, `z` = @z, `rotx` = rotx, `roty` = roty, `rotz` = rotz " +
" WHERE `charname` = @charname";
}
else
{
cmd.CommandText =
@"INSERT INTO `character_records` (characterid, accountid, chargender, charname, charrace, decor, ext, eyebrow, guid, leftPupil, level, lip, modifiers," +
" movex, movey, rightpupil, rotate, scale, type0, type1, type2, type3, type4, type5, type6, type7, type8, type9, type10, type11, type12, type13," +
" type14, type15, type16, type17, v, Weight0, Weight1, Weight2, Weight3, Weight4, Weight5, Weight6, Weight7, Weight8, Weight9, Weight10," +
" Weight11, Weight12, Weight13, Weight14, Weight15, Weight16, Weight17, Worldid, ability0, ability1, ability2, zone," +
" x, y, z, rotx, roty, rotz) " +
" VALUES (@characterid, @accountid, @chargender, @charname, @charrace, @decor, @ext, @eyebrow, @guid, @leftPupil, @level, @lip, @modifiers, @movex, @movey," +
" @rightpupil, @rotate, @scale, @type0, @type1, @type2, @type3, @type4, @type5, @type6, @type7, @type8, @type9, @type10, @type11, @type12," +
" @type13, @type14, @type15, @type16, @type17, @v, @Weight0, @Weight1, @Weight2, @Weight3, @Weight4, @Weight5, @Weight6, @Weight7, @Weight8," +
" @Weight9, @Weight10, @Weight11, @Weight12, @Weight13, @Weight14, @Weight15, @Weight16, @Weight17, @Worldid, @ability0, @ability1, @ability2," +
" @zone, @x, @y, @z, @rotx, @roty, @rotz)";
}
MySqlParameterCollection parameters = cmd.Parameters;
parameters.Add("@characterid", MySqlDbType.UInt32).Value = character.CharacterId;
parameters.Add("@accountid", MySqlDbType.UInt32).Value = character.AccountId;
parameters.Add("@chargender", MySqlDbType.Byte).Value = character.CharGender;
parameters.Add("@charname", MySqlDbType.String).Value = character.CharName;
parameters.Add("@charrace", MySqlDbType.Byte).Value = character.CharRace;
parameters.Add("@decor", MySqlDbType.Int32).Value = character.Decor;
parameters.Add("@ext", MySqlDbType.Int32).Value = character.Ext;
parameters.Add("@eyebrow", MySqlDbType.Int32).Value = character.Eyebrow;
parameters.Add("@guid", MySqlDbType.String).Value = character.Guid;
parameters.Add("@leftPupil", MySqlDbType.Int32).Value = character.LeftPupil;
parameters.Add("@level", MySqlDbType.Byte).Value = character.Level;
parameters.Add("@lip", MySqlDbType.Int32).Value = character.Lip;
parameters.Add("@modifiers", MySqlDbType.String).Value = character.Modifiers;
parameters.Add("@movex", MySqlDbType.Float).Value = character.MoveX;
parameters.Add("@movey", MySqlDbType.Float).Value = character.MoveY;
parameters.Add("@rightpupil", MySqlDbType.Int32).Value = character.RightPupil;
parameters.Add("@rotate", MySqlDbType.Float).Value = character.Rotate;
parameters.Add("@scale", MySqlDbType.Float).Value = character.Scale;
parameters.Add("@type0", MySqlDbType.Int32).Value = character.Type[0];
parameters.Add("@type1", MySqlDbType.Int32).Value = character.Type[1];
parameters.Add("@type2", MySqlDbType.Int32).Value = character.Type[2];
parameters.Add("@type3", MySqlDbType.Int32).Value = character.Type[3];
parameters.Add("@type4", MySqlDbType.Int32).Value = character.Type[4];
parameters.Add("@type5", MySqlDbType.Int32).Value = character.Type[5];
parameters.Add("@type6", MySqlDbType.Int32).Value = character.Type[6];
parameters.Add("@type7", MySqlDbType.Int32).Value = character.Type[7];
parameters.Add("@type8", MySqlDbType.Int32).Value = character.Type[8];
parameters.Add("@type9", MySqlDbType.Int32).Value = character.Type[9];
parameters.Add("@type10", MySqlDbType.Int32).Value = character.Type[10];
parameters.Add("@type11", MySqlDbType.Int32).Value = character.Type[11];
parameters.Add("@type12", MySqlDbType.Int32).Value = character.Type[12];
parameters.Add("@type13", MySqlDbType.Int32).Value = character.Type[13];
parameters.Add("@type14", MySqlDbType.Int32).Value = character.Type[14];
parameters.Add("@type15", MySqlDbType.Int32).Value = character.Type[15];
parameters.Add("@type16", MySqlDbType.Int32).Value = character.Type[16];
parameters.Add("@type17", MySqlDbType.Int32).Value = character.Type[17];
parameters.Add("@v", MySqlDbType.Int64).Value = character.V;
parameters.Add("@Weight0", MySqlDbType.Float).Value = character.Weight[0];
parameters.Add("@Weight1", MySqlDbType.Float).Value = character.Weight[1];
parameters.Add("@Weight2", MySqlDbType.Float).Value = character.Weight[2];
parameters.Add("@Weight3", MySqlDbType.Float).Value = character.Weight[3];
parameters.Add("@Weight4", MySqlDbType.Float).Value = character.Weight[4];
parameters.Add("@Weight5", MySqlDbType.Float).Value = character.Weight[5];
parameters.Add("@Weight6", MySqlDbType.Float).Value = character.Weight[6];
parameters.Add("@Weight7", MySqlDbType.Float).Value = character.Weight[7];
parameters.Add("@Weight8", MySqlDbType.Float).Value = character.Weight[8];
parameters.Add("@Weight9", MySqlDbType.Float).Value = character.Weight[9];
parameters.Add("@Weight10", MySqlDbType.Float).Value = character.Weight[10];
parameters.Add("@Weight11", MySqlDbType.Float).Value = character.Weight[11];
parameters.Add("@Weight12", MySqlDbType.Float).Value = character.Weight[12];
parameters.Add("@Weight13", MySqlDbType.Float).Value = character.Weight[13];
parameters.Add("@Weight14", MySqlDbType.Float).Value = character.Weight[14];
parameters.Add("@Weight15", MySqlDbType.Float).Value = character.Weight[15];
parameters.Add("@Weight16", MySqlDbType.Float).Value = character.Weight[16];
parameters.Add("@Weight17", MySqlDbType.Float).Value = character.Weight[17];
parameters.Add("@worldid", MySqlDbType.Byte).Value = Settings.Default.Game_Id; //character.WorldId;
parameters.Add("@ability0", MySqlDbType.Byte).Value = character.Ability[0];
parameters.Add("@ability1", MySqlDbType.Byte).Value = character.Ability[1];
parameters.Add("@ability2", MySqlDbType.Byte).Value = character.Ability[2];
parameters.Add("@zone", MySqlDbType.Int32).Value = character.MapId;
parameters.Add("@x", MySqlDbType.Float).Value = character.Position.X;
parameters.Add("@y", MySqlDbType.Float).Value = character.Position.Y;
parameters.Add("@z", MySqlDbType.Float).Value = character.Position.Z;
parameters.Add("@rotx", MySqlDbType.Int32).Value = character.Heading.X;
parameters.Add("@roty", MySqlDbType.Int32).Value = character.Heading.Y;
parameters.Add("@rotz", MySqlDbType.Int32).Value = character.Heading.Z;
//parameters.Add("@liveobjectid", MySqlDbType.Int32).Value = character.LiveObjectId;
//parameters.Add("@x", MySqlDbType.Float).Value = character.Position.X;
//parameters.Add("@y", MySqlDbType.Float).Value = character.Position.Y;
//parameters.Add("@z", MySqlDbType.Float).Value = character.Position.Z;
//parameters.Add("@exp", MySqlDbType.Int32).Value = character.Exp;
//parameters.Add("@maxexp", MySqlDbType.Int32).Value = character.MaxExp;
//parameters.Add("@totalexp", MySqlDbType.Int32).Value = character.TotalExp;
//parameters.Add("@hp", MySqlDbType.Int32).Value = character.Hp;
//parameters.Add("@maxhp", MySqlDbType.Int32).Value = character.MaxHp;
//parameters.Add("@sp", MySqlDbType.Int32).Value = character.Sp;
//parameters.Add("@maxsp", MySqlDbType.Int32).Value = character.MaxSp;
//parameters.Add("@stamina", MySqlDbType.Int32).Value = character.Stamina;
//parameters.Add("@maxstamina", MySqlDbType.Int32).Value = character.MaxStamina;
//parameters.Add("@str", MySqlDbType.Int32).Value = character.Str;
//parameters.Add("@con", MySqlDbType.Int32).Value = character.Con;
//parameters.Add("@int", MySqlDbType.Int32).Value = character.Int;
//parameters.Add("@spr", MySqlDbType.Int32).Value = character.Spr;
//parameters.Add("@dex", MySqlDbType.Int32).Value = character.Dex;
//
_mDbCharacters.Add(character);
if (_mDbCharacters.Contains(character))
{
parameters.Add("@acharname", MySqlDbType.String).Value = character.CharName;
}
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ex)
{
Logger.Trace("Cannot InsertOrUpdate template for " + character.CharName + ": {0}",
ex); // ex.Message - написать так, если нужно только сообщение без указания строки в сурсах
}
finally
{
conn.Close();
}
}
return(CharactersList);
}
/// <summary>
/// Adiciona parâmetros na coleção de parâmetros.
/// </summary>
/// <param name="parameterName">Nome do parâmetro</param>
/// <param name="parameterValue">Valor do parâmetro</param>
public void AddParameters(string parameterName, object parameterValue)
{
mySqlParameterCollection.Add(new MySqlParameter(parameterName, parameterValue));
}