Imports System.Reflection
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SQLite 'nuget SQLite
Imports MySql.Data.MySqlClient 'nuget MySQL
Imports Npgsql 'nuget Npgsql
Imports Oracle.ManagedDataAccess.Client 'ยังไม่ได้ทดสอบ InsertAsync อื่นฯ OK
'https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/ODPNET_Core_get_started/index.html
Imports Dapper 'nuget Dapper With Lastest Version 1.60.6 AT 2019-05-19
Imports Microsoft.Extensions.Configuration 'nuget Microsoft.Extensions.Configuration
Imports Microsoft.Extensions.Configuration.Json 'nuget Microsoft.Extensions.Configuration.Json
Public Class SexyDatabase
Private Shared _DBType As SimpleCRUD.Dialect
Private Shared _ConnectionString As String = String.Empty
''' <summary>
''' มันเข้าอีเวนต์นี้ ครั้งแรกและครั้งเดียวเท่านั้น (ตรงตามที่ต้องการ)
''' </summary>
Shared Sub New()
Dim configBuilder As IConfigurationBuilder = New Microsoft.Extensions.Configuration.ConfigurationBuilder()
Dim binPath = System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location)
Dim configFile = System.IO.Path.Combine(binPath, "appsettings.json") 'Dim configFile = "appsettings.json" 'อยู่ตรงไหน/เจอที่ไหน เอาตรงนั้น
configBuilder.AddJsonFile(configFile)
Dim ass As IConfigurationRoot = configBuilder.Build()
Dim DBType = ass.GetSection("DBSettings:DBType")
Dim ConnectionString = ass.GetSection("DBSettings:DefaultConnection") 'ass.GetConnectionString("DefaultConnection")
Dim IsEncrypt = ass.GetSection("DBSettings:IsEncrypt")
If (CBool(IsEncrypt.Value)) Then
'Decrypt Connection string here.
'_ConnectionString = Decrypt(ConnectionString.Value)
Else
_ConnectionString = ConnectionString.Value
End If
_DBType = CInt(DBType.Value) '0-SQL Server, 1-PostgreSQL, 2-SQLite, 3-MySQL, 4-Oracle, etc...
SimpleCRUD.SetDialect(_DBType) 'กำหนดประเภท Database
End Sub
''' <summary>
''' กำหนดประเภท Database ถ้าจำเป็นต้องกำหนด(SQLServer/Oracle/etc...)
''' </summary>
''' <param name="db">Enum SimpleCRUD.Dialect</param>
Public Shared Sub ConfigDatabase(db As SimpleCRUD.Dialect)
_DBType = db 'Public Enum Dialect {SQLServer, PostgreSQL, SQLite, MySQL, Oracle}
SimpleCRUD.SetDialect(CInt(db))
End Sub
''' <summary>
''' Main Core
''' </summary>
''' <param name="forceOpen"></param>
''' <returns></returns>
Public Shared Function SexyConnection(Optional forceOpen As Boolean = False) As IDbConnection
Dim connection As IDbConnection
Select Case _DBType
Case SimpleCRUD.Dialect.SQLServer
'connection = New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=true;")
connection = New SqlConnection(_ConnectionString)
Case SimpleCRUD.Dialect.SQLite
'connection = New SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")
connection = New SQLiteConnection(_ConnectionString)
Case SimpleCRUD.Dialect.PostgreSQL
'connection = New NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "testdb"))
connection = New NpgsqlConnection(_ConnectionString)
Case SimpleCRUD.Dialect.MySQL
connection = New MySqlConnection(_ConnectionString)
'connection = New MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "3306", "root", "admin@หีหัวเราะแตด", "SEXdb"))
Case SimpleCRUD.Dialect.Oracle
'Dim แพงฉิบหาย = "Data Source=(DESCRIPTION=" _
' + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))" _
' + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));" _
' + "User Id=sys;Password=1234;"
'connection = New OracleConnection("Data Source=localhost:1521/orcl;User Id=admin;Password=password;")
connection = New OracleConnection(_ConnectionString)
Case Else 'Set Default to SQLServer
connection = New SqlConnection(_ConnectionString)
End Select
If forceOpen Then
connection.Open()
End If
Return connection
End Function
End Class