WPF, 오라클연동, Linq쿼리식,OracleDataAdapter, LiveView, 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET, ItemTemplate)
n 이전 예제에 버튼을 하나 더 추가하고 버튼을 클릭했을 때 OracleDataAdapter를 이용하여 DataSet에 DataTable로 EMP 테이블을 생성하고 이를 Linq 쿼리식을 이용하여 LiveView에 바인딩 시키는 기능을 추가해 보자.
n MainWindow.xaml
<Window x:Class="WpfOracleTest.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:WpfOracleTest"
mc:Ignorable="d"
Title="MainWindow" Height="350" Width="461.659">
<Grid Margin="0,0,3.2,-0.2">
<Button x:Name="button" Content="DB Connect" HorizontalAlignment="Left" Margin="20,28,0,0" VerticalAlignment="Top" Width="75" RenderTransformOrigin="0.391,-0.29" Click="DB_Connect"/>
<ListView Margin="10,66,10,10" Name="lstView">
<ListView.ItemTemplate>
<DataTemplate>
<WrapPanel>
<TextBlock Text="Empno: " />
<TextBlock Text="{Binding Empno}" FontWeight="Bold" />
<TextBlock Text=", " />
<TextBlock Text=" (" />
<TextBlock Text="Ename: " />
<TextBlock Text="{Binding Ename}" TextDecorations="Underline" FontWeight="Bold" />
<TextBlock Text=")" />
<TextBlock Text="{Binding Job}" Foreground="Blue" Cursor="Hand" />
</WrapPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
<Button x:Name="button1" Content="Get Data" HorizontalAlignment="Left" Margin="114,28,0,0" VerticalAlignment="Top" Width="75" Click="Select_Emp"/>
<Button x:Name="button2" Content="Get Data from Adapter" HorizontalAlignment="Left" Margin="205,28,0,0" VerticalAlignment="Top" Width="133" Click="Select_Emp2"/>
<Button x:Name="button3" Content="From Linq" HorizontalAlignment="Left" Margin="357,28,0,0" VerticalAlignment="Top" Width="75" Click="Select_Emp3"/>
</Grid>
</Window>
|
n MainWindow.xaml.cs
using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using System.Linq;
namespace WpfOracleTest
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
OracleConnection conn;
private void DB_Connect(object sender, RoutedEventArgs e)
{
try
{
string strCon = @"Data Source=(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.27)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = topcredu)
)
) ;User Id=scott;Password=tiger";
//string strCon = "data source=topcredu;User ID=scott;Password=tiger";
conn = new OracleConnection(strCon);
conn.Open();
MessageBox.Show("DB Connection OK!");
}
catch(Exception error)
{
MessageBox.Show(error.ToString());
}
}
/* Connection, Command, DataReader를 통한 데이터 추출 */
private void Select_Emp(object sender, RoutedEventArgs e)
{
string sql = "select empno, ename, job from emp ";
OracleCommand comm = new OracleCommand();
if(conn == null) DB_Connect(this, null);
comm.Connection = conn;
comm.CommandText = sql;
OracleDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
List<EmpViewModel> emps = new List<EmpViewModel>();
while (reader.Read())
{
emps.Add(new EmpViewModel() { Empno = reader.GetInt32(reader.GetOrdinal("empno")),
Ename = reader.GetString(reader.GetOrdinal("ename")),
Job = reader.GetString(reader.GetOrdinal("job"))
});
}
lstView.ItemsSource = emps;
}
/* OracleDataAdapter를 통한 EMP 테이터 추출 */
private void Select_Emp2(object sender, RoutedEventArgs e)
{
OracleDataAdapter adapter = new OracleDataAdapter();
string sql = "select empno, ename, job from emp ";
OracleCommand comm = new OracleCommand();
if (conn == null) DB_Connect(this, null);
comm.Connection = conn;
adapter.SelectCommand = comm;
comm.CommandText = sql;
DataSet ds = new DataSet("emps");
adapter.Fill(ds, "emp");
// Clear the ListView control
lstView.Items.Clear();
List<EmpViewModel> emps = new List<EmpViewModel>();
for (int i = 0; i < ds.Tables["emp"].Rows.Count; i++)
{
DataRow dr = ds.Tables["emp"].Rows[i];
emps.Add(new EmpViewModel()
{
Empno = System.Convert.ToInt32(dr["empno"]),
Ename = dr["ename"].ToString(),
Job = dr["job"].ToString()
});
}
lstView.ItemsSource = emps;
conn.Close();
}
/* Linq 쿼리식을 통한 EMP 테이터 추출 */
private void Select_Emp3(object sender, RoutedEventArgs e)
{
OracleDataAdapter adapter = new OracleDataAdapter();
string sql = "select empno, ename, job from emp ";
OracleCommand comm = new OracleCommand();
if (conn == null) DB_Connect(this, null);
comm.Connection = conn;
adapter.SelectCommand = comm;
comm.CommandText = sql;
DataSet ds = new DataSet("emps");
adapter.Fill(ds, "emp");
// Clear the ListView control
lstView.Items.Clear();
List<EmpViewModel> emps = new List<EmpViewModel>();
IEnumerable query = from myemp1 in ds.Tables["emp"].AsEnumerable()
where myemp1["job"].Equals("CLERK")
select myemp1;
foreach (DataRow dr in query)
{
emps.Add(new EmpViewModel()
{
Empno = System.Convert.ToInt32(dr["empno"]),
Ename = dr["ename"].ToString(),
Job = dr["job"].ToString()
});
}
lstView.ItemsSource = emps;
conn.Close();
}
}
}
|
n 실행화면
댓글 없음:
댓글 쓰기