DataAccess

using System;
using System.Data;
using System.Data.OleDb;
using System.ComponentModel;

/// <summary>
///     DataAccess layer defined to manage database [Selects,Inserts,Updates]
///     for anyone table.
///     <remarks>
///         In this class, Sql Command objects with parameter collections and
///         datasets have been used throughout since in each of the Select,
///         Insert and Update cases the code is working with a single dataset
///         containing a data table.
///     </remarks>
///     <remarks>
///         This class uses Dispose semantics for closing the active
///         database connection.
///     </remarks>
/// </summary>
///
public class DataAccess
{
    /// <summary>
    /// DataBase connect string.
    /// </summary>
    private OleDbConnection m_oleDbConn;

    public DataAccess()
    {

    }

    /// <summary>   
    ///     <remarks>Initialize the internal OleDbConnection object.</remarks>
    /// </summary>
    private OleDbConnection GetConnection()
    {
        try
        {
            String strDbConnection = "";

            strDbConnection = NexcashConfiguration.GetNexCashConfiguration.GetDataBaseConnectionString;
            if (strDbConnection.StartsWith("Provider=Microsoft.Jet"))
                strDbConnection = strDbConnection.Replace("[AppDomainPath]", AppDomain.CurrentDomain.BaseDirectory);

            return new OleDbConnection(strDbConnection.Trim());
        }

        catch (Exception error)
        {
            throw new Exception(error.ToString());
            return null;
        }

    }
    private void GetLoadConnection()
    {
        try
        {
            String strDbConnection = "";

            strDbConnection = NexcashConfiguration.GetNexCashConfiguration.GetDataBaseConnectionString;
            if (strDbConnection.StartsWith("Provider=Microsoft.Jet"))
                strDbConnection = strDbConnection.Replace("[AppDomainPath]", AppDomain.CurrentDomain.BaseDirectory);

            m_oleDbConn = new OleDbConnection(strDbConnection.Trim());
            m_oleDbConn.Open();
        }

        catch (Exception error)
        {
            throw new Exception(error.ToString());
        }

 

    }

    /// <summary>
    /// execute select sentence.
    /// This function is substituted,but in order to  compatibility,reserve it.
    /// recommend use of GetQueryData function.this function return value is true or false.
    /// </summary>
    /// <param name="strSelect">import select sentence.</param>
    /// <returns>DataSet, a dataset containing detailed  table information by execute select sentence.</returns>
    public DataSet ExecuteSQL(String strSelect)
    {
        DataSet data = new DataSet();
        try
        {
            //
            // Get the load connection
            //
            GetLoadConnection();
            OleDbCommand oleCommand = new OleDbCommand(strSelect);
            //oleCommand.CommandType = CommandType.Text ;
            oleCommand.Connection = m_oleDbConn;

            OleDbDataAdapter dsCommand = new OleDbDataAdapter(oleCommand);
            dsCommand.Fill(data);
        }

        catch (Exception error)
        {
            throw new Exception(error.ToString());
            //return false;
        }

        finally
        {
            m_oleDbConn.Close();
            m_oleDbConn.Dispose();
        }
        return data;
    }
    public DataSet ExecuteSQL(String strSelect, string Connectionstring)
    {
        DataSet data = new DataSet();
        try
        {
            //
            // Get the load connection
            //
            OleDbCommand oleCommand = new OleDbCommand(strSelect);
            //oleCommand.CommandType = CommandType.Text ;
            oleCommand.Connection = new OleDbConnection(Connectionstring);

            OleDbDataAdapter dsCommand = new OleDbDataAdapter(oleCommand);
            dsCommand.Fill(data);
        }

        catch (Exception error)
        {
            throw new Exception(error.ToString());
            //return false;
        }
        return data;
    }
    /// <summary>
    /// execute update sentence.
    ///
    /// </summary>
    /// <param name="strUpdate">import update sentence.</param>
    /// <returns>Success,return true;error,return false.</returns>
    public bool UpdateManipulate(String strUpdate)
    {
        try
        {
            //
            // Get the load connection
            //
            GetLoadConnection();
            OleDbCommand oleCommand = new OleDbCommand(strUpdate);
            //oleCommand.CommandType = CommandType.Text ;
            oleCommand.Connection = m_oleDbConn;
            oleCommand.ExecuteNonQuery();

            return true;
        }

        catch (Exception error)
        {
            //throw new Exception (error.ToString ());
            return false;
        }
        m_oleDbConn.Close();

    }  //Function UpdateManipulate

