Temporary Table -SQL Server

This example Shows you how to create temporary table in SqlServer, Insert values in them, Check their existence and finally Drop them.

Create Temp Table

This one is declared as Global Temporary Table

create table ##YourTempTable (YourTableField1 bigint,YourTableField2 varchar(50))

This one is declared as Local Temporary Table

create table #YourTempTable (YourTableField1 bigint,YourTableField2 varchar(50))

Insert Values in Your Temporary Table

insert into ##YourTempTable 

SELECT * FROM  YourDesiredTable

Check Existence of Temporary table

if exists(select * from tempdb.dbo.sysobjects where ID=object_ID(N'tempdb..##YourTempTable  '))

Check Existence n Then Drop Temporary Table

if exists(select * from tempdb.dbo.sysobjects where ID=object_ID(N'tempdb..##YourTempTable  '))

BEGIN

DROP TABLE ##YourTempTable 

END

Show Total in GridView Footer

Design:

<asp:TemplateField HeaderText="Total Pcs">

 <ItemTemplate>

          <asp:Label ID="lbltotpcs" runat="server"  Text='<%# Bind("totpcs") %>'></asp:Label>

 </ItemTemplate>

 <FooterTemplate>

        Total : <asp:Label ID="lbltotqty" runat="server" Text="Label"></asp:Label>

</FooterTemplate>

</asp:TemplateField>

Populate the RowDataBound event of Gridview where you have to do the main calculation.

int totqty=0;

protected void gvCrate_RowDataBound(object sender, GridViewRowEventArgs e)

    {

        if (e.Row.RowType == DataControlRowType.DataRow)

        {

            Label totpcs = (Label)e.Row.FindControl("lbltotpcs");

            totqty = totqty + (Convert.ToInt32(totpcs.Text));

        }

        if (e.Row.RowType == DataControlRowType.Footer)

        {

            ((Label)e.Row.FindControl("lbltotqty")).Text = totqty.ToString();

            TxtTotalpcs.Value = totqty.ToString();

        }

    }

NOTE:

Enable  "ShowFooter" as True in Gridview's property.

Add Month To any Given Date

using System.Globalization;

protected void DropEMISchedule_SelectedIndexChanged(object sender, EventArgs e)

{

DateTime dt;

int calcy = Convert.ToInt32(DropEMISchedule.SelectedValue) * Convert.ToInt32(txttenure.Text);

if (DateTime.TryParseExact(txtEMIStartDate.Text, "dd/MM/yyyy",new CultureInfo("en-US"),  System.Globalization.DateTimeStyles.None, out dt))

   {

     TextBoxend.Text = "";

     DateTime dtshow = dt.Date.AddMonths(calcy);

  

     TextBoxend.Text = (dtshow).ToString("dd/MM/yyyy");

    }

 }

error code 0x8007045D

Meaning of Error 0x8007045d is "ERROR_IO_DEVICE" i.e. "The request could not be performed because of an I/O device error. There are various cause for this error like :

1.  May be the brand of the disc is incompatible with the burner.
2. May be the disc is defective.Use another one .
3. May be the burner have malfunctioned.

In summary, "error code 0x8007045D" simply means the media is bad.

Convert Number to Text

Function below can be used in converting any number into text.

  public string NumberToText(int number)

    {

        if (number == 0) return "Zero";

        if (number == -2147483648) return "Minus Two Hundred and Fourteen Crore Seventy Four Lakh     Eighty Three Thousand Six Hundred and Forty Eight";

        int[] num = new int[4];

        int first = 0;

        int u, h, t;

        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        if (number < 0)

        {

            sb.Append("Minus ");

            number = -number;

        }

        string[] words0 = { "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine " };

        string[] words1 = { "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen " };

        string[] words2 = { "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety " };

        string[] words3 = { "Thousand ", "Lakh ", "Crore " };

        num[0] = number % 1000; // units

        num[1] = number / 1000;

        num[2] = number / 100000;

        num[1] = num[1] - 100 * num[2]; // thousands

        num[3] = number / 10000000; // crores

        num[2] = num[2] - 100 * num[3]; // lakhs

        for (int i = 3; i > 0; i--)

        {

            if (num[i] != 0)

            {

                first = i;

                break;

            }

        }

        for (int i = first; i >= 0; i--)

        {

            if (num[i] == 0) continue;

            u = num[i] % 10; // ones

            t = num[i] / 10;

            h = num[i] / 100; // hundreds

            t = t - 10 * h; // tens

            if (h > 0) sb.Append(words0[h] + "Hundred ");

            if (u > 0 || t > 0)

            {

                //if (h > 0 || i == 0) sb.Append("and ");

                if (t == 0)

                    sb.Append(words0[u]);

                else if (t == 1)

                    sb.Append(words1[u]);

                else

                    sb.Append(words2[t - 2] + words0[u]);

            }

            if (i != 0) sb.Append(words3[i - 1]);

        }

        //  sb.Append(" Rupees Only");

        return sb.ToString().TrimEnd();

    }

 And if you want to convert Rupees value in word then one can use this idea.Here lblBUnit contains the bigger unit of any currency and lblSUnit holds the value of smaller unit of any currency.

int i = 0;

            String[] val = lbltotal_amount.Text.Split('.');

            foreach (string s in val)

                i++;

            lbltotal_amountinword.Text = NumberToText(Convert.ToInt32(val[0]));

            if (i == 1)

                lbltotal_amountinword.Text = lbltotal_amountinword.Text + " " + lblBUnit.Text + " Only";

            else

                lbltotal_amountinword.Text = lbltotal_amountinword.Text + " " + lblBUnit.Text + " and " +    NumberToText(Convert.ToInt32(val[1])) + " " + lblSUnit.Text + " Only";

Get returned value from Stored Procedure

This example shows you how to get returned value from Stored Procedure.First of all create a stored procedure which returns a value.In this example of stored procedure it receives a parameter named "uname".
Then its getting the Id of user from function fn_getUserBD_ID and returns that value.

Create PROCEDURE sp_GetBDID

@uname varchar(25)=null

AS

 declare @dbBD_id bigint

BEGIN

    SET NOCOUNT ON;

    Select @dbBD_id=dbo.fn_getUserBD_ID(@uname)

    return @dbBD_id

END

Now to get the value write this code in code behind

  public void GetBDID()

    {

        string st = Session["BDGen"].ToString();

        SqlCommand cmd = new SqlCommand("sp_GetBDID", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@uname", st);

        // Return value as parameter

        SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);

        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        if (con.State == ConnectionState.Closed)

            con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        lblUId.Text = returnValue.Value.ToString();

    }

Serial No in Gridview

<asp:TemplateField>

  <ItemTemplate>

   <table class="style2">

       <tr>

      <td>

        <%#Container.DataItemIndex+1 %>

     </td>

    </tr>

   </table>

 </ItemTemplate>

</asp:TemplateField>

Create, Insert, Search, Delete in DataTable

//Create DataTable and Define its Fields (Cloumns)

DataTable dt = new DataTable();

dt.Columns.Add("ItemId",typeof(int));

dt.Columns.Add("Item",typeof(string));

dt.Columns.Add("Qty",typeof(float));

dt.Columns.Add("Category_Name", typeof(string));

//Fetch Data from DataBase and Insert into DataTable

DataSet dstmp = new DataSet();

query = "SELECT  *  FROM  vw_ItemDet";

SqlDataAdapter adp = new SqlDataAdapter(query, con);

adp.Fill(dstmp);

dt = dstmp.Tables[0];

//Insert Data from WebForm -:

 DataTable dt = (DataTable)ViewState["Dt"];

 DataRow dtdr = dt.NewRow();

 dtdr["ItemId"]=ddlOtherItem.SelectedValue;

 dtdr["Item"]=ddlOtherItem.SelectedItem.Text;

 dtdr["Qty"] = txtQty.Text;

 dt.Rows.Add(dtdr);

//Store values of DataTable in  ViewState for further use.Basically its helpful if you have to use  this table value later.

ViewState["dtable"] = dt;

//Retrieve value from ViewState

DataTable VWTempDTable=new DataTable();

VWTempDTable = (DataTable)ViewState["dtable"];

//Insert Values from one DataTable to Another based on some condition..-:

string cond = "Category_Name='" + ViewState["sad"] + "'";

DataRow[] foundRows;

foundRows = VWTempDTable.Select(cond);

foreach (DataRow row in foundRows)

{

            VWTempSecond.ImportRow(row); ///VWTempSecond is another DataTable

}

//Delete Rows from DataTable -:

VWTempSecond.Rows[Convert.ToInt32(s)].Delete();

Show Value upto Two Decimal Places in Gridview

Show money or float values upto two decimal places using 

String.Format in Gridview.

<%# String.Format("{0:f2}",DataBinder.Eval(Container.DataItem,"Rate")) %>


or

<asp:label id="Label1" runat="server" text='<%# Eval("Amt","{0:f2}")  %>'></asp:label>

Example :

<asp:templatefield headertext="Rate">

<ItemTemplate>

   <table align="right"
style="text-align:
right"><tr><td align="right">

    <asp:Label ID="lblRate"
runat="server"


Text='<%#
String.Format("{0:f2}",DataBinder.Eval(Container.DataItem,"Rate"))
%>' ></asp:Label>

   </td></tr></table>

</ItemTemplate>

</asp:templatefield>

Calculate No of Days Between Two Dates in Sql Server

C Sharp interview questions

C Sharp Interview Questions with Answers

Here I am going to include some important questions asked in recent interview.This is beneficial for both freshers and experienced.By answering these questions You will leave a good impression on interviewer.

So,now no more talks..start reading and digesting.

What is Mutex? 

Mutex is the short form for MUTual EXclusion.

Mutual exclusion refers to the problem of ensuring that no two processes or threads can be in their critical section at the same time. Here, a critical section refers to a period of time when the process accesses a shared resource, such as Shared Memory. The problem of mutual exclusion was first identified and solved by Edsger W. Dijkstra in his seminal 1965 paper titled: Solution of a problem in concurrent programming control.

In C# there is Mutex class defined within System.Threading Namespace

What do You mean by LOCK and Wait?

A Lock is a synchronization mechanism for enforcing limits on access to a resource in an environment where there are many threads of execution. Locks are one way of enforcing concurrency control policies.

A process (or task) may wait on another process to complete its execution. In most systems, a parent process can create an independently executing child process. The parent process may then issue a wait system call, which suspends the execution of the parent process while the child executes. When the child process terminates, it returns an exit status to the operating system, which is then returned to the waiting parent process. The parent process then resumes execution.

Define Static members of the class in the terms of C# -:

Static members belong to whole class rather than to individual object. For example, if you have a static phoneNumber field in your Student class, then there will be the single instance of this field and all the objects of this class will share this single field. Changes made by one object to phone Number will be realized by the other object.

Class Student

{

    Public static int phoneNumber;

    Public int rollNumber;

}

Class Test

{

    Public  static void Main()

    {

        Student  st1=new Student();

        Student st2=new Student();

        St1.rollNumber=3

        St2.rollNumber=5;

        Student.phoneNumber=4929067;

     }

}

Here you can see that the phoneNumber is accessed without any reference to the object but with the name of the class it belongs. Static methods are very useful while programming. In fact, the WriteLine() and ReadLine() methods are static methods of Console Class. Even Main () is declared as static as CLR calls it without making any instance of class. It’s useful when we want to cache data that should be available to all objects of class.

Some precautions:

Don’t put too many static methods in your class as it is against the OO design principles and makes your class less extensible.

You tend to loose a number of OO advantages while using static methods, as static methods can’t be overridden which means it cannot be used polymorphically, something widely used in the OO paradigm of programming.

What DoYou mean by Singleton Approach?

This structural code demonstrates the Singleton pattern which assures only a single instance (the singleton) of the class can be created.

Open doc,txt or pdf files in C#

This example shows you how to open any doc,txt or pdf file which is saved in any specified location.In this example I m using a GridView which contains a template field which holds FileName.

<asp:TemplateField HeaderText="Application Name" SortExpression="ApplicationName">

        <itemtemplate>

                       <asp:LinkButton ID="lbOpenApplication" runat="server" Text='<%# Bind("ApplicationName") %>'  CommandArgument='<%# Bind("ApplicationName") %>' CommandName="ViewApplication"></asp:LinkButton>

       </itemtemplate>

    </asp:TemplateField>

In Gridview's RowCommand event we have to write code snippet given below.

protected void gvViewLeave_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        if (e.CommandName == "ViewApplication")

        {

            string filename = e.CommandArgument.ToString();

            string path = Server.MapPath("~/PayRoll/Applications/");

            System.Diagnostics.Process.Start(path + filename);

        }

    }

Count No of Table,View,Indexes,Stored Procedure

Sometime we need to count the no of table/view/indexes/stored procedure in Database.

 --Returns Total No of User Defined Table

select count(*) cntTables from sysobjects where type = 'U'

--Returns Total No of User Defined View

select count(*) cntView from sysobjects where type = 'V'

 --Returns Total No of Index.You may need to further filter,

 -- depending on which types of indexes you want.

select count(*) cntIndex from sysindexes

--Returns No of Stored Procredure

select Count(*) cntProc from sys.procedures

--Return numbers of non clustered indexes on any table in entire database.

SELECT COUNT(i.TYPE) NoOfIndex,

[schema_name] = s.name, table_name = o.name

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]

INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

WHERE o.TYPE IN ('U')

AND i.TYPE = 2

GROUP BY s.name, o.name

ORDER BY schema_name, table_name

Return All Dates of Selected Month

return all dates of a selected month.
So, here is the function solving this purpose -:

CREATE FUNCTION GetAllDateOfMonth

(   

    @dbDate datetime

)

RETURNS @AllDates TABLE

(

 GenDate datetime not null

)

AS

BEGIN

    DECLARE @monthNo int;

    -- Set Month no of Selected Date

    SET @monthNo = datepart(MM, @dbDate);

    -- Set first day of month

    SET @dbDate = convert(datetime, convert(varchar,datepart(yy,@dbDate)) + '.' + convert(varchar,@monthNo) + '.01 00:00:00');

     WHILE datepart(MM,@dbDate) = @monthNo

     BEGIN

      INSERT INTO @AllDates VALUES (@dbDate);

      SET @dbDate = dateadd(dd, 1, @dbDate);

     END

   

    RETURN

END

==============================================================

And this is the implementation of this function -:

SELECT * FROM [dbo].[GetAllDateOfMonth] (GETDATE())

And the Output will be -