How can URL Rewrite in Window Server

If you want rewrite your page from http://abc.com to http://www.abc.com then follow below step in window server.

Step1:open server and click on URL Rewrite

 Step2: Add Rule for domain.

Step3: Add rewrite URL in given box and submit. 

How can check in SQL 2008 which query execute in sql

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

global.asax works on local computer but not after i publish to server

instead of the Application_Start because the first request might be local but later you could call the application on some other domain and it will no longer be local.
Local system code:

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

On Server Code:

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

How can implement Restful API

Restful Web Service:

                                     Case 1: It is not work on Add reference process
                                     Case 2: It is not use soap protocol
                                     Case 3: It is Call by HttpWebRequest HttpWebResponse
                                     Case4: In this case get post method  are use for getting response and           request.
The Methods

The interface of REST is generic. There is no need for protocol conventions for the communication between client and server. The following list describes the meaning of the HTTP methods and how they are used by REST.

Table 1: HTTP Methods
Method	Description
GET	GET queries the representation of a resource. The execution of requests should be free from side effects. GET requests can be sent arbitrarily often. You cannot blame the client for effects caused by GET requests. That means a GET can be sent heedlessly.
POST	With POST you can change the state of a resource. For example you can add a good to a shopping cart. POST isn't free from side effects. For example you can change fields in a data base or start a new process on the server with a POST request.
PUT	New resources can be produced with PUT or you can replace the content of existing resources.
DELETE	Resources can be deleted using DELETE.

