CPU Uese in SQL Server

CPU Uese in SQL Server :

SELECT total_worker_time/execution_count AS AvgCPU
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration
, total_elapsed_time AS TotalDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count  
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1
, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS txt
, query_plan
FROM sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY 1 DESC

select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
    (select top 10
        sum(qs.total_worker_time) as total_cpu_time,
        sum(qs.execution_count) as total_execution_count,
        count(*) as  number_of_statements,
        qs.plan_handle
    from
        sys.dm_exec_query_stats qs
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    ) a
inner join
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id

Create and Delete INDEX in SQL Server

Drop index from table :

DROP INDEX table_name.index_name

DROP INDEX aspnet_Paths.aspnet_Paths_index

Create Index in table:

CREATE INDEX aspnet_Paths_index
ON aspnet_Paths (PathID)

Find out index in table in SQL Server

Find out index in table :

USE table name
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

MS-SQL predefined stored procedures in sql server

1.EXEC sp_databases database name on server.

2.SELECT DB_NAME() AS DataBaseName  current database name.

3.EXEC sp_MSForEachDB 'Print ''?'''

4.EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'

5.SELECT name,crdate FROM master..sysdatabases with created date

6.DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union
   SET @sqlString = @sqlString + '
    SELECT
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs;
DEALLOCATE crs;
EXEC(@sqlString)



7.SELECT name,crdate,category,cmptlevel,filename,mode,reserved,sid,status,version FROM master..sysdatabases

How many data types in C# (Bool type)

Bool type
Bool type in C# can be used to represent a boolean value. A bool type is binary in nature and takes only 2 values true or false.

// Example for boolean type

using System;

    class bool_type
    {
        public static void Main(string[] args)
        {
            bool switch_on = true;
            bool switch_off = false;

            Console.WriteLine("If switch is ON,it is: " + switch_on);
            Console.WriteLine("If switch is OFF,it is: " + switch_off);
         
        }
    }


 Output
  If switch is ON,it is:True
  If switch is OFF,it is:False
 

How many data types in C# (Decimal type )

Decimal type
The decimal type in C# (not available in C, C++ or Java) is a 128-bit data type. It is mainly used for financial and monetary calculations. It’s range is between 1.0 * 10-28 to 7.9 * 1028 .
// Example for decimal types

using System;

    class decimal_types
    {
        public static void Main(string[] args)
        {
            decimal d = 1233455.25m;
            Console.WriteLine("Decimal Value: " +d);


        }
 }


Output:

   Decimal Value:1233455.25

How many data types in C# (Floating point types)

Floating point types
C# supports 2 floating point types viz. float and double.

Type # Floating type Data Type Default Value Range

1 float  32-bit single-precision IEEE 754 format 0.0f  1.5 * 10-45 to 3.4 * 1038
2 double  64-bit double-precision IEEE 754 format  0.0d 5.0 * 10-324 to 1.7 * 10308
Table – Floating point types

// Example for floating point types

using System;

    class float_types
    {
        public static void Main(string[] args)
        {
            float f = 12345.12545f;
            double d = 123456789.123456789d;

            Console.WriteLine("Float value: " + f);
            Console.WriteLine("Double value: " + d);
     
        }
    }


 Output:

   Float value:12345.13
 
   Double value: 123456789.123457

How many data types in C# (Integral types)

DataTypes In C#
Value Based Datatypes:

Value based datatypes are those which store the data directly into the variables. Value based datatypes can further be classified as simple types, enum-type and struct-type. We follow a different type of classification which is based on the "data-type".

1.Integral types
2.Floating point types
3.Decimal type
4.Bool type
5.Integral types

C# supports 9 different integral types.

Type # Integral type Data Type Default Value Range

1 sbyte 8-bit signed integer 0 -128 to +127
2 byte 8-bit unsigned integer 0 0 to 255
3 short  16-bit signed integer  0  -32,768 to +32,767
4 ushort 16-bit unsigned integer 0 0 to 65535
5 int 32-bit signed integer  0 -2,147,483,648 to +2,147,483,647
6 uint 32-bit unsigned integer 0 0 and 4294967295
7 long 64-bit signed integer 0 -9,223,372,036,854,775,808 to+ 9,223,372,036,854,775,807
8 ulong 64-bit unsigned integer 0 0 to 18446744073709551615
9 char 16-bit unsigned integer  '\x0000' 0 to 65535

// Example that demonstrates integral datatypes in C#

using System;

    class integral_dtypes
    {
        public static void Main(string[] args)
        {
         sbyte sb=-125;
            byte b=125; // try assigning -125 to this variable
            short s = -32225;
            ushort us = 32225; // try assigning -32225 to this variable
            int i = -123456789;
            uint ui = 123456789; //try assigning -123456789
            long l = -123456789;
            ulong ul = 11112222333344445555;
            char c = 'a';

            Console.WriteLine("Signed byte: " + sb);
            Console.WriteLine("Unsigned byte: " + b);
            Console.WriteLine("Signed short: " + s);
            Console.WriteLine("Unsigned short: " + us);
            Console.WriteLine("Integer: " + i);
            Console.WriteLine("Unsigned integer : " + ui);
            Console.WriteLine("Long: " + l);
            Console.WriteLine("Unsigned Long: " + ul);
            Console.WriteLine("Character: " + c);

        }
    }

Output:

   Signed byte: -125
   Unsigned byte:125
   Signed short:-32225
   Unsigned short:32225
   Integer: -123456789
   Unsigned integer :123456789
   Long: -123456789
   Unsigned Long:11112222333344445555
   Character: a

How can use Ajax validation control on our form.

Select ValidatorCalloutExtender from Ajax control:

After adding  ValidatorCalloutExtender on form set property.

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

  </script>

 

   <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

    <asp:ValidatorCalloutExtender ID="reqCurPassword" runat="server" TargetControlID="curr_password">

    </asp:ValidatorCalloutExtender>

    <asp:ValidatorCalloutExtender ID="reqNewPassword" runat="server" TargetControlID="new_password">

    </asp:ValidatorCalloutExtender>

    <asp:ValidatorCalloutExtender ID="reqConfPassword" runat="server" TargetControlID="conf_password">

    </asp:ValidatorCalloutExtender>

Add .net RequiredFieldValidator on page:

<tr id="trBranch" runat="server">

        <td style="text-align: left; width: 188px;" class="text" >

        New Password :</td>

        <td style="text-align: left; ">

<asp:TextBox ID="txt_newpassword" runat="server" TabIndex="3" CssClass="TextBox" TextMode="Password"></asp:TextBox>

<asp:RequiredFieldValidator ID="new_password" runat="server"

ErrorMessage="Please enter new password." ToolTip="Please enter new password." ControlToValidate="txt_newpassword">*</asp:RequiredFieldValidator>

        </td>

    </tr>

Out put of Ajax validation control:

Creating database for .net framework login system

Print own text on current date in calendar in c#

Print own text on current date.

#region DayRender

  

    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

    {

     if (e.Day.IsToday)

            {

               e.Cell.Text = "today is chandra day";

            }

    }

    #endregion

Print own text on current date in calendar in c# (put text in textbox).

Print own text on current date (put text in textbox).

#region DayRender

  

    protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

    {

     if (e.Day.IsToday)

            {

                e.Cell.Text = TextBox1.Text;

            }

    }

    #endregion

On Selection change print date in label in calendar in c#

On Selection change print date in label.

protected void Calendar1_SelectionChanged(object sender, EventArgs e)

    {

        Label1.Text = "Your Date" + Calendar1.SelectedDate.ToLongDateString();

    }

Calendar show weekend disable in c#

Calendar show weekend disable.

     //code for day is weekend so disable to select event that day.

            if (e.Day.IsWeekend)

            {

                e.Cell.BackColor = System.Drawing.Color.White;

                e.Cell.ForeColor = System.Drawing.Color.Brown;

                e.Day.IsSelectable = false;

                e.Cell.Text = "Holyday";

            }

Calendar month change then print “your month change” in c#

Calendar month change then print “your month change”

protected void Calendar1_VisibleMonthChanged(object sender, MonthChangedEventArgs e)

    {

        Label1.Text = "Your month change";

    }

Calendar show date with disable link on date 1,2,3,4,5,6,7,8,9,10 in c#

       
Calendar  show date with disable link on date 1,2,3,4,5,6,7,8,9,10.

if (dr["D" + i].ToString() == "1")

                            {

                                e.Cell.BackColor = Color.Green;

                                e.Day.IsSelectable = false;

                            }

Calendar show with different color of date depends on database value in c#

         

