collecteddataprovider.cs

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

        /// <param name="column">Grid Column to use as a table field.</param>
        /// <param name="tableName">Name of the GridField table.</param>
        private void CreateTableColumn(GridColumn column, string tableName)
        {
            string queryText = "ALTER TABLE [" + tableName + "] ADD [" + column.Name + "] ";

            // Convert the Meta Field Type used on the GridDialog dropdown to Sql Data Type.
            switch (column.GridColumnType)
            {
                case MetaFieldType.Text:
                    queryText += SqlDataTypes.NTEXT;
                    break;
                case MetaFieldType.Number:
                    queryText += SqlDataTypes.FLOAT;
                    break;
                case MetaFieldType.PhoneNumber:
                    queryText += SqlDataTypes.NVARCHAR;
                    break;
                case MetaFieldType.Date:
                    queryText += SqlDataTypes.DATE_TIME;
                    break;
                default:
                    queryText += SqlDataTypes.NVARCHAR;
                    break;
            }
            if (column.IsRequired)
            {
                queryText += " NOT NULL";
            }
            Query query = db.CreateQuery(queryText);
            db.ExecuteNonQuery(query);
        }

        /// <summary>
        /// Creates tables for collected data database
        /// </summary>
        private void CreateTables()
        {
            try
            {
                string[] SqlLine;
                Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

                string txtSQL = ResourceLoader.GetCollectedDataTableScripts();
                SqlLine = regex.Split(txtSQL);


                foreach (string line in SqlLine)
                {
                    if (line.Length > 0)
                    {
                        Query query = db.CreateQuery(line);
                        db.ExecuteNonQuery(query);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not create collected data tables", ex);
            }
            finally
            {

            }
        }

        /// <summary>
        /// Creates a datatable corresponding to a GridField.
        /// </summary>
        /// <param name="view">View of a table that references the original table.</param>
        /// <param name="startingId">Starting Id for new table.</param>
        /// <param name="field">GridField object.</param>
        private void CreateDataTableForGrid(View view, int startingId, GridField field)
        {
            #region Input Validation
            if (view == null)
            {
                throw new ArgumentNullException("view");
            }
            #endregion Input Validation
            string tableName = view.TableName + field.Name;
            Query createQuery = db.CreateQuery("CREATE TABLE [" + tableName +
                "] (UniqueKey INT IDENTITY (" + startingId +
                ",1) PRIMARY KEY NOT NULL, RecStatus SMALLINT NOT NULL Default 1, FKey INT NOT NULL Default 1)");
            try
            {
                db.ExecuteNonQuery(createQuery);
                foreach (GridColumn column in field.Columns)
                {
                    CreateTableColumn(column, tableName);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
        }

        private int SaveGridRecord(View view, int recordID)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                string sTableName = string.Empty;
                foreach (Field field in view.Fields)
                {
                    if (field is GridField)
                    {
                        sTableName = view.TableName + field.Name;
                        sb.Append("INSERT INTO [" + sTableName + "] (");
                        foreach (GridColumn column in ((GridField)field).Columns)
                        {
                            if (!(column is PredefinedColumn))
                            {
                                sb.Append(Util.InsertInSquareBrackets(column.Name));
                                sb.Append(", ");
                            }
                        }
                        sb.Remove(sb.Length - 2, 2);
                        sb.Append(") VALUES (");
                        foreach (GridColumn column in ((GridField)field).Columns)
                        {
                        }
                    }
                }
                Query insertQuery = db.CreateQuery(sb.ToString());

                db.ExecuteNonQuery(insertQuery);

                return (int)db.ExecuteScalar(insertQuery);
            }
            catch (Exception ex)
            {

                throw new GeneralException("Could not insert grid record.", ex);
            }
            finally
            {
            }
        }
        #endregion Private Methods

        #region IDbDriver Members
        /// <summary>
        /// Returns the full name of the data source. Typically used for display purposes
        /// </summary>
        public string FullName
        {
            get
            {
                return db.FullName;
            }
        }

        /// <summary>
        /// Gets or sets the Database name.
        /// </summary>
        public string DbName
        {
            get
            {
                return this.project.CollectedDataDbInfo.DBName;   //db.DbName;
            }
            set
            {
                this.project.CollectedDataDbInfo.DBName = value; // db.DbName = value;
            }
        }

        /// <summary>
        /// Gets the database connection string.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return db.ConnectionString;
            }
            set
            {
                db.ConnectionString = value;
            }
        }

        /// <summary>
        /// Name of the source of the data.
        /// </summary>
        public string DataSource
        {
            get
            {
                return db.DataSource;
            }
        }

        /// <summary>
        /// Gets a user-friendly description of the otherwise bewildering connection string.
        /// </summary>
        public string ConnectionDescription
        {
            get
            {
                return db.ConnectionDescription;
            }
        }

        /// <summary>
        /// Get nonview tables as datatable
        /// </summary>
        /// <returns>All table names of tables that are not metatables</returns>
        public DataTable GetNonViewTablesAsDataTable()
        {
            try
            {
                DataRow dataRow;
                DataTable tables = db.GetTableSchema(); //now GetTableSchema only gets user tables  /zack 1/30/08

                DataTable viewsAndTables = new DataTable("ViewsAndTables");
                viewsAndTables.Columns.Add(ColumnNames.NAME);
                DataRow[] rows = tables.Select("TABLE_NAME not like 'meta%'");
                foreach (DataRow row in rows)
                {
                    string tableName = row["Table_Name"].ToString();

                    //if (!tableName.ToLower().StartsWith("sys"))   //This probably only works with Microsoft database, zack 1/29/08
                    //{
                    dataRow = viewsAndTables.NewRow();
                    dataRow[ColumnNames.NAME] = tableName;
                    viewsAndTables.Rows.Add(dataRow);
                    //}
                }

                return (viewsAndTables);

            }
            catch (Exception ex)
            {
                throw new GeneralException("Could not retrieve Views and Tables.", ex);
            }
        }

        /// <summary>
        /// Gets Primary_Keys schema information about an OLE table as a DataTable class.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>DataTable with schema information.</returns>
        public DataTable GetPrimaryKeysAsDataTable(string tableName)
        {

            DataTable keys = db.GetTableKeysSchema(tableName);
            return keys;
        }

        /// <summary>
        /// Test database connectivity
        /// </summary>
        /// <returns>Returns true if connection can be made successfully</returns>
        public bool TestConnection()
        {
            return db.TestConnection();
        }

        /// <summary>
        /// Creates a table with the given columns
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columns">List of columns</param>
        public void CreateTable(string tableName, List<Epi.Data.TableColumn> columns)
        {
            db.CreateTable(tableName, columns);
        }
        //public Epi.Data.IConnectionStringGui GetConnectionStringGuiForExistingDb()
        //{
        //    //return db.GetConnectionStringGuiForExistingDb();
        //    //never used , remove
        //    return null;
        //}
        //public Epi.Data.IConnectionStringGui GetConnectionStringGuiForNewDb()
        //{
        //    //return db.GetConnectionStringGuiForNewDb();
        //    //remove, never used zack
        //    return null;
        //}        

        /// <summary>
        /// Get schema of DataTable
        /// </summary>
        /// <returns>Represents one table of in-memory data.</returns>
        public System.Data.DataTable GetTableSchema()
        {
            return db.GetTableSchema();
        }

        /// <summary>
        /// Returns the distinct values of a column from a data table.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column</param>
        /// <returns></returns>
        public ArrayList SelectDistinct(string tableName, string columnName)
        {
            string queryString = "SELECT DISTINCT(" + columnName + ") FROM " + tableName;
            Query query = db.CreateQuery(queryString);
            DataTable dt = db.Select(query);
            ArrayList arrayList = new ArrayList(dt.Rows.Count);
            foreach (DataRow dr in dt.Rows)
            {
                arrayList.Add(dr[0]);
            }
            return arrayList;
        }

        /// <summary>
        /// Executes a SQL query to select records into a data table
        /// </summary>
        /// <param name="selectQuery">Container for SQL statement and related parameters</param>
        /// <returns>Represents one table of in-memory data.</returns>
        public System.Data.DataTable Select(Epi.Data.Query selectQuery)
        {
            return db.Select(selectQuery);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="selectQuery">Container for SQL statement and related parameters</param>
        /// <param name="table"></param>
        /// <returns>Represents one table of in-memory data.</returns>
        public System.Data.DataTable Select(Epi.Data.Query selectQuery, System.Data.DataTable table)
        {
            return db.Select(selectQuery, table);
        }