Wednesday, 26 June 2013

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.

Tuesday, 25 June 2013

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

Monday, 24 June 2013

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);

    }

Friday, 21 June 2013

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;
        }
    }

Thursday, 20 June 2013

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!";
    }

Tuesday, 18 June 2013

Validate URL Address in ASP.NET

In aspx page:

<table>
        <tr>
            <td colspan="2" align="center">
                <asp:Label ID="lblURL" runat="server" Font-Bold="true" ForeColor="Red"></asp:Label>
            </td>
        </tr>
        <tr>
            <td>
                <strong>Enter URL:</strong>
            </td>
            <td>
                <asp:TextBox ID="txtUrlAddress" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RFV_txtUrlAddress" runat="server" ControlToValidate="txtUrlAddress"
                    Display="None" SetFocusOnError="true" ErrorMessage="Please Enter URL" ValidationGroup="Submitgp"></asp:RequiredFieldValidator>
                <ajaxToolkit:ValidatorCalloutExtender ID="VCE1_txtUrlAddress" runat="server" TargetControlID="RFV_txtUrlAddress">
                </ajaxToolkit:ValidatorCalloutExtender>
                <asp:RegularExpressionValidator ID="REV_txtUrlAddress" runat="server" ControlToValidate="txtUrlAddress"
                    ValidationExpression="http(s)?://([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?" ValidationGroup="Submitgp"
                    Display="None" ErrorMessage="Please Enter Valid URL" SetFocusOnError="true"></asp:RegularExpressionValidator>
                <ajaxToolkit:ValidatorCalloutExtender ID="VCE2_txtUrlAddress" runat="server" TargetControlID="REV_txtUrlAddress">
                </ajaxToolkit:ValidatorCalloutExtender>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSubmit" runat="server" Text="Submit" ValidationGroup="Submitgp"
                    OnClick="btnSubmit_Click" />
            </td>
        </tr>
    </table>

In aspx.cs code:

 protected void btnSubmit_Click(object sender, EventArgs e)
    {
        lblURL.Text = "Your URL is:" + txtUrlAddress.Text;
    }

JOINS IN SQL SERVER

create tables and insert data into tables

CREATE TABLE dbo.Table1
(ID INT, Value VARCHAR(10))
INSERT INTO dbo.Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'


CREATE TABLE dbo.Table2
(ID INT, Value VARCHAR(10))
INSERT INTO dbo.Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'



Basic of joins:

INNER JOIN

This join returns rows when there is at least one match in both the tables.


Select * from Table1 t1 INNER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 inner join dbo.Table2 t2 on t1.ID=t2.ID


OUTER JOIN:

There are three different Outer Join methods.

LEFT OUTER JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

select * from Tabel1 t1 LEFT OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 left join dbo.Table2 t2 on t1.ID=t2.ID



RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

Select * from Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 right join dbo.Table2 t2 on t1.ID=t2.ID

FULL OUTER JOIN

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

select * from Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.col1=t2.col1

Ex: select t1.*,t2.* from dbo.Table1 t1 Full outer join dbo.Table2 t2 on t1.ID=t2.ID

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

select * from Table1 t1 cross JOIN Table2 t2

Ex: select t1.*,t2.* from dbo.Table1 t1 cross join dbo.Table2 t2

Additional Notes related to JOIN:

/* LEFT JOIN - WHERE NULL */
This query using Outer Join and WHERE clause in join.
select * from Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.col1=t2.col1 WHERE t2.Col1 IS NULL

 Ex: select * from dbo.Table1 t1 left join dbo.Table2 t2 on t1.ID=t2.ID where t2.ID is null

/* RIGHT JOIN - WHERE NULL */
This query also using Outer Join and WHERE clause in join.

select * from Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.col1=t2.col2 WHERE t1.col1 IS NULL

Ex:select t1.*,t2.* from dbo.Table1 t1 right join dbo.Table2 t2 on t1.ID=t2.ID where t1.ID is null


