/AlterGear.SQLLibrary/SQLList.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.Sql;

using System.Xml.Serialization;



namespace AlterGear.SQLLibrary

{

    [Serializable]

    public class SQLList : IComparable<SQLList>

    {

        [XmlIgnore]

        const string SERVER_NAME = "ServerName";

        [XmlIgnore]

        const string INSTANCE_NAME = "InstanceName";

        [XmlIgnore]

        const string SCHEMA_DATABASES = "Databases";

        [XmlIgnore]

        const string DATABASE_NAME = "Database_name";



        [XmlIgnore]

        const string INDEX_ID = "index_id";

        [XmlIgnore]

        const string OBJECT_ID = "object_id";

        [XmlIgnore]

        const string NAME = "name";

        [XmlIgnore]

        const string AVG_FRAG = "Avg_Fragmentation_In_Percent";

        [XmlIgnore]

        const string TABLE_NAME = "tableName";



        [XmlIgnore]

        const string SQL_INDICES = @"SELECT A.object_id, A.index_id, B.[name], Avg_Fragmentation_In_Percent, C.[name] as tableName 

                                        FROM sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS A 

                                        JOIN sys.indexes B WITH(NOLOCK) ON A.Object_id = B.Object_id 

                                            AND A.Index_id = B.Index_id 

                                        JOIN sys.Tables C WITH(NOLOCK) ON B.Object_id = C.Object_id

                                        WHERE Avg_Fragmentation_In_Percent >= {0}

                                        AND B.[name] IS NOT NULL

                                        order by Avg_Fragmentation_In_Percent desc";

        [XmlIgnore]

        const string SQL_REORGANISE = @"ALTER INDEX [{0}] ON [{1}] REORGANIZE";

        [XmlIgnore]

        const string SQL_REBUILD = @"ALTER INDEX [{0}] ON [{1}] REBUILD";



        [XmlIgnore]

        private System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder;



        

        [XmlAttribute(DataType="string")]

        public string ConnectionString { get; set; }



        public static List<string> GetSqlInstances()

        {

            List<string> lstInstances = new List<string>();



            SqlDataSourceEnumerator sqlEnumerator = SqlDataSourceEnumerator.Instance;

            DataTable dataTable = sqlEnumerator.GetDataSources();



            foreach (DataRow row in dataTable.Rows)

            {

                lstInstances.Add(row[SERVER_NAME] + "\\" + row[INSTANCE_NAME]);

            }

            return lstInstances;

        }



        public static List<string> GetSQLDatabase(string sqlInstance, bool integratedSecurity, string userName, string password)

        {

            List<string> lstDataBases = new List<string>();



            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();

            sqlConnBuilder.DataSource = sqlInstance;

            sqlConnBuilder.IntegratedSecurity = integratedSecurity;

            sqlConnBuilder.UserID = userName;

            sqlConnBuilder.Password = password;

            string connectionString = sqlConnBuilder.ConnectionString;



            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connectionString))

            {

                sqlConn.Open();

                DataTable dt = sqlConn.GetSchema(SCHEMA_DATABASES);

                foreach (DataRow row in dt.Rows)

                {

                    lstDataBases.Add(row[DATABASE_NAME].ToString());

                }

            }

            return lstDataBases;

        }



        public bool CanConnect(string sqlInstance, bool integratedSecurity, string userName, string password, string databaseName, int timeout, bool throwException, out Exception exception, out string connectionString)

        {

            bool canConnect = false;

            connectionString = "";

            exception = null;

            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();

            sqlConnBuilder.DataSource = sqlInstance;

            sqlConnBuilder.IntegratedSecurity = integratedSecurity;

            sqlConnBuilder.UserID = userName;

            sqlConnBuilder.Password = password;

            sqlConnBuilder.InitialCatalog = databaseName;

            sqlConnBuilder.ConnectTimeout = timeout;



            try

            {

                using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(sqlConnBuilder.ConnectionString))

                {

                    sqlConn.Open();

                    connectionString = sqlConnBuilder.ConnectionString;

                    this.ConnectionString = connectionString;     

                    canConnect = true;

                }

            }

            catch (Exception ex)

            {

                exception = ex;

                if(throwException == true)

                    throw ex;

            }



            return canConnect;

        }



        

        /*

        public string SQLServerName()

        {

            sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

            return sqlConnBuilder.DataSource;

        }

        */

        [XmlIgnore]

        public string DisplaySQLConn

        {

            get

            {

                if (!string.IsNullOrEmpty(this.SQLDatabaseName) && !string.IsNullOrEmpty(this.SQLServerName))

                    return this.SQLServerName + " - " + this.SQLDatabaseName;

                else 

                    return "";

            }

            private set { }

        }

        [XmlAttribute]

        public string SQLServerName

        {

            get

            {

                sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

                return sqlConnBuilder.DataSource;

            }

            set

            { }





        }



        [XmlAttribute]

        public string SQLDatabaseName

        {

            get

            {

                sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

                return sqlConnBuilder.InitialCatalog;

            }

            set { }

        } 

        /*

        public string SQLDatabaseName()

        {

            sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

            return sqlConnBuilder.InitialCatalog;

        }

        */

        

        public System.Data.SqlClient.SqlConnectionStringBuilder GetSqlConnectionStringBuilder()

        {

            return new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);

        }



        public List<IndexInfo> GetIndices()

        {

            return this.GetIndices(0);

        }



        public List<IndexInfo> GetIndices(int fragPct)

        {

            List<IndexInfo> lstIndexInfo = new List<IndexInfo>();

            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))

            {

                sqlConn.Open();

                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();

                sqlCommand.CommandText = string.Format(SQL_INDICES, fragPct);

                System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                while (sqlDataReader.Read())

                {

                    string name = sqlDataReader.GetString(sqlDataReader.GetOrdinal(NAME));

                    string tableName = sqlDataReader.GetString(sqlDataReader.GetOrdinal(TABLE_NAME));

                    int indexId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal(INDEX_ID));

                    int objectId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal(OBJECT_ID));

                    double avgFrag = sqlDataReader.GetDouble(sqlDataReader.GetOrdinal(AVG_FRAG));

                    lstIndexInfo.Add(new IndexInfo(objectId, tableName, indexId, name, (decimal)avgFrag));

                }

            }

            return lstIndexInfo;

        }



        public void ReorganiseIndex(IndexInfo indexInfo)

        {

            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);



            string reorganise = string.Format(SQL_REORGANISE, indexInfo.Name, indexInfo.TableName);



            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))

            {

                sqlConn.Open();

                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();

                sqlCommand.CommandText = reorganise;

                sqlCommand.ExecuteNonQuery();

            }

        }



        public void ReBuildIndex(IndexInfo indexInfo)

        {

            System.Data.SqlClient.SqlConnectionStringBuilder sqlConnBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConnectionString);



            string reorganise = string.Format(SQL_REBUILD, indexInfo.Name, indexInfo.TableName);



            using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(this.ConnectionString))

            {

                sqlConn.Open();

                System.Data.SqlClient.SqlCommand sqlCommand = sqlConn.CreateCommand();

                sqlCommand.CommandText = reorganise;

                sqlCommand.ExecuteNonQuery();

            }

        }







        #region IComparable<SQLList> Members



        public int CompareTo(SQLList other)

        {

            if (this.SQLServerName == other.SQLServerName

                && this.SQLDatabaseName == other.SQLDatabaseName)

            {

                return 0;

            }

            else

                return -1;

        }



        #endregion

    }



    

}