collecteddataprovider.cs

EpiInfo 开源的导航系统远程序,不知道在哪里下的了,分享一下,有兴趣的

        #region Select Statements

        /// <summary>
        /// Returns the total number of records from the view's table.
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <returns>Total number of records</returns>
        public int GetRecordCount(View view)
        {
            try
            {
                #region Input Validation
                if (view == null)
                {
                    throw new System.ArgumentNullException("view");
                }
                #endregion Input Validation

                Query query = db.CreateQuery("SELECT COUNT(*) FROM " + Util.InsertInSquareBrackets(view.TableName));
                int count = Int32.Parse((db.ExecuteScalar(query)).ToString());
                return count;
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not retrieve record count.", ex); //TODO: move to shared strings
            }
            finally
            {
            }
        }

        /// <summary>
        /// Returns the first record Id from the view's table.
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <returns>First record Id</returns>
        public int GetFirstRecordId(View view)
        {
            try
            {
                #region Input Validation
                if (view == null)
                {
                    throw new System.ArgumentNullException("view");
                }
                #endregion Input Validation

                Query query = db.CreateQuery(" SELECT MIN([UniqueKey]) FROM " + Util.InsertInSquareBrackets(view.TableName));
                object result = db.ExecuteScalar(query);
                if (result != DBNull.Value)
                {
                    return (int)result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not retrieve record id for the first record.", ex);
            }
        }


        /// <summary>
        /// Returns the last record Id from the view's table.
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <returns>Last record Id</returns>
        public int GetLastRecordId(View view)
        {
            try
            {
                #region Input Validation
                if (view == null)
                {
                    throw new System.ArgumentNullException("view");
                }
                #endregion Input Validation

                Query query = db.CreateQuery(" SELECT MAX([UniqueKey]) FROM " + Util.InsertInSquareBrackets(view.TableName));
                object result = db.ExecuteScalar(query);
                if (result != DBNull.Value)
                {
                    return (int)result;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not retrieve record id for the last record.", ex);
            }
        }

        /// <summary>
        /// Loads a record by it's Id
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <param name="recordId">Id of the record</param>
        public void LoadRecordIntoView(View view, int recordId)
        {
            #region Input Validation
            if (view == null)
            {
                throw new ArgumentOutOfRangeException("view");
            }
            if (recordId < 1)
            {
                throw new ArgumentOutOfRangeException("recordId");
            }
            #endregion Input Validation

            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append(Util.ToString(view.TableColumnNames, StringLiterals.COMMA));
                sb.Append(" FROM ");
                sb.Append(Util.InsertInSquareBrackets(view.TableName));
                sb.Append(" WHERE [Uniquekey] = ");
                sb.Append(recordId.ToString());

                Query selectQuery = db.CreateQuery(sb.ToString());

                DataTable dataTable = db.Select(selectQuery);
                if (dataTable.Rows.Count > 0)
                {
                    DataRow dataRow = dataTable.Rows[0];
                    foreach (IDataField dataField in view.Fields.DataFields)
                    {
                        if (dataField is MirrorField)
                        {
                            MirrorField mirror = (MirrorField)dataField;
                            dataField.CurrentRecordValue = dataRow[mirror.SourceField.Name].ToString();
                        }
                        else
                        {
                            dataField.CurrentRecordValue = dataRow[dataField.Name].ToString();
                        }
                    }
                }
            }
            //			catch (Exception ex)
            //			{
            //				throw new GeneralException("Could not retrieve previous record.", ex);
            //			}
            finally
            {

            }
        }

        /// <summary>
        /// Retrieves Id of the previous record.
        /// Returns 0 if this is the first record.
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <param name="currentRecordId">Id of current record.</param>
        public int GetPreviousRecordId(View view, int currentRecordId)
        {
            #region Input Validation
            if (view == null)
            {
                throw new ArgumentOutOfRangeException("View");
            }
            if (currentRecordId < 1)
            {
                throw new ArgumentOutOfRangeException("Record ID");
            }
            #endregion Input Validation

            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(" SELECT max([UniqueKey]) FROM ");
                sb.Append(Util.InsertInSquareBrackets(view.TableName));
                sb.Append(" WHERE [UniqueKey] < ");
                sb.Append(currentRecordId.ToString());

                Query selectQuery = db.CreateQuery(sb.ToString());

                object result = db.ExecuteScalar(selectQuery);
                if (result != DBNull.Value)
                {
                    return (int)result;
                }
                else
                {
                    return 0;
                }
            }
            finally
            {

            }
        }

        /// <summary>
        /// Returns the Id of the next record.
        /// Returns 0 if this is the last record.
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <param name="currentRecordId">Id of current record.</param>
        /// <returns>Id of next record.</returns>
        public int GetNextRecordId(View view, int currentRecordId)
        {
            #region Input Validation
            if (view == null)
            {
                throw new ArgumentOutOfRangeException("View");
            }
            if (currentRecordId < 1)
            {
                throw new ArgumentOutOfRangeException("Record ID");
            }
            #endregion Input Validation

            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(" SELECT MIN([UniqueKey]) FROM ");
                sb.Append(Util.InsertInSquareBrackets(view.TableName));
                sb.Append(" WHERE [UniqueKey] > ");
                sb.Append(currentRecordId.ToString());

                Query selectQuery = db.CreateQuery(sb.ToString());

                object result = db.ExecuteScalar(selectQuery);
                if (result != DBNull.Value)
                {
                    return (int)result;
                }
                else
                {
                    return 0;
                }
            }
            finally
            {

            }
        }

        /// <summary>
        /// Returns records found for search criteria
        /// </summary>
        /// <param name="view">View of a table.</param>
        /// <param name="searchFields">A collection of search fields</param>
        /// <param name="searchFieldItemTypes"> A collection of the corresponding types of the search field items</param>
        /// <param name="searchFieldValues">A collection values for the search fields</param>
        /// <returns>Records found meeting search criteria</returns>
        //        public DataTable GetSearchRecords(View view, Collection<string> searchFields, Collection<string> searchFieldValues)        
        public DataTable GetSearchRecords(View view, Collection<string> searchFields, Collection<string> searchFieldItemTypes, ArrayList searchFieldValues)
        {
            #region Input Validation
            if (view == null)
            {
                throw new ArgumentOutOfRangeException("View");
            }

            if (searchFields == null)
            {
                throw new ArgumentOutOfRangeException("Search Fields Collections");
            }

            if (searchFieldItemTypes == null)
            {
                throw new ArgumentOutOfRangeException("Search Fields Item Types");
            }

            if (searchFieldValues == null)
            {
                throw new ArgumentOutOfRangeException("Search Fields Values");
            }
            #endregion  //input validation

            try
            {
                Configuration config = Configuration.GetNewInstance();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM " + Util.InsertInSquareBrackets(view.TableName) + " WHERE ");

                for (int i = 0; i <= searchFields.Count - 1; i++)
                {
                    if (searchFieldItemTypes[i].Equals("YesNo"))
                    {
                        if (searchFieldValues[i] == null)
                        {
                            sb.Append(searchFields[i] + " is null");
                        }
                        else
                        {
                            if (searchFieldValues[i].Equals(config.Settings.RepresentationOfYes) || searchFieldValues[i].Equals("1"))
                            {
                                sb.Append(searchFields[i] + " = " + 1 + "");
                            }
                            else if (searchFieldValues[i].Equals(config.Settings.RepresentationOfNo) || searchFieldValues[i].Equals("0"))
                            {
                                sb.Append(searchFields[i] + " = " + 0 + "");
                            }
                        }
                    }
                    else if (searchFieldItemTypes[i].Equals("Number"))
                    //if (searchFieldValues[i].GetType().FullName.Equals("System.Double"))
                    {
                        if (searchFieldValues[i] == null)
                        {
                            sb.Append(searchFields[i] + " is null");
                        }
                        else
                        {
                            double num;
                            bool isNum = Double.TryParse(searchFieldValues[i].ToString(), out num);
                            if (isNum)      //Value is a number
                            {
                                sb.Append(searchFields[i] + " = " + searchFieldValues[i] + "");
                            }
                        }
                    }
                    else if (searchFieldItemTypes[i].Equals("PhoneNumber"))
                    {
                        if (string.IsNullOrEmpty(searchFieldValues[i].ToString()))
                        {
                            sb.Append(searchFields[i] + " is null");
                        }
                        else
                        {
                            sb.Append(searchFields[i] + " = '" + searchFieldValues[i] + "'");
                        }
                    }
                    else
                    {
                        if (searchFieldItemTypes[i].Equals("Date") || searchFieldItemTypes[i].Equals("DateTime") || searchFieldItemTypes[i].Equals("Time"))