내부DB(Inner DB)는 MS ACCESS 파일(.mdb)
외부DB(External DB)는  MS SQL 2005 SERVER EXPRESS 를 기준으로 작성한 파일이다.
내부 DB를 사용 하기 위해선 파라미터의 names부분을 (:이름) 배열 형태로 작성하면 되고,
외부 DB를 사용 하기 위해선 파라미터의 names부분을 (@이름) 배열 형태로 작성하면 된다.

내부DB는 오피스 2003 이상 버전이 있다면 DB생성 및 편집이 가능하고,
외부DB는 상업적 이용도 무료다.(단, EXPRESS 에디션은 기능에 약간의 제한이 있긴하다.그러나 테스트 구축이나
작은 사이트를 운영하는데는 큰 무리 없다고 생각 된다.)

각각 constr과 ext_constr에 컨넥션 부분의 기본정보를 작성하고,
테이블이 필요 하다면 gettable,ext_gettable을 사용하고,
데이터만 필요 하다면 GetDatas,Ext_GetDatas를 사용하면 된다.



using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Collections;

namespace DUAL_DAO
{
    class DAO
    {
        static string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(accessDB경로);Persist Security Info=True";

        static OleDbDataReader rd;

        static OleDbDataAdapter da;

        static SqlDataReader sdr;

        static SqlDataAdapter sda;

        static string ext_constr = @"Data Source=(DB서버URL);User ID=(ID);Password=(PASSWORD)";

        DAO()
        {
           
        }

        public static bool connect_test()
        {
            bool state = false;
            SqlConnection conn = new SqlConnection(ext_constr);
            try
            {
                conn.Open();
                state = true;
                conn.Close();
            }
            catch (Exception)
            {
                state = false;
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            return state;
        }

        public static DataTable gettable(string sql)
        {
            DataSet ds = new DataSet();

            da = new OleDbDataAdapter(sql,new OleDbConnection(constr));

            da.Fill(ds);

            return ds.Tables[0];
        }

        public static DataTable Ext_gettable(string sql)
        {
            DataSet ds = new DataSet();

            sda = new SqlDataAdapter(sql, new SqlConnection(ext_constr));

            sda.Fill(ds);

            return ds.Tables[0];
        }

        public static DataTable gettable(string sql,string[] names, object[] values)
        {
            DataSet ds = new DataSet();

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    odc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            odc.Connection.Open();

            da = new OleDbDataAdapter(odc);

            da.Fill(ds);

            return ds.Tables[0];
        }

        public static DataTable Ext_gettable(string sql, string[] names, object[] values)
        {
            DataSet ds = new DataSet();

            SqlCommand sc = new SqlCommand(sql, new SqlConnection(ext_constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    sc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            sc.Connection.Open();

            sda = new SqlDataAdapter(sc);

            sda.Fill(ds);

            return ds.Tables[0];
        }

        public static DataTable ExtGetTable(string sql,string[] cnames)
        {
            DataTable dt = new DataTable();

            string[][] datas = null;

            SaveLogs(sql, cnames);

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            odc.Connection.Open();

            rd = odc.ExecuteReader();

            try
            {
                if (rd.HasRows)//데이터 테이블의 크기가 0보다 크다면
                {
                    int dt_cols_count = rd.FieldCount;

                    int k = 0;
                    //ArrayList temparr = new ArrayList();
                    for (int i = 0; i < cnames.Length; i++)
                    {
                        dt.Columns.Add(cnames[i]);
                    }

                    ArrayList temparr = new ArrayList();
                    while (rd.Read())
                    {
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            temparr.Add(Convert.ToString(rd[j]));//데이터를 생성한 배열에 저장
                        }
                        k++;
                    }

                    for (int i = 0; i < temparr.Count / dt_cols_count; i++)
                    {
                        datas[i] = new string[dt_cols_count];//해당 행만큼 세부 1차원 배열 생성
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            datas[i][j] = (string)temparr[(i * dt_cols_count) + j];
                        }
                    }

                    for (int i = 0; i < dt_cols_count; i++)
                    {
                        dt.Rows.Add(datas[i]);
                    }
                }
                else
                {
                    dt = null;//0보다 작다면 null값 설정
                }
            }
            catch (Exception ex)
            {
                Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (odc.Connection.State == ConnectionState.Open)
                {
                    odc.Connection.Close();
                }
                odc.Dispose();
            }

            return dt;
        }

        public static string[][] GetDatas(string sql, string[] names, object[] values)
        {
            string[][] datas = null;

            SaveLogs(sql, values);

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    odc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            odc.Connection.Open();

            rd = odc.ExecuteReader();

            try
            {
                if (rd.HasRows)//데이터 테이블의 크기가 0보다 크다면
                {
                    int dt_cols_count = rd.FieldCount;

                    int k = 0;
                    ArrayList temparr = new ArrayList();
                    while (rd.Read())
                    {
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            temparr.Add(Convert.ToString(rd[j]));//데이터를 생성한 배열에 저장
                        }
                        k++;
                    }

                    datas = new string[temparr.Count / dt_cols_count][];//행만큼의 2차원 배열 생성
                    for (int i = 0; i < temparr.Count / dt_cols_count; i++)
                    {
                        datas[i] = new string[dt_cols_count];//해당 행만큼 세부 1차원 배열 생성
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            datas[i][j] = (string)temparr[(i * dt_cols_count) + j];
                        }
                    }
                }
                else
                {
                    datas = null;//0보다 작다면 null값 설정
                }
            }
            catch (Exception ex)
            {
                Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (odc.Connection.State == ConnectionState.Open)
                {
                    odc.Connection.Close();
                }
                //if(odc != null) odc.Dispose();
            }

