닷넷,ADO.NET예제(오라클,C#주소록윈폼,ListVew,DataGrid,MDIForm응용예에제소스)
[닷넷Windorm강좌]C# ADO.NET, 윈폼을 이용한 주소록 작성(,트리메뉴,ListView, DataGrid, TreeView)소스
:namespace
prefix = o ns = "urn:schemas-microsoft-com:office:office" />
1. 아래와 같은 기능을 가진 C#
주소록 프로그램을 작성하자.
- 로그인 기능
- DataSet, DataGrid를 이용한 주소록 데이터 수정 기능
- ListView에서 정렬 기능
- MDI Form 이용
- TreeView이용
로그 파일을 위해 c:\dotnet 폴더를 만들어 두어야 한다.
2. 실행화면
[그림0, 전체 프로젝트
화면]
[그림
1, 로그인 화면]
[그림2, LiveView로 주소록 로딩]
[그림3, DataGrid로 주소록 로딩]
4. 테이블 생성
create table AddrBook (
name varchar2(20) not
null primary key,
sex varchar2(2) not
null constraint ck_sex check (sex in ('M','F')),
addr varchar2(50),
tel varchar2(20) not
null
)
create table member (
id
varchar2(20) primary key,
pwd
varchar2(20)
)
SQL> drop table member;
테이블이
삭제되었습니다.
SQL> create table member (
2 id varchar2(20) primary
key,
3 pwd varchar2(20)
4 );
테이블이
생성되었습니다.
SQL> insert into member (id, pwd) values
('onj','onj');
1 개의
행이
만들어졌습니다.
SQL> commit;
커밋이
완료되었습니다.
5. CodeFile1.cs(공통코드파일)
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlTypes;
using System.Windows.Forms;
using System.Collections;
public enum Sorting { Ascending, Descending
};
//ListView에서
컬럼을
누르면
정렬이
되게
하기
위해...
public class ColumnSorter : IComparer
{
public int
currentColumn = -1; // 현재
선택한
컬럼
public int
previousColumn = -1; // 전에
선책한
컬럼
public Sorting
sort = Sorting.Ascending;
public int
Compare(object x, object y)
{
ListViewItem rowA = (ListViewItem)x;
ListViewItem rowB = (ListViewItem)y;
int result=0;
switch(sort)
{
case Sorting.Ascending: //
오름차순
정렬을
원할때
result = String.Compare(rowA.SubItems[currentColumn].Text,rowB.SubItems[currentColumn].Text);
break;
case Sorting.Descending: //
내림차순
정렬을
원할때
result = String.Compare(rowB.SubItems[currentColumn].Text,rowA.SubItems[currentColumn].Text);
break;
}
return result;
}
public ColumnSorter() {}
}
public class Common_DB
{
//-----------------------------------------------------------------------------
// DataBase Connection
//-----------------------------------------------------------------------------
public static OleDbConnection
DBConnection()
{
OleDbConnection
Conn;
//아래는
오라클용
접속
문자열, data source 애는 tnsnames.ora 파일에
있는 Alias명을
넣으면
됩니다.
string ConStr = ("Provider=MSDAORA;data source=onj;User
ID=scott;Password=tiger");
Conn = new OleDbConnection(ConStr);
return Conn;
}
//-----------------------------------------------------------------------------
// DataSelect
//-----------------------------------------------------------------------------
public static OleDbDataReader DataSelect(string sql, OleDbConnection Conn)
{
try
{
OleDbCommand myCommand = new OleDbCommand(sql, Conn);
return
myCommand.ExecuteReader();
}
catch(Exception ex)
{
//Log File에
출력
MessageBox.Show(sql + "\n" + ex.Message, "DataSelect");
return null;
}
finally
{
}
}
//-----------------------------------------------------------------------------
// DataDelete, DataInsert
//-----------------------------------------------------------------------------
public static bool DataManupulation(string sql, OleDbConnection Conn)
{
try
{
OleDbCommand myCommand = new OleDbCommand(sql, Conn);
myCommand.ExecuteNonQuery();
return true;
}
catch(Exception ex)
{
//Log File에
출력
MessageBox.Show(sql + "\n" + ex.Message, "DataManupulation");
return false;
}
finally
{
}
}
}
6. Log.cs(로그출력용)
using System;
using System.IO;
public class Log
{
public static void WriteLine(string name, string
e)
{
string filename = @"c:\dotnet\" + name;
string logtime = DateTime.Now.ToString();
FileStream aFile = new FileStream(filename, FileMode.Append);
StreamWriter aWriter = new StreamWriter(aFile, System.Text.Encoding.Default);
aWriter.WriteLine("[" + logtime +
"] ");
aWriter.WriteLine(e);
aWriter.Flush();
aWriter.Close();
}
}
7. FrmLogin.cs(로그인화면)
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace 주소록
{
/// <summary>
///
FrmLogin에
대한
요약
설명입니다.
/// </summary>
public class FrmLogin : System.Windows.Forms.Form
{
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.TextBox txtID;
private System.Windows.Forms.TextBox txtPWD;
///
<summary>
///
필수
디자이너
변수입니다.
///
</summary>
private System.ComponentModel.Container components = null;
public
FrmLogin()
{
//
// Windows Form 디자이너
지원에
필요합니다.
//
InitializeComponent();
//
// TODO:
InitializeComponent를
호출한
다음
생성자
코드를
추가합니다.
//
}
///
<summary>
///
사용
중인
모든
리소스를
정리합니다.
///
</summary>
protected override void
Dispose( bool disposing
)
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing
);
}
#region Windows Form Designer generated code
///
<summary>
///
디자이너
지원에
필요한
메서드입니다.
///
이
메서드의
내용을
코드
편집기로
수정하지
마십시오.
///
</summary>
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.txtID = new System.Windows.Forms.TextBox();
this.txtPWD = new System.Windows.Forms.TextBox();
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// label1
//
this.label1.AutoSize = true;
this.label1.Font = new System.Drawing.Font("굴림", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(129)));
this.label1.Location = new System.Drawing.Point(16, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(19, 15);
this.label1.TabIndex =
0;
this.label1.Text = "ID";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Font = new System.Drawing.Font("굴림", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(129)));
this.label2.Location = new System.Drawing.Point(16, 40);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(70, 15);
this.label2.TabIndex =
1;
this.label2.Text = "PassWord";
//
// txtID
//
this.txtID.Location = new System.Drawing.Point(104, 8);
this.txtID.Name = "txtID";
this.txtID.Size = new System.Drawing.Size(120, 21);
this.txtID.TabIndex =
2;
this.txtID.Text = "";
//
// txtPWD
//
this.txtPWD.Location = new System.Drawing.Point(104, 32);
this.txtPWD.Name = "txtPWD";
this.txtPWD.PasswordChar = '*';
this.txtPWD.Size = new System.Drawing.Size(120, 21);
this.txtPWD.TabIndex =
3;
this.txtPWD.Text = "";
//
// button1
//
this.button1.Location = new System.Drawing.Point(232, 8);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(56, 48);
this.button1.TabIndex =
4;
this.button1.Text = "로그인";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// FrmLogin
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(304, 69);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.button1,
this.txtPWD,
this.txtID,
this.label2,
this.label1});
this.Name = "FrmLogin";
this.Text = "FrmLogin";
this.ResumeLayout(false);
}
#endregion
[STAThread]
static void Main()
{
Application.Run(new FrmLogin());
}
private OleDbConnection LocalConn;
private void button1_Click(object sender, System.EventArgs e)
{
OleDbDataReader
myReader;
string sql = null;
try
{
LocalConn = Common_DB.DBConnection();
LocalConn.Open();
if (txtID.Text == "" || txtPWD.Text == "")
{
MessageBox.Show("ID 또는 Password를
입력
하세요...");
return;
}
sql = "select pwd from member ";
sql += " where id = " + "'" + txtID.Text + "'";
myReader = Common_DB.DataSelect(sql,
LocalConn);
if (myReader.Read())
{
if (txtPWD.Text != myReader["pwd"].ToString())
{
MessageBox.Show("Password가
맞지
않습니다...");
return;
}
}
else
{
MessageBox.Show("등록되지
않은 ID 입니다.");
return;
}
//----------- ID가 PWD가
맞는
경우
MDIMain m = new MDIMain();
m.Show();
m.Owner = this;
//---------------------------------
this.Hide();
}
catch(Exception e1)
{
Log.WriteLine("FrmLogin",
e1.ToString());
MessageBox.Show("FrmLogin", "로그인
오류! " + sql );
}
}
}
}
8. FrmGrid.cs (DataGrid화면)
using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
namespace 주소록
{
/// <summary>
///
FrmGrid에
대한
요약
설명입니다.
/// </summary>
public class FrmGrid : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
///
<summary>
///
필수
디자이너
변수입니다.
///
</summary>
private System.ComponentModel.Container components = null;
public FrmGrid()
{
//
// Windows Form 디자이너
지원에
필요합니다.
//
InitializeComponent();
//
// TODO:
InitializeComponent를
호출한
다음
생성자
코드를
추가합니다.
//
}
///
<summary>
///
사용
중인
모든
리소스를
정리합니다.
///
</summary>
protected override void
Dispose( bool disposing
)
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing
);
}
#region Windows Form Designer generated code
///
<summary>
///
디자이너
지원에
필요한
메서드입니다.
///
이
메서드의
내용을
코드
편집기로
수정하지
마십시오.
///
</summary>
private void InitializeComponent()
{
this.dataGrid1 = new
System.Windows.Forms.DataGrid();
this.button1 = new
System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(8, 40);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(608, 344);
this.dataGrid1.TabIndex = 0;
//
// button1
//
this.button1.Location = new System.Drawing.Point(16, 8);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(80, 24);
this.button1.TabIndex = 1;
this.button1.Text = "저장하기";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// FrmGrid
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(624, 389);
this.Controls.Add(this.button1);
this.Controls.Add(this.dataGrid1);
this.Name = "FrmGrid";
this.Text = "FrmGrid";
this.Load += new
System.EventHandler(this.FrmGrid_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
private OleDbConnection LocalConn;
private OleDbDataAdapter adapter;
private DataSet ds;
private void FrmGrid_Load(object sender, System.EventArgs e)
{
string sql=null;
try
{
//------ Form Loading시
한번만...
LocalConn = Common_DB.DBConnection();
//-------------------------------
LocalConn.Open();
sql = "select name, sex, addr, tel from
addrbook";
adapter = new OleDbDataAdapter(sql,
LocalConn);
ds = new DataSet();
adapter.Fill(ds, "ADDRBOOK");
//dataGrid1.DataSource =
ds;
//dataGrid1.DataMember =
"ADDRBOOK";
dataGrid1.DataSource = ds.Tables["ADDRBOOK"];
}
catch(Exception e1)
{
MessageBox.Show(e1.ToString());
Log.WriteLine("FrmGrid", e1.ToString());
Log.WriteLine("FrmGrid", sql);
}
finally
{
LocalConn.Close();
}
}
private void button1_Click(object sender, System.EventArgs e)
{
try
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.Update(ds, "ADDRBOOK");
MessageBox.Show("저장 OK~");
}
catch(Exception e1)
{
MessageBox.Show("주소록
저장
오류~" + e1.ToString());
Log.WriteLine("FrmGrid", e1.ToString());
}
}
}
}
댓글 없음:
댓글 쓰기