본문 바로가기
코스웨어/15년 스마트컨트롤러

20150701-13번-박서연 MSSQL연동&자동 업데이트 프로그램

by 알 수 없는 사용자 2015. 7. 1.
728x90
반응형

2015-07-01


*C# 프로그래밍


# MSSQL연동 & 자동 업데이트 프로그램 만들기

$ 참고자료 

 (수정)MSSQL연동&자동업데이트.pdf


1. DB구성

DataBase 

mook

Table 

1) Table_File


2) Table_Update


2. C# Coding

1) mook_AutoAdmin

Window Form


소스

 using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace mook_AutoAdmin
{
    public partial class Form1 : Form
    {
        FileInfo f = null;
        string Constr = "server=localhost;uid=sa;pwd=1234;database=mook";
        public Form1()
        {
            InitializeComponent();
        }

        private void btnFile_Click(object sender, EventArgs e)
        {
            if(this.ofdFile.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    this.txtFile.Text = this.ofdFile.FileName;
                    f = new FileInfo(this.ofdFile.FileName);
                    this.lbldisName.Text = f.Name;
                    this.lbldisSize.Text = f.Length.ToString() + " byte";
                    this.lbldisDate.Text = f.LastWriteTime.ToString();
                }
                catch
                {
                    return;
                }
            }
        }

        private void btnUpload_Click(object sender, EventArgs e)
        {
            var fs = f.OpenRead();
            var bytebuffer = new byte[fs.Length];
            fs.Read(bytebuffer, 0, Convert.ToInt32(fs.Length));
            var conn = new SqlConnection(Constr);
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            var Sql = "update Table_File set M_File=@M_File, M_FileName='" +
                    this.lblFileName + "' where M_Num=1";
            cmd.CommandText = Sql;
            cmd.Parameters.Add(new SqlParameter("@M_File",
                System.Data.SqlDbType.Image)).Value = bytebuffer;
            var iResult = cmd.ExecuteNonQuery();
            conn.Close();
            fs.Close();

            if( (iResult > 0) && (DataSave() > 0))
            {
                MessageBox.Show("저장이 정상적으로 되었습니다.", "알림",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Close();
            }
            else
            {
                MessageBox.Show("저장이 정상적으로 되지 않았습니다.", "에러",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private int DataSave()
        {
            var Conn = new SqlConnection(Constr);
            Conn.Open();
            var strSQL = "update Table_Update set M_Date=M_Date + 1";
            var myCom = new SqlCommand(strSQL, Conn);
            var i = myCom.ExecuteNonQuery();
            Conn.Close();

            return i;
        }


    }
}


2) mook_AutoMain

Window Form


소스

 using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Diagnostics;

namespace mook_AutoMain
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.cbTime.SelectedIndex = 2;
        }

        private void Timer_Tick(object sender, EventArgs e)
        {
            this.lbldisUpdate.Text = DateTime.Now.ToString();
            var srt = File.OpenText("setup.txt");
            var str = srt.ReadLine();
            if (Convert.ToInt32(str) == Convert.ToInt32(DataCheck()))
            {
                srt.Close();
                return;
            }
            else if(Convert.ToInt32(str) < Convert.ToInt32(DataCheck()))
            {
                srt.Close();
                this.Timer.Enabled = false;
                var dlr = MessageBox.Show(
                    "최신 프로그램을 다운로드 받으시겠습니까??","알림",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if(dlr == System.Windows.Forms.DialogResult.Yes)
                {
                    var myprocess = new Process();
                    myprocess.StartInfo.FileName = "mook_AutoUpdate.exe";
                    myprocess.Start();
                    Application.Exit();
                }
                else
                {
                    this.Timer.Enabled = true;
                    return;
                }
            }
            else
            {
                srt.Close();
                MessageBox.Show("잘못된 프로그램입니다.", "알림",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Close();
            }
        }

        private string DataCheck()
        {
            var Constr = "server=localhost;uid=sa;pwd=1234;database=mook";
            var Conn = new SqlConnection(Constr);
            Conn.Open();
            var Comm = new SqlCommand("Select * from Table_Update", Conn);
            var myRead = Comm.ExecuteReader();
            if(myRead.Read())
            {
                var str = myRead["M_Date"].ToString();
                myRead.Close();
                Conn.Close();

                return str;
            }
            else
            {
                myRead.Close();
                Conn.Close();
                return "1";
            }
        }

        private void cbTime_SelectedIndexChanged(object sender, EventArgs e)
        {
            Timer.Stop();
            Timer.Enabled = false;
            if(cbTime.SelectedIndex == 0)
            {
                Timer.Interval = 100;

            }
            else if(cbTime.SelectedIndex == 1)
            {
                Timer.Interval = 1000;
            }
            else if(cbTime.SelectedIndex == 2)
            {
                Timer.Interval = 6000;
            }

            Timer.Enabled = true;
            Timer.Start();
            
        }



    }
}


3) mook_AutoUpdate

Window Form


소스

using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Diagnostics;

namespace mook_AutoUpdate
{
    public partial class Form1 : Form
    {
        private Thread myDownload;
        private string Constr = "server=localhost;uid=sa;pwd=1234;Database=mook";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                var tProcess = Process.GetProcessesByName("mook_AutoMain");
                if (tProcess.Length == 1)
                {
                    tProcess[0].Kill();
                }
            }
            catch
            {
 
            }

            var fs = new FileStream("setup.txt", FileMode.Create);
            var sw = new StreamWriter(fs);
            sw.WriteLine(DataCheck());
            sw.Close();
            fs.Close();
            myDownload = new Thread(DataDownLoding);
            myDownload.Start();

        }
        private string DataCheck()
        {
            var Conn = new SqlConnection(Constr);
            Conn.Open();
            var Comm = new SqlCommand("Select * from Table_Update", Conn);
            var myRead = Comm.ExecuteReader();
            if(myRead.Read())
            {
                string Update = myRead["M_Date"].ToString();
                myRead.Close();
                Conn.Close();

                return Update;
            }
            else
            {
                myRead.Close();
                Conn.Close();

                return "1";
            }

        }

        private void DataDownLoding()
        {
            byte[] bytes = DatabaseDownload(1);
            var fs = new FileStream("mook_AutoMain.exe", FileMode.Create);
            fs.Write(bytes, 0, bytes.Length);
            fs.Close();
            this.Close();
        }

        private byte[] DatabaseDownload(int M_Num)
        {
            using(var Conn = new SqlConnection(Constr))
            {
                Conn.Open();
                var sql =
                    "Select DataLength(M_File) From Table_File WHERE M_Num=@M_Num";
                SqlParameter param = new SqlParameter("@M_Num", SqlDbType.Int);
                param.Value = M_Num;
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.Parameters.Add(param);
                int totalLength = Convert.ToInt32(cmd.ExecuteScalar());
                this.ProgressBar.Maximum = totalLength;
                this.ProgressBar.Value = 0;
                sql = "Select M_File From Table_File WHERE M_Num=@M_Num";
                param = new SqlParameter("@M_Num", SqlDbType.Int);
                param.Value = M_Num;
                cmd = new SqlCommand(sql, Conn);
                cmd.Parameters.Add(param);
                SqlDataReader reader =
                    cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                reader.Read();
                long remainder = totalLength;
                int bufferSize = 2048;
                if(totalLength < bufferSize)
                {
                    totalLength = bufferSize;
                }
                byte[] buf = new byte[(int)totalLength];
                int startIndex = 0;
                long retval = reader.GetBytes(0, startIndex, buf, 0, bufferSize);
                remainder -= retval;
                while(remainder > 0)
                {
                    startIndex += bufferSize;
                    if(remainder < bufferSize)
                    {
                        bufferSize = (int)remainder;
                    }
                    retval = reader.GetBytes(0, startIndex, buf, startIndex, bufferSize);
                    remainder -= retval;
                    this.ProgressBar.Value = startIndex;
                    Thread.Sleep(100);
                }
                this.ProgressBar.Value = totalLength;
                reader.Close();

                return buf;
            }
        }
    }
}


실행결과

1. 현재 버전은 1.0이고 업데이트 하려는 2.0 버전의 파일을 업로드 한다.


2. main프로그램을 실행시키면 현재 1.0버전이고 1분의 타이머를 통해서 업데이트를 계속해서 확인한다.


3. 확인 결과 현재버전이 최신버전이 아님을 알고 최신 프로그램을 다운 받을지에 대한 메세지를 띄운다.


4. 확인을 누르면 자동으로 프로그램이 새버전으로 다운받아진다. 그리고 또다른 새로운 버전이 확인될때 까지 1초간격으로 확인한다.


728x90

'코스웨어 > 15년 스마트컨트롤러' 카테고리의 다른 글

20150708 - 19번 안향진 AVR-AVR studio_AX-12+(스테핑모터) / C#-채팅프로그램 실습 / 네트워크-joinc 소켓프로그램  (2) 2015.07.08
20150707 - 18번 안해운 AVR toolchain & C# attribute  (4) 2015.07.07
20150703 - 16번 박태인 - [C#]Thread 동기화, 파일 정보와 디렉토리 정보 다루기  (3) 2015.07.03
20150702-14번-박제혁 C#스레드 사용법  (3) 2015.07.02
20150626-11번-남수진 MySQL/클래스템플릿의 정적 멤버 변수  (6) 2015.06.26
20150625 업무일지 출석번호 9번 김태현  (10) 2015.06.25
20150624-8번-김태영-MySQL 비쥬얼스튜디오 연동, 클래스 템플릿  (6) 2015.06.24
20150623-7번-김재홍-MySQL설치(C#)_템플릿의 개념(C++)  (7) 2015.06.24

관련글

티스토리툴바