    /// <summary>
    /// execute select sentence.
    /// This function is substituted,but in order to  compatibility,reserve it.
    /// recommend use of GetQueryData function.this function return value is true or false.
    /// </summary>
    /// <param name="strTableName">Import table name.</param>
    /// <param name="strSQL">import select sentence.</param>
    /// <returns>DataSet, a dataset containing detailed  table information by execute select sentence.</returns>
    public DataSet GetQueryData(string strTableName, string strSQL)
    {
        DataSet data = new DataSet();
        OleDbCommand oleCommand = null;
        try
        {
            //
            // Get the load connection
            //
            //GetLoadConnection();
            oleCommand = new OleDbCommand(strSQL);
            //oleCommand.CommandType = CommandType.Text ;
            oleCommand.Connection = GetConnection();

            OleDbDataAdapter dsAdapter = new OleDbDataAdapter(oleCommand);
            OleDbCommandBuilder cbBuilder = new OleDbCommandBuilder(dsAdapter);
            try
            {
                dsAdapter.DeleteCommand = cbBuilder.GetDeleteCommand();
                dsAdapter.InsertCommand = cbBuilder.GetInsertCommand();
                dsAdapter.UpdateCommand = cbBuilder.GetUpdateCommand();
            }
            catch (System.Exception)
            {
            }

            if (strTableName == null || strTableName.Trim() == "")
            {
                dsAdapter.Fill(data);
                data.Tables[0].ExtendedProperties.Add("adapter", dsAdapter);
            }
            else
            {
                dsAdapter.Fill(data, strTableName);
                data.Tables[strTableName].ExtendedProperties.Add("adapter", dsAdapter);
            }
        }

        catch (Exception error)
        {
            throw new Exception(error.ToString());
        }

        //   finally
        //   {
        //    oleCommand.Connection.Close ();
        //    oleCommand.Connection.Dispose ();
        //   }
        return data;
    }  //Function GetQueryData

    /// <summary>
    /// execute select sentence.
    /// </summary>
    /// <param name="dsData">DataSet, a dataset containing detailed  table information.</param>
    /// <param name="strTableName">Import table name.</param>
    /// <param name="strSQL">import select sentence.</param>
    /// <returns>Success,return true;error,return false.</returns>
    public bool GetQueryData(DataSet dsData, String strTableName, String strSQL)
    {
        DataSet data = dsData;
        OleDbCommand oleCommand = null;
        try
        {
            //
            // Get the load connection
            //
            //GetLoadConnection();
            oleCommand = new OleDbCommand(strSQL);
            oleCommand.Connection = GetConnection();

            OleDbDataAdapter dsAdapter = new OleDbDataAdapter(oleCommand);
            OleDbCommandBuilder cbBuilder = new OleDbCommandBuilder(dsAdapter);
            try
            {
                dsAdapter.DeleteCommand = cbBuilder.GetDeleteCommand();
                dsAdapter.InsertCommand = cbBuilder.GetInsertCommand();
                dsAdapter.UpdateCommand = cbBuilder.GetUpdateCommand();
            }
            catch (System.Exception)
            {
            }
            if (strTableName == null || strTableName.Trim() == "")
            {
                dsAdapter.Fill(data);
                data.Tables[0].ExtendedProperties.Add("adapter", dsAdapter);
            }
            else
            {
                dsAdapter.Fill(data, strTableName);
                if (data.Tables[strTableName].ExtendedProperties.Contains("adapter"))
                    data.Tables[strTableName].ExtendedProperties.Remove("adapter");

                data.Tables[strTableName].ExtendedProperties.Add("adapter", dsAdapter);
            }
            return true;
        }

        catch (System.SystemException er)
        {
            return false;
        }

        //   finally
        //   {
        //    oleCommand.Connection.Close ();
        //    oleCommand.Connection.Dispose ();
        //   }

    }  //Function GetQueryData

