/// <summary>
/// type, size,collate, null
/// </summary>
public override void WriteColumnChanges(ColumnModifications col)
{
var w = new MySqlColumnWriter(Builder);
Builder.AppendFormat("alter table {0} modify ", MySqlProvider.EscapeIdentifier(col.Name));
w.Write(col);
}
internal static void Initialize()
{
try
{
Provider = new MySqlProvider(
Settings.AppSettings.GetStringElement("Database.Host"),
Settings.AppSettings.GetStringElement("Database.Name"),
Settings.AppSettings.GetStringElement("Database.Username"),
Settings.AppSettings.GetStringElement("Database.Password"));
Provider.Connect();
RealmProvider = new MySqlProvider(
Settings.AppSettings.GetStringElement("Database.Host"),
Settings.AppSettings.GetStringElement("Database.rName"),
Settings.AppSettings.GetStringElement("Database.Username"),
Settings.AppSettings.GetStringElement("Database.Password"));
RealmProvider.Connect();
Logger.Info("Connected to the database !");
}
catch (Exception e)
{
Logger.Error("Can't connect to database : " + e.ToString());
}
}
public MySqlDataContext(string server, string user, string password, string database, bool open = true)
{
try
{
string[] serverSplit = server.Split(':');
string connectionString = string.Empty;
if (serverSplit.Length > 1)
{
connectionString = $"server={serverSplit[0]};port={serverSplit[1]};userid={user};password={password};database={database}";
}
else
{
connectionString = $"server={serverSplit[0]};userid={user};password={password};database={database}";
}
MySqlConnection sqlConnection = new MySqlConnection(connectionString);
m_MySqlProvider = new MySqlProvider(sqlConnection);
if (open)
{
Open();
}
}
catch
{
m_MySqlProvider.Connection.Close();
}
}
public static async Task FixDuplicateScores(Presence pr, string channel, string message, string[] args)
{
await ChannelManager.BotMessage(pr, channel, "Start cleaning duplicate scores...");
await using (var db = MySqlProvider.GetDbConnection())
{
var scoreHashes = new List <string>();
var scores = await db.QueryAsync <DbScore>("SELECT * FROM Scores");
await ChannelManager.BotMessage(pr, channel, $"Scores to check: {scores.Count()}");
foreach (var score in scores)
{
var scoreHash = Crypto.ComputeHash($"sum:{score.Count50 + score.Count100 + score.Count300}c300" +
$"{score.Count300}c100{score.Count100}c50{score.Count50}cgeki{score.CountGeki}ckatu" +
$"{score.CountKatu}cmiss{score.CountMiss}beatmap{score.FileChecksum}accuracy{score.Accuracy}");
if (scoreHashes.Contains(scoreHash))
{
await db.ExecuteAsync($"DELETE FROM Scores WHERE Id = {score.Id}");
}
else
{
scoreHashes.Add(scoreHash);
}
}
}
await ChannelManager.BotMessage(pr, channel, "Done!");
}
/************************************************************************************************************************/
internal void Init(IDbConnection conn)
{
//
if (XConfig.DB == DbEnum.None)
{
if (XConfig.MySQL.Equals(conn.GetType().FullName, StringComparison.OrdinalIgnoreCase))
{
XConfig.DB = DbEnum.MySQL;
}
else
{
throw new Exception("MyDAL 目前只支持 【MySQL】,后续将会支持【Oracle/SQLServer/PostgreSQL/DB2/Access/SQLite/Teradata/MariaDB】.");
}
}
//
Conn = conn;
Parameters = new List <DicParam>();
AH = new AttributeHelper(this);
VH = new CsValueHelper(this);
GH = new GenericHelper(this);
EH = new XExpression(this);
SC = new XCache(this);
PH = new ParameterHelper(this);
DPH = new DicParamHelper(this);
BDH = new BatchDataHelper();
DS = new DataSource(this);
//
if (XConfig.DB == DbEnum.MySQL)
{
SqlProvider = new MySqlProvider(this);
}
}
private static IDbProvider CreateAppDbProvider()
{
IDbProvider dbProvider = null;
switch (DbType)
{
case DatabaseType.SqlServer:
dbProvider = new SqlServerProvider();
break;
case DatabaseType.MySql:
dbProvider = new MySqlProvider();
break;
case DatabaseType.Oracle:
dbProvider = new OracleProvider();
break;
default:
dbProvider = new SqlServerProvider();
break;
}
return(dbProvider);
}
public void LateInit()
{
orignal = R.Permissions;
Provider = new MySqlProvider();
R.Permissions = Provider;
RocketLogger.Log(string.Format("Late Initialize was successful!"), ConsoleColor.Yellow);
}
public UserRepository SetUserRepository()
{
service = new MySqlProvider(
"server=localhost;UserId=root;Password=1234;database=remotenotesIntegrationTest;allow zero datetime=yes;Allow User Variables=True;");
var hold = new UserRepository(service);
return(hold);
}
public IHttpActionResult WeChatServicesRefundApi([FromBody] JToken json)
{
return(this.TryReturn <object>(() =>
{
try
{
WxPayData wxp = new WxPayData();
//插入调用退款接口Json
wxp.WriteLogFile("调用退款Json:" + json.ToJsonString());
var arg = json.AsDynamic();
//订单号
string orderNo = arg.orderNo;
//查询要退款的单据
var selectOrderSql = string.Format(@"SELECT * FROM B_ORDER WHERE CODE='{0}' AND PAY_STATUS='已支付' AND IS_DELETE=0", orderNo, this.LoginUser.Cid);
wxp.WriteLogFile("执行单据退款查询SQL：" + selectOrderSql);
var recordList = new DataSet();
using (var x = MySqlProvider.X())
{
recordList = x.ExecuteSqlCommand(selectOrderSql);
x.Close();
}
var recordRows = recordList.Tables[0].DataSet.Rows().FirstOrDefault();
if (recordRows != null)
{
var item = recordRows;
var refundNo = "RFD" + orderNo;
//订单金额
string price = item["PRICE"].ToString();
var data = WxPayData.ForRefund(double.Parse(price), double.Parse(price), orderNo, refundNo);
var url = GlobalVariableWeChatApplets.REFUND_URL;
var xml = data.ToXml();
var response = HttpService.PostByCertificates(xml, url, 6);
var preOrder = WxPayData.FromXml(response, "HMAC-SHA256");
var backCode = preOrder.GetValue("return_code");
if (backCode != null && backCode.ToString() == "FAIL")
{
var returnMsg = preOrder.GetValue("return_msg").ToString();
return new { Table = new { IsSuccess = false, ErroMessage = returnMsg, MSG = "" } };
}
else
{
var payData = WxPayData.ForWechatRefund(preOrder);
var orderString = payData.ToJson();
return new { Table = new { IsSuccess = true, ErroMessage = "", MSG = orderString } };
}
}
else
{
return new { Table = new { IsSuccess = false, ErroMessage = "未查询到需要退款的数据", MSG = "" } };
}
}
catch (Exception ex)
{
return new { Table = new { IsSuccess = false, ErroMessage = ex.Message, Json = "" } };
}
}));
}
public void ShouldHaveSetDialectProvider()
{
// Act
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
// Assert
Assert.IsInstanceOf<MySqlDialectProvider>(OrmLiteConfig.DialectProvider);
}
}
public MySqlDataContext(MySqlConnection connection)
{
if (connection is null)
{
throw new ArgumentNullException(nameof(connection));
}
m_MySqlProvider = new MySqlProvider(connection);
}
public Configuration(IConfiguration configuration)
{
_configuration = configuration;
MySqlProvider = new MySqlProvider(new MySqlConnection(configuration.GetConnectionString("ttyplatform")));
AesAddin = new AesAddin("LAVERALSTARDANDMDS7024200345IEVS");
TableMap = new TableMap();
API = new API();
//TermTimeUnis = GetTimeConfig();
}
public void mysql_test_pagination_strings()
{
var m = new MySqlProvider(Config.SqlServerProviderName);
string cnt;
string sel;
m.MakePaged("select * from test", out sel, out cnt);
Assert.Equal("select count(*) from test", cnt);
Assert.Equal(string.Format("select * from test limit @{0},@{1}", PreparePagedStatement.SkipParameterName, PreparePagedStatement.TakeParameterName), sel);
}
protected void grdResult_RowDeleting(object sender, System.Web.UI.WebControls.GridViewDeleteEventArgs e)
{
string query = "DELETE FROM Books WHERE Id = @Id";
Dictionary <string, object> parametters = new Dictionary <string, object>();
parametters.Add("Id", e.Keys[0]);
MySqlProvider.ExecuteSqlQueryReturnValue(query, parametters, result => grdResultFill());
}
private void grdResultFill()
{
string query = "SELECT * FROM Books";
MySqlProvider.ExecuteSqlQueryReturnValue(query, null, delegate(MySqlDataReader reader)
{
grdResult.DataSource = reader;
grdResult.DataBind();
});
}
protected override void WriteIndexName()
{
Builder.Append(MySqlProvider.EscapeIdentifier(Index.Name));
var opt = Index.Options.Get(MySqlIndexOptions.Using);
if (opt != null)
{
Builder.Append(" " + opt);
}
}
public void ShouldDropAndCreate()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
// Act
provider.DropAndCreate(typeof(Shipper));
// Assert
Assert.IsTrue(provider.CollectionExists(typeof(Shipper)));
Assert.IsTrue(provider.CollectionExists(typeof(ShipperContact)));
}
}
protected void grdResult_RowUpdating(object sender, System.Web.UI.WebControls.GridViewUpdateEventArgs e)
{
string query = "UPDATE Books";
string where = "WHERE Id = " + e.Keys[0];
Dictionary <string, object> parametters = new Dictionary <string, object>();
parametters.Add("Title", e.NewValues[0] == null ? string.Empty : e.NewValues[0]);
parametters.Add("Author", e.NewValues[1] == null ? string.Empty : e.NewValues[1]);
MySqlProvider.ExecuteSqlQueryUpdate(query, where, parametters, reader => grdResult_RowCancelingEdit(sender, null));
}
/// <summary>
/// 条件查询用户信息
/// </summary>
/// <param name="mobile">手机号</param>
/// <param name="phone">电话</param>
/// <param name="trueName">真实姓名</param>
/// <param name="isJoinMoblie">手机号是否联合查询 默认为 是</param>
/// <returns></returns>
public OperationResult <IList <UsersInfo> > Users_GetUserByParms(string mobile, string phone, string trueName, int userID, int isJoinMoblie = 1)
{
IList <UsersInfo> userInfoList = new List <UsersInfo>();
try
{
using (var context = new MySqlProvider(DbConnectionName.MySqlUsersContext.ToString()))
{
var sql = string.Empty;
sql += " SELECT u.UID,u.UserName,r.TrueName,r.Address,u.CreateTime,u.Status,u.Mobile ";
sql += " FROM Users u LEFT JOIN Receiver r ";
sql += " ON u.UID=r.UID ";
sql += " INNER JOIN Usersbase b ";
sql += " on u.UID=b.UID ";
sql += " WHERE 1=1 ";
if (!string.IsNullOrEmpty(mobile) && mobile != "" && isJoinMoblie == 1)
{
sql += " AND r.Mobile='{0}' OR u.Mobile='{1}' OR b.Mobile='{2}'";
sql = string.Format(sql, mobile, mobile, mobile, mobile);
}
else if (!string.IsNullOrEmpty(mobile) && mobile != "" && isJoinMoblie == 0)
{
sql += " AND u.Mobile='{0}' ";
sql = string.Format(sql, mobile);
}
if (!string.IsNullOrEmpty(phone) && phone != "")
{
sql += " AND r.Telephone LIKE '%{0}%' ";
sql = string.Format(sql, phone);
}
if (!string.IsNullOrEmpty(trueName) && trueName != "")
{
sql += " AND r.TrueName LIKE '%{0}%' ";
sql = string.Format(sql, trueName);
}
if (userID > 0)
{
sql += " AND (r.UID ={0} OR u.UID={1}) ";
sql = string.Format(sql, userID, userID);
}
sql += " GROUP BY u.UID ";
userInfoList = context.Database.SqlQuery <Model.ResultModel.UsersInfo>(sql).ToList();
return(new OperationResult <IList <UsersInfo> >(OperationResultType.Success, null, userInfoList));
}
}
catch (Exception ex)
{
return(new OperationResult <IList <UsersInfo> >(OperationResultType.Error, ex.Message, userInfoList));
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string query = "SELECT * FROM books WHERE LOCATE (@SearchOption, Title)";
Dictionary <string, object> parametters = new Dictionary <string, object>();
parametters.Add("SearchOption", txtSearch.Text);
MySqlProvider.ExecuteSqlQueryReturnValue(query, parametters, delegate(MySqlDataReader reader)
{
grdResult.DataSource = reader;
grdResult.DataBind();
});
}
private MySqlProviderAdapter(
MySqlProvider provider,
Type connectionType,
Type dataReaderType,
Type parameterType,
Type commandType,
Type transactionType,
Type?mySqlDecimalType,
Type mySqlDateTimeType,
Type mySqlGeometryType,
Func <object, decimal>?mySqlDecimalGetter,
Func <IDbDataParameter, object> dbTypeGetter,
string?getMySqlDecimalMethodName,
string?getDateTimeOffsetMethodName,
string getMySqlDateTimeMethodName,
string providerTypesNamespace,
MappingSchema mappingSchema,
BulkCopyAdapter?bulkCopy)
{
ProviderType = provider;
ConnectionType = connectionType;
DataReaderType = dataReaderType;
ParameterType = parameterType;
CommandType = commandType;
TransactionType = transactionType;
MySqlDecimalType = mySqlDecimalType;
MySqlDateTimeType = mySqlDateTimeType;
MySqlGeometryType = mySqlGeometryType;
MySqlDecimalGetter = mySqlDecimalGetter;
GetDbType = dbTypeGetter;
GetMySqlDecimalMethodName = getMySqlDecimalMethodName;
GetDateTimeOffsetMethodName = getDateTimeOffsetMethodName;
GetMySqlDateTimeMethodName = getMySqlDateTimeMethodName;
ProviderTypesNamespace = providerTypesNamespace;
MappingSchema = mappingSchema;
BulkCopy = bulkCopy;
}
protected void btnInsert_Click(object sender, EventArgs e)
{
string query = "INSERT INTO Books";
Dictionary <string, object> parametters = new Dictionary <string, object>();
parametters.Add("Title", txtBookTitle.Text);
parametters.Add("Author", txtBookAuthor.Text);
parametters.Add("PublishDate", DateTime.Now);
MySqlProvider.ExecuteSqlQueryInsert(query, parametters, delegate(int i)
{
grdResultFill();
});
}
public void ShouldClearAllCollections()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
Shipper shipper = DataProvider.PreInsertArrange(provider);
shipper = provider.Insert<Shipper>(shipper);
// Act
provider.DeleteAll<Shipper>();
// Assert
Assert.AreEqual(provider.Select<Shipper>().Count, 0);
Assert.AreEqual(provider.Select<ShipperContact>().Count, 0);
}
}
public void ShouldThrowArgumentNullExceptionForNullConnectionString()
{
// Act
try
{
using (MySqlProvider provider = new MySqlProvider(null))
{
// Assert
Assert.Fail("Should not allow empty connection strings");
}
}
catch (ArgumentNullException)
{
Assert.Pass();
}
}
public static void TryMySqlData()
{
if (!IsTryMySql)
{
try
{
MySqlProvider db = new MySqlProvider();
var conn = db.GetAdapter();
IsTryMySql = true;
}
catch
{
throw new Exception("You need to refer to MySql.Data.dl");
}
}
}
static void Main(string[] args)
{
const string connectionStringPath = @"\\psf\Home\Dropbox\Dox\Mac-Git\connectionString.txt";
string connectionString = ConnectionStringReader.GetConnectionString(connectionStringPath);
var dbFactory = new OrmLiteConnectionFactory(connectionString, MySqlDialectProvider.Instance);
ISqlProvider customProvider = new MySqlProvider();
using (var db = dbFactory.OpenDbConnection())
{
db.UpdateTable <Customer>(customProvider, true);
var x = db.GetLastSql();
Console.ReadKey();
}
}
public static void TryMySqlData()
{
if (!IsTryMySqlData)
{
try
{
MySqlProvider db = new MySqlProvider();
var conn = db.GetAdapter();
IsTryMySqlData = true;
}
catch
{
var message = ErrorMessage.GetThrowMessage(
"You need to refer to MySql.Data.dll",
"需要引用MySql.Data.dll，请在Nuget安装最新稳定版本,如果有版本兼容问题请先删除原有引用");
throw new Exception(message);
}
}
}
public static async Task RecalculatePerformance(Presence pr, string channel, string message, string[] args)
{
await ChannelManager.BotMessage(pr, channel, "Start recalculating performance points...");
await using (var db = MySqlProvider.GetDbConnection())
{
var scores = await db.QueryAsync <DbScore>("SELECT * FROM Scores");
await ChannelManager.BotMessage(pr, channel, $"Scores to re-calculate: {scores.Count()}");
foreach (var score in scores)
{
try
{
var beatmap = (await BeatmapManager.Get(score.FileChecksum));
if (beatmap.Item1 is RankedStatus.NotSubmitted or RankedStatus.NeedUpdate)
{
continue;
}
double pp = 0;
if (beatmap.Item2.Status == RankedStatus.Loved)
{
pp = 0;
}
else
{
pp = await Calculator.CalculatePerformancePoints(score);
}
await db.ExecuteAsync($"UPDATE Scores SET PerformancePoints = @PP WHERE Id = {score.Id}", new
{
PP = pp
});
}
catch
{
// ignored
}
}
}
await ChannelManager.BotMessage(pr, channel, "Done!");
}
/// <summary>
/// 创建参数回调
/// </summary>
/// <param name="item">参数对象</param>
protected override void OnCreate(CommandParameter item)
{
if (item.Value == null)
{
return;
}
if (item.Value.GetType() != item.RealType)
{
item.Value = TypeExtensions.Convert(item.Value, item.RealType);
}
if (item.RealType.IsEnum)
{
//item.Properties["MySqlDbType"] = TypeExtensions.Convert("Int64", MySqlProvider.GetDbType());
item.RealType = typeof(long);
item.Value = TypeExtensions.Convert <long>(item.Value);
return;
}
if (item.RealType.IsArray && item.RealType.GetElementType() != typeof(byte))
{
item.Properties["MySqlDbType"] = TypeExtensions.Convert("JSON", MySqlProvider.GetDbType());
item.Value = item.Value == null ? null : JSON.ToJSON(item.Value);
return;
}
if (
item.RealType == typeof(object) ||
TypeExtensions.IsAnonymousType(item.RealType) ||
TypeExtensions.IsInheritFrom(item.RealType, typeof(System.Collections.Generic.IDictionary <string, object>)) ||
(item.RealType.IsClass && !TypeExtensions.IsSystemBaseType(item.RealType))
)
{
item.Properties["MySqlDbType"] = TypeExtensions.Convert("JSON", MySqlProvider.GetDbType());
item.RealType = typeof(object);
item.Value = item.Value == null ? null : JSON.ToJSON(item.Value);
return;
}
}
public static async Task <Score[]> GetRawScores(string beatmapMd5, PlayMode mode, RankedStatus status,
LeaderboardMode lbMode)
{
IEnumerable <DbScore> scores = null;
await using (var db = MySqlProvider.GetDbConnection())
{
scores = await db.QueryAsync <DbScore>($"SELECT *, Scores.Id AS Id FROM Scores " +
$"JOIN Users ON Users.Id = Scores.UserId " +
$"WHERE Users.Privileges & {(int) Privileges.Normal} > 0 " +
$"AND FileChecksum = '{beatmapMd5}' " +
$"AND Completed = {(int) CompletedStatus.Best} " +
$"AND PlayMode = {(int) mode} " +
$"AND Relaxing = {lbMode == LeaderboardMode.Relax} " +
$"ORDER BY {(lbMode == LeaderboardMode.Relax ? "PerformancePoints" : "TotalScore")} DESC " +
$"LIMIT 50");
}
var dbScores = scores as DbScore[] ?? scores.ToArray();
return(dbScores.Select(score => FromDb(score, status, dbScores)).ToArray());
}
public static void Init(IServiceProvider serviceProvider, IWebHostEnvironment env)
{
IAdminSecurityApplication securityApplication = new AdminSecurityApplication();
securityApplication.ResetIpFilter();
securityApplication = null;
var cache = SqlCache.Instance;
ISqlProvider provider = new SqlServerProvider();
provider.Init(cache);
provider = new MySqlProvider();
provider.Init(cache);
provider = new SqliteProvider();
provider.Init(cache);
provider = new PostgreSQLProvider();
provider.Init(cache);
provider = null;
//var assList = AppDomain.CurrentDomain.GetAssemblies().Where(q => q.FullName?.Contains("Plugin") ?? false).ToList();
//foreach (var ass in assList) {
// SqlProviderInit(cache, ass);
//}
//var dir = Path.Combine(env.ContentRootPath, "Plugins");
//if (Directory.Exists(dir)) {
// var files = Directory.GetFiles(dir, "*.Plugin*.dll", SearchOption.AllDirectories);
// foreach (var file in files) {
// try {
// var ass = Assembly.LoadFrom(file);
// SqlProviderInit(cache, ass);
// } catch (Exception) { }
// }
//}
}
protected override string Escape(string name)
{
return(MySqlProvider.EscapeIdentifier(name));
}
public void ShouldNotClearLookup()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
Shipper shipper = DataProvider.PreInsertArrange(provider);
// Act
provider.DeleteAll<Shipper>();
// Assert
Assert.AreEqual(provider.Select<ShipperType>().Count, 1);
}
}
public void ShouldSelectShipperAndTreeWithExpression()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
Shipper shipper = DataProvider.PreInsertArrange(provider);
shipper = provider.Insert<Shipper>(shipper);
// Act
Shipper savedTree = provider.Select<Shipper>(x => x.Id == shipper.Id).First();
// Assert
Assert.IsNotNull(savedTree);
Assert.AreEqual(savedTree.ShipperContacts.Count, 1);
Assert.IsNotNull(savedTree.ShipperContacts[0]);
}
}
public void ShouldUpdateShipper()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
Shipper shipper = DataProvider.PreInsertArrange(provider);
provider.Insert<Shipper>(shipper);
shipper.CompanyName += " Changed";
shipper.ShipperContacts[0].EmailAddress += shipper.ShipperContacts[0].EmailAddress + " Changed";
// Act
provider.Update<Shipper>(shipper);
// Assert
Shipper updatedShipper = provider.SelectById<Shipper>(shipper.Id);
Assert.AreEqual(shipper.CompanyName, updatedShipper.CompanyName);
Assert.AreEqual(shipper.ShipperContacts[0].EmailAddress, updatedShipper.ShipperContacts[0].EmailAddress);
}
}
public static void Initialize(TestContext context)
{
prov = new MySqlProvider();
}
public override void provider_escape_name()
{
Assert.Equal("`dbo.table`", MySqlProvider.EscapeIdentifier("dbo.table"));
}
public void ShouldDeleteAll()
{
// Arrange
using (MySqlProvider provider = new MySqlProvider(ConfigSettings.MySqlConnectionString))
{
Shipper shipper = DataProvider.PreInsertArrange(provider);
provider.Insert<Shipper>(shipper);
// Act
provider.DeleteAll<Shipper>();
// Assert
Assert.IsNull(provider.SelectById<Shipper>(shipper.Id));
Assert.IsNull(provider.SelectById<Shipper>(shipper.ShipperContacts[0].Id));
}
}
