public static void db2()
{
string conStr = "server=localhost;username=root;pwd=123456; port=3306;database=ctjs;SslMode=none;";
using (MySqlConnection connection = new MySqlConnection(conStr))
{
//var sql = "select * from user";
//查询 集合
//var r = connection.Query<User>(sql).AsList();
//System.Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(r));
// //sql 操作
// var rInt = connection.Execute(" insert User (ID,Name) values (@id,'javc')", new { id = new Random().Next(100, 200) });
// rInt = connection.Execute(" update User set name=@name where id=1002", new { name = "hellon" });
// // 查询单个对象
var rOne = connection.CommandSet <User>().Where(a => a.Id == 123123).Delete();
//var rOne = connection.QuerySet<User>().Get;
//var rOne = connection.;
System.Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(rOne));
}
}
public void Test()
{
var commne = new Comment()
{
Id = 10,
Content = "test",
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
PId = 0,
RefCommentId = 0
};
DateTime dateTime = DateTime.Now.AddDays(100);
using (var conn = new MySqlConnection(mysqlConnection))
{
//根据成员修改
var result = conn.CommandSet <Comment>()
.Where(x => x.Id.In(new int[] { 1, 2, 3 }) && x.IsDeleted == false)
.Update(x => new Comment()
{
// Content = commne.Content,
SubTime = dateTime,
IsDeleted = false,
Content = "123" + x.Content
});
//全部修改
var result1 = conn.CommandSet <Comment>()
//.Where(x => x.Id == commne.Id)
.Update(commne, new string[] { "ArticleId" });
//新增
var result2 = conn.CommandSet <Comment>()
.Insert(new Comment()
{
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
Content = "test",
PId = 0,
RefCommentId = 0
});
//新增返回自增id
var result3 = conn.CommandSet <Comment>()
.InsertIdentity(new Comment()
{
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
Content = "test",
PId = 0,
RefCommentId = 0
});
//批量新增
var result4 = conn.CommandSet <Comment>()
.Insert(new List <Comment>()
{
commne,
commne,
commne
});
//删除
var result5 = conn.CommandSet <Comment>()
.Where(x => x.Id == result3)
.Delete();
}
}
static void Main(string[] args)
{
var con = new MySqlConnection(
"server = 192.168.13.86;User Id = root;password = 123456789;Database = SkyChen");
var user = new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "18988563330",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
};
var insertResult = con.CommandSet <SysUser>().Insert(user);
Console.WriteLine("Insert添加数{0}", insertResult);
user.Email = "*****@*****.**";
user.SysUserid = Guid.NewGuid().ToString("N");
var ifNotExistsResult = con.CommandSet <SysUser>().IfNotExists(a => a.Email == "*****@*****.**").Insert(user);
Console.WriteLine("IfNotExists添加数{0}", ifNotExistsResult);
user.SysUserid = Guid.NewGuid().ToString("N");
var ifNotExistsResult2 = con.CommandSet <SysUser>().IfNotExists(a => a.Email == "*****@*****.**").Insert(user);
Console.WriteLine("IfNotExists2添加数{0}", ifNotExistsResult2);
var getResult = con.QuerySet <SysUser>().WithNoLock().Get();
getResult.Email = "*****@*****.**";
var updateModelResult = con.CommandSet <SysUser>().Update(getResult);
Console.WriteLine("Update添加数{0}", updateModelResult);
var countResult = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**").Count();
Console.WriteLine("Count数{0}", countResult);
var toListResult = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**")
.OrderBy(a => a.CreateDatetime).Top(2).Select(a => a.Email).ToList();
Console.WriteLine("ToList数{0}", toListResult.Count());
var listResult2 = con.QuerySet <SysUser>().WithNoLock().Where(a => a.Email == "*****@*****.**")
.OrderBy(a => a.CreateDatetime).Select(a => a.Email).PageList(2, 2);
Console.WriteLine("PageList:{0}", listResult2.TotalPage);
var updateResult4 = con.QuerySet <SysUser>().WithNoLock().Sum(a => a.UserStatus);
Console.WriteLine("Sum:{0}", updateResult4);
var deleteResult = con.CommandSet <SysUser>().Delete();
Console.WriteLine("Delete:{0}", deleteResult);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
using (var transaction = con.BeginTransaction())
{
con.CommandSet <SysUser>(transaction).Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "11111111111",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
con.CommandSet <SysUser>(transaction).Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "22222222222",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
transaction.Commit();
}
con.Transaction(tc =>
{
var SysUserid1 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561110").Select(a => a.SysUserid).Get();
var SysUserid2 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561111").Select(a => a.SysUserid).Get();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid1).Delete();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid2).Delete();
tc.CommandSet <SysUser>().Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "13536059332",
RealName = "大笨贞",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = EUserType.Super,
Password = "******"
});
}, ex =>
{
//do something
});
con.Dispose();
}
public void Test()
{
//Sql执行前
SqlMapper.Aop.OnExecuting += (ref CommandDefinition command) =>
{
//生成的sql
var sql = command.CommandText;
//生成的参数
var param = command.Parameters;
Console.WriteLine(sql);
};
//Sql执行后
SqlMapper.Aop.OnExecuted += (ref CommandDefinition command) =>
{
//生成的sql
var sql = command.CommandText;
//生成的参数
var param = command.Parameters;
Console.WriteLine(sql);
};
//Thread thread = new Thread(() =>
//{
// //执行前
// SqlMapper.Aop.OnExecuting += (CommandDefinition Command) =>
// {
// };
// //执行后
// SqlMapper.Aop.OnExecuted += (CommandDefinition Command) =>
// {
// };
// using (var conn = new MySqlConnection(mysqlConnection))
// {
// var list= conn.QuerySet<Comment>().ToList();
// }
//});
//thread.Start();
using (var conn = new MySqlConnection(mysqlConnection))
{
EntityCache.Register(typeof(AdminUser));
IBaseEntity entitys = new Comment();
var test = conn.CommandSet <IBaseEntity>()
.Where(x => 1 == 1)
.Insert(entitys);
////测试codefirst
//CodeFirst codeFirst = new CodeFirst(conn);
//codeFirst.SyncStructure();
var dataSet = conn.QuerySet <Comment>()
.Where(x => 1 == 1)
.ToDataSet(new MySqlDataAdapter());
DateTime dateTime = DateTime.Now.AddDays(-10);
//var comments = conn.Query<Comment>("Select * from Comment").ToList();
//var lis = conn.Query<News1, List<Comment>, News1, News1, News1, News1>("SELECT * FROM NEWS AS A LEFT JOIN COMMENT AS B ON A.Id=B.ArticleId");
//var test1 = conn.QuerySet<News1>().Get();
//var getIfTest = conn.QuerySet<Comment>()
// .Get(false, x => new CommentDto()
// {
// Id = x.Id,
// ArticleIds = x.ArticleId
// }, x => new CommentDto()
// {
// Id = x.Id,
// Content = x.Content
// });
//int[] array = new int[] { 1, 2, 3 };
DynamicParameters param = new DynamicParameters();
param.Add("Id", 10);
//var aaaa = SqlMapper.Query<Comment2, News>(conn,"", (first, second) =>
// {
// };
var comment = conn.QuerySet <News1>()
.Where(x => x.Comments.Any(y => y.Id.Between(1, 100) && y.ResourceMapping.Any(z => z.Id.Between(1, 100) && z.MenList.Any(l => l.Id.Between(1, 100)))))
.ToList();
//var comment11 = conn.QuerySet<News1>()
//.Where(x => x.Comments.Any(y => y.Id != 10))
//.Get(x => new NewsDto1
//{
// Id = x.Id,
// Title = x.Content,
// CommentDto1 = x.Comments.Select(y => new CommentDto1()
// {
// Id = y.Id,
// Content = y.Content
// }).ToList()
//});
//x.Comments.Select(y => new CommentDto1()
//{
// Id = y.Id,
// Content = y.Content
//}).ToList()
var testaa = "123";
// var list= conn.QueryFirstOrDefault<News1, List<Comment>, DontMap, DontMap, DontMap, DontMap>(@"SELECT
//* FROM News AS A LEFT JOIN COMMENT AS B ON A.Id=B.ArticleId");
//var list1 = conn.Query<Comment2, News>("SELECT * FROM COMMENT AS A LEFT JOIN NEWS AS B ON A.ArticleId=B.Id");
////使用导航属性
//var list = conn.Query<Comment, News>("SELECT * FROM COMMENT AS A LEFT JOIN NEWS AS B ON A.ArticleId=B.Id", (a, b) => {
// a.News = b;
// }, splitOn: "Id");
////var count = conn.QuerySet<Comment>().Count();
////单个属性返回
//var ContentList = conn.QuerySet<Comment>()
// //.Where(x => x.Content.IsNotNull() && !(x.Content == "") && x.IsDeleted)
// //.WhereIf(!string.IsNullOrEmpty("aaa"), x => x.ArticleId == 1, x => x.ArticleId == 2)
// .PageList(1, 20, x => new CommentDto()
// {
// Id = x.Id,
// ArticleIds = x.ArticleId,
// ////count = conn.QuerySet<News>().Where(y => y.Id == x.ArticleId).Count(),
// NewsList = conn.QuerySet<News>()
// .Join<News, Comment>((a, b) => a.Id == b.ArticleId, JoinMode.LEFT, true)
// .Where(y => y.Id == x.ArticleId)
// .From<News, Comment>()
// .ToList((y, z) => new NewsDto()
// {
// Id = y.Id,
// Contents = y.Content,
// }).ToList(),
// NewsDto = conn.QuerySet<News>().Where(y => y.Id == x.ArticleId).Get(y => new NewsDto()
// {
// Id = y.Id,
// Contents = y.Content
// }),
// IsClickLike = conn.QuerySet<News>().Where(y => y.Id == x.ArticleId).Get(y => true)
// });
var array1 = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, };
var commne = conn.QuerySet <Comment>()
.Where(x => x.Id > 0 && array1.Contains(x.Id) && x.Content.Replace("1", "2") == x.Content)
.Where(x => x.Id.In(array1))
.GroupBy(x => new { x.ArticleId })
.Having(x => Function.Sum(x.Id) >= 5)
.ToList(x => new
{
x.ArticleId,
test1 = Function.Sum(x.Id)
});
var ids = conn.QuerySet <Comment>()
.Where(x => x.Id > 0 && array1.Contains(x.Id) && x.Content.Replace("1", "2") == x.Content && x.Content.Contains(null))
.Where(x => x.Id.In(array1))
.Get(x => Function.Sum(x.Id));
//翻页
var comment1 = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Id.ToString().ToUpper().Equals("3".ToUpper()))
.Where(x => x.Id.Between(80, 100) &&
x.SubTime.AddDays(-10).AddYears(1) < DateTime.Now.AddYears(1) && x.Id > 10 &&
x.Id > new QuerySet <News>(conn, new MySqlProvider()).Where(y => y.Id < 3 && x.Id < y.Id).Sum(y => y.Id))
.From <Comment, News>()
.OrderBy <News>(x => x.Id)
.PageList(1, 1, (a, b) => new
{
test = new List <int>()
{
3, 3, 1
}.FirstOrDefault(y => y == 1),
aaa = "6666" + "777",
Content = a.Content + "'test'" + b.Headlines + a.IdentityId,
bbb = conn.QuerySet <Comment1>()
.Where(y => y.ArticleId == b.Id && y.Content.Contains("test"))
.Sum(x => x.Id),
ccc = a.IdentityId,
a.Id,
times = DateTime.Now
});
//多视图查询
var comment5 = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Join <Comment, ResourceMapping>((a, b) => a.Id == b.FKId)
.Where(x => x.Content == "test")
.From <Comment, News, ResourceMapping>()
.OrderBy <News>(x => x.Id)
.Where((a, b, c) => a.ArticleId == b.Id)
.PageList(1, 10, (a, b, c) => new
{
id = a.Id,
name = b.NewsLabel,
resource = c.RPath,
});
//计总
var sum = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Content == "test")
.WithNoLock()
.Sum(x => x.Id);
//计数
var count = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Content == "test1")
.Where <News>(x => x.NewsLabel.Contains("足球"))
.WithNoLock()
.Count();
}
}
void IUnitOfWork.Add <T>(T entity)
{
_mySqlConnection.CommandSet <T>(_mySqlTransaction)
.Insert(entity);
}
public void Test()
{
var commne = new Comment()
{
Id = 10,
Content = "test",
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
PId = 0,
RefCommentId = 0
};
DateTime dateTime = DateTime.Now;
using (var conn = new MySqlConnection(mysqlConnection))
{
//根据成员修改
var result = conn.CommandSet <Comment>()
.Where(x => x.Id > commne.Id || x.Id < commne.Id)
.Update(x => new Comment()
{
// Content = commne.Content,
SubTime = dateTime
});
//全部修改
var result1 = conn.CommandSet <Comment>()
.Where(x => x.Id == commne.Id)
.Update(commne, new string[] { "ArticleId" });
//新增
var result2 = conn.CommandSet <Comment>()
.Insert(new Comment()
{
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
Content = "test",
PId = 0,
RefCommentId = 0
});
//新增返回自增id
var result3 = conn.CommandSet <Comment>()
.InsertIdentity(new Comment()
{
ArticleId = 11,
Type = 1,
SubTime = DateTime.Now,
Content = "test",
PId = 0,
RefCommentId = 0
});
//批量新增
var result4 = conn.CommandSet <Comment>()
.Insert(new List <Comment>()
{
commne,
commne,
commne
});
//删除
var result5 = conn.CommandSet <Comment>()
.Where(x => x.Id == result3)
.Delete();
}
}
static void Main(string[] args)
{
var con = new MySqlConnection(
"server = 192.168.13.86;User Id = root;password = 123456789;Database = SkyChen");
var insertResult = con.CommandSet <SysUser>().Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "18988561111",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = 1,
Password = "******"
});
Console.WriteLine("添加数{0}", insertResult);
var insertResult2 = con.CommandSet <SysUser>().IfNotExists(a => a.Mobile == "18988563222").Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "18988563222",
RealName = "陈珙",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = 1,
Password = "******"
});
var countResult = con.QuerySet <SysUser>().Where(a => a.Email == "*****@*****.**").Count();
Console.WriteLine("查询个数{0}", countResult);
var getResult = con.QuerySet <SysUser>().Get();
getResult.Email = "*****@*****.**";
var updateModelResult = con.CommandSet <SysUser>().Update(getResult);
Console.WriteLine("updateModelResult个数{0}", updateModelResult);
var list = con.QuerySet <SysUser>().ToList();
foreach (var item in list)
{
con.CommandSet <SysUser>().Where(a => a.SysUserid == item.SysUserid)
.Update(a => new SysUser {
Email = "*****@*****.**"
});
}
var listResult = con.QuerySet <SysUser>().OrderBy(a => a.CreateDatetime).Select(a => a.Email).ToList();
var listResult2 = con.QuerySet <SysUser>().OrderBy(a => a.CreateDatetime).Top(2).Select(a => a.Email).ToList();
var updateResult = con.CommandSet <SysUser>().Where(a => a.SysUserid == "487c9dac0c094a31a89fef1a98bc4204")
.Update(a => new SysUser {
Email = "*****@*****.**"
});
getResult.Email = "*****@*****.**";
var updateResult2 = con.CommandSet <SysUser>().Where(a => a.Email == "*****@*****.**").Update(getResult);
var updateResult3 = con.QuerySet <SysUser>().Where(a => a.Email == "*****@*****.**").OrderBy(b => b.Email)
.Top(10).Select(a => a.Email).ToList();
var updateResult8 = con.QuerySet <SysUser>().OrderBy(b => b.Email).Top(10).ToList();
var updateResult4 = con.QuerySet <SysUser>().Sum(a => a.UserStatus);
var updateResult6 = con.QuerySet <SysUser>().Where(a => a.Email == "*****@*****.**")
.OrderBy(a => a.CreateDatetime)
.Select(a => new SysUser {
Email = a.Email, Mobile = a.Mobile, Password = a.Password
}).PageList(1, 10);
con.Transaction(tc =>
{
var SysUserid1 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561110").Select(a => a.SysUserid).Get();
var SysUserid2 = tc.QuerySet <SysUser>().Where(a => a.Mobile == "18988561111").Select(a => a.SysUserid).Get();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid1).Delete();
tc.CommandSet <SysUser>().Where(a => a.SysUserid == SysUserid2).Delete();
tc.CommandSet <SysUser>().Insert(new SysUser
{
CreateDatetime = DateTime.Now,
Email = "*****@*****.**",
Mobile = "13536059332",
RealName = "大笨贞",
SysUserid = Guid.NewGuid().ToString("N"),
UserName = "******",
UserStatus = 1,
UserType = 1,
Password = "******"
});
});
con.Dispose();
}