닷넷,ADO.NET예제(오라클,C#주소록윈폼,ListVew,DataGrid,MDIForm응용예에제소스)
[닷넷Windorm강좌]C# ADO.NET, 윈폼을 이용한 주소록 작성(,트리메뉴,ListView, DataGrid, TreeView)소스
<?xml: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 = newSystem.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 += newSystem.EventHandler(this.button1_Click);
//
// FrmLogin
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(304, 69);
this.Controls.AddRange(newSystem.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 = newMDIMain();
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 = newOleDbDataAdapter(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 = newOleDbCommandBuilder(adapter);
adapter.Update(ds, "ADDRBOOK");
MessageBox.Show("저장 OK~");
}
catch(Exception e1)
{
MessageBox.Show("주소록 저장 오류~" + e1.ToString());
Log.WriteLine("FrmGrid", e1.ToString());
}
}
}
}
댓글 없음:
댓글 쓰기