sqldbrepository.cs

sqlce查询分析器

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

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

        private delegate void AddToListDelegate<T>(ref List<T> list, SqlDataReader dr);
        private void AddToListString(ref List<string> list, SqlDataReader dr)
        {
            list.Add(dr.GetString(0));
        }
        private void AddToListColumns(ref List<Column> list, SqlDataReader 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]))                
                , ColumnDefault = (dr.IsDBNull(5) ? string.Empty : dr.GetString(5).Trim())
            });
        }
        private void AddToListConstraints(ref List<Constraint> list, SqlDataReader 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, SqlDataReader dr)
        {
            list.Add(new Index()
            {
                TableName = dr.GetString(0)
                , IndexName = dr.GetString(1)
                , PrimaryKey = dr.GetBoolean(2)
                , Unique = dr.GetBoolean(3)
                , Clustered = dr.GetString(4) == "NONCLUSTERED" ? false : true
                //, OrdinalPosition = (int)dr.GetValue(5)
                , ColumnName = dr.GetString(6)                
                , SortOrder = (dr.GetBoolean(7) ? SortOrderEnum.Desc : SortOrderEnum.Asc) 
            });

        }

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

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

        #region IRepository Members

        public List<string> GetAllTableNames()
        {
            return ExecuteReader<string>(
                "SELECT table_name FROM information_schema.tables WHERE Table_TYPE='BASE TABLE' AND table_name NOT LIKE 'sys%'"
                , 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, 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 P.Table_Name AS constraint_table_name, R.Constraint_Name, P.Column_Name, C.Table_Name AS unique_constraint_table_name, R.Unique_Constraint_Name AS unique_constraint_name, C.Column_Name AS unique_column_name " +
            //    "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R " +
            //    "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE P ON R.Constraint_Name = P.Constraint_Name " +
            //    "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON R.Unique_Constraint_Name = C.Constraint_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 O.Name AS Table_name,N.name AS Constraint_name,N.is_primary_key,N.is_unique,N.type_desc,C.key_Ordinal,S.name as Column_name,C.is_descending_key " +
                "FROM sys.index_columns C JOIN sys.indexes N ON C.Object_id = N.Object_Id AND C.index_id = N.index_id JOIN sys.objects O ON N.Object_id = O.Object_id JOIN sys.columns S ON O.Object_id = S.Object_id AND S.Column_id=C.Column_Id " +
                "WHERE     (is_primary_key = 0) " +
                "   AND (O.Name = '" + tableName + "') " +
                "ORDER BY O.Name, N.name, C.key_Ordinal"
                , new AddToListDelegate<Index>(AddToListIndexes));
            
        }

        #endregion
    }
}