LINQ To Sql 學習之

Linq自3.5 出世便受到很多人注意,有Linq to sql、Linq to objcet、Linq to Json、Linq to DataSet等等

今日 我也是出於好奇，便對其Linq to sql 進行了感受，感覺與以前使用java的Hibernate或說是Hibernate.net

完全不一樣，有點像女子。。。。呵呵

好了 ，開始我們的小小的CRUD操作吧

體繫結構： 

BLL:商務邏輯  DAL:資料訪問  Model:實體層 Interfaces介面

 資料庫表

create table users

{

id int identity(1,1) primary key not null,

userName varchar(50) not null,

pwd varchar(50) not null,

age int not null

}

 

namespace LinqDemo.Model
{
    [Table(Name = "Users")]
    public class UserInfo
    {

        public UserInfo()
        {

        }
        [Column(IsPrimaryKey = true,IsDbGenerated=true)]
        public int id { set; get; }

        [Column]
        public string userName { set; get; }

        [Column]
        public string pwd { set; get; }

        [Column]
        public int age { set; get; }
    }
}

 

namespace LinqDemo.Interface
{
    interface IOperate<T>
    {
            bool Add(T type);
            bool Update(T type);
            bool Delete(int id);
            List<T> GetList();
           
    }
}

 

 

namespace LinqDemo.DAL
{
    public class DALBase
    {
        private DALBase()
        {
        }
        private static DataContext dataContext = null;

        private static string connectString = "server=.;database=demoDB;user=sa;pwd=sa";

        public static DataContext GetDataContext()
        {
            if (dataContext == null)
            {
                dataContext = new DataContext(connectString);
            }
            return dataContext;
        }
    }
}

 

 

using LinqDemo.Interface;
using System.Data.Linq;
using LinqDemo.Model;
/// <summary>
///DALOperate 的摘要說明
/// </summary>
namespace LinqDemo.DAL
{
    public class UserInfoDAL : IOperate<UserInfo>
    {
        public UserInfoDAL()
        {
        }

        public static DataContext DC
        {
            get { return DALBase.GetDataContext(); }

        }
        #region IOperate<UserInfo> 成員

        //使用者添加
        public bool Add(UserInfo userInfo)
        {
            try
            {
                DC.GetTable<UserInfo>().InsertOnSubmit(userInfo);
                DC.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        //更新使用者
        public bool Update(UserInfo userInfo)
        {
            try
            {
                UserInfo user = DC.GetTable<UserInfo>().Single<UserInfo>(u => u.id == userInfo.id);
                user.userName = userInfo.userName;
                user.pwd = userInfo.pwd;
                user.age = userInfo.age;
                DC.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        //根據ID 刪除使用者
        public bool Delete(int id)
        {
            try
            {
                UserInfo userInfo = DC.GetTable<UserInfo>().Single<UserInfo>(u => u.id == id);
                DC.GetTable<UserInfo>().DeleteOnSubmit(userInfo);
                DC.SubmitChanges();
                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }

        //查詢使用者列表
        public System.Collections.Generic.List<UserInfo> GetList()
        {
            return DC.GetTable<UserInfo>().ToList<UserInfo>();
        }

        #endregion

    }
}

 

namespace LinqDemo.BLL
{
    public class UserInfoBLL
    {
        public UserInfoBLL()
        {
        }

        IOperate<UserInfo> userDAL = new UserInfoDAL();

        public bool AddUser(UserInfo userInfo)
        {
            return userDAL.Add(userInfo);
        }

        public bool Update(UserInfo userInfo)
        {
            return userDAL.Update(userInfo);
        }

        public bool DeleteUser(int id)
        {
            return userDAL.Delete(id);
        }

        public List<UserInfo> GetUserInfoList()
        {
            return userDAL.GetList();
        }
    }
}

 

 

==view層進行類比操作

 

  protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        SetDataBind();
    }

    UserInfoBLL userBLL = new UserInfoBLL();

    public void SetDataBind()
    {
        GridView1.DataSource = userBLL.GetUserInfoList();

        GridView1.DataBind();
    }

    //添加
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        UserInfo userInfo = new UserInfo { userName = "test", pwd = "456",age=11 };
        userBLL.AddUser(userInfo);
        SetDataBind();
    }

    //修改
    protected void btnEdit_Click(object sender, EventArgs e)
    {
        UserInfo userInfo = new UserInfo { id=1,userName = "我是更新的資料", pwd = "456", age = 11 };
        userBLL.Update(userInfo);
        SetDataBind();
    }

    //刪除
    protected void btnDel_Click(object sender, EventArgs e)
    {
        userBLL.DeleteUser(2);
        SetDataBind();
    }

 

  簡單的CRUD操作完成   沒有一句SQL語句 不錯吧