Split Multiple Separator Text Into Table SQL Server

ALTER FUNCTION [dbo].[udf_GEN_

SplitMultipleSeparatorTextIntoTable]
( 
    @DelimiterFirst VARCHAR(5),
    @DelimiterSecond VARCHAR(5),
    @List      NVARCHAR(MAX)
)
RETURNS @TableOfValues table
  (  RowID   smallint IDENTITY(1,1),
     [Value] varchar(50)
  )
AS
   BEGIN
   
    ----select * from [udf_GEN_SplitMultipleSeparatorTextIntoTable]( '#',',','1,2#3,4#5,6#')
     
      DECLARE @LenString int

      WHILE len( @List ) > 0
         BEGIN
        
            SELECT @LenString =
               (CASE charindex( @DelimiterFirst, @List )
                   WHEN 0 THEN len( @List )
                   ELSE ( charindex( @DelimiterFirst, @List ) -1 )
                END
               )
                 
            IF SUBSTRING( @List, 1, @LenString ) <> ''
                BEGIN
                    --INSERT INTO @TableOfValues SELECT substring( @List, 1, @LenString )
                    --print(substring( @List, 1, @LenString ))
                    -------------
                    declare @SecondList      varchar(8000)
                    DECLARE @LenStringSecond int
                    set @SecondList =substring( @List, 1, @LenString )
                    WHILE len( @SecondList ) > 0
                         BEGIN
                        
                            SELECT @LenStringSecond =
                               (CASE charindex( @DelimiterSecond, @SecondList )
                                   WHEN 0 THEN len( @SecondList )
                                   ELSE ( charindex( @DelimiterSecond, @SecondList ) -1 )
                                END
                               )
                                 
                            IF SUBSTRING( @SecondList, 1, @LenStringSecond ) <> ''
                                BEGIN
                                    INSERT INTO @TableOfValues SELECT substring( @SecondList, 1, @LenStringSecond )
                                    --print(substring( @SecondList, 1, @LenStringSecond ))
                                END
                               
                            SELECT @SecondList =
                               (CASE ( len( @SecondList ) - @LenStringSecond )
                                   WHEN 0 THEN ''
                                   ELSE right( @SecondList, len( @SecondList ) - @LenStringSecond - 1 )
                                END
                               )
                         END
                    ---------
                END
               
            SELECT @List =
               (CASE ( len( @List ) - @LenString )
                   WHEN 0 THEN ''
                   ELSE right( @List, len( @List ) - @LenString - 1 )
                END
               )
         END
         
      RETURN
     
   END

Backup and restore database in SQLServer

Code:

BACKUP DATABASE abc_SuperAdmin
   TO DISK = 'c:\abc_SuperAdmin.bak'
RESTORE FILELISTONLY
   FROM DISK = 'c:\abc_SuperAdmin.bak'
RESTORE DATABASE TestDB
   FROM DISK = 'c:\abc_SuperAdmin.bak'
   WITH MOVE 'abc_SuperAdmin_data' TO 'c:\testdb.mdf',
   MOVE 'abc_SuperAdmin_log' TO 'c:\testdb.ldf'

//////////////////////////////

////////////////////////////////
SP

CREATE PROCEDURE sp_AddDatabase

@DatabaseName     varchar(255),   
@BakFilePath        varchar(255)

 AS

Declare @DatabaseNameMdf    VARCHAR(255)
Declare @DatabaseNameLdf    VARCHAR(255)

SELECT @DatabaseNameMdf =  'C:\Program Files\Microsoft SQL Server\MSSQL$abcd2000\Data\' + @DatabaseName+ '.mdf'
SELECT @DatabaseNameLdf =  'C:\Program Files\Microsoft SQL Server\MSSQL$abcd2000\Data\'+ @DatabaseName +'.ldf'

RESTORE DATABASE @DatabaseName
FROM DISK = @BakFilePath
WITH MOVE 'abc_Events_Data'  TO @DatabaseNameMdf,
MOVE 'abc_Events_log'  TO  @DatabaseNameLdf
GO

Association, Aggregation, Composition

Association is a relationship where all object have their own lifecycle and there is no owner. Let’s take an example of Teacher and Student. Multiple students can associate with single teacher and single student can associate with multiple teachers but there is no ownership between the objects and both have their own lifecycle. Both can create and delete independently.

Aggregation is a specialize form of Association where all object have their own lifecycle but there is ownership and child object can not belongs to another parent object. Let’s take an example of Department and teacher. A single teacher can not belongs to multiple departments, but if we delete the department teacher object will not destroy. We can think about “has-a” relationship.

Composition is again specialize form of Aggregation and we can call this as a “death” relationship. It is a strong type of Aggregation. Child object dose not have their lifecycle and if parent object deletes all child object will also be deleted. Let’s take again an example of relationship between House and rooms. House can contain multiple rooms there is no independent life of room and any room can not belongs to two different house if we delete the house room will automatically delete. Let’s take another example relationship between Questions and options. Single questions can have multiple options and option can not belong to multiple questions. If we delete questions options will automatically delete.

Code to limit the textbox characters

function limitText(objTextBox, maxCharacters)
{
    if(objTextBox.innerText.length >= maxCharacters)
    {
    if((event.keyCode >= 32 && event.keyCode <=126) || (event.keyCode >= 128 && event.keyCode <= 254))
    {
        event.returnValue = false;
    }
    }
}
<asp:TextBox ID="txtDescription" runat="server" TextMode="MultiLine" Rows="6" Columns="80" OnKeyDown="javascript:limitText(this,1024)" ></asp:TextBox>

Code to deselect all the items of the Listbox

<asp:ListBox ID="lbTopics" runat="server" Width="200px" SelectionMode="Multiple" onclick="javascript:listselection();" CssClass="normalFields"></asp:ListBox>

function listselection()
{
    var listitem=document.getElementById("<%=lbTopics.ClientID %>");
    if(listitem.options[0].selected)
    {
        listitem.selectedIndex = -1;
    }
}

How to download files from any website to local disk.

The simply way how to download file is to use WebClient class and its method DownloadFile. This method has two parameters, first is the url of the file you want to download and the second parameter is path to local disk to which you want to save the file.

Download File Synchronously

The following code shows how to download file synchronously. This method blocks the main thread until the file is downloaded or an error occur (in this case the WebException is thrown).
using System.Net;

WebClient webClient = new WebClient();
webClient.DownloadFile("http://mysite.com/myfile.txt", @"c:\myfile.txt");

Download File Asynchronously

To download file without blocking the main thread use asynchronous method DownloadFileA­sync. You can also set event handlers to show progress and to detect that the file is downloaded.

private void btnDownload_Click(object sender, EventArgs e)
{
  WebClient webClient = new WebClient();
  webClient.DownloadFileCompleted += new AsyncCompletedEventHandler(Completed);
  webClient.DownloadProgressChanged += new DownloadProgressChangedEventHandler(ProgressChanged);
  webClient.DownloadFileAsync(new Uri("http://mysite.com/myfile.txt"), @"c:\myfile.txt");
}

private void ProgressChanged(object sender, DownloadProgressChangedEventArgs e)
{
  progressBar.Value = e.ProgressPercentage;
}

private void Completed(object sender, AsyncCompletedEventArgs e)
{
  MessageBox.Show("Download completed!");
}

Note: Although you use asynchronous method, it can block the main thread for a while. It's because before the async download itself, it checks the DNS name (in this case „mysite.com“) and this check is done internally by blocking function. If you use directly IP instead of domain name, the DownloadFileAsync method will be fully asynchronous.

Code to upload or save document and display them in Gridview with a Hyperlink to download

<asp:FileUpload ID="flUpload" runat="server" />&nbsp;(Word/PDF/Excel formats only.)
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click"/>

<asp:GridView ID="grdResults" DataKeyNames="doc_id" OnRowCommand="grdResults_RowCommand">
<Columns>
<asp:ButtonField ButtonType="Link" DataTextField="name" CommandName="Download" HeaderText="Document Name" />
</Columns>
</asp:GridView>

protected void btnSave_Click(object sender, EventArgs e)
{
if (flUpload.HasFile)
    {
    if (flUpload.PostedFile.ContentLength < 4194304)//Less than 4MB
    {
        string filePath = flUpload.PostedFile.FileName;
        string filename = Path.GetFileName(filePath);
        string ext = Path.GetExtension(filename);
        string contenttype = String.Empty;
        //Set the contenttype based on File Extension
        switch (ext)
        {
        case ".doc":
            contenttype = "application/vnd.ms-word";
            break;
        case ".docx":
            contenttype = "application/vnd.ms-word";
            break;
        case ".xls":
            contenttype = "application/vnd.ms-excel";
            break;
        case ".xlsx":
            contenttype = "application/vnd.ms-excel";
            break;
        case ".pdf":
            contenttype = "application/pdf";
            break;
        }
        if (contenttype != String.Empty)
        {
        Stream fs = flUpload.PostedFile.InputStream;
        BinaryReader br = new BinaryReader(fs);
        byte[] myByte = br.ReadBytes((Int32)fs.Length);
        //Write your code to save in DB
        }
        else
        {
        lblError.ForeColor = System.Drawing.Color.Red;
        lblError.Text = "File format not recognised." + " Upload Word/PDF/Excel formats only.";
        return;
        }
    }
    else
    {
        lblError.ForeColor = System.Drawing.Color.Red;
        lblError.Text = "File size can not be greater than 4MB.";
        return;
    }
    }
}
protected void grdResults_RowCommand(object sender, GridViewCommandEventArgs e)
{
    if (e.CommandName == "Download")
    {
        string fileName = string.Empty;
        int index = Convert.ToInt32(e.CommandArgument);
        GridViewRow row = grdResults.Rows[index];
        int documentID = Convert.ToInt32(grdResults.DataKeys[index].Value);
        DataView dv = Util.GetDataViewFaqDocs(documentID);//Get data from DB
        if (dv.ToTable().Rows.Count > 0)
        {
        byte[] fileData = (byte[])dv.ToTable().Rows[0]["content"];
        Response.ClearContent();
        Response.AddHeader("Content-Disposition", "attachment; filename=" + dv.ToTable().Rows[0]["name"].ToString());
        BinaryWriter bw = new BinaryWriter(Response.OutputStream);
        bw.Write(fileData);
        bw.Close();
        Response.ContentType = dv.ToTable().Rows[0]["content_type"].ToString();
        Response.End();
        }               
    }
}