dbrepository.cs

sqlce查询分析器

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;

namespace ScriptGenerator
{
    public class DBRepository : IRepository
    {
        private string _connectionString;
        public DBRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        private delegate void AddToListDelegate<T>(ref List<T> list, SqlCeDataReader dr);
        private void AddToListString(ref List<string> list, SqlCeDataReader dr)
        {
            list.Add(dr.GetString(0));
        }
        private void AddToListColumns(ref List<Column> list, SqlCeDataReader dr)
        {
            list.Add(new Column()
            {
                ColumnName = dr.GetString(0)
                , IsNullable = (YesNoOptionEnum)Enum.Parse(typeof(YesNoOptionEnum), dr.GetString(1))
                , DataType = dr.GetString(2)
                , CharacterMaxLength = (dr.IsDBNull(3) ? 0 : dr.GetInt32(3))
                , NumericPrecision = (dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr[4]))
                , AutoIncrementBy = (dr.IsDBNull(5) ? 0 : Convert.ToInt32(dr[5]))
                , ColumnHasDefault = (dr.IsDBNull(6) ? false : dr.GetBoolean(6))
                , ColumnDefault = (dr.IsDBNull(7) ? string.Empty : dr.GetString(7).Trim())
            });
        }
        private void AddToListConstraints(ref List<Constraint> list, SqlCeDataReader dr)
        {
            list.Add(new Constraint()
            {
                ConstraintTableName = dr.GetString(0)
                , ConstraintName = dr.GetString(1)
                , ColumnName = dr.GetString(2)
                , UniqueConstraintTableName = dr.GetString(3)
                , UniqueConstraintName = dr.GetString(4)
                , UniqueColumnName = dr.GetString(5)
                , OrdinalPosition = dr.GetInt32(6)
            });
        }
        private void AddToListIndexes(ref List<Index> list, SqlCeDataReader dr)
        {
            list.Add(new Index()
            {
                TableName = dr.GetString(0)
                , IndexName = dr.GetString(1)
                , PrimaryKey = dr.GetBoolean(2)
                , Unique = dr.GetBoolean(3)
                , Clustered = dr.GetBoolean(4)
                , OrdinalPosition = dr.GetInt32(5)
                , ColumnName = dr.GetString(6)                
                , SortOrder = (dr.GetInt16(7) == 1 ? SortOrderEnum.Asc : SortOrderEnum.Desc) 
            });

        }

        private List<T> ExecuteReader<T>(string commandText, AddToListDelegate<T> AddToListMethod)
        {
            List<T> list = new List<T>();
            using (SqlCeConnection cn = new SqlCeConnection(_connectionString))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(
                    commandText, cn))
                {
                    using (SqlCeDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                            AddToListMethod(ref list, dr);

                    }
                }
            }
            return list;
        }
        private DataTable ExecuteDataTable(string commandText)
        {
            DataTable dt = new DataTable();
            using (SqlCeConnection cn = new SqlCeConnection(_connectionString))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(
                    commandText, cn))
                {
                    using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }
            }
            return dt;
        }

        #region IRepository Members

        public List<string> GetAllTableNames()
        {
            return ExecuteReader<string>(
                "SELECT table_name FROM information_schema.tables"
                , new AddToListDelegate<string>(AddToListString));
        }
        public List<Column> GetColumnsFromTable(string tableName)
        {
            return ExecuteReader<Column>(
                "SELECT     Column_name, is_nullable, data_type, character_maximum_length, numeric_precision, autoinc_increment, column_hasdefault, column_default " +
                "FROM         information_schema.columns " +
                "WHERE     (table_name = '" + tableName + "') " +
                "ORDER BY ordinal_position ASC "
                , new AddToListDelegate<Column>(AddToListColumns));
        }
        public DataTable GetDataFromTable(string tableName)
        {
            return ExecuteDataTable(string.Format("Select * From [{0}]", tableName));
        }
        public List<string> GetPrimaryKeysFromTable(string tableName)
        {
            return ExecuteReader<string>(
                "SELECT u.COLUMN_NAME " +
                "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN " +
                    "INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME " +
                "where u.TABLE_NAME = '" + tableName + "' and c.CONSTRAINT_TYPE = 'PRIMARY KEY'"
                , new AddToListDelegate<string>(AddToListString));
        }
        public List<Constraint> GetAllForeignKeys()
        {
            //return ExecuteReader<Constraint>(
            //    "SELECT     r.constraint_table_name, r.constraint_name, c.column_name, r.unique_constraint_table_name, r.unique_constraint_name, u.column_name AS unique_column_name " +
            //    "FROM         information_schema.REFERENTIAL_CONSTRAINTS AS r " +
            //    "   INNER JOIN information_schema.key_column_usage AS c ON r.constraint_name = c.constraint_name AND r.constraint_table_name = c.table_name " +
            //    "   INNER JOIN information_schema.key_column_usage AS u ON r.unique_constraint_name = u.constraint_name AND r.unique_constraint_table_name = u.table_name "
            //    , new AddToListDelegate<Constraint>(AddToListConstraints));

            return ExecuteReader<Constraint>(
                "SELECT P.Table_Name AS constraint_table_name,R.Constraint_Name, P.Column_Name AS [Base_Column],'' AS reference_table_name, R.Unique_Constraint_Name AS unique_constraint_name, '' AS [Reference Column], P.Ordinal_Position " +
                "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE P ON R.Constraint_Name = P.Constraint_Name UNION " +
                "SELECT '' AS constraint_table_name, R.Constraint_Name, '' AS [Base_Column], C.Table_Name AS reference_table_name, R.Unique_Constraint_Name AS unique_constraint_name, C.Column_Name AS unique_column_name,C.Ordinal_Position " +
                "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON R.Unique_Constraint_Name = C.Constraint_Name "
                , new AddToListDelegate<Constraint>(AddToListConstraints));
        }
        /// <summary>
        /// Get the query based on http://msdn.microsoft.com/en-us/library/ms174156.aspx
        /// </summary>
        /// <returns></returns>
        public List<Index> GetIndexesFromTable(string tableName)
        {
            return ExecuteReader<Index>(
                "SELECT     TABLE_NAME, INDEX_NAME, PRIMARY_KEY, [UNIQUE], [CLUSTERED], ORDINAL_POSITION, COLUMN_NAME, COLLATION AS SORT_ORDER " + // Weird column name COLLATION FOR SORT_ORDER
                "FROM         Information_Schema.Indexes "+
                "WHERE     (PRIMARY_KEY = 0) " +
                "   AND (TABLE_NAME = '" + tableName + "') " +
                "ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION"
                , new AddToListDelegate<Index>(AddToListIndexes));
        }

        #endregion
    }
}