Difference Between STUFF() and Replace() in SqlServer

1. STUFF()

This function can be used for delete a certain length of the string and insert a new string in the deleted place.

Syntax

Code:
STUFF ( InputString, start, length, ReplacedString )

STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)

String - String to be overwritten
StartPos - Starting Position for overwriting
LengthofReplaceChar - Length of replacement string
ReplaceString - String to overwrite


Example

Code:

Select STUFF ("this is a test", 7, 2, "was")

-- Result - this was a test


2. REPLACE()

This function replaces all the occurrences of a string expression with a new string within an input string.

Syntax:

Code:
REPLACE ( InputString , StringToReplace , NewString )

REPLACE (String, StringToReplace,StringTobeReplaced)

String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite



Example,


Code:
Select REPLACE ("This is a test and it is successful!","is","was")

 Result :
This was a test and it was successful!

EX:

Both STUFF and REPLACE are used to replace characters in a string.

select replace('abcdef','ab','xx')
Result:   xxcdef
select replace('defdefdef','def','abc')
Result :   abcabcabc
We cannot replace a specific occurrence of "def" using REPLACE.

select stuff('defdefdef',4, 3,'abc')
Result:  defabcdef

where 4 is the character to begin replace from and 3 is the number of characters to replace.

sqlserver interview questions

1.What is difference between Clustered and Non Clustered index?

There are two Types of Index available in Sql Server:

1) Clustered Index
2) Non Clustered Index

Index is used for increase the performance application.
1) Clustered Index sequential order .primary key default clustered index. each table only once clustered index.

2) unique key default non clustered index. There 256 non clustered index in each table.


2.how to find duplicates in a table in sql server?

This query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

yield keyword in c#

With yield keyword, the control moves from the caller to source and from the source back to the caller to and fro.

yield statement can be used in two forms

yield return <expression>;
yield break;

yield return statement returns each element at at time.
Yield return allows you to run custom iteration without temp collection.

Lets take an example to understand in more detail

Function to return RandomNumbers using List

static IEnumerable<int> GetRandomNumbers(int count)
{
    Random rand = new Random();
    List<int> ints = new List<int>(count);
    for (int i = 0; i < count; i++)
    {
        ints.Add(rand.Next());
    }
    return ints;
}

Same function using return yield without using any temp collection

using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            foreach (int i in GetRandomNumbers(10))
                Console.WriteLine(i);
        }      

        static IEnumerable<int> GetRandomNumbers(int count)
        {
            Random rand = new Random();          
            for (int i = 0; i < count; i++)
            {
                yield return rand.Next();
            }          
        }
    }
}
OUTPUT:

1191885058
2054102666
1386021465
1841172832
1017760911
2105028861
549081689
1595410548
585549736
381274724

See, in the above examples, both are performing the same thing, but in the first example we are creating temp collection "list" but in the second example with yield there is no need create any temp collection

yield break statement is used to break the iteration. break statement is used to terminate the loop but yield break is used to return from the method.

static void Main(string[] args)
{          
    foreach (int i in GetRandomNumbers(10))
        Console.WriteLine(i);
    System.Threading.Thread.Sleep(5000);          
}

static IEnumerable<int> GetRandomNumbers(int count)
{
    Random rand = new Random();
    for (int i = 0; i < count; i++)
    {
        yield return rand.Next();
        yield break;
    }
}
OUTPUT:

41820105

Datalist Paging in asp.net

<table>              
        <tr>
            <td>
           
            <div class="youtube_thumb_handler1">
                <asp:DataList ID="DLVideos" runat="server" RepeatColumns="3" RepeatDirection="Horizontal"
                    CellPadding="0" CellSpacing="0">
                    <ItemTemplate>                                    
                          <iframe id="iframe_Trailer" runat="server" scrolling="no" marginwidth="0" marginheight="0"
                           src='<%#Eval("VideoURL") %>' height="100" width="150" frameborder="0"></iframe>
                         
                              <div class="youtube_thumb_title1"> <a id="A1" href='<%#Eval("Sno","videoDetails.aspx?sno={0}") %>' runat="server">
                                        <%#Eval("Title")%></a> </div>
                       
                       
                    </ItemTemplate>
                </asp:DataList>
                </div>
               
                <table align="center">
                        <tr>
                            <td>
                            <br />

                                <asp:Label ID="lblCurrentPage" runat="server" Visible="true" CssClass="y_pagetext"></asp:Label>
                                <asp:Button ID="cmdPrev" runat="server" CssClass="y_next" Text=" Previous " OnClick="cmdPrev_Click" />&nbsp;&nbsp;&nbsp;
                                <asp:Button ID="cmdNext" runat="server" CssClass="y_next" Text=" Next " OnClick="cmdNext_Click" />
                            </td>
                        </tr>
               </table>                          
            </td>
        </tr>      
    </table>



  protected void Page_Load(object sender, EventArgs e)
    {
        BindVideos();
    }
    protected void BindVideos()
    {
        try
        {
            ds = objbll.Get_Related_Content("AllVideos", "");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                PagedDataSource PagedData = new PagedDataSource();
                dv = ds.Tables[0].DefaultView;
                PagedData.DataSource = dv;
                PagedData.AllowPaging = true;
                PagedData.PageSize = 12;

                PagedData.CurrentPageIndex = CurrentPage;
                lblCurrentPage.Text = "Page:" + (CurrentPage + 1).ToString() + " Of " + PagedData.PageCount.ToString();
                cmdPrev.Enabled = !PagedData.IsFirstPage;
                cmdNext.Enabled = !PagedData.IsLastPage;

                DLVideos.DataSource = PagedData;
                DLVideos.DataBind();
            }
            else
            {
                DLVideos.DataSource = null;
                dv = null;
                DLVideos.DataBind();
            }
        }
        catch
        {
        }
    }
    public int CurrentPage
    {

        get
        {
            object o = this.ViewState["_CurrentPage"];

            if (o == null)

                return 0;
            else

                return (int)o;
        }

        set
        {
            this.ViewState["_CurrentPage"] = value;

        }
    }
    protected void cmdPrev_Click(object sender, EventArgs e)
    {
        try
        {
            CurrentPage -= 1;
            BindVideos();
        }
        catch
        {

        }
    }
    protected void cmdNext_Click(object sender, EventArgs e)
    {
        try
        {
            CurrentPage += 1;

            BindVideos();
        }
        catch
        {

        }
    }

Ajax ToggleButtonExtender used in gridview in asp.net

<asp:TemplateField HeaderText="Status" ItemStyle-HorizontalAlign="Center">
                                <ItemTemplate>
                                    <center>
                                        <asp:CheckBox ID="chkboxStatus" Checked='<%# Convert.ToBoolean(Eval("Status").ToString())%>'
                                            runat="server" AutoPostBack="true" OnCheckedChanged="chkboxStatus_CheckedChanged" />
                                        <br />
                                        <ajaxToolkit:ToggleButtonExtender ID="ToggleButtonExtender1" runat="server" TargetControlID="chkboxStatus"
                                            ImageWidth="50" ImageHeight="50" UncheckedImageUrl="~/images/unchecked.jpg" CheckedImageUrl="~/images/tick.png"
                                            CheckedImageAlternateText="Check" UncheckedImageAlternateText="UnCheck">
                                        </ajaxToolkit:ToggleButtonExtender>
                                    </center>
                                </ItemTemplate>
                            </asp:TemplateField>


protected void chkboxStatus_CheckedChanged(object sender, EventArgs e)
    {
        GridViewRow gvrow;
        gvrow = ((CheckBox)sender).Parent.Parent as GridViewRow;
        int gindex = gvrow.RowIndex;
        Label lblHeadLineID = (Label)gridNews.Rows[gindex].FindControl("lblHeadLineID");
        CheckBox chkboxStatus = (CheckBox)gridNews.Rows[gindex].FindControl("chkboxStatus");
        string news_status = null;

        if (chkboxStatus.Checked)
        {
            news_status = "True";
        }
        else
        {
            news_status = "False";
        }

        objbll.Update_News_status(lblHeadLineID.Text, news_status);

    }

Check EmailID Already exists or not in asp.net

<script language="javascript" type="text/javascript">

    function checkuserEmail(txt) {
        var valuseremail = txt;
        if (valuseremail != "") {

            WebService.check_ContentEditor_userEmail(valuseremail, OnSucceeded, OnFailed);
        }

    }
    function OnSucceeded(result, userContext, methodName) {

        var userarray = result;
        document.getElementById('<%=lblresult.ClientID %>').innerHTML = userarray;
        if (userarray != "<font color='green'>EmaiID Available,Please proceed !</font>") {
            document.getElementById('<%=txtEmail.ClientID %>').value = "";
        }


    }
    function OnFailed(error, userContext, methodName) {

        alert("An Error Occured During Check Your EmailID. Please Try After Some Time!!!");
    }

</script>


<asp:TextBox ID="txtEmail" runat="server" CssClass="serachtext1" onblur="checkuserEmail(this.value);"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RFV_txtEmail" runat="server" ControlToValidate="txtEmail"
                    Display="None" SetFocusOnError="true" ErrorMessage="Enter EmailID" ValidationGroup="Insert"></asp:RequiredFieldValidator>
                <ajaxToolkit:ValidatorCalloutExtender ID="VCE3_txtEmail" CssClass="customCalloutStyle"
                    runat="server" WarningIconImageUrl="~/images/ajaxerror.png" CloseImageUrl="~/images/ajaxclose.png"
                    TargetControlID="RFV_txtEmail">
                </ajaxToolkit:ValidatorCalloutExtender>
                <asp:RegularExpressionValidator ID="REV_txtEmail" runat="server" ControlToValidate="txtEmail"
                    Display="None" SetFocusOnError="true" ErrorMessage="Enter valid EmailID" ValidationGroup="Insert"
                    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
                <ajaxToolkit:ValidatorCalloutExtender ID="VCE2_txtEmail" CssClass="customCalloutStyle"
                    runat="server" WarningIconImageUrl="~/images/ajaxerror.png" CloseImageUrl="~/images/ajaxclose.png"
                    TargetControlID="REV_txtEmail">
                </ajaxToolkit:ValidatorCalloutExtender>
                <asp:Label ID="lblresult" runat="server" Font-Bold="False" ForeColor="Red" Text=""></asp:Label>

[WebMethod]

    public string check_ContentEditor_userEmail(string emailid)

    {

        try

        {

            BAL.TravelBAL objtravelbal = new BAL.TravelBAL();  

            DataSet ds = new DataSet();

            ds = objtravelbal.Get_CityContentEditor_ByEmail(emailid);

            string result = "";

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)

            {

                result = "<font color='red'>EmaiID Already Exist,Please Enter Another EmailID!</font>";

            }

            else

            {

                result = "<font color='green'>EmaiID Available,Please proceed !</font>";

            }

            return result;

        }

        catch (Exception ex)

        {

            string result = "Error occur during checking your EmaiID";

            return result;

        }

    }

Ajax AsyncFileUpload control using javascript in asp.net

In .aspx Page:


<script type="text/javascript">
        function UploadCompleted(sender, args) {
            var filename = document.getElementById('ctl00_ContentPlaceHolder1_Label1').innerHTML;
            var imgDisplay = $get("imgDisplay");
            $get("<%=divImagetag.ClientID %>").style.display = 'block';
            imgDisplay.src = "UploadedImages/" + filename;
            $get("<%=Label1.ClientID %>").innerHTML = "Successfully uploaded!!!";
            clearContents();
        }
        function UploadError(sender, args) {
            $get("<%=Label1.ClientID %>").innerHTML = "File upload failed.";
            clearContents();
            $get("<%=divImagetag.ClientID %>").style.display = 'none';
        }
        function clearContents() {
            var span = $get("<%=AsyncFileUpload1.ClientID%>");
            var txts = span.getElementsByTagName("input");
            for (var i = 0; i < txts.length; i++) {
                if (txts[i].type == "text") {
                    txts[i].value = "";
                }
            }
        }
     
    </script>


 <table border="0" cellpadding="5" cellspacing="5">
            <tr>
                <td colspan="2">
                    <asp:Label ID="lblMessage" runat="server" Font-Bold="true" ForeColor="Red"></asp:Label>
                </td>
            </tr>
            <tr>
                <td>
                    <strong>Image Upload </strong>
                </td>
                <td>
                    <ajaxToolkit:AsyncFileUpload ID="AsyncFileUpload1" runat="server" Width="400px" UploaderStyle="Modern"
                        CompleteBackColor="White" UploadingBackColor="#CCDDEE" ThrobberID="inProgress"
                        OnClientUploadError="UploadError" OnClientUploadComplete="UploadCompleted" OnUploadedFileError="AsyncFileUpload1_UploadedFileError"
                        OnUploadedComplete="AsyncFileUpload1_UploadedComplete" />
                    <asp:Image ID="inProgress" runat="server" ImageUrl="~/inProgress.gif" />
                    <br />
                    <asp:Label ID="Label1" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
        <div id="divImagetag" runat="server" align="center" style="display: none">
            <img id="imgDisplay" alt="" src="" height="100px" width="150px" />
        </div>

In aspx.cs page:


  protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
    {
        Random random = new Random();
        int randomNumber = random.Next(0, 100);
        if (AsyncFileUpload1.HasFile)
        {
            string fileextension = Path.GetExtension(e.FileName);
            if (fileextension == ".jpeg" || fileextension == ".gif" || fileextension == ".png" || fileextension == ".jpg" || fileextension == ".bmp")
            {
                string filename = randomNumber + fileextension;
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "sample", "top.$get(\"" + Label1.ClientID + "\").innerHTML = '" + filename + "';", true);
                string path = Server.MapPath(("~/UploadedImages/") + filename);
                AsyncFileUpload1.SaveAs(path);
            }
            else
            {
                lblMessage.Text = "We support only images of type jpg, gif,jpeg,bmp and png";
            }
        }
        else
        {
            lblMessage.Text = "Please Upload Image";
        }
    }
 protected void AsyncFileUpload1_UploadedFileError(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
    {
        lblMessage.Text = "Could not upload file!";
    }