Remark : Stored Procedure 저장 프로시저 적용 샘플
|
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 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Data; namespace WinRuleCache.Function { class DBData { static string connectionString = "Server=SQLOLEDB.1;Password=*****;Persist Security Info=True;User ID=REGTEST;Initial Catalog=REG_TEST;Data Source=MYPC"; //LOG_FILE 파일 정보 가져오기 public static void LOG_FILESelect(Dictionary<int, string> LOG_FILE) { //SQL 연결설정 //Call stored procedure with parameter and return value SqlConnection conn = new SqlConnection(connectionString); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "LOG_FILESelect"; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { //Console.WriteLine("{0}\t{1}{2}", reader.GetInt32(0),reader.GetString(1), reader.GetString(2)); LOG_FILE.Add(reader.GetInt32(0), reader.GetString(1) + @"\" + reader.GetString(2)); } } reader.Close(); } catch { } finally { conn.Close(); } } //LOG_FILE 처리 업데이트 public static string LOG_FILEUpdate(int IDX) { //SQL 연결설정 //Call stored procedure with parameter and return value SqlConnection conn = new SqlConnection(connectionString); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "LOG_FILEUpdate"; cmd.Parameters.Add(new SqlParameter("@IDX", SqlDbType.Int)).Value = IDX; cmd.ExecuteNonQuery(); cmd.Dispose(); return "1"; } catch { return "0"; } finally { conn.Close(); } } // 캐싱 메인 public static int CAHE_Save(int IDX, int TEST_NUM, int NOTE_TEST, string DICSTRING, int DICTYPE, int FINAL_IDX, string ENGLISH, string KOREAN, string PATTERN_IDXS) { int CACHE_IDX = 0; //Return default 0 //SQL 연결설정 //Call stored procedure with parameter and return value SqlConnection conn = new SqlConnection(connectionString); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CACHE_Save"; //SqlParameter paramIDX = cmd.Parameters.Add("@IDX", SqlDbType.Int); //paramIDX.Direction = ParameterDirection.Input; //paramIDX.Value = IDX; cmd.Parameters.Add(new SqlParameter("@IDX", SqlDbType.Int)).Value = IDX; cmd.Parameters.Add(new SqlParameter("@TEST_NUM", SqlDbType.TinyInt)).Value = TEST_NUM; cmd.Parameters.Add(new SqlParameter("@NOTE_TEST", SqlDbType.TinyInt)).Value = NOTE_TEST; cmd.Parameters.Add(new SqlParameter("@DICSTRING", SqlDbType.VarChar, 200)).Value = DICSTRING; cmd.Parameters.Add(new SqlParameter("@DICTYPE", SqlDbType.TinyInt)).Value = DICTYPE; cmd.Parameters.Add(new SqlParameter("@FINAL_IDX", SqlDbType.Int)).Value = FINAL_IDX; cmd.Parameters.Add(new SqlParameter("@ENGLISH", SqlDbType.VarChar, 200)).Value = ENGLISH; cmd.Parameters.Add(new SqlParameter("@KOREAN", SqlDbType.VarChar, 200)).Value = KOREAN; cmd.Parameters.Add(new SqlParameter("@PATTERN_IDXS", SqlDbType.VarChar, 200)).Value = PATTERN_IDXS;//PATTERN_IDXS SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { if (reader.Read()) { CACHE_IDX = int.Parse(reader["CACHE_IDX"].ToString()); } } reader.Close(); return CACHE_IDX; } catch { return CACHE_IDX; } finally { conn.Close(); } } // // 캐싱 메인 public static void CACHE_PATTERNSave(int CACHE_IDX, int PATTERN_IDX, string REGULAR_EXPRESSION) { //SQL 연결설정 //Call stored procedure with parameter and return value SqlConnection conn = new SqlConnection(connectionString); try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CACHE_PATTERNSave"; cmd.Parameters.Add(new SqlParameter("@CACHE_IDX", SqlDbType.Int)).Value = CACHE_IDX; cmd.Parameters.Add(new SqlParameter("@PATTERN_IDX", SqlDbType.Int)).Value = PATTERN_IDX; cmd.Parameters.Add(new SqlParameter("@REGULAR_EXPRESSION", SqlDbType.VarChar, 2000)).Value = REGULAR_EXPRESSION; cmd.ExecuteNonQuery(); cmd.Dispose(); } catch { } finally { conn.Close(); } } } } |