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