SQL Batch alter all columns

< 1 min read

Sometimes you need to make mass changes to a database, and either add or remove a column, or other table or data manipulations. The script below prints SQL statements you can later execute for whatever you need. It loops through each table in your database, and replaces ? with the table name.

EXEC sp_msforeachtable
'PRINT ALTER TABLE ? ADD LastModified DATETIME NOT NULL DEFAULT GETDATE();
ALTER TABLE ? ADD UserID int NULL;
ALTER TABLE ? ADD Deleted bit NOT NULL DEFAULT 0;'
EXEC sp_msforeachtable
    'PRINT Update ? SET SiteID=1'

IE 8 HTML5 Compatibility

< 1 min read Adds new HTML5 elements, and supports printing HTML5 elements and includes the default styles for HTML5 elements, like block on article and section.

To use this script, download the html5shiv and roll it in to your own code (ideally minified). It must be included before theelement (i.e. in the) but doesn’t matter if it appears before or after the CSS – but for the sake of performance, it would make better sense to include the CSS first then this script.


Dynamic Linq for searching

< 1 min read

 var predicate = PredicateBuilder.True();
 var q = db.Profiles.Where(t => !t.aspnet_Users.aspnet_Membership.IsLockedOut);
 foreach (string keyword in pars)
 {
     string temp = keyword;
     predicate = predicate.And(t => t.FirstName.StartsWith(temp) || t.LastName.StartsWith(temp));
 }

Filezilla Access to only certain subfolders

< 1 min read I had a scenario once, where I needed to give certain users access to multiple sub folders, without creating multiple FTP accounts. The goal was for them to see certain folders, but not others.
So let’s say my folder structure is:

And my user should be able to have full control of Site1 and Site2, but not even see Site3.
To do this, first create the user, and give him only List access on the root folder

Next, add each of the sub folders he has access to, and give him appropriate access on each sub folder.

When he connects now, he will only see the folders he has access to.

C# Render Razor View as String

< 1 min read The Razor View Engine is very powerful, easy to use, and just overall great for binding data to HTML. But sometimes you need to render HTML for use in Emails, download pages, generate PDF’s, or other purposes, so it would be neat if you could use the Razor View Engine to generate those for you, and just give you the HTML to do with it as you wish. Luckily we have this somewhat built-in, and here’s a wrapper for it.

public string RenderRazorViewToString(string viewName, object model)
{
  ViewData.Model = model;
  using (var sw = new StringWriter())
  {
    var viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
    var viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
    viewResult.View.Render(viewContext, sw);
    viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);
    return sw.GetStringBuilder().ToString();
  }
}

You can now use it as in the following example

var htmlPage = RenderRazorViewToString("~/Views/Home/myview.cshtml", siteModel);

You can of course extend the method above, and pass it your own ViewData, TempData, RouteData, change the View Engine, enable or disable ClientValidation and UnobtrusiveJavasript, and so on.

C# Add and Edit Office Document Custom Properties

< 1 min read Office documents store a lot more information than is obviously visible at first. Microsoft released a library called DSOfile, which is intended for changing properties of Office files if you don’t have Office installed.
First, you need to download the DSOfile from Microsoft. http://support.microsoft.com/kb/224351

Second, you’ll need to reference it in your project

using DSOFile;

Next, you can start looping through the Office predefined properties of each file. Note, some attributes are read-only.

OleDocumentProperties file = new OleDocumentProperties();
file.Open(@"C:myfile.docx", false, dsoFileOpenOptions.dsoOptionDefault);
int charCount = file.SummaryProperties.CharacterCount;
int wordCount = file.SummaryProperties.WordCount;
int pageCount = file.SummaryProperties.PageCount;
file.SummaryProperties.Author = "John Smith";
file.SummaryProperties.Category = "My Category";
file.SummaryProperties.Company = "My Company Inc.";
file.SummaryProperties.Manager = "David Smith";
file.SummaryProperties.Subject = "Sample files";
file.SummaryProperties.Title = "A very sample file";
file.Save();
file.Close(true);

Those might sometimes not be enough, so surely enough you can add your own custom attributes, and use the NTFS File System as your own personal database.

OleDocumentProperties file = new DSOFile.OleDocumentProperties();
file.Open(@"C:myfile.docx", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault);
string key = "My Custom Key"; /* Use any key you want, these will be saved in the file. */
object value = "My Custom Value";
// Check if file has a certain property set
bool hasProperty = false;
foreach (DSOFile.CustomProperty p in file.CustomProperties)
    if (p.Name == key)
        hasProperty = true;
// If it doesn't have the property, add it, otherwise set it.
// This is the only way I found to loop through the properties
if (!hasProperty)
    file.CustomProperties.Add(key, ref value);
else
    foreach (DSOFile.CustomProperty p in file.CustomProperties)
        if (p.Name == key)
            p.set_Value(value);
// Go through existing custom properties.
foreach (DSOFile.CustomProperty p in file.CustomProperties)
{
    Console.WriteLine("{0}:{1}", p.Name, p.get_Value().ToString());
}
file.Save();
file.Close(true);