WPF 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET, ItemTemplate, Linq쿼리, OracleDataAdapter),wpf학원
http://ojc.asia/bbs/board.php?bo_table=WPF&wr_id=177
WPF 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET,OracleDataAdapter, Li
WPF 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET, ItemTemplate, Linq쿼리, OracleDataAdapter)ODP.NET을 이용하여 오라클 DB의 EMP 테이블의 내용을 ListView에 출력하는 예제를
ojc.asia
https://www.youtube.com/watch?v=TA1eChovYEU&list=PLxU-iZCqT52Cmj47aKB1T-SxI33YL7rYS&index=8
https://www.youtube.com/watch?v=2iD0UZBuWgs&list=PLxU-iZCqT52Cmj47aKB1T-SxI33YL7rYS&index=3
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
오라클 클라이언트 설치 후 오라클홈(C:\app\사용자\product\11.2.0\client_1) 디렉토리 아래 NETWORK\Admin 폴더의 tnsnames.ora 파일에 속하고자 하는 Oracle Server의 접속정보 추가 // ONJ라는 TNS NAME을 정의하는데 C# 코드에서 “TOPCREDU”를 DB 접속 자열의 Data Source 항목에 기술한다.
TOPCREDU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = topcredu) // DB명
)
)
WPF 응용프로그램 형태의 프로젝트를 생성( 프로젝트명 : WpfOracleTest ) 참조 추가 : 프로젝트 생성 후 솔루션 탐색기 -> 참조 -> 참조추가 선택 후 확장 >> Oracle.MaangedDataAccess를 선택하여 추가하자. 오라클홈\network\admin 아래에 tnsnames.ora을 생성 테스트 PC가 64Bit 라면 프로젝트에서 마우스 오른쪽 속성 선택 후 빌드 -> 플랫폼 대상(Target Platform)을 x64로 선택한다. (32Bit인 경우 생략) using System;
using System.ComponentModel;
using System.Windows.Input;
namespace WpfOracleTest
{
public class EmpViewModel {
int empno = 0;
string ename = string .Empty;
string job = string .Empty;
// public 프로퍼티
public int Empno
{
get { return empno; }
set { this .empno = value ; }
}
// public 프로퍼티
public string Ename
{
get { return ename; }
set { this .ename = value ; }
}
// public 프로퍼티
public string Job
{
get { return job; }
set { this .job = value ; }
}
}
}
< 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 ="43,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 ="137,28,0,0" VerticalAlignment ="Top" Width ="75" Click ="Select_Emp"/>
</ Grid >
</ Window >
using System;
using System.Collections.Generic;
using System.Windows;
using System.Data;
using Oracle.ManagedDataAccess.Client;
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=topcredu;User ID=scott;Password=tiger" ;
conn = new OracleConnection (strCon);
conn.Open();
MessageBox .Show( "DB Connection OK!" );
}
catch ( Exception error)
{
MessageBox .Show(error.ToString());
}
}
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;
reader.Close();
}
}
}
6.16 OracleDataAdapter, ListView, 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET, ItemTemplate)
이전 예제에 버튼을 하나 추가하고 버튼을 클릭했을 때 OracleDataAdapter를 이용하여 DataSet에 DataTable로 EMP 테이블을 생성하고 이를 LiveView에 바인딩 시키는 기능을 추가해 보자. 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 ="43,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 ="137,28,0,0" VerticalAlignment ="Top" Width ="75" Click ="Select_Emp"/>
< Button x : Name ="button2" Content ="Get Data from Adapter" HorizontalAlignment ="Left" Margin ="228,28,0,0" VerticalAlignment ="Top" Width ="133" Click ="Select_Emp2"/>
</ Grid >
</ Window >
using System;
using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;
using System.Data;
using Oracle.ManagedDataAccess.Client;
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=topcredu;User ID=scott;Password=tiger" ;
conn = new OracleConnection (strCon);
conn.Open();
MessageBox .Show( "DB Connection OK!" );
}
catch ( Exception error)
{
MessageBox .Show(error.ToString());
}
}
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;
reader.Close();
}
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;
comm.CommandText = sql;
adapter.SelectCommand = comm;
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();
}
}
}
6.17 Linq쿼리식,OracleDataAdapter, LiveView, 데이터 바인딩을 이용하여 오라클 EMP 테이블 데이터를 ListView에 뿌리기(ODP.NET, ItemTemplate)
이전 예제에 버튼을 하나 더 추가하고 버튼을 클릭했을 때 OracleDataAdapter를 이용하여 DataSet에 DataTable로 EMP 테이블을 생성하고 이를 Linq 쿼리식을 이용하여 LiveView에 바인딩 시키는 기능을 추가해 보자. 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 >
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=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();
}
}
}
#WPF교육, #WPF오라클, #WPF데이터바인딩, #WPF오라클예제, #WPF학원, #WPFORACLE
WPF교육, WPF오라클, WPF데이터바인딩, 오라클데이터바인딩, WPFORACLE, 데이터바인딩, WPF오라클실습
