using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using Microsoft.SqlServer.Management;

using Microsoft.SqlServer.Management.Common;

using Smo = Microsoft.SqlServer.Management.Smo;

using System.Collections.Generic;

/// <summary>

/// Helper class to make it easier to request objects or collections from the 

/// SQL Server 

/// </summary>

namespace SqlWebAdmin.CSharp

{

    public sealed class SmoHelper

    {

        // make constructor private to keep the class from being instantiated

        private SmoHelper()

        {

        }

		public static Microsoft.SqlServer.Management.Smo.Server CurrentServer

		{

			get

			{

				HttpContext context = HttpContext.Current;

				return context.Cache[String.Format("{0}:CurrentServer", context.Session.SessionID)] as Smo.Server;

			}

			set

			{

				HttpContext context = HttpContext.Current;

				//context.Cache[String.Format("{0}:CurrentServer", context.Session.SessionID)] = value;

				context.Cache.Insert(String.Format("{0}:CurrentServer", context.Session.SessionID), value);

			}

		}



		public static void ResetServer(ref Smo.Server smoServer)

		{

			HttpContext.Current.Trace.Write("SmoHelper", "Resetting SMO Server");

			ServerConnection currentConnection = smoServer.ConnectionContext;

			smoServer = new Microsoft.SqlServer.Management.Smo.Server(currentConnection);

	 

		}



        public static string GetConnectionString()

        {

            string returnValue = null;

            HttpContext context = HttpContext.Current;

            context.Trace.Write("SmoHelper", "End GetConnectionString");

            if (context != null)

            {

                string cryptConnString = (string)context.Profile["ConnectionString"];



                if (cryptConnString == null || cryptConnString.Length < 1)

                    context.Response.Redirect(SiteUrls.Credentials());

                string clearConnString = string.Empty;



                try

                {

                    Encryption crypt = Encryption.Current();

                    returnValue = crypt.Decrypt(cryptConnString);

                }

                catch (Exception ex)

                {

                    HttpContext.Current.Response.Redirect(String.Format("~/credentials.aspx?Error={0}&RU={1}", context.Server.UrlEncode(ex.Message), context.Request["SCRIPT_NAME"]));

                }

            }



            context.Trace.Write("SmoHelper", "End GetConnectionString");

            return returnValue;

        }



        /// <summary>

        /// Method to return an Smo.Server instance based on information in the Session. If the expected

        /// session key is not in the session this will cause an exception to be thrown. Use the <see>GetServer</see>

        /// method that allows you to pass in the server name as a parameter to the method.

        /// </summary>

        /// <returns></returns>

        public static Microsoft.SqlServer.Management.Smo.Server GetServer()

		{

				Smo.Server server = null;

				HttpContext context = HttpContext.Current;

				if (context != null)

				{

                    context.Trace.Write("SmoHelper", "Begin GetServer");

                    if (CurrentServer == null)

					{

                        string clearConnString = GetConnectionString();

						server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(new SqlConnection(clearConnString)));

						try

						{

							// get a build number from ther server, this will ensure that we have connected with the server.  

							int buildNumber = server.ConnectionContext.ServerVersion.BuildNumber;

						}

						catch (ConnectionFailureException sqlex)

						{

							//HttpContext context = HttpContext.Current;

							context.Response.Redirect(String.Format("~/credentials.aspx?Error={0}&RU={1}", context.Server.UrlEncode(sqlex.Message), context.Request["SCRIPT_NAME"]));

							//	context.Response.End();

						}



						CurrentServer = server;

					}

					else

					{

						context.Trace.Write("Cache Hit", "Extracting Connection from user Cache");



						server = CurrentServer;

						if (server.ConnectionContext.IsOpen == false)

							server.ConnectionContext.Connect();



						// The cache here is really a connection cache.  We won't have to pay the price of retrieving and decrypting 

						// the connection string from the profile data store.

						ResetServer(ref server);



					}



                    context.Trace.Write("SmoHelper", "Begin GetServer");			

					return server;

				}

				else

					throw new System.InvalidOperationException("Trying to access an unspecified server");

			

        }

        /// <summary>

        /// 

        /// </summary>

        /// <param name="serverName"></param>

        /// <returns></returns>

        public static Microsoft.SqlServer.Management.Smo.Server GetServer(string serverName, bool useIntegratedSecurity, string username, string password)

        {

            Smo.Server smoServer = new Microsoft.SqlServer.Management.Smo.Server(serverName);

            if (useIntegratedSecurity)

            {

                smoServer.ConnectionContext.LoginSecure = false;

                smoServer.ConnectionContext.Login = username;

                smoServer.ConnectionContext.Password = password;

            }

            return smoServer;

        }

        public static Smo.DatabaseCollection GetDatabases()

        {

            Smo.Server server = GetServer();

            if (server != null)

                return server.Databases;

            else

                return null;

        }



        public static Smo.Database GetDatabase()

        {

            HttpContext context = HttpContext.Current;

            if (context != null)

            {

                string databaseName = context.Request.QueryString[QueryStringKeys.Database];

                if (databaseName != null)

                {

                    Smo.DatabaseCollection databases = GetDatabases();

                    if (databases != null)

                    {

                        if (databases.Contains(databaseName))

                            return databases[databaseName];

                        else

                            throw new Exception(String.Format("Database ({0}) not found.", databaseName));

                    }

                    else

                        throw new InvalidOperationException("No databases found for the server");

                }

                else

                    throw new InvalidOperationException("Could not retrieve a database object without be instructed as to which specific database to access");

            }

            else

                throw new InvalidOperationException("Could not retrieve a database object without be instructed as to which specific database to access");

        }



        public static Smo.TableCollection GetTables()

        {

            HttpContext context = HttpContext.Current;

            Smo.Database db = GetDatabase();

            if (db != null)

            {

                return db.Tables;

            }

            return null;

        }



        public static Smo.Table GetTable()

        {

            HttpContext context = HttpContext.Current;

            context.Trace.Write("SmoHelper", "Begin GetTable");

            Smo.Database db = GetDatabase();

            Smo.Table returnValue = null;

            if (db != null)

            {

                string tableName = context.Request.QueryString[QueryStringKeys.Table];

                string schemaName = context.Request.QueryString[QueryStringKeys.Schema];



                if (String.IsNullOrEmpty(tableName))

                    throw new ArgumentNullException(QueryStringKeys.Table, "Table name was not specified");



                if (db.Tables.Contains(tableName, schemaName))

                {

                    returnValue = db.Tables[tableName, schemaName];

                }

            }



            context.Trace.Write("SmoHelper", "End GetTable");

            // fall through

            return null;

        }



        public static Smo.StoredProcedureCollection GetStoredProcedures()

        {

            HttpContext context = HttpContext.Current;

            Smo.Database db = GetDatabase();

            if (db != null)

            {

                return db.StoredProcedures;

            }

            return null;

        }



        public static Smo.StoredProcedure GetStoredProcedure()

        {

            HttpContext context = HttpContext.Current;

            Smo.Database db = GetDatabase();

            if (db != null)

            {

                string procedureName = context.Request.QueryString[QueryStringKeys.StoredProcedure];

                string schemaName = context.Request.QueryString[QueryStringKeys.Schema];



                if (String.IsNullOrEmpty(procedureName))

                    throw new ArgumentNullException(QueryStringKeys.StoredProcedure, "Stored Procedure name was not specified");



                if (db.StoredProcedures.Contains(procedureName, schemaName))

                {

                    return db.StoredProcedures[procedureName, schemaName];

                }

            }



            // fall through

            return null;

        }



		public static List<Smo.Database> FilterSystemObjects(Smo.DatabaseCollection dbc, bool hideUserObjects)

		{

			List<Smo.Database> dbCollection = new List<Microsoft.SqlServer.Management.Smo.Database>();



			for (int i = 0; i < dbc.Count; i++)

			{

				if (dbc[i].IsSystemObject == hideUserObjects)

					dbCollection.Add(dbc[i]);

			}



			return dbCollection;

		}



		public static List<Smo.Table> FilterSystemObjects(Smo.TableCollection tbc, bool hideUserObjects)

		{

			List<Smo.Table> tbCollection = new List<Microsoft.SqlServer.Management.Smo.Table>();



			for (int i = 0; i < tbc.Count; i++)

			{

				if (tbc[i].IsSystemObject == hideUserObjects)

					tbCollection.Add(tbc[i]);

			}



			return tbCollection;

		}



		public static List<Smo.StoredProcedure> FilterSystemObjects(Smo.StoredProcedureCollection spc, bool hideUserObjects)

		{

			List<Smo.StoredProcedure> spCollection = new List<Microsoft.SqlServer.Management.Smo.StoredProcedure>();



			for (int i = 0; i < spc.Count; i++)

			{

				if (spc[i].IsSystemObject == hideUserObjects)

					spCollection.Add(spc[i]);

			}



			return spCollection;

		}



		public static List<Smo.Database> ListUserDatabases(Smo.DatabaseCollection dbc, bool hideUserObjects)

		{

            HttpContext context = HttpContext.Current;

            context.Trace.Write("SmoHelper", "Start ListUserDatabases");



			List<Smo.Database> dbCollection = new List<Microsoft.SqlServer.Management.Smo.Database>();



            DateTime start = DateTime.Now;

            foreach (Smo.Database db in dbc)

            {

                if (db.IsAccessible)

                    dbCollection.Add(db);

            }



            //for (int i = 0; i < dbc.Count; i++)

            //{

            //    try

            //    {

            //        int cnt = dbc[i].Tables.Count; // force it to check to see if we have access to table.

            //        if(dbc[i].IsSystemObject == hideUserObjects)

            //            dbCollection.Add(dbc[i]);

            //    }

            //    catch(Exception ex)

            //    {

            //        HttpContext.Current.Trace.Warn("Database Access Exception",String.Format("Exception: {0}", ex.Message));

            //    }

            //}



            DateTime end = DateTime.Now;





            context.Trace.Write("SmoHelper", "End ListUserDatabases");

            return dbCollection;

        }

	}

}