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