* 개발환경
개발 툴 : Visual Studio 15.0 (2017)
개발 언어 : C#
데이터베이스 : MySQL 5.7

* 데이터베이스 테이블
Table 1 : User - Num(PK), ID, PW, Manager
Table 2 : Data - Code, Date, User(FK)

* 기능
- 데이터 추가, 삭제, 수정, 검색(JDBC를 통한 Java-MySQL Database연동)
- 사용자 추가, 삭제, 목록(DB연동)
- 파일 입출력(Local)

* 주요 소스코드
- 기본 설명은 주석으로 처리했습니다.

1) 로그인
private void button_Login_Click(object sender, EventArgs e) { 
            String id = textBox4.Text; 
            String password = textBox3.Text; 
 // 입력받은 ID와 PASSWORD를 변수에 저장 
            int logInCheck = 0; 
            scon = new MySqlConnection(url); 
            scom = new MySqlCommand(); 
            scom.Connection = scon; 
 // SELECT 쿼리문(입력받은 ID,PASSWORD를 DB내에서 검색) 
            scom.CommandText = "SELECT * FROM user"; 
            try   { 
                scon.Open(); 
                sdr = scom.ExecuteReader(); 
                while (sdr.Read())   { 
                    String getId = sdr["id"].ToString(); 
                    String getPassword = sdr["password"].ToString(); 
                    String getManager = sdr["manager"].ToString(); 
 // 입력한 ID와 PASSWORD가 일치하는 내용이 있을 때 
                    if (id.Equals(getId) && password.Equals(getPassword))   { 
                        if (getManager.Equals("True")) 
   {   // 관리자가 로그인 할 경우
                            MessageBox.Show("[관리자] " + id + " 님 환영합니다.", "로그인 성공"); 
                        } 
                        else  

                        { // 사용자가 로그인 할 경우 
                            MessageBox.Show("[사용자] " + id + " 님 환영합니다.", "로그인 성공"); 
                        } 
                        logInCheck = 1; 
                        nowId = id; 
                       
 // 저장 경로 설정 
                        saveFolder = @"D:\BarcodeDataManager\" + textBox4.Text + @"\" 
                                     + nowTime.Split('.')[0] + @"년\" + nowTime.Split('.')[1] + @"월\"; 
                        saveRoute = saveFolder + nowTime.Split('.')[2].Split(' ')[0] + "일.txt"; 
                        textBox2.Text = saveRoute; 
                        dataGridView1.Columns[0].HeaderText = "시간"; 
                        dataGridView1.Columns[1].HeaderText = "바코드"; 
                        break; 
                    } 
                } 
                if (logInCheck != 1) 
// 입력받은 ID와 PASSWORD가 일치하지 않는 경우 
                { 
                    MessageBox.Show("ID 또는 PASSWORD를 확인해주세요", "로그인 실패"); 
                    textBox3.Clear(); 
                    textBox4.Clear(); 
                    textBox4.Focus(); 
                } 
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message, "오류발생!"); 
            } 
            finally 
            { 
                scon.Close(); 
                textBox1.Focus(); 
            } 
 }

2) 바코드 입력
private void textBox_Barcode_KeyUp(object sender, KeyEventArgs e)   { 
            
// 엔터키 입력 시 
            if (e.KeyCode == Keys.Enter) 
            { 
                
// 입력 란이 비어있을 경우 
                if (textBox1.Text.Equals("")) 
                { 
                  
  // 함수 종료 
                    return; 
                } 
                String barcode = textBox1.Text; 
                textBox1.Text = ""; 
               
 // Database 내 중복검사 함수 호출 
                if (isOverlap(barcode)) 
                { 
                    
// Database 내 데이터 입력 함수 호출 
                    insertDatabase(barcode); 
                  
  // 로컬 컴퓨터 내 txt파일 저장 
                    dataFileWriter(barcode); 
                    if (checkBox1.Checked) // 기록여부 체크박스 확인 
                    { 
                        String[] Row = { nowTime, barcode }; 
                        dataGridView1.Rows.Add(Row); 
                        dataGridView1.FirstDisplayedScrollingRowIndex = 
                        dataGridView1.Rows.Count - 1;
 // DataGridView 자동 스크롤 
                    } 
                } 
                
// Data 중복 시 
                else 
                { 
                   
 // 함수 종료 
                    return; 
                } 
            } 
}