            return datas;
        }

        public static object[][] Get_Inner_Datas(string sql, string[] names, object[] values)
        {
            object[][] datas = null;

            SaveLogs(sql, values);

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    odc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            odc.Connection.Open();

            rd = odc.ExecuteReader();

            try
            {
                if (rd.HasRows)//데이터 테이블의 크기가 0보다 크다면
                {
                    int dt_cols_count = rd.FieldCount;

                    int k = 0;
                    ArrayList temparr = new ArrayList();
                    while (rd.Read())
                    {
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            temparr.Add(rd[j]);//데이터를 생성한 배열에 저장
                        }
                        k++;
                    }

                    datas = new string[temparr.Count / dt_cols_count][];//행만큼의 2차원 배열 생성
                    for (int i = 0; i < temparr.Count / dt_cols_count; i++)
                    {
                        datas[i] = new string[dt_cols_count];//해당 행만큼 세부 1차원 배열 생성
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            datas[i][j] = temparr[(i * dt_cols_count) + j];
                        }
                    }
                }
                else
                {
                    datas = null;//0보다 작다면 null값 설정
                }
            }
            catch (Exception ex)
            {
                Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (odc.Connection.State == ConnectionState.Open)
                {
                    odc.Connection.Close();
                }
                //if(odc != null) odc.Dispose();
            }

            return datas;
        }

        public static string[][] Ext_GetDatas(string sql, string[] names, object[] values)
        {
            string[][] datas = null;

            Ext_SaveLogs(sql, values);

            //OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(ext_constr));
            SqlCommand sc = new SqlCommand(sql, new SqlConnection(ext_constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    sc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            sc.Connection.Open();

            sdr = sc.ExecuteReader();

            try
            {
                if (sdr.HasRows)//데이터 테이블의 크기가 0보다 크다면
                {
                    int dt_cols_count = sdr.FieldCount;

                    int k = 0;
                    ArrayList temparr = new ArrayList();
                    while (sdr.Read())
                    {
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            temparr.Add(sdr[j]);//데이터를 생성한 배열에 저장
                        }
                        k++;
                    }

                    datas = new string[temparr.Count / dt_cols_count][];//행만큼의 2차원 배열 생성
                    for (int i = 0; i < temparr.Count / dt_cols_count; i++)
                    {
                        datas[i] = new string[dt_cols_count];//해당 행만큼 세부 1차원 배열 생성
                        for (int j = 0; j < dt_cols_count; j++)
                        {
                            datas[i][j] = temparr[(i * dt_cols_count) + j].ToString();
                        }
                    }
                }
                else
                {
                    datas = null;//0보다 작다면 null값 설정
                }
            }
            catch (Exception ex)
            {
                Ext_Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (sc.Connection.State == ConnectionState.Open)
                {
                    sc.Connection.Close();
                }
                //if(odc != null) odc.Dispose();
            }

            return datas;
        }

        public static int ExcuteQuery(string sql, string[] names, object[] values)
        {
            int state = 0;

            SaveLogs(sql, values);

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    odc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            try
            {
                odc.Connection.Open();

                state = odc.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (odc.Connection.State == ConnectionState.Open)
                {
                    odc.Connection.Close();
                }
                odc.Dispose();
            }

            return state;
        }

        public static int Ext_ExcuteQuery(string sql, string[] names,object[] values)
        {
            int state = 0;

            Ext_SaveLogs(sql, values);

            SqlCommand idc = new SqlCommand(sql, new SqlConnection(ext_constr));

            if (values != null)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    idc.Parameters.AddWithValue(names[i], values[i]);
                }
            }

            try
            {
                idc.Connection.Open();

                state = idc.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Ext_Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (idc.Connection.State == ConnectionState.Open)
                {
                    idc.Connection.Close();
                }
                idc.Dispose();
            }

            return state;
        }

        public static bool SaveLogs(string sql, object[] values)
        {
            bool state = false;

            try
            {
                using (StreamWriter sw = new StreamWriter("./querylogs.txt", true))
                {
                    sw.WriteLine("---------------------------------");
                    sw.WriteLine(DateTime.Now.ToString());
                    sw.WriteLine(sql);
                    if (values != null)
                    {
                        foreach (object i in values)
                        {
                            sw.Write(i.ToString() + ", ");
                        }
                        sw.WriteLine();
                    }
                    sw.WriteLine("---------------------------------");
                }
                state = true;
            }
            catch (Exception ex)
            {
                state = false;
                Save_Error_Logs(ex.Message);
            }

            return state;
        }

        public static bool Ext_SaveLogs(string sql, object[] values)
        {
            bool state = false;

            try
            {
                using (StreamWriter sw = new StreamWriter("./Ext_querylogs.txt", true))
                {
                    sw.WriteLine("---------------------------------");
                    sw.WriteLine(DateTime.Now.ToString());
                    sw.WriteLine(sql);
                    if (values != null)
                    {
                        foreach (object i in values)
                        {
                            sw.Write(i.ToString() + ", ");
                        }
                        sw.WriteLine();
                    }
                    sw.WriteLine("---------------------------------");
                }
                state = true;
            }
            catch (Exception ex)
            {
                state = false;
                Save_Error_Logs(ex.Message);
            }

            return state;
        }

        public static int ExcuteQuery(string sql)
        {
            int state = 0;

            OleDbCommand odc = new OleDbCommand(sql, new OleDbConnection(constr));

            try
            {
                odc.Connection.Open();

                state = odc.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Save_Error_Logs(ex.Message);
            }
            finally
            {
                if (odc.Connection.State == ConnectionState.Open)
                {
                    odc.Connection.Close();
                }
                odc.Dispose();
            }

            return state;
        }

        public static int Updating_External_Servers(string logfile)
        {
            int state = 0;

            return state;
        }

        public static bool Save_Error_Logs(string errormesage)
        {
            bool state = false;

            try
            {
                using (StreamWriter sw = new StreamWriter("./errorlogs.txt", true))
                {
                    sw.WriteLine("---------------------------------");
                    sw.WriteLine(DateTime.Now.ToString());
                    sw.WriteLine(errormesage);
                    sw.WriteLine("---------------------------------");
                }
                state = true;
            }
            catch (Exception)
            {
                state = false;
            }

            return state;
        }

        public static bool Ext_Save_Error_Logs(string errormesage)
        {
            bool state = false;

            try
            {
                using (StreamWriter sw = new StreamWriter("./Ext_errorlogs.txt", true))
                {
                    sw.WriteLine("---------------------------------");
                    sw.WriteLine(DateTime.Now.ToString());
                    sw.WriteLine(errormesage);
                    sw.WriteLine("---------------------------------");
                }
                state = true;
            }
            catch (Exception)
            {
                state = false;
            }

            return state;
        }
    }
}



by Tomoya 2009.07.20 11:49
| 1 ··· 4 5 6 7 8 9 10 11 |