Calendar show with different color of date depends on database value.

  #region DayRender

   protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

    {

        {

            if (!e.Day.IsOtherMonth)

            {

                int Fmonth = e.Day.Date.Month;

                string Enroll = "123abcd";

                string sql = "select * from Atendence where Amonth=" + Fmonth + " and EnrollNo='" + Enroll.ToString() + "'";

                SqlDataReader dr = dut.ExecuteReader(sql);

                if (dr.Read())

                {

                    int i;

                    for (i = 1; i <= 31; i++)

                    {

                        if (e.Day.Date.Day == i)

                        {

                            if (dr["D" + i].ToString() == "1")

                            {

                                e.Cell.BackColor = Color.Green;

                            }

                            else if (dr["D" + i].ToString() == "0")

                            {

                                e.Cell.BackColor = Color.Red;

                            }

                            else if (dr["D" + i].ToString() == "2")

                            {

                                e.Cell.BackColor = Color.Yellow;

                            }

                            else

                            {

                                e.Cell.BackColor = Color.White;

                            }

                        }

                    }

                }

            }

            else

    { e.Cell.Text = ""; }

Calendar show text place of date in c#

       
Calendar show text place of date.

//***********************************day render create cell load time**********

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

if (dr.Read())

                {

                    int i;

                    for (i = 1; i <= 31; i++)

                    {

                        if (e.Day.Date.Day == i)

                        {

                            if (dr["D" + i].ToString() == "1")

                            {

e.Cell.Text = "P";

                            }

                            else if (dr["D" + i].ToString() == "0")

                            {

                               e.Cell.Text = "A";

    }

                            else if (dr["D" + i].ToString() == "2")

                            {

e.Cell.Text = "H";                      

          }

                            else

                            {

                                e.Cell.BackColor = Color.White;

                          }

                      }

                  }

   }

            }

            else

 { e.Cell.Text = ""; }

Calendar show only current month date in c#

Calendar show only current month date.

//***********************************day render create cell load time**********

 protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)

 if (dr.Read())

                {

                    int i;

                    for (i = 1; i <= 31; i++)

                    {

                        if (e.Day.Date.Day == i)

                        {

                            if (dr["D" + i].ToString() == "1")

                            {

                                e.Cell.BackColor = Color.Green;

                            }

                            else if (dr["D" + i].ToString() == "0")

                            {

                                e.Cell.BackColor = Color.Red;

                            }

                            else if (dr["D" + i].ToString() == "2")

                            {

                                e.Cell.BackColor = Color.Yellow;

                            }

                            else

                            {

                                e.Cell.BackColor = Color.White;

                          }

                      }

                  }

   }

            }

            else

  { 

e.Cell.Text = ""; 

}

How can manage calendar object in asp.net with c#

How can manage calendar object in asp.net with c#

1. Calendar add on page.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class Webadmin_Atendence_calt : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

}

Hit Counter for Total number of Current Visitors on website

Total No of Current Visitors :

For current visitors hit we can use Global.asax file and Application variable :

Global.asax :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using System.Data.SqlClient;
using System.Text;
using System.IO;

namespace SRTT_HEALTH
{
    public class Global : System.Web.HttpApplication
    {

        protected void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup
            Application["Visitors"] = 0;
         
        }

        protected void Session_Start(object sender, EventArgs e)
        {
            // Code that runs when a new session is started
            Application.Lock();
            Application["Visitors"] = Convert.ToInt32(Application["Visitors"]) + 1;
            int users = Convert.ToInt32(Application["Visitors"]);
            Application.UnLock();
         
        }

        protected void Application_BeginRequest(object sender, EventArgs e)
        {

        }

        protected void Application_AuthenticateRequest(object sender, EventArgs e)
        {

        }

        protected void Application_Error(object sender, EventArgs e)
        {
         
        }

        protected void Session_End(object sender, EventArgs e)
        {

        }

        protected void Application_End(object sender, EventArgs e)
        {

        }
    }
}

Other page code where show visitors number:


protected void Page_Load(object sender, EventArgs e)
        {
         
            Label1.Text=Application["Visitors"].ToString();
         
        }

Hit counter for application (Total Visit of Web Application)

Hit Counter for Application without Database:

count.ascx :


<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="count.ascx.cs" Inherits="SRTT_HEALTH.uiControl.count" %>
<tr>
<td align="center" height="20">Total Visit of Web Application : <asp:Label ID="lblCounter" runat="server"></asp:Label><br>

Total No of Current Visitors : <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </td>
</tr>