3) 데이터 추가(관리자용)
private void button_Data_Insert_Click(object sender, EventArgs e)  { 
            String barcode = textBox1.Text; 
            if(barcode.Equals("")) 
            { 
                MessageBox.Show("입력을 확인해주세요"); 
                return; 
            } 
            String time; 
            String user; 
           
 // 현재 시간 사용 체크 시 
            if(checkBox1.Checked) 
            { 
                time = Form1.nowTime; 
            } 
            
// 아닐 시 입력된 dateTimePicker에서 값을 받아온다. 
            else 
            { 
                time = dateTimePicker1.Value.ToString("yyyy.MM.dd HH:mm:ss"); 
            } 
            
// 현재 사용자 사용 체크시 
            if(checkBox2.Checked) 
            { 
                user = Form1.GetId(); 
            } 
           
 // 아닐 시 입력된 콤보박스에서 값을 받아온다. 
            else 
            { 
                user = comboBox1.SelectedItem.ToString(); 
            } 
            
// 데이터 중복 확인 
            if(Form1.isOverlap(barcode)) 
            { 
                Form1.scon = new MySqlConnection(Form1.url); 
                Form1.scom = new MySqlCommand(); 
                Form1.scom.Connection = Form1.scon; 
               
 // INSERT 쿼리문 
                Form1.scom.CommandText = "INSERT INTO datalist (code, serial, user) VALUES ('" 
                    + barcode + "','" + time + "','" + user + "');"; 
                try 
                { 
                    Form1.scon.Open(); 
                    Form1.sdr = Form1.scom.ExecuteReader(); 
                    MessageBox.Show("데이터가 입력되었습니다."); 
                } 
                catch (Exception ex) 
                { 
                    MessageBox.Show(ex.Message); 
                } 
                finally 
                { 
                    Form1.scon.Close(); 
                    this.Close(); 
                } 
            } 
            
// 데이터 중복 시 
            else 
            { 
               
 // 함수 종료 
                textBox1.Focus(); 
                return; 
            } 
 }


4) 데이터 중복검사
public static bool isOverlap(String inputBarcode)   { 
            scon = new MySqlConnection(url); 
            scom = new MySqlCommand(); 
            scom.Connection = scon; 
           
 // SELECT 쿼리문(입력받은 Barcode를 DB내에서 검색) 
            scom.CommandText = "SELECT * FROM datalist"; 
            try 
            { 
                scon.Open(); 
                sdr = scom.ExecuteReader(); 
                while (sdr.Read()) 
                { 
                    String getSerial = sdr["serial"].ToString(); 
                    String getCode = sdr["code"].ToString(); 
                    if (inputBarcode.Equals(getCode)) 
                    { 
                        MessageBox.Show("[ " + getSerial + " ] " + inputBarcode + 
                                        "은 이미 중복되었습니다.", "데이터 중복"); 
                        return false; 
                    } 
                } 
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message, "오류발생!"); 
            } 
            finally 
            { 
                scon.Close(); 
            } 
            return true; 
 }


5) 파일쓰기(로컬 컴퓨터에 따로 저장)
 private void dataFileWriter(String barcode) { 
            if(checkBox2.Checked == true) 
// 저장 여부 체크박스 확인 
            { 
                DirectoryInfo di = new DirectoryInfo(saveFolder); 
                if (di.Exists == false)
 // 지정된 경로에 폴더가 있는 지 확인 
                { 
                    di.Create(); 
// 폴더 생성 
                } 
                
// 파일쓰기 (true 옵션 : 파일 맨 끝에 덧붙여 쓰기) 
                using (StreamWriter outputFile = new StreamWriter(saveRoute, true)) 
                { 
                    outputFile.WriteLine("[" + nowTime + "] " + barcode); 
                } 
            }   
}

* 실행화면


- 화면구성 
기록 란 리스트 ) DataGridView
* 다음 함수를 통해 DataGridView PostHeader에 index를 추가
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) 
        { 
            if (e.RowIndex >= 0) 
            { 
                string NumberingText = (e.RowIndex + 1).ToString(); 

               
 // 글자 사이즈 구하기. 
                SizeF stringSize = e.Graphics.MeasureString(NumberingText, Font); 

               
 // 글자에 맞춰 좌표계산.  
                PointF StringPoint = new PointF 
                ( 
                    Convert.ToSingle(dataGridView1.RowHeadersWidth - 3 - stringSize.Width), 
                    Convert.ToSingle(e.RowBounds.Y) + dataGridView1[0, e.RowIndex].ContentBounds.Height * 0.3f 
                ); 

                
// 문자열 그리기. 
                e.Graphics.DrawString 
                ( 
                    NumberingText, 
                    Font, 
                    Brushes.Black, 
                    StringPoint.X, 
                    StringPoint.Y 
                ); 
            } 
        }

바코드 입력 란) TextBox - USB를 통해 바코드 입력 시 바코드 입력 후 자동 Enter키 입력
현재시간) Label - 쓰레드를 통해 1초마다 현재시간 갱신(바코드 입력 시간 저장)

** 바코드 입력 란은 항상 포커스가 유지되어야 하기 때문에 쓰레드 또는 버튼 이벤트에 포커스를 유지하는 함수를 호출 해 주어야 합니다 **

질문 사항은 댓글로 남겨주세요





to Top