How to call:

    Case: 1 First Request Format

         1: text
         2: Xml
         3: html

   Case 2: Check your query string name for send request or any variable name

  Case 3: url of rest web service

         Ex: http://shop/articles/585560

   Method: For Xml Format

     private string PostData_new(string url, string postData)

    {

        HttpWebRequest request = null;

        Uri uri = new Uri(url);

        request = (HttpWebRequest)WebRequest.Create(uri);

        request.Method = "POST";

        request.ContentType = "application/x-www-form-urlencoded";

        request.ContentLength = postData.Length;

        using (Stream writeStream = request.GetRequestStream())

        {

            UTF8Encoding encoding = new UTF8Encoding();

            byte[] bytes = encoding.GetBytes(postData);

            writeStream.Write(bytes, 0, bytes.Length);

        }

        string result = string.Empty;

        using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())

        {

            using (Stream responseStream = response.GetResponseStream())

            {

                using (StreamReader readStream = new StreamReader(responseStream, Encoding.UTF8))

                {

                    result = readStream.ReadToEnd();

                }

            }

        }

        return result;

    }

 

 Call function:

  Postdate=”variable name/Query string name=your date”

   private string PostData_new(“: http://shop/articles/584460”,postData);

ASK DOT NET: ASK DOT NET

ASK DOT NET: ASK DOT NET

How Can Set Task Scheduler in IIS for particular event

In table we have no identity number then create row id in table run time

SELECT t.* FROM (SELECT *, Row_Number() OVER(ORDER BY userid) AS RowNumber FROM table_user) t WHERE t.RowNumber % 2 = 0

select data in 2 part by even and odd data selection by sql query

select * from help where (SNo%2)=0 //even case

select * from help where (SNo%2)<>0 //odd case

How to use FusionCharts in Windows .NET Applications

Steps to run graphical charts dinamically in your application

Step 1::
Create a folder to store xml files(In this folder dinamically xml will be created based on the values comes from the
database).

step 2::
Create a folder to store javascript and swf files in your application.

Now come to the coding part
step 3::
Take a literal control in your aspx page where you want to show the graph

step 4::

Part 1::
Put this function when you want to get data in graph monthwise

public void showmonths()
    {
        SqlParameter[] showreportmonthwise = new SqlParameter[]
        {
            new SqlParameter("@emailid",Session["emailid"].ToString()),
            new SqlParameter("@testname",Label1.Text.Trim())
        };
        DataSet objDs = SqlHelper.GetDataSet(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "sp_your_query",
showreportmonthwise);
        if (objDs.Tables[0].Rows.Count != 0)
        {
            string str = "<chart caption='Test Report' xAxisName='Months' yAxisName='Marks' numberPostfix='%'
showValues='0' formatNumberScale='0' showBorder='1'>";
            //str = showmonths();
            foreach (DataRow dr in objDs.Tables[0].Rows)
            {
                monthname = dr[1].ToString();
                marks = dr[0].ToString();
                //str = str + "<set label='" + monthname + "'value='" + marks + "'/>";//' value='" + d + "'  
                str = str + "<set label='" + monthname + "' value='" + marks + "' />";
            }
            str = str + "</chart>";
            DataSet ds = new DataSet();
            //c = Convert.ToString(ds);
            ds.ReadXml(new StringReader(str));
            ds.WriteXml(Server.MapPath("~/xml_files/wholereport.xml"));
            Literal3.Text = FusionCharts.RenderChart("FusionCharts/Column3D.swf", "xml_files/wholereport.xml", "",
"myFirst1", "600", "300", false, true);
        }
    }

Part 2::
Put this function when you want to get the data user wise

private void showresult_user_wise()
    {
        SqlParameter[] showreportuserwise = new SqlParameter[]
        {
            //new SqlParameter("@emailid",Session["emailid"].ToString()),
            new SqlParameter("@testname",Label1.Text.Trim())
        };
        DataSet objDs = SqlHelper.GetDataSet(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "sp_userwise",
showreportuserwise);
        if (objDs.Tables[0].Rows.Count != 0)
        {
            string str = "<chart caption='Test Report' xAxisName='User' yAxisName='Marks' numberPrefix='%' showValues='0'
formatNumberScale='0' showBorder='1'>";
            //str = showmonths();
            foreach (DataRow dr in objDs.Tables[0].Rows)
            {
                username = dr[1].ToString();
                score = dr[0].ToString();
                //str = str + "<set label='" + monthname + "'value='" + marks + "'/>";//' value='" + d + "'  
                str = str + "<set label='" + username + "' value='" + score + "' />";
            }
            str = str + "</chart>";
            DataSet ds = new DataSet();
            //c = Convert.ToString(ds);
            ds.ReadXml(new StringReader(str));
            ds.WriteXml(Server.MapPath("~/xml_files/userwise.xml"));
            Literal2.Text = FusionCharts.RenderChart("FusionCharts/Column3D.swf", "xml_files/userwise.xml", "", "myFirst2",
"600", "300", false, true);
        }
    }

Part 3::
This function is used to get the data in pie chart
public void showmonths_piechart()
    {
        SqlParameter[] showreportmonthwise = new SqlParameter[]
        {
            new SqlParameter("@emailid",Session["emailid"].ToString()),
            new SqlParameter("@testname",Label1.Text.Trim())
        };
        DataSet objDs = SqlHelper.GetDataSet(SqlHelper.mainConnectionString, CommandType.StoredProcedure,
"sp_monthwise_in_singlequery", showreportmonthwise);
        if (objDs.Tables[0].Rows.Count != 0)
        {
            string str = "<chart caption='Test Report' palette='2' animation='1' numberPostfix='%' formatNumberScale='0'
pieSliceDepth='30' startingAngle='125'>";
            //str = showmonths();
            foreach (DataRow dr in objDs.Tables[0].Rows)
            {
                monthname = dr[1].ToString();
                marks = dr[0].ToString();
                //str = str + "<set label='" + monthname + "'value='" + marks + "'/>";//' value='" + d + "'  
                str = str + "<set label='" + monthname + "' value='" + marks + "' />";
            }
            str = str + "</chart>";
            DataSet ds = new DataSet();
            //c = Convert.ToString(ds);
            ds.ReadXml(new StringReader(str));
            ds.WriteXml(Server.MapPath("~/xml_files/" + Convert.ToString(Session["emailid"]) + "piechart.xml"));
            //Literal3.Text = FusionCharts.RenderChart("FusionCharts/Pie3D.swf", "xml_files/wholereport.xml", "",
"myFirst1", "600", "300", false, true);
            Literal4.Text = FusionCharts.RenderChart("FusionCharts/Pie3D.swf", "xml_files/" + Convert.ToString(Session
["emailid"]) + "piechart.xml", "", "myfirst_piechart", "300", "200", false, true);
        }
    }

file download code in C#

 #region File download
    private void forceDownload(string filePath, bool forceDownload)
    {
        try
        {
            string path = MapPath(filePath);
            string name = Path.GetFileName(filePath);
            string extension = Path.GetExtension(filePath);
            string contentType = string.Empty;
            switch (extension.ToLower())
            {
                case ".jpg":
                    contentType = "image/JPEG";
                    break;
                case ".gif":
                    contentType = "image/GIF";
                    break;
                case ".html":
                case ".htm":
                    contentType = "image/HTML";
                    break;
                case ".pdf":
                    contentType = "Application/pdf";
                    break;
                case ".doc":
                case ".rtf":
                    //for Microsoft Word files
                    contentType = "Application/msword";
                    break;
                case ".xls":
                    //for Microsoft Excel files
                    contentType = "Application/x-msexcel";
                    break;
                case ".txt":
                    contentType = "text/plain";
                    break;
            }

            if (forceDownload)
            {
                Response.AppendHeader("content-disposition", "attachment; filename=" + name);
            }
            if (contentType != string.Empty)
                //Set the appropriate ContentType.
                Response.ContentType = contentType;
            //Write the file directly to the HTTP content output stream.
            Response.WriteFile(path);
            Response.End();
        }
        catch (Exception errmsg)
        {
            Response.Write(errmsg.Message.ToString());
        }
    }
    #endregion
    protected void LinkButtonDownLoad_Click(object sender, EventArgs e)
    {
        string filePath = ".\\uploadData\\" + LinkButtonDownLoad.Text.Trim();
        if (File.Exists(Server.MapPath(filePath)))
        {
            forceDownload(filePath, true);
        }
        else
        {
            ClientScript.RegisterStartupScript(this.GetType(), "onload", "alert('Sorry, file not found!');", true);
        }
    }
}

< iframe id="iframetest"
src="http://localhost:1446/openPdf/csharp_ebook.pdf" height="100%"
width="100%"> Test Iframe </iframe>
open the html page using javascript
function popWin(){
window.showModalDialog("http://localhost/openPdf/try.html%22,'','dialogHeight:650px;dialogWidth:1000px')
}
This will open the pdf in modal dialog box.

Window Services for Scheduler

Please download this code by download code link.

In this Window Service code I have create Scheduler for money transfer at a particular requested date. If any user set different different date or time for money transfer automatic on same date and time without any event. Then we have use window services.

One user set time and date 12 July 2011 at 10:00 am money transfer A client table on sane date and time.

DB detail:

Two tables

1 for balance Userbalance (given date and time amount updated by window service)

2^nd one for User_Request_Amount (set transfer amount)

We have created window service exe and register in server its work automatic.

Download Code Click Here askdotnet.com

open the pdf in modal dialog box.

< iframe id="iframetest"
src="http://localhost:1446/openPdf/csharp_ebook.pdf" height="100%"
width="100%"> Test Iframe </iframe>
open the html page using javascript
function popWin(){
window.showModalDialog("http://localhost/openPdf/try.html%22,'','dialogHeight:650px;dialogWidth:1000px')
}
This will open the pdf in modal dialog box.

how to hack website using sql injection

if database built by your self if you use this type of query to login and have no validation in your field.

select * from tabelname where username=@username and password=@password and flag=1

then you simply write in username field.

username = (select top 1 username from tabelname where username like 'm%') and

password=(select top 1 password from tabelname where username =(select top 1 username from tabelname where username like 'm%'))

and then the query becomes work like this

select * from tabelname where username=(select top 1 username from tabelname where username like 'm%')
and password=(select top 1 password from tabelname where username =(select top 1 username from tabelname where username like 'm%'))
and flag=1

*************************************************************************************
select * from tableABC where userid=(select top 1 userid from tableABC where userid like 'm%') and
userpwd =(select  top 1 userpwd from tableABC where userpwd like 'v%')

username=(select top 1 userid from tableABC where userid like 'm%')
password=(select  top 1 userpwd from tableABC where userpwd like 'v%')

Call popup in grid

open pop by function in grid uesing javascript.
 protected void Grid_Author_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[8].Attributes.Add("onclick", "return ConfirmDeletion();");
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[6].Attributes.Add("onclick", "return popup("+ Grid_Author.DataKeys[e.Row.RowIndex].Value +");");
        }
    }

