private static async Task UpdateAsync(SqlDb db)
{
var order = await db.SelectSingleAsync <Order>((o, s) => s.Navigate(o), 50);
var u1 = await db.UpdateAsync <Order>((o, s) => new Order
{
CreateTime = DateTime.Now,
Address = order.Address,
Name = order.Name,
}, order.Id);
var now = DateTime.Now;
var u2 = await db.UpdateAsync <Order>((o, s) => new Order
{
CreateTime = now,//param
Address = order.Address,
Name = order.Name,
}, (o, s) => o.Id == order.Id);
var u3 = await db.UpdateAsync <Order>(order, o => new { o.Name, o.CreateTime });
var u4 = await db.UpdateAsync <Order>(order, o => new { o.Name, o.CreateTime }, (o, s) => o.Id == order.Id);
var u5 = await db.UpdateAsync <Order>(order, o => SqlDbExtensions.Except(o.Id));
var u6 = await db.UpdateAsync <Order>(order, o => SqlDbExtensions.Except(new { o.Id, o.CustomId }));
var u7 = await db.UpdateAsync <Order>(order, o => SqlDbExtensions.Except(new { o.Id, o.CustomId }), (o, s) => o.Id == order.Id);
}
private static async Task InsertAsync(SqlDb db)
{
var orders = new List <Order>();
var customDetails = new List <CustomDetail>();
for (int i = 0; i < 100; i++)
{
var custom = new Custom()
{
Name = $"Custom{i}", Age = i
};
custom.Id = await db.InsertIdentityAsync <Custom, int>(custom, (c) => SqlDbExtensions.Except(c.Id));
var random = new Random().Next(0, 9);
for (int j = 0; j < random; j++)
{
var order = new Order()
{
Name = $"Order{i}-{j}",
CreateTime = DateTime.Now,
CustomId = custom.Id,
Price = j,
State = OrderState.State3,
JsonData = new JsonData()
{
String = "JsonString--XYZ",
Long = long.MaxValue
}
};
orders.Add(order);
}
customDetails.Add(new CustomDetail()
{
CustomId = custom.Id,
Bool = false,
NullInt = null,
});
}
await db.InsertRangeAsync <Order>(orders, o => SqlDbExtensions.Except(o.Id));
await db.InsertRangeAsync <CustomDetail>(customDetails, c => new { c.CustomId, c.Bool, c.NullInt });
var selectOrders = await db.SelectAsync <Order>((o, s) => o, null);
foreach (var selectOrder in selectOrders)
{
var detail = new string((char)new Random().Next(1, 127), 10);
await db.InsertAsync <Address>(s => new Address()
{
OrderId = selectOrder.Id,
Mobile = selectOrder.Id.ToString(),
Sheng = "Sheng",
Shi = "Shi",
Xian = "Xian",
Name = Guid.NewGuid().ToString(),
Detail = detail
});
}
}
public static async Task RunOracle()
{
var builder = new OracleConnectionStringBuilder();
builder.DataSource = "49.232.79.230/orcl";
builder.UserID = "system";
builder.Password = "******";
var connectionString = builder.ToString();
Console.WriteLine(connectionString);
var db = SqlDb.Create <OracleConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
});
//Number of rows affected
await db.ExecuteAsync("SELECT 'ZhangHe' FROM DUAL");
var stringParam = "ZhangHe";
await db.ExecuteAsync("SELECT :String FROM DUAL", new { String = stringParam });
await db.ExecuteAsync("SELECT :String FROM DUAL", new List <(string, object)>() { ("String", stringParam) });
await db.ExecuteFormatAsync($"SELECT {stringParam} FROM DUAL");
//Model
var exe1 = await db.ExecuteAsync <string>("SELECT 'ZhangHe' FROM DUAL");
var exe2 = await db.ExecuteAsync <string>("SELECT :String FROM DUAL", new { String = stringParam });
var exe3 = await db.ExecuteAsync <string>("SELECT :String FROM DUAL", new List <(string, object)>() { ("String", stringParam) });
var exe4 = await db.ExecuteFormatAsync <string>($"SELECT {stringParam} FROM DUAL");
(var name, var age) = await db.ExecuteAsync <string, int>("BEGIN OPEN :R1 FOR SELECT :Name FROM DUAL;OPEN :R2 FOR SELECT :Age FROM DUAL;END;", new
{
R1 = new OracleParameter()
{
OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output
},
R2 = new OracleParameter()
{
OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output
},
Name = stringParam,
Age = int.MinValue
});
try { await db.ExecuteAsync("DROP TABLE \"SYSTEM\".\"Custom\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"SYSTEM\".\"CustomDetail\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"SYSTEM\".\"Address\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"SYSTEM\".\"Order\""); } catch { }
await db.ExecuteAsync("CREATE TABLE \"SYSTEM\".\"Custom\"(\"Id\" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE ,\"Name\" VARCHAR2(500 BYTE),\"Age\" NUMBER(*,0))");
await db.ExecuteAsync("CREATE TABLE \"SYSTEM\".\"CustomDetail\"(\"CustomId\" NUMBER(*,0),\"NullInt\" NUMBER(*,0),\"Bool\" NUMBER(1))");
await db.ExecuteAsync("CREATE TABLE \"SYSTEM\".\"Address\"(\"OrderId\" NUMBER(*,0),\"Name\" VARCHAR2(500 BYTE),\"Detail\" VARCHAR2(500 BYTE),\"Mobile\" VARCHAR2(500 BYTE),\"Sheng\" VARCHAR2(500 BYTE),\"Shi\" VARCHAR2(500 BYTE),\"Xian\" VARCHAR2(500 BYTE))");
await db.ExecuteAsync("CREATE TABLE \"SYSTEM\".\"Order\"(\"Id\" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE ,\"Name\" VARCHAR2(500 BYTE),\"Price\" NUMBER(*,0),\"CreateTime\" DATE,\"CustomId\" NUMBER(*,0),\"State\" NUMBER(*,0),\"JsonData\" VARCHAR2(500 BYTE))");
//SqlDbExtensions.RegisterTable(type => type.Name);//ToUpper()
//SqlDbExtensions.RegisterProperty(property => property.Name);
//SqlDbExtensions.RegisterIdentity
SqlDbExtensions.RegisterDbParameter <OracleConnection, JsonData>((value) => {
return(JsonWriter.ToJson <JsonData>((JsonData)value));
});
//(reader,i)=field,(reader)=model Mapper
SqlDbExtensions.RegisterDbReader <OracleDataReader, JsonData>((reader, i) => {
var json = reader.GetString(i);
return(JsonReader.FromJson <JsonData>(json));
});
//如果支持bool<=>int 不需要
//If supported bool<=>int,not required
SqlDbExtensions.RegisterDbParameter <OracleConnection, bool>((value) => {
return((bool)value ? 1 : 0);
});
SqlDbExtensions.RegisterDbReader <OracleDataReader, bool>((reader, i) => {
return(reader.GetInt32(i) == 1 ? true : false);
});
SqlDbExtensions.RegisterDbMethod <OracleConnection>((methods) => {
foreach (var item in methods)
{
var method = item.Key;
Console.WriteLine($"{method.DeclaringType}.{method.Name}()");
}
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus1"), (expr) => {
return(new object[] { "COUNT(", expr.Arguments[1], ")+1" });//string OR Expression
});
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus2"), (expr) => {
var exprObjs = new List <object>();//string OR Expression
exprObjs.Add("COUNT(");
exprObjs.Add(expr.Arguments[0]);
exprObjs.Add(")+2");
return(exprObjs);
});
});
SqlDbExtensions.RegisterDbMember <OracleConnection>((members) => {
foreach (var item in members)
{
var member = item.Key;
Console.WriteLine($"{member.DeclaringType}.{member.Name}");
}
});
await InsertAsync(db);
await SelectAsync(db);
await UpdateAsync(db);
await DeleteAsync(db);
//Transaction
//out ITransactionFactory use AsyncLocal<>
//如果不介意性能 不需要分开定义 db,dbTx
//If you don't mind performance There is no need to define separately db,dbTx
var dbTx = SqlDb.Create <OracleConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
}, out var txFactory);
await TransactionAsync(dbTx, txFactory);
}
public static async Task RunPostgre()
{
var builder = new NpgsqlConnectionStringBuilder();
builder.Database = "testdb";
builder.Host = "localhost";
builder.Port = 5432;
builder.Username = "******";
builder.Password = "******";
var connectionString = builder.ToString();
Console.WriteLine(connectionString);
var db = SqlDb.Create <NpgsqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
});
//Number of rows affected
await db.ExecuteAsync("SELECT 'ZhangHe'");
var stringParam = "ZhangHe";
await db.ExecuteAsync("SELECT @String", new { String = stringParam });
await db.ExecuteAsync("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
await db.ExecuteFormatAsync($"SELECT {stringParam}");
//Model
var exe1 = await db.ExecuteAsync <string>("SELECT 'ZhangHe'");
var exe2 = await db.ExecuteAsync <string>("SELECT @String", new { String = stringParam });
var exe3 = await db.ExecuteAsync <string>("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
var exe4 = await db.ExecuteFormatAsync <string>($"SELECT {stringParam}");
(var name, var age) = await db.ExecuteAsync <string, int>("SELECT @Name;SELECT @Age", new { Name = stringParam, Age = int.MinValue });
(var names, var ages, var ids) = await db.ExecuteAsync <List <string>, List <int>, List <int> >("SELECT @Name;SELECT @Age;SELECT @Id", new { Id = 1024, Name = stringParam, Age = int.MinValue });
try { await db.ExecuteAsync("DROP TABLE \"Custom\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"CustomDetail\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"Address\""); } catch { }
try { await db.ExecuteAsync("DROP TABLE \"Order\""); } catch { }
await db.ExecuteAsync("CREATE TABLE \"Custom\"(\"Id\" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),\"Name\" text,\"Age\" integer)");
await db.ExecuteAsync("CREATE TABLE \"CustomDetail\"(\"CustomId\" integer,\"NullInt\" integer,\"Bool\" boolean)");
await db.ExecuteAsync("CREATE TABLE \"Address\"(\"OrderId\" integer,\"Name\" text,\"Detail\" text,\"Mobile\" text,\"Sheng\" text,\"Shi\" text,\"Xian\" text)");
await db.ExecuteAsync("CREATE TABLE \"Order\"(\"Id\" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),\"Name\" text,\"Price\" integer,\"CreateTime\" DATE,\"CustomId\" integer,\"State\" integer,\"JsonData\" text)");
//SqlDbExtensions.RegisterTable(type => type.Name);
//SqlDbExtensions.RegisterProperty(property => property.Name);
//SqlDbExtensions.RegisterIdentity
SqlDbExtensions.RegisterDbParameter <NpgsqlConnection, JsonData>((value) => {
return(JsonWriter.ToJson <JsonData>((JsonData)value));
});
//(reader,i)=field,(reader)=model Mapper
SqlDbExtensions.RegisterDbReader <NpgsqlDataReader, JsonData>((reader, i) => {
var json = reader.GetString(i);
return(JsonReader.FromJson <JsonData>(json));
});
SqlDbExtensions.RegisterDbMethod <NpgsqlConnection>((methods) => {
foreach (var item in methods)
{
var method = item.Key;
Console.WriteLine($"{method.DeclaringType}.{method.Name}()");
}
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus1"), (expr) => {
return(new object[] { "COUNT(", expr.Arguments[1], ")+1" });//string OR Expression
});
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus2"), (expr) => {
var exprObjs = new List <object>();//string OR Expression
exprObjs.Add("COUNT(");
exprObjs.Add(expr.Arguments[0]);
exprObjs.Add(")+2");
return(exprObjs);
});
});
SqlDbExtensions.RegisterDbMember <NpgsqlConnection>((members) => {
foreach (var item in members)
{
var member = item.Key;
Console.WriteLine($"{member.DeclaringType}.{member.Name}");
}
});
await InsertAsync(db);
await SelectAsync(db);
await UpdateAsync(db);
await DeleteAsync(db);
//Transaction
//out ITransactionFactory use AsyncLocal<>
//如果不介意性能 不需要分开定义 db,dbTx
//If you don't mind performance There is no need to define separately db,dbTx
var dbTx = SqlDb.Create <NpgsqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
}, out var txFactory);
await TransactionAsync(dbTx, txFactory);
}
public static async Task RunMySql()
{
var builder = new MySqlConnectionStringBuilder();
builder.Database = "testdb";
builder.Server = "49.232.79.230";
builder.Port = 3306;
builder.UserID = "root";
builder.Password = "******";
var connectionString = builder.ToString();
Console.WriteLine(connectionString);
var db = SqlDb.Create <MySqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
});
//Number of rows affected
await db.ExecuteAsync("SELECT 'ZhangHe'");
var stringParam = "ZhangHe";
await db.ExecuteAsync("SELECT @String", new { String = stringParam });
await db.ExecuteAsync("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
await db.ExecuteFormatAsync($"SELECT {stringParam}");
//Model
var exe1 = await db.ExecuteAsync <string>("SELECT 'ZhangHe'");
var exe2 = await db.ExecuteAsync <string>("SELECT @String", new { String = stringParam });
var exe3 = await db.ExecuteAsync <string>("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
var exe4 = await db.ExecuteFormatAsync <string>($"SELECT {stringParam}");
(var name, var age) = await db.ExecuteAsync <string, int>("SELECT @Name;SELECT @Age", new { Name = stringParam, Age = int.MinValue });
(var names, var ages, var ids) = await db.ExecuteAsync <List <string>, List <int>, List <int> >("SELECT @Name;SELECT @Age;SELECT @Id", new { Id = 1024, Name = stringParam, Age = int.MinValue });
try { await db.ExecuteAsync("DROP TABLE `Custom`"); } catch { }
try { await db.ExecuteAsync("DROP TABLE `CustomDetail`"); } catch { }
try { await db.ExecuteAsync("DROP TABLE `Address`"); } catch { }
try { await db.ExecuteAsync("DROP TABLE `Order`"); } catch { }
await db.ExecuteAsync("CREATE TABLE `Custom`(`Id` int(11) NOT NULL AUTO_INCREMENT,`Name` varchar(500),`Age` int(11),PRIMARY KEY (`Id`))");
await db.ExecuteAsync("CREATE TABLE `CustomDetail`(`CustomId` int(11),`NullInt` int(11),`Bool` int(11),PRIMARY KEY (`CustomId`))");
await db.ExecuteAsync("CREATE TABLE `Address`(`OrderId` int(11),`Name` varchar(500),`Detail` varchar(500),`Mobile` varchar(500),`Sheng` varchar(500),`Shi` varchar(500),`Xian` varchar(500))");
await db.ExecuteAsync("CREATE TABLE `Order`(`Id` int(11) NOT NULL AUTO_INCREMENT,`Name` varchar(500),`Price` int(11),`CreateTime` datetime,`CustomId` int(11),`State` int(11),`JsonData` varchar(500),PRIMARY KEY (`Id`))");
//SqlDbExtensions.RegisterTable(type => type.Name);
//SqlDbExtensions.RegisterProperty(property => property.Name);
//SqlDbExtensions.RegisterIdentity
SqlDbExtensions.RegisterDbParameter <MySqlConnection, JsonData>((value) => {
return(JsonWriter.ToJson <JsonData>((JsonData)value));
});
//(reader,i)=field,(reader)=model Mapper
SqlDbExtensions.RegisterDbReader <MySqlDataReader, JsonData>((reader, i) => {
var json = reader.GetString(i);
return(JsonReader.FromJson <JsonData>(json));
});
SqlDbExtensions.RegisterDbMethod <MySqlConnection>((methods) => {
foreach (var item in methods)
{
var method = item.Key;
Console.WriteLine($"{method.DeclaringType}.{method.Name}()");
}
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus1"), (expr) => {
return(new object[] { "COUNT(", expr.Arguments[1], ")+1" });//string OR Expression
});
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus2"), (expr) => {
var exprObjs = new List <object>();//string OR Expression
exprObjs.Add("COUNT(");
exprObjs.Add(expr.Arguments[0]);
exprObjs.Add(")+2");
return(exprObjs);
});
});
SqlDbExtensions.RegisterDbMember <MySqlConnection>((members) => {
foreach (var item in members)
{
var member = item.Key;
Console.WriteLine($"{member.DeclaringType}.{member.Name}");
}
});
await InsertAsync(db);
await SelectAsync(db);
await UpdateAsync(db);
await DeleteAsync(db);
//Transaction
//out ITransactionFactory use AsyncLocal<>
//如果不介意性能 不需要分开定义 db,dbTx
//If you don't mind performance There is no need to define separately db,dbTx
var dbTx = SqlDb.Create <MySqlConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
}, out var txFactory);
await TransactionAsync(dbTx, txFactory);
}
public static async Task RunSQLite()//Better synchronization??
{
var builder = new SqliteConnectionStringBuilder();
builder.DataSource = "testdb";
var connectionString = builder.ToString();
Console.WriteLine(connectionString);
var db = SqlDb.Create <SqliteConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
});
//Number of rows affected
await db.ExecuteAsync("SELECT 'ZhangHe'");
var stringParam = "ZhangHe";
await db.ExecuteAsync("SELECT @String", new { String = stringParam });
await db.ExecuteAsync("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
await db.ExecuteFormatAsync($"SELECT {stringParam}");
//Model
var exe1 = await db.ExecuteAsync <string>("SELECT 'ZhangHe'");
var exe2 = await db.ExecuteAsync <string>("SELECT @String", new { String = stringParam });
var exe3 = await db.ExecuteAsync <string>("SELECT @String", new List <(string, object)>() { ("String", stringParam) });
var exe4 = await db.ExecuteFormatAsync <string>($"SELECT {stringParam}");
(var name, var age) = await db.ExecuteAsync <string, int>("SELECT @Name;SELECT @Age", new { Name = stringParam, Age = int.MinValue });
(var names, var ages, var ids) = await db.ExecuteAsync <List <string>, List <int>, List <int> >("SELECT @Name;SELECT @Age;SELECT @Id", new { Id = 1024, Name = stringParam, Age = int.MinValue });
try { await db.ExecuteAsync("DROP TABLE [Custom]"); } catch { }
try { await db.ExecuteAsync("DROP TABLE [CustomDetail]"); } catch { }
try { await db.ExecuteAsync("DROP TABLE [Address]"); } catch { }
try { await db.ExecuteAsync("DROP TABLE [Order]"); } catch { }
await db.ExecuteAsync("CREATE TABLE [Custom]([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Name] TEXT NULL,[Age] INTEGER NULL)");
await db.ExecuteAsync("CREATE TABLE [CustomDetail]([CustomId] INTEGER,[NullInt] INTEGER NULL,[Bool] INTEGER NULL)");
await db.ExecuteAsync("CREATE TABLE [Address]([OrderId] INTEGER NOT NULL,[Name] TEXT NULL,[Detail] TEXT NULL,[Mobile] TEXT NULL,[Sheng] TEXT NULL,[Shi] TEXT NULL,[Xian] TEXT NULL)");
await db.ExecuteAsync("CREATE TABLE [Order]([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Name] TEXT NULL,[Price] INTEGER NULL,[CreateTime] datetime NULL,[CustomId] INTEGER NULL,[State] INTEGER NULL,[JsonData] TEXT NULL)");
//SqlDbExtensions.RegisterTable(type => type.Name);
//SqlDbExtensions.RegisterProperty(property => property.Name);
//SqlDbExtensions.RegisterIdentity
SqlDbExtensions.RegisterDbParameter <SqliteConnection, JsonData>((value) => {
return(JsonWriter.ToJson <JsonData>((JsonData)value));
});
//(reader,i)=field,(reader)=model Mapper
SqlDbExtensions.RegisterDbReader <SqliteDataReader, JsonData>((reader, i) => {
var json = reader.GetString(i);
return(JsonReader.FromJson <JsonData>(json));
});
SqlDbExtensions.RegisterDbMethod <SqliteConnection>((methods) => {
foreach (var item in methods)
{
var method = item.Key;
Console.WriteLine($"{method.DeclaringType}.{method.Name}()");
}
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus1"), (expr) => {
return(new object[] { "COUNT(", expr.Arguments[1], ")+1" });//string OR Expression
});
methods.Add(typeof(MyExpressionEx).GetMethod("CountPlus2"), (expr) => {
var exprObjs = new List <object>();//string OR Expression
exprObjs.Add("COUNT(");
exprObjs.Add(expr.Arguments[0]);
exprObjs.Add(")+2");
return(exprObjs);
});
});
SqlDbExtensions.RegisterDbMember <SqliteConnection>((members) => {
foreach (var item in members)
{
var member = item.Key;
Console.WriteLine($"{member.DeclaringType}.{member.Name}");
}
});
await InsertAsync(db);
await SelectAsync(db);
await UpdateAsync(db);
await DeleteAsync(db);
//Transaction
//out ITransactionFactory use AsyncLocal<>
//如果不介意性能 不需要分开定义 db,dbTx
//If you don't mind performance There is no need to define separately db,dbTx
var dbTx = SqlDb.Create <SqliteConnection>(connectionString, (cmd) => {
Console.WriteLine(cmd.CommandText);
}, out var txFactory);
await TransactionAsync(dbTx, txFactory);
}