ORM-Dapper學習<四>.Dapper的基本用法

標籤：資料   對象   single   argument   動態類型   into   cut   for   throw   

1.    新增

public int insert_user_info(user_info_model user)        {            using (conn)            {                string query = @"INSERT INTO user_info (name ,pwd ,loginDate)                                  VALUES (@name,@pwd,@loginDate) ";                int row = conn.Execute(query, user);                //更新對象的Id為資料庫裡新增的Id,假如增加之後不需要獲得新增的對象，                //只需將對象添加到資料庫裡，可以將下面的一行注釋掉。                int max_id = SetIdentity(conn, id => user.id = id, "id", "user_info");                return row;            }        }注1：附加SetIdentity方法（.net3.5及以下）：/// <param name="conn">IDbConnection</param>/// <param name="setId">ActionID</param>/// <param name="primarykey">主鍵</param>/// <param name="tableName">表名</param>public int SetIdentity(IDbConnection conn, Action<int> setId, string primarykey, string tableName)        {            if (string.IsNullOrEmpty(primarykey)) primarykey = "id";            if (string.IsNullOrEmpty(tableName))            {                throw new ArgumentException("tableName參數不可為空，為查詢的表名");            }            string query = string.Format("SELECT max({0}) as id FROM {1}", primarykey, tableName);            NewId identity = conn.Query<NewId>(query, null).Single();            setId(identity.Id);            return identity.Id;        }注2：藉助Net4.0新增的dynamic動態類型實現SetIdentity(建議使用此方法)public int SetIdentity<user_info>( user_info_model user, IDbConnection conn, Action<int> setId)        {            dynamic identity = conn.Query("INSERT INTO user_info (name ,pwd ,loginDate)                                            VALUES (@name,@pwd,@loginDate);SELECT @@IDENTITY AS Id").Single();            NewId id = (NewId)identity.Id;            setId(id.Id);            return id.Id;        }

 

2.    更新

public int update_user_info_by_id(int id, string name)        {            using (conn)            {                string query = @"Update user_info SET name = @name WHERE id = @id ";                return conn.Execute(query, new { id, name });            }        }

 

3.    刪除

public int delete_user_info_by_id(int id)        {            using (conn)            {                string query = @"DELETE FROM user_info WHERE id = @id ";                return conn.Execute(query, new { id });            }        }

4.    查詢

public IEnumerable<user_info_model> get_user_info()        {            using (conn)            {                string query = @"SELECT * FROM user_info ";                return conn.Query<user_info_model>(query);            }        }

5.    傳參及SQL 語句關鍵字用法

public user_info_model get_user_info_by_id(int id, string name)        {            user_info_model user;            using (conn)            {                string query = "SELECT * FROM user_info WHERE id [email protected] AND name like @name ";                user = conn.Query<user_info_model>(query, new { id, name }).FirstOrDefault();                return user;            }        }

注一：當傳進來的參數名稱與sql語句中的相同時,不需指定參數（樣本如上），反之需要指定參數（樣本如下）：

參數：int id1, string name1Sql語句：同上樣本user = conn.Query<user_info_model>(query, new { id = id1, name = name1 }).SingleOrDefault();

 

ORM-Dapper學習<四>.Dapper的基本用法