How To Send Mail Using SQL Server

Create procedure as.....

ALTER PROCEDURE [dbo].[usp_SendTextEmail]  @ServerAddr nvarchar(128),
@From nvarchar(128),
@To nvarchar(1024),
@Subject nvarchar(256),
@Bodytext nvarchar(max) = 'This is a test text email from MS SQL server, do not reply.',
@User nvarchar(128) = '',
@Password nvarchar(128) = '',
@SSLConnection int = 0,
@ServerPort int = 25

AS

DECLARE @hr int
DECLARE @oSmtp int
DECLARE @result int
DECLARE @description nvarchar(255)

EXEC @hr = sp_OACreate 'EASendMailObj.Mail',@oSmtp OUT 
If @hr <> 0 
BEGIN
    PRINT 'Please make sure you have EASendMail Component installed!'
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End

EXEC @hr = sp_OASetProperty @oSmtp, 'LicenseCode', 'TryIt'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', @ServerPort

EXEC @hr = sp_OASetProperty @oSmtp, 'UserName', @User
EXEC @hr = sp_OASetProperty @oSmtp, 'Password', @Password

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @From

EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL,  @To, 0

EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject 
EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText 


If @SSLConnection > 0 
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL
END

/* you can also add an attachment like this */
/*EXEC @hr = sp_OAMethod @oSmtp, 'AddAttachment', @result OUT, 'd:\test.jpg'*/
/*If @result <> 0 */
/*BEGIN*/
/*   EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT*/
/*    PRINT 'failed to add attachment with the following error:'*/
/*    PRINT @description*/
/*END*/

PRINT 'Start to send email ...' 

EXEC @hr = sp_OAMethod @oSmtp, 'SendMail', @result OUT  

If @hr <> 0 
BEGIN
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End

If @result <> 0 
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT
    PRINT 'failed to send email with the following error:'
    PRINT @description
END
ELSE 
BEGIN
    PRINT 'Email was sent successfully!'
END

EXEC @hr = sp_OADestroy @oSmtp
============================================================

Call above procedure as .......


/* Gmail SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'

/* Set your Gmail email address */
DECLARE @From nvarchar(128)
Set @From = 'xyz@gmail.com'

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'abc@gmail.com'

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL SERVER'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'

/* Gmail user authentication should use your 
 Gmail email address as the user name. 
For example: your email is "gmailid@gmail.com", then the user should be "gmailid@gmail.com" */
DECLARE @User nvarchar(128)
Set @User = 'xyz@gmail.com'

DECLARE @Password nvarchar(128)
Set @Password = 'pwd#123'

/* Enable SSL/TLS */
DECLARE @SSLConnection int
Set @SSLConnection = 1

/* If you want to use TLS, please set it to 25 or 587 */
DECLARE @ServerPort int
Set @ServerPort = 587

PRINT 'start to send email ...'


exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSLConnection, @ServerPort

What is the difference between jquery.size() and jquery.length

jquery.size() and jquery.length both returns the number of element found in the object. But, jquery.length is faster than jquery.size() because size() is a method but length is a property .

jQuery .size() method returns number of element in the object. But it is not preferred to use the size()method as jQuery provide .length property and which does the same thing. But the .length property is preferred because it does not have the overhead of a function call. 

Difference Between Constant and ReadOnly and Static

Constant

Constant fields or local variables must be assigned a value at the time of declaration and after that they cannot be modified. By default constant are static, hence you cannot define a constant type as static.

ReadOnly

A readonly field can be initialized either at the time of declaration or with in the constructor of same class. Therefore, readonly fields can be used for run-time constants.

Static

The static keyword is used to specify a static member, which means static members are common to all the objects and they do not tied to a specific object.

The maximum string content length quota (8192) has been exceeded while reading XML data.

Unhandled Exception: System.ServiceModel.CommunicationException: Error in deserializing body of reply message for operation ‘GetData’. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader.

--------------------------------------------------------------------------------------------------------------------------
Change in to the web.config file
--------------------------------------------------------------------------------------------------------------------------
<bindings>
      <basicHttpBinding>
        <binding>
          <readerQuotas maxStringContentLength=”2147483647″/>
        </binding>
      </basicHttpBinding>
</bindings>

Multiple Inheritance in c#

C# does not support multiple inheritance. However, you can use interfaces to implement multiple inheritance. The following program demonstrates this:


namespace MultipleInheritance
{
class A
{
public void disp()
{
Console.WriteLine("disp() method of Class A");
}
}

interface First
{
void mno();
}

interface Second
{
void pqr();
}

class Z:A,First,Second
{
public void mno()
{
console.WriteLine("mno() method is overridden.");
}
public void pqr()
{
console.WriteLine("pqr() method is overridden.");
}

}
}

