1: /// <summary>

   2: /// Functions helpful for SQL Server

   3: /// </summary>

   4: public static class SQLServer

   5: {

   6:     #region Public Static Functions

   7:  

   8:     /// <summary>

   9:     /// Checks if a database exists

  10:     /// </summary>

  11:     /// <param name="Database">Name of the database</param>

  12:     /// <param name="ConnectionString">Connection string</param>

  13:     /// <returns>True if it exists, false otherwise</returns>

  14:     public static bool DoesDatabaseExist(string Database, string ConnectionString)

  15:     {

  16:         return CheckExists("SELECT * FROM Master.sys.Databases WHERE name=@Name", Database, ConnectionString);

  17:     }

  18:  

  19:     /// <summary>

  20:     /// Checks if a table exists

  21:     /// </summary>

  22:     /// <param name="Table">Table name</param>

  23:     /// <param name="ConnectionString">Connection string</param>

  24:     /// <returns>True if it exists, false otherwise</returns>

  25:     public static bool DoesTableExist(string Table, string ConnectionString)

  26:     {

  27:         return CheckExists("SELECT * FROM sys.Tables WHERE name=@Name", Table, ConnectionString);

  28:     }

  29:  

  30:     /// <summary>

  31:     /// Checks if a view exists

  32:     /// </summary>

  33:     /// <param name="View">View name</param>

  34:     /// <param name="ConnectionString">Connection string</param>

  35:     /// <returns>True if it exists, false otherwise</returns>

  36:     public static bool DoesViewExist(string View, string ConnectionString)

  37:     {

  38:         return CheckExists("SELECT * FROM sys.views WHERE name=@Name", View, ConnectionString);

  39:     }

  40:  

  41:     /// <summary>

  42:     /// Checks if stored procedure exists

  43:     /// </summary>

  44:     /// <param name="StoredProcedure">Stored procedure's name</param>

  45:     /// <param name="ConnectionString">Connection string</param>

  46:     /// <returns>True if it exists, false otherwise</returns>

  47:     public static bool DoesStoredProcedureExist(string StoredProcedure, string ConnectionString)

  48:     {

  49:         return CheckExists("SELECT * FROM sys.Procedures WHERE name=@Name", StoredProcedure, ConnectionString);

  50:     }

  51:  

  52:     /// <summary>

  53:     /// Checks if trigger exists

  54:     /// </summary>

  55:     /// <param name="Trigger">Trigger's name</param>

  56:     /// <param name="ConnectionString">Connection string</param>

  57:     /// <returns>True if it exists, false otherwise</returns>

  58:     public static bool DoesTriggerExist(string Trigger, string ConnectionString)

  59:     {

  60:         return CheckExists("SELECT * FROM sys.triggers WHERE name=@Name", Trigger, ConnectionString);

  61:     }

  62:  

  63:     /// <summary>

  64:     /// Gets a list of columns associated with the table

  65:     /// </summary>

  66:     /// <param name="TableName">Table name</param>

  67:     /// <param name="ConnectionString">Connection string</param>

  68:     /// <returns>A list of column name/type pairs</returns>

  69:     public static System.Collections.Generic.List<Pair<string, string>> GetListOfColumns(string TableName, string ConnectionString)

  70:     {

  71:         System.Collections.Generic.List<Pair<string, string>> Columns = new System.Collections.Generic.List<Pair<string, string>>();

  72:         string Command="SELECT [sys].[columns].name as [Column],[sys].[systypes].name as [ColumnType] FROM [sys].[systypes],[sys].[columns],[sys].[tables] WHERE [sys].[systypes].xtype=[sys].[columns].system_type_id    AND [sys].[columns].object_id=[sys].[tables].object_id    AND [sys].[tables].name=@TableName";

  73:         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))

  74:         {

  75:             try

  76:             {

  77:                 Helper.Open();

  78:                 Helper.AddParameter("@TableName", TableName, 200);

  79:                 Helper.ExecuteReader();

  80:                 while (Helper.Read())

  81:                 {

  82:                     Columns.Add(new Pair<string, string>((string)Helper.GetParameter("Column", ""), (string)Helper.GetParameter("ColumnType", "")));

  83:                 }

  84:             }

  85:             catch { }

  86:             finally { Helper.Close(); }

  87:         }

  88:         return Columns;

  89:     }

  90:  

  91:     #endregion

  92:  

  93:     #region Private Static Functions

  94:  

  95:     /// <summary>

  96:     /// Checks if something exists

  97:     /// </summary>

  98:     /// <param name="Command">Command to run</param>

  99:     /// <param name="Name">Name of the item</param>

 100:     /// <param name="ConnectionString">Connection string</param>

 101:     /// <returns>True if it exists, false otherwise</returns>

 102:     private static bool CheckExists(string Command, string Name, string ConnectionString)

 103:     {

 104:         bool Exists = false;

 105:         using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))

 106:         {

 107:             try

 108:             {

 109:                 Helper.Open();

 110:                 Helper.AddParameter("@Name", Name, 200);

 111:                 Helper.ExecuteReader();

 112:                 if (Helper.Read())

 113:                     Exists = true;

 114:             }

 115:             catch { }

 116:             finally { Helper.Close(); }

 117:         }

 118:         return Exists;

 119:     }

 120:  

 121:     #endregion

 122: }