<script language="javascript" type="text/javascript">
function ConfirmDeletion()
{
return confirm('Are you sure you want to delete this record?');
}
function popup(getID)
{
window.open('Detail.aspx?AuthorID='+getID,'BaseWork','dialogWidth:120px;');
}
</script>
set grid property DataKeyNames=sno,title,.......
  e.Row.Cells[6].Attributes.Add("onclick", "return popup("+ Grid_Author.DataKeys[e.Row.RowIndex].Values[1] +","+Grid_Author.DataKeys[e.Row.RowIndex].Values[2] +");");

Online seo on CS page by C# code

//***********Page Title **********************************************************
        Page.Title = dr["Book_name"].ToString();
        //*********************************************************************

void _HtmlMetaTags()
    {
        HtmlMeta myMetaKeyWords = new HtmlMeta();
        HtmlMeta myMetaDescription = new HtmlMeta();
        HtmlMeta myMetaRobot = new HtmlMeta();
        HtmlMeta HTTP_EQUIV = new HtmlMeta();
        myMetaKeyWords.Name = "keywords";
        myMetaKeyWords.Content = "Non-Retail Social Investor, Institutional investor Lender, Not-For-Profit Institutions, CapitalConnect, Social Enterprise, Private Placement Market, Start-up Enterprises, exit option, Social Investment Trends, Double Bottomline, Private Equity management";
        myMetaDescription.Name = "Description";
        myMetaDescription.Content = "Social enterprise is an organisations that run Non-Retail Social Investor, Institutional investor Lender, Not-For-Profit Institutions.";
        myMetaRobot.Name = "robots";
        myMetaRobot.Content = "index,follow";
        HTTP_EQUIV.HttpEquiv = "Content-Type";
        HTTP_EQUIV.Content = "text/html; charset=iso-8859-1";
        Page.Header.Controls.Add(myMetaKeyWords);
        Page.Header.Controls.Add(myMetaDescription);
        Page.Header.Controls.Add(myMetaRobot);
        Page.Header.Controls.Add(HTTP_EQUIV);
    }

file download code in C#

 #region File download
    private void forceDownload(string filePath, bool forceDownload)
    {
        try
        {
            string path = MapPath(filePath);
            string name = Path.GetFileName(filePath);
            string extension = Path.GetExtension(filePath);
            string contentType = string.Empty;
            switch (extension.ToLower())
            {
                case ".jpg":
                    contentType = "image/JPEG";
                    break;
                case ".gif":
                    contentType = "image/GIF";
                    break;
                case ".html":
                case ".htm":
                    contentType = "image/HTML";
                    break;
                case ".pdf":
                    contentType = "Application/pdf";
                    break;
                case ".doc":
                case ".rtf":
                    //for Microsoft Word files
                    contentType = "Application/msword";
                    break;
                case ".xls":
                    //for Microsoft Excel files
                    contentType = "Application/x-msexcel";
                    break;
                case ".txt":
                    contentType = "text/plain";
                    break;
            }

            if (forceDownload)
            {
                Response.AppendHeader("content-disposition", "attachment; filename=" + name);
            }
            if (contentType != string.Empty)
                //Set the appropriate ContentType.
                Response.ContentType = contentType;
            //Write the file directly to the HTTP content output stream.
            Response.WriteFile(path);
            Response.End();
        }
        catch (Exception errmsg)
        {
            Response.Write(errmsg.Message.ToString());
        }
    }
    #endregion
    protected void LinkButtonDownLoad_Click(object sender, EventArgs e)
    {
        string filePath = ".\\uploadData\\" + LinkButtonDownLoad.Text.Trim();
        if (File.Exists(Server.MapPath(filePath)))
        {
            forceDownload(filePath, true);
        }
        else
        {
            ClientScript.RegisterStartupScript(this.GetType(), "onload", "alert('Sorry, file not found!');", true);
        }
    }
}