/* OUTER JOIN - WHERE NULL */

This join will give all the results that were not present in Inner Join.

select * from Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.col1=t2.col1 WHERE t1.col1 IS NULL OR t2.col1 IS NULL

Ex:select t1.*,t2.* from dbo.Table1 t1 full outer join dbo.Table2 t2 on t1.ID=t2.ID where t1.ID is null or t2.ID is null

select statement for retriving only column names in a table

Ex1:

SELECT  *  FROM yourTableName WHERE 1=0

Ex2:


select
a.[name]
from
sys.columns a join sys.tables b on
a.object_id = b.object_id
where
b.[name] = 'YourTableName'

Friday, 14 June 2013

Remove Delete Duplicate Records Or Rows-Sql Server




First Method:

Query to delete duplicate rows.

DELETE FROM dbo.Employees
WHERE ID NOT IN (SELECT MIN(ID)
FROM dbo.Employees GROUP BY FirstName,Department)


Second method:

Query to delete duplicate rows.


WITH DuplicateRecords AS
(
SELECT *,row_number() OVER(PARTITION BY FirstName,Department ORDER BY

FirstName)
AS RowNumber FROM dbo.Employees
)
DELETE FROM DuplicateRecords WHERE RowNumber>1

This should remove all duplicate records from table.


Third Method:

Query to delete duplicate rows.


SELECT DISTINCT * INTO TempTable FROM dbo.Employees
GROUP BY FirstName,Department
HAVING COUNT(FirstName) > 1

DELETE dbo.Employees WHERE FirstName
IN (SELECT FirstName FROM TempTable)

INSERT dbo.Employees SELECT * FROM TempTable
DROP TABLE TempTable


This should remove all duplicate records from table.






Reset Identity Column Value In Sql Server Table

How To Reset Identity Column In Sql Server Table To Start Auto Increment Or Seed From Desired Number

If we delete All data from Sql Server table having Identity Column with Identity Seed as 1 and insert new data Identity column value doesn't get reset and it starts from last number.

If table were having 10 records and we insert new records after deleting all records in table, identity column would start from 11 if identity seed is 1.

we can use below mentioned command to reset Identity Column.


DBCC CHECKIDENT('YouTableName', RESEED, 0)

Check UserName Availble or Not in asp.net

In Default.aspx:

  <style type="text/css">
        .waitingdiv
        {
            background-color: #F5F8FA;
            border: 1px solid #5A768E;
            color: #333333;
            font-size: 93%;
            margin-bottom: 1em;
            margin-top: 0.2em;
            padding: 8px 12px;
            width: 8.4em;
        }
    </style>
     <script type="text/javascript">
         Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(BeginRequestHandler);
         function BeginRequestHandler(sender, args) {
             var state = document.getElementById('loadingdiv').style.display;
             if (state == 'block') {
                 document.getElementById('loadingdiv').style.display = 'none';
             } else {
                 document.getElementById('loadingdiv').style.display = 'block';
             }
             args.get_postBackElement().disabled = true;
         }
</script>
    <div>
        <asp:UpdatePanel ID="UpdatePanel" runat="server">
            <ContentTemplate>
                <table>
                    <tr>
                        <td>
                            <strong>UserName: </strong>
                        </td>
                        <td>
                            <asp:TextBox ID="txtUserName" runat="server" AutoPostBack="true" CssClass="serachtext"
                                ontextchanged="txtUserName_TextChanged"></asp:TextBox>
                        </td>
                        <td>
                            <div id="CheckUserName" runat="server" visible="false">
                                <asp:Image ID="imgstatus" runat="server" Height="17px" Width="17px" />
                                <asp:Label ID="lblstatus" runat="server"></asp:Label>
                            </div>
                        </td>
                    </tr>
                </table>
                <div class="waitingdiv" id="loadingdiv" style="display: none; margin-left: 5.3em">
                <img src="LoadingImage.gif" alt="Loading" />Please Wait.......
                </div>
            </ContentTemplate>
        </asp:UpdatePanel>
    </div>

