public IEnumerable <DocumentViewModel> FilterFilesByGuid(string currentSid)
{
_logger.LogDebug($"Return files from database by current sid");
Select select = Qb.Select(nameof(Document.path), nameof(Document.upload_time_stamp), nameof(Document.short_name), nameof(Document.picture), nameof(Document.doc_id))
.From(Tables.Document)
.Where(
Cond.Equal(nameof(Document.user_sid), currentSid),
Cond.Equal(nameof(Document.type), Convert.ToByte(IO.FileExtension.Extensions.Pdf))
);
IEnumerable <DocumentViewModel> documents;
try
{
using (AnyDbConnection connection = _factory.OpenConnection())
{
return(documents = connection.Query <DocumentViewModel>(select));
}
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
}
_logger.LogDebug($"FileDbService.FilterFilesByGuid....OK");
return(null);
}
public IQueryBuilder QueryIds <T>(List <T> rs) where T : IDataObject
{
var id = ReflectionTool.FieldsAndPropertiesOf(typeof(T)).FirstOrDefault(f => f.GetCustomAttribute <PrimaryKeyAttribute>() != null);
var rid = ReflectionTool.FieldsAndPropertiesOf(typeof(T)).FirstOrDefault(f => f.GetCustomAttribute <ReliableIdAttribute>() != null);
return(Qb.Fmt($"SELECT {id.Name}, {rid.Name} FROM {typeof(T).Name} WHERE") + Qb.In(rid.Name, rs, i => i.RID));
}
void TestPaging()
{
Select sel = Qb.Select("*")
.From("AE", "ae")
.OrderBy("AE_ID")
.Page(2, 10);
Renderer.MySqlRenderer my = new Renderer.MySqlRenderer();
string sql = my.RenderSelect(sel);
From u = From.Table("unit", "u", "nsi");
sel = Qb.Select("*")
.From(u)
.OrderBy("id")
.Page(2, 10);
Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer();
sql = pg.RenderSelect(sel);
Renderer.SqLiteRenderer lite = new Renderer.SqLiteRenderer();
sql = lite.RenderSelect(sel);
Renderer.SqlServerRenderer ms = new Renderer.SqlServerRenderer();
sql = ms.RenderSelect(sel);
Renderer.OracleRenderer ora = new Renderer.OracleRenderer();
sql = ora.RenderSelect(sel);
}
static string GetUpdateSql(AnyDbConnection cnn, Update query)
{
ISqlOmRenderer renderer = Qb.CreateRenderer(cnn.DatabaseProvider);
string sql = renderer.RenderUpdate(query);
return(sql);
}
static string GetInsertSelectSql(AnyDbConnection cnn, InsertSelect query)
{
ISqlOmRenderer renderer = Qb.CreateRenderer(cnn.DatabaseProvider);
string sql = renderer.RenderInsertSelect(query);
return(sql);
}
public IQueryBuilder OnInsertSubQuery(Type t, MemberInfo mi)
{
return(Qb.Fmt($@"(
SELECT a.{mi.Name} + 1 as ICount From {t.Name} a
ORDER BY a.{mi.Name} DESC
LIMIT 1
)"));
}
public void TestFrom()
{
From f = From.Table("a", "b", "dbo");
f = From.SubQuery(Qb.Select("a"), "t");
Union u = Qb.Union();
f = From.Union(u, "a");
}
public IQueryBuilder QueryIds <T>(List <T> rs) where T : IDataObject
{
var type = rs.FirstOrDefault()?.GetType() ?? typeof(T);
var id = FiTechBDadosExtensions.IdColumnOf[type];
var rid = FiTechBDadosExtensions.RidColumnOf[type];
var ridType = ReflectionTool.GetTypeOf(ReflectionTool.FieldsAndPropertiesOf(type).FirstOrDefault(x => x.GetCustomAttribute <ReliableIdAttribute>() != null));
return(Qb.Fmt($"SELECT {id} AS Id, {rid} AS RID FROM {type.Name} WHERE") + Qb.In(rid, rs, i => Convert.ChangeType(i.RID, ridType)));
}
public void TestExpr()
{
Expr e = Expr.Field("a");
e = Expr.Field("a", From.Table("tab"));
e = Expr.Function(AggFunc.Max, Expr.Field("a"));
e = Expr.IfNull(Expr.Field("a"), Expr.Param("p"));
e = Expr.SubQuery(Qb.Select("a"));
}
public object ExecuteScalar(Select query)
{
ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);
_dbCommand.CommandText = render.RenderSelect(query);
_dbCommand.CommandType = CommandType.Text;
FillParameters(_dbCommand, query.Query.CommandParams, render);
return(ExecuteScalar());
}
void TestNotIn()
{
Select sel = Qb.Select("*")
.From("AE", "ae")
.Where(Cond.NotIn(Expr.Field("f"), 1, 2, 3, 4));
Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer();
string sql = pg.RenderSelect(sel);
}
public int ExecuteNonQuery(Update query)
{
ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);
_dbCommand.CommandText = render.RenderUpdate(query);
_dbCommand.CommandType = CommandType.Text;
FillParameters(_dbCommand, query.Query.CommandParams, render);
return(ExecuteNonQuery());
}
public DbDataReader ExecuteReader(Select query, CommandBehavior behavior)
{
ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);
_dbCommand.CommandText = render.RenderSelect(query);
_dbCommand.CommandType = CommandType.Text;
FillParameters(_dbCommand, query.Query.CommandParams, render);
return(ExecuteReader(behavior));
}
public IQueryBuilder GenerateGetStateChangesQuery(List <Type> workingTypes, Dictionary <Type, MemberInfo[]> fields, DateTime moment)
{
var dataLen = fields.Max(f => f.Value.Length);
Qb query = new Qb("");
workingTypes.ForEachIndexed((type, ti) => {
var cTimeField = fields[type].FirstOrDefault(f => f.GetCustomAttribute <CreationTimeStampAttribute>() != null);
var uTimeField = fields[type].FirstOrDefault(f => f.GetCustomAttribute <UpdateTimeStampAttribute>() != null);
query.Append($"(SELECT \r\n\t'{type.Name}' AS TypeName, ");
for (int i = 0; i < dataLen; i++)
{
if (fields[type].Length > i)
{
query.Append($"\r\n\tCAST({fields[type][i].Name} AS BINARY)");
}
else
{
query.Append("\r\n\tNULL");
}
if (ti == 0)
{
query.Append($"AS data_{i}");
}
if (i < dataLen - 1)
{
query.Append(",");
}
}
query.Append("\r\nFROM");
query.Append(type.Name);
if (moment != DateTime.MinValue && (cTimeField != null || uTimeField != null))
{
query.Append("\r\nWHERE");
if (cTimeField != null)
{
query.Append($"{cTimeField.Name} > @m", moment);
}
if (cTimeField != null && uTimeField != null)
{
query.Append("OR");
}
if (uTimeField != null)
{
query.Append($"{uTimeField.Name} > @m", moment);
}
}
query.Append(")\r\n");
if (ti < workingTypes.Count - 1)
{
query.Append("UNION ALL\r\n");
}
});
return(query);
}
public IQueryBuilder OnInsertSubQuery(Type t, MemberInfo mi)
{
return(Qb.Fmt($@"(
SELECT a.{mi.Name} + 1 as PCount From {t.Name} a
LEFT JOIN {t.Name} b
ON b.{mi.Name} = a.{mi.Name} + 1
WHERE b.{mi.Name} IS NULL
LIMIT 1
)"));
}
public void TestJoin()
{
Select sel = Qb.Select("a").From("lt").Join("rt", JoinCond.Fields("id_0"));
From lt = From.Table("lt");
From rt = From.Table("rt");
Logic l = Logic.And(Cond.Equal(Expr.Field("id_0", lt), Expr.Field("id_0", rt)));
sel = Qb.Select("a").From("lt").Join(JoinType.Left, lt, rt, l);
}
public void SelectTest()
{
Select sel = Qb.Select("*")
.From("customer").OrderBy("id");
using (AnyDbConnection con = _factory.OpenConnection())
{
var res = con.Query(sel);
Assert.Equal(100, res.Count());
}
}
public void TestCond()
{
Cond c = Cond.Equal(Expr.Field("a"), Expr.Param("p"));
c = Cond.Like(Expr.Field("a"), Expr.Param("p"), '!');
c = Cond.Like("a", "%");
c = Cond.In(Expr.Field("a"), Qb.Select("a"));
c = Cond.IsNull(Expr.Field("a"));
c = Cond.Between(Expr.Field("a"), Expr.Param("p1"), Expr.Param("p2"));
c = Cond.Exists(Qb.Select("a"));
}
public void TestDelete()
{
Delete del = Qb.Delete("Customers")
.Where(Cond.Equal("Id", 20));
Renderer.SqlServerRenderer renderer = new Renderer.SqlServerRenderer();
string sql = renderer.RenderDelete(del);
del = Qb.Delete("Customers", "nsi")
.Where(Cond.Equal("Id", 20));
sql = renderer.RenderDelete(del);
}
public void GetPageSqlTest()
{
Select sel = Qb.Select("*")
.From("customer").OrderBy("id").Page(1, 10);
string sql = _factory.GetSql(sel);
using (AnyDbConnection con = _factory.OpenConnection())
{
var res = con.Query(sel);
Assert.Equal(10, res.Count());
}
}
public int ExecuteNonQuery(Delete query)
{
ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);
_dbCommand.CommandText = render.RenderDelete(query);
_dbCommand.CommandType = CommandType.Text;
FillParameters(_dbCommand, query.Query.CommandParams, render);
using (SqlStopwatch sw = new SqlStopwatch(this))
{
return(ExecuteNonQuery());
}
}
public long ExecuteNonQuery(Insert query)
{
ISqlOmRenderer render = Qb.CreateRenderer(_anyConnection.DatabaseProvider);
_dbCommand.CommandText = render.RenderInsert(query);
_dbCommand.CommandType = CommandType.Text;
FillParameters(_dbCommand, query.Query.CommandParams, render);
if (!string.IsNullOrEmpty(query.Query.IdentityField))
{
return(Convert.ToInt64(ExecuteScalar()));
}
return(ExecuteNonQuery());
}
void TestLong()
{
Select sel = Qb.Select("*")
.From("tab")
.Where(Cond.Equal("col", DateTime.Now.Ticks));
AnyDbFactory factory = new AnyDbFactory(new AnyDbSetting());
string sql = factory.GetSql(sel);
Update upd = Qb.Update("tab")
.Values(
Value.New("col", DateTime.Now.Ticks)
);
sql = factory.GetSql(upd);
}
public static Qb ListRids <T>(this List <T> me) where T : IDataObject, new()
{
Qb retv = new Qb();
for (int i = 0; i < me.Count; i++)
{
retv.Append($"@{++upseq}_{i}", me[i].RID);
if (i < me.Count - 1)
{
retv.Append(",");
}
}
return(retv);
}
public IQueryBuilder GenerateMultiInsert <T>(List <T> inputRecordset, bool OmmitPk = true) where T : IDataObject
{
var t = inputRecordset.FirstOrDefault()?.GetType();
if (t == null)
{
return(Qb.Fmt("SELECT 1"));
}
List <T> workingSet = new List <T>();
workingSet.AddRange(inputRecordset.Where((r) => !r.IsPersisted));
if (workingSet.Count < 1)
{
return(null);
}
// --
QueryBuilder Query = new QueryBuilder();
Query.Append($"INSERT INTO {t.Name} (");
Query.Append(GenerateFieldsString(t, OmmitPk));
Query.Append(") VALUES");
// --
for (int i = 0; i < workingSet.Count; i++)
{
Query.Append("(");
Query.Append(GenerateValuesString(workingSet[i], OmmitPk));
Query.Append(")");
if (i < workingSet.Count - 1)
{
Query.Append(",");
}
}
// --
//Query.Append("ON DUPLICATE KEY UPDATE ");
//var Fields = GetMembers(typeof(T)).Where(
// field=>
// (OmmitPk || field.GetCustomAttribute<PrimaryKeyAttribute>() == null) &&
// field.GetCustomAttribute<ReliableIdAttribute>() == null
//).ToList();
//for (int i = 0; i < Fields.Count; ++i) {
// Query.Append($"{Fields[i].Name} = VALUES({Fields[i].Name})");
// if (i < Fields.Count - 1) {
// Query.Append(",");
// }
//}
// --
return(Query);
}
public override double Intersect(BasinBase otherBasin)
{
var other = (MeridianBase)otherBasin;
var Qt = new Vector2D(
other.Q.X * Math.Cos(Lambda.Value - otherBasin.Lambda.Value),
other.Q.Y);
Beta_traverse = Qt.AngleTo(Qb /*or Q?*/.ToVector2D()).Radians;
return(Triangles.SinusesTheorem(
Math.PI / 2 + Delta_g_meridian,
r,
Beta_traverse)
-
//r;
otherBasin.r); //there's deformation
}
public void TestUpdate()
{
Update upd = Qb.Update("Customers")
.Values(
Value.New("LastName", "Pavlov")
)
.Where(Cond.Equal("FirstName", "Pavel"));
Renderer.SqlServerRenderer renderer = new Renderer.SqlServerRenderer();
string sql = renderer.RenderUpdate(upd);
upd = Qb.Update("Customers", "rem")
.Values(
Value.New("LastName", "Pavlov")
)
.Where(Cond.Equal("FirstName", "Pavel"));
sql = renderer.RenderUpdate(upd);
}
public void TestInsert()
{
Insert ins = Qb.Insert("Customers")
.Values(
Value.New("FirstName", "Pavel"),
Value.New("LastName", "Pavel")
);
Renderer.SqlServerRenderer renderer = new Renderer.SqlServerRenderer();
string sql = renderer.RenderInsert(ins);
ins = Qb.Insert("Customers", "rem")
.Values(
Value.New("FirstName", "Pavel"),
Value.New("LastName", "Pavel")
);
sql = renderer.RenderInsert(ins);
}
public void Test()
{
string str = System.IO.File.ReadAllText("select.json");
SelectQuery selectQuery = JsonConvert.DeserializeObject <SelectQuery>(str);
for (int i = 0; i < queryes.Length; i++)
{
string line = lines[i];
SelectQuery net_sel = Qb.GetQueryObject(queryes[i]);
string net_json = JsonConvert.SerializeObject(net_sel);
SelectQuery from_js_sel = JsonConvert.DeserializeObject <SelectQuery>(line);
string from_js_json = JsonConvert.SerializeObject(from_js_sel);
Assert.Equal(net_json, from_js_json);
}
}
public void ExecuteAsyncTest()
{
using (AnyDbConnection con = _factory.OpenConnection())
{
Insert ins = Qb.Insert("customer")
.Values(
Value.New("first_name", "123"),
Value.New("last_name", "456")
);
con.ExecuteAsync(ins).Wait();
Assert.Equal(101, GetAll(con).Count());
ins = Qb.Insert("customer")
.Values(
Value.New("first_name", "321"),
Value.New("last_name", "654")
);
con.ExecuteAsync(ins).Wait();
Assert.Equal(102, GetAll(con).Count());
Select sel_0 = Qb.Select("*").From("customer").Page(0, 200);
sel_0 = sel_0.OrderBy("first_name");
var page = con.QueryAsync(sel_0).Result;
Assert.Equal(102, page.Count());
Update upd = Qb.Update("customer")
.Values(
Value.New("first_name", "XXX")
);
int res = con.ExecuteAsync(upd).Result;
Assert.Equal(102, res);
Select sel = Qb.Select("*")
.From("customer");
IEnumerable <Customer> en = con.QueryAsync <Customer>(sel).Result;
Assert.Equal(102, en.Count());
Delete del = Qb.Delete("customer");
res = con.ExecuteAsync(del).Result;
Assert.Equal(102, res);
}
}