Call Java Script function on cs page

<script language="javascript" type="text/javascript">
function ConfirmDeletion()
{
return confirm('Are you sure you want to delete this record?');
}
</script>


if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[7].Attributes.Add("onclick", "return ConfirmDeletion();");
        }

Ues of Fieldset in aspx page

<fieldset>
                            <legend>test name of heading</legend>
                                <table bgcolor="#f3e8d4" style="position: relative" width="100%">
                                    <tr>
                                        <td colspan="6" style="height: 15px" bgcolor="#ddc89d">
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="width: 179px; height: 15px">
                                            <span class="textbg">New Arrivals:</span></td>
                                        <td style="width: 389px; height: 15px">
                                            <asp:CheckBox ID="cbox_new" runat="server" Style="position: relative" /></td>
                                        <td style="width: 100px; height: 15px">
                                            Forthcoming:</td>
                                        <td colspan="3" style="height: 15px">
                                            <asp:CheckBox ID="cbox_for" runat="server" Style="position: relative" /></td>
                                    </tr>
                                    <tr>
                                        <td style="width: 179px">
                                            Best Sellers:</td>
                                        <td style="width: 389px">
                                            <asp:CheckBox ID="cbox_sell" runat="server" Style="position: relative" /></td>
                                        <td style="width: 100px">
                                            Hot Buys:</td>
                                        <td colspan="3">
                                            <asp:CheckBox ID="cbox_hot" runat="server" Style="position: relative" /></td>
                                    </tr>
                                    <tr>
                                        <td style="height: 15px;" colspan="5">
                                            If Other Language:
                                            <asp:DropDownList ID="ddl_other" runat="server" Style="position: relative" CssClass="box">
                                                <asp:ListItem Selected="True" Value="">Select</asp:ListItem>
                                                <asp:ListItem Value="1">YES</asp:ListItem>
                                                <asp:ListItem Value="0">NO</asp:ListItem>
                                            </asp:DropDownList>
                                            <asp:RequiredFieldValidator ID="val_lang" runat="server" ControlToValidate="ddl_other"
                                                ErrorMessage="Please select language." ForeColor="" Style="position: relative"></asp:RequiredFieldValidator></td>
                                        <td style="width: 100px; height: 15px;">
                                        </td>
                                    </tr>
                                    <tr>
                                        <td style="width: 179px">
                                        </td>
                                        <td style="width: 389px">
                                        </td>
                                        <td style="width: 100px">
                                        </td>
                                        <td style="width: 100px">
                                        </td>
                                        <td style="width: 100px">
                                        </td>
                                        <td style="width: 100px">
                                        </td>
                                    </tr>
                                </table>
                                </fieldset>

paging in DataList by stored procedure in SQL

CREATE procedure getQueryPageitems
 @totalItemCount int,       
 @pageNumber int,       
 @pageSize int       
as       
declare @numberOfItemToSelectForPage int       
declare @totalItemsToSel int   
declare @query nvarchar(4000)       
if(@pageSize*@pageNumber <= @totalItemCount)       
begin        
 set @numberOfItemToSelectForPage = @pageSize       
 set @totalItemsToSel = @pageSize*@pageNumber       
end       
else       
begin        
 set @numberOfItemToSelectForPage =  @totalItemCount - (@pageSize*(@pageNumber -1))      
 set @totalItemsToSel = @totalItemCount       
end   
begin
select  @query = 'select * from(select top ' + cast(@numberOfItemToSelectForPage as varchar(10))       
 + ' * from (select top ' + cast(@totalItemsToSel as varchar(10))        
+ ' * from (select * from  Query where Leedflag=1) tbl1       
    order by EntryDate desc)tabl2   order by EntryDate asc)tbl3   order by EntryDate desc'
end  
--print @query       
exec (@query)

GO

Bind Year in C#

#region BINDYEAR
    private void BINDYEAR()
    {
        try
        {
            int A = 1800;
            for (int i = 0; i < 20; i++)
            {
                ListItem LI = new ListItem();
                LI.Text = Convert.ToInt16(A).ToString();
                LI.Value = A.ToString();
                A=A+1;
                ddl_year.Items.Add(LI);
            }
            ddl_year.Items.Insert(0, "YYYY");
              
        }
        catch (Exception errmsg)
        {
            Response.Write(errmsg.Message.ToString());
        }
    }
    #endregion

Block back option in application by cache

Page load or Page init event

  Response.Cache.SetExpires(DateTime.UtcNow.AddMinutes(-1));

  Response.Cache.SetCacheability(HttpCacheability.NoCache);

  Response.Cache.SetNoStore();

How can manage length of text in C#

 #region DisplayAbout_book()
    public string DisplayAbout_book(object DataItem)
    {
        string About_book = Convert.ToString(DataBinder.Eval(DataItem, "About_book"));
        if (About_book != string.Empty)
        {
            if (About_book.Length > 75)
            {
                About_book = About_book.ToString().Substring(0, 75);
            }
            else
            {
                About_book = About_book.ToString();
            }
        }
        return About_book;
    }
    #endregion

convert multiple space in one space by function in c#

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Text.RegularExpressions;
public partial class SMS_space : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    public static string NormalizeWhiteSpace(string S)
    {
        string s = S.Trim();
        bool iswhite = false;
        int iwhite;
        int sLength = s.Length;
        StringBuilder sb = new StringBuilder(sLength);
        foreach (char c in s.ToCharArray())
        {
            if (Char.IsWhiteSpace(c))
            {
                if (iswhite)
                {
                    continue;
                }
                else
                {
                    sb.Append(" ");
                    iswhite = true;
                }
            }
            else
            {
                sb.Append(c.ToString());
                iswhite = false;
            }
        }
        return sb.ToString();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //*********************its also working**********************************
        //string text = NormalizeWhiteSpace(TextBox1.Text);
        //Response.Write(text);
        //******************************8
        Regex r = new Regex(@"\s+");
        // Strip multiple spaces.
        string s3 = r.Replace(TextBox1.Text, @" ");
        Response.Write(s3);    
    }
}

How can ues post method when ues Master page in ASP.NET

When use master page then form tag ues in master page but we want post our page from index.asp to index1.aspx in this case ues this code on cs page.

****************************Page index.aspx.cs************************************
protected void Page_Load(object sender, EventArgs e)
{
HtmlForm TEST;
TEST = (HtmlForm)Master.FindControl("form1");
TEST.Method = "post";
TEST.Action = "index1.aspx";
}

********************************Page index.aspx1.cs*******************************************
string textvalur=request("control name);

User Define function for ID

CREATE  FUNCTION [NewID]()
RETURNS varchar(20)
AS
BEGIN
DECLARE @_ggmvalue varchar(30)
DECLARE @_ggmvalue1 varchar(30)
DECLARE @_ggmvalue2 varchar(20)
DECLARE @_ggmmilisec varchar(5)
DECLARE @lengthmilisec varchar(5)
DECLARE @_TtmPrefix varchar(16)
DECLARE @_TtmSufix int
DECLARE @_TtmId varchar(22)
DECLARE @length int


select @_ggmvalue1=convert(varchar(10),get_ggm(),112)+convert(varchar(10),get_ggm(),108)
select @_ggmmilisec=convert(varchar(10),_ggmPART(ms,get_ggm()))
set @length=Len(@_ggmmilisec)
if(@length<2)
begin
set @_ggmmilisec='000'+@_ggmmilisec
end
if(@length<3 and @length>=2)
begin
set @_ggmmilisec='00'+@_ggmmilisec
end
if(@length<4 and @length>=3)
begin
set @_ggmmilisec='0'+@_ggmmilisec
end
if(@length>=4)
begin
set @_ggmmilisec=@_ggmmilisec
end
select @_ggmvalue=@_ggmvalue1+@_ggmmilisec
select @_ggmvalue2= Replace(@_ggmvalue,':','')
select @_TtmSufix=Number from randomNumber
select @_TtmPrefix=Substring(@_ggmvalue2,3,18)
set @_TtmId=@_TtmPrefix+convert(varchar(6),@_TtmSufix)
return @_TtmId
END
GO

 

How can Use LINQ in C#

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping framework provides the following basic features:
LINQ has a power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable interface. Microsoft basically divides LINQ into three areas and that are give below.

•LINQ to Object {Queries performed against the in-memory data}
•LINQ to ADO.Net
◦LINQ to SQL (formerly DLinq) {Queries performed against the relation database only Microsoft SQL Server Supported}
◦LINQ to DataSet {Supports queries by using ADO.NET data sets and data tables}
◦LINQ to Entities {Microsoft ORM solution}
•LINQ to XML (formerly XLinq) { Queries performed against the XML source}
Tools to create classes (usually called entities) mapped to database tables
Compatibility with LINQ’s standard query operations
The DataContext class, with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection

Code for LINQ**************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using pmsContract.entities;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace pms.Webproject
{
//we have create class usertest and table nam: pms_user

[Table(Name = "pms_user")]//define table name
public class usertest //this is classs
{
[Column]
public string userfname { get; set; } //get or set table fields
[Column]
public string userlname { get; set; }
[Column]
public string useremailID { get; set; }

}

public partial class test : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
string conn = ConfigurationManager.ConnectionStrings["stringConnection"].ToString();
DataContext db = new DataContext(conn);//Interface between linq or SQL
Table<usertest> pmsusettest = db.GetTable<usertest>();//create object of table
var query = from d in pmsusettest orderby d.userfname select d; //select linq query for data selection.
GridView1.DataSource = query;//provide datasource to gridview
GridView1.DataBind();
}

}
}

What is Triggers ???

Triggers are not new to SQL Server. But prior to SQL Server 2005 triggers were DML triggers, which were raised only when there is an INSERT,UPDATE or DELETE action. A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.
Sometimes a trigger is referred as a special kind of stored procedure in term of procedural code inside its body. The difference between a trigger and a stored procedure is that a trigger is activated or called when an event happens in a database table, a stored procedure must be called explicitly. For example you can have
some business logic to do before or after inserting a new record in a database table.

INSERT trigger
When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted.
DELETE trigger
When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.
UPDATE trigger
When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

Trigger Example......
We have main table [fig] and we want to create trigger [trgr_logdetails].In this query when insert data in main table [fig] at this movement also fire trigger [trgr_logdetails] and insert data in logical table [trgr_logdetails].

CREATE TRIGGER [trgr_logdetails]
 on [fig]
   AFTER insert
AS
BEGIN
 declare @figID int
 declare @roleID  int
 declare @menuID int
 declare @figcreatedBy nvarchar(100)
 declare @figupdatedBy nvarchar(100)
 declare @rigthscreatedate datetime
 declare @figupdateddate datetime
 declare @rigthsstatus  bit
 declare @tabid   int

 set @figID = (select figID  from inserted)
 set @roleID =(select roleID from inserted)
 set @menuID=(select menuID from inserted)
 set @figcreatedBy=(select  figcreatedBy from inserted)
 set @figupdatedBy=(select  figupdatedBy from inserted)
 set @rigthscreatedate=(select  rigthscreatedate from inserted)
 set @figupdateddate=(select  figupdateddate from inserted)
 set @rigthsstatus=(select  rigthsstatus from inserted)
 set @tabid=(select tabid from inserted)

    -- Insert statements for trigger here
    begin
  INSERT INTO [trgr_logdetails]
           ([figID]
           ,[roleID]
           ,[menuID]
           ,[figcreatedBy]
           ,[figupdatedBy]
           ,[rigthscreatedate]
           ,[figupdateddate]
           ,[rigthsstatus]
           ,[tabid])
     VALUES
           (@figID
           ,@roleID
           ,@menuID
           ,@figcreatedBy
           ,@figupdatedBy
           ,@rigthscreatedate
           ,@figupdateddate
           ,@rigthsstatus
           ,@tabid)
 end
END

Date format in MSSQL

Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds) SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM