1: /// <summary>
2: /// Creates a database out of the structure it is given
3: /// </summary>
4: /// <param name="Database">Database structure</param>
5: /// <param name="ConnectionString">The connection string to the database's location</param>
6: public static void CreateDatabase(Database Database,string ConnectionString)
7: {
8: try
9: {
10: string Command = BuildCommands(Database);
11: string[] Splitter = { "\n" };
12: string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
13: string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "");
14: using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
15: {
16: try
17: {
18: Helper.Open();
19: Helper.ExecuteNonQuery();
20: }
21: catch { throw; }
22: finally { Helper.Close(); }
23: }
24: for (int x = 1; x < Commands.Length; ++x)
25: {
26: using (SQLHelper Helper = new SQLHelper(Commands[x], ConnectionString, CommandType.Text))
27: {
28: try
29: {
30: Helper.Open();
31: Helper.ExecuteNonQuery();
32: }
33: catch { throw; }
34: finally { Helper.Close(); }
35: }
36: }
37: }
38: catch { throw; }
39: }
40:
41: /// <summary>
42: /// Gets the structure of a database
43: /// </summary>
44: /// <param name="ConnectionString">Connection string</param>
45: /// <returns>The database structure</returns>
46: public static Database GetDatabaseStructure(string ConnectionString)
47: {
48: string DatabaseName = Regex.Match(ConnectionString, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "");
49: Database Temp = new Database(DatabaseName);
50: GetTables(ConnectionString, Temp);
51: SetupTables(ConnectionString, Temp);
52: SetupViews(ConnectionString, Temp);
53: SetupStoredProcedures(ConnectionString, Temp);
54: SetupFunctions(ConnectionString, Temp);
55: return Temp;
56: }
57:
58: /// <summary>
59: /// Builds the list of commands to build the database
60: /// </summary>
61: /// <param name="Database">Database object</param>
62: /// <returns>The commands needed to build the database</returns>
63: private static string BuildCommands(Database Database)
64: {
65: StringBuilder Builder = new StringBuilder();
66: Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE DATABASE ").Append(Database.Name).Append("'\n");
67: foreach (Table Table in Database.Tables)
68: {
69: Builder.Append(GetTableCommand(Table));
70: }
71: foreach (Table Table in Database.Tables)
72: {
73: Builder.Append(GetForeignKeyCommand(Table));
74: }
75: foreach (Function Function in Database.Functions)
76: {
77: Builder.Append(GetFunctionCommand(Function));
78: }
79: foreach (View View in Database.Views)
80: {
81: Builder.Append(GetViewCommand(View));
82: }
83: foreach (StoredProcedure StoredProcedure in Database.StoredProcedures)
84: {
85: Builder.Append(GetStoredProcedure(StoredProcedure));
86: }
87: return Builder.ToString();
88: }
89:
90: /// <summary>
91: /// Gets the foreign keys creation command
92: /// </summary>
93: /// <param name="Table">Table object</param>
94: /// <returns>The string creating the foreign keys</returns>
95: private static string GetForeignKeyCommand(Table Table)
96: {
97: StringBuilder Builder=new StringBuilder();
98: foreach (Column Column in Table.Columns)
99: {
100: if (Column.ForeignKey.Count > 0)
101: {
102: foreach (Column ForeignKey in Column.ForeignKey)
103: {
104: Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ");
105: Builder.Append(Column.ParentTable.Name).Append(" ADD FOREIGN KEY (");
106: Builder.Append(Column.Name).Append(") REFERENCES ").Append(ForeignKey.ParentTable.Name);
107: Builder.Append("(").Append(ForeignKey.Name).Append(")'\n");
108: }
109: }
110: }
111: return Builder.ToString();
112: }
113:
114: /// <summary>
115: /// Gets the stored procedure creation command
116: /// </summary>
117: /// <param name="StoredProcedure">The stored procedure object</param>
118: /// <returns>The string creating the stored procedure</returns>
119: private static string GetStoredProcedure(StoredProcedure StoredProcedure)
120: {
121: return StoredProcedure.Definition.Replace("\n"," ").Replace("\r"," ")+"\n";
122: }
123:
124: /// <summary>
125: /// Gets the view creation command
126: /// </summary>
127: /// <param name="View">The view object</param>
128: /// <returns>The string creating the view</returns>
129: private static string GetViewCommand(View View)
130: {
131: return View.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
132: }
133:
134: /// <summary>
135: /// Gets the function command
136: /// </summary>
137: /// <param name="Function">The function object</param>
138: /// <returns>The string creating the function</returns>
139: private static string GetFunctionCommand(Function Function)
140: {
141: return Function.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
142: }
143:
144: /// <summary>
145: /// Gets the table creation commands
146: /// </summary>
147: /// <param name="Table">Table object</param>
148: /// <returns>The string containing the creation commands</returns>
149: private static string GetTableCommand(Table Table)
150: {
151: StringBuilder Builder=new StringBuilder();
152: Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE TABLE ").Append(Table.Name).Append("(");
153: string Splitter="";
154: foreach (Column Column in Table.Columns)
155: {
156: Builder.Append(Splitter).Append(Column.Name).Append(" ").Append(Column.DataType.ToString());
157: if (Column.DataType == SqlDbType.VarChar || Column.DataType == SqlDbType.NVarChar)
158: {
159: if (Column.Length == -1)
160: {
161: Builder.Append("(MAX)");
162: }
163: else
164: {
165: Builder.Append("(").Append(Column.Length.ToString()).Append(")");
166: }
167: }
168: if (!Column.Nullable)
169: {
170: Builder.Append(" NOT NULL");
171: }
172: if (Column.Unique)
173: {
174: Builder.Append(" UNIQUE");
175: }
176: if (Column.PrimaryKey)
177: {
178: Builder.Append(" PRIMARY KEY");
179: }
180: if (!string.IsNullOrEmpty(Column.Default))
181: {
182: Builder.Append(" DEFAULT ").Append(Column.Default.Replace("(", "").Replace(")", "").Replace("'","''"));
183: }
184: if (Column.AutoIncrement)
185: {
186: Builder.Append(" IDENTITY");
187: }
188: Splitter = ",";
189: }
190: Builder.Append(")'\n");
191: int Counter = 0;
192: foreach (Column Column in Table.Columns)
193: {
194: if (Column.Index&&Column.Unique)
195: {
196: Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE UNIQUE INDEX ");
197: Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
198: Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
199: Builder.Append("'\n");
200: }
201: else if (Column.Index)
202: {
203: Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE INDEX ");
204: Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
205: Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
206: Builder.Append("'\n");
207: }
208: ++Counter;
209: }
210: return Builder.ToString();
211: }
212:
213: /// <summary>
214: /// Sets up the functions
215: /// </summary>
216: /// <param name="ConnectionString">Connection string</param>
217: /// <param name="Temp">Database object</param>
218: private static void SetupFunctions(string ConnectionString, Database Temp)
219: {
220: string Command = "SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'";
221: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
222: {
223: try
224: {
225: Helper.Open();
226: Helper.ExecuteReader();
227: while (Helper.Read())
228: {
229: string Name = (string)Helper.GetParameter("NAME", "");
230: string Definition = (string)Helper.GetParameter("DEFINITION", "");
231: Temp.AddFunction(Name, Definition);
232: }
233: }
234: catch { }
235: finally { Helper.Close(); }
236: }
237: }
238:
239: /// <summary>
240: /// Sets up stored procedures
241: /// </summary>
242: /// <param name="ConnectionString">Connection string</param>
243: /// <param name="Temp">Database object</param>
244: private static void SetupStoredProcedures(string ConnectionString, Database Temp)
245: {
246: string Command = "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures";
247: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
248: {
249: try
250: {
251: Helper.Open();
252: Helper.ExecuteReader();
253: while (Helper.Read())
254: {
255: string ProcedureName = (string)Helper.GetParameter("NAME", "");
256: string Definition = (string)Helper.GetParameter("DEFINITION", "");
257: Temp.AddStoredProcedure(ProcedureName, Definition);
258: }
259: }
260: catch { }
261: finally { Helper.Close(); }
262: }
263: foreach (StoredProcedure Procedure in Temp.StoredProcedures)
264: {
265: Command = "SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@ProcedureName AND (sys.systypes.xusertype <> 256)";
266: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
267: {
268: try
269: {
270: Helper.Open();
271: Helper.AddParameter("@ProcedureName", Procedure.Name, 128);
272: Helper.ExecuteReader();
273: while (Helper.Read())
274: {
275: string Type = (string)Helper.GetParameter("TYPE", "");
276: string Name = (string)Helper.GetParameter("NAME", "");
277: int Length = int.Parse(Helper.GetParameter("LENGTH", 0).ToString());
278: string Default = (string)Helper.GetParameter("DEFAULT VALUE", "");
279: Procedure.AddColumn(Name, Type, Length, Default);
280: }
281: }
282: catch { }
283: finally { Helper.Close(); }
284: }
285: }
286: }
287:
288: /// <summary>
289: /// Sets up the views
290: /// </summary>
291: /// <param name="ConnectionString">Connection string</param>
292: /// <param name="Temp">Database object</param>
293: private static void SetupViews(string ConnectionString, Database Temp)
294: {
295: foreach (View View in Temp.Views)
296: {
297: string Command = "SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE sys.views.name=@ViewName";
298: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
299: {
300: try
301: {
302: Helper.Open();
303: Helper.AddParameter("@ViewName", View.Name, 128);
304: Helper.ExecuteReader();
305: if (Helper.Read())
306: {
307: View.Definition = (string)Helper.GetParameter("Definition", "");
308: }
309: }
310: catch { }
311: finally { Helper.Close(); }
312: }
313: Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE] FROM sys.views INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id WHERE (sys.views.name = @ViewName) AND (sys.systypes.xusertype <> 256)";
314: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
315: {
316: try
317: {
318: Helper.Open();
319: Helper.AddParameter("@ViewName", View.Name, 128);
320: Helper.ExecuteReader();
321: while (Helper.Read())
322: {
323: string ColumnName = (string)Helper.GetParameter("Column", "");
324: string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
325: int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
326: bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
327: View.AddColumn(ColumnName, ColumnType, MaxLength, Nullable);
328: }
329: }
330: catch { }
331: finally { Helper.Close(); }
332: }
333: }
334: }
335:
336: /// <summary>
337: /// Sets up the tables (pulls columns, etc.)
338: /// </summary>
339: /// <param name="ConnectionString">Connection string</param>
340: /// <param name="Temp">Database object</param>
341: private static void SetupTables(string ConnectionString, Database Temp)
342: {
343: foreach (Table Table in Temp.Tables)
344: {
345: string Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE], sys.columns.is_identity as [IS IDENTITY], sys.index_columns.index_id as [IS INDEX], key_constraints.name as [PRIMARY KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN KEY TABLE], columns_1.name as [FOREIGN KEY COLUMN], sys.default_constraints.definition as [DEFAULT VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @TableName) AND (sys.systypes.xusertype <> 256)";
346: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
347: {
348: try
349: {
350: Helper.Open();
351: Helper.AddParameter("@TableName", Table.Name, 128);
352: Helper.ExecuteReader();
353: while (Helper.Read())
354: {
355: string ColumnName = (string)Helper.GetParameter("Column", "");
356: string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
357: int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
358: bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
359: bool Identity = (bool)Helper.GetParameter("IS IDENTITY", false);
360: bool Index = (bool)((int)Helper.GetParameter("IS INDEX", 0) != 0);
361: bool PrimaryKey = string.IsNullOrEmpty((string)Helper.GetParameter("PRIMARY KEY", "")) ? false : true;
362: bool Unique = string.IsNullOrEmpty((string)Helper.GetParameter("UNIQUE", "")) ? false : true;
363: string ForeignKeyTable = (string)Helper.GetParameter("FOREIGN KEY TABLE", "");
364: string ForeignKeyColumn = (string)Helper.GetParameter("FOREIGN KEY COLUMN", "");
365: string DefaultValue = (string)Helper.GetParameter("DEFAULT VALUE", "");
366: if (Table.ContainsColumn(ColumnName))
367: {
368: Table.AddForeignKey(ColumnName, ForeignKeyTable, ForeignKeyColumn);
369: }
370: else
371: {
372: Table.AddColumn(ColumnName, ColumnType, MaxLength, Nullable, Identity, Index, PrimaryKey, Unique, ForeignKeyTable, ForeignKeyColumn, DefaultValue);
373: }
374: }
375: }
376: catch { }
377: finally { Helper.Close(); }
378: }
379: Command = "SELECT sys.triggers.name as Name,sys.trigger_events.type as Type,OBJECT_DEFINITION(sys.triggers.object_id) as Definition FROM sys.triggers INNER JOIN sys.trigger_events ON sys.triggers.object_id=sys.trigger_events.object_id INNER JOIN sys.tables on sys.triggers.parent_id=sys.tables.object_id where sys.tables.name=@TableName";
380: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
381: {
382: try
383: {
384: Helper.Open();
385: Helper.AddParameter("@TableName", Table.Name, 128);
386: Helper.ExecuteReader();
387: while (Helper.Read())
388: {
389: string Name = (string)Helper.GetParameter("Name", "");
390: int Type = (int)Helper.GetParameter("Type", 0);
391: string Definition = (string)Helper.GetParameter("Definition", "");
392: Table.AddTrigger(Name, Definition, Type);
393: }
394: }
395: catch { }
396: finally { Helper.Close(); }
397: }
398: }
399: foreach (Table Table in Temp.Tables)
400: {
401: Table.SetupForeignKeys();
402: }
403: }
404:
405: /// <summary>
406: /// Gets the tables for a database
407: /// </summary>
408: /// <param name="ConnectionString">Connection string</param>
409: /// <param name="Temp">The database object</param>
410: private static void GetTables(string ConnectionString, Database Temp)
411: {
412: string Command = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
413: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
414: {
415: try
416: {
417: Helper.Open();
418: Helper.ExecuteReader();
419: while (Helper.Read())
420: {
421: string TableName = (string)Helper.GetParameter("TABLE_NAME", "");
422: string TableType = (string)Helper.GetParameter("TABLE_TYPE", "");
423: if (TableType == "BASE TABLE")
424: {
425: Temp.AddTable(TableName);
426: }
427: else if (TableType == "VIEW")
428: {
429: Temp.AddView(TableName);
430: }
431: }
432: }
433: catch { }
434: finally { Helper.Close(); }
435: }
436: }
437:
438: /// <summary>
439: /// Checks if something exists
440: /// </summary>
441: /// <param name="Command">Command to run</param>
442: /// <param name="Name">Name of the item</param>
443: /// <param name="ConnectionString">Connection string</param>
444: /// <returns>True if it exists, false otherwise</returns>
445: private static bool CheckExists(string Command, string Name, string ConnectionString)
446: {
447: bool Exists = false;
448: using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
449: {
450: try
451: {
452: Helper.Open();
453: Helper.AddParameter("@Name", Name, 200);
454: Helper.ExecuteReader();
455: if (Helper.Read())
456: Exists = true;
457: }
458: catch { }
459: finally { Helper.Close(); }
460: }
461: return Exists;
462: }