Concatenete Multiple Rows Within Single Row in SQL Server 2008

Concatenate Multiple Rows within Single Row in Sql Server 2008

Description:We can concatenate multiple rows within single row using predefined function STUFF available in SQL Server. Here is the example.

1. Create Database. Eg: StudentCourseDB (In this example)
   Create Database StudentCourseDB
2. Create 2 Tables(Courses, StudentCourses)
   Courses
   

   Create Table Courses

   (

     CourseID int primary key,

     CourseName nvarchar(20)

   )
3. Add some records in Courses Table
   
   INSERT INTO Courses(CourseId,CourseName) VALUES (1,'C#')
   INSERT INTO Courses(CourseId,CourseName) VALUES (2,'ASP.Net')
   INSERT INTO Courses(CourseId,CourseName) VALUES (3,'MVC')
   INSERT INTO Courses(CourseId,CourseName) VALUES (4,'WCF')
   INSERT INTO Courses(CourseId,CourseName) VALUES (5,'Share Point')
   INSERT INTO Courses(CourseId,CourseName) VALUES (6,'WPF')
   INSERT INTO Courses(CourseId,CourseName) VALUES (7,'SQL Server')
   INSERT INTO Courses(CourseId,CourseName) VALUES (8,'JQuery')
   SELECT * FROM Courses
   
   
4. Create another table StudentCourses
   

   CREATE TABLE StudentCourses

   (

   StudentID int,

   CourseID int

   )
5. Add some records into StudentCourses Table
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,1)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,3)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,5)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,2)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,4)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,5)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,3)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,6)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,7)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,8)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,1)
   INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,2)
   SELECT * FROM StudentCourses
   
   
6. Execute the following SQL Query to get student courseIds separated by comma
   USE StudentCourseDB
   SELECT StudentID,
   CourseIDs=STUFF
   (
          (
            SELECT DISTINCT ', ' + CAST(CourseID AS VARCHAR(MAX))
            FROM StudentCourses t2
            WHERE t2.StudentID = t1.StudentID
            FOR XML PATH('')
          ),1,1,''
   )
   FROM StudentCourses t1
   GROUP BY StudentID The final result will display all studentid and courseids separated by comma
   
   
7. Execute this query to get student course names separated by comma
   USE StudentCourseDB
   SELECT StudentID,
   CourseNames=STUFF
   (
         (
           SELECT DISTINCT ', '+ CAST(g.CourseName AS VARCHAR(MAX))
           FROM Courses g,StudentCourses e
           WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID
           FOR XMl PATH('')
         ),1,1,''
   )
   FROM StudentCourses t1
   GROUP BY StudentID
   The final result will be display all course names separated by comma
   
   

ASP.Net C# Download file from physical location

ASP.Net C# Download file from physical location

Description: In this example we learn how to download a file physically stored in our local system in asp.net c#

C# Code: protected void btnDownload_Click(object sender, EventArgs e)     {         string strURL = System.IO.Path.GetFullPath("D:\\Files\\Report.xlsx");         if (File.Exists(strURL))         {             WebClient req = new WebClient();             HttpResponse response = HttpContext.Current.Response;             response.Clear();             response.ClearContent();             response.ClearHeaders();             response.Buffer = true;             response.AddHeader("Content-Disposition", "attachment;filename=myfile.xlsx");             byte[] data = req.DownloadData(strURL);             response.BinaryWrite(data);             response.End();         }         else         {             lblStatus.Text = "File Not Found";         }     }

C#.Net Generate Unique key using RNGCryptoServiceProvider Class

Generate Unique key in c#

Description: In this example I will explain how to generate Unique Key using C# RNGCryptoServiceProvider class. In this example I have taken dropdownlist control to specify the length of the unique key to generate. This class is available under namespace System.Security.Cryptography.

