Tuesday, July 15, 2014

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