Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SQLite
Imports MySql.Data.MySqlClient
Imports Npgsql
Imports Oracle.ManagedDataAccess.Client
'https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/ODPNET_Core_get_started/index.html
Imports Dapper
Public Class SexyDatabase
Private Shared _dbType As sexy
Public Enum sexy
SQLServer
PostgreSQL
SQLite
MySQL
Oracle
End Enum
''' <summary>
''' กำหนดประเภท Database (SQLServer/Oracle/etc...)
''' </summary>
''' <param name="db">sexy</param>
Public Shared Sub ConfigDatabase(db As sexy)
.SetSexy(CInt(db))
End Sub
Public Shared Function SexyConnection(Optional forceOpen As Boolean = False) As IDbConnection
Dim connection As IDbConnection
'ถ้ามีการเข้ารหัส ถอก มันออกมา
'Dim strCon = Decrypt(System.IO.File.ReadAllText("Path/assFileass.txt"))
Dim strConn As String = "yes you are wonderfull to night."
Select Case _dbType
Case SimpleCRUD.Dialect.SQLServer
connection = New SqlConnection(strConn)
Case SimpleCRUD.Dialect.SQLite
connection = New SQLiteConnection(strConn)
Case SimpleCRUD.Dialect.PostgreSQL
connection = New NpgsqlConnection(strConn)
Case SimpleCRUD.Dialect.MySQL
connection = New MySqlConnection(strConn)
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=Ass1;Password=Ass2;"
connection = New OracleConnection("Data Source=localhost:1521/orcl; User Id = Ass1; Password = Ass2;")
Case Else
'Set Default to SQLServer
Dim sb = New System.Text.StringBuilder()
sb.Append("Data Source=.\sqlexpress;Initial Catalog=DBName;")
sb.Append("Integrated Security=True;MultipleActiveResultSets=true;")
connection = New SqlConnection(strConn)
End Select
If forceOpen Then
connection.Open()
End If
Return connection
End Function
End Class
Date :
2019-04-13 06:29:11
By :
หน้าฮี
No. 19
Guest
SexyConnection ไม่ได้สนใจ Simple Store Procedure/Advance Store Procedure
เด็กเด็กเด็ก เขาเล่นกัน
Code (VB.NET)
Private Sub SetupSqLite()
File.Delete(Directory.GetCurrentDirectory() & "\MyDatabase.sqlite")
SQLiteConnection.CreateFile("MyDatabase.sqlite")
Dim connection = New SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;")
Using connection
connection.Open()
connection.Execute(" create table Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null, CreatedDate datetime default current_timestamp ) ")
connection.Execute(" create table Car (CarId INTEGER PRIMARY KEY AUTOINCREMENT, Id INTEGER null, Make nvarchar(100) not null, Model nvarchar(100) not null) ")
connection.Execute(" create table BigCar (CarId INTEGER PRIMARY KEY AUTOINCREMENT, Make nvarchar(100) not null, Model nvarchar(100) not null) ")
connection.Execute(" insert into BigCar (CarId,Make,Model) Values (2147483649,'car','car') ")
connection.Execute(" create table City (Name nvarchar(100) not null, Population int not null) ")
connection.Execute(" CREATE TABLE GUIDTest([Id] [uniqueidentifier] NOT NULL,[name] [varchar](50) NOT NULL, CONSTRAINT [PK_GUIDTest] PRIMARY KEY ([Id] ASC))")
connection.Execute(" create table StrangeColumnNames (ItemId INTEGER PRIMARY KEY AUTOINCREMENT, word nvarchar(100) not null, colstringstrangeword nvarchar(100) not null, KeywordedProperty nvarchar(100) null) ")
connection.Execute(" create table UserWithoutAutoIdentity (Id INTEGER PRIMARY KEY, Name nvarchar(100) not null, Age int not null) ")
connection.Execute(" create table IgnoreColumns (Id INTEGER PRIMARY KEY AUTOINCREMENT, IgnoreInsert nvarchar(100) null, IgnoreUpdate nvarchar(100) null, IgnoreSelect nvarchar(100) null, IgnoreAll nvarchar(100) null) ")
connection.Execute(" CREATE TABLE KeyMaster (Key1 INTEGER NOT NULL, Key2 INTEGER NOT NULL, PRIMARY KEY ([Key1], [Key2]))")
connection.Execute(" CREATE TABLE stringtest (stringkey nvarchar(50) NOT NULL,name nvarchar(50) NOT NULL, PRIMARY KEY ([stringkey] ASC))")
End Using
End Sub
Private Sub SetupMySQL()
Using connection = New MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "3306", "root", "admin@1234", "sys"))
connection.Open()
connection.Execute("DROP DATABASE IF EXISTS Demo;")
connection.Execute("CREATE DATABASE Demo;")
End Using
System.Threading.Thread.Sleep(1000)
Using connection = New MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "3306", "root", "admin@1234", "Demo"))
connection.Open()
connection.Execute(" create table Users (Id INTEGER PRIMARY KEY AUTO_INCREMENT, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null, CreatedDate datetime default current_timestamp ) ")
connection.Execute(" create table Car (CarId INTEGER PRIMARY KEY AUTO_INCREMENT, Id INTEGER null, Make nvarchar(100) not null, Model nvarchar(100) not null) ")
connection.Execute(" create table BigCar (CarId BIGINT PRIMARY KEY AUTO_INCREMENT, Make nvarchar(100) not null, Model nvarchar(100) not null) ")
connection.Execute(" insert into BigCar (CarId,Make,Model) Values (2147483649,'car','car') ")
connection.Execute(" create table City (Name nvarchar(100) not null, Population int not null) ")
connection.Execute(" CREATE TABLE GUIDTest(Id CHAR(38) NOT NULL,name varchar(50) NOT NULL, CONSTRAINT PK_GUIDTest PRIMARY KEY (Id ASC))")
connection.Execute(" create table StrangeColumnNames (ItemId INTEGER PRIMARY KEY AUTO_INCREMENT, word nvarchar(100) not null, colstringstrangeword nvarchar(100) not null, KeywordedProperty nvarchar(100) null) ")
connection.Execute(" create table UserWithoutAutoIdentity (Id INTEGER PRIMARY KEY, Name nvarchar(100) not null, Age int not null) ")
connection.Execute(" create table IgnoreColumns (Id INTEGER PRIMARY KEY AUTO_INCREMENT, IgnoreInsert nvarchar(100) null, IgnoreUpdate nvarchar(100) null, IgnoreSelect nvarchar(100) null, IgnoreAll nvarchar(100) null) ")
connection.Execute(" CREATE table KeyMaster (Key1 INTEGER NOT NULL, Key2 INTEGER NOT NULL, CONSTRAINT PK_KeyMaster PRIMARY KEY CLUSTERED (Key1 ASC, Key2 ASC))")
End Using
End Sub