To filter the value from the Data Table

            DataSet DSDocumentationFee = SelectLeaseDocumentationFeeSlabDetails();
            DataTable DTDocumentationFee = DSDocumentationFee.Tables[0];
            DataRow[] rows = DTDocumentationFee.Select("LeaseCostFrom <= " + LeaseCost + " AND  LeaseCostTo >=" + LeaseCost);
            DocumentationFee = Convert.ToDouble(rows[0]["DocumentationFeeAmount"]);

To get the Date format in SQL Server

 Method:1
//SET DATEFORMAT mdy
Declare @DueDay varchar(10)           
Declare @Month varchar(10)
Declare @Year varchar(10)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(11) 

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select @CommenceDate=CONVERT(varchar(11), Convert(varchar(2),@DueDay)+'/'+Convert(varchar(2),@Month)+'/' + Convert(varchar(4),@Year), 104)
print @CommenceDate

Method:2
CREATE FUNCTION [dbo].[FnDateTime]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
    DECLARE @Dateformat INT
    DECLARE @ReturnedDate VARCHAR(80)
    SELECT @DateFormat=CASE @format
    WHEN 'mm/dd/yyyy' THEN 101
    WHEN 'dd/mm/yyyy' THEN 103
    WHEN 'yyyy/mm/dd' THEN 111
    END
    SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
RETURN @ReturnedDate
END

SELECT [dbo].[FnDateTime] ('8/7/2008', 'dd/mm/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'mm/dd/yyyy')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

SELECT [dbo].[FnDateTime] ('8/7/2008', 'yyyy/mm/dd')

Declare @DueDay varchar(5)           
Declare @Month varchar(5)
Declare @Year varchar(5)
DECLARE @date nvarchar(50)
Declare @CommenceDate varchar(50)  

SET @Month = '06';
SET @Year = '2012';
SET @DueDay = 15;
select CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104)
set @CommenceDate= [dbo].[FnDateTime] (CONVERT(DATETIME, Convert(varchar(5),@DueDay)+'/'+Convert(varchar(5),@Month)+'/' + Convert(varchar(5),@Year), 104),'dd/mm/yyyy')

print @CommenceDate

//More Detail you can use the below link
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

http://sandeep-tada.blogspot.in/2012/01/format-date-with-sql-server-function-in.html

To Export the Crystal Report from Dataset through the C# Codes

// To create the DataSet by adding Add->new Item..

        private void btnReport_Click(object sender, EventArgs e)
        {
            ReportDocument cryRpt;
            ExportOptions CrExportOptions;
            DiskFileDestinationOptions CrDiskFileDestinationOptions;
            PdfRtfWordFormatOptions CrFormatTypeOptions;
                cryRpt = new ReportDocument();
                cryRpt.Load("C:\\LawrenceSoft\\Sample.rpt");
                DataSet DataNew = new DataSet();
                DataNew.ReadXml("F:\\LawrenceSoft\\VatDetails.xml");
                DataTable dtReportSource = (DataTable)DataNew.Tables[0];
                cryRpt.SetDataSource(dtReportSource);

                CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
                CrFormatTypeOptions = new PdfRtfWordFormatOptions();
                SaveFileDialog sfdSavePDF = new SaveFileDialog();
                sfdSavePDF.OverwritePrompt = true;
                sfdSavePDF.SupportMultiDottedExtensions = true;
                sfdSavePDF.Filter = "PDF files (*.pdf)|*.pdf";
                sfdSavePDF.ShowDialog(this);
                CrDiskFileDestinationOptions.DiskFileName = sfdSavePDF.FileName;//@"c:\\test.pdf";
                if (string.IsNullOrEmpty(CrDiskFileDestinationOptions.DiskFileName) == false)
                {
                    CrExportOptions = cryRpt.ExportOptions;
                    {
                        CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                        CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                        CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
                        CrExportOptions.FormatOptions = CrFormatTypeOptions;
                    }
                    cryRpt.Export();
                }

        }
//To use the Report Viewer for showing the Crystal Report..

            DataSet DataNew = new DataSet();
            DataNew.ReadXml("F:\\LawrenceSoft\\VatDetails.xml");
            DataTable dtReportSource = (DataTable)DataNew.Tables[0];
            ReportDocument rdSample = new ReportDocument();

            rdSample.Load("C:\\LawrenceSoft\\Sample.rpt");
            rdSample.SetDataSource(dtReportSource);
            crystalReportViewer1.Visible = true;
            crystalReportViewer1.ReportSource = rdSample;
            crystalReportViewer1.Refresh();

To Kill the Database transaction using the query

//Below query for getting system process id in the master database
select  kpid,* from sysprocesses where dbid=DB_ID('Training')


//Based on the process id we can kill the process of Database
kill kpid[processId] eg:- kill 53

Disable CUT,COPY,PASTE in your ASP.NET textbox

 Here I will explain how to disable copy, cut and paste functionality (Ctrl + c/Ctrl + v/ Ctrl+ x) in asp.net textbox using JavaScript.

            To achieve this we have two methods first method is directly set copy, cut and paste options return false in textbox to disable and second one we can use JavaScript functionality to disable copy, cut and paste options.

First Method

<asp:TextBox ID="TextBox1" runat="server" oncopy="return false" oncut="return false" onpaste="return false"></asp:TextBox>

Second Method

JavaScript Function:

         function DisableControlKey(e) {

                  // Message to display

                  var message = "Cntrl key/ Right Click Option disabled";

                  // Condition to check mouse right click / Ctrl key press

                  if (e.which == 17 || e.button == 2) {

                  alert(message);

                  return false;

                     }

                  }

            

In .aspx page

<asp:TextBox ID="txtUser" runat="server" onKeyDown="return DisableControlKey(event)"

onMouseDown="return DisableControlKey(event)"></asp:TextBox>

Expiring a page on clicking browser back button in Asp.net?

It is most common in some scenarios for us to expire a page as soon as the user clicks the back button in the web browser. This will be needed in scenarios like, when registering a new user or company in our web application.


For all who need this, I thought of sharing the code which will accomplish the same.


Response.Expires = -1;
Response.Cache.SetNoServerCaching();
Response.Cache.SetAllowResponseInBrowserHistory(false);
Response.CacheControl ="no-cache";
Response.Cache.SetNoStore();


Just paste the above code in the Page_Load event of your asp.net web page for which you want to expire when user clicks browser back button.