public static DatabaseModel.ProcedureParameters CreateTestProc(string createProcedureStatement, ConnectionType connectionType)
{
try
{
RemoveTestProc(connectionType);
}
catch { }
try
{
DatabaseHelpers.ExecuteSqlStatement(createProcedureStatement, connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(new DatabaseModel.ProcedureParameters(databaseAssistant.GetStoredProcedureParameters(connection, "Test Proc")));
}
}
catch
{
try
{
RemoveTestProc(connectionType);
}
catch { }
throw;
}
}
public void TestGetResultSetsForStoredProcedureWithDynamicSQL()
{
string createProcedureStatement = GetProcedureWithDynamicSQL(this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets = null;
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("Rowcount_Returned", field.ColumnName);
Assert.AreEqual("Rowcount_Returned", field.OutputName);
}
internal static DbCommand CreateCommand(ConnectionType connectionType, string connectionString, string sql, object[] sqlValues, int timeout)
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
var connection = assistant.CreateConnection(connectionString);
return(CreateCommand(connectionType, connection, sql, sqlValues, timeout));
}
public void TestGetStoredProcedureDoesNotLoadFunction()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
string createFunctionStatement = GetFunctionStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseObjectCreation.CreateTestFunction(createFunctionStatement, this.connectionType);
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
Assert.IsFalse(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("TestFunction")));
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestFunction(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
}
private void TestConnection()
{
IsBusy = true;
Task.Factory.StartNew(() =>
{
try
{
using (IDbConnection connection = DatabaseAssistant.GetDatabaseAssistant(connectionType).CreateConnection(ConnectionString))
{
connection.Open();
}
if (ShowMessage != null)
{
Application.Current.Dispatcher.Invoke(() => ShowMessage(this, new ShowMessageEventArgs("Success!", "Connect to database", MessageBoxButton.OK, MessageBoxImage.Information)));
}
}
catch (Exception exc)
{
if (ShowMessage != null)
{
Application.Current.Dispatcher.Invoke(() => ShowMessage(this, new ShowMessageEventArgs(exc.Message, "Error connecting", MessageBoxButton.OK, MessageBoxImage.Error)));
}
}
finally
{
IsBusy = false;
}
});
}
public void RefreshConnectionParameters(bool retainUnknownParameters = false)
{
foreach (var nextParameter in ConnectionParameters)
{
if (parameterValueCache.ContainsKey(nextParameter.Name.ToLower()))
{
parameterValueCache[nextParameter.Name.ToLower()] = nextParameter.TextValue;
}
else
{
parameterValueCache.Add(nextParameter.Name.ToLower(), nextParameter.TextValue);
}
}
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
var convertedParameters = databaseAssistant.RefreshConnectionParameters(ConnectionParameters.Where(p => !p.IsDefault).ToLookup(p => p.Name, p => p.TextValue), retainUnknownParameters);
ConnectionParameters = new ObservableCollection <ConnectionParameter>(convertedParameters.Select(p => new ConnectionParameter(this, p.Key, p.Value)));
foreach (var nextParameter in ConnectionParameters)
{
if ((parameterValueCache.ContainsKey(nextParameter.Name.ToLower())) && (nextParameter.TextValue == nextParameter.DefaultTextValue))
{
string cachedValue = parameterValueCache[nextParameter.Name.ToLower()];
if (!string.IsNullOrEmpty(cachedValue))
{
nextParameter.TextValue = cachedValue;
}
}
}
}
public static DatabaseModel.Columns CreateTestTable(string createTableStatement, ConnectionType connectionType)
{
try
{
RemoveTestTable(connectionType);
}
catch { }
try
{
DatabaseHelpers.ExecuteSqlStatement(createTableStatement, connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(new DatabaseModel.Columns(databaseAssistant.GetTableColumns(connection, "TESTTABLE")));
}
}
catch
{
try
{
RemoveTestTable(connectionType);
}
catch { }
throw;
}
}
private void MigrateOrg(string schemaName)
{
var connector = new Connector();
var dbAssistant = new DatabaseAssistant();
var migrator = new OrganizationMigrator(connector, dbAssistant, schemaName);
migrator.Migrate();
}
private void MigrateOrg(string schemaName)
{
// TODO AP : Dedupe with OrganizationAdminController
var connector = new Connector();
var dbAssistant = new DatabaseAssistant();
var migrator = new OrganizationMigrator(connector, dbAssistant, schemaName);
migrator.Migrate();
}
public StoredProcedureNodeViewModel(DatabaseAssistant assistant, IDbConnection connection, string procedureName, NodeViewModel parent)
: base(procedureName, parent)
{
AllowDrag = true;
var parameterNames = GetParameterNames(assistant.GetStoredProcedureParameters(connection, procedureName));
ConfigureParameters(procedureName, parameterNames);
}
private static DatabaseModel.ResultSets FetchResultSets(ConnectionType connectionType)
{
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
{
connection.Open();
return(databaseAssistant.GetResultSets(connection, "Test Proc"));
}
}
private ObservableCollection <NodeViewModel> GetDatabaseAssistantObjectsAsync()
{
ObservableCollection <NodeViewModel> databaseObjects = new ObservableCollection <NodeViewModel>();
IDbConnection connection = null;
try
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
connection = assistant.CreateConnection(this.connectionString);
connection.Open();
var tableContainer = new ComplexNodeViewModel("Tables");
databaseObjects.Add(tableContainer);
foreach (var tableName in assistant.GetTableNames(connection).OrderBy(t => t))
{
tableContainer.Children.Add(new TableNodeViewModel(this.connectionType, this.connectionString, tableName, tableContainer, SetSqlAction, true));
}
var viewContainer = new ComplexNodeViewModel("Views");
databaseObjects.Add(viewContainer);
foreach (var viewName in assistant.GetViewNames(connection).OrderBy(v => v))
{
viewContainer.Children.Add(new TableNodeViewModel(this.connectionType, this.connectionString, viewName, viewContainer, null, false));
}
if (this.connectionType != ConnectionType.Oracle)
{
var storedProcedureContainer = new ComplexNodeViewModel("Stored Procedures");
databaseObjects.Add(storedProcedureContainer);
foreach (var spName in assistant.GetStoredProcedureNames(connection).OrderBy(sp => sp))
{
storedProcedureContainer.Children.Add(new StoredProcedureNodeViewModel(assistant, connection, spName, storedProcedureContainer));
}
}
}
catch (Exception exception)
{
databaseObjects.Clear();
databaseObjects.Add(new SimpleNodeViewModel(exception.Message));
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return(databaseObjects);
}
public void TestTableData()
{
var columns = DatabaseObjectCreation.CreateTestTable(this.connectionType);
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetTableNames(connection).Any(t => t.IndexOf("TestTable", StringComparison.InvariantCultureIgnoreCase) != -1));
}
}
finally
{
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(5, columns.Count);
var column = columns[0];
Assert.AreEqual("INTVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Int32, column.DataType);
Assert.IsFalse(column.IsNullable);
column = columns[1];
Assert.AreEqual("DOUBLEVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Double, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[2];
Assert.AreEqual("STRINGVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.String, column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[3];
Assert.AreEqual("DATEVALUE", column.Name.ToUpper());
Assert.AreEqual(this.connectionType == ConnectionType.OleDb ? DatabaseModel.DataType.String : DatabaseModel.DataType.Date,
column.DataType);
Assert.IsTrue(column.IsNullable);
column = columns[4];
Assert.AreEqual("BYTESVALUE", column.Name.ToUpper());
Assert.AreEqual(DatabaseModel.DataType.Binary, column.DataType);
Assert.IsTrue(column.IsNullable);
}
private IDbCommand CreateCommand()
{
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);
if (transaction == null)
{
IDbConnection connection = databaseAssistant.CreateConnection(connectionString);
Log(string.Format("Opening connection <{0}>", connectionString));
connection.Open();
Log("Opened connection.");
return(databaseAssistant.BuildStoredProcedureCommand(connection, storedProcedureName, parameters));
}
else
{
IDbCommand command = databaseAssistant.BuildStoredProcedureCommand(transaction.Connection, storedProcedureName, parameters);
command.Transaction = transaction;
return(command);
}
}
private FunctionResult Execute(DbBulkCopy.ConnectionType connectionType, string connectionString, string tableName)
{
DatabaseModel.Columns columns;
var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(ToCommonConnectionType(connectionType));
using (var connection = databaseAssistant.CreateConnection(connectionString))
{
connection.Open();
columns = new DatabaseModel.Columns(databaseAssistant.GetTableColumns(connection, tableName));
}
var tester = (new FunctionTester <DbBulkCopy.DbBulkCopy>()).Compile(
new PropertyValue(DbBulkCopyShared.ConnectionTypePropertyName, connectionType),
new PropertyValue(DbBulkCopyShared.ColumnsPropertyName, columns),
new PropertyValue(DbBulkCopyShared.TableNamePropertyName, tableName));
return(tester.Execute(
new ParameterValue(DbShared.ConnectionStringPropertyName, connectionString)));
}
public void GetRoleInfo(uint serv_id, uint user_id)
{
STRoleInfo st = new STRoleInfo();
st.err_code = -1;
st.user_id = user_id;
st.role_name = "";
st.app_id = int.Parse(gmt.Global.GetGameAppID);
if (serv_id != 0 && user_id != 0)
{
gmt.STServerInfo server = gmt.Server.GetServerInfo(serv_id);
if (server != null)
{
#region
string sql = string.Format("SELECT t1.uid , uname , team_level FROM USER AS t1 JOIN card_package AS t2 ON t1.uid = t2.uid WHERE t1.cyuid = {0} LIMIT 1", user_id);
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
st.err_code = 1;
st.role_id = reader.GetInt32(0);
st.role_name = reader.GetString(1);
st.role_rank = reader.GetInt32(2);
}
},
server.gamedb.host,
server.gamedb.port.ToString(),
server.gamedb.cset.ToString(),
server.gamedb.name,
server.gamedb.user,
server.gamedb.pwd,
sql
);
#endregion
}
}
string ret = JsonConvert.SerializeObject(st);
this.Context.Response.Write(ret);
this.Context.Response.End();
}
private bool GetCachedTableColumns(out DatabaseModel.Column[] columns, out string error)
{
if (this.cachedTableColumns == null)
{
try
{
var assistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (IDbConnection connection = assistant.CreateConnection(this.connectionString))
{
connection.Open();
this.cachedTableColumns = assistant.GetTableColumns(connection, Text).ToArray();
}
}
catch (Exception exception)
{
this.cachedTableColumns = new DatabaseModel.Column[0];
this.cachedTableColumnsError = exception.Message;
}
}
columns = this.cachedTableColumns;
error = this.cachedTableColumnsError;
return(string.IsNullOrEmpty(this.cachedTableColumnsError));
}
private IFunctionData AddMissingProperties(IFunctionData data)
{
var connectionType = ConnectionType.OleDb;
if (data.Properties[DbShared.ConnectionStringPropertyName].Value is string)
{
connectionType = DatabaseAssistant.DetectConnectionType(data.Properties[DbShared.ConnectionStringPropertyName].Value as string) ?? ConnectionType.OleDb;
}
if (data.FindPropertyById(DbShared.ConnectionTypePropertyName) == null)
{
data = data.AddProperty(new Property(DbShared.ConnectionTypePropertyName, typeof(ConnectionType), ValueUseOption.DesignTime, ConnectionType.SqlServer)
{
Value = connectionType
});
}
if (data.FindPropertyById(DbShared.DesignTimeConnectionTypePropertyName) == null)
{
data = data.AddProperty(new Property(DbShared.DesignTimeConnectionTypePropertyName, typeof(ConnectionType), ValueUseOption.DesignTime, ConnectionType.SqlServer)
{
Value = connectionType,
IsVisible = false
});
}
if (data.FindPropertyById(DbShared.DesignTimeConnectionStringPropertyName) == null)
{
data = data.AddProperty(new Property(DbShared.DesignTimeConnectionStringPropertyName, typeof(string), ValueUseOption.DesignTime, string.Empty)
{
IsVisible = false
});
}
return(data);
}
public void TestGetResultSetForStoredProcedureDataDoesNotRunProcedure()
{
string createProcedureStatement = GetProcedureStatement(this.connectionType);
DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType);
DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType);
DatabaseModel.ResultSets resultSets;
try
{
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType);
Assert.AreEqual(0, results.Rows.Count);
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
DatabaseObjectCreation.RemoveTestTable(this.connectionType);
}
Assert.AreEqual(1, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
}
/// <summary>
/// 查找按钮点击响应
/// </summary>
protected void findButton_Click(object sender, EventArgs e)
{
Server server = Session["Server"] as Server;
if (server == null)
{
this.resultLabel.Text = "错误：无法获取服务器";
return;
}
Dictionary <int, object[]> userDictionary = new Dictionary <int, object[]>();
DatabaseAssistant.Execute
(
reader =>
{
while (reader.Read())
{
int uid = reader.GetInt32(0);
int level = reader.GetInt32(1);
if (level < 3)
{
continue;
}
byte[] buffer = new byte[reader.GetBytes(2, 0, null, 0, int.MaxValue)];
reader.GetBytes(2, 0, buffer, 0, buffer.Length);
using (MemoryStream stream = new MemoryStream(buffer))
{
try
{
mw.OtherInfo othern = ProtoSerializer.Instance.Deserialize(stream, null, typeof(mw.OtherInfo)) as mw.OtherInfo;
if (othern == null ||
othern.roleexInfo == null ||
othern.roleexInfo.user_data == null)
{
continue;
}
string[] dataSet = othern.roleexInfo.user_data.Split(';');
if (dataSet.Length > 10 &&
(dataSet[8] == "0" ||
dataSet[9] == "0" ||
dataSet[10] == "0"))
{
userDictionary.Add(uid, new object[] { reader.GetString(3), level });
}
}
catch (Exception exception)
{
continue;
}
}
}
},
server.DatabaseAddress,
server.DatabasePort,
server.DatabaseCharSet,
server.GameDatabase,
server.DatabaseUserId,
server.DatabasePassword,
"SELECT `other_info`.`uid`,`card_package`.`team_level`,`other_info`.`other_data`,`user`.`uname` FROM `other_info`,`user`,`card_package` WHERE `other_info`.`uid`=`user`.`uid` && `other_info`.`uid`=`card_package`.`uid`;"
);
if (userDictionary.Count == 0)
{
this.resultLabel.Text = "没有找到";
}
else
{
StringBuilder buider = new StringBuilder(string.Format("找到 {0} 个新手引导错误玩家", userDictionary.Count));
foreach (var pair in userDictionary)
{
buider.Append("<br>");
buider.Append(pair.Key);
buider.Append(" ");
buider.Append(pair.Value[0]);
buider.Append(" 等级：");
buider.Append(pair.Value[1]);
}
this.resultLabel.Text = buider.ToString();
}
}
/// <summary>
/// 查找按钮点击响应
/// </summary>
protected void findButton_Click(object sender, EventArgs e)
{
Server server = Session["Server"] as Server;
if (server == null)
{
this.resultLabel.Text = "错误：无法获取服务器";
return;
}
Dictionary <int, object[]> userDictionary = new Dictionary <int, object[]>();
DatabaseAssistant.Execute
(
reader =>
{
while (reader.Read())
{
int uid = reader.GetInt32(0);
int level = reader.GetInt32(1);
if (level < 20)
{
continue;
}
byte[] buffer = new byte[reader.GetBytes(2, 0, null, 0, int.MaxValue)];
reader.GetBytes(2, 0, buffer, 0, buffer.Length);
using (MemoryStream stream = new MemoryStream(buffer))
{
try
{
mw.CardPackage cn = ProtoSerializer.Instance.Deserialize(stream, null, typeof(mw.CardPackage)) as mw.CardPackage;
int type = cn.cardInfos[0].type;
if (type >= 9000 && type <= 9004)
{
userDictionary.Add(uid, new object[] { reader.GetString(3), level });
}
}
catch (Exception exception)
{
continue;
}
}
}
},
server.DatabaseAddress,
server.DatabasePort,
server.DatabaseCharSet,
server.GameDatabase,
server.DatabaseUserId,
server.DatabasePassword,
"SELECT `user`.`uid`,`card_package`.`team_level`,`card_package`.`card_data`,`user`.`uname` FROM `user`,`card_package` WHERE `card_package`.`uid`=`user`.`uid`;"
);
if (userDictionary.Count == 0)
{
this.resultLabel.Text = "没有找到";
}
else
{
StringBuilder buider = new StringBuilder(string.Format("找到 {0} 个进阶卡错误玩家", userDictionary.Count));
foreach (var pair in userDictionary)
{
buider.Append("<br>");
buider.Append(pair.Key);
buider.Append(" ");
buider.Append(pair.Value[0]);
buider.Append(" 等级：");
buider.Append(pair.Value[1]);
}
this.resultLabel.Text = buider.ToString();
}
}
public void TestGetResultSetsForStoredProcedure()
{
var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType).Where(p => (p.Direction != DatabaseModel.ParameterDirection.ReturnValue) && (p.DataType != DatabaseModel.DataType.RefCursor)).ToList();
DatabaseModel.ResultSets resultSets;
DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType);
try
{
using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType)))
{
connection.Open();
Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc")));
resultSets = databaseAssistant.GetResultSets(connection, "Test Proc");
}
}
finally
{
DatabaseObjectCreation.RemoveTestProc(this.connectionType);
}
Assert.AreEqual(8, parameters.Count);
var parameter = parameters[0];
Assert.AreEqual("@IntValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Int32, parameter.DataType);
parameter = parameters[1];
Assert.AreEqual("@DoubleValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Double, parameter.DataType);
parameter = parameters[2];
Assert.AreEqual("@StringValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[3];
Assert.AreEqual("@StringValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(20, parameter.Size);
}
parameter = parameters[4];
Assert.AreEqual("@DateValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[5];
Assert.AreEqual("@DateValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType);
parameter = parameters[6];
Assert.AreEqual("@BytesValue", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
parameter = parameters[7];
Assert.AreEqual("@BytesValueOut", parameter.Name);
Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction);
Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType);
if (this.connectionType != ConnectionType.Oracle)
{
Assert.AreEqual(50, parameter.Size);
}
Assert.AreEqual(2, resultSets.Count);
var fields = resultSets[0].Fields;
Assert.AreEqual(1, fields.Count);
var field = fields[0];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
fields = resultSets[1].Fields;
Assert.AreEqual(2, fields.Count);
field = fields[0];
Assert.AreEqual("Counter", field.ColumnName);
Assert.AreEqual("Counter", field.OutputName);
field = fields[1];
Assert.AreEqual("StringValue", field.ColumnName);
Assert.AreEqual("StringValue", field.OutputName);
Assert.AreEqual(DatabaseModel.DataType.String, field.DataType);
}
/// <summary>
/// 获取玩家数据
/// </summary>
private static void GetPlayerDataFromDB(gmt.Server server)
{
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
PlayerDataInfo pdi = new PlayerDataInfo();
PlayerDataBaseInfo bi = new PlayerDataBaseInfo();
bi.uid = reader.GetUInt32(0);
if (!reader.IsDBNull(1))
{
byte[] buffer = new byte[reader.GetBytes(1, 0, null, 0, int.MaxValue)];
reader.GetBytes(1, 0, buffer, 0, buffer.Length);
using (MemoryStream stream = new MemoryStream(buffer))
{
mw.Role3rdData roledata = ProtoSerializer.Instance.Deserialize(stream, null, typeof(mw.Role3rdData)) as mw.Role3rdData;
bi.team_level = roledata.level;
bi.soulex_level = roledata.soulex_level;
for (int i = 0; i < 6; i++)
{
if (i == roledata.detailInfo.heroInfos.Count)
{
break;
}
#region 英雄战力信息
PlayerDataHeroInfo hi = new PlayerDataHeroInfo();
hi.hero_id = roledata.detailInfo.heroInfos[i].heroInfo.index;
hi.hero_color = roledata.detailInfo.heroInfos[i].heroInfo.color;
hi.hero_star = roledata.detailInfo.heroInfos[i].heroInfo.star;
if (roledata.detailInfo.heroInfos[i].heroInfo.equipInfos.Count > 0)
{
hi.hero_equip_min_level = roledata.detailInfo.heroInfos[i].heroInfo.equipInfos[0].levelex;
for (int j = 0; j < roledata.detailInfo.heroInfos[i].heroInfo.equipInfos.Count; j++)
{
if (roledata.detailInfo.heroInfos[i].heroInfo.equipInfos[j].levelex < hi.hero_equip_min_level)
{
hi.hero_equip_min_level = roledata.detailInfo.heroInfos[i].heroInfo.equipInfos[j].levelex;
}
}
}
if (roledata.detailInfo.heroInfos[i].heroInfo.starArrayInfos.Count > 0)
{
hi.hero_star_min_level = roledata.detailInfo.heroInfos[i].heroInfo.starArrayInfos[0].star_count;
for (int j = 0; j < roledata.detailInfo.heroInfos[i].heroInfo.starArrayInfos.Count; j++)
{
if (roledata.detailInfo.heroInfos[i].heroInfo.starArrayInfos[j].star_count < hi.hero_star_min_level)
{
hi.hero_star_min_level = roledata.detailInfo.heroInfos[i].heroInfo.starArrayInfos[j].star_count;
}
}
}
hi.hero_pet_star = (roledata.lookInfo.heroInfos[i].pet_type - roledata.lookInfo.heroInfos[i].pet_type / 10000 * 10000) / 100;
pdi.heroInfo.Add(hi);
#endregion
#region 助战位信息
PlayerDataFightInfo fi = new PlayerDataFightInfo();
if (roledata.detailInfo.heroInfos[i].fightStoneInfos.Count > 0)
{
fi.equip_min_level = roledata.detailInfo.heroInfos[i].fightStoneInfos[0].level;
fi.equip_min_levelex = roledata.detailInfo.heroInfos[i].fightStoneInfos[0].levelex;
for (int j = 0; j < roledata.detailInfo.heroInfos[i].fightStoneInfos.Count; j++)
{
if (roledata.detailInfo.heroInfos[i].fightStoneInfos[j].level < fi.equip_min_level)
{
fi.equip_min_level = roledata.detailInfo.heroInfos[i].fightStoneInfos[j].level;
}
if (roledata.detailInfo.heroInfos[i].fightStoneInfos[j].levelex < fi.equip_min_levelex)
{
fi.equip_min_levelex = roledata.detailInfo.heroInfos[i].fightStoneInfos[j].levelex;
}
switch ((FightStoneColor)roledata.detailInfo.heroInfos[i].fightStoneInfos[j].color)
{
case FightStoneColor.Blue: bi.blue_equip_count++; break;
case FightStoneColor.Purple: bi.purple_equip_count++; break;
case FightStoneColor.Orange: bi.orange_equip_count++; break;
case FightStoneColor.Red: bi.red_equip_count++; break;
}
}
}
if (roledata.detailInfo.heroInfos[i].stoneInfos.Count > 0)
{
fi.stone_min_level = roledata.detailInfo.heroInfos[i].stoneInfos[0].level;
fi.stone_min_levelex = roledata.detailInfo.heroInfos[i].stoneInfos[0].levelex;
for (int j = 0; j < roledata.detailInfo.heroInfos[i].stoneInfos.Count; j++)
{
if (roledata.detailInfo.heroInfos[i].stoneInfos[j].level < fi.stone_min_level)
{
fi.stone_min_level = roledata.detailInfo.heroInfos[i].stoneInfos[j].level;
}
if (roledata.detailInfo.heroInfos[i].stoneInfos[j].levelex < fi.stone_min_levelex)
{
fi.stone_min_levelex = roledata.detailInfo.heroInfos[i].stoneInfos[j].levelex;
}
switch ((StoneColor)(roledata.detailInfo.heroInfos[i].stoneInfos[j].type % 10 + 1))
{
case StoneColor.Blue: bi.blue_stone_count++; break;
case StoneColor.Purple: bi.purple_stone_count++; break;
case StoneColor.Orange: bi.orange_stone_count++; break;
case StoneColor.Red: Log.AddLog(bi.uid + " " + hi.hero_id + " 5"); break;
}
}
}
pdi.fightInfo.Add(fi);
#endregion
}
}
}
bi.money = reader.GetInt32(2);
bi.yuanbao = reader.GetInt32(3);
pdi.baseInfo = bi;
if (!playerDataInfoDic.ContainsKey(bi.uid))
{
playerDataInfoDic.Add(bi.uid, pdi);
}
}
},
server.DatabaseAddress,
server.DatabasePort,
server.DatabaseCharSet,
server.GameDatabase,
server.DatabaseUserId,
server.DatabasePassword,
"SELECT user.uid,3rd_data,money,yuanbao FROM USER,user_info WHERE user.uid=user_info.uid"
);
}
public static bool GetServerDataFromDB(gmt.Server server
, string channel_id
, DateTime date_start
, DateTime date_end
, ref List <STServerTableData> listTable)
{
STServerTableData stData = new STServerTableData();
stData.section = server.Id;
stData.serverName = server.Name;
stData.channelId = channel_id;
Dictionary <string, int> dicRawType = new Dictionary <string, int>()
{
{ "lzsgkr_ios", 1 },
{ "lzsgkr_google", 2 },
{ "lzsgkr_onestore", 3 }
};
string sqlCondi = "";
string sqlAuthCondi = "";
if (!string.IsNullOrEmpty(channel_id))
{
sqlCondi += string.Format("and channel_id = '{0}'", channel_id);
sqlAuthCondi += string.Format("and raw_type={0}", dicRawType[channel_id]);
}
if (date_start != new DateTime(0))
{
sqlCondi += string.Format(" and date >= '{0}'", date_start.ToString("yyyy/MM/dd HH:mm:ss"));
sqlAuthCondi += string.Format(" and last_update_time >= '{0}'", date_start.ToString("yyyy/MM/dd HH:mm:ss"));
}
if (date_end != new DateTime(0))
{
sqlCondi += string.Format(" and date <= '{0}'", date_end.ToString("yyyy/MM/dd HH:mm:ss"));
sqlAuthCondi += string.Format(" and last_update_time <= '{0}'", date_end.ToString("yyyy/MM/dd HH:mm:ss"));
}
string sql = "";
#region 今日活跃
sql = string.Format("SELECT COUNT(DISTINCT cyuid) FROM login_ WHERE cyuid <> 'null' {0};", sqlCondi);
bool suc = DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
stData.activeUserCnt += reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
sql);
#endregion
if (!suc)
{
return(false);
}
#region 今日新增
sql = string.Format("SELECT COUNT(DISTINCT uid) FROM `rolebuild_` WHERE uid <> 'null' {0};", sqlCondi);
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
stData.newUserCnt += reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
sql);
#endregion
#region 今日设备新增
sql = string.Format("SELECT COUNT(DISTINCT deviceid) FROM `rolebuild_` WHERE uid <> 'null' {0};", sqlCondi);
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
stData.newDeviceCnt += reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
sql);
#endregion
StringBuilder sbIds = new StringBuilder();
#region 新用户付费数
sql = string.Format("SELECT uid FROM rolebuild_ WHERE uid <> 'null' {0}", sqlCondi);
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
sbIds.Append(reader.GetString(0));
sbIds.Append(",");
}
if (sbIds.Length != 0)
{
sbIds.Remove(sbIds.Length - 1, 1);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
sql
);
sql = string.Format("SELECT COUNT(DISTINCT userID) FROM receipt WHERE server_id = {0} and userID IN ({1})"
, server.serverID
, sbIds.ToString());
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
stData.newUserPayCnt += reader.GetInt32(0);
}
},
server.BillDatabaseAddress,
server.BillDatabasePort,
server.BillDatabaseCharSet,
server.BillDatabase,
server.BillDatabaseUserId,
server.BillDatabasePassword,
sql);
#endregion
#region 总付费数
sql = string.Format("SELECT COUNT(DISTINCT userID) FROM receipt WHERE server_id = {0} {1}"
, server.serverID
, sqlAuthCondi);
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
stData.totalPayCnt += reader.GetInt32(0);
}
},
server.BillDatabaseAddress,
server.BillDatabasePort,
server.BillDatabaseCharSet,
server.BillDatabase,
server.BillDatabaseUserId,
server.BillDatabasePassword,
sql);
#endregion
#region 新用户充值额
sql = string.Format("SELECT uid FROM rolebuild_ WHERE uid <> 'null' {0}", sqlCondi);
sbIds.Remove(0, sbIds.Length);
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
sbIds.Append(reader.GetString(0));
sbIds.Append(",");
}
if (sbIds.Length != 0)
{
sbIds.Remove(sbIds.Length - 1, 1);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
sql
);
sql = string.Format("SELECT orderID FROM receipt WHERE server_id = {0} and userID IN ({1})"
, server.serverID
, sbIds.ToString());
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
string orderInfo = reader.GetString(0);
string[] orderArray = orderInfo.Split(',');
mw.RmbShopConfig config = null;
string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]);
TableManager.RmbShopTable.TryGetValue(index, out config);
int cost = (null == config) ? config.rmb_cost : 0;
stData.newUserPayVal += (uint)cost;
}
},
server.BillDatabaseAddress,
server.BillDatabasePort,
server.BillDatabaseCharSet,
server.BillDatabase,
server.BillDatabaseUserId,
server.BillDatabasePassword,
sql);
#endregion
#region 总充值额
sql = string.Format("SELECT orderID FROM receipt WHERE server_id = {0} {1}"
, server.serverID
, sqlAuthCondi);
Log.AddLog(sql);
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
string orderInfo = reader.GetString(0);
string[] orderArray = orderInfo.Split(',');
mw.RmbShopConfig config = null;
string index = string.Format("{0}-{1}", orderArray[1], orderArray[4]);
TableManager.RmbShopTable.TryGetValue(index, out config);
int cost = (null == config) ? config.rmb_cost : 0;
stData.totalPayVal += (uint)cost;
}
},
server.BillDatabaseAddress,
server.BillDatabasePort,
server.BillDatabaseCharSet,
server.BillDatabase,
server.BillDatabaseUserId,
server.BillDatabasePassword,
sql
);
#endregion
#region 新用户付费率
if (stData.newUserCnt != 0)
{
stData.newUserPayRate = stData.newUserPayCnt / (float)stData.newUserCnt * 100;
}
#endregion
#region 新增付费用户
sql = string.Format("SELECT last_update_time FROM receipt where server_id = {0} GROUP BY userID;"
, server.serverID);
DatabaseAssistant.Execute(reader =>
{
while (reader.Read())
{
DateTime firstTime = reader.GetDateTime(0);
if (firstTime >= date_start &&
firstTime <= date_end)
{
stData.newPayUserCnt += 1;
}
}
},
server.BillDatabaseAddress,
server.BillDatabasePort,
server.BillDatabaseCharSet,
server.BillDatabase,
server.BillDatabaseUserId,
server.BillDatabasePassword,
sql);
#endregion
#region 活跃付费率
if (stData.activeUserCnt != 0)
{
stData.activePayRate = stData.totalPayCnt / (float)stData.activeUserCnt * 100;
}
#endregion
#region ARPPU
if (stData.totalPayCnt != 0)
{
stData.arppu = stData.totalPayVal / (float)stData.totalPayCnt;
}
#endregion
#region ARPU
if (stData.activeUserCnt != 0)
{
stData.arpu = stData.totalPayVal / (float)stData.activeUserCnt;
}
#endregion
listTable.Add(stData);
return(true);
}
public static void GetSevenDataFromDB(gmt.Server server, string channel_id, string start_time, string end_time, ref List <STSevenData> sdList)
{
string channel_condi = "= " + channel_id;
if (channel_id == "0" || channel_id == "" || channel_id == TableManager.GetGMTText(699))
{
channel_condi = "<> " + channel_id;
}
DateTime st = Convert.ToDateTime(start_time);
DateTime et = Convert.ToDateTime(end_time);
STSevenData sd = new STSevenData();
sd.serverName = server.Name;
int idx = 0;
for (DateTime dt = st; dt <= et; dt = dt.AddDays(1))
{
// 今日活跃
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
sd.activeUserCnt[idx] = reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
"SELECT COUNT(DISTINCT cyuid) FROM login_{0} WHERE (cyuid <> 'null' OR cyuid IS NOT NULL) and channelid {1};"
, dt.ToString("yyyy_MM_dd")
, channel_condi);
// 总充值额
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
sd.totalPayVal[idx] = reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
@"SELECT SUM(rmb_1) + SUM(rmb_2) + SUM(rmb_3) FROM (SELECT uid, IF(typeid=21,168,0)'rmb_1', IF(typeid=15,25,0)'rmb_2', IF(typeid=13,quantity/10,0)'rmb_3' FROM economic_{0} WHERE causeid = 50 and channel_id {1})t"
, dt.ToString("yyyy_MM_dd")
, channel_condi);
// 总付费数
DatabaseAssistant.Execute(reader =>
{
if (reader.Read())
{
sd.totalPayCnt[idx] = reader.GetInt32(0);
}
},
server.LogDatabaseAddress,
server.LogDatabasePort,
server.LogDatabaseCharSet,
server.LogDatabase,
server.LogDatabaseUserId,
server.LogDatabasePassword,
@"SELECT COUNT(DISTINCT uid) FROM economic_{0} WHERE causeid = 50 and channel_id {1}"
, dt.ToString("yyyy_MM_dd")
, channel_condi
);
idx++;
}
sdList.Add(sd);
}
