Dapper使用入门Demo
配置连接数据库
<connectionStrings>
<add name="strCon" connectionString="server=*.168.13.*\SERVICE;user id=sa; password=123456; database=TimeRecord;"/>
</connectionStrings>
准备一个UserInfo表
CREATE TABLE [dbo].[UserInfo]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, [MyPic] [nvarchar](255) NULL, [Email] [nvarchar](50) NULL, [Password] [nvarchar](50) NULL, [Gender] [nvarchar](50) NULL, [Birthday] [datetime] NULL, [Textarea] [nvarchar](255) NULL, [CreateTime] [datetime] NULL, CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
TimeRecord表
GO CREATE TABLE [dbo].[TimeRecords]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [UserID] [bigint] NULL, [Title] [nvarchar](50) NULL, [Speaking] [nvarchar](max) NOT NULL, [ImgUrl] [nvarchar](255) NULL, [SpeakingColor] [nvarchar](50) NULL, [AddTime] [datetime] NULL, CONSTRAINT [PK_TimeRecord] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
注明：一个用户UserInfo对应多个TimeRecord记录
用vs建立一个控制台应用程序
新建一个UserInfo类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 { class UserInfo { private int iD; private string name; private string myPic; private string email; private string password; private string gender; private DateTime birthday; private string textarea; private List<TimeRecords> userTimeRecords; public string Name { get { return name; } set { name = value; } } public string MyPic { get { return myPic; } set { myPic = value; } } public string Email { get { return email; } set { email = value; } } public string Gender { get { return gender; } set { gender = value; } } public DateTime Birthday { get { return birthday; } set { birthday = value; } } public string Textarea { get { return textarea; } set { textarea = value; } } public string Password { get { return password; } set { password = value; } } public int ID { get { return iD; } set { iD = value; } } internal List<TimeRecords> UserTimeRecords { get { return userTimeRecords; } set { userTimeRecords = value; } } } }
新建一个TimeRecords类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 { class TimeRecords { public long ID { get; set; } public long UserID { get; set; } public UserInfo user { get; set; } public string Title { get; set; } public string Speaking { get; set; } public string ImgUrl { get; set; } public string SpeakingColor { get; set; } public Nullable<System.DateTime> AddTime { get; set; } } }
项目添加Dapper引用，通过工具→NUg包管理器→解决方案的nug管理包，进行查询dapper进行安装。
主程序
using Dapper; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { //读取config中的字符串 string connstr = ConfigurationManager.ConnectionStrings["strCon"].ToString(); //IDbConnection IDbConnection connection = new SqlConnection(connstr); //插入操作 // var result = connection.Execute("insert into UserInfo (name,mypic,email,[password],gender,birthday) values (@Name,@MyPic,@Email,@Password,@Gender,@Birthday)", new { Name = "kfz", MyPic = "74.jpg",Email="1067945009@qq.com", Password = "123456" ,Gender="Male", Birthday="1994-11-09"}); //批量插入操作 // var UserList = Enumerable.Range(0, 10).Select(i => new UserInfo() { Name = "kfz" + i,Textarea=i.ToString(),Password="456"+i }); // var result2 = connection.Execute("insert into UserInfo (Name,Textarea,Password) values(@Name,@Textarea,@Password)", UserList); //查询操作 // var query = connection.Query<UserInfo>("select * from UserInfo where Name =@Name", new UserInfo() { Name = "VinKong" }); // foreach (var item in query) // { // Console.WriteLine(item.Name+"==="+item.Email); // } //更新操作 //var result = connection.Execute("update UserInfo set Name ='MyGirl' where ID=@ID",new UserInfo (){ID = 10008 }); //删除操作 //var result = connection.Execute("delete from UserInfo where ID = @ID",new UserInfo() { ID=10010}); // List<TimeRecords> list = get_user_info_TimeRecord_info(10005); // for (int i = 0; i < list.Count; i++) // { // Console.WriteLine( "Speeking ===="+list[i].Speaking); // } //Dapper還可以在命令中一次包含多組SELECT，透過QueryMultiple()後再以Read()或Read<T>分別取出查詢結果。 //var result = connection.QueryMultiple(@"select * from TimeRecords where UserId = @id select * from UserInfo where ID=@id ",new { id=10005}); //var timeRecords = result.Read<TimeRecords>(); //var aa = result.Read(); //Console.WriteLine(aa.Count()); //执行存储过程 //带输入输出参数的存储过程 // DynamicParameters dp = new DynamicParameters(); // dp.Add("@UserId", 10005); // dp.Add("@Speaking", "", DbType.String, ParameterDirection.Output); // connection.Execute("QueryTimeRecords", dp, null, null, CommandType.StoredProcedure); // var a = dp.Get<string>("@Speaking"); // Console.WriteLine( a); //不带输出参数的存储过程 // var b = connection.Query<TimeRecords>("QueryTimeRecords2", new { UserId=10005},null, commandType: CommandType.StoredProcedure); // foreach (var item in b) //{ // Console.WriteLine(item.ID+"====="+item.Speaking); // } //查询多映射一对多 string sql = "select * from UserInfo u inner join TimeRecords t on u.ID = t.UserID "; var PersonDictionary = new Dictionary<int, UserInfo>(); var query = connection.Query<UserInfo, TimeRecords, UserInfo>( sql, (u, t) => { UserInfo user; if (!PersonDictionary.TryGetValue(u.ID, out user)) { user = u; user.UserTimeRecords = new List<TimeRecords>(); PersonDictionary.Add(user.ID,user); } user.UserTimeRecords.Add(t); return user; } ,splitOn:"ID" ).Distinct().ToList(); foreach (var item in PersonDictionary) { Console.WriteLine(item.Value.Name); foreach (var timeRecord in item.Value.UserTimeRecords) { if (timeRecord.Speaking != null) { Console.WriteLine(timeRecord.Speaking+"===="+timeRecord.ID); } } } Console.ReadKey(); } public static List<TimeRecords> get_user_info_TimeRecord_info(int id) { //读取config中的字符串 string connstr = ConfigurationManager.ConnectionStrings["strCon"].ToString(); //IDbConnection IDbConnection connection = new SqlConnection(connstr); List<TimeRecords> list = new List<TimeRecords>() { }; using (connection) { string query = @"select * from TimeRecords u left join UserInfo ug on u.UserID = ug.ID where u.UserID= @id"; var aa = connection.Query<TimeRecords>(query, new { id = id }); foreach (var item in aa) { list.Add(item); } return list ; } } } }
