@@ -137,9 +137,41 @@ | ||
137 | 137 | |
138 | 138 | Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click |
139 | 139 | |
140 | - initDB_AutoEpub.initDB() | |
140 | + Dim dbClass As New dbSQLite() | |
141 | 141 | |
142 | + dbClass.OpenConnection() | |
143 | + dbClass.BeginTransaction() | |
142 | 144 | |
145 | + | |
146 | + Dim cmd As String = "INSERT INTO TRN_EPUB(BOOK_NM,BOOK_ER,BOOK_JSON,CRT_DT,UPD_DT)VALUES(@BOOK_NM,@BOOK_ER,@BOOK_JSON,20200606,20200707)" | |
147 | + | |
148 | + Dim listParam As New List(Of SQLite.SQLiteParameter) | |
149 | + listParam.Add(New SQLite.SQLiteParameter("@BOOK_NM", "CC12C!'")) | |
150 | + listParam.Add(New SQLite.SQLiteParameter("@BOOK_ER", "d2fdf")) | |
151 | + listParam.Add(New SQLite.SQLiteParameter("@BOOK_JSON", "!!22<>")) | |
152 | + | |
153 | + | |
154 | + | |
155 | + dbClass.GetExecuteNum(cmd, listParam) | |
156 | + | |
157 | + | |
158 | + | |
159 | + Dim kke As DataTable = dbSQLiteSim.GetDataTable("select * from TRN_EPUB") | |
160 | + | |
161 | + | |
162 | + 'dbClass.Commit() | |
163 | + | |
164 | + | |
165 | + | |
166 | + | |
167 | + | |
168 | + | |
169 | + | |
170 | + 'dbClass.CloseConnection() | |
171 | + | |
172 | + 'initDB_AutoEpub.initDB() | |
173 | + | |
174 | + | |
143 | 175 | 'Dim cAutoEpub As New cDep2_004("C:\Users\DTC_PC07\Downloads\je\") |
144 | 176 | 'cAutoEpub.CreadEpub() |
145 | 177 |
@@ -13,19 +13,23 @@ | ||
13 | 13 | |
14 | 14 | Public Class initDB_AutoEpub |
15 | 15 | |
16 | - Public Shared sSQLitePath As String = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "SQLite\APP_DB.db") | |
16 | + Public Shared sSQLitePath As String = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "SQLite", "epub.db") | |
17 | 17 | |
18 | 18 | |
19 | 19 | |
20 | 20 | Public Shared Sub initDB() |
21 | - Dim connSQL As SQLiteConnection = New SQLiteConnection("data source=" & sSQLitePath) | |
21 | + | |
22 | + If Not System.IO.Directory.Exists(sSQLitePath) Then | |
23 | + System.IO.Directory.CreateDirectory(sSQLitePath) | |
24 | + End If | |
25 | + | |
26 | + Dim connSQL As SQLiteConnection = New SQLiteConnection("Data Source=" & Path.Combine(sSQLitePath, "epub.db")) | |
22 | 27 | connSQL.Open() |
23 | 28 | |
24 | - | |
25 | - Dim cmd As SQLiteCommand = New SQLiteCommand | |
29 | + Dim cmd As SQLiteCommand = New SQLiteCommand(connSQL) | |
26 | 30 | cmd.CommandText = sCreat_TRN_EPUB() |
27 | 31 | |
28 | - cmd.ExecuteNonQuery() | |
32 | + Dim iExec As Integer = cmd.ExecuteNonQuery() | |
29 | 33 | |
30 | 34 | |
31 | 35 | End Sub |
@@ -40,8 +44,8 @@ | ||
40 | 44 | sbCmd.AppendLine(" BOOK_NM TEXT NOT NULL PRIMARY KEY, ") |
41 | 45 | sbCmd.AppendLine(" BOOK_ER TEXT NULL, ") |
42 | 46 | sbCmd.AppendLine(" BOOK_JSON TEXT NULL, ") |
43 | - sbCmd.AppendLine(" CRT_DT TEXT NULL, ") | |
44 | - sbCmd.AppendLine(" UPD_DT TEXT NULL ") | |
47 | + sbCmd.AppendLine(" CRT_DT int NULL, ") | |
48 | + sbCmd.AppendLine(" UPD_DT int NULL ") | |
45 | 49 | sbCmd.AppendLine(")") |
46 | 50 | sCreat_TRN_EPUB = sbCmd.ToString() |
47 | 51 |
@@ -0,0 +1,123 @@ | ||
1 | + | |
2 | +Imports System.Data.SQLite | |
3 | +Imports System.IO | |
4 | + | |
5 | + | |
6 | +Public Class dbSQLiteSim | |
7 | + | |
8 | + Public Shared Function GetExecuteNum(ByVal cmd As String) As Integer | |
9 | + Dim dbClass As New dbSQLite | |
10 | + dbClass.OpenConnection() | |
11 | + GetExecuteNum = dbClass.GetExecuteNum(cmd) | |
12 | + dbClass.CloseConnection() | |
13 | + End Function | |
14 | + | |
15 | + Public Shared Function GetExecuteNum(ByVal cmd As String, ByVal listParam As List(Of SQLiteParameter)) As Integer | |
16 | + | |
17 | + Dim dbClass As New dbSQLite | |
18 | + dbClass.OpenConnection() | |
19 | + GetExecuteNum = dbClass.GetExecuteNum(cmd, listParam) | |
20 | + dbClass.CloseConnection() | |
21 | + | |
22 | + End Function | |
23 | + | |
24 | + Public Shared Function GetDataTable(ByVal cmd As String) As DataTable | |
25 | + | |
26 | + Dim dbClass As New dbSQLite | |
27 | + dbClass.OpenConnection() | |
28 | + GetDataTable = dbClass.GetDataTable(cmd) | |
29 | + dbClass.CloseConnection() | |
30 | + | |
31 | + End Function | |
32 | + Public Shared Function GetDataTable(ByVal cmd As String, ByVal listParam As List(Of SQLiteParameter)) As DataTable | |
33 | + Dim dbClass As New dbSQLite | |
34 | + dbClass.OpenConnection() | |
35 | + GetDataTable = dbClass.GetDataTable(cmd, listParam) | |
36 | + dbClass.CloseConnection() | |
37 | + End Function | |
38 | + | |
39 | +End Class | |
40 | + | |
41 | +Public Class dbSQLite | |
42 | + | |
43 | + Private sqlDbPath As String = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "SQLite") | |
44 | + Private sqlDbName As String = "epub.db" | |
45 | + Private sqlConString As String = Path.Combine(sqlDbPath, sqlDbName) | |
46 | + Private sqlCon As SQLiteConnection = New SQLiteConnection("Data Source=" & sqlConString) | |
47 | + Private sqlTransaction As SQLiteTransaction = Nothing | |
48 | + | |
49 | + Sub New() | |
50 | + If Not System.IO.Directory.Exists(sqlDbPath) Then | |
51 | + System.IO.Directory.CreateDirectory(sqlDbPath) | |
52 | + End If | |
53 | + End Sub | |
54 | + | |
55 | + Sub OpenConnection() | |
56 | + If sqlCon.State = ConnectionState.Closed Then | |
57 | + sqlCon.Open() | |
58 | + End If | |
59 | + End Sub | |
60 | + | |
61 | + Sub CloseConnection() | |
62 | + If sqlCon.State = ConnectionState.Open Then | |
63 | + sqlCon.Close() | |
64 | + End If | |
65 | + | |
66 | + End Sub | |
67 | + | |
68 | + | |
69 | + Sub BeginTransaction() | |
70 | + sqlTransaction = sqlCon.BeginTransaction | |
71 | + | |
72 | + End Sub | |
73 | + | |
74 | + Sub Commit() | |
75 | + sqlTransaction.Commit() | |
76 | + End Sub | |
77 | + | |
78 | + Sub Rollback() | |
79 | + sqlTransaction.Rollback() | |
80 | + End Sub | |
81 | + | |
82 | + Function GetExecuteNum(ByVal cmd As String) As Integer | |
83 | + | |
84 | + Dim iRet As Integer = 0 | |
85 | + Dim sqlCmd As SQLiteCommand = New SQLiteCommand(sqlCon) | |
86 | + sqlCmd.CommandText = cmd | |
87 | + sqlCmd.Transaction = sqlTransaction | |
88 | + GetExecuteNum = sqlCmd.ExecuteNonQuery() | |
89 | + | |
90 | + End Function | |
91 | + | |
92 | + Function GetExecuteNum(ByVal cmd As String, ByVal listParam As List(Of SQLiteParameter)) As Integer | |
93 | + | |
94 | + Dim iRet As Integer = 0 | |
95 | + Dim sqlCmd As SQLiteCommand = New SQLiteCommand(sqlCon) | |
96 | + sqlCmd.CommandText = cmd | |
97 | + sqlCmd.Parameters.AddRange(listParam.ToArray) | |
98 | + sqlCmd.Transaction = sqlTransaction | |
99 | + GetExecuteNum = sqlCmd.ExecuteNonQuery() | |
100 | + | |
101 | + End Function | |
102 | + | |
103 | + Function GetDataTable(ByVal cmd As String) As DataTable | |
104 | + GetDataTable = New DataTable() | |
105 | + Dim sqlCmd As SQLiteCommand = New SQLiteCommand(sqlCon) | |
106 | + sqlCmd.CommandText = cmd | |
107 | + sqlCmd.Transaction = sqlTransaction | |
108 | + Dim adapter As SQLiteDataAdapter = New SQLiteDataAdapter(sqlCmd) | |
109 | + adapter.Fill(GetDataTable) | |
110 | + End Function | |
111 | + | |
112 | + | |
113 | + Function GetDataTable(ByVal cmd As String, ByVal listParam As List(Of SQLiteParameter)) As DataTable | |
114 | + GetDataTable = New DataTable() | |
115 | + Dim sqlCmd As SQLiteCommand = New SQLiteCommand(sqlCon) | |
116 | + sqlCmd.CommandText = cmd | |
117 | + sqlCmd.Parameters.AddRange(listParam.ToArray) | |
118 | + sqlCmd.Transaction = sqlTransaction | |
119 | + Dim adapter As SQLiteDataAdapter = New SQLiteDataAdapter(sqlCmd) | |
120 | + adapter.Fill(GetDataTable) | |
121 | + End Function | |
122 | + | |
123 | +End Class |