Retrieving a Dataset from View State (Visual C#)

If you want dataset on same page and with post back it not destroy use viewstate      

DataSet ds = new DataSet();
            ViewState["ViewStateDateSet"] = ds;//Insert Data Set in View State


            DataSet ds1 = (DataSet)ViewState["ViewStateDateSet"]; // Retrive DataSet from ViewState

Using Common Table Expressions(CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

e.g.-

;with SelectCta(Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID) as
(
SELECT DISTINCT
                         IE_BundleNo.Bundle_No, IE_BundleNo.User_Id, CONVERT(varchar, IE_BundleNo.Alloted_Date, 103) AS AllotDate, UserLogin.UserName, IE_BundleNo.NoofBills,
                         IE_BundleNo.Net_Amt, IE_BundleNo.Claim_Amt, IE_BundleNo.Status, IE_BundleNo.Alloted_Date, IE_BundleNo.BundleNoText, IE_BillAllotStatus.AllotedUserId,
                        (select DISTINCT InsurerComName from IE_Master_InsuranceCompany where IE_Master_InsuranceCompany.InsurerComID=IE_BillAllotStatus.TPAID)  AS TPA,
'From ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateFrom, 103) + ' To ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateTo, 103) AS billdated,
IE_BillAllotStatus.TPAID
FROM            IE_BundleNo INNER JOIN
                         UserLogin ON IE_BundleNo.User_Id = UserLogin.UserID INNER JOIN
                         IE_BillAllotStatus ON IE_BundleNo.BundleNoText = IE_BillAllotStatus.BundleNoText
)
select Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID,
dbo.Get_Insurencecompany(TPAID,Bundle_No ) as InsCompanyName
 from SelectCta

Merge Statement in SQL Server

Many a times we came across situation to merge records between two tables. It will be like incremental update i.e., add new records and update existing records based on a reference column.

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command looks like as below:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_ matched> ];

e.g.-
Merge into  EmployeeBulk1 as tab1
using(select * from EmployeeBulk2) as tab2
on tab1.employeeid=tab2.employeeid
when matched then
update set tab1.employeename=tab2.employeename,
    tab1.employeedepartment=tab2.employeedepartment,
    tab1.Company=tab2.company
when not matched then
insert values(tab2.employeeid,tab2.employeename,tab2.employeedepartment,tab2.company);

Get HTML code from a website C#

string urlAddress = "http://vfet.no-ip.biz:9211/" + (HFHTML.Value.Remove(0, 2));

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(urlAddress);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

if (response.StatusCode == HttpStatusCode.OK)
{
Stream receiveStream = response.GetResponseStream();
StreamReader readStream = null;

if (response.CharacterSet == null)
{
readStream = new StreamReader(receiveStream);
}
else
{
readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
}

data = readStream.ReadToEnd();
response.Close();
readStream.Close();
}
mail.Body = data;

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

---

SQL Wildcard Examples

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

---

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Rolling up multiple rows into a single row and column for SQL Server data

If you want all of the data concatenated into a single column in a single row. In this tip we look at a simple approach to accomplish this.

 we can use the FOR XML PATH option to return the results as an XML string which will put all of the data into one row and one column.

SELECT PatFullName FROM IE_Patients
FOR XML PATH('')

STUFF function in SQL Server

The SQL Server (Transact-SQL) STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.

Syntax:

The syntax for the STUFF function in SQL Server (Transact-SQL) is:

STUFF( source_string, start, length, add_string )

Example:

SELECT STUFF('C#Infrasolution', 8, 2, '1234');
Result: 'C#Infra1234tion'

SQL SERVER – Computed Column – PERSISTED and Performance

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs.

There are two types of computed columns namely persisted and non-persisted.

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

Example:

Duration Calculator Between Two Dates Using ASP.Net C#

if (txt_LeaveFrom.Text != "" && txt_LeaveTo.Text != "")  

{      

DateTime FromYear = Convert.ToDateTime(clsObjCommon.GetDateForDB(txt_LeaveFrom.Text));

DateTime ToYear = Convert.ToDateTime(clsObjCommon.GetDateForDB(txt_LeaveTo.Text));

//Creating object of TimeSpan Class  

TimeSpan objTimeSpan = ToYear - FromYear;

//years  

int Years = ToYear.Year - FromYear.Year;

//months  

int month = ToYear.Month - FromYear.Month;

//TotalDays  

double Days = Convert.ToDouble(objTimeSpan.TotalDays);

//Total Months  

int TotalMonths = (Years * 12) + month;

//Total Hours  

double TotalHours = objTimeSpan.TotalHours;

//Total Minutes  

double TotalMinutes = objTimeSpan.TotalMinutes;

//Total Seconds  

double TotalSeconds = objTimeSpan.TotalSeconds;

//Total Mile Seconds  

double TotalMileSeconds = objTimeSpan.TotalMilliseconds;

} 

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can't go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

What is the difference between UNION and UNION ALL ?

Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values. 

How to validate field in asp.net using Javascript

Write in header section of page

<script type="text/javascript">

        function ValidateFiled() {

if (document.getElementById("<%= txt_LeaveNOD.ClientID  %>").value == "") {

                document.getElementById("<%= lbl_Msg.ClientID  %>").innerHTML = "Please Enter Number of Days...!";

                return false;

            }

}

    </script>

Write in body section of page

<asp:Button ID="btn_Save" runat="server" Text="Save" Width="15%" OnClientClick="return ValidateFiled();"/>