不懂不熟悉的時候會覺得很難，其實懂了之後是很簡單的
我一部分是從這本書學的
http://olivermode.pixnet.net/blog/post/309044256-C# LINQ SQL Winform - Visual C# 2013 視窗程式設計
另外有個影片不錯特別列一下
https://www.youtube.com/watch?v=V8jQHsBvwmg
另一大部分是靠自己看其他書和網路漫遊和請教他人學的。
以下開始
首先是SQL版，(下一個是OLEDB版，兩者達到的效果完全一樣，只是code寫法不太一樣)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsDBShow20151022
{
public partial class Form2 : Form
{
//建立sql 連結
SqlConnection sqlConnection = new SqlConnection(
@"Data Source=這裡填你自己的連線字串;Initial Catalog=.....;User ID=...;Password=.......");
public Form2()//視窗的建構子
{
InitializeComponent();
}
//勾選時引發的方法
private void treeView2_AfterCheck(object sender, TreeViewEventArgs e)
{
// The code only executes if the user caused the Checked state to change.
if (e.Action != TreeViewAction.Unknown)
{
//建立sql連結產生需要的總資料表
SqlCommand sqlCommandEm = new SqlCommand("Select Name,EmployeeId,OrganizationId From Employee");//雙引號""內是寫你要對資料庫下的sql，它的作用跟你直接在MSSQL環境裡下的效果會是一樣的，只是在C#這裡下，C#會再把查詢字串傳到SQL那邊去查詢再拿回資料
DataTable dataTableEm = new DataTable();
SqlDataAdapter sqlDataAdapterEm = new SqlDataAdapter(sqlCommandEm.CommandText, sqlConnection);
sqlDataAdapterEm.Fill(dataTableEm);
//使用CheckAllCheckedNodesId方法取得有勾選的node以利之後的查詢及進行另一個字串處理，分別各使用一個List處理
List<Guid> CheckedNodeList = new List<Guid>();
List<StringValue> CheckedNodeListIdName = new List<StringValue>();
foreach (TreeNode node in treeView2.Nodes)
{
CheckAllCheckedNodesId(node, CheckedNodeList, CheckedNodeListIdName);
}
//用LINQ語法查詢總資料表取得需要的資料，Where的部分查詢有勾選到的部門ID，Select部分選了三個欄位
var myQuery =
dataTableEm.AsEnumerable().
Where(em => CheckOrgaId(CheckedNodeList, em.Field<Guid>("OrganizationId"))).
Select(em =>
new
{
員工姓名 = em.Field<string>("name"),
部門ID = em.Field<Guid>("OrganizationId").ToString(),
員工ID = em.Field<Guid>("EmployeeId").ToString()
}
);
//將兩個結果分別顯示在兩個dataGridView
dataGridView2.DataSource = myQuery.ToList();
dataGridView1.DataSource = CheckedNodeListIdName;
}
}
//檢測Employee的OrganizationId是否是屬於已勾選的部門
private bool CheckOrgaId(List<Guid> gList, Guid g)
{
bool b = false;
foreach (var gg in gList)
{
if (gg == g)
b = true;
}
return b;
}
//取得有勾選的node以利之後的查詢及進行另一個字串處理，分別各使用一個List處理
private void CheckAllCheckedNodesId(TreeNode treeNode, List<Guid> g, List<StringValue> s)
{
if (treeNode.Checked)
{
g.Add(new Guid(treeNode.Name));
s.Add(new StringValue(treeNode.Text + " 的ID是 " + treeNode.Name.ToLower()));
}
//呼叫自己的方法對子節點進行遞回處理(和上面相同動作)
foreach (TreeNode node in treeNode.Nodes)
CheckAllCheckedNodesId(node, g, s);
}
private void Form2_Load(object sender, EventArgs e)
{
//動態生成樹TreeView
//建立sql連結及產生資料表，取得資料表的Id、ParentId、Name
SqlCommand sqlCommandOrga = new SqlCommand("select OrganizationId, ParentId, Name From Organization");
DataTable dataTableOrga = new DataTable();
SqlDataAdapter sqlDataAdapterOrga = new SqlDataAdapter(sqlCommandOrga.CommandText, sqlConnection);
sqlDataAdapterOrga.Fill(dataTableOrga);
//用底下新建的TreeViewProperty建立一個List將資料表中的每一列屬性都new一個TreeViewProperty物件以利之後生成
List<TreeViewProperty> orgResults = new List<TreeViewProperty>();
foreach (DataRow dr in dataTableOrga.Rows)
{
orgResults.Add(
new TreeViewProperty
(
dr["OrganizationId"].ToString(),
dr["Name"].ToString(),
dr["ParentId"].ToString()
)
);
}
//先找出RootNode，由ParentId為空可找出
TreeNode RootNode = new TreeNode();
for (int i = 0; i < orgResults.Count; i++)
{
if (orgResults[i].GetParentId() == "")
{
RootNode.Name = orgResults[i].GetOragId();
RootNode.Text = orgResults[i].GetOragName();
treeView2.Nodes.Add(RootNode);//將TreeView加入第一個根節點
}
}
//用AddNodeInTreeView2遞迴直接動態生成一棵樹
AddNodeInTreeView2(orgResults, RootNode);
//讓TreeView有勾選功能
treeView2.CheckBoxes = true;
}
//動態生成樹的方法，上方已把每個點的資料生成為一個list，方法中就對List的父子關係做檢查
//並將找到的子節點加入父節點的底下，並再對子節點做遞迴
private void AddNodeInTreeView2(List<TreeViewProperty> L, TreeNode t)
{
for (int i = 0; i < L.Count; i++)//對同一層的每個節點進行處理
{
if (L[i].GetParentId() == t.Name)
{
TreeNode tmpNode = new TreeNode();
tmpNode.Name = L[i].GetOragId();
tmpNode.Text = L[i].GetOragName();
t.Nodes.Add(tmpNode);
//呼叫自身遞迴對子節點逐一進行處理
AddNodeInTreeView2(L, tmpNode);
}
}
}
}
//因為String少了些特性所以再創一個StringValue class在某些地方代替String
public class StringValue
{
public StringValue(string s)
{
_value = s;
}
public string Value { get { return _value; } set { _value = value; } }
string _value;
}
//將資料表中的資料填入類別以利處理
class TreeViewProperty
{
public string _orgaId;
private string _orgaName;
private string _parentId;
public TreeViewProperty(string orgId, string orgName, string parentId)
{
_orgaId = orgId;
_orgaName = orgName;
_parentId = parentId;
}
public string GetOragId() { return _orgaId; }
public string GetOragName() { return _orgaName; }
public string GetParentId() { return _parentId; }
override public string ToString()
{
return _parentId + " " + _orgaId + " " + _orgaName;
}
}
}
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
OLEDB版本基本上相同內容
using System.Data;
using System.Drawing;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsDBShow20151022
{
//因為string本身屬性不符需要所以多自訂一個string class
public partial class Form2 : Form
{
public Form2()//視窗的建構子
{
InitializeComponent();
}
//沒有用到的Afterselect方法
private void treeView1_AfterSelect(object sender, TreeViewEventArgs e) { }
//宣告初始資料庫
private OleDbConnectionStringBuilder _showTree =
new OleDbConnectionStringBuilder
(@"Provider=SQLOLEDB; Data Source=這裡填你自己的連線字串;Initial Catalog=.....;User ID=...;Password=.......");
//資料庫需要的變數宣告
private DataSet _employeesDataSet = new DataSet();
private DataTable _employees;
private OleDbDataAdapter _employeesAdapter;
//勾選時引發的方法
private void treeView2_AfterCheck(object sender, TreeViewEventArgs e)
{
// The code only executes if the user caused the Checked state to change.
if (e.Action != TreeViewAction.Unknown)
{
using (OleDbConnection connection = new OleDbConnection(_showTree.ConnectionString))
{
//建立連結
OleDbCommand command = new OleDbCommand(_showTree.ConnectionString);
connection.Open();
//取得需要的資料表欄位並Fill入_myAdapter
string querystring = "Select Name,EmployeeId,OrganizationId From Employee";
_employeesAdapter = new OleDbDataAdapter(querystring, connection);
_employeesAdapter.Fill(_employeesDataSet, "EmployeeTable");
_employees = _employeesDataSet.Tables["EmployeeTable"];
//使用CheckAllCheckedNodesId方法取得有勾選的node以利之後的查詢及進行另一個字串處理，分別各使用一個List處理
List<Guid> CheckedNodeList = new List<Guid>();
List<StringValue> CheckedNodeListIdName = new List<StringValue>();
foreach (TreeNode node in treeView2.Nodes)
{
CheckAllCheckedNodesId(node, CheckedNodeList,CheckedNodeListIdName);
}
//用LINQ語法查詢，Where的部分查詢有勾選到的部門ID，Select部分選了三個欄位
var myQuery =
_employees.AsEnumerable().
Where(em => CheckOrgaId(CheckedNodeList,em.Field<Guid>("OrganizationId"))).
Select(em =>
new
{
員工姓名 = em.Field<string>("name"),
部門ID = em.Field<Guid>("OrganizationId"),
員工ID = em.Field<Guid>("EmployeeId")
}
);
//將兩個結果分別顯示在兩個dataGridView
dataGridView2.DataSource = myQuery.ToList();
dataGridView1.DataSource = CheckedNodeListIdName;
}
}
}
//檢測Employee的OrganizationId是否是屬於已勾選的部門
private bool CheckOrgaId(List<Guid> gList , Guid g)
{
bool b=false;
foreach (var gg in gList)
{
if (gg == g)
b= true;
}
return b;
}
//取得有勾選的node以利之後的查詢及進行另一個字串處理，分別各使用一個List處理
private void CheckAllCheckedNodesId(TreeNode treeNode, List<Guid> g, List<StringValue> s)
{
if (treeNode.Checked)
{
g.Add(new Guid(treeNode.Name));
s.Add(new StringValue(treeNode.Text+" 的ID是 "+treeNode.Name.ToLower()));
}
foreach (TreeNode node in treeNode.Nodes)
CheckAllCheckedNodesId(node,g,s);
}
private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e){}
private DataSet _orgaDataSet = new DataSet();
private DataTable _orga;
private OleDbDataAdapter _orgaAdapter;
private void Form2_Load(object sender, EventArgs e)
{
using (OleDbConnection connection = new OleDbConnection(_showTree.ConnectionString)) {
//建立連結
OleDbCommand command = new OleDbCommand(_showTree.ConnectionString);
connection.Open();
//取得需要的資料表欄位並Fill入_myAdapter
string querystring = "select OrganizationId, ParentId, Name From Organization";
_orgaAdapter = new OleDbDataAdapter(querystring, connection);
_orgaAdapter.Fill(_orgaDataSet, "OrgaTable");
_orga = _orgaDataSet.Tables["OrgaTable"];
//方便生成一個"TreeViewProperty的List以印出資料"而前置生成的三個List
var QueryOrgaId =
_orga.AsEnumerable().
Select(em =>em.Field<Guid>("OrganizationId").ToString() );
List<object> ListOrgaId = new List<object>(QueryOrgaId.ToList());
var QueryOrgaName =
_orga.AsEnumerable().
Select(em => em.Field<string>("Name").ToString());
List<object> ListOrgaName = new List<object>(QueryOrgaName.ToList());
var QueryOrgaParentId =
_orga.AsEnumerable().
Select(em => em.Field<Guid?>("ParentId").ToString());
List<object> ListOrgaParentId = new List<object>(QueryOrgaParentId.ToList());
List<TreeViewProperty> ListTreeViewProperty = new List<TreeViewProperty>();
for (int i = 0; i < ListOrgaId.Count; i++)
{
ListTreeViewProperty.Add(
new TreeViewProperty(
ListOrgaId[i].ToString(),ListOrgaName[i].ToString(),ListOrgaParentId[i].ToString()
)
);
}
TreeNode RootNode = new TreeNode();
for (int i = 0; i < ListTreeViewProperty.Count; i++)
{
if (ListTreeViewProperty[i].GetParentId() == "")
{
RootNode.Name = ListTreeViewProperty[i].GetOragId();
RootNode.Text = ListTreeViewProperty[i].GetOragName();
treeView2.Nodes.Add(RootNode);
}
}
AddNodeInTreeView2(ListTreeViewProperty, RootNode);
treeView2.CheckBoxes = true;
}
}
private void AddNodeInTreeView2(List<TreeViewProperty> L, TreeNode t )
{
for (int i = 0; i < L.Count; i++)
{
if (L[i].GetParentId() == t.Name)
{
TreeNode tmpNode= new TreeNode();
tmpNode.Name = L[i].GetOragId();
tmpNode.Text = L[i].GetOragName();
t.Nodes.Add(tmpNode);
AddNodeInTreeView2(L,tmpNode);
}
}
}
private void treeView1_AfterCheck(object sender, TreeViewEventArgs e)
{
}
}
public class StringValue
{
public StringValue(string s)
{
_value = s;
}
public string Value { get { return _value; } set { _value = value; } }
string _value;
}
class TreeViewProperty
{
public string _orgaId;
private string _orgaName;
private string _parentId;
public TreeViewProperty(string s, string s2, string s3)
{
_orgaId = s;
_orgaName = s2;
_parentId = s3;
}
public string GetOragId() { return _orgaId; }
public string GetOragName() { return _orgaName; }
public string GetParentId(){return _parentId;}
override public string ToString()
{
return _parentId + " " + _orgaId + " " + _orgaName;
}
}
}
全站熱搜
留言列表