In Default.aspx.cs:

  protected void txtUserName_TextChanged(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(txtUserName.Text))
        {
            ds = objbal.checkUserName(txtUserName.Text);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                CheckUserName.Visible = true;
                imgstatus.ImageUrl = "NotAvailable.jpg";
                lblstatus.Text = "UserName Alredy Exists,choose Another One";
                System.Threading.Thread.Sleep(2000);
            }
            else
            {
                CheckUserName.Visible = true;
                imgstatus.ImageUrl = "Icon_Available.gif";
                lblstatus.Text = "UserName Available";
                System.Threading.Thread.Sleep(2000);
            }

        }
        else
        {

            CheckUserName.Visible = false;
        }
    }

Stored Procedure:
create procedure dbo.checkuserId
(
@UserId varchar(50)
)
as
begin
select * from dbo.Cartoon_Admin_Login where UserId=@UserId
end


Thursday, 13 June 2013

Clear controls in asp.net using javascript

   <script type="text/javascript" language="javascript">
        function clearAllServerFields() {

            //Code for accessing all dropdown lists and listboxes in an aspx page

            var select = document.getElementsByTagName("select");
            var length = select.length;
            for (i = 0; i < length; i++) {
                for (var j = 0; j < select[i].length; j++) {
                    select[i][j].selected = false;
                }
            }

            var elements = document.getElementsByTagName("input");
            for (var i = 0; i < elements.length; i++) {
                if (elements[i].disabled == false) {
                    //Code for accessing all textboxes in an aspx page

                    if (elements[i].type == "text") {
                        elements[i].value = "";
                    }
                    //Code for accessing all radio buttons in an aspx page

                    if (elements[i].type == "radio") {
                        elements[i].checked = false;
                    }
                    //Code for accessing all checkboxes in an aspx page

                    if (elements[i].type == "checkbox") {
                        elements[i].checked = false;
                    }
                }
            }
        }

    </script>


queries of date functions in sqlserver



weekstartdate and weekenddate of current week:

select convert(varchar,DATEADD(dd, -(DATEPART(dw, GETDATE())-1), GETDATE()),103) as [WeekStart]

select CONVERT(varchar, DATEADD(dd, 7-(DATEPART(dw, GETDATE())), GETDATE()),103) as [WeekEnd]

--India Time=UTC+5:30

select convert(varchar,DATEADD(dd, -(DATEPART(dw, DATEADD(mi,30,DATEADD(hh,5,getutcdate())))-1), DATEADD(mi,30,DATEADD(hh,5,getutcdate()))),103) as [WeekStart]

select CONVERT(varchar, DATEADD(dd, 7-(DATEPART(dw, DATEADD(mi,30,DATEADD(hh,5,getutcdate())))), DATEADD(mi,30,DATEADD(hh,5,getutcdate()))),103) as [WeekEnd]


monthstartdate and monthenddate of currentmonth :

select convert(varchar,dateadd(d,-(day(getdate()-1)),getdate()),103) as [monthfirstDate]

select  convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),103)as [monthlastDate]

--India Time=UTC+5:30

select convert(varchar,dateadd(d,-(day(DATEADD(mi,30,DATEADD(hh,5,getutcdate()))-1)),DATEADD(mi,30,DATEADD(hh,5,getutcdate()))),103) as [monthfirstDate]

select  convert(varchar,dateadd(d,-day(DATEADD(mi,30,DATEADD(hh,5,getutcdate()))),dateadd(m,1,DATEADD(mi,30,DATEADD(hh,5,getutcdate())))),103)as [monthlastDate]

Wednesday, 12 June 2013

C# Basics And Interview Questions

http://www.gointerviews.com/top-50-c-sharp-interview-questions-answers/


http://www.java2s.com/Tutorial/CSharp/CatalogCSharp.htm

stored procedure for counting Records


Create Procedure [dbo].[Buddy_DashBoard]
as
/*
exec Buddy_DashBoard
*/
begin


declare @Buddy_Total_Android int,@Buddy_Month_Android int,@Buddy_Week_Android int,@Buddy_Day_Android int

---To Get Android Users Data for dashBoard
select @Buddy_Total_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where BUD.User_From='Android'
select @Buddy_Month_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where MONTH(BUD.CreatedDate)=MONTH(dateadd(hh,5,dateadd(mi,30,getutcdate()))) and YEAR(BUD.CreatedDate)=YEAR(dateadd(hh,5,dateadd(mi,30,getutcdate()))) and BUD.User_From='Android'
select @Buddy_Week_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where CONVERT(date,BUD.CreatedDate,103) between CONVERT(date,dateadd(d,-7,dateadd(hh,5,dateadd(mi,30,getutcdate()))),103) and CONVERT(date,dateadd(hh,5,dateadd(mi,30,getutcdate()))) and BUD.User_From='Android'
select @Buddy_Day_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where CONVERT(date,BUD.CreatedDate,103)=CONVERT(date,dateadd(hh,5,dateadd(mi,30,getutcdate())),103) and BUD.User_From='Android'


select @Buddy_Day_Android as Buddy_Day_Android,@Buddy_Week_Android as Buddy_Week_Android,@Buddy_Month_Android as Buddy_Month_Android,@Buddy_Total_Android as Buddy_Total_Android

end

Tuesday, 4 June 2013

Add Youtube Videos in asp.net

 <asp:TextBox ID="txtvideourl" onclick="hide_video();" runat="server" Width="258px"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfv_txtvideourl" runat="server" ControlToValidate="txtvideourl"
                        ErrorMessage="Please Enter Video URL" Display="None" SetFocusOnError="true" ValidationGroup="submitvideo"></asp:RequiredFieldValidator>
                    <ajaxToolkit:ValidatorCalloutExtender ID="vce_txtvideourl" runat="server" TargetControlID="rfv_txtvideourl">
                    </ajaxToolkit:ValidatorCalloutExtender>


 protected void btnVideo_Click(object sender, EventArgs e)
    {

        string videourl = string.Empty, filename = string.Empty;
        try
        {
            if (txtvideourl.Text.Contains("http://youtu.be"))
            {
                videourl = txtvideourl.Text;
                string[] arr = new string[5];
                arr = videourl.Split('/');
                filename = arr[3];
                if (ObjBll.Opertaions_on_Videos("", txtVideoName.Text, filename, "True", "Insert"))
                {
                    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "Warning", "alert(' Submited Successfully..')", true);
                    iframe_Video.Visible = true;
                    iframe_Video.Attributes["src"] = "http://www.youtube.com/embed/" + filename;
                    bind_data();
                    txt_Clear();
                }
            }
            else
            {
                txtvideourl.Focus();
                lbl_urlmsg.Text = "Enter Valid URL";
            }
        }
        catch (Exception ex)
        {
            lblMsg.Text = ex.Message.ToString();
        }

Saturday, 1 June 2013

How to call java script function from code behind file in asp.net




Example:

 Declare a JavaScript function in the head tag of your design file as shown below:

<head runat="server">
    <title>Calling JavaScript From CodeBehind in asp.net file</title>
    <script type="text/javascript">
        function MyFunction() {
            alert('Demonstration Successfull..');
        }
    </script>
</head>

In order to call it from code behind, use the following code in your Page_Load

protected void Page_Load(object sender, EventArgs e)
{
    if (!ClientScript.IsStartupScriptRegistered("alert"))
    {
        Page.ClientScript.RegisterStartupScript(this.GetType(),
            "alert", "MyFunction();", true);
    }
}