2017년 1월 3일 화요일

[닷넷교육,C#교육,실무교육학원추천_탑크리에듀]닷넷,ADO.NET예제(오라클,C#주소록윈폼,ListVew,DataGrid,MDIForm응용예에제소스)

닷넷,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, 전체 프로젝트 화면]


C20140223-1.png
[그림
1, 
로그인 화면]
 

C20140223-2.png

[그림2, LiveView로 주소록 로딩]



C20140223-3.png

[그림3, DataGrid로 주소록 로딩]



C20140223-4.png

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');

개의 행이 만들어졌습니다.

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());
                                                     }
                                                    
                                   }
                  }
}

댓글 없음:

댓글 쓰기