Thursday 16 July 2015

Code to send email from Gmail in .net

        private void SendEmail(string emailBody)
        {
            try
            {
                MailMessage message = new MailMessage();

                //SmtpClient smtp = new SmtpClient();

                message.From = new MailAddress(ConfigurationManager.AppSettings["FROMEMAIL"].ToString());

                message.To.Add(new MailAddress(emailTo));

                message.CC.Add(new MailAddress(emailCC));

                message.Subject = emailSubject;

                message.Body = emailBody.ToString();

                message.IsBodyHtml = true;

                var client = new SmtpClient("smtp.gmail.com", 587)
                {
                    Credentials = new NetworkCredential(ConfigurationManager.AppSettings["FROMEMAIL"].ToString(), ConfigurationManager.AppSettings["FROMPWD"].ToString()),
                    EnableSsl = true
                };
                client.Send(message);
            }
            catch (Exception ex)
            {
                objLogWriter.LogWrite(ex.Message);
            }
        }

Execute Stored Procedure with parameters and Fill Dataset

Introcution: Here you can call and execute stored procedure from code behind and pass the parameters and fill dataset.

Implementation:
DataSet ds = new DataSet();

            DataTable dt;

            sb.Append("<table>");

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {

                    using (SqlCommand command = new SqlCommand("sp_DistwiseDetails1", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        command.Parameters.Add("@RetriveType", SqlDbType.VarChar).Value = 2;

                        command.Parameters.Add("@FromDate", SqlDbType.VarChar).Value = DateTime.Now.AddYears(-2);

                        command.Parameters.Add("@ToDate", SqlDbType.VarChar).Value = DateTime.Now.ToShortDateString();

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(ds);
                        }

                    }
                }
                dt = ds.Tables[0];
}
catch
{
}

Monday 6 July 2015

SQL Server Database Queries Part 1

Select Statement:

select * from Subjects;

Aliasing Column Name:
you can rename column according to your need using AS keyword.
select subjectname as subject from subjects;


Eliminating duplicate records:(Distinct Clause)
you can eliminate duplicate records using DISTINCT Keyword from resultset.
Select distinct subjectname from subjects;

Filtering Data(Where Clause):

you can filter data using WHERE clause with some condition,
Select subject;name from subjects where subjectid=3;
Select subject;name from subjects where subjectid>3;



The 'Not' Clause:

you can use NOT keyword condition
Select subjectname from subjects where subjectname IS NOT NULL;
SELECT SUBJECTNAME FROM SUBJECTS WHERE SUBJECTID NOT IN (2,3);

Filtering String:
you can filter string using LIKE keyword and %.
'H%' will return all the records start with 'H'.
'%H' will return all the records end with 'H'.
'%H%'  will return all the records start and end with 'H'.

Select subjectname from subject where name like('H%')  ;
  
The In and Between Keywords:

you can use IN clause when you have multiple ids you can get all records which are IN();
select * from subjects where subjectid in (2,3);

you can user BETWEEN clause when you want to get records between some range.

select * from subjects where subjectid BETWEEN 2 and 3;

Sorting Data(Order By Clause):
you can sort resultset using ORDER BY clause.
select * from subjects ORDER BY subjectname;

Limiting Records(Top Clause):

you can limit records by using TOP clause in your query.
select TOP 2 * from subjects ORDERBY subjectname;

OFFSET FETCH:

you can use OFFSET to keep and skip records. It is useful in paging concept.
SELECT * FROM SUBJECTS OFFSET 10 ROWS FETCH NEXT 25 ROWS ONLY;

AGGREGATING DATA; GROUP BY AND HAVING CLAUSE
you can use GROUP BY clause to get record set in Group.
SELECT COUNT(*) FROM SUBJECTS GROUP BY SUBJECTNAME;

Select from multiple tables (Using Joins)

Joins are very useful when you want to get data from multiple tables.
select * from subjects , books;

select a.subjectname,b.bookname from subjects a, books b where a.SubjectID=b.Subject;

There are different type of joins in sql.

Cross Join:

It will return Carthesian Product. 

select * from books CROSS JOIN subjects;

Inner Join:

It is the most useful join. It is optional to use Inner Keyword. It is used in place of where and is a
proper way of join. it will return all the matching rows based on conditon.

select * from subjects INNER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Outer Join:

Outer joins are used when we want to matching and unmatching data from tables. it is of two types.

Left Outer Join:

It returns all matching of left and all of right records.


SELECT * FROM SUBJECTS LEFT OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Right Outer Join:

It return all record of left and matching of right.

SELECT * FROM SUBJECTS Right OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT; 

Ranking Functions:
 
 There are four ranking functions in sql server.
  1. ROW_NUMBER
  2. RANK
  3. DENSE_RANK
  4. NTILE
ROW_NUMBER simply select the no of current row.
RANK and DENSE_RANK assisgn unique number to each row.
RANK return total no of rows of same rank plus 1.
DENSE_RANK return rank plus 1.