    /// <summary>
    /// execute update sentence.
    /// </summary>
    /// <param name="dsData">DataSet,a dataset containing detailed  table information.</param>
    /// <param name="strTableName">Import table name.</param>
    /// <returns>Success,return true;error,return false.</returns>
    public bool UpdateQueryData(DataSet dsData, string strTableName)
    {
        OleDbDataAdapter dsCommand = null;
        try
        {
            //
            // Get the load connection
            //
            //GetLoadConnection();
            if (strTableName == null || strTableName.Trim() == "")
            {
                dsCommand = (OleDbDataAdapter)dsData.Tables[0].ExtendedProperties["adapter"];
                //dsCommand.SelectCommand.Connection =GetConnection();
                dsCommand.Update(dsData, dsData.Tables[0].TableName);
            }
            else
            {
                dsCommand = (OleDbDataAdapter)dsData.Tables[strTableName].ExtendedProperties["adapter"];
                //dsCommand.SelectCommand.Connection = GetConnection();
                dsCommand.Update(dsData, strTableName);
            }

            return true;
        }

        catch (Exception error)
        {
            return false;
        }

        //   finally
        //   {
        //    dsCommand.SelectCommand.Connection .Close ();
        //    dsCommand.SelectCommand.Connection .Dispose ();
        //   }  
    }

    public bool ExecuteDML(String strSQL)
    {
        try
        {
            //
            // Get the load connection
            //
            GetLoadConnection();
            OleDbCommand oleCommand = new OleDbCommand(strSQL);

            oleCommand.Connection = m_oleDbConn;

            //oleCommand.CommandText = strSQL ;
            //oleCommand.CommandType = CommandType.Text ;

            //OleDbDataAdapter dsAdapter = new OleDbDataAdapter (oleCommand);
            oleCommand.ExecuteNonQuery();

            return true;

        }

        catch (System.Data.OleDb.OleDbException ex)
        {
            ex.ToString();
            return false;

        }

        //   finally
        //   {
        //    m_oleDbConn.Close ();
        //    m_oleDbConn.Dispose ();
        //   }

    }

    public object ExecuteScalar(string strSQL)
    {
        GetLoadConnection();
        OleDbCommand oleCommand = new OleDbCommand(strSQL);
        oleCommand.Connection = m_oleDbConn;
        oleCommand.CommandText = strSQL;
        return oleCommand.ExecuteScalar();
    }

