Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version
Go to top

Using SQL Server Metadata and Statistics to Build a Table Explorer Application

, 1 Oct 2012
Develops an analogy between database table and file directory information and argues that it could be very useful if it were possible to have a similar application to Windows Explorer for database tables. Demonstrates how to obtain the information from Sql Server system tables and dynamic management
using System;
using System.Collections;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace DbView
{
	/// <summary>
	/// Summary description for Model.
	/// </summary>   
	public class Model
	{
		const String XML_CONFIG_FILE = @"DbViewData.xml";
		const String TEMPLATE_FILE = @"DbViewTemplates.txt";

		private MainForm m_AppForm;
		private String m_AppPath;
        private String m_ConnectionString;
		private String m_NamePart;
        private String m_SourceSearchString;
        private FileSystemWatcher ConfigFileWatcher;
        private ConnectionListMgr m_ConnectionMgr;
        //private ViewLinker m_View_Linker;
        private ArrayList m_DbViewCollection;
//        private Legacy_Opt   m_config;
        private DbViewBase m_DbView;
//        private Favourites m_Favourites;
        
		public String ConnectionString
		{
		    get { return m_ConnectionString; }
		    set { m_ConnectionString = value; }
		}

		public ArrayList DbViewCollection
		{   
			get { return m_DbViewCollection; }
		}
        
        public DbViewBase DbView
        {
            get { return m_DbView; }
            set { m_DbView = value; }
        }
        
        public String NamePart
        {
            get { return m_NamePart; }
            set { m_NamePart = value; }
        }
	    
        public String SourceSearchString
        {
            get { return m_SourceSearchString; }
            set { m_SourceSearchString = value; }
        }
	    
        public ConnectionListMgr ConnectionList
        {
            get { return m_ConnectionMgr; }
        }
 
		//public ViewLinker View_LInker
		//{
		//    get { return m_View_Linker; }
		//}

		//public Favourites FavouriteManager
		//{
		//    get { return m_Favourites; }
		//}


		public String XmlConfigFile
		{
			get { return m_AppPath + @"\" + XML_CONFIG_FILE; }
		}

		public String TemplateFile
		{
			get { return m_AppPath + @"\" + TEMPLATE_FILE; }
		}

		public Model(String AppPath, MainForm AppForm)
		{
            // replace above eventually with 
            //String configFile = System.Windows.Forms.Application.StartupPath+@"\"+CONFIG_FILE;
			m_AppForm = AppForm;
            m_AppPath = AppPath;
            m_ConnectionString = "";
            m_SourceSearchString = "";
			m_ConnectionMgr = new ConnectionListMgr("");
			// Legacy data store hopefully not for much longer
			//Legacy_LoadAllConfigFromFile();
			// new load xml config
			LoadAllConfigFromFile();
			InitialiseWatcher(); // for multi-instance use
		}

		void InitialiseWatcher()
		{
			// observe other instances updating connections, views etc.
			this.ConfigFileWatcher = new System.IO.FileSystemWatcher();
			this.ConfigFileWatcher.Path = Path.GetDirectoryName(XmlConfigFile);
			this.ConfigFileWatcher.Filter = Path.GetFileName(XmlConfigFile);
			this.ConfigFileWatcher.NotifyFilter = System.IO.NotifyFilters.CreationTime;
			this.ConfigFileWatcher.Changed += new System.IO.FileSystemEventHandler(this.ConfigFileWatcher_Changed);
			this.ConfigFileWatcher.EnableRaisingEvents = true;
		}


		public void LoadAllConfigFromFile()
        {
			String operation = "";
            try
            {
				operation = "Load new config";
				ConfigDoc.Instance.LoadConfig(XmlConfigFile);
				// transition code
				Legacy_Port_Config(); // (if necessary)
				
				operation = "Load connections";
				LoadConnections();
				operation = "Init Views";
				//InitViewObjectCollection(m_AppForm);
				operation = "Init Favourites";
//				InitFavourites();
			}
			catch (Exception exc)
			{
				System.Diagnostics.Debug.WriteLine(String.Format("Reload error: {0}\n {1}", operation, exc.Message));
			}
        }


		public void LoadConnections()
		{
			// then overwrite.
			m_ConnectionMgr.Load();
		}
		
		public void SaveConfig()
        {
            // turn off watcher
            ConfigFileWatcher.EnableRaisingEvents = false;
            // save the new one
            ConfigDoc.Instance.SaveConfig(XmlConfigFile);
            // turn it on
            ConfigFileWatcher.EnableRaisingEvents = true;
        }

        public bool ViewExists(ViewParams tgt, String connTag)
        {
            String t = tgt.ToString();
            foreach (DbViewBase vb in m_DbViewCollection)
            {
                String s = vb.ViewParams.ToString();
                if (s.CompareTo(t) == 0)
					if (vb.ViewParams.Connection == connTag)
						return true;
            }
            return false;
        }
        
        public String CheckChangeDuplicateName(String targetName)
        {
            int  iEx = 0;   // name collision counter
            String newName;
            while (true)    // testing for version 1,2,3,4 etc.
            {
                bool bUnique = true;   // normally this will be true
                
                // generate a target 
                if (iEx == 0)              // pass 0
                    newName = targetName;  // check original name
                else                       // else synthesize a new name 
                    newName = String.Format("{0} ({1})", targetName, iEx);
                    
                // search for the name amongst the current list
                foreach (DbViewBase dbv in m_DbViewCollection)
                {
                    if (dbv.ViewParams.Name.CompareTo(newName) == 0)
                        bUnique = false;
                }
                if (!bUnique)
                    // increment the collision counter which will generate a new name
                    ++iEx;                    
                else
                    // normal case: no match so we are done
                    break;    
            }
            return newName;
        }

		//// called only on first load.
		//private void InitViewObjectCollection(MainForm AppForm)
		//{
		//    m_DbViewCollection = new ArrayList();
		//    m_View_Linker = new ViewLinker();
			
		//    String xpath = String.Format("//{0}/{1}", ConfigDoc.CFGELM_VIEWDEFS, ConfigDoc.CFGELM_VIEWDEF);
		//    XmlNodeList ViewDefNodes = ConfigDoc.Instance.SelectNodes(xpath);
		//    foreach (XmlNode viewNode in ViewDefNodes)
		//    {
		//        ViewParams vp = new ViewParams(viewNode);
		//        DbViewBase vb;
		//        vb = new DbConfigurableView(vp, AppForm);
		//        m_DbViewCollection.Add(vb);
		//    }
			
		//    //m_config.get_group_lines("VIEWLINKS", out lines);
		//    m_View_Linker.Load(null);

		//}



		//private void InitFavourites()
		//{
		//    m_Favourites = new Favourites();
		//    m_Favourites.Load(null);
		//}
        
        public void SaveViewInfo()
        {
			//ViewParamPersistor vpp = new ViewParamPersistor();

			//foreach (DbViewBase dbv in m_DbViewCollection)
			//{
			//    // odd case where a view name is changed in another instance
			//    if (dbv != null)
			//    {
			//        vpp.Add(dbv.ViewParams);
			//        dbv.ViewParams.RefreshNode();
			//        dbv.ViewParams.MoveToEnd();
			//    }
			//}
			//// Somehow remove view links that are not wanted
			//m_View_Linker.Save();
			//this.SaveConfig();
        }

        private void ConfigFileWatcher_Changed(object sender, System.IO.FileSystemEventArgs e)
        {
System.Diagnostics.Debug.WriteLine(String.Format("Config Change {0}", DateTime.Now));
			// put this in a thread.
			// just introduce a delay
			System.Random r = new Random();
			int delay = (int)(2000.0 * r.NextDouble());
			System.Threading.Thread.Sleep(500 + delay);
System.Diagnostics.Debug.WriteLine(String.Format("about to {0}", DateTime.Now));
			this.LoadAllConfigFromFile();
System.Diagnostics.Debug.WriteLine(String.Format("done it {0}", DateTime.Now));
		}
		
		/////////////////////////////////////////////////////////////////////////
		// legacy file save. Destroy in 2012!
		/////////////////////////////////////////////////////////////////////////
		//
		// transition code
		void Legacy_Port_Config()
		{
			// port if:
			// there is currently connection info in the manager
			// there is *NO* info in the new xml config 
			if (ConfigDoc.Instance.Connections.ChildNodes.Count > 0) return; // NO. something in new config
			if (this.m_ConnectionMgr.Count == 0) return; // NO. Nothing in legacy file
			// OK. some old data and no new data
			this.m_ConnectionMgr.Save();
			this.SaveViewInfo();
			ConfigDoc.Instance.SaveConfig(XmlConfigFile);
		}


		const String LEGACY_CONFIG_FILE = @"DbView.opt";

		public String Legacy_ConfigFile
		{
			//get { return System.Windows.Forms.Application.StartupPath+@"\"+CONFIG_FILE; }
			get { return m_AppPath + @"\" + LEGACY_CONFIG_FILE; }
		}
		
//        // this only done once. Only wanted once for conversion
//        public void Legacy_LoadAllConfigFromFile()
//        {
//            String operation = "";
//            try
//            {
				
//                operation = "Legacy Load config";
//                //m_config = new Legacy_Opt();
//                //m_config.load(Legacy_ConfigFile);
//                operation = "Legacy Load connections";
//                Legacy_LoadConnections();
//                operation = "Legacy Init Views";
//                Legacy_InitViewObjectCollection(m_AppForm);
//                operation = "Legacy Init Favourites";
////				Legacy_InitFavourites();
//            }
//            catch (Exception exc)
//            {
//                System.Diagnostics.Debug.WriteLine(String.Format("Reload error: {0}\n {1}", operation, exc.Message));
//            }
//        }



		private void Legacy_InitDefaultViewObjectCollection()
		{
			/*  tables moved. Now this is views only
						ViewParams vp = new ViewParams("TABLES", "");
						DbViewBase vb = new DbViewSample();
						m_DbViewCollection.Add(vb);
			*/
		}

		//private void Legacy_InitViewObjectCollection(MainForm AppForm)
		//{
		//    m_View_Linker = new ViewLinker();

		//    ArrayList lines;
		//    m_config.get_group_lines("VIEWLINKS", out lines);
		//    m_View_Linker.Legacy_Load(lines);

		//    m_DbViewCollection = new ArrayList();
		//    m_config.get_group_lines("VIEWDEFS", out lines);
		//    ViewParamPersistor vpp = new ViewParamPersistor();
		//    vpp.Legacy_Load(lines);
		//    for (int idx = 0; idx < vpp.Count; ++idx)
		//    {
		//        ViewParams vp = vpp.getNth(idx);
		//        // add connection tag for new stuff
		//        vp.Connection = m_View_Linker.find_connection(vp.Name);
		//        DbViewBase vb;
		//        if (!vp.IsTypedClass)
		//            vb = new DbConfigurableView(vp, AppForm);
		//        else
		//        {
		//            Type t = Type.GetType(String.Format("DbView.{0}", vp.Table));
		//            vb = (DbViewBase)Activator.CreateInstance(t);
		//        }
		//        m_DbViewCollection.Add(vb);
		//    }

		//}

		//public void Legacy_LoadConnections()
		//{
		//    ArrayList connection_lines;
		//    m_config.get_group_lines("CONNECTIONS", out connection_lines);
		//    m_ConnectionMgr = new ConnectionListMgr("");
		//    m_ConnectionMgr.Legacy_Load(connection_lines);
		//}

		//private void Legacy_InitFavourites()
		//{
		//    ArrayList lines;
		//    m_config.get_group_lines("FAVOURITES", out lines);
		//    m_Favourites = new Favourites();
		//    m_Favourites.Load(lines);
		//}

		public void Legacy_SaveConnections()
		{
			/* no more saving
			ArrayList connection_lines;
			m_ConnectionMgr.Save(out connection_lines);
			m_config.set_group_lines("CONNECTIONS", connection_lines);
			//String configFile = System.Windows.Forms.Application.StartupPath+@"\"+CONFIG_FILE;
			SaveConfig(Legacy_ConfigFile);
			*/
		}

		public void Legacy_SaveFavouriteInfo()
		{
			/* No more saving
				ArrayList lines;
				this.m_Favourites.Save(out lines);
				m_config.set_group_lines("FAVOURITES", lines);
				SaveConfig(this.configFile);
			 */
		}
		void Legacy_SaveViewInfo()
		{
			/* no more saving
			ViewParamPersistor vpp = new ViewParamPersistor();
			ArrayList lines;
            vpp.Save(out lines);
            m_config.set_group_lines("VIEWDEFS", lines);
            m_View_Linker.Save(out lines);
            m_config.set_group_lines("VIEWLINKS", lines);
            SaveConfig(this.Legacy_ConfigFile);
			*/
		}

  	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DaveDbViewSharp

United Kingdom United Kingdom
No Biography provided

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 1 Oct 2012
Article Copyright 2012 by DaveDbViewSharp
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid