DbInstance Prepare()
{
var db = new DbInstance("MyDatabase3");
db.Map<AData, PrototypeBasedData>().Automap(i => i.Id, true);
db.Initialize();
return db;
}
public void Indexing3()
{
var db = new DbInstance(@"MyDatabase3\Indexing");
db.Map<AData, PrototypeBasedData>().Automap(i => i.Id, true)
.WithIndex("LastName", i => i.Name, StringComparer.CurrentCulture)
.WithIndex("LastNameText", i => i.Name, StringComparer.CurrentCultureIgnoreCase);
db.Initialize();
var table = db.Table<AData>();
table.Purge();
db.BulkWrite(() =>
{
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new PrototypeBasedData { Name = "Test" + i });
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new PrototypeBasedData { Name = "TeST" + i });
});
var list1count = table.IndexQueryByKey("LastName", "Test5").Count();
var list2count = table.IndexQueryByKey("LastNameText", "TEst5").Count();
Assert.AreEqual(list1count, 100);
Assert.AreEqual(list2count, 200);
}
DbInstance Prepare()
{
var db = new DbInstance("MyDatabase");
db.Map<MyData>().Automap(i => i.Id, true);
db.Initialize();
return db;
}
DbInstance Prepare()
{
var db = new DbInstance("MyDatabase2");
db.Map<IData, InterfaceBasedData>().Automap(i => i.Id, true);
db.Initialize();
return db;
}
public void Indexing2()
{
var db = new DbInstance(@"MyDatabase2\Indexing");
db.Map<IData, InterfaceBasedData>().Automap(i => i.Id, true)
.WithIndex("LastName", i => i.Name, StringComparer.CurrentCulture)
.WithIndex("LastNameText", i => i.Name, StringComparer.CurrentCultureIgnoreCase);
db.Initialize();
var table = db.Table<IData>();
table.Purge();
db.BulkWrite(() =>
{
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new InterfaceBasedData { Name = "Test" + i });
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new InterfaceBasedData { Name = "TeST" + i });
});
var list1 = table.LoadAll("LastName", "Test5");
var list2 = table.LoadAll("LastNameText", "TEst5");
Assert.AreEqual(list1.Count, 100);
Assert.AreEqual(list2.Count, 200);
}
public Program()
{
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<MyData>();
}
public void PurgeDb()
{
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<IData>();
}
static DbInstance OpenInstance()
{
var result = new DbInstance("test.db");
result.Map<Person>().Automap(i => i.Id).
WithIndex("FullName", i => i.FullName).
WithIndex("LastName", i => i.LastName);
result.Initialize();
return result;
}
public void Dispose()
{
if (!_isDisposed)
{
_isDisposed = true;
_db.Dispose();
_db = null;
}
}
private void Init()
{
if (_db == null)
{
//cerate
_db = new DbInstance("openhab.database", ApplicationData.Current.LocalFolder);
//setup
_db.Map<Setup>().Automap(i => i.Id);
//start
_db.Initialize();
}
}
static Dal()
{
// Create database
db = new DbInstance("Storage", ApplicationData.Current.RoamingFolder);
// Define table mapping
// * 1st parameter is primary key
// * 2nd parameter is autoGen e.g. auto-increment
db.Map<VintageMuscleCar>().Automap(p => p.Id, true);
// Initialize database
db.Initialize();
}
public void PurgeDb()
{
try
{
Extender.RegisterType<Point, PointSerializer>(2000);
}
catch { }
using (var i = Prepare())
i.Purge();
db = Prepare();
table = db.Table<IData>();
}
// [ExpectedException(typeof(InvalidOperationException))]
public void DoubleOpenDbComplexPath()
{
try
{
var db = new DbInstance(@"My Database\My Schema");
db.Initialize();
db.Initialize();
Assert.Fail("InvalidOperationException expected");
}
catch (InvalidOperationException)
{
}
}
public void OpenDbComplexPath2()
{
try
{
var db = new DbInstance(@"d:\test.db");
db.Initialize();
}
#if SILVERLIGHT
catch (System.IO.IsolatedStorage.IsolatedStorageException)
{
// SL without ElevatedPriviliges does not allow absolute path access
}
#endif
finally
{
}
}
public void Initialize(DbInstance db)
{
// simple or already known type
DbType = DbTypes.TryGetDbType(MemberType);
if (DbType != null)
return;
// reference
{
var key = db.GetKeyType(MemberType);
if (key != null)
{
DbType = DbTypes.GetDbType(key);
return;
}
}
// dictionary of references
{
var elements = DbTypes.GetDictionaryElementTypes(MemberType);
if (elements != null)
{
var key = db.GetKeyType(elements.Item1) ?? elements.Item1;
var value = db.GetKeyType(elements.Item2) ?? elements.Item2;
DbType = DbTypes.GetDbType(typeof(Dictionary<,>).MakeGenericType(key, value));
return;
}
}
// collection of references
{
var element = DbTypes.GetCollectionElementType(MemberType);
if (element != null)
{
var key = db.GetKeyType(element);
if (key != null)
{
DbType = DbTypes.GetDbType(key.MakeArrayType());
return;
}
}
}
throw new NotSupportedException(string.Format("Serialization of '{0}' is not supported", MemberType));
}
public void Indexing2()
{
var db = new DbInstance(@"MyDatabase2\Indexing");
db.Map<IData, InterfaceBasedData>().Automap(i => i.Id, true)
.WithIndex("LastName", i => i.Name, StringComparer.CurrentCulture)
.WithIndex("LastNameText", i => i.Name, StringComparer.CurrentCultureIgnoreCase);
db.Initialize();
var table = db.Table<IData>();
table.Purge();
db.BulkWrite(() =>
{
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new InterfaceBasedData { Name = "Test" + i });
for (var s = 0; s < 100; s++)
for (var i = 0; i < 10; i++)
table.Save(new InterfaceBasedData { Name = "TeST" + i });
});
var list1count = table.IndexQuery<string>("LastName").Key("Test5").Count();
var list2count = table.IndexQuery<string>("LastNameText").Key("TEst5").Count();
Assert.AreEqual(list1count, 100);
Assert.AreEqual(list2count, 200);
var list3count = table.IndexQuery<string>("LastName").GreaterThan("Test5").Count();
var list4count = table.IndexQuery<string>("LastName").LessThan("Test6").Count();
var list5count = table.IndexQuery<string>("LastName").LessThan("Test6").GreaterThan("Test5").Count();
Assert.AreEqual(900, list3count);
Assert.AreEqual(1200, list4count);
Assert.AreEqual(100, list5count);
var list6count = table.IndexQuery<string>("LastName").GreaterThan("Test5", true).Count();
var list7count = table.IndexQuery<string>("LastName").LessThan("Test6", true).Count();
var list8count = table.IndexQuery<string>("LastName").LessThan("Test6", true).GreaterThan("Test5", true).Count();
Assert.AreEqual(1000, list6count);
Assert.AreEqual(1300, list7count);
Assert.AreEqual(300, list8count);
}
/// <summary>
///
/// </summary>
/// <param name="data"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <WJ_T_PlanLog> GetPlanLogList(JObject data, ref int totalCount)
{
try
{
using (var db = DbInstance)
{
int pageIndex = data["page"].ToObject <int>();
int pageSize = data["limit"].ToObject <int>();
string planName = data["PlanName"] != null ? data["PlanName"].ToString().Trim() : "";
string userName = data["UserName"] != null ? data["UserName"].ToString().Trim() : "";
return(DbInstance.Queryable <WJ_T_PlanLog>()
.WhereIF(!string.IsNullOrWhiteSpace(planName), p => p.PlanLog_PlanIdName.Contains(planName))
.WhereIF(!string.IsNullOrWhiteSpace(userName), p => p.PlanLog_UserName.Contains(userName))
.OrderBy(p => p.PlanLog_Time, OrderByType.Desc)
.ToPageList(pageIndex, pageSize, ref totalCount));
}
}
catch (Exception ex)
{
LogHelper.DbServiceLog(ex.Message);
return(null);
}
}
public IActionResult HighestRowAndBelow([Required][FromQuery] string tableName, [Required][FromQuery] string partitionKey,
[Required][FromQuery] string rowKey, [Required][FromQuery] int maxAmount)
{
if (string.IsNullOrEmpty(tableName))
{
return(this.TableNameIsNull());
}
if (string.IsNullOrEmpty(partitionKey))
{
return(this.PartitionKeyIsNull());
}
if (string.IsNullOrEmpty(rowKey))
{
return(this.RowKeyIsNull());
}
var table = DbInstance.GetTable(tableName);
var result = table.GetHighestRowAndBelow(partitionKey, rowKey, maxAmount);
return(this.ToDbRowsResult(result));
}
public IActionResult SinglePartitionMultipleRows([Required][FromQuery] string tableName, [Required][FromQuery] string partitionKey,
[Required][FromBody] string[] rowKeys)
{
if (string.IsNullOrEmpty(tableName))
{
return(this.TableNameIsNull());
}
if (string.IsNullOrEmpty(partitionKey))
{
return(this.PartitionKeyIsNull());
}
if (rowKeys == null || rowKeys.Length == 0)
{
return(this.ToDbRowsResult(Array.Empty <DbRow>()));
}
var table = DbInstance.GetTable(tableName);
var result = table.GetMultipleRows(partitionKey, rowKeys);
return(this.ToDbRowsResult(result));
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <WJ_V_User> GetManagerList(JObject data, ref int totalCount)
{
try
{
int pageIndex = data["page"].ToObject <int>();
int pageSize = data["limit"].ToObject <int>();
string userName = (data["username"] == null ? "" : data["username"].ToString().Trim());
string telphone = (data["telphone"] == null ? "" : data["telphone"].ToString().Trim());
int roleId = (data["role"] == null ? 0 : data["role"].ToObject <int>());
//PageModel page = new PageModel();
//page.PageIndex = pageIndex;
//page.PageSize = limit;
//managerList = new DbContext<WJ_V_User>().GetList(p => p.User_Name.Contains(""), page);
//拼接拉姆达
//var exp = Expressionable.Create<WJ_V_User>()
// .OrIF(1 == 1, it => it.Id == 11)
// .And(it => it.Id == 1)
// .AndIF(2 == 2, it => it.Id == 1)
// .Or(it => it.User_Name == "a1").ToExpression();//拼接表达式
//var list = DbInstance.Queryable<WJ_V_User>().Where(exp).ToList();
var queryable = DbInstance.Queryable <WJ_V_User>().Where(p => p.Id == 1 || p.User_Type <= 2 && p.User_State == 1)
.WhereIF(!string.IsNullOrWhiteSpace(userName), p => p.User_Name.Contains(userName))
.WhereIF(!string.IsNullOrWhiteSpace(telphone), p => p.User_Name.Contains(telphone))
.WhereIF(roleId > 0, p => p.RoleId == data["role"].ToObject <int>())
.ToPageList(pageIndex, pageSize, ref totalCount);
return(queryable);
}
catch (Exception ex)
{
LogHelper.DbServiceLog(ex.Message);
return(null);
}
}
public IResult UpdateArticle(int articleId, string ArticleTitle, byte[] ImageBytes, string ArticleContent, List <int> CategoryIds, int writerID)
{
try
{
Article article = DbInstance.GetArticleById(articleId);
article.ArticleContent = ArticleContent;
article.ArticlePictureURL = ImageBytes;
article.ArticleTitle = ArticleTitle;
article.Categories.Clear();
foreach (var item in CategoryIds)
{
var cat = DbInstance.GetCategoryById(item);
article.Categories.Add(cat);
}
article.WriterId = writerID;
DbInstance.UpdateArticle(article);
DbInstance.SaveChanges();
return(new SuccessResult(Messages.SuccessUpdateArticle));
}
catch (Exception e)
{
return(new ErrorResult(Messages.ErrorUpdateArticle + e.Message));
}
}
public void TestGordon()
{
var db = new DbInstance("gordon.db");
db.Map<Person>().Automap(i => i.PersonID, true).WithIndex("Surname", i => i.Surname);
db.Initialize();
var table = db.Table<Person>();
table.Purge();
Person newPerson1 = new Person { Forename = "Joe", Surname = "Bloggs" };
Person newPerson2 = new Person { Forename = "James", Surname = "Smith" };
Person newPerson3 = new Person { Forename = "David", Surname = "Peterson" };
Person newPerson4 = new Person { Forename = "Steve", Surname = "Gordon" };
Person newPerson5 = new Person { Forename = "David", Surname = "Gordon" };
Person newPerson6 = new Person { Forename = "Colin", Surname = "Gordon" };
Person newPerson7 = new Person { Forename = "Michael", Surname = "Gordon" };
var newPeople = new[]
{
newPerson1,
newPerson2,
newPerson3,
newPerson4,
newPerson5,
newPerson6,
newPerson7
};
table.Save(newPeople);
var index = table.IndexQuery<string>("Surname");
// HIJKLMNOPQRS
var queryindex = index.GreaterThan("H", true).LessThan("T", true).ToLazyList();
Assert.AreEqual(2, queryindex.Count);
}
public int GetArticleCommentLimit(string articleID)
{
string sql = "select articleCommentLimit from blog_tb_article where articleID=@articleID";
return(DbInstance.GetInt(sql, DbInstance.CreateParameter("@articleID", articleID)));
}
public int Insert(blog_tb_article article, blog_tb_article_content content)
{
//if (!String.IsNullOrEmpty(entity.articleSourceUrl))
//{
// if (this.GetEntities().Where(c => c.articleSourceUrl == entity.articleSourceUrl).Count() > 0)
// {
// throw new CustomException("来源Url已存在");
// }
//}
//删除临时正文
this.DbInstance.ExecuteSql("delete from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", article.articleID));
//如果描述文本为空并且正文不为空 则取正文的html格式化后的一小段
if (String.IsNullOrEmpty(content.articleSubContentText) && (!String.IsNullOrEmpty(content.articleContent)))
{
string articleSubContentText = HttpHelper.HtmlFilter(content.articleContent);
content.articleSubContentText = articleSubContentText.Substring(0, Math.Min(300, articleSubContentText.Length));
}
//插入正文
content.UPDATE_DATE = DateTime.Now;
content.contentID = Guid.NewGuid().ToString("N");
EntityHelper <blog_tb_article_content> .Insert(content, "blog_tb_article_content", "contentID", true, this.DbInstance);
return(base.Insert(article));
}
public string ReadTempContent(string articleID)
{
string content = "";
if (articleID == "0")
{
content = DbInstance.GetString("select top 1 articleContent from blog_tb_article_temp order by UPDATE_DATE DESC");
}
else
{
content = DbInstance.GetString("select articleContent from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID));
}
return(content);
}
public static T ExecuteEntity <T>(DbInstance instance, string cmdText, object parameter = null) where T : class, new()
{
return(ExecuteEntity <T>(instance, CommandType.Text, cmdText, parameter));
}
public IEnumerable<blog_tb_tag> GetArticleTags(string articleID)
{
string sql = "select * from blog_tb_tag where tagID in ( select tagID from blog_tb_tagArticle where articleID=@articleID)";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID));
return ObjectHelper.DataTableToModel<blog_tb_tag>(dt);
}
public static DataSet ExecuteDataSet(DbInstance instance, string cmdText, object parameter = null)
{
return(ExecuteDataSet(instance, CommandType.Text, cmdText, parameter));
}
public static int ExecuteNonQuery(DbInstance instance, CommandType cmdType, string cmdText, object parameter = null)
{
string connStrKey = GetConnStrKey(instance);
return(ExecuteNonQuery(connStrKey, cmdType, cmdText, parameter));
}
public static object ExecuteScalar(DbInstance instance, string cmdText, object parameter = null)
{
return(ExecuteScalar(instance, CommandType.Text, cmdText, parameter));
}
public static void ExecuteReaderFirst(Action <DbDataReader> action, DbInstance instance, string cmdText, object parameter = null)
{
ExecuteReaderFirst(action, instance, CommandType.Text, cmdText, parameter);
}
public static DbDataReader ExecuteReader(DbInstance instance, string cmdText, object parameter = null)
{
return(ExecuteReader(instance, CommandType.Text, cmdText, parameter));
}
public static DbDataReader ExecuteReader(DbInstance instance, CommandType cmdType, string cmdText, object parameter = null)
{
string connStrKey = GetConnStrKey(instance);
return(ExecuteReader(connStrKey, cmdType, cmdText, parameter));
}
public static T ExecuteSingle <T>(Func <DbDataReader, T> converter, DbInstance instance, string cmdText, object parameter = null)
{
return(ExecuteSingle <T>(converter, instance, CommandType.Text, cmdText, parameter));
}
public int SaveTempContent(string articleID, string content)
{
int result = 0;
if (this.DbInstance.Exists("select 1 from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID)))
{
result = DbInstance.ExecuteSql("update blog_tb_article_temp set articleContent=@articleContent,UPDATE_DATE=@UPDATE_DATE where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@articleContent", content)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now));
}
else
{
result = DbInstance.ExecuteSql("insert into blog_tb_article_temp values(@articleID,@articleContent,@ADD_DATE,@UPDATE_DATE)"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@articleContent", content)
, DbInstance.CreateParameter("@ADD_DATE", DateTime.Now)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now));
}
return result;
}
public void MultiplexClassUseInDb()
{
//Table 클래스 중복 테스트하기
using (DbInstance db = new DbInstance("LexDBTest1.db"))
{
db.Map<ClassA>().Automap(i => i.UUID).WithIndex("Value", i => i.Value);
db.Map<ClassB>().Automap(i => i.UUID).WithIndex("Value", i => i.Value);
db.Map<ClassC>().Automap(i => i.UUID).WithIndex("Date", i => i.Date);
db.Map<ClassD>().Automap(i => i.UUID).WithIndex("Value", i => i.Value);
db.Map<ClassE>().Automap(i => i.UUID).WithIndex("Value", i => i.Value);
db.Map<ClassF>().Automap(i => i.UUID).WithIndex("Value", i => i.Value);
db.Map<Class>().Automap(i => i.UUID).WithIndex("classF", i => i.classF);
//db.Map<Node>().Automap(i => i.UUID).WithIndex("Str", i => i.Str).WithIndex("Link", i => i.Link);
db.Initialize();
db.Purge();
//테이블 추가 및 레코드 저장
db.Save(new ClassA(1), new ClassA(2), new ClassA(3));
ClassA[] classA = db.LoadAll<ClassA>();
foreach (var a in classA)
Console.WriteLine(a.Value);
db.Save(new ClassB("A"), new ClassB("B"), new ClassB("C"));
ClassB[] classB = db.LoadAll<ClassB>();
foreach (var b in classB)
Console.WriteLine(b.Value);
db.Save(new ClassA(1), new ClassA(2), new ClassA(3));
classA = db.LoadAll<ClassA>();
foreach (var a in classA)
Console.WriteLine(a.Value);
Assert.AreEqual(db.Count<ClassA>(), 6);
Assert.AreEqual(db.Count<ClassB>(), 3);
//테이블 삭제
db.Purge<ClassB>();
classB = db.LoadAll<ClassB>();
Assert.AreEqual(db.Count<ClassB>(), 0);
Assert.AreEqual(classB.Length, 0);
Assert.AreEqual(db.Count<ClassA>(), 6);
//삭제
var classX = new ClassA(100);
db.Save(classX);
Assert.AreEqual(db.Count<ClassA>(), 7);
db.Delete(classX);
Assert.AreEqual(db.Count<ClassA>(), 6);
//키에 의한 삭제
db.Save(classX);
Assert.AreEqual(db.Count<ClassA>(), 7);
db.DeleteByKey<ClassA, string>(classX.UUID);
Assert.AreEqual(db.Count<ClassA>(), 6);
//키에 의한 쿼리
db.Save(classX);
ClassA findA = db.LoadByKey<ClassA, string>(classX.UUID);
Assert.AreEqual(findA.UUID, classX.UUID);
Assert.AreEqual(findA.Value, classX.Value);
//편집
db.Purge<ClassA>();
for (int i = 0; i < 10; i++)
{
ClassA newA = new ClassA();
newA.Value = i;
newA.UUID = classX.UUID;
db.Save(newA);
}
Assert.AreEqual(db.Count<ClassA>(), 1);
Assert.AreEqual(db.LoadByKey<ClassA, string>(classX.UUID).Value, 9);
//DateTime 저장 테스트
ClassC newC = new ClassC(DateTime.Now);
db.Save(newC);
ClassC findC = db.LoadByKey<ClassC, string>(newC.UUID);
Assert.AreEqual(newC.Date, findC.Date);
//Nullable 객체 저장 테스트
ClassD newD = new ClassD(1);
db.Save(newD);
ClassD findD = db.LoadByKey<ClassD, string>(newD.UUID);
Assert.AreEqual(newD.Value, findD.Value);
newD = new ClassD(null);
db.Save(newD);
findD = db.LoadByKey<ClassD, string>(newD.UUID);
Assert.AreEqual(newD.Value, findD.Value);
//제네릭 객체 저장 테스트
var list = new List<string>() { "a", "b", "c" };
ClassE newE = new ClassE(list);
db.Save(newE);
ClassE findE = db.LoadByKey<ClassE, string>(newE.UUID);
Assert.AreEqual(list.SequenceEqual(findE.Value), true);
newE.Value.Add("d");
db.Save(newE);
findE = db.LoadByKey<ClassE, string>(newE.UUID);
Assert.AreEqual(newE.Value.SequenceEqual(findE.Value), true);
//Null 반응 테스트
findE = db.LoadByKey<ClassE, string>(null);
Assert.AreEqual(findE, null);
//dictionary..
var dic = new Dictionary<string, int>() { { "a", 1 }, { "b", 2 } };
ClassF newF = new ClassF(dic);
db.Save(newF);
ClassF findF = db.LoadByKey<ClassF, string>(newF.UUID);
Assert.AreEqual(dic.SequenceEqual(findF.Value), true);
//newF.Value = new Dictionary<string, int>(dic);
newF.Value["a"] = 10;
db.Save(newF);
findF = db.LoadByKey<ClassF, string>(newF.UUID);
Assert.AreEqual(newF.Value.SequenceEqual(findF.Value), true);
//트리형 Node
Node root = new Node();
Node i1 = new Node();
Node i2 = new Node();
Node i11 = new Node();
Node i12 = new Node();
Node i111 = new Node();
i11.Link = i111;
i1.Link = i11;
i1.Link = i12;
root.Link = i1;
root.Link = i2;
db.Save(root);
Node node = db.LoadByKey<Node>(root.UUID);
}
}
public string LoadLastArticleID()
{
string articleID = DbInstance.GetString("select articleID from blog_tb_article_temp where articleID not in (select articleID from blog_tb_article)");
return(articleID);
}
public static T ExecuteEntity <T>(DbInstance instance, CommandType cmdType, string cmdText, object parameter = null) where T : class, new()
{
return(ExecuteSingle <T>(ConvertReaderToEntity <T>, instance, cmdType, cmdText, parameter));
}
public int UpdateLastOpenDatetime(string articleID)
{
int count = DbInstance.GetInt("select count(*) from blog_tb_article_extend where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID));
if (count == 0)
{
return(DbInstance.ExecuteSql("insert into blog_tb_article_extend(extendID,articleID,lastOpenDatetime,UPDATE_DATE) values (@extendID,@articleID,@lastOpenDatetime,@UPDATE_DATE)"
, DbInstance.CreateParameter("@extendID", Guid.NewGuid().ToString("N"))
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now)));
}
else
{
return(DbInstance.ExecuteSql("update blog_tb_article_extend set lastOpenDatetime=@lastOpenDatetime,UPDATE_DATE=@UPDATE_DATE where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@lastOpenDatetime", DateTime.Now)
, DbInstance.CreateParameter("@UPDATE_DATE", DateTime.Now)));
}
}
// [ExpectedException(typeof(InvalidOperationException))]
public void MapDbWrong()
{
try
{
var db = new DbInstance(@"My Database\My Schema");
db.Initialize();
db.Map<MyData>().Automap(i => i.Id);
Assert.Fail("InvalidOperationException expected");
}
catch (InvalidOperationException)
{
}
}
public string GetarticleUserID(string articleID)
{
string sql = "select userID from blog_tb_article a left join blog_tb_blog b on a.blogID=b.blogID where a.articleID=@articleID";
return(DbInstance.GetString(sql, DbInstance.CreateParameter("@articleID", articleID)));
}
public blog_tb_article_content GetArticleContent(string articleID)
{
string sql = "select * from blog_tb_article_content where articleID=@articleID";
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@articleID", articleID));
return ObjectHelper.DataTableToSingleModel<blog_tb_article_content>(dt);
}
public int SaveTempContent(string articleID, string content)
{
int result = 0;
if (this.DbInstance.Exists("select 1 from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", articleID)))
{
result = DbInstance.ExecuteSql("update blog_tb_article_temp set articleContent=@articleContent,UPDATE_DATE=GETDATE() where articleID=@articleID"
, DbInstance.CreateParameter("@articleID", articleID)
, DbInstance.CreateParameter("@articleContent", content));
}
else
{
result = DbInstance.ExecuteSql("insert into blog_tb_article_temp values(@ID,@articleID,@articleContent,GETDATE(),GETDATE())",
DbInstance.CreateParameter("@ID", Guid.NewGuid().ToString("N")),
DbInstance.CreateParameter("@articleID", articleID),
DbInstance.CreateParameter("@articleContent", content));
}
return(result);
}
public int UpdateComment(string articleID, int add)
{
string addstr = (add >= 0 ? "+" + add : add.ToString());
string sql = "update blog_tb_article_extend set articleCommentTimes=articleCommentTimes" + addstr + " where articleID=@articleID";
return DbInstance.ExecuteSql(sql, DbInstance.CreateParameter("@articleID", articleID));
}
public int Update(blog_tb_article article, blog_tb_article_content content)
{
//删除临时正文
this.DbInstance.ExecuteSql("delete from blog_tb_article_temp where articleID=@articleID", DbInstance.CreateParameter("@articleID", article.articleID));
//如果描述文本为空并且正文不为空 则取正文的html格式化后的一小段
if (String.IsNullOrEmpty(content.articleSubContentText) && (!String.IsNullOrEmpty(content.articleContent)))
{
string articleSubContentText = HttpHelper.HtmlFilter(content.articleContent);
content.articleSubContentText = articleSubContentText.Substring(0, Math.Min(300, articleSubContentText.Length));
}
//修改正文
content.UPDATE_DATE = DateTime.Now;
EntityHelper <blog_tb_article_content> .Update(content, "blog_tb_article_content", this.DbInstance, "contentID");
return(base.Update(article));
}
public void OpenDb()
{
var db = new DbInstance("My Database");
db.Initialize();
}
public string ReadTemp(string userID)
{
string sql = "select TalkContent from blog_tb_Talk where UserID=@UserID and IsTemp=1 limit 0,1";
return(DbInstance.GetString(sql, DbInstance.CreateParameter("@UserID", userID)));
}
void TestDeleteBugfix()
{
var db = new DbInstance("test.fix1");
//mapping done before init
db.Map<TemplateModel>().Automap(i => i.Id, false).WithIndex<int>("Type", i => i.Type);
db.Initialize();
//testing this in a method
db.Table<TemplateModel>().Save(new TemplateModel { Id = 66, Name = "test", Type = 3 });
db.Table<TemplateModel>().Save(new TemplateModel { Id = 67, Name = "test2", Type = 3 });
//The Type is 3 for both records
//The first indexQuery returns 2 records, OK!
var indexQuery = db.Table<TemplateModel>().IndexQueryByKey<int>("Type", 3).ToList();
db.Table<TemplateModel>().DeleteByKey<int>(67);
//allItems returns 1 record, OK!
var allItems = db.Table<TemplateModel>().LoadAll().ToList();
//indexQuery2 returns 0 records, wrong!
var indexQuery2 = db.Table<TemplateModel>().IndexQueryByKey<int>("Type", 3).ToList();
Assert.AreEqual(1, indexQuery2.Count());
}
public void OpenDbComplexPath()
{
var db = new DbInstance(@"My Database\My Schema");
db.Initialize();
}
public void OpenDbComplexPath2()
{
var db = new DbInstance(@"My Database\My Schema", Windows.Storage.ApplicationData.Current.TemporaryFolder);
db.Initialize();
}
public void IndexingDetails()
{
var db = new DbInstance(@"My Database\Indexing2");
db.Map<MyData>().Automap(i => i.Id, true).WithIndex("Test", i => i.IntField);
db.Initialize();
var table = db.Table<MyData>();
table.Purge();
db.BulkWrite(() =>
{
table.Save(new MyData { IntField = 1 });
table.Save(new MyData { IntField = 1 });
table.Save(new MyData { IntField = 1 });
table.Save(new MyData { IntField = 1 });
table.Save(new MyData { IntField = 1 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 3 });
table.Save(new MyData { IntField = 3 });
table.Save(new MyData { IntField = 3 });
table.Save(new MyData { IntField = 3 });
table.Save(new MyData { IntField = 3 });
table.Save(new MyData { IntField = 4 });
table.Save(new MyData { IntField = 5 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 6 });
table.Save(new MyData { IntField = 7 });
table.Save(new MyData { IntField = 8 });
table.Save(new MyData { IntField = 8 });
table.Save(new MyData { IntField = 8 });
table.Save(new MyData { IntField = 8 });
table.Save(new MyData { IntField = 8 });
table.Save(new MyData { IntField = 9 });
});
var list1 = table.LoadAll();
var index = table.IndexQuery<int>("Test");
Assert.AreEqual(index.Key(1).Count(), list1.Count(i => i.IntField == 1));
Assert.AreEqual(index.Key(8).Count(), list1.Count(i => i.IntField == 8));
Assert.AreEqual(index.GreaterThan(6, true).LessThan(8).Count(), list1.Count(i => i.IntField >= 6 && i.IntField < 8));
IdSequenceEqual(index.GreaterThan(6).LessThan(8).ToList(), list1.Where(i => i.IntField > 6 && i.IntField < 8));
IdSequenceEqual(index.LessThan(8).ToList(), list1.Where(i => i.IntField < 8));
IdSequenceEqual(index.GreaterThan(6, true).ToList(), list1.Where(i => i.IntField >= 6));
IdSequenceEqual(index.GreaterThan(7, true).LessThan(7).ToList(), list1.Where(i => i.IntField >= 7 && i.IntField < 7));
IdSequenceEqual(index.GreaterThan(7).LessThan(7, true).ToList(), list1.Where(i => i.IntField > 7 && i.IntField <= 7));
}
public static int ExecuteNonQuery(DbInstance instance, string cmdText, object parameter = null)
{
return(ExecuteNonQuery(instance, CommandType.Text, cmdText, parameter));
}
public IEnumerable <ArticleInfo> GetList(ArticleQueryFilter filter)
{
StringBuilder sbBuff = new StringBuilder();
sbBuff.Append("select sql_calc_found_rows * from (");
sbBuff.Append("select a.*,b.Name as CategoryName,c.Content from article a");
sbBuff.AppendFormat(@" left join category b on a.CategoryId=b.Id
left join article_content c on a.Id= c.ArticleId
where 1=1");
if (filter.CategoryIds != null && filter.CategoryIds.GetEnumerator().MoveNext())
{
if (filter.CategoryIds.Count() > 1)
{
sbBuff.AppendFormat(" and a.CategoryId in ({0})", string.Join(",", filter.CategoryIds));
}
else
{
sbBuff.AppendFormat(" and a.CategoryId={0}", filter.CategoryIds.ElementAt(0));
}
}
if (!string.IsNullOrWhiteSpace(filter.Keywords))
{
sbBuff.AppendFormat(" and a.Title like '%{0}%'", filter.Keywords.ReplaceSqlInjectChar());
}
if (filter.IsCover.HasValue)
{
if (filter.IsCover.Value)
{
sbBuff.Append(" and a.Cover is not null and length(a.Cover)>0");
}
else
{
sbBuff.Append(" and a.Cover is null");
}
}
sbBuff.Append(" order by a.CreateDate desc,a.id desc");
sbBuff.Append(") as temp");
//分页
sbBuff.Append(" limit " + filter.StartRowNo + "," + filter.PageSize + ";select found_rows();");
using (DbCommand cmd = DbInstance.GetSqlStringCommand(sbBuff.ToString()))
{
var ds = GetDataSet(cmd);
filter.TotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
return(ds.Tables[0].ToList <ArticleInfo>(new DBMapOption()
{
RowFillAction = delegate(object contextObject, object dataRow, object rowFillActionParam)
{
if (dataRow != null)
{
var article = (ArticleInfo)contextObject;
var content = (((DataRow)dataRow)["Content"]).ToString();
content = content.ClearHtml().ClearLine();
if (!string.IsNullOrWhiteSpace(content) && content.Length > 200)
{
content = content.Substring(0, 200);
}
article.Summary = content;
}
}
}));
}
}
public Repository(DbInstance dbInstance)
{
instance = dbInstance;
}