    /// <summary>
    /// 生成CrossTable数据
    /// -----------sample--------
    /// public class testunit
    ///{
    ///[Test]
    ///public void testcrosstable()
    ///{
    /// DataAccess s=new DataAccess();
    /// string sql="SELECT READERID,READERNAME, PROJECTID,PLACE FROM READER";
    /// string crosssql="SELECT PROJECTID,PROJECTNAME FROM PROJECT WHERE PROJECTTYPE=1";
    /// DataSet ds=s.GetCrossTab(sql,crosssql);
    /// ds.Tables[0].Rows[0][2]=true;
    /// s.UpdateCrossTab(ds);
    /// ds=s.GetCrossTab(sql,crosssql);
    /// Assert.AreEqual(ds.Tables[0].Rows[0][2].ToString(),"True");
    ///}
    ///}
    /// </summary>
    /// <param name="Sql"></param>
    /// <param name="CrossSql"></param>
    /// <param name="CrossValueCol"></param>
    /// <param name="CrossDisplayCol"></param>
    /// <returns></returns>
    public DataSet GetCrossTab(string Sql, string CrossSql)
    {
        try
        {
            Sql = Sql.ToUpper();
            CrossSql = CrossSql.ToUpper();
            DataSet dsSrcData = GetQueryData("Src", Sql);   //获取原始数据
            DataSet dsCrossData = GetQueryData("Cross", CrossSql);   //获取相关联的交叉表字段信息
            DataSet dsResultData = new DataSet();              //定义要返回的交叉表数据集对象
            DataTable tblResult = new DataTable("result");     //定义要返回的交叉表对象
            string CrossValueCol = dsCrossData.Tables["Cross"].Columns[0].ColumnName;
            string CrossDisplayCol = dsCrossData.Tables["Cross"].Columns[1].ColumnName;
            //-------以下开始生成交叉表的字段结构----------------------------
            for (int i = 0; i < dsSrcData.Tables["Src"].Columns.Count; i++)
            {
                if (dsSrcData.Tables["Src"].Columns[i].ColumnName != CrossValueCol)  //判断如果不是要交叉的字段信息，就在返回表中加入该字段
                    tblResult.Columns.Add(dsSrcData.Tables["Src"].Columns[i].ColumnName, dsSrcData.Tables["Src"].Columns[i].DataType);
                else
                    for (int n = 0; n < dsCrossData.Tables["Cross"].Rows.Count; n++)
                    {
                        DataColumn col = new DataColumn(dsCrossData.Tables["Cross"].Rows[n][CrossDisplayCol].ToString(), true.GetType());
                        col.ExtendedProperties.Add("mappingvalue", dsCrossData.Tables["Cross"].Rows[n][CrossValueCol]);
                        tblResult.Columns.Add(col);
                    }

            }
            //------以下开始根据原始数据生成交叉表的数据---------------------
            for (int m = 0; m < dsSrcData.Tables["Src"].Rows.Count; m++)
            {
                DataRow drTemp = tblResult.NewRow();
                string tempvalue;
                for (int i = 0; i < dsSrcData.Tables["Src"].Columns.Count; i++)
                {
                    if (dsSrcData.Tables["Src"].Columns[i].ColumnName != CrossValueCol)  //判断如果不是要交叉的字段信息，就在返回表中加入该字段
                        drTemp[dsSrcData.Tables["Src"].Columns[i].ColumnName] = dsSrcData.Tables["Src"].Rows[m][dsSrcData.Tables["Src"].Columns[i].ColumnName];
                    else
                    {
                        tempvalue = dsSrcData.Tables["Src"].Rows[m][dsSrcData.Tables["Src"].Columns[i].ColumnName].ToString();
                        for (int n = 0; n < dsCrossData.Tables["Cross"].Rows.Count; n++)
                        {
                            if (tempvalue == dsCrossData.Tables["Cross"].Rows[n][CrossValueCol].ToString())
                                drTemp[dsCrossData.Tables["Cross"].Rows[n][CrossDisplayCol].ToString()] = true;
                            else
                                drTemp[dsCrossData.Tables["Cross"].Rows[n][CrossDisplayCol].ToString()] = false;
                        }
                    }
                }
                tblResult.Rows.Add(drTemp);
            }
            dsResultData.Tables.Add(tblResult);
            dsResultData.Tables.Add(dsSrcData.Tables[0].Copy());
            dsResultData.Tables.Add(dsCrossData.Tables[0].Copy());
            return dsResultData;
        }
        catch (System.Exception error)
        {
            return null;
        }
    }

    /// <summary>
    /// ---------------sample----------------------------
    /// [Test]
    ///  public void testcrosstable2()
    ///  {
    ///   DataAccess s=new DataAccess();
    ///   string sql="SELECT READERID,READERNAME FROM READER";
    ///   string mappingsql="SELECT USEID,READERID,PROJECTID FROM USER_PROJECT";
    ///   string crosssql="SELECT PROJECTID,PROJECTNAME FROM PROJECT WHERE PROJECTTYPE=0";
    ///   DataSet ds=s.GetCrossTab(sql,mappingsql,crosssql);
    ///   ds.Tables[0].Rows[0][2]=true;
    ///   ds.Tables[0].Rows[0][3]=true;
    ///   ds.Tables[0].Rows[0][4]=true;
    ///   ds.Tables[0].Rows[0][5]=true;
    ///   ds.Tables[0].Rows[0][6]=true;
    ///   s.UpdateCrossTab(ds);
    ///   ds=s.GetCrossTab(sql,mappingsql,crosssql);
    ///   Assert.AreEqual(ds.Tables[0].Rows[0][6].ToString(),"True");
    ///  }
    /// </summary>
    /// <param name="Sql"></param>
    /// <param name="MappingSql"></param>
    /// <param name="CrossSql"></param>
    /// <returns></returns>
    public DataSet GetCrossTab(string Sql, string MappingSql, string CrossSql)
    {
        try
        {
            Sql = Sql.ToUpper();
            MappingSql = MappingSql.ToUpper();
            CrossSql = CrossSql.ToUpper();
            DataSet dsSrcData = GetQueryData("Src", Sql);   //获取原始数据
            DataSet dsMappingData = GetQueryData("Mapping", MappingSql);
            DataSet dsCrossData = GetQueryData("Cross", CrossSql);   //获取相关联的交叉表字段信息
            DataSet dsResultData = new DataSet();              //定义要返回的交叉表数据集对象
            DataTable tblResult = new DataTable("result");     //定义要返回的交叉表对象
            string CrossValueCol = dsCrossData.Tables["Cross"].Columns[0].ColumnName;
            string CrossDisplayCol = dsCrossData.Tables["Cross"].Columns[1].ColumnName;
            //-------以下开始生成交叉表的字段结构----------------------------
            for (int i = 0; i < dsSrcData.Tables["Src"].Columns.Count; i++)
            {
                tblResult.Columns.Add(dsSrcData.Tables["Src"].Columns[i].ColumnName, dsSrcData.Tables["Src"].Columns[i].DataType);
            }
            for (int n = 0; n < dsCrossData.Tables["Cross"].Rows.Count; n++)
            {
                DataColumn col = new DataColumn(dsCrossData.Tables["Cross"].Rows[n][CrossDisplayCol].ToString(), true.GetType());
                col.DefaultValue = false;
                col.ExtendedProperties.Add("mappingvalue", dsCrossData.Tables["Cross"].Rows[n][CrossValueCol]);
                tblResult.Columns.Add(col);
            }

            //------以下开始根据原始数据生成交叉表的数据---------------------
            for (int m = 0; m < dsSrcData.Tables["Src"].Rows.Count; m++)
            {
                DataRow drTemp = tblResult.NewRow();
                for (int i = 0; i < dsSrcData.Tables["Src"].Columns.Count; i++)//判断如果不是要交叉的字段信息，就在返回表中加入该字段
                {
                    drTemp[dsSrcData.Tables["Src"].Columns[i].ColumnName] = dsSrcData.Tables["Src"].Rows[m][dsSrcData.Tables["Src"].Columns[i].ColumnName];
                }
                //-----------------------------------------------------------
                string MappingCol = dsMappingData.Tables[0].Columns[1].ColumnName;
                string strFilter = MappingCol + "=";
                strFilter = strFilter + dsSrcData.Tables["Src"].Rows[m][MappingCol].ToString();
                dsMappingData.Tables[0].DefaultView.RowFilter = strFilter;
                for (int n = 0; n < dsMappingData.Tables[0].DefaultView.Count; n++)
                {
                    dsCrossData.Tables[0].DefaultView.RowFilter = dsCrossData.Tables[0].Columns[0].ColumnName + "=" + dsMappingData.Tables[0].DefaultView[n][2].ToString();
                    drTemp[dsCrossData.Tables[0].DefaultView[0][1].ToString()] = true;
                }

                tblResult.Rows.Add(drTemp);
            }
            dsResultData.Tables.Add(tblResult);
            dsResultData.Tables.Add(dsSrcData.Tables[0].Copy());
            dsResultData.Tables.Add(dsMappingData.Tables[0].Copy());
            dsResultData.Tables.Add(dsCrossData.Tables[0].Copy());
            return dsResultData;
        }
        catch (System.Exception error)
        {
            return null;
        }
    }
    /// <summary>
    /// 更新CrossTable数据
    /// </summary>
    /// <param name="Sql"></param>
    /// <param name="CrossSql"></param>
    /// <param name="CrossValueCol"></param>
    /// <param name="CrossDisplayCol"></param>
    /// <param name="CrossData"></param>
    /// <returns></returns>
    public bool UpdateCrossTab(DataSet CrossData)
    {
        if (CrossData.Tables.Count == 3)
            return UpdateTwoCross(CrossData);
        else
            return UpdateThreeCross(CrossData);
    }
    private bool UpdateTwoCross(DataSet CrossData)
    {
        DataTable dtResult = CrossData.Tables["result"];
        DataTable dtSrc = CrossData.Tables["Src"];
        DataTable dtCross = CrossData.Tables["Cross"];
        bool IsBind;
        string ColName;
        string Val;
        for (int i = 0; i < dtResult.Rows.Count; i++)
        {
            IsBind = false;
            for (int n = 0; n < dtCross.Rows.Count; n++)
            {
                ColName = dtCross.Rows[n][1].ToString();
                Val = dtResult.Rows[i][ColName].ToString();
                if (Val == "True")
                {
                    dtSrc.Rows[i][dtCross.Columns[0].ColumnName] = dtCross.Rows[n][0];
                    IsBind = true;
                    break;
                }
            }
            if (IsBind == false)
                dtSrc.Rows[i][dtCross.Columns[0].ColumnName] = DBNull.Value;
        }
        this.UpdateQueryData(CrossData, "Src");
        return true;
    }
    private bool UpdateThreeCross(DataSet CrossData)
    {
        DataTable dtResult = CrossData.Tables["result"];
        DataTable dtSrc = CrossData.Tables["Src"];
        DataTable dtCross = CrossData.Tables["Cross"];
        DataTable dtMapping = CrossData.Tables["Mapping"];
        string strMappingCol;
        string strCrossCol;
        for (int i = 0; i < dtResult.Rows.Count; i++)
        {
            strMappingCol = dtMapping.Columns[1].ColumnName;
            dtMapping.DefaultView.RowFilter = strMappingCol + "=" + dtResult.Rows[i][strMappingCol].ToString();
            for (int n = dtMapping.DefaultView.Count - 1; n >= 0; n--)//把原来的交叉纪录删除
                dtMapping.DefaultView[n].Row.Delete();
            for (int m = 0; m < dtCross.Rows.Count; m++)
            {
                strCrossCol = dtCross.Rows[m][1].ToString();
                if (dtResult.Rows[i][strCrossCol].ToString() == "True")  //判断该交叉数据是否被选中
                {
                    DataRow drRow = dtMapping.NewRow();
                    drRow[1] = dtResult.Rows[i][strMappingCol];
                    drRow[2] = dtCross.Rows[m][0];
                    dtMapping.Rows.Add(drRow);
                }
            }
        }
        this.UpdateQueryData(CrossData, "Mapping");
        return true;
    }
    /// <summary>
    /// ------------example---------------------
    /// [Test]
    ///public void testtransaction()
    ///{
    ///
    ///DataAccess s=new DataAccess();
    ///DataSet ds=s.GetQueryData("work","select * from reader");
    ///ds.Tables[0].Rows[0][5]="ok";
    ///OleDbTransaction trans=s.GetTansaction(ds,"");
    ///s.UpdateQueryData(ds,"work");
    ///trans.Commit();
    /// }
    /// </summary>
    /// <param name="trans"></param>
    /// <param name="DS"></param>
    /// <returns></returns>
    public OleDbTransaction GetTansaction(DataSet DS, string TableName)
    {
        OleDbDataAdapter adapter;
        OleDbTransaction trans;
        try
        {
            if (TableName != "")
                adapter = (OleDbDataAdapter)DS.Tables[TableName].ExtendedProperties["adapter"];
            else
                adapter = (OleDbDataAdapter)DS.Tables[0].ExtendedProperties["adapter"];

            if (adapter.SelectCommand.Connection.State == System.Data.ConnectionState.Closed)
            {
                adapter.SelectCommand.Connection = GetConnection();
                adapter.InsertCommand.Connection = adapter.SelectCommand.Connection;
                adapter.DeleteCommand.Connection = adapter.SelectCommand.Connection;
                adapter.UpdateCommand.Connection = adapter.SelectCommand.Connection;
                adapter.SelectCommand.Connection.Open();
            }
            trans = adapter.SelectCommand.Connection.BeginTransaction();
            adapter.InsertCommand.Transaction = trans;
            adapter.DeleteCommand.Transaction = trans;
            adapter.UpdateCommand.Transaction = trans;
            return trans;
        }
        catch (System.Exception error)
        {
            string s = error.ToString();
            return null;
        }
    }
}