ArrayList集合+LINQ to Object實作資料庫對象記憶體通路（以登入為例）

1.    開發目的：将資料庫資料讀取到ArrayList集合，實作在記憶體中查詢資料庫對象，将資料庫和資料通路分離。

2.    開發環境：windows 7+Visual Studio2017+SQLService 2012

3.    開發過程：

1)     編寫資料庫通路類

2)     封裝對象

3)     執行SQL語句，将資料對象傳回到ArrayList集合，利用LINQ查詢對象

4.    開發整體結構圖

5.    開發源碼

1）    資料庫通路輔助SQLHelp

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace SQL
{
    public  class SqlHelp
    {
        public static SqlDataReader ExecuteReader(string cmdStr)
        {
            string conStr= "Data Source=VICTORY;Initial Catalog = test; Persist Security Info = True; User ID = sa; Password = 123456";
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(cmdStr, con);
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
  
        }
    }

}
           

2）對象封裝UserModel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace USER
{
    class Userinfo
    {
        public Userinfo(string name, string pwd)
        {
            this.name = name;
            this.pwd = pwd;
        }
        public string name;
        public string pwd;
    }
}
           

3）資料讀取與通路 UserDAL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using SQL;
using USER;
using System.Collections;


namespace UserDAL
{

    class UserLoginDAL
    {
        public bool LoginDAL(string name, string pwd)
        {
            string cmd = "select * from users3_0";
            SqlDataReader da = SqlHelp.ExecuteReader(cmd);
            ArrayList list = new ArrayList();
            while (da.Read())
            {
                string m = da["name"].ToString();
                string n = da["pwd"].ToString();
                Userinfo u = new Userinfo(m, n);
                list.Add(u);
            }

            Userinfo c = new Userinfo(name, pwd);

            var query = from Userinfo st in list
                        where st.name.Contains(c.name) && st.pwd.Contains(c.pwd)
                        select st;
            if(query.Count()==1)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

    }
}
           

4）使用者界面 Buttorn

private void button1_Click(object sender, EventArgs e)
        {
            string name = textBox1.Text.ToString().Trim();
            string pwd = textBox2.Text.ToString().Trim();
            UserDAL.UserLoginDAL a = new UserDAL.UserLoginDAL();
            if(a.LoginDAL(name, pwd))
            {
                MessageBox.Show("1");
            }
            else
            {
                MessageBox.Show("0");
            }
            
        }
           

6.運作結果截圖

附：資料庫資料表圖