SQL Generation

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:

   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 &lt;= 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: }

More DOS batch fun

Recently I needed to create a simple way to backup alot of website's log files. The directory structure is such:

C:\DemoSites\
 |
 |----\Demo1
 |    |
 |    |-----\Logs
 |
 |----\Demo2
 |    |
 |    |-----\Logs
 |
 |----\Demo3
 |    |
 |    |-----\Logs
 |
 |----\Demo4
 |    |
 |    |-----\Logs
 |
... etc

... So I need to backup the log files inside each "Logs" directory, in a loop...

Here is the batch file that takes care of it:

   01: @ECHO OFF

   02:  

   03: :: Get Today's Date in YYYYMMDD Format...

   04: for /F "tokens=2-4 delims=/ " %%a in ('date/t') do (set datevar=%%c%%a%%b)

   05:  

   06: :: For each subdirectory of C:\DemoSites, Zip all log files in their Logs directories...

   07: for /D %%d in (C:\DemoSites\*) do (

   08:     if exist %%d\Logs (

   09:         for /F "tokens=5 delims= " %%k in ('dir %%d\..\.') do (

   10:             if exist %%d\..\%%k\Logs\*.* (

   11:                 if not exist C:\Backup\archive_%%k-Logs_%datevar%.zip ( 

   12:                     \util\zip -9 -j C:\Backup\archive_%%k-Logs_%datevar%.zip %%d\..\%%k\Logs\*.*

   13:                 )

   14:             )

   15:         )

   16:     )

   17: )

[ Note: I'm using Info-Zip to compress the log files. ]

Backup Databases in a loop...

Note: This applies to Microsoft SQL Server 2005/2008...

    Ever get sick of having a different SQL Job for each database backup? I created this method for backing up all databases (filtered) in a cursor. Just create a job with this as the Step and give the Job a schedule...

  01:  DECLARE @dbName varchar(50)

  02:  DECLARE @BKPDate varchar(10)

  03:  DECLARE @sql nvarchar(1000)

  04:  SET @BKPDate = replace(convert(varchar(10), getdate(), 102), '.', '')

  05:  DECLARE bkpCursor CURSOR FOR

  06:      

  07:      /* only backup non-system databases */

  08:      select [Name] from sys.databases where [owner_sid] <> 0x01

  09:          

  10:  OPEN bkpCursor

  11:  FETCH NEXT FROM bkpCursor INTO  @dbName

  12:  IF @@FETCH_STATUS = 0

  13:  BEGIN

  14:      WHILE (@@FETCH_STATUS = 0)

  15:      BEGIN

  16:      set @sql = 

  17:          ' BACKUP DATABASE '+ @dbName +

  18:          ' TO DISK = N''C:\SQLBackup\'+ @dbName +'_'+ @BKPDate +'.bak''' +

  19:          ' WITH NOFORMAT, NOINIT,'+

  20:          ' NAME = N'''+ @dbName +'-Full Database Backup'',' +

  21:          ' SKIP, STATS = 10;'

  22:      exec sp_executesql @statement = @sql

  23:      FETCH NEXT FROM bkpCursor INTO @dbName

  24:      END

  25:  END

  26:  ELSE

  27:      PRINT 'No Databases to Backup.'

  28:  CLOSE bkpCursor

  29:  DEALLOCATE bkpCursor

MS SQL Server Express Edition

But what if you're running MS SQL Server Express? That doesn't come with SQL Server Agent Service, which is required for creating Jobs. Use the osql command-line utility.

Here is a batch file I wrote which uses osql to call a sql script file (after saving the above syntax into a sql file called "backup_Databases.sql").  Then I zip up the backup files and delete them.

  01:  @ECHO OFF

  02:  osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL

  03:   

  04:  C:\util\zip -9 -j -q C:\backup\archive_db_%1.zip C:\SQLBackup\*%1.bak

  05:  IF EXIST C:\backup\archive_db_%1.zip (

  06:     del C:\SQLBackup\*%1.bak

  07:  )

This batch file assumes you are passing in a Date string.

So to call it I would type:

[ Note: I'm using Info-Zip to compress the backup files. ]

Stand-alone Batch File
I know what you're thinking...  Why not make the batch file smart enough to get the date instead of having to pass it in?

Here is a revised batch file which does just that:

  01:  @ECHO OFF

  02:  osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL

  03:   

  04:  for /f "tokens=2-4 delims=/ " %%a in ('date/t') do (set datevar=%%c%%a%%b)

  05:   

  06:  C:\util\zip -9 -j -q C:\backup\archive_db_%datevar%.zip C:\SQLBackup\*%datevar%.bak

  07:  IF EXIST C:\backup\archive_db_%datevar%.zip (

  08:     del C:\SQLBackup\*%datevar%.bak

  09:  )

What's left?

All that's left is to create a scheduled task using Windows Task Scheduler, which will call the batch file (daily, weekly, etc.).

General practices (tomAYto tomAHto)...

I hate repeating code and sometimes generics can make things a bit easier. One of the things that needs to be done a lot with Drop-down lists is adding a "Please Select" as the first option, but I don't particularly like doing that in the data layer (stored procedure, etc.). I like to add that to the list, but why repeat the same code for every Drop-down list if there are several on the page? Here is one way I like to take care of that:

Here are the supporting Classes:

  01:  #region Supporting Classes

  02:  public interface IReportObject {

  03:      int ID { get; set; }

  04:      string Name { get; set; }

  05:  }

  06:  public class CSP : IReportObject {

  07:      public int ID { get; set; }

  08:      public string Name { get; set; }

  09:  }

  10:  public class Base : IReportObject {

  11:      public int ID { get; set; }

  12:      public string Name { get; set; }

  13:  }

  14:  public class Property : IReportObject {

  15:      public int ID { get; set; }

  16:      public string Name { get; set; }

  17:  }

  18:  #endregion

Here is the method that all my Drop-down list binders will call. Notice the "new()" constraint, which is needed in order to instantiate a generic. Notice also that I'm going ahead here and changing the text based on whether or not the list is empty. It wouldn't be very helpful to have an empty Drop-down list with just a "Please Select" option and nothing else. What's not shown here is that in the presentation layer, if there are no options to select in any given Drop-down list, the control is disabled as well.

  01:  public static void InsertBefore<T>(List<T> list)

  02:    where T : IReportObject, new() {

  03:      list.Insert(0, new T { ID = 0, Name = ((list.Count() > 0) ? 

  04:         "-- Please Select --" : 

  05:         "-- No Data Found --") });

  06:  }

And here is one of the methods binding one of my Drop-down lists calling the method...

  01:  public static List<CSP> GetCSPs() {

  02:      var list = new List<CSP>();

  03:      using (SqlDataReader rs = DBAccess.getRS("cspGetMilitaryCSPs")) {

  04:          while (rs.Read()) {

  05:              list.Add(new CSP {

  06:                  ID = DBAccess.ToInt32(rs["CSPID"], 0),

  07:                  Name = DBAccess.ToStringValue(rs["CSPName"], string.Empty)

  08:              });

  09:          }

  10:      }

  11:      InsertBefore<CSP>(list);

  12:      return list;

  13:  }

Alas, here is another way I'm doing basically the same thing using LINQ's Union operator. Doing it this way circumvents having to call the 'InsertBefore' method:

  01:  protected IEnumerable<PatientData> BindPatientData() {

  02:      List<PatientData> opt = new List<PatientData>();

  03:      IOrderedQueryable<PatientData> set = 

  04:          (from p in db.Patients

  05:           select new PatientData {

  06:               PatientID = p.PatientID,

  07:               PatientName = p.FirstName + " " + p.LastName

  08:           }).OrderBy(o => o.PatientName);

  09:      opt.Add(new PatientData {

  10:          PatientID = -1, 

  11:          PatientName = (set.Count() > 0) ?  

  12:          "-- Please Select --" : "-- No Data Found --"

  13:      });

  14:      return opt.Union(set.AsEnumerable()); 

  15:  }

Extend MVC HtmlHelper methods...

I always like to use helper methods, and the ASP.NET MVC HtmlHelper methods are great. But sometimes you need some extra logic that they don't provide... Enter custom extension methods.

Here is a class I wrote specifically for extension methods (Note: Some of the extension methods are calling some static Utility methods, and their implementation is not shown here):

  01:  public static class HtmlHelperExtensions {

  02:   

  03:      public static string DateFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  04:          DateTime date, string format, object attributes) {

  05:          string val = string.Format(format, date);

  06:          if (date.Year == 1900)

  07:              val = "0000-00-00";

  08:          return htmlHelper.TextBox(name, val, attributes);

  09:      }

  10:      public static string DateFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  11:          DateTime date, string format) {

  12:          string val = string.Format(format, date);

  13:          if (date.Year == 1900)

  14:              val = "0000-00-00";

  15:          return htmlHelper.TextBox(name, val);

  16:      }

  17:      public static string TimeFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  18:          int time, object attributes) {

  19:          string val = Utility.ConvertIntToTimeString(time);

  20:          return htmlHelper.TextBox(name, val, attributes);

  21:      }

  22:      public static string TimeFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  23:          int time) {

  24:          string val = Utility.ConvertIntToTimeString(time);

  25:          return htmlHelper.TextBox(name, val);

  26:      }

  27:      public static string MaskFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  28:          int mask, object attributes) {

  29:          string val = Utility.ConvertMaskToStartDays(mask);

  30:          return htmlHelper.TextBox(name, val, attributes);

  31:      }

  32:      public static string MaskFormatterTextBox(this HtmlHelper htmlHelper, string name, 

  33:          int mask) {

  34:          string val = Utility.ConvertMaskToStartDays(mask);

  35:          return htmlHelper.TextBox(name, val);

  36:      }

  37:  }

Here is the View that is using my extension methods...

  01:  <tr><td>Start Days:</td>

  02:      <td><%=Html.MaskFormatterTextBox("StartDays", ViewData.Model.StartDays.Value, 

  03:              new { size = 50 })%> 

  04:          <%=Html.ValidationMessage("StartDays", "*")%>

  05:      </td></tr>

  06:  <tr><td>Start Date:</td>

  07:      <td><%=Html.DateFormatterTextBox("StartDate", ViewData.Model.StartDate.Value,

  08:              "{0:yyyy-MM-dd}", new { size = 50 })%> 

  09:          <%=Html.ValidationMessage("StartDate", "*")%>

  10:      </td></tr>

  11:  <tr><td>End Date:</td>

  12:      <td><%=Html.DateFormatterTextBox("EndDate", ViewData.Model.EndDate.Value, 

  13:              "{0:yyyy-MM-dd}", new { size = 50 })%> 

  14:          <%=Html.ValidationMessage("EndDate", "*")%>

  15:      </td></tr>

  16:  <tr><td>Start Time:</td>

  17:      <td><%=Html.TimeFormatterTextBox("StartTime", ViewData.Model.StartTime.Value)%> 

  18:          <%=Html.ValidationMessage("StartTime", "*")%>

  19:      </td></tr>

  20:  <tr><td>End Time:</td>

  21:      <td><%=Html.TimeFormatterTextBox("EndTime", ViewData.Model.EndTime.Value)%> 

  22:          <%=Html.ValidationMessage("EndTime", "*")%>

  23:      </td></tr>

Configure Microsoft SSRS to use a Web Service as a Data Source...

I recently created a SQL Server Reporting Services (SSRS) report which used a Stored Procedure as a datasource, but the report needed some extra customization we're getting by using PaxScript in a similar web page. This isn't easily achievable using SQL stored procedures, so I decided to use a web service instead as the data source for the report. This way I have the advantage of LINQ/C# and PaxScript. Also, we have a need to run different versions of the web service, each for a different client. So the data source's web service URL needs to be dynamic, rather than static.

Here's how it's done...

In the report Data Source properties, the Type is XML and the Connection string value is a Report Parameter:


Next, the report's Dataset Text should be in the following format:


Note: There is a Parameter for each Web Service method parameter with the correct Name and Type. The ElementPath value is derived partly from the WSDL and partly from the XML that the Web Service method returns:

From WSDL:
"GetPatientVentDataLinqResponse{}/GetPatientVentDataLinqResult{}"


from the Web Service returned XML.
"/PatientVentData{}/Result{ParameterName,ParameterData,UpdateTime}"

Sample XML:

  01:  <?xml version="1.0" encoding="utf-8" ?>

  02:  <PatientVentData>

  03:      <Result>

  04:          <ParameterName>Tidal Vol exp (ml)</ParameterName>

  05:          <ParameterData>342.0</ParameterData>

  06:          <UpdateTime>1/1/2008 07:31:03 [Gallileo - 9]</UpdateTime>

  07:      </Result>

  08:  </PatientVentData>

Here is the Web Service method:

   01: [WebMethod]

   02: public XmlDocument GetPatientVentDataLinq(string patientId, 

   03:     string beginDate, string endDate, string deviceID, string userTimeZone) {

   04:     DataTable result = PatientController.GetVentData(true, 

   05:             SafeCast.Int(patientId), 

   06:             SafeCast.Date(beginDate), 

   07:             SafeCast.Date(endDate), 

   08:             SafeCast.Int(deviceID),

   09:             userTimeZone);

   10:     IEnumerable<DataRow> rows = result.AsEnumerable();

   11:     List<TReads[]> _reads = new List<TReads[]>();

   12:     foreach (DataColumn _col in result.Columns) {

   13:         if (_col.ColumnName.Contains("Column") ||

   14:             _col.ColumnName == "Parameter") {

   15:             var set = (from ro in rows

   16:                        select new TReads (

   17:                            ro.Field<string>(result.Columns[0].ColumnName),

   18:                            (result.Columns.Contains(_col.ColumnName) ?

   19:                                 ro.Field<string>(_col.ColumnName) : null),

   20:                            _col.Caption

   21:                        )).OrderByDescending(o => o.UpdateTime);

   22:             _reads.Add(set.ToArray());  // ToArray() is to offset LINQ's deferred execution.

   23:         }

   24:     }

   25:     if (_reads.Count > 0) {

   26:         var re = from o in _reads

   27:                  select o;

   28:         XElement xDoc = 

   29:             new XElement("PatientVentData",

   30:                 from x in re

   31:                 select (

   32:                     from t in x

   33:                     where t.UpdateTime != "Parameter"

   34:                     select new XElement("Result",

   35:                         new XElement("ParameterName", t.ParameterName),

   36:                         new XElement("ParameterData", t.ParameterData),

   37:                         new XElement("UpdateTime", t.UpdateTime)

   38:                     )

   39:                 )

   40:             );

   41:         XmlDocument xmlDocument = new XmlDocument();

   42:         xmlDocument.LoadXml(xDoc.ToString());

   43:         return xmlDocument;

   44:     }

   45:     return GetEmptySet();

   46: }

   47:  

Here is part the code that calls the Report Service:

(this segment of code establishes the Web Service URL relative to it's own URL and assigns it to the Report Parameters used to call the Report)

  01:  var url = Request.Url.AbsoluteUri;

  02:  var index = url.IndexOf(AppRelativeVirtualPath.Substring(1));

  03:  var webServiceUrl = url.Substring(0, index) + "/Services/BridgeTechVentHistory.asmx";

  04:   

  05:  ParameterValue[] parms = new ParameterValue[] {

  06:      new ParameterValue { Name = "patientID", Value = Request.QueryString[_requestString[0].Name] },

  07:      new ParameterValue { Name = "beginDate", Value = vdb.ToShortDateString() },

  08:      new ParameterValue { Name = "endDate", Value = vde.ToShortDateString() },

  09:      new ParameterValue { Name = "deviceID", Value = Request.QueryString[_requestString[3].Name] },

  10:      new ParameterValue { Name = "timeZone", Value = UserTimeZoneString },

  11:      new ParameterValue { Name = "webServiceUrl", Value = webServiceUrl }

  12:  };

Tyler Eating

Here is a picture of Tyler eating Peas. They are all over his face and he loves it!
If you look closely, you can see his first 2 teeth (bottom).