count.ascx.cs :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace HEALTH.uiControl
{
    public partial class count : System.Web.UI.UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
           
            Label1.Text=Application["Visitors"].ToString();
           
            this.hitcount();

            DataSet DS = new DataSet();
            DS.ReadXml(Server.MapPath("~/ErrorLog/counter.xml"));

            lblCounter.Text = DS.Tables[0].Rows[0]["hits"].ToString();
           
        }

        private void hitcount()
        {
            DataSet DS = new DataSet();
            DS.ReadXml(Server.MapPath("~/ErrorLog/counter.xml"));

            int hits = Int32.Parse(DS.Tables[0].Rows[0]["hits"].ToString());

            hits += 1;

            DS.Tables[0].Rows[0]["hits"] = hits.ToString();

            DS.WriteXml(Server.MapPath("~/ErrorLog/counter.xml"));
         
        }
    }
}

Find out week in month find week range

Query: How can find out week in month find week range.



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SBIWEB
{
    public partial class weekly : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        private string getDatetm(DateTime dt)
        {

            string s = string.Empty;
            DateTime dtTempS = dt;
            DateTime dtTempL = dt;
            int daysLeft = 7;
            int totalDayRemaing = DateTime.DaysInMonth(dt.Year, dt.Month);
            if ((int)dt.DayOfWeek != 1)
            {
                daysLeft = (int)dt.DayOfWeek;
                if (daysLeft == 0)
                    daysLeft = 8;
                daysLeft = 7 - daysLeft + 1;
            }
            while (totalDayRemaing > 7)
            {
                dtTempL = dtTempS.AddDays(daysLeft - 1);
                s = s + dtTempS.ToString("dd/MM") + " - " + dtTempL.ToString("dd/MM") + "\r\n";
                dtTempS = dtTempL.AddDays(1);
                totalDayRemaing = totalDayRemaing - (daysLeft);
                daysLeft = 7;
            }
            dtTempL = dtTempS.AddDays(totalDayRemaing - 1);
            s = s + dtTempS.ToString("dd/MM") + " - " + dtTempL.ToString("dd/MM") + "\r\n";
            return s;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string s = string.Empty;
            DateTime dtStart = new DateTime(2012, 3, 1);
            DateTime dtEnd = new DateTime(2013, 4, 30);
            while (dtStart.Year < dtEnd.Year || dtStart.Month <= dtEnd.Month)
            {
                s = s + getDatetm(dtStart);
                dtStart = dtStart.AddMonths(1);
            }
            Response.Write(HttpUtility.HtmlEncode(s).Replace("\n", "<br />"));
        }
    }
}

Output:

01/03 - 04/03
05/03 - 11/03
12/03 - 18/03
19/03 - 25/03
26/03 - 31/03 

Code for how many week in financial year

Query:  Find out week in between two year's.

 public void weekly()

        {

            int startdate = 2011;

            int endDate = 2012;

            string s = string.Empty;

            DateTime dtStart = new DateTime(startdate, 4, 1);

            DateTime dtEnd = new DateTime(endDate, 3, 31);

            DateTime dtTemp2 = default(DateTime);

            DateTime dtTemp = default(DateTime);

            while (dtStart <= dtEnd)

            {

                dtTemp = dtStart.AddDays(6);

                if (dtTemp < dtEnd)

                    s = s + dtStart.ToString("dd MMMM yyyy") + " - " + dtTemp.ToString("dd MMMM yyyy") + "\r\n\n";

                else

                {

                    s = s + dtStart.ToString("dd MMMM yyyy") + " - " + dtEnd.ToString("dd MMMM yyyy") + "\r\n\n";

                    break;

                }

                dtTemp2 = dtTemp.AddDays(1);

                dtStart = dtTemp2;

            }

         

            Response.Write(HttpUtility.HtmlEncode(s).Replace("\n", "<br />"));

         

        }

Out Put:

01 April 2011 - 07 April 2011
08 April 2011 - 14 April 2011
15 April 2011 - 21 April 2011
22 April 2011 - 28 April 2011
29 April 2011 - 05 May 2011
06 May 2011 - 12 May 2011
13 May 2011 - 19 May 2011
20 May 2011 - 26 May 2011
27 May 2011 - 02 June 2011
03 June 2011 - 09 June 2011
10 June 2011 - 16 June 2011
17 June 2011 - 23 June 2011
24 June 2011 - 30 June 2011
01 July 2011 - 07 July 2011
08 July 2011 - 14 July 2011
15 July 2011 - 21 July 2011