Design Code: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="GetUnique.aspx.cs" Inherits="GetUnique" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title><link href="style.css" rel="stylesheet" /> </head> <body>     <form id="form1" runat="server">     <div>      <table>         <tr><td>                 <asp:DropDownList ID="ddlsize" runat="server" AutoPostBack="true">                 <asp:ListItem Text="Select size" Value="0"></asp:ListItem>                 <asp:ListItem Text="10" Value="1"></asp:ListItem>                 <asp:ListItem Text="15" Value="2"></asp:ListItem>                 <asp:ListItem Text="20" Value="3"></asp:ListItem>                 <asp:ListItem Text="25" Value="4"></asp:ListItem>                 <asp:ListItem Text="30" Value="5"></asp:ListItem>             </asp:DropDownList></td>             <td>                 <asp:Button ID="btnGetkey" runat="server" Text="Get Unique Key" OnClick="btnGetkey_Click" />             </td>         </tr>            </table>         <asp:Label ID="lblkey" runat="server" Text=""></asp:Label>          </div>            </form> </body> </html>

C# Code: using System; using System.Security.Cryptography; using System.Text;  public partial class GetUnique : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {            }     public string GetUniqueKey(int maxSize)     {               char[] chars = new char[62];         chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890".ToCharArray();         byte[] data = new byte[1];         RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();         crypto.GetNonZeroBytes(data);         data = new byte[maxSize];         crypto.GetNonZeroBytes(data);         StringBuilder result = new StringBuilder(maxSize);         foreach (byte b in data)         {             result.Append(chars[b % (chars.Length)]);         }         return result.ToString();     }     protected void btnGetkey_Click(object sender, EventArgs e)     {         if (ddlsize.SelectedIndex > 0)         {             lblkey.Text = GetUniqueKey(Convert.ToInt32(ddlsize.SelectedItem.ToString()));         }         else         {             lblkey.Text = "<span style=color:red>Invalid size</span>";         }     } }

Result:

Interview Questions

Interview Questions on .Net Framework What is .Net framework? In case of OS independent languages code executes under special software known as “Framework”. “Framework is software which will mask the functionalities of an OS and makes the code to execute under its control” providing features like OS independency, security and automatic memory management. What is CLR? CLR is the core component of the .net framework responsible in execution of a .net application. It contains various things under it. Security Manager: which is responsible for managing the security of applications. Class loader:This is responsible for loading the required libraries for the execution of applications. JIT Compiler:It is responsible for converting IL code into machine code. Garbage Collector: This is responsible for automatic memory management, performing the cleanup of unused objects in a program i.e. whenever it identifies unused objects treats them as garbage and reclaims the memory allocated for that objects. Exception Handler: This is responsible for managing the runtime errors that occur within a program. What is CLS? It is a set of base rules all the high level .net languages has to adopt to interoperate with each other. Most importantly after compiling any .net language program it should generate the same type of output known as CIL code. What is CTS? According to this the data types that are present under every .net language should adopt similar structure. i.e. sizes of similar types should be uniform. The names of the types will not be similar in all these languages. But similar types present in different languages even if they don’t have the same name will have the same size. When we use a type in any language after compilation that type gets converted into “IL” type as following What is CIL? Dotnet is a collection of languages which allows writing the source code in any dotnet language but once we compile it, it gets converted into CIL (Common Intermediate Language). CIL code can be executed on any machine with the help of CLR which takes the responsibility of converting CIL code into machine code specific to the OS. What is Assembly? Whenever a project is compiled it generates an output file that contains IL code of all items in the project known as assembly. These assemblies are what we carry on the client machines when the application has to be deployed. So they are referred as units of deployment. An assembly file can have an extension of either .exe or .dll Can we consume classes of a project from other classes of same project? Yes, we can consume them directly because all those classes were under same project and will be considered as a family. Can we consume classes of a project from other classes of other project? Yes, we can consume but not directly as they are under different projects, first we need to add reference of the assembly in which the class is present to the project who wants to consume it. How to add reference of an assembly to a project? To add a reference of an assembly to a project, Open solution explorer Right click on the project to whom reference has to be added Select Add reference Click browse tab on top of the window which has been open Select the assembly we want to consume from its physical location and click ok. Now we can consume classes of that assembly referring with their namespace. Define shared assembly? An assembly that resides in a centralized location known as GAC (Global Assembly Cache) and provides resources to any no.of projects that wants to consume is known as shared assemblies. If an assembly is shared multiple copies will not be created even if being consumed by multiple projects, only a single copy under GAC serves all the projects. GAC is a folder that is present under the Windows folder. C:\Windows\assembly What assemblies can be copied into GAC? We can copy only strong named assemblies into GAC.