public void Query_Limit()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.IntKey > 3).Select(t => new { t.IntKey, t.StringKey }).OrderByDescending(t => t.IntKey).Limit(30).ToList();
Assert.Equal(30, re.Count);
}
}
public void Query_Select()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.IntKey <= 3).Select(t => new { t.IntKey, t.StringKey }).ToList();
Assert.Equal(3, re.Count);
}
}
public void Query_OrderBy()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.IntKey <= 9).Select(t => new { t.IntKey, t.StringKey }).OrderByDescending(t => t.IntKey).ToList();
Assert.True(re.Count == 9 && re.First().IntKey == 9 && re.First().Id == 0);//没有查id，id应该=0
}
}
public void Query_MultiWhere()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.StringKey.Contains("3")).Where(t => t.IntKey == 3).ToList();
Assert.Single(re);
}
}
public void Query_Where()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.StringKey.EndsWith("3")).ToList();
Assert.Equal(100, re.Count);
}
}
public void Query_All()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().ToList();
Assert.Equal(1000, re.Count);
}
}
public void Query_SQL_FirstOrDefault()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>($"SELECT * FROM TTTTTest LIMIT 1").FirstOrDefault();
Assert.Equal("SELECT * FROM TTTTTest LIMIT 1", db.SqlStatement);
}
}
public void Query_ToList()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().ToList();
Assert.Equal("SELECT * FROM OperateTest t WHERE 1=1", db.SqlStatement);
Assert.Equal(new string[0], db.Parameters.Keys.ToArray());
}
}
public void Query_Any()
{
using (var db = new ApiDb())
{
var re = db.Queryable <OperateTestModel>().Where(t => t.StringKey.EndsWith("3")).Any();
//db.SqlStatement = "SELECT COUNT(0) FROM OperateTest t WHERE ( 1=1 ) AND (t.StringKey LIKE @tStringKey)";
Assert.True(re);
}
}
public void Query_Paging()
{
using (var db = new ApiDb())
{
var re4 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.Contains("1")).Select(t => new { t.IntKey, t.StringKey }).OrderBy(t => t.IntKey).Paging(0, 10).ToList();
var re5 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.Contains("1")).Select(t => new { t.IntKey, t.StringKey }).OrderByDescending(t => t.IntKey).Paging(0, 10).ToList();
var re6 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.Contains("1")).Select(t => new { t.IntKey, t.StringKey }).OrderBy(t => t.IntKey).Paging(1, 10).ToList();
Assert.True(re4.Count == re5.Count && re5.Count == re6.Count && re6.Count == re4.Count);
}
}
protected Behavior(Worker worker, ApiDb db)
{
if (worker == null || db == null)
{
throw new ArgumentException();
}
Worker = worker;
_db = db;
}
public void Query_Paging()
{
using (var db = new ApiDb())
{
db.OperationTests.Where(t => t.StringKey.Contains("1")).Select(t => new { t.IntKey, t.StringKey }).OrderBy(t => t.IntKey).Paging(0, 10).ToList();
Assert.Equal("SELECT t.IntKey,t.StringKey FROM OperateTest t WHERE ( 1=1 ) AND (t.StringKey LIKE @tStringKey) ORDER BY t.IntKey ASC LIMIT 0,10", db.SqlStatement);
Assert.Equal(new[] { "@tStringKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "%1%" }, db.Parameters.Values.ToArray());
}
}
public void Query_MultiWhere()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().Where(t => t.StringKey.Contains("3")).Where(t => t.IntKey == 3).ToList();
Assert.Equal("SELECT * FROM OperateTest t WHERE (( 1=1 ) AND (t.StringKey LIKE @tStringKey)) AND (t.IntKey = @tIntKey)", db.SqlStatement);
Assert.Equal(new[] { "@tStringKey", "@tIntKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "%3%", "3" }, db.Parameters.Values.ToArray());
}
}
public void Query_Limit()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().Where(t => t.IntKey > 3).Select(t => new { t.IntKey, t.StringKey }).OrderByDescending(t => t.IntKey).Limit(30).ToList();
Assert.Equal("SELECT TOP 30 t.IntKey,t.StringKey FROM OperateTest t WHERE ( 1=1 ) AND (t.IntKey > @tIntKey) ORDER BY t.IntKey DESC", db.SqlStatement);
Assert.Equal(new[] { "@tIntKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "3" }, db.Parameters.Values.ToArray());
}
}
public void Query_Select()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().Where(t => t.IntKey <= 3).Select(t => new { t.IntKey, t.StringKey }).ToList();
Assert.Equal("SELECT t.IntKey,t.StringKey FROM OperateTest t WHERE ( 1=1 ) AND (t.IntKey <= @tIntKey)", db.SqlStatement);
Assert.Equal(new[] { "@tIntKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "3" }, db.Parameters.Values.ToArray());
}
}
public void Query_Paging()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().Where(t => t.StringKey.Contains("1")).Select(t => new { t.IntKey, t.StringKey }).OrderBy(t => t.IntKey).Paging(0, 10).ToList();
Assert.Equal("SELECT TOP 10 TTTTTT.IntKey,TTTTTT.StringKey FROM (SELECT ROW_NUMBER() OVER ( ORDER BY t.IntKey ASC) AS RowNumber,t.IntKey,t.StringKey FROM OperateTest t WHERE ( 1=1 ) AND (t.StringKey LIKE @tStringKey)) AS TTTTTT WHERE RowNumber > -10", db.SqlStatement);
Assert.Equal(new[] { "@tStringKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "%1%" }, db.Parameters.Values.ToArray());
}
}
public void Query_Any()
{
using (var db = new ApiDb())
{
db.OperationTests.Where(t => t.StringKey.EndsWith("3")).Any();
Assert.Equal("SELECT 1 FROM OperateTest t WHERE ( 1=1 ) AND (t.StringKey LIKE @tStringKey) LIMIT 1", db.SqlStatement);
Assert.Equal(new[] { "@tStringKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "%3" }, db.Parameters.Values.ToArray());
}
}
public void Query_ToDataSet()
{
using (var db = new ApiDb())
{
db.Queryable <OperationTest>().Where(t => t.StringKey.EndsWith("3")).ToDataSet();
Assert.Equal("SELECT * FROM OperateTest t WHERE ( 1=1 ) AND (t.StringKey LIKE @tStringKey)", db.SqlStatement);
Assert.Equal(new[] { "@tStringKey" }, db.Parameters.Keys.ToArray());
Assert.Equal(new[] { "%3" }, db.Parameters.Values.ToArray());
}
}
public void Persistence()
{
using (var db = new ApiDb())
{
int value = 999999;
//初次查询没有数据
var re = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Null(re);
//add一条数据
OperateTestModel model = new OperateTestModel
{
IntKey = value,
StringKey = "AddTest"
};
model.IntKey = value;
db.Add <OperateTestModel>(model);
//插入后查询有一条记录
var re1 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Single(re1);
Assert.Equal(value, re1.First().IntKey);
//查询一条
var entity = db.Queryable <OperateTestModel>().Where(t => t.IntKey == value).ToOne();
Assert.NotNull(entity);
Assert.Equal(value, entity.IntKey);
//更新数据
//entity.Id = value; //自增的主键不应该被修改,如果用这种方式进行修改，给Id赋值就会导致修改不成功，因为条件是用第一个主键作为标识修改的
entity.Key2 = value;
entity.StringKey = $"UpdateTest_{value}";
entity.IntNullKey = value;
entity.DateTimeNullKey = DateTime.Now;
entity.DateNullKey = DateTime.Now.Date;
entity.DoubleNullKey = entity.IntNullKey;
entity.FloatNullKey = entity.IntNullKey;
db.Update <OperateTestModel>(entity);
var entity2 = db.Queryable <OperateTestModel>().Where(t => t.IntKey == value).ToOne();
Assert.NotNull(entity2);
Assert.Equal(value, entity2.IntNullKey);
Assert.Equal($"UpdateTest_{value}", entity2.StringKey);
//删除数据
db.Delete <OperateTestModel>(t => t.IntKey == value);
//删除后查询没有
var re4 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Null(re4);
}
}
public void DeleteAsync()
{
using (var db = new ApiDb())
{
var a = db.DeleteAsync(new OperationTest
{
Id = 1
});
Assert.Equal("DELETE t FROM OperateTest t WHERE t.Id = @tId", db.SqlStatement);
Assert.Equal(new[] { "@tId" }, db.Parameters.Keys.ToArray());
}
}
public DataManager(string login, string password)
{
var _db = new ApiDb($"{Directory.GetCurrentDirectory()}/configs/db.json");
#if DEBUG
try
{
_db.AddNewWorker(new Worker
{
FirstName = "A1",
MiddleName = "AA1",
LastName = "AAA1",
Login = "******",
Password = "******",
Role = Role.Technician
});
_db.AddNewWorker(new Worker
{
FirstName = "A2",
MiddleName = "AA2",
LastName = "AAA2",
Login = "******",
Password = "******",
Role = Role.Technician
});
_db.AddNewWorker(new Worker
{
FirstName = "A3",
MiddleName = "AA3",
LastName = "AAA3",
Login = "******",
Password = "******",
Role = Role.Manager
});
}
catch
{
}
#endif
auth = new SimpleAuthenticator(_db);
if (!auth.Verify(login, password))
{
throw new AuthenticationException("Неверный логин или пароль");
}
_bh = new BehaviorFactory().Factory(login, _db);
}
public Basic.Behavior Factory(Worker worker, ApiDb db)
{
if (worker == null)
{
throw new ArgumentNullException(nameof(worker));
}
if (db == null)
{
throw new ArgumentNullException(nameof(db));
}
return(worker.Role == Role.Manager
? new ManagerBehavior(worker, db) as Basic.Behavior
: new TechnicianBehavior(worker, db) as Basic.Behavior);
}
private void txtSql_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.KeyCode == Keys.F5)
{
ApiDb cad = null;
Db db = (Db)listDb.SelectedItem;
if (db != null)
{
foreach (ApiDb ad in _apiObj.db)
{
if (ad.db.id == db.id)
{
cad = ad;
break;
}
}
if (cad == null)
{
cad = new Entity.ApiDb()
{
db = db,
strSql = txtSql.Text
};
_apiObj.db.Add(cad);
}
}
Dictionary <string, string> apd = new Dictionary <string, string>();
foreach (ApiParam ap in _apiObj.param)
{
apd.Add(ap.key, ap.defValue);
}
dgData.DataSource = cad.Exec(apd);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void UpdateAsync()
{
using (var db = new ApiDb())
{
var a = db.UpdateAsync(new OperationTest
{
Id = 1,
Key2 = 1,
IntKey = 1,
StringKey = "1",
IntNullKey = null,
FloatNullKey = 1,
DoubleNullKey = 1,
DateNullKey = DateTime.MinValue,
DateTimeNullKey = null
});
Assert.Equal("UPDATE t SET Key2=@tKey2,StringKey=@tStringKey,IntKey=@tIntKey,IntNullKey=@tIntNullKey,DateNullKey=@tDateNullKey,DateTimeNullKey=@tDateTimeNullKey,DoubleNullKey=@tDoubleNullKey FROM OperateTest t WHERE t.Id = @tId", db.SqlStatement);
Assert.Equal(new[] { "@tId", "@tKey2", "@tStringKey", "@tIntKey", "@tIntNullKey", "@tDateNullKey", "@tDateTimeNullKey", "@tDoubleNullKey" }, db.Parameters.Keys.ToArray());
}
}
public void AddAsync()
{
using (var db = new ApiDb())
{
var a = db.AddAsync(new OperationTest
{
Id = 1,
Key2 = 1,
IntKey = 1,
StringKey = "1",
IntNullKey = null,
FloatNullKey = 1,
DoubleNullKey = 1,
DateNullKey = DateTime.MinValue,
DateTimeNullKey = null
});
Assert.Equal("INSERT INTO OperateTest (Key2,StringKey,IntKey,IntNullKey,DateNullKey,DateTimeNullKey,DoubleNullKey) VALUES (@Key2,@StringKey,@IntKey,@IntNullKey,@DateNullKey,@DateTimeNullKey,@DoubleNullKey)", db.SqlStatement);
Assert.Equal(new[] { "@Key2", "@StringKey", "@IntKey", "@IntNullKey", "@DateNullKey", "@DateTimeNullKey", "@DoubleNullKey" }, db.Parameters.Keys.ToArray());
}
}
public Basic.Behavior Factory(string login, ApiDb db)
{
if (db == null)
{
throw new ArgumentNullException(nameof(db));
}
if (login == null)
{
throw new ArgumentNullException(nameof(login));
}
var temp = db.FindWorkerByLogin(login);
if (temp == null)
{
throw new ArgumentException();
}
return(Factory(temp, db));
}
public void Persistence_DeleteEntity()
{
using (var db = new ApiDb())
{
int value = 999999;
//初次查询没有数据
var re = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Null(re);
//add一条数据
OperateTestModel model = new OperateTestModel
{
IntKey = value,
StringKey = "AddTest"
};
model.IntKey = value;
db.Add <OperateTestModel>(model);
//插入后查询有一条记录
var re1 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Single(re1);
Assert.Equal(value, re1.First().IntKey);
var entity = db.Queryable <OperateTestModel>().Where(t => t.IntKey == value).ToOne();
Assert.NotNull(entity);
Assert.Equal(value, entity.IntKey);
//删除数据
db.Delete <OperateTestModel>(entity);
//删除后查询没有
var re4 = db.Queryable <OperateTestModel>().Where(t => t.StringKey.StartsWith("AddTest")).ToList();
Assert.Null(re4);
}
}
protected Authenticator([NotNull] ApiDb db)
{
_db = db ?? throw new ArgumentException();
}
public SimpleAuthenticator(ApiDb apiDb) : base(apiDb)
{
}
public Query(ApiDb db)
{
_db = db;
}
