Recently I wrote a console application that generates SQL code to populate a database with sample data. I wanted it to be as generic as possible, but it still relies on a Source database to get sample data from. The application is configurable in that it lets you specify which Database is the source database, as well which tables to get the data out of. I did not want this application to depend on any stored procedures; it needs to be self contained, which is why I'm hard-coding the table definition sql query.
So I call the console app via a batch file. The batch file calls the exe file, outputting the text to a file:
Here is the main method in the console app:
Here is the relevant excerpt from the App.config file:
Here is the Enum which the working class relies on:
Here is the entire working class
(Note: this class uses a database utility helper class, called "DBAccess", which is not included here. This post assumes you have a similar Database utility or framework in place)...
So I call the console app via a batch file. The batch file calls the exe file, outputting the text to a file:
01: @ECHO OFF
02: bin\debug\SqlGenerator.exe > "C:\run.sql"
03: ECHO done.
Here is the main method in the console app:
01: class Program {
02: static void Main(string[] args) {
03: Console.WriteLine(Database.GenerateSQL()); 04: } 05: }Here is the relevant excerpt from the App.config file:
01: <appSettings>
02: <!--
03: GenerateSQL Possible values:
04: SystemData,
05: SampleData
06: -->
07: <add key="GenerateSQL" value="SystemData,SampleData"/>
08: <add key="SystemTables" value="StandardCommands,PaxScript,VersionInfo,Model,
09: Parameter,Forms,FormFieldTypes,FormFields,ListDefinition,
10: ListColumn,ListType,ListData"/>
11: <add key="SampleTables" value="Patient|WHERE PatientID <= 7564,
12: Bridge,Device"/>
13: </appSettings>
Here is the Enum which the working class relies on:
01: public enum DB : int {
02: SystemData = 0, 03: SampleData = 1 04: }Here is the entire working class
(Note: this class uses a database utility helper class, called "DBAccess", which is not included here. This post assumes you have a similar Database utility or framework in place)...
01: class Database {
02: 03: #region Private Properties
04: private static List<string> SystemDataTables {
05: get {
06: return new List<string>() {
07: "StandardCommands",
08: "PaxScript",
09: "VersionInfo",
10: "Model",
11: "Parameter",
12: "Forms",
13: "FormFieldTypes",
14: "FormFields",
15: "ListDefinition",
16: "ListColumn",
17: "ListType",
18: "ListData"
19: }; 20: } 21: }22: private static List<string> SampleDataTables {
23: get {
24: return new List<string>() {
25: "Patient",
26: "Bridge",
27: "Device"
28: }; 29: } 30: }31: #endregion
32: 33: public static string GenerateSQL() {
34: StringBuilder sb = new StringBuilder();
35: if (ConfigurationManager.AppSettings["GenerateSQL"] != null) {
36: List<DB> list = new List<DB>();
37: foreach (string s in ConfigurationManager
38: .AppSettings["GenerateSQL"].Split(',')) {
39: list.Add((DB)Enum.Parse(typeof(DB), s));
40: } 41: sb.Append(GenerateSQL(list));42: } else
43: sb.Append(Database.GenerateSQL(new List<DB> {
44: DB.SystemData, DB.SampleData }));45: return sb.ToString();
46: }47: public static string GenerateSQL(List<DB> list) {
48: StringBuilder sb = new StringBuilder();
49: 50: // Order them by precedence
51: // (in-case the App.config has the values out of order)...
52: var e = from o in list
53: orderby (int)o
54: select o;
55: 56: foreach (var d in e) {
57: switch (d) {
58: case DB.SystemData:
59: sb.Append(SystemData());60: break;
61: case DB.SampleData:
62: sb.Append(SampleData());63: break;
64: } 65: }66: return sb.ToString();
67: } 68: 69: #region [ System Data ]
70: private static string SystemData() {
71: StringBuilder sb = new StringBuilder();
72: if (ConfigurationManager.AppSettings["SystemTables"] != null) {
73: foreach (string s in ConfigurationManager
74: .AppSettings["SystemTables"].Split(',')) {
75: if (s.Contains("|")) {
76: string[] parms = s.Split('|');
77: sb.Append(GenerateSqlFromTable(parms[0], parms[1]));78: } else
79: sb.Append(GenerateSqlFromTable(s)); 80: }81: } else {
82: foreach (string s in SystemDataTables) {
83: sb.Append(GenerateSqlFromTable(s)); 84: } 85: }86: return sb.ToString();
87: }88: #endregion
89: 90: #region [ Sample Data ]
91: private static string SampleData() {
92: StringBuilder sb = new StringBuilder();
93: if (ConfigurationManager.AppSettings["SampleTables"] != null) {
94: foreach (string s in ConfigurationManager
95: .AppSettings["SampleTables"].Split(',')) {
96: if (s.Contains("|")) {
97: string[] parms = s.Split('|');
98: sb.Append(GenerateSqlFromTable(parms[0], parms[1]));99: } else
100: sb.Append(GenerateSqlFromTable(s)); 101: }102: } else {
103: foreach (string s in SampleDataTables) {
104: sb.Append(GenerateSqlFromTable(s)); 105: } 106: }107: return sb.ToString();
108: }109: #endregion
110: 111: #region GenerateSqlFromTable overloads
112: private static string GenerateSqlFromTable(string tableName) {
113: return GenerateSqlFromTable(tableName, null);
114: } 115: 116: private static string GenerateSqlFromTable(string tableName,
117: string condition) {
118: StringBuilder sb = new StringBuilder();
119: List<GenericTable> genTable = new List<GenericTable>();
120: DataSet ds = new DataSet();
121: 122: // Store the Table definition in a List...
123: using (SqlDataReader rdr = DBAccess
124: .executeQuery(ColumnDefinitions(tableName))) {125: if (rdr.HasRows) {
126: while (rdr.Read()) {
127: genTable.Add(new GenericTable {
128: Column = DBAccess.ToStringValue(rdr["Column"]),
129: Type = DBAccess.ToStringValue(rdr["Type"]),
130: Width = DBAccess.ToInt16(rdr["Width"]),
131: IsNullable = DBAccess.ToBool(rdr["IsNullable"]),
132: DefaultValue = DBAccess.ToStringValue(rdr["DefaultValue"]),
133: Length = DBAccess.ToInt16(rdr["Length"]),
134: PrimaryKey = DBAccess.ToInt16(rdr["PrimaryKey"]),
135: Identity = DBAccess.ToBool(rdr["Identity"])
136: }); 137: } 138: } 139: }140: var identityExists = (from cols in genTable
141: where cols.Identity == true
142: select cols).Any();
143: 144: // Create the Select statement for the Source Database Table...
145: string sql = "select ";
146: foreach (var t in genTable) {
147: sql += String.Format("[{0}]{1}",
148: t.Column, (t == genTable.Last() ? "" : ", "));
149: }150: sql += String.Format(" from {0} {1}", tableName, condition ?? "");
151: var s = genTable
152: .Where(o => o.PrimaryKey == 1)
153: .Select(o => o.Column).First();
154: if (!String.IsNullOrEmpty(s))
155: sql += String.Format(" order by {0}", s);
156: 157: // Convert DataReader to Dataset in order to
158: // detect when you're on the last record.
159: using (SqlDataReader reader = DBAccess.executeQuery(sql)) {
160: ds = ConvertDataReaderToDataSet(reader); 161: } 162: 163: // Right now it's really just 1 table, but that could change.
164: foreach (DataTable dt in ds.Tables) {
165: List<DataRow> rows = new List<DataRow>();
166: for (int x = 0; x < dt.Rows.Count; x++) {
167: rows.Add(dt.Rows[x]); 168: } 169: 170: if (rows.Count > 0) {
171: if (identityExists) {
172: sb.AppendFormat("SET IDENTITY_INSERT [dbo].[{0}] ON\n",
173: tableName); 174: }175: // Get number of 100 record increments
176: // and add them to a List of List<DataRow>
177: int chunks = Convert.ToInt32(Math.Ceiling(rows.Count() / 100.0));
178: List<IEnumerable<DataRow>> rowsList =
179: new List<IEnumerable<DataRow>>();
180: for (int c = 0; c < chunks; c++) {
181: var r = (from row in rows
182: select row).Skip(c * 100).Take(100);
183: rowsList.Add(r); 184: } 185: 186: // Enumerate through each 100 record increment...
187: foreach (var list in rowsList) {
188: sb.AppendFormat(@"INSERT [dbo].[{0}] (", tableName);
189: foreach (var t in genTable) {
190: sb.AppendFormat(@"[{0}]{1}", t.Column,
191: (t == genTable.Last() ? "" : ", "));
192: }193: sb.Append(")\n");
194: 195: int i = 0;
196: foreach (var dr in list) {
197: sb.Append(@"select ");
198: foreach (var t in genTable) {
199: switch (t.Type.ToLower()) {
200: case "varchar":
201: case "nvarchar":
202: case "char":
203: case "nchar":
204: case "text":
205: case "ntext":
206: case "datetime":
207: case "smalldatetime":
208: case "uniqueidentifier":
209: if (t.IsNullable) {
210: if (dr[t.Column] == DBNull.Value) {
211: if (!String.IsNullOrEmpty(t.DefaultValue))
212: sb.AppendFormat(@"N'{0}'", t.DefaultValue);
213: else
214: sb.Append("NULL");
215: } else {
216: sb.AppendFormat(@"N'{0}'",
217: DBAccess.ToStringValue(dr[t.Column])218: .Replace("'", "''"));
219: }220: } else {
221: sb.AppendFormat(@"N'{0}'",
222: DBAccess.ToStringValue(dr[t.Column])223: .Replace("'", "''"));
224: }225: break;
226: case "image": // Cannot Convert IMAGE to String
227: sb.Append("NULL");
228: if (dr[t.Column] != DBNull.Value) {
229: MessageBox.Show(String.Format(
230: "{0}.[{1}] has a valid value for an " +
231: "Image data type in Source Database!",
232: tableName, t.Column)); 233: }234: break;
235: case "bit":
236: sb.AppendFormat(@"{0}",
237: ((t.IsNullable) ?238: (dr[t.Column] == DBNull.Value) ?
239: ((!String.IsNullOrEmpty(t.DefaultValue)) ?
240: t.DefaultValue : "NULL") :
241: DBAccess.ToStringValue(DBAccess 242: .ToInt16(dr[t.Column])) 243: : DBAccess.ToStringValue(DBAccess 244: .ToInt16(dr[t.Column])) 245: ));246: break;
247: default:
248: sb.AppendFormat(@"{0}",
249: ((t.IsNullable) ?250: (dr[t.Column] == DBNull.Value) ?
251: ((!String.IsNullOrEmpty(t.DefaultValue)) ?
252: t.DefaultValue : "NULL") :
253: DBAccess.ToStringValue(dr[t.Column]) 254: : DBAccess.ToStringValue(dr[t.Column]) 255: ));256: break;
257: }258: sb.Append((t == genTable.Last()) ? "" : ", ");
259: }260: sb.Append((++i < list.Count()) ? " union all\n" : "\n\n");
261: } /* finished with IEnumerable<DataRow> */
262: } /* finished with List<IEnumerable<DataRow>> */
263: if (identityExists) {
264: sb.AppendFormat("SET IDENTITY_INSERT [dbo].[{0}] OFF\n\n",
265: tableName); 266: } 267: }268: } /* finished with DataTable */
269: return sb.ToString();
270: }271: #endregion
272: 273: #region Helper Methods
274: private static string ColumnDefinitions(string TableName) {
275: string ret = String.Format(@"
276: select
277: sc.[name] as 'Column',
278: syt.[name] as 'Type',
279: sc.prec as 'Width',
280: sc.IsNullable,
281: dc.[definition] as 'DefaultValue',
282: sc.[Length],
283: case when
284: sc.[name] in (
285: select sc2.[name]
286: from sys.columns sc2
287: inner join sys.tables st on sc2.object_id = st.object_id
288: inner join sys.indexes si on sc2.object_id = si.object_id
289: inner join sys.index_columns sic
290: on st.object_id = sic.object_id
291: and si.index_id = sic.index_id
292: and sc2.column_id = sic.column_id
293: where st.[name] = '{0}'
294: and is_primary_key = '1') then 1
295: else 0
296: end as 'PrimaryKey',
297: case when
298: sc.[name] in (
299: select sc3.[name]
300: from sys.columns sc3
301: inner join sys.objects so
302: on sc3.object_id = so.object_id
303: where so.[name] = '{0}'
304: and so.[type] = 'U'
305: and sc3.[is_identity] = 1) then 1
306: else 0 end as 'Identity'
307: from syscolumns sc
308: inner join systypes syt
309: on sc.xusertype = syt.xusertype
310: and sc.xtype = syt.xtype
311: left join sys.default_constraints dc
312: on sc.cdefault = dc.object_id
313: where sc.id = OBJECT_ID('{0}')
314: and syt.[name] <> 'sysname'", TableName);
315: return ret;
316: } 317: 318: private static DataSet ConvertDataReaderToDataSet(
319: SqlDataReader reader) {
320: DataSet dataSet = new DataSet();
321: do {
322: DataTable schemaTable = reader.GetSchemaTable();
323: DataTable dataTable = new DataTable();
324: if (schemaTable != null) {
325: for (int i = 0; i < schemaTable.Rows.Count; i++) {
326: DataRow dataRow = schemaTable.Rows[i];
327: string columnName = (string)dataRow["ColumnName"];
328: DataColumn column = new DataColumn(columnName,
329: (Type)dataRow["DataType"]);
330: dataTable.Columns.Add(column); 331: } 332: 333: dataSet.Tables.Add(dataTable);334: while (reader.Read()) {
335: DataRow dataRow = dataTable.NewRow();
336: for (int i = 0; i < reader.FieldCount; i++)
337: dataRow[i] = reader.GetValue(i); 338: dataTable.Rows.Add(dataRow); 339: }340: } else {
341: // No records were returned
342: DataColumn column = new DataColumn("RowsAffected");
343: dataTable.Columns.Add(column); 344: dataSet.Tables.Add(dataTable);345: DataRow dataRow = dataTable.NewRow();
346: dataRow[0] = reader.RecordsAffected; 347: dataTable.Rows.Add(dataRow); 348: } 349: }350: while (reader.NextResult());
351: return dataSet;
352: }353: #endregion
354: 355: #region Helper Classes
356: private class GenericTable {
357: public string Column { get; set; }
358: public string Type { get; set; }
359: public int Width { get; set; }
360: public bool IsNullable { get; set; }
361: public int Length { get; set; }
362: public string DefaultValue { get; set; }
363: public int PrimaryKey { get; set; }
364: public bool Identity { get; set; }
365: }366: #endregion
367: }
Comments