เอา Class สำหรับ Connect Database มาแจก ติดต่อ ACCESS,MSSQL,ORACLE

string sql1 = "Select * from Employees";
DataSet dsEmp1 = new DBClass().SqlGet(sql1,"tblEmployee");

command insert,delete,update
string sql3 = "Insert into Employees(empId,empName) " +
                " Values(1,'pheak')";
        int i = new DBClass().SqlExecute(sql3);

string sql4 = "Insert into Employees(empId,empName) " +
            " Values(@empId,@empName) ";
        SqlParameterCollection param2 = new SqlCommand().Parameters;
        param2.AddWithValue("empId", SqlDbType.Int).Value = 1;
        param2.AddWithValue("empName",SqlDbType.VarChar).Value = "pheak";
        int i2 = new DBClass().SqlExecute(sql4, param2);

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 [email protected]
/// มีข้อสงสัย,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
}

//การใช้งาน Class SQL
        //อ่านข้อมูลจาก Database แบบไม่มี Parameters
        string sql1 = "Select * from Employees";
        DataSet dsEmp1 = new DBClass().SqlGet(sql1,"tblEmployee");

        //อ่านข้อมูล แบบใช้ parameters
        string sql2 = "select * from Employees where empId=@empId";
        SqlParameterCollection param = new SqlCommand().Parameters;
        param.AddWithValue("empId",SqlDbType.Int).Value = 1;
        DataSet dsEmp2 = new DBClass().SqlGet(sql2, "tblEmployee", param);

        //Insert,delete,update
        string sql3 = "Insert into Employees(empId,empName) " +
                " Values(1,'pheak')";
        int i = new DBClass().SqlExecute(sql3);

        //Insert,delete,update แบบใช้ Parameters
        string sql4 = "Insert into Employees(empId,empName) " +
            " Values(@empId,@empName) ";
        SqlParameterCollection param2 = new SqlCommand().Parameters;
        param2.AddWithValue("empId", SqlDbType.Int).Value = 1;
        param2.AddWithValue("empName",SqlDbType.VarChar).Value = "pheak";
        int i2 = new DBClass().SqlExecute(sql4, param2);

        //ส่วน Access กับ Oracle ตัวอย่างก็จะคล้ายๆ กับ SQL ครับ ต่างกันตรงที่
        // Oracle สามารถประกาศ OracleParameterCollection param = new OracleParameterCollection();
        // ส่วนจุดอื่นๆ ก็เหมือนกัน โดยที่ Access ใช้ AccGet,AccExecute ส่วน Oracle ใช้ OracleGet,OracleExcute