(C#,WPF,ADO.NET,오라클 ODP.NET예제)C#,WPF에서 DataSet의 Linq쿼리식을 이용하여 데이터 추출(XAML윈도우,ListView이용)
n 오라클홈 디렉토리 아래 NETWORK\Admin 폴더에 tnsnames.ora 파일에 다음 접속하고자 하는 Oracle Server의 접속정보 추가
ONJ =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = onj) ß DB명
)
)
n WPF 응용프로그램 형태의 프로젝트를 생성(프로젝트명 : WpfOracleTest)
n 참조 추가 : 프로젝트 생성 후 솔루션 탐색기 -> 참조 -> 참조추가 선택 후 “찾아보기” 버튼을 클릭하여%ORACLE_HOME%\ODP.NET\bin\2.x\Oracle.DataAccess.dll을 선택하여 추가하자.
n 테스트 PC가 64Bit 윈도우10 이므로 프로젝트에서 마우스 오른쪽 속성 선택 후 빌드 -> 플랫폼 대상(Target Platform)을 x64로 선택한다. (32Bit인 경우 생략)
n
EmpViewModel.cs
using System;
using System.ComponentModel;
using System.Windows.Input;
namespace WpfOracleTest
{
public class EmpViewModel : INotifyPropertyChanged
{
int empno = 0;
string ename = string.Empty;
string job = string.Empty;
//속성이 바뀔때 이벤트 발생하도록 이벤트 정의
public event PropertyChangedEventHandler PropertyChanged;
// 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; }
}
protected void OnPropertyChanged(string propertyName)
{
//이벤트를 발생시킨다.
if (PropertyChanged != null)
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
n MainWindow.xaml
<Window x:Class="WpfOracleTest.MainWindow"
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.DataAccess.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=onj;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();
}
}
}
