■ SQLiteFunction 클래스에서 커스텀 함수를 사용하는 방법을 보여준다.
▶ MainForm.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
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 } } |
