using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.OracleClient;
/// <summary>
/// Summary description for DBClass
/// Class สำหรับ connect database สามารถ ใช้งานได้กับ ACCESS,SQL Server,Oracle
/// เขียนขึ้นโดย Pheak Email manop.muangpia@hotmail.com
/// มีข้อสงสัย,bug แจ้งได้ทาง Email ครับ
/// </summary>
///
//ประกาศ Connection ของแต่ละ Database
public class ConnectDB
{
//SQL Server
public SqlConnection SqlStrCon()
{
return new SqlConnection("Data Source=127.0.0.1;Initial Catalog=xxxx" +
";Persist Security Info=True;User ID=xxxx;Password=xxxx");
}
//Access
public OleDbConnection AccessStrCon()
{
return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\xxxx.mdb");
}
//Oracle
public OracleConnection OracleStrCon()
{
return new OracleConnection("Data Source=xxxx;Persist Security Info=True;User ID=xxxx;Password=xxxx;Unicode=True");
}
}
public class DBClass
{
//SQL Server Class
#region
public DataSet SqlGet(string sql, string tblName)
{
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, tblName);
return ds;
}
public DataSet SqlGet(string sql, string tblName, SqlParameterCollection parameters)
{
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
foreach (SqlParameter param in parameters)
{
da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
}
da.Fill(ds, tblName);
return ds;
}
public int SqlExecute(string sql)
{
int i;
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
public int SqlExecute(string sql, SqlParameterCollection parameters)
{
int i;
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlCommand cmd = new SqlCommand(sql, conn);
foreach (SqlParameter param in parameters)
{
cmd.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
public DataSet SqlExcSto(string stpName, string tblName, SqlParameterCollection parameters)
{
SqlConnection conn = new ConnectDB().SqlStrCon();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stpName;
foreach (SqlParameter param in parameters)
{
cmd.Parameters.AddWithValue(param.ParameterName, param.SqlDbType).Value = param.Value;
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, tblName);
return ds;
}
#endregion
//Access Class
#region
public DataSet AccGet(string sql, string tblName)
{
OleDbConnection conn = new ConnectDB().AccessStrCon();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, tblName);
return ds;
}
public DataSet AccGet(string sql, string tblName, OleDbParameterCollection parameters)
{
OleDbConnection conn = new ConnectDB().AccessStrCon();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
foreach (OleDbParameter param in parameters)
{
da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.OleDbType).Value = param.Value;
}
da.Fill(ds, tblName);
return ds;
}
public int AccExecute(string sql)
{
int i;
OleDbConnection conn = new ConnectDB().AccessStrCon();
OleDbCommand cmd = new OleDbCommand(sql,conn);
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
public int AccExecute(string sql, OleDbParameterCollection parameters)
{
int i;
OleDbConnection conn = new ConnectDB().AccessStrCon();
OleDbCommand cmd = new OleDbCommand(sql, conn);
foreach (OleDbParameter param in parameters)
{
cmd.Parameters.AddWithValue(param.ParameterName, param.OleDbType).Value = param.Value;
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
#endregion
//Oracle Class
#region
public DataSet OracleGet(string sql, string tblName)
{
OracleConnection conn = new ConnectDB().OracleStrCon();
OracleDataAdapter da = new OracleDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, tblName);
return ds;
}
public DataSet OracleGet(string sql, string tblName, OracleParameterCollection parameters)
{
OracleConnection conn = new ConnectDB().OracleStrCon();
OracleDataAdapter da = new OracleDataAdapter(sql, conn);
DataSet ds = new DataSet();
foreach (OracleParameter param in parameters)
{
da.SelectCommand.Parameters.AddWithValue(param.ParameterName, param.OracleType).Value = param.Value;
}
da.Fill(ds, tblName);
return ds;
}
public int OracleExecute(string sql)
{
int i;
OracleConnection conn = new ConnectDB().OracleStrCon();
OracleCommand cmd = new OracleCommand(sql, conn);
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
public int OracleExcute(string sql, OracleParameterCollection parameters)
{
int i;
OracleConnection conn = new ConnectDB().OracleStrCon();
OracleCommand cmd = new OracleCommand(sql, conn);
foreach (OracleParameter param in parameters)
{
cmd.Parameters.AddWithValue(param.ParameterName, param.OracleType).Value = param.Value;
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
#endregion
}
/////https://developer.oracle.com/dotnet/cook-vs08.html
//https://www.oracle.com/database/technologies/odp-dotnet-microsoft.html
/////////https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF260
