PageRenderTime 1330ms CodeModel.GetById 33ms RepoModel.GetById 1ms app.codeStats 1ms

/branches/v41/Source/Widgetsphere.Generators/SqlSchemaToModel.cs

#
C# | 1735 lines | 1291 code | 180 blank | 264 comment | 243 complexity | 8d4309674e9e3f01b3c04ad359dd89ee MD5 | raw file
Possible License(s): JSON, CC-BY-SA-3.0

Large files files are truncated, but you can click here to view the full file

  1. #region Copyright (c) 2006-2012 Widgetsphere LLC, All Rights Reserved
  2. //--------------------------------------------------------------------- *
  3. // Widgetsphere LLC *
  4. // Copyright (c) 2006-2012 All Rights reserved *
  5. // *
  6. // *
  7. //This file and its contents are protected by United States and *
  8. //International copyright laws. Unauthorized reproduction and/or *
  9. //distribution of all or any portion of the code contained herein *
  10. //is strictly prohibited and will result in severe civil and criminal *
  11. //penalties. Any violations of this copyright will be prosecuted *
  12. //to the fullest extent possible under law. *
  13. // *
  14. //THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED *
  15. //TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND *
  16. //TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
  17. //CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY *
  18. //THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC *
  19. // *
  20. //UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN *
  21. //PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR *
  22. //SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT. *
  23. // *
  24. //THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE *
  25. //CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE, *
  26. //INC. THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO *
  27. //INSURE ITS CONFIDENTIALITY. *
  28. // *
  29. //THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT *
  30. //PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN *
  31. //EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING *
  32. //THE PRODUCT. IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE *
  33. //SOURCE CODE CONTAINED HEREIN. *
  34. // *
  35. //THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE. *
  36. //--------------------------------------------------------------------- *
  37. #endregion
  38. using System;
  39. using System.Collections.Generic;
  40. using System.Data;
  41. using System.Data.SqlClient;
  42. using System.Linq;
  43. using System.Text;
  44. using System.Text.RegularExpressions;
  45. using System.Windows.Forms;
  46. using System.Xml;
  47. using Widgetsphere.Generator.Common;
  48. using Widgetsphere.Generator.Common.Logging;
  49. using Widgetsphere.Generator.Common.Util;
  50. using Widgetsphere.Generator.Models;
  51. namespace Widgetsphere.Generator
  52. {
  53. internal enum SqlNativeTypes
  54. {
  55. image = 34,
  56. text = 35,
  57. uniqueidentifier = 36,
  58. date = 40,
  59. time = 41,
  60. datetime2 = 42,
  61. datetimeoffset = 43,
  62. tinyint = 48,
  63. smallint = 52,
  64. @int = 56,
  65. smalldatetime = 58,
  66. real = 59,
  67. money = 60,
  68. datetime = 61,
  69. @float = 62,
  70. sql_variant = 98,
  71. ntext = 99,
  72. bit = 104,
  73. @decimal = 106,
  74. numeric = 108,
  75. smallmoney = 122,
  76. bigint = 127,
  77. varbinary = 165,
  78. varchar = 167,
  79. binary = 173,
  80. @char = 175,
  81. timestamp = 189,
  82. nvarchar = 231,
  83. sysname = 231,
  84. nchar = 239,
  85. hierarchyid = 240,
  86. geometry = 240,
  87. geography = 240,
  88. xml = 241,
  89. }
  90. internal static class SqlSchemaToModel
  91. {
  92. #region Class Members
  93. //private string _connectString;
  94. //private bool _assumeInheritance = true;
  95. #endregion
  96. #region Constructors
  97. //public SqlSchemaToModel(string connectString, bool assumeInheritance)
  98. //{
  99. // this._connectString = connectString;
  100. // _assumeInheritance = assumeInheritance;
  101. //}
  102. #endregion
  103. //#region Events
  104. //public event EventHandler<ProgressEventArgs> Progress;
  105. //protected virtual void OnProgress(ProgressEventArgs e)
  106. //{
  107. // if (this.Progress != null)
  108. // this.Progress(this, e);
  109. //}
  110. //#endregion
  111. ///// <summary>
  112. ///// Load a database schema
  113. ///// </summary>
  114. ///// <param name="project">The project object to load from database</param>
  115. //public bool GetProjectFromSqlSchema(WidgetsphereGeneratorProject project)
  116. //{
  117. // return this.GetProjectFromSqlSchema(project, true);
  118. //}
  119. public static void SetupNewProject(WidgetsphereGeneratorProject project, string connectionString)
  120. {
  121. var root = project.RootController.Object as ModelRoot;
  122. var companyName = GetCompanyName(connectionString);
  123. var projectName = GetProjectName(connectionString);
  124. var databaseName = GetDatabaseName(connectionString);
  125. var databaseCollation = GetDatabaseCollation(connectionString);
  126. if (!string.IsNullOrEmpty(companyName))
  127. root.CompanyName = companyName;
  128. if (!string.IsNullOrEmpty(projectName))
  129. root.ProjectName = projectName;
  130. if (!string.IsNullOrEmpty(databaseName))
  131. root.Database.DatabaseName = databaseName;
  132. root.Version = "0.0.0.0";
  133. root.SQLServerType = GetSQLVersion(connectionString);
  134. root.Database.Columns.Clear();
  135. root.Database.Relations.Clear();
  136. root.Database.Tables.Clear();
  137. }
  138. /// <summary>
  139. /// Load a database schema
  140. /// </summary>
  141. /// <param name="project">The project object to load from database</param>
  142. /// <param name="refreshModel">Determines whether to load all model controllers and other UI components</param>
  143. public static bool GetProjectFromSqlSchema(WidgetsphereGeneratorProject project, string connectionString, bool refreshModel, bool assumeInheritance)
  144. {
  145. try
  146. {
  147. if (!IsValidConnectionString(connectionString))
  148. {
  149. //this.OnProgress(new ProgressEventArgs() { PercentDone = 100 });
  150. GenerationHelper.ShowError("The SQL Server engine could not be found.");
  151. return false;
  152. }
  153. if (!IsSupportedSQLVersion(connectionString))
  154. {
  155. //this.OnProgress(new ProgressEventArgs() { PercentDone = 100 });
  156. GenerationHelper.ShowError("The current version of SQL is not supported.");
  157. return false;
  158. }
  159. var root = project.RootController.Object as ModelRoot;
  160. SetupNewProject(project, connectionString);
  161. var databaseCollation = GetDatabaseCollation(connectionString);
  162. //var root = project.RootController.Object as ModelRoot;
  163. //var companyName = GetCompanyName(connectionString);
  164. //var projectName = GetProjectName(connectionString);
  165. //var databaseName = GetDatabaseName(connectionString);
  166. //var databaseCollation = GetDatabaseCollation(connectionString);
  167. //if (!string.IsNullOrEmpty(companyName))
  168. // root.CompanyName = companyName;
  169. //if (!string.IsNullOrEmpty(projectName))
  170. // root.ProjectName = projectName;
  171. //if (!string.IsNullOrEmpty(databaseName))
  172. // root.Database.DatabaseName = databaseName;
  173. //root.Version = "0.0.0.0";
  174. //root.SQLServerType = GetSQLVersion(connectionString);
  175. //root.Database.Columns.Clear();
  176. //root.Database.Relations.Clear();
  177. //root.Database.Tables.Clear();
  178. //Progress
  179. //this.OnProgress(new ProgressEventArgs() { PercentDone = 0, Text = "Loading..." });
  180. var tableCount = GetTableCount(connectionString);
  181. var tableIndex = 1;
  182. #region Tables
  183. var tableReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlDatabaseTables());
  184. while (tableReader.Read())
  185. {
  186. var startTime1 = DateTime.Now;
  187. var currentTable = root.Database.Tables.Add(tableReader["name"].ToString());
  188. currentTable.DBSchema = tableReader["schema"].ToString();
  189. ////Progress
  190. //this.OnProgress(new ProgressEventArgs()
  191. //{
  192. // PercentDone = (int)(tableIndex * 100.0 / tableCount),
  193. // Text = currentTable.Name
  194. //});
  195. if (!ValidationHelper.ValidDatabaseIdenitifer(currentTable.Name))
  196. {
  197. currentTable.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentTable.Name);
  198. }
  199. //If single field table with identity then mark it immutable
  200. if ((currentTable.Columns.Count == 1) && (currentTable.GetColumns().First().Identity == IdentityTypeConstants.Database))
  201. {
  202. currentTable.Immutable = true;
  203. }
  204. //Default to false when importing
  205. currentTable.AllowCreateAudit = false;
  206. currentTable.AllowModifiedAudit = false;
  207. currentTable.AllowTimestamp = false;
  208. #region Table Attributes
  209. if (CanUseExtendedProperty(connectionString))
  210. {
  211. var tableAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, "SELECT name, value FROM ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + currentTable.Name + "', default, default)");
  212. while (tableAttributeReader.Read())
  213. {
  214. var attribName = string.Empty;
  215. attribName = tableAttributeReader["name"].ToString();
  216. if (attribName.ToLower().Equals("ms_description"))
  217. {
  218. currentTable.Description = tableAttributeReader["value"].ToString();
  219. }
  220. else if (attribName.ToLower().Equals("generated"))
  221. {
  222. currentTable.Generated = bool.Parse(tableAttributeReader["value"].ToString());
  223. }
  224. else if (attribName.ToLower().Equals("associative"))
  225. {
  226. currentTable.AssociativeTable = bool.Parse(tableAttributeReader["value"].ToString());
  227. }
  228. else if (attribName.ToLower().Equals("hasHistory"))
  229. {
  230. currentTable.HasHistory = bool.Parse(tableAttributeReader["value"].ToString());
  231. }
  232. System.Windows.Forms.Application.DoEvents();
  233. }
  234. tableAttributeReader.Close();
  235. }
  236. #endregion
  237. //System.Windows.Forms.Application.DoEvents();
  238. var endTime1 = DateTime.Now;
  239. //System.Diagnostics.Debug.WriteLine("Table '" + currentTable.Name + "': " + endTime1.Subtract(startTime1).TotalMilliseconds.ToString("###,###,###"));
  240. if (currentTable.Name == "sysdiagrams" ||
  241. currentTable.Name == "__nhydrateschema")
  242. {
  243. root.Database.Tables.Remove(currentTable);
  244. }
  245. tableIndex++;
  246. }
  247. tableReader.Close();
  248. #region Columns
  249. var startTime2 = DateTime.Now;
  250. var columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlColumnsForTable());
  251. while (columnReader.Read())
  252. {
  253. var columnName = columnReader["columnName"].ToString();
  254. var tableName = columnReader["tableName"].ToString();
  255. var currentTable = root.Database.Tables.FirstOrDefault(x => x.Name == tableName);
  256. if (currentTable != null)
  257. {
  258. if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedByColumnName) ||
  259. StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.CreatedDateColumnName))
  260. {
  261. currentTable.AllowCreateAudit = true;
  262. }
  263. else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedByColumnName) ||
  264. StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.ModifiedDateColumnName) ||
  265. StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date"))
  266. {
  267. currentTable.AllowModifiedAudit = true;
  268. }
  269. else if (StringHelper.Match(columnName, ((ModelRoot)project.RootController.Object).Database.TimestampColumnName))
  270. {
  271. currentTable.AllowTimestamp = true;
  272. }
  273. else
  274. {
  275. var currentColumn = root.Database.Columns.Add(columnName);
  276. if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name))
  277. {
  278. currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentColumn.Name);
  279. }
  280. currentColumn.ParentTableRef = currentTable.CreateRef();
  281. currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString());
  282. if (bool.Parse(columnReader["isIdentity"].ToString()))
  283. currentColumn.Identity = IdentityTypeConstants.Database;
  284. else
  285. currentColumn.Identity = IdentityTypeConstants.None;
  286. if (columnReader["isPrimaryKey"] != System.DBNull.Value)
  287. currentColumn.PrimaryKey = true;
  288. //currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString());
  289. try
  290. {
  291. //string columnTypename = columnReader["datatype"].ToString();
  292. //if (StringHelper.Match(columnTypename, "numeric", true))
  293. // currentColumn.DataType = SqlDbType.Decimal;
  294. //else
  295. // currentColumn.DataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true);
  296. currentColumn.DataType = DatabaseHelper.GetSQLDataType((SqlNativeTypes)int.Parse(columnReader["xtype"].ToString()));
  297. }
  298. catch { }
  299. var defaultvalue = columnReader["defaultValue"].ToString();
  300. SetupDefault(currentColumn, defaultvalue);
  301. currentColumn.Length = (int)columnReader["length"];
  302. //Decimals are a little different
  303. if (currentColumn.DataType == SqlDbType.Decimal)
  304. {
  305. currentColumn.Length = (byte)columnReader["precision"];
  306. currentColumn.Scale = (int)columnReader["scale"];
  307. }
  308. if (columnReader["collation"] != System.DBNull.Value)
  309. {
  310. if (databaseCollation != (string)columnReader["collation"])
  311. currentColumn.Collate = (string)columnReader["collation"];
  312. }
  313. currentTable.Columns.Add(currentColumn.CreateRef());
  314. } //Create New Column
  315. }
  316. }
  317. columnReader.Close();
  318. var endTime2 = DateTime.Now;
  319. //System.Diagnostics.Debug.WriteLine("Table '" + currentTable.Name + "' Load Columns: " + endTime2.Subtract(startTime2).TotalMilliseconds.ToString("###,###,###"));
  320. if (CanUseExtendedProperty(connectionString))
  321. {
  322. foreach (Table table in root.Database.Tables)
  323. {
  324. foreach (var column in table.GetColumns())
  325. {
  326. var startTime3 = DateTime.Now;
  327. var columnAttributeReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, "SELECT name, value FROM ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + table.Name + "', 'column', '" + column.Name + "')");
  328. while (columnAttributeReader.Read())
  329. {
  330. var attribName = string.Empty;
  331. attribName = columnAttributeReader["name"].ToString();
  332. if (attribName.ToLower().Equals("ms_description"))
  333. {
  334. column.Description = columnAttributeReader["value"].ToString();
  335. }
  336. }
  337. columnAttributeReader.Close();
  338. var endTime3 = DateTime.Now;
  339. //System.Diagnostics.Debug.WriteLine("Time 3: " + endTime3.Subtract(startTime3).TotalMilliseconds.ToString("###,###,###"));
  340. }
  341. }
  342. }
  343. #endregion
  344. #region Columns Extra Info
  345. columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlColumnInfoAuxForTable());
  346. while (columnReader.Read())
  347. {
  348. var columnName = columnReader["columnname"].ToString();
  349. var tableName = columnReader["tablename"].ToString();
  350. var currentTable = root.Database.Tables.First(x => x.Name == tableName);
  351. var currentColumn = root.Database.Columns[columnName];
  352. if (currentColumn != null)
  353. {
  354. currentColumn.ComputedColumn = true;
  355. currentColumn.Formula = columnReader["definition"].ToString();
  356. }
  357. }
  358. columnReader.Close();
  359. #endregion
  360. #region Indexes
  361. var indexReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlIndexesForTable());
  362. while (indexReader.Read())
  363. {
  364. var indexName = indexReader["indexname"].ToString();
  365. var columnName = indexReader["columnname"].ToString();
  366. var tableName = indexReader["tableName"].ToString();
  367. var currentTable = root.Database.Tables.FirstOrDefault(x => x.Name == tableName);
  368. if (currentTable != null)
  369. {
  370. var pk = bool.Parse(indexReader["is_primary_key"].ToString());
  371. var column = currentTable.GetColumns().FirstOrDefault(x => x.Name == columnName);
  372. if (column != null && !pk)
  373. column.IsIndexed = true;
  374. }
  375. }
  376. #endregion
  377. #endregion
  378. #region Relations
  379. var startTimeRelation = DateTime.Now;
  380. LoadRelations(project, assumeInheritance, connectionString);
  381. var endTimeRelation = DateTime.Now;
  382. //System.Diagnostics.Debug.WriteLine("Load Relations: " + endTimeRelation.Subtract(startTimeRelation).TotalMilliseconds.ToString("###,###,###"));
  383. #endregion
  384. #region Views
  385. var startTimeV = DateTime.Now;
  386. LoadViews(project, connectionString);
  387. var endTimeV = DateTime.Now;
  388. //System.Diagnostics.Debug.WriteLine("Load Views: " + endTimeV.Subtract(startTimeV).TotalMilliseconds.ToString("###,###,###"));
  389. #endregion
  390. #region Stored Procedures
  391. var startTimeSP = DateTime.Now;
  392. LoadStoredProcedures(project, connectionString);
  393. var endTimeSP = DateTime.Now;
  394. //System.Diagnostics.Debug.WriteLine("Load Stored Procedures: " + endTimeSP.Subtract(startTimeSP).TotalMilliseconds.ToString("###,###,###"));
  395. #endregion
  396. //Refresh the tree
  397. if (refreshModel)
  398. {
  399. //project.RootController.Node.RefreshDeep();
  400. //Thread t = new Thread(new ThreadStart(project.RootController.Node.RefreshDeep));
  401. //t.Start();
  402. }
  403. //Progress
  404. //this.OnProgress(new ProgressEventArgs() { PercentDone = 100, Text = "Complete" });
  405. return true;
  406. }
  407. catch (Exception ex)
  408. {
  409. throw;
  410. }
  411. finally
  412. {
  413. //Progress
  414. //this.OnProgress(new ProgressEventArgs() { PercentDone = 100, Text = "Complete" });
  415. }
  416. }
  417. public static IEnumerable<string> GetTableListFromDatabase(string connectionString)
  418. {
  419. var retval = new List<string>();
  420. var tableReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlDatabaseTables());
  421. while (tableReader.Read())
  422. {
  423. retval.Add(tableReader["name"].ToString());
  424. }
  425. tableReader.Close();
  426. return retval;
  427. }
  428. public static IEnumerable<Column> GetTableDefinitionFromDatabase(string connectionString, string tableName, ModelRoot root)
  429. {
  430. try
  431. {
  432. var retval = new List<Column>();
  433. //Columns
  434. var connection = DatabaseHelper.GetConnection(connectionString);
  435. var columnReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, GetSqlColumnsForTable(tableName));
  436. while (columnReader.Read())
  437. {
  438. var columnName = columnReader["columnName"].ToString();
  439. if (StringHelper.Match(columnName, root.Database.CreatedByColumnName) || StringHelper.Match(columnName, root.Database.CreatedDateColumnName))
  440. {
  441. //Do Nothing
  442. }
  443. else if (StringHelper.Match(columnName, root.Database.ModifiedByColumnName) || StringHelper.Match(columnName, root.Database.ModifiedDateColumnName) ||
  444. StringHelper.Match(columnName, "updated_by") || StringHelper.Match(columnName, "updated_date"))
  445. {
  446. //Do Nothing
  447. }
  448. else if (StringHelper.Match(columnName, root.Database.TimestampColumnName))
  449. {
  450. //Do Nothing
  451. }
  452. else
  453. {
  454. var currentColumn = new Column(root);
  455. currentColumn.Name = columnName;
  456. if (!ValidationHelper.ValidDatabaseIdenitifer(currentColumn.Name))
  457. {
  458. currentColumn.CodeFacade = ValidationHelper.MakeDatabaseIdentifier(currentColumn.Name);
  459. }
  460. //currentColumn.ParentTableRef = currentTable.CreateRef();
  461. currentColumn.AllowNull = bool.Parse(columnReader["allowNull"].ToString());
  462. if (bool.Parse(columnReader["isIdentity"].ToString()))
  463. currentColumn.Identity = IdentityTypeConstants.Database;
  464. else
  465. currentColumn.Identity = IdentityTypeConstants.None;
  466. if (columnReader["isPrimaryKey"] != System.DBNull.Value)
  467. currentColumn.PrimaryKey = true;
  468. //currentColumn.PrimaryKey = bool.Parse(columnReader["isPrimaryKey"].ToString());
  469. try
  470. {
  471. //string columnTypename = columnReader["datatype"].ToString();
  472. //if (StringHelper.Match(columnTypename, "numeric", true))
  473. // currentColumn.DataType = SqlDbType.Decimal;
  474. //else
  475. // currentColumn.DataType = (SqlDbType)Enum.Parse(typeof(SqlDbType), columnTypename, true);
  476. currentColumn.DataType = DatabaseHelper.GetSQLDataType((SqlNativeTypes)int.Parse(columnReader["xtype"].ToString()));
  477. }
  478. catch { }
  479. var defaultvalue = columnReader["defaultValue"].ToString();
  480. SetupDefault(currentColumn, defaultvalue);
  481. currentColumn.Length = (int)columnReader["length"];
  482. if (CanUseExtendedProperty(connectionString))
  483. {
  484. var columnAttributeReader = (SqlDataReader)DatabaseHelper.ExecuteReader(connection, CommandType.Text, "SELECT name, value FROM ::fn_listextendedproperty (default,'user', 'dbo', 'table', '" + tableName + "', 'column', '" + currentColumn + "')");
  485. while (columnAttributeReader.Read())
  486. {
  487. var attribName = string.Empty;
  488. attribName = columnAttributeReader["name"].ToString();
  489. if (attribName.ToLower().Equals("ms_description"))
  490. {
  491. currentColumn.Description = columnAttributeReader["value"].ToString();
  492. }
  493. }
  494. columnAttributeReader.Close();
  495. }
  496. retval.Add(currentColumn);
  497. } //Create New Column
  498. }
  499. columnReader.Close();
  500. return retval;
  501. }
  502. catch (Exception ex)
  503. {
  504. throw;
  505. }
  506. }
  507. private static void SetupDefault(Column field, string defaultvalue)
  508. {
  509. if (defaultvalue == null) defaultvalue = string.Empty;
  510. //This is some sort of default pointer, we do not handle this.
  511. if (defaultvalue.Contains("create default ["))
  512. defaultvalue = string.Empty;
  513. //Just in case some put 'null' in to the default field
  514. if (field.AllowNull && defaultvalue.ToLower() == "null")
  515. defaultvalue = string.Empty;
  516. if (field.IsNumericType || field.DataType == SqlDbType.Bit || field.IsDateType || field.IsBinaryType)
  517. {
  518. field.Default = defaultvalue.Replace("(", string.Empty).Replace(")", string.Empty); //remove any parens
  519. }
  520. else if (field.DataType == SqlDbType.UniqueIdentifier)
  521. {
  522. if (!string.IsNullOrEmpty(defaultvalue) && defaultvalue.Contains("newid"))
  523. field.Default = "newid";
  524. else
  525. field.Default = defaultvalue.Replace("(", string.Empty).Replace(")", string.Empty).Replace("'", string.Empty); //Format: ('000...0000')
  526. }
  527. else if (field.IsTextType)
  528. {
  529. while (defaultvalue.StartsWith("('")) defaultvalue = defaultvalue.Substring(2, defaultvalue.Length - 2);
  530. while (defaultvalue.EndsWith("')")) defaultvalue = defaultvalue.Substring(0, defaultvalue.Length - 2);
  531. field.Default = defaultvalue;
  532. }
  533. else
  534. field.Default = defaultvalue;
  535. }
  536. #region Private Methods
  537. public static int GetTableCount(string connectionString)
  538. {
  539. var ds = DatabaseHelper.ExecuteDataset(connectionString, "select count(*) from sysobjects where Type = 'U'");
  540. return (int)ds.Tables[0].Rows[0][0];
  541. }
  542. private static void LoadViews(WidgetsphereGeneratorProject project, string connectionString)
  543. {
  544. var root = (ModelRoot)project.RootController.Object;
  545. var dsView = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForViews());
  546. var dsViewColumn = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForViewsColumns());
  547. //Add the Views
  548. if (dsView.Tables.Count > 0)
  549. {
  550. foreach (DataRow rowView in dsView.Tables[0].Rows)
  551. {
  552. var name = (string)rowView["name"];
  553. var sql = (string)rowView["definition"];
  554. var customView = root.Database.CustomViews.FirstOrDefault(x => x.Name == name);
  555. if (customView == null)
  556. {
  557. customView = ((ModelRoot)project.RootController.Object).Database.CustomViews.Add();
  558. customView.Name = name;
  559. var regEx = new Regex(@"CREATE VIEW.*[\r\n]*AS.*[\r\n]*([\s\S\r\n]*)");
  560. var match = regEx.Match(sql);
  561. if (match != null && match.Groups != null && match.Groups.Count == 2)
  562. sql = match.Groups[1].Value;
  563. customView.SQL = sql;
  564. }
  565. }
  566. }
  567. //Add the columns
  568. if (dsViewColumn.Tables.Count > 0)
  569. {
  570. foreach (DataRow rowView in dsViewColumn.Tables[0].Rows)
  571. {
  572. var viewName = (string)rowView["viewname"];
  573. var columnName = (string)rowView["columnname"];
  574. var length = int.Parse(rowView["max_length"].ToString());
  575. var customView = root.Database.CustomViews.FirstOrDefault(x => x.Name == viewName);
  576. if (customView != null)
  577. {
  578. var column = root.Database.CustomViewColumns.Add();
  579. column.Name = columnName;
  580. column.DataType = DatabaseHelper.GetSQLDataType((SqlNativeTypes)int.Parse(rowView["system_type_id"].ToString()));
  581. column.Length = length;
  582. column.Scale = int.Parse(rowView["scale"].ToString());
  583. customView.Columns.Add(column.CreateRef());
  584. column.ParentViewRef = customView.CreateRef();
  585. }
  586. }
  587. }
  588. }
  589. private static void LoadStoredProcedures(WidgetsphereGeneratorProject project, string connectionString)
  590. {
  591. try
  592. {
  593. var root = (ModelRoot)project.RootController.Object;
  594. var dsSP = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForStoredProcedures((project.Model as ModelRoot).StoredProcedurePrefix));
  595. var dsSPColumn = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForStoredProceduresParameters((project.Model as ModelRoot).StoredProcedurePrefix));
  596. //Add the Stored Procedures
  597. foreach (DataRow rowSP in dsSP.Tables[0].Rows)
  598. {
  599. var id = (int)rowSP["id"];
  600. var name = (string)rowSP["name"];
  601. var customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == name);
  602. if (customStoredProcedure == null)
  603. {
  604. customStoredProcedure = ((ModelRoot)project.RootController.Object).Database.CustomStoredProcedures.Add();
  605. customStoredProcedure.Name = name;
  606. customStoredProcedure.SQL = GetSqlForStoredProceduresBody(name, connectionString);
  607. }
  608. }
  609. ////Add the columns
  610. //foreach (DataRow rowSP in dsSPColumn.Tables[0].Rows)
  611. //{
  612. // int id = (int)rowSP["id"];
  613. // string spName = (string)rowSP["name"];
  614. // string name = (string)rowSP["ColName"];
  615. // string typeName = (string)rowSP["ColType"];
  616. // CustomStoredProcedure customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == spName);
  617. // if (customStoredProcedure != null)
  618. // {
  619. // CustomStoredProcedureColumn column = root.Database.CustomStoredProcedureColumns.Add();
  620. // column.Name = name;
  621. // column.DataType = DatabaseHelper.GetSQLDataType(typeName);
  622. // customStoredProcedure.Columns.Add(column.CreateRef());
  623. // column.ParentRef = customStoredProcedure.CreateRef();
  624. // }
  625. //}
  626. //Add the parameters
  627. foreach (DataRow rowSP in dsSPColumn.Tables[0].Rows)
  628. {
  629. var id = (int)rowSP["id"];
  630. var spName = (string)rowSP["name"];
  631. var name = (string)rowSP["ColName"];
  632. var typeName = (string)rowSP["ColType"];
  633. var length = int.Parse(rowSP["length"].ToString());
  634. var customStoredProcedure = root.Database.CustomStoredProcedures.FirstOrDefault(x => x.Name == spName);
  635. if (customStoredProcedure != null)
  636. {
  637. var parameter = root.Database.CustomRetrieveRuleParameters.Add();
  638. parameter.Name = name.Replace("@", string.Empty);
  639. parameter.DataType = DatabaseHelper.GetSQLDataType((SqlNativeTypes)int.Parse(rowSP["xtype"].ToString()));
  640. parameter.Length = length;
  641. customStoredProcedure.Parameters.Add(parameter.CreateRef());
  642. parameter.ParentTableRef = customStoredProcedure.CreateRef();
  643. }
  644. }
  645. }
  646. catch (Exception ex)
  647. {
  648. throw;
  649. }
  650. }
  651. public static void LoadRelations(WidgetsphereGeneratorProject project, bool assumeInheritance, string connectionString)
  652. {
  653. var root = (ModelRoot)project.RootController.Object;
  654. var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, GetSqlForRelationships());
  655. foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows)
  656. {
  657. var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString();
  658. var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"];
  659. var childTableName = (string)rowRelationship["FK_TABLE_NAME"];
  660. var parentTable = root.Database.Tables[parentTableName];
  661. var childTable = root.Database.Tables[childTableName];
  662. if ((parentTable != null) && (childTable != null))
  663. {
  664. Relation relation = null;
  665. var isAdd = false;
  666. if (!root.Database.Relations.Contains(constraintName))
  667. {
  668. var roleName = string.Empty;
  669. relation = root.Database.Relations.Add();
  670. relation.ParentTableRef = parentTable.CreateRef();
  671. relation.ChildTableRef = childTable.CreateRef();
  672. relation.ConstraintName = constraintName;
  673. var search = ("_" + childTable.DatabaseName + "_" + parentTable.DatabaseName).ToLower();
  674. roleName = constraintName.ToLower().Replace(search, string.Empty);
  675. if (roleName.Length >= 3) roleName = roleName.Remove(0, 3);
  676. var v = roleName.ToLower();
  677. if (v != "fk") relation.RoleName = v;
  678. isAdd = true;
  679. }
  680. else
  681. {
  682. relation = root.Database.Relations.GetByName(constraintName);
  683. }
  684. //add the column relationship to the relation
  685. var columnRelationship = new ColumnRelationship(relation.Root);
  686. var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"];
  687. var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"];
  688. var parentColumns = parentTable.GetColumns().ToList();
  689. var childColumns = childTable.GetColumns().ToList();
  690. if (parentColumns.Count(x => x.Name == parentColumnName) == 1 && (childColumns.Count(x => x.Name == childColumnName) == 1))
  691. {
  692. var parentColumn = parentTable.Columns[parentColumnName].Object as Column;
  693. var childColumn = childTable.Columns[childColumnName].Object as Column;
  694. columnRelationship.ParentColumnRef = parentColumn.CreateRef();
  695. columnRelationship.ChildColumnRef = childColumn.CreateRef();
  696. relation.ColumnRelationships.Add(columnRelationship);
  697. //ONLY ADD THIS RELATION IF ALL WENT WELL
  698. if (isAdd)
  699. parentTable.Relationships.Add(relation.CreateRef());
  700. }
  701. else
  702. {
  703. System.Diagnostics.Debug.Write(string.Empty);
  704. }
  705. } //Not Contains constraint
  706. }
  707. //Map parent tables if there is 1-1 relation and PK match
  708. //Make sure we have choosen to assume inheritance
  709. if (assumeInheritance)
  710. {
  711. foreach (Relation relation in root.Database.Relations)
  712. {
  713. if (relation.IsOneToOne)
  714. {
  715. var parentTable = (Table)relation.ParentTableRef.Object;
  716. var childTable = (Table)relation.ChildTableRef.Object;
  717. if (parentTable.PrimaryKeyColumns.Count == childTable.PrimaryKeyColumns.Count)
  718. {
  719. var pkMatch = true;
  720. foreach (var k in parentTable.PrimaryKeyColumns)
  721. {
  722. pkMatch |= (childTable.PrimaryKeyColumns.Count(x => x.Name == k.Name) == 1);
  723. }
  724. if (pkMatch && childTable.CanInherit(parentTable))
  725. {
  726. childTable.ParentTable = parentTable;
  727. }
  728. }
  729. }
  730. }
  731. }
  732. //Check for associative tables
  733. foreach (Relation relation in root.Database.Relations)
  734. {
  735. var parentTable = (Table)relation.ParentTableRef.Object;
  736. var childTable = (Table)relation.ChildTableRef.Object;
  737. //If there are 2 PK in the child table and that is all the columns
  738. //and it is a base table
  739. if ((childTable.PrimaryKeyColumns.Count == 2) &&
  740. (childTable.Columns.Count == 2) &&
  741. childTable.ParentTable == null)
  742. {
  743. //If child table has 2 relations comming in
  744. var allRelations = childTable.GetRelationsWhereChild();
  745. if (allRelations.Count() == 2)
  746. {
  747. //Relation relation2 = allRelations.FirstOrDefault(x => x != relation);
  748. //Table parentTable2 = (Table)relation2.ParentTableRef.Object;
  749. childTable.AssociativeTable = true;
  750. }
  751. }
  752. }
  753. }
  754. public static bool IsValidConnectionString(string connectionString)
  755. {
  756. var valid = false;
  757. var conn = new System.Data.SqlClient.SqlConnection();
  758. try
  759. {
  760. conn.ConnectionString = connectionString;
  761. conn.Open();
  762. valid = true;
  763. }
  764. catch (Exception ex)
  765. {
  766. valid = false;
  767. }
  768. finally
  769. {
  770. conn.Close();
  771. }
  772. return valid;
  773. }
  774. public static bool IsSupportedSQLVersion(string connectionString)
  775. {
  776. var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')");
  777. var version = (string)ds.Tables[0].Rows[0][0];
  778. if (version.StartsWith("10."))
  779. return true;
  780. else if (version.StartsWith("9."))
  781. return true;
  782. else
  783. return false;
  784. }
  785. public static SQLServerTypeConstants GetSQLVersion(string connectionString)
  786. {
  787. var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')");
  788. var version = (string)ds.Tables[0].Rows[0][0];
  789. if (version.StartsWith("10."))
  790. {
  791. var ds2 = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('Edition')");
  792. var version2 = (string)ds2.Tables[0].Rows[0][0];
  793. if (version2 == "SQL Azure")
  794. return SQLServerTypeConstants.SQLAzure;
  795. else
  796. return SQLServerTypeConstants.SQL2008;
  797. }
  798. else
  799. {
  800. return SQLServerTypeConstants.SQL2005;
  801. }
  802. }
  803. private static bool? extendedPropertyEnabled = null;
  804. private static bool CanUseExtendedProperty(string connectionString)
  805. {
  806. if (extendedPropertyEnabled == null)
  807. {
  808. var conn = new System.Data.SqlClient.SqlConnection();
  809. try
  810. {
  811. conn.ConnectionString = connectionString;
  812. conn.Open();
  813. var cmdGetExtProp = new SqlCommand();
  814. cmdGetExtProp.CommandText = "SELECT value FROM ::fn_listextendedproperty('', '', '', '', '', '', '')";
  815. cmdGetExtProp.CommandType = System.Data.CommandType.Text;
  816. cmdGetExtProp.Connection = conn;
  817. cmdGetExtProp.ExecuteNonQuery();
  818. extendedPropertyEnabled = true;
  819. }
  820. catch (Exception ex)
  821. {
  822. extendedPropertyEnabled = false;
  823. }
  824. finally
  825. {
  826. if (conn != null)
  827. conn.Close();
  828. }
  829. }
  830. return extendedPropertyEnabled.Value;
  831. }
  832. public static string GetProjectName(string connectionString)
  833. {
  834. string retval = null;
  835. if (CanUseExtendedProperty(connectionString))
  836. {
  837. var reader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'projectName', NULL, NULL,NULL, NULL,NULL,NULL)");
  838. if (reader.Read()) retval = reader["value"].ToString();
  839. reader.Close();
  840. }
  841. return retval;
  842. }
  843. public static string GetCompanyName(string connectionString)
  844. {
  845. string retval = null;
  846. if (CanUseExtendedProperty(connectionString))
  847. {
  848. try
  849. {
  850. var reader = (SqlDataReader)DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, "SELECT value FROM ::fn_listextendedproperty ( N'companyName',NULL, NULL,NULL, NULL,NULL,NULL)");
  851. if (reader.Read()) retval = reader["value"].ToString();
  852. reader.Close();
  853. }
  854. catch (Exception ex)
  855. {
  856. WSLog.LogError(ex);
  857. throw ex;
  858. }
  859. }
  860. return retval;
  861. }
  862. public static string GetDatabaseName(string connectionString)
  863. {
  864. return (new System.Data.SqlClient.SqlConnection(connectionString)).Database;
  865. }
  866. private static string GetSqlDatabaseTables()
  867. {
  868. var sb = new StringBuilder();
  869. sb.AppendLine("DECLARE @bar varchar(150)");
  870. sb.AppendLine("DECLARE @val varchar(150)");
  871. sb.AppendLine("DECLARE @tab table");
  872. sb.AppendLine("(");
  873. sb.AppendLine("xName varchar(150) NOT NULL,");
  874. sb.AppendLine("xValue varchar(150) NULL,");
  875. sb.AppendLine("xSchema varchar(150) NOT NULL");
  876. sb.AppendLine(")");
  877. sb.AppendLine("INSERT INTO @tab SELECT so.name, null, sc.name [schema] FROM sys.tables so INNER JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name <> 'dtproperties' AND (so.name <> 'sysdiagrams') AND (so.name <> '__nhydrateschema') AND NOT (so.name like '__AUDIT__%')");
  878. //sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab ORDER BY xName ASC)");
  879. //sb.AppendLine("WHILE @bar is not null");
  880. //sb.AppendLine("BEGIN");
  881. //sb.AppendLine("SET @val = (SELECT CONVERT(varchar(150), value) FROM ::fn_listextendedproperty ( N'selectionCriteria', N'user', N'dbo', N'table',@bar,NULL,NULL))");
  882. //sb.AppendLine("UPDATE @tab SET xValue = @val where xName = @bar");
  883. //sb.AppendLine("SET @bar = (SELECT TOP 1 xName FROM @tab where xName > @bar ORDER BY xName ASC)");
  884. //sb.AppendLine("END");
  885. sb.AppendLine("select xName as name, xSchema as [schema], xValue selectionCriteria from @tab WHERE xName <> 'dtproperties' ORDER BY xName");
  886. return sb.ToString();
  887. }
  888. private static string GetSqlColumsDescription(string tableName)
  889. {
  890. var sb = new StringBuilder();
  891. sb.AppendLine("SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,");
  892. sb.AppendLine(" ep.value AS Description");
  893. sb.AppendLine("FROM sys.objects");
  894. sb.AppendLine("INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id");
  895. sb.AppendLine("CROSS APPLY fn_listextendedproperty(default,");
  896. sb.AppendLine(" 'SCHEMA', schema_name(schema_id),");
  897. sb.AppendLine(" 'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep");
  898. sb.AppendLine("WHERE sys.objects.name = '" + tableName + "'");
  899. return sb.ToString();
  900. }
  901. public static string GetDatabaseCollation(string connectionString)
  902. {
  903. System.Data.SqlClient.SqlConnection connection = null;
  904. try
  905. {
  906. var sql = "SELECT DATABASEPROPERTYEX('" + GetDatabaseName(connectionString) + "', 'Collation') SQLCOLLATION";
  907. connection = new System.Data.SqlClient.SqlConnection(connectionString);
  908. var command = new SqlCommand(sql, connection);
  909. connection.Open();
  910. var retval = (string)command.ExecuteScalar();
  911. return retval;
  912. }
  913. catch (Exception ex)
  914. {
  915. throw;
  916. }
  917. finally
  918. {
  919. if (connection != null && connection.State == ConnectionState.Open)
  920. connection.Close();
  921. }
  922. }
  923. private static string GetSqlColumnInfoAuxForTable()
  924. {
  925. var sb = new StringBuilder();
  926. sb.AppendLine("select o.name as tablename, c.name as columnname, c.definition from sys.computed_columns c inner join sys.objects o on c.object_id = o.object_id");
  927. return sb.ToString();
  928. }
  929. private static string GetSqlIndexesForTable()
  930. {
  931. var sb = new StringBuilder();
  932. sb.AppendLine("select t.name as tablename, i.name as indexname, c.name as columnname, i.is_primary_key");
  933. sb.AppendLine("from sys.tables t");
  934. sb.AppendLine("inner join sys.indexes i on i.object_id = t.object_id");
  935. sb.AppendLine("inner join sys.index_columns ic on ic.object_id = t.object_id");
  936. sb.AppendLine("inner join sys.columns c on c.object_id = t.object_id and");
  937. sb.AppendLine("ic.column_id = c.column_id");
  938. //sb.AppendLine("select o.name as tablename, i.name as indexname, i.is_primary_key from sys.objects o inner join sys.indexes i on o.object_id = i.object_id where o.[type] = 'U'");
  939. return sb.ToString();
  940. }
  941. private static string GetSqlColumnsForTable()
  942. {
  943. return GetSqlColumnsForTable(null);
  944. }
  945. private static string GetSqlColumnsForTable(string tableName)
  946. {
  947. var sb = new StringBuilder();
  948. sb.AppendLine(" SELECT ");
  949. sb.AppendLine(" c.ORDINAL_POSITION as colorder,");
  950. sb.AppendLine(" c.TABLE_NAME as tablename,");
  951. sb.AppendLine(" c.COLUMN_NAME as columnname,");
  952. sb.AppendLine("(");
  953. sb.AppendLine("select top 1 c1.name");
  954. sb.AppendLine("from sys.indexes i");
  955. sb.AppendLine("join sysobjects o ON i.object_id = o.id");
  956. sb.AppendLine("join sysobjects pk ON i.name = pk.name");
  957. sb.AppendLine("AND pk.parent_obj = i.object_id");
  958. sb.AppendLine("AND pk.xtype = 'PK'");
  959. sb.AppendLine("join sys.index_columns ik on i.object_id = ik.object_id");
  960. sb.AppendLine("and i.index_id = ik.index_id");
  961. sb.AppendLine("join syscolumns c1 ON ik.object_id = c1.id");
  962. sb.AppendLine("AND ik.column_id = c1.colid");
  963. sb.AppendLine("AND c1.name = c.COLUMN_NAME");
  964. sb.AppendLine("where o.name = c.TABLE_NAME");
  965. sb.AppendLine(") as [isPrimaryKey],");
  966. sb.AppendLine(" case WHEN");
  967. sb.AppendLine(" (");
  968. sb.AppendLine(" SELECT ");
  969. sb.AppendLine(" count(*) ");
  970. sb.AppendLine(" FROM ");
  971. sb.AppendLine(" INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE foreignkeyccu");
  972. sb.AppendLine(" INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS foreignkeytc on foreignkeyccu.CONSTRAINT_NAME = foreignkeytc.CONSTRAINT_NAME AND");
  973. sb.AppendLine(" foreignkeyccu.CONSTRAINT_SCHEMA = foreignkeytc.CONSTRAINT_SCHEMA AND");
  974. sb.AppendLine(" foreignkeytc.CONSTRAINT_TYPE = 'FOREIGN KEY'");
  975. sb.AppendLine(" WHERE");
  976. sb.AppendLine(" foreignkeyccu.TABLE_SCHEMA = c.TABLE_SCHEMA AND");
  977. sb.AppendLine(" foreignkeyccu.TABLE_NAME = c.TABLE_NAME AND");
  978. sb.AppendLine(" foreignkeyccu.COLUMN_NAME = c.COLUMN_NAME ");
  979. sb.AppendLine(" ) > 0 THEN 'true' ELSE 'false' END as isForeignKey,");
  980. sb.AppendLine(" c.DATA_TYPE as datatype,");
  981. sb.AppendLine(" s.xtype,");
  982. sb.AppendLine(" c.numeric_precision AS [precision], c.numeric_scale AS [scale],");
  983. sb.AppendLine(" case when c.CHARACTER_MAXIMUM_LENGTH is null or c.CHARACTER_MAXIMUM_LENGTH > 8000 then s.length else c.CHARACTER_MAXIMUM_LENGTH end as length,");
  984. sb.AppendLine(" case when c.IS_NULLABLE = 'No' then 'false' else 'true' end as allowNull, ");
  985. //sb.AppendLine(" case when c.COLUMN_DEFAULT is null then '' else REPLACE(REPLACE(REPLACE(REPLACE(c.COLUMN_DEFAULT,'(N''',''),')',''),'(',''),'''','') end as defaultValue,");
  986. sb.AppendLine(" case when c.COLUMN_DEFAULT is null then '' else c.COLUMN_DEFAULT end as defaultValue,");
  987. sb.AppendLine(" case when COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'true' else 'false' end as isIdentity,");
  988. sb.AppendLine(" collation");
  989. sb.AppendLine(" FROM ");
  990. sb.AppendLine(" INFORMATION_SCHEMA.COLUMNS c ");
  991. sb.AppendLine(" INNER JOIN systypes s on s.name = c.DATA_TYPE");
  992. if (!string.IsNullOrEmpty(tableName))
  993. sb.AppendLine(" WHERE c.TABLE_NAME = '" + tableName + "'");
  994. sb.AppendLine(" ORDER BY");
  995. sb.AppendLine(" c.TABLE_NAME,");
  996. sb.AppendLine(" c.COLUMN_NAME");
  997. return sb.ToString();
  998. }
  999. private static string GetSqlForRelationships()
  1000. {
  1001. var sb = new StringBuilder();
  1002. sb.AppendLine("SELECT ");
  1003. sb.AppendLine(" KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'");
  1004. sb.AppendLine(" , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'");
  1005. sb.AppendLine(" , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME' ");
  1006. sb.AppendLine(" , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'");
  1007. sb.AppendLine(" , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME' ");
  1008. sb.AppendLine("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC");
  1009. sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1");
  1010. sb.AppendLine("ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG ");
  1011. sb.AppendLine(" AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA");
  1012. sb.AppendLine(" AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
  1013. sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2");
  1014. sb.AppendLine("ON KCU2.CONSTRAINT_CATALOG = ");
  1015. sb.AppendLine("RC.UNIQUE_CONSTRAINT_CATALOG ");
  1016. sb.AppendLine(" AND KCU2.CONSTRAINT_SCHEMA = ");
  1017. sb.AppendLine("RC.UNIQUE_CONSTRAINT_SCHEMA");
  1018. sb.AppendLine(" AND KCU2.CONSTRAINT_NAME = ");
  1019. sb.AppendLine("RC.UNIQUE_CONSTRAINT_NAME");
  1020. sb.AppendLine(" AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION");
  1021. sb.AppendLine("ORDER BY");
  1022. sb.AppendLine(" KCU1.CONSTRAINT_NAME,");
  1023. sb.AppendLine(" KCU1.ORDINAL_POSITION");
  1024. return sb.ToString();
  1025. }
  1026. private static string GetSqlForViews()
  1027. {
  1028. var sb = new StringBuilder();
  1029. sb.AppendLine("select v.name, m.definition from sys.views v inner join sys.sql_modules m on v.object_id = m.object_id");
  1030. return sb.ToString();
  1031. }
  1032. private static string GetSqlForViewsColumns()
  1033. {
  1034. var sb = new StringBuilder();
  1035. sb.AppendLine("select v.name as viewname, c.name as columnname, c.system_type_id, c.max_length, c.precision, c.scale, c.is_nullable from sys.views v inner join sys.columns c on v.object_id = c.object_id order by v.name, c.name");
  1036. return sb.ToString();
  1037. }
  1038. private static string GetSqlForStoredProceduresParameters(string spPrefix)
  1039. {
  1040. var sb = new StringBuilder();
  1041. sb.AppendLine("SELECT dbo.syscolumns.xtype, dbo.sysobjects.name, dbo.sysobjects.id,");
  1042. sb.AppendLine(" dbo.syscolumns.name AS ColName,");
  1043. sb.AppendLine(" dbo.systypes.name AS ColType,");
  1044. sb.AppendLine(" dbo.syscolumns.length");
  1045. sb.AppendLine("FROM dbo.sysobjects INNER JOIN");
  1046. sb.AppendLine(" dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN");
  1047. sb.AppendLine(" dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype");
  1048. sb.AppendLine("WHERE (dbo.sysobjects.category = 0) AND");
  1049. sb.AppendLine(" (dbo.sysobjects.xtype = 'P') AND");
  1050. sb.AppendLine(" NOT (dbo.sysobjects.name LIKE '" + spPrefix + "_%') AND");
  1051. sb.AppendLine(" NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");
  1052. sb.AppendLine(" dbo.systypes.name <> 'sysname' AND");
  1053. sb.AppendLine(" (dbo.sysobjects.uid in (select uid from dbo.sysusers))");
  1054. sb.AppendLine("ORDER BY");
  1055. sb.AppendLine(" dbo.sysobjects.name, dbo.syscolumns.name");
  1056. return sb.ToString();
  1057. }
  1058. private static string GetSqlForStoredProceduresBody(string spName, string connectionString)
  1059. {
  1060. var sb = new StringBuilder();
  1061. var ds = DatabaseHelper.ExecuteDataset(connectionString, "sp_helptext [" + spName + "]");
  1062. if (ds.Tables.Count > 0)
  1063. {
  1064. foreach (DataRow dr in ds.Tables[0].Rows)
  1065. {
  1066. sb.Append((string)dr[0]);
  1067. }
  1068. var arr = sb.ToString().Split('\n');
  1069. sb = new StringBuilder();
  1070. var inBody = false;
  1071. foreach (var lineText in arr)
  1072. {
  1073. if (inBody)
  1074. {
  1075. sb.AppendLine(lineText);
  1076. }
  1077. else if (!inBody && lineText.ToLower() == "as")
  1078. {
  1079. inBody = true;
  1080. }
  1081. }
  1082. }
  1083. return sb.ToString();
  1084. }
  1085. private static string GetSqlForStoredProcedures(string spPrefix)
  1086. {
  1087. var sb = new StringBuilder();
  1088. sb.AppendLine("SELECT dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.sysobjects.name");
  1089. sb.AppendLine("FROM dbo.sysobjects");
  1090. sb.AppendLine("WHERE (dbo.sysobjects.category = 0) AND");
  1091. sb.AppendLine(" (dbo.sysobjects.xtype = 'P') AND");
  1092. sb.AppendLine(" NOT (dbo.sysobjects.name LIKE '" + spPrefix + "_%') AND");
  1093. sb.AppendLine(" NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");
  1094. sb.AppendLine(" (dbo.sysobjects.uid in (select uid from dbo.sysusers))");
  1095. sb.AppendLine("ORDER BY dbo.sysobjects.name");
  1096. return sb.ToString();
  1097. }
  1098. //private static string GetSqlForForeignKeys(string parentTable, string childTable, string constraintName)
  1099. //{
  1100. // var sb = new StringBuilder();
  1101. // sb.Append("DECLARE @parent_table varchar(256) ");
  1102. // sb.Append("DECLARE @child_table varchar(256) ");
  1103. // sb.Append("DECLARE @constraint varchar(256) ");
  1104. // sb.Append("SET @parent_table = '").Append(parentTable).Append("' ");
  1105. // sb.Append("SET @child_table = '").Append(childTable).Append("' ");
  1106. // sb.Append("SET @constraint = '").Append(constraintName).Append("' ");
  1107. // sb.Append("DECLARE @FKeys TABLE (parentTable varchar(100) NOT NULL, childTable varchar(100) NOT NULL, childColumn varchar(100) NOT NULL, constid int NOT NULL, keyno smallint NOT NULL ) ");
  1108. // sb.Append("DECLARE @PKeys TABLE (parentTable varchar(100) NOT NULL, childTable varchar(100) NOT NULL, parentColumn varchar(100) NOT NULL, constid int NOT NULL, keyno smallint NOT NULL ) ");
  1109. // sb.Append("INSERT INTO @FKeys SELECT DISTINCT parent.name parentTable, child.name childTable, ");
  1110. // sb.Append(" syscolumns.name as childColumn, sysforeignkeys.constid, sysforeignkeys.keyno FROM sysforeignkeys ");
  1111. // sb.Append(" inner join sysobjects child on fkeyid = child.id ");
  1112. // sb.Append(" inner join sysobjects parent on rkeyid = parent.id ");
  1113. // sb.Append(" inner join syscolumns on syscolumns.id = sysforeignkeys.fkeyid AND syscolumns.colorder = sysforeignkeys.fkey ");
  1114. // sb.Append("INSERT INTO @PKeys ");
  1115. // sb.Append("SELECT parent.name parentTable, child.name childTable, syscolumns.name as parentColumn, sysforeignkeys.constid, …

Large files files are truncated, but you can click here to view the full file