SQL - How to capture multiple identities from INSERT INTO SELECT

-- Example Source table
CREATE TABLE #SourceTable ( Contact CHAR(30) );

INSERT #SourceTable VALUES ( 'User1' )
INSERT #SourceTable VALUES ( 'User2' )
INSERT #SourceTable VALUES ( 'User3' )

-- Example Dest table
CREATE TABLE #ImportedUsers ( Id       INT IDENTITY(1, 1) NOT NULL
,                             UserName CHAR(30) );

-- Imported users + new ID table
DECLARE @IDs TABLE ( Id       INT
,                    UserName CHAR(30) )

INSERT #ImportedUsers
 OUTPUT inserted.Id
 ,      inserted.UserName
 INTO @IDs ( Id
 ,           UserName )
SELECT DISTINCT Contact
FROM #SourceTable

SELECT *
FROM @IDs

Time Your Code Using StopWatch

Here's a useful snippet for timing how long a piece of code takes to execute, and so can be useful for identifying bottlenecks.

using System;
using System.Diagnostics;
using System.Threading;
class Program
{
    static void Main(string[] args)
    {
        Stopwatch stopWatch = new Stopwatch();
        stopWatch.Start();

        // Code you want to measure goes here.. lets just sleep for the purpose of this example
        Thread.Sleep(10000);

        stopWatch.Stop();
        // Get the elapsed time as a TimeSpan value.
        TimeSpan ts = stopWatch.Elapsed;

        // Format and display the TimeSpan value into something meaningful
        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
            ts.Hours, ts.Minutes, ts.Seconds,
            ts.Milliseconds / 10);
        Console.WriteLine("RunTime " + elapsedTime);
    }
}

SQL Server - How convert SQL rows to Comma Separated String (CSV)

declare @T table (GroupID int, Value varchar(10))

insert into @T values (1, 'Row 1')
insert into @T values (1, 'Row 2')
insert into @T values (2, 'Row 3')
insert into @T values (2, 'Row 4')
insert into @T values (2, 'Row 5')

select GroupID,
  stuff(
  (select ','+Value as '*'
   from @T as T2
   where T2.GroupID = T1.GroupID
   for xml path('')), 1, 1, '') as [Values]
from @T as T1
group by GroupID

go

C# - How to Fetch a Report from SQL Server Reporting Services (SSRS)

Tested with SSRS 2008, may well work with 2005 too. You'll need to add a Service Reference in your project to the SSRS API, which will generate the ReportExecutionServiceSoapClient proxy seen here. You can request the report in various formats: "PDF", "WORD", or "EXCEL".

 class ReportFetcher  
   {  
     public enum ReportFileFormat  
     { PDF }  
     /// <summary>  
     /// Makes a call to a a SSRS server to load and render a report in a certain file format  
     /// </summary>  
     /// <param name="pathToReport">Path to the report on the server (including name)</param>  
     /// <param name="execParams">Execution parameters</param>  
     /// <param name="fileFormat">Rendering file format</param>  
     /// <returns></returns>  
     public byte[] ToFile(string pathToReport, List<ParameterValue> execParams, string format = "PDF")  
     {  
       ExecutionInfo execInfo;  
       var trusteduserHeader = new TrustedUserHeader();  
       var execHeader = new ExecutionHeader();  
       ServerInfoHeader serviceInfo;  
      
       var client = new ReportExecutionServiceSoapClient();  
       if (client.ClientCredentials == null)  
       {  
         throw new NullReferenceException("Client Credentials are null");  
       }  
       client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;  
       client.ClientCredentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;  
       // Load the report  
       client.LoadReport(trusteduserHeader, pathToReport, null, out serviceInfo, out execInfo);  
       execHeader.ExecutionID = execInfo.ExecutionID;  
       client.SetExecutionParameters(execHeader, trusteduserHeader, execParams.ToArray(), "en-us", out execInfo);  
       // Render the report  
       string extension;  
       string encoding;  
       string mimeType;  
       string[] streamIds;  
       var warnings = new Warning[1];  
       warnings[0] = new Warning();  
       Byte[] result;  
       client.Render(execHeader, trusteduserHeader, format, null, out result, out extension, out mimeType, out encoding, out warnings, out streamIds);  
       ConnectionHelper.SafelyCloseConnection(client);  
       return result;  
     }  
   }  

If you want to save the returned byte array to a file, it's easily done like this:

using (var stream = File.OpenWrite("myReport.pdf"))

{
   stream.Write(result, 0, result.Length);

}

C# ASP.NET MVC4 - How to Generate an Atom or RSS Feed

.NET's syndication classes make creating RSS/Atom feeds easy. Here's how to do it in ASP.NET MVC. We're using Atom below, but switching to RSS is simply a matter of changing the Atom10FeedFormatter to Rss20FeedFormatter.

The feed url in this example would be http://myserver/myappname/feed

 public class FeedResult : ActionResult  
   {  
     public Encoding ContentEncoding { get; set; }  
     public string ContentType { get; set; }  
     private readonly SyndicationFeedFormatter _feed;  
     public SyndicationFeedFormatter Feed  
     {  
       get { return _feed; }  
     }  
     public FeedResult(SyndicationFeedFormatter feed)  
     {  
       _feed = feed;  
     }  
     public override void ExecuteResult(ControllerContext context)  
     {  
       if (context == null)  
         throw new ArgumentNullException("context");  
       HttpResponseBase response = context.HttpContext.Response;  
       response.ContentType = !string.IsNullOrEmpty(ContentType) ? ContentType : "application/rss+xml";  
       if (ContentEncoding != null)  
         response.ContentEncoding = ContentEncoding;  
       if (_feed != null)  
       {  
         using (var xmlWriter = new XmlTextWriter(response.Output))  
         {  
           xmlWriter.Formatting = Formatting.Indented;  
           _feed.WriteTo(xmlWriter);  
         }  
       }  
     }  
   }   
 

 
   public class FeedController : Controller  
   {  
     private readonly IPostingDataSource _db;  
     private static readonly Settings Settings = new Settings();  
     private const int ItemContentLength = 500; 
 
     public FeedController()  
     {  
       _db = new PostingDbContext();  
     }  
     public ActionResult Index()  
     {  
       string appUrl = Request.Url.GetLeftPart(UriPartial.Authority) + Request.ApplicationPath;  
       string detailsUrl = appUrl + "/Home/Details/";  
       var postItems = _db.Postings  
           .OrderByDescending(x => x.AddedDate)  
           .Take(20)  
           .AsEnumerable()  
           .Select(p => new SyndicationItem(p.Title, TruncateLongString(p.Body, ItemContentLength, true), new Uri(detailsUrl + p.Id), p.Id.ToString(), new DateTimeOffset(p.AddedDate)));  
       var feed = new SyndicationFeed("bCay Auctions", "Buy! Sell!", new Uri(appUrl), postItems)  
       {  
         Copyright = new TextSyndicationContent("Codestruck Productions"),  
         Language = "en-US"  
       };  
       feed.Authors.Add(new SyndicationPerson(Settings.Email, "Codestruck", appUrl));  
       // Can change from Atom to RSS here  
       return new FeedResult(new Atom10FeedFormatter(feed));  
     }  
     private static string TruncateLongString(string str, int maxLength, bool addEllipsis)  
     {  
       if (str.Length <= maxLength)  
         return str;  
       string result = str.Substring(0, Math.Min(str.Length, maxLength));  
       if (addEllipsis)  
         result += "...";  
       return result;  
     }  
   }  

Welcome to Codestruck

I've started this blog as a place to keep useful code snippets, whether I come across them on-line or code I've written myself. Keeping stuff here in on place will certainly be useful to me, and I hope others will find it useful too.

Codestruck. Like star-struck, but less celebrity-spotting and more bug-spotting.