SSWMS_2220449_CP/SiaSun.LMS.Persistence/P_Base.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using IBatisNet.Common;
using IBatisNet.Common.Exceptions;
using IBatisNet.Common.Pagination;
using IBatisNet.Common.Utilities;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.Exceptions;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.DataMapper.Scope;
using System.Data;
using System.Reflection;
using System.Data.Common;
using SiaSun.LMS.Model;


namespace SiaSun.LMS.Persistence
{
    public class P_Base
    {
        public P_Base()
        {

        }

        public  ISqlMapper _sqlMap;


        public string _sConfig;

        #region  Connection&Transaction

        #endregion

        #region Methods

        /// <summary>
        /// �õ��б�
        /// </summary>
        /// <typeparam name="T">ʵ������</typeparam> 
        /// <param name="statementName">�������ƣ���Ӧxml�е�Statement��id</param>
        /// <param name="parameterObject">����</param>
        /// <returns></returns>
        protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject)
        {
            try
            {
                return this._sqlMap.QueryForList<T>(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for list.  Cause: " + e.Message, e);
            }
        }

        public ObjectList ExecuteQueryForList(string statementName, object parameterObject)
        {
            try
            {
                IList<object> ls = this._sqlMap.QueryForList<object>(statementName, parameterObject);

                ObjectList objlist = new ObjectList(ls);

                return new Model.ObjectList(ls);
                
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for list.  Cause: " + e.Message, e);
            }
        }


        /// <summary>
        /// �õ�ָ�������ļ�¼��
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statementName"></param>
        /// <param name="parameterObject">����</param>
        /// <param name="skipResults">�����ļ�¼��</param>
        /// <param name="maxResults">���󷵻صļ�¼��</param>
        /// <returns></returns>
        protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject, int skipResults, int maxResults)
        {
            try
            {
                return this._sqlMap.QueryForList<T>(statementName, parameterObject, skipResults, maxResults);
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for list.  Cause: " + e.Message, e);
            }
        }

        protected IPaginatedList ExecuteQueryForPaginatedList(string statementName, object parameterObject, int pageSize)
        {
            try
            {
                return this._sqlMap.QueryForPaginatedList(statementName, parameterObject, pageSize);
            }
            catch (Exception e)
            {
                throw new IBatisNetException("Error executing query '" + statementName + "' for paginated list.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// ��ѯ�õ�������һ��ʵ��
        /// </summary>
        /// <typeparam name="T">����type</typeparam>
        /// <param name="statementName">������</param>
        /// <param name="parameterObject">����</param>
        /// <returns></returns>
        public T ExecuteQueryForObject<T>(string statementName, object parameterObject)
        {
            try
            {
                return this._sqlMap.QueryForObject<T>(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for object.  Cause: " + e.Message, e);
            }
        }

        public ObjectT ExecuteQueryForObject(string statementName, object parameterObject)
        {
            try
            {
                object obj = this._sqlMap.QueryForObject(statementName, parameterObject);
                return new ObjectT(obj);
               
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for object.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// ִ������
        /// </summary>
        /// <param name="statementName">������</param>
        /// <param name="parameterObject">����</param>
        protected int ExecuteInsert(string statementName, object parameterObject)
        {
            object generatedKey = null;

            try
            {
                generatedKey = this._sqlMap.Insert(statementName, parameterObject);

            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for insert.  Cause: " + e.Message, e);
            }

            return generatedKey == null ? 0 : 1;
        }

        /// <summary>
        /// ִ���޸�
        /// </summary>
        /// <param name="statementName">������</param>
        /// <param name="parameterObject">����</param>
        protected int ExecuteUpdate(string statementName, object parameterObject)
        {
            try
            {
                return this._sqlMap.Update(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for update.  Cause: " + e.Message, e);
            }
        }

        /// <summary>
        /// ִ��ɾ��
        /// </summary>
        /// <param name="statementName">������</param>
        /// <param name="parameterObject">����</param>
        protected int ExecuteDelete(string statementName, object parameterObject)
        {
            try
            {
                return this._sqlMap.Delete(statementName, parameterObject);
            }
            catch (Exception e)
            {
                throw new DataMapperException("Error executing query '" + statementName + "' for delete.  Cause: " + e.Message, e);
            }
        }

        #endregion

        #region �Զ���

        /// <summary>����Command
        /// ����Command
        /// </summary>
        /// <param name="statementName"></param>
        /// <param name="paramObject"></param>
        /// <returns></returns>
        private IDbCommand GetCommand(IDalSession session, string statementName, object paramObject)
        {
            IStatement statement = this._sqlMap.GetMappedStatement(statementName).Statement;
                 
            IMappedStatement mapStatement = this._sqlMap.GetMappedStatement(statementName);

            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject,session as ISqlMapSession);

            mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);

            IDbCommand cmd = session.CreateCommand(CommandType.Text);
            cmd.CommandText = request.IDbCommand.CommandText;
            //return request.IDbCommand;
            return cmd;


            //return  request.IDbCommand;
        }

        /// <summary>����DataSet
        /// ����DataSet
        /// </summary>
        /// <param name="statementName">����ID</param>
        /// <param name="paramObject">��������Ҫ�IJ���</param>
        /// <returns>�õ���DataTable</returns>
        public DataSet ExecuteQueryForDataSet(string statementName, object paramObject)
        {
            DataSet ds = new DataSet();

            bool isSessionLocal = false;
            
            IDalSession session = _sqlMap.LocalSession;

            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);

                session.OpenConnection();

                isSessionLocal = true;
            }

            IDbCommand cmd = this.GetCommand(session, statementName, paramObject);

            //IDbCommand cmd = session.CreateCommand(CommandType.Text);
            //cmd.CommandText = paramObject.ToString();
            

            cmd.CommandTimeout = 5000;
            try
            {
                cmd.Connection = session.Connection;
                
                IDbDataAdapter adapter = session.CreateDataAdapter(cmd);

                adapter.Fill(ds);
            }
            catch(Exception ex)
            {
            }
            finally
            {
                if (isSessionLocal)
                {
                    session.CloseConnection();
                }
            }

            return ds;
        }

        /// <summary>����DataTable
        /// ����DataTable
        /// </summary>
        /// <param name="statementName">����ID</param>
        /// <param name="paramObject">��������Ҫ�IJ���</param>
        /// <returns>�õ���DataTable</returns>
        public DataTable ExecuteQueryForDataTable(string statementName, object paramObject)
        {
            DataTable dtResult = new DataTable();

            try
            {
                dtResult = this.ExecuteQueryForDataSet(statementName, paramObject).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtResult;
        }

        /// <summary>
        /// ͨ������SQL���в�ѯ���������ݼ�
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSQL)
        {
            DataSet dtResult = new DataSet();

            try
            {
                dtResult = this.ExecuteQueryForDataSet("dynamicSQL", strSQL);
            }
            catch
            {
            }

            return dtResult;
        }

        /// <summary>
        /// ͨ������SQL���в�ѯ���������ݼ�
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string strSQL)
        {
            DataTable dtResult = new DataTable();

            try
            {
                dtResult = this.ExecuteQueryForDataTable("dynamicSQL", strSQL);
            }
            catch
            {
            }

            return dtResult;
        }

        /// <summary>����DataSet
        /// ����DataSet
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="statementName"></param>
        /// <param name="paramObject"></param>
        /// <returns></returns>
        public int SaveDataSet(DataSet ds, string statementName, object paramObject)
        {
            int iResult = 0;

            bool isSessionLocal = false;


            ISqlMapSession session = this._sqlMap.LocalSession;

            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);

                session.OpenConnection();

                isSessionLocal = true;
            }

            IDbCommand cmd = GetCommand(session, statementName, paramObject);
            // IDbCommand cmd = session.CreateCommand(CommandType.Text);

            try
            {
                cmd.Connection = session.Connection;

                IDbDataAdapter adapter = session.CreateDataAdapter(cmd);

                ConstructorInfo cif = _sqlMap.DataSource.DbProvider.CommandBuilderType.GetConstructor(new Type[] { adapter.GetType() });

                cif.Invoke(new object[] { adapter });


                //foreach (DataRow row in ds.Tables[0].Rows)
                //{

                //}

                iResult = adapter.Update(ds);

                //  cmd.Cancel();
            }
            finally
            {
                if (isSessionLocal)
                {
                    session.CloseConnection();
                }
            }

            return iResult;
        }

        /// <summary>����DataTable
        /// ����DataTable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public int SaveDataTable(DataTable dt, string tablename)
        {
            int iResult;

            DataSet ds;

            if (null == dt.DataSet)
            {
                ds = new DataSet();

                ds.Tables.Add(dt);
            }
            else
            {
                ds = dt.DataSet;
            }

            string tmpTableName = dt.TableName;

            dt.TableName = "Table";

            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    if (DataRowState.Added == dt.Rows[i].RowState)
            //    {
            //        //dt.Rows[i][0] = this.GetPrimaryID(tablename, dt.Columns[0].ColumnName);
            //    }
            //}
            string sStament = "dynamicSQL";

            string paramObject = " select * from " + tablename;

            iResult = this.SaveDataSet(ds, sStament, paramObject);

            dt.TableName = tmpTableName;

            ds.Tables.Remove(dt);

            return iResult;
        }

        /// <summary>ִ���޽�������
        /// ִ���޽�������
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="statementName"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SQL, string statementName)
        {
            int iResult = 0;

            bool isSessionLocal = false;

            IDalSession session = _sqlMap.LocalSession;

            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);

                session.OpenConnection();

                isSessionLocal = true;
            }

            IDbCommand cmd = session.CreateCommand(CommandType.Text);

            try
            {
                cmd.CommandText = SQL;

                iResult = cmd.ExecuteNonQuery();
            }
            finally
            {
                if (isSessionLocal)
                {
                    session.CloseConnection();
                }
            }

            return iResult;
        }

        public int GetPrimaryID(string tableName, string fieldName)
        {
            try
            {
                Hashtable ht = new Hashtable();

                ht.Add("TABLENAME", tableName);

                ht.Add("FIELDNAME", fieldName);

                ht.Add("NEXTID", 0);

                _sqlMap.QueryForList("GetTableID", ht);

                return (int)ht["NEXTID"];
            }
            catch (Exception e)
            {
                throw (e);
                //System.Threading.Thread.Sleep(3000);
                //return this.GetPrimaryID(tableName, fieldName);
            }
        }

        public int GetPrimaryID(string tableName)
        {

            try
            {
                string sSQL = string.Format(string.Format("select {0}_SEQ.nextval from dual", tableName));

                return Convert.ToInt32(this.ExecuteQueryForDataTable("dynamicSQL", sSQL).Rows[0][0]);
            }

            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>��ʼ��ϵͳ
        /// ��ʼ��ϵͳ
        /// </summary>
        public void InitSystem()
        {
            try
            {
                _sqlMap.QueryForList("InitSystem", null);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        #endregion

        #region ��������

        public void BeginTransaction()
        {
            this._sqlMap.BeginTransaction();
        }

        public void BeginTransaction(bool bTrans)
        {
            if (!bTrans)
            {
                return;
            }

            this.BeginTransaction();
        }

        public void CommitTransaction()
        {
            this._sqlMap.CommitTransaction();
        }

        public void CommitTransaction(bool bTrans)
        {
            if (!bTrans)
            {
                return;
            }

            this.CommitTransaction();
        }

        public void RollBackTransaction()
        {
            this._sqlMap.RollBackTransaction();
        }

        public void RollBackTransaction(bool bTrans)
        {
            if (!bTrans)
            {
                return;
            }

            this.RollBackTransaction();
        }

        #endregion

        #region Silverlight��Ҫ�Ļ�������
        /// <summary>
        /// ִ��������ؽ�����
        /// </summary>
        /// <param name="cmd">����</param>
        /// <returns>������</returns>
        public int ExecuteNonQuery(string command)
        {
            IDalSession session = _sqlMap.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);
                session.OpenConnection();
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.CommandText = command;
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                return -1;
            }
            finally
            {
                session.CloseConnection();
            }
        }
        /// <summary>
        /// ִ���������������������
        /// </summary>
        /// <param name="command">����</param>
        /// <returns>����</returns>
        public object ExecuteScalar(string command)
        {
            int result = 0;
            IDalSession session = _sqlMap.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);
                session.OpenConnection();
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.CommandText = command;
                return cmd.ExecuteScalar();
            }
            catch
            {
                return result;
            }
            finally
            {
                session.CloseConnection();
            }
        }
        /// <summary>
        /// ִ��������ؽ���
        /// </summary>
        /// <param name="command">����</param>
        /// <returns>����</returns>
        public Dictionary<string, string> GetEntityAsDictionary(string command)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            IDalSession session = _sqlMap.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);
                session.OpenConnection();
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.CommandText = command;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    string[] fieldName = new string[dr.FieldCount];
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        fieldName[i] = dr.GetName(i);
                    }
                    dr.Read();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        result.Add(fieldName[i], Convert.ToString(dr[i]));
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
            finally
            {
                session.CloseConnection();
            }
        }
        /// <summary>
        /// ִ��������ؽ�����
        /// </summary>
        /// <param name="command">����</param>
        /// <returns>������</returns>
        public List<string[]> GetEntitiesAsStringList(string command)
        {
            List<string[]> result = new List<string[]>();
            IDalSession session = _sqlMap.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);
                session.OpenConnection();
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.CommandText = command;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string[] s = new string[dr.FieldCount];
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            s[i] = Convert.ToString(dr[i]);
                        }
                        result.Add(s);
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
            finally
            {
                session.CloseConnection();
            }
        }
        /// <summary>
        /// ִ��������ؽ�����
        /// </summary>
        /// <param name="command">����</param>
        /// <returns>������</returns>
        public List<Dictionary<string, string>> GetEntitiesAsDictionaryList(string command)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
            IDalSession session = _sqlMap.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(_sqlMap);
                session.OpenConnection();
            }
            try
            {
                IDbCommand cmd = session.CreateCommand(CommandType.Text);
                cmd.CommandText = command;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    string[] fieldName = new string[dr.FieldCount];
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        fieldName[i] = dr.GetName(i);
                    }
                    while (dr.Read())
                    {
                        Dictionary<string, string> row = new Dictionary<string, string>();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            row.Add(fieldName[i], Convert.ToString(dr[i]));
                        }
                        result.Add(row);
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
            finally
            {
                session.CloseConnection();
            }
        }
        #endregion
    }
}