Other Posts in Database

  1. SQL Helper Class and Jack Thompson
  2. Determining if a Database or Table Exists in SQL Server
  3. Helpful SQL Queries for SQL Server in C#
  4. Simplified Database Creation and Pulling Database Structure in C#

Helpful SQL Queries for SQL Server in C#

3/5/2010

I've been sans a development computer for a little while. I wont go into why it happened, but just know that some individuals need to test their updates before pushing them out (and surprisingly it wasn't Microsoft). Anyway, I've been working on my ORM a bit, trying to add in some features (paging, better creation/updating of tables, etc.). In doing so I figured I would show a couple easy functions that you can run in order to find out the structure of a database:

   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: }

The code has a number of functions that you can use. Most of them should be rather obvious as to what they do (most are checks if a table, database, etc. exist). The big thing that needs to be pointed out is the CheckExists function. In that function, I use the SQLHelper class. That class is from my utility library. It's simply a wrapper for SqlConnection, SqlCommand, etc. Past that the only function that should need explanation is GetListOfColumns. That function returns a list of objects (Pair objects specifically, which is another class from my utility library). The function sets up an SQL query which pulls from a number of built in system views within SQL Server. Specifically it uses the tables, columns, and systypes views, searching on the table name. Past that it stores the returned values in the list. That's all there is to it. Anyway, I know that I showed some of these queries before, but this shows a bit more and an easy way to run them in C#. Anyway, try it out, leave feedback, and happy coding.



Comments