■ SQLiteFunction 클래스 : 커스텀 함수 사용하기

----------------------------------------------------------------------------------------------------


TestProject.zip


MainForm.cs

 

 

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SQLite;

using System.Windows.Forms;

 

namespace TestProject

{

    /// <summary>

    /// 메인 폼

    /// </summary>

    public partial class MainForm : Form

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region CREATE_STUDENT_TABLE_SQL

 

        /// <summary>

        /// CREATE_STUDENT_TABLE_SQL

        /// </summary>

        private const string CREATE_STUDENT_TABLE_SQL = @"

CREATE TABLE STUDENT_TABLE

(

    NAME  TEXT NOT NULL

   ,SCORE REAL NOT NULL

)

";

 

        #endregion

        #region INSERT_STUDENT_TABLE_SQL

 

        /// <summary>

        /// INSERT_STUDENT_TABLE_SQL

        /// </summary>

        private const string INSERT_STUDENT_TABLE_SQL = @"

INSERT INTO STUDENT_TABLE

(

    NAME

   ,SCORE

)

VALUES

(

    @NAME

   ,@SCORE

);

";

 

        #endregion

        #region SELECT_STUDENT_TABLE_SQL

 

        /// <summary>

        /// SELECT_STUDENT_TABLE_SQL

        /// </summary>

        private static string SELECT_STUDENT_TABLE_SQL = @"

SELECT

    NAME

   ,AVG(SCORE)     AS AVG

   ,AVERAGE(SCORE) AS AVERAGE

   ,STDEV(SCORE)   AS STDEV

FROM STUDENT_TABLE

GROUP BY NAME;

";

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 생성자 - MainForm()

 

        /// <summary>

        /// 생성자

        /// </summary>

        public MainForm()

        {

            InitializeComponent();

 

            this.Load += Form_Load;

        }

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Private

        //////////////////////////////////////////////////////////////////////////////// Event

 

        #region 폼 로드시 처리하기 - Form_Load(sender, e)

 

        /// <summary>

        /// 폼 로드시 처리하기

        /// </summary>

        /// <param name="sender">이벤트 발생자</param>

        /// <param name="e">이벤트 인자</param>

        private void Form_Load(object sender, EventArgs e)

        {

            using(SQLiteConnection connection = SQLiteHelper.GetConnection("Data Source=:memory:"))

            {

                connection.Open();

 

                SQLiteFunction.RegisterFunction(typeof(SQRT));

                SQLiteFunction.RegisterFunction(typeof(AVERAGE));

                SQLiteFunction.RegisterFunction(typeof(STDEV));

 

                // 학생 테이블을 생성한다.

                SQLiteHelper.Execute(connection, CREATE_STUDENT_TABLE_SQL);

 

                // 학생 데이터를 추가한다.

                InsertStudentData(connection);

 

                DataTable resultTable = SQLiteHelper.GetDataTable(connection, SELECT_STUDENT_TABLE_SQL);

 

                this.dataGridView.DataSource = resultTable;

            }

        }

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////// Function

 

        #region 학생 데이터 추가하기 - InsertStudentData(connection, name, score)

 

        /// <summary>

        /// 학생 데이터 추가하기

        /// </summary>

        /// <param name="connection">연결</param>

        /// <param name="name">성명</param>

        /// <param name="score">점수</param>

        /// <returns>처리 행 수</returns>

        private int InsertStudentData(SQLiteConnection connection, string name, double score)

        {

            List<SQLiteParameter> parameterList = new List<SQLiteParameter>()

            {

                new SQLiteParameter { ParameterName = "@NAME" , DbType = DbType.String, Value = name  },

                new SQLiteParameter { ParameterName = "@SCORE", DbType = DbType.Double, Value = score }

            };

 

            int result = SQLiteHelper.Execute(connection, INSERT_STUDENT_TABLE_SQL, parameterList);

 

            return result;

        }

 

        #endregion

        #region 학생 데이터 추가하기 - InsertStudentData(connection)

 

        /// <summary>

        /// 학생 데이터 추가하기

        /// </summary>

        /// <param name="connection">연결</param>

        private void InsertStudentData(SQLiteConnection connection)

        {

            InsertStudentData(connection, "김철수", 100.0);

            InsertStudentData(connection, "김철수",  95.5);

            InsertStudentData(connection, "김철수",  80.3);

            InsertStudentData(connection, "김철수", 100.0);

            InsertStudentData(connection, "김철수",  90.7);

 

            InsertStudentData(connection, "이영희", 100.0);

            InsertStudentData(connection, "이영희", 100.0);

            InsertStudentData(connection, "이영희",  80.1);

            InsertStudentData(connection, "이영희",  85.3);

            InsertStudentData(connection, "이영희",  90.5);

        }

 

        #endregion

    }

}

 

 

SQRT.cs

 

 

using System;

using System.Data.SQLite;

 

namespace TestProject

{

    /// <summary>

    /// 제곱근 함수

    /// </summary>

    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Scalar, Name = "SQRT")]

    public class SQRT : SQLiteFunction

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 호출하기 - Invoke(argumentArray)

 

        /// <summary>

        /// 호출하기

        /// </summary>

        /// <param name="argumentArray">인자 배열</param>

        /// <returns>처리 결과</returns>

        public override object Invoke(object[] argumentArray)

        {

            return Math.Sqrt((double)argumentArray[0]);

        }

 

        #endregion

    }

}

 

 

AVERAGE.cs

 

 

using System.Data.SQLite;

 

namespace TestProject

{

    /// <summary>

    /// 평균 함수

    /// </summary>

    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Aggregate, Name = "AVERAGE")]

    public class AVERAGE : SQLiteFunction

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region Field

 

        /// <summary>

        /// 카운트

        /// </summary>

        private int count = 0;

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 단계별 처리하기 - Step(argumentArray, stepNumber, contextData)

 

        /// <summary>

        /// 단계별 처리하기

        /// </summary>

        /// <param name="argumentArray">인자 배열</param>

        /// <param name="stepNumber">단계 변호</param>

        /// <param name="contextData">컨텍스트 데이터</param>

        public override void Step(object[] argumentArray, int stepNumber, ref object contextData)

        {

            if(contextData == null)

            {

                contextData = 0.0;

 

                this.count = 0;

            }

 

            contextData = (double)contextData + (double)argumentArray[0];

 

            this.count++;

        }

 

        #endregion

        #region 최종 처리하기 - Final(contextData)

 

        /// <summary>

        /// 최종 처리하기

        /// </summary>

        /// <param name="contextData">컨텍스트 데이터</param>

        /// <returns>결과</returns>

        public override object Final(object contextData)

        {

            return (double)contextData / count;

        }

 

        #endregion

    }

}

 

 

STDEV.cs

 

 

using System;

using System.Collections.Generic;

using System.Data.SQLite;

using System.Linq;

 

namespace TestProject

{

    /// <summary>

    /// 표준 편차 함수

    /// </summary>

    [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Aggregate, Name = "STDEV")]

    public class STDEV : SQLiteFunction

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region Field

 

        /// <summary>

        /// 소스 리스트

        /// </summary>

        private List<double> sourceList = new List<double>();

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 단계별 처리하기 - Step(argumentArray, stepNumber, contextData)

 

        /// <summary>

        /// 단계별 처리하기

        /// </summary>

        /// <param name="argumentArray">인자 배열</param>

        /// <param name="stepNumber">단계 변호</param>

        /// <param name="contextData">컨텍스트 데이터</param>

        public override void Step(object[] argumentArray, int stepNumber, ref object contextData)

        {

            if(contextData == null)

            {

                contextData = 0;

 

                this.sourceList.Clear();

            }

 

            this.sourceList.Add((double)argumentArray[0]);

        }

 

        #endregion

        #region 최종 처리하기 - Final(contextData)

 

        /// <summary>

        /// 최종 처리하기

        /// </summary>

        /// <param name="contextData">컨텍스트 데이터</param>

        /// <returns>결과</returns>

        public override object Final(object contextData)

        {

            if(this.sourceList.Count == 1)

            {

                return double.NaN;

            }

 

            double average = this.sourceList.Average();

 

            double minusSquareSummary = 0.0;

 

            foreach(double source in sourceList)

            {

                minusSquareSummary += (source - average) * (source - average);

            }

 

            double stdev = Math.Sqrt(minusSquareSummary / (sourceList.Count - 1));

 

            return stdev;

        }

 

        #endregion

    }

}

 

----------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker
TAG