//--CREATE MAIN TABLE --//

//--CREATE MAIN TABLE --//

CREATE TABLE TEACHERLIST(
NAME VARCHAR(50),
QUALIFICATION VARCHAR(50),
ADD VARCHAR(50)
);

//-- CREATE TABLE WHERE TRIGGER AUTO FIRE ON CHANGE ON MAIN TABLE--//

CREATE TABLE TEACHERLISTOFFICE(
NAME VARCHAR(50),
QUALIFICATION VARCHAR(50),
ADD VARCHAR(50)
);

//------CREATE TRIGGER-----//

SYNTAX FOR INSERT:-

CREATE TRIGGER <TRIGGERNAME> AFTER INSERT ON <TABLENAME> FOR EACH ROW
BEGIN
  INSERT INTO <TABLENAME>
(<NAME_OF_COL_1>,<NAME_OF_COL_2>,<NAME_OF_COL_3>)
VALUES
(<VALUE_OF_COL_1>,<VALUE_OF_COL_1>,<VALUE_OF_COL_1>);

END

EXAMPLE:-

CREATE TRIGGER INSERTDATA AFTER INSERT ON TEACHERLIST FOR EACH ROW
BEGIN
INSERT INTO TEACHERLISTOFFICE
(NAME,QUALIFICATION,ADD) VALUES ('RAM','MBA','DELHI');
END

SYNTAX FOR UPDATE:-

CREATE TRIGGER <TRIGGERNAME> AFTER UPDATE ON <TABLENAME>
FOR EACH ROW
 BEGIN
UPDATE <TABLENAME>
SET
<NAME_OF_COL1>=NEW.<NAME_OF_COL1>
<NAME_OF_COL2>=NEW.<NAME_OF_COL2>
END

EXAMPLE:-

CREATE TRIGGER UPDATEDATA AFTER UPDATE ON TEACHERLIST
FOR EACH ROW
BEGIN
UPDATE TEACHERLISTOFFICE
SET
NAME=NEW.NAME,
QUALIFICATION=NEW.QUALIFICATION,
ADD=NEW.ADD;
END

//--CREATE STORE PROCEDURE--//

SYNTAX FOR STORE PROCEDURE:-

CREATE PROCEDURE <PROCEDURE_NAME>()
BEGIN
         ----WRITE SCRIPT HERE----
END

EXAMPLE OF STORE PROCEDURE:-

CREATE PROCEDURE GETDETAILS_TEACHER()
BEGIN
SELECT * FROM TEACHERLIST;
END

//-- CALLING OF STORE PROCEDURE --//

CALL <PROCEDURE_NAME>();

//--EXAMPLE OF CALLING OF STORE PROCEDURE--//

CALL GETDETAILS_TEACHER();

Sample MYSQL Query

                           Sample MYSQL Query 

/* Create database*/

create database demoDatabase;



/* Use database*/

use demoDatabase;

/* Create Table*/




create table employee (emp_Name varchar(50),emp_salary int(11));


/* description of Table*/



desc employee;



/* Insert data in Table*/

insert into employee (emp_name,emp_salary) values('Sonu',20000);

insert into employee (emp_name,emp_salary) values('Monu',2000);

insert into employee (emp_name,emp_salary) values('gita',10000);

insert into employee (emp_name,emp_salary) values('rita',1000);

insert into employee (emp_name,emp_salary) values('Sohan',200000);

insert into employee (emp_name,emp_salary) values('priyanshu',50000);

insert into employee (emp_name,emp_salary) values('Vickys',30000);


/* get all data from table*/

select * from employee;

















/* get Maximum Salary from the Table*/



select max(emp_salary) from employee;






/* get Minimum Salary from the Table*/

select min(emp_salary) from employee;






/* get Average Salary from the Table*/

select avg(emp_salary) from employee;







/* get Sum of Salary from the Table*/

select sum(emp_salary) from employee;






select emp_Name,emp_salary from employee where emp_salary in (1000,20000);



select emp_name,emp_salary from employee where emp_salary not in (1000,20000);



/* Search name which is started from S*/

select emp_name,emp_salary from employee where emp_Name like '%S';






/* Search name which is end with S*/

select emp_name,emp_salary from employee where emp_Name like 'S%';






/* Search name which has from S*/



select emp_name,emp_salary from employee where emp_Name like '%S%';

Program for Create Table using C# Console Application

Program for Create Table using C# Console Application

Main Program

Output Screen                          

SQL JOINS

SQL JOINS

SQL JOIN

SQL JOIN:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: returns rows when there is a match in one of the tables.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

WE can take each first four joins in Details :

We have two tables with the following values.

TableA

id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc                                           

TableB

id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai

....................................................................

INNER JOIN

Note :it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB

Syntax

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;

Result Will Be

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm

LEFT JOIN

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age   Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL

RIGHT JOIN

Note : will give all selected rows in TableB, plus any common selected rows in TableA.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age     Place
...............................................................................
arun                        prasanth                    24     kerala
ann                         antony                      24     usa
sruthy                      abc                         25     ekm
NULL                        NULL                        24     chennai

FULL JOIN

Note : It is same as union operation, it will return all selected values from both tables.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age    Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
NULL                        NULL                        24    chennai

Mail System API in create C#

Mail System API in create C#


using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Web;

namespace CationHRManagementSystem.WebAPI
{
 
    public class SendMailAPI
    {
   
        public void SendEmail(string emailID,string mailSubject,string mailBody)
        {
            //MailMessage("From","To")
            using (MailMessage mm = new MailMessage("cationtest@gmail.com", emailID))
            {
                mm.Subject = mailSubject;
                mm.Body = mailBody;
                //if (fuAttachment.HasFile)
                //{
                //    string FileName = Path.GetFileName(fuAttachment.PostedFile.FileName);
                //    mm.Attachments.Add(new Attachment(fuAttachment.PostedFile.InputStream, FileName));
                //}
                mm.IsBodyHtml = true;
                SmtpClient smtp = new SmtpClient();
                smtp.Host = "smtp.gmail.com";
                smtp.Port = 465;
                smtp.EnableSsl = true;
                NetworkCredential NetworkCred = new NetworkCredential("cationtest@gmail.com", "test_12345");
                smtp.UseDefaultCredentials = true;
                smtp.Credentials = NetworkCred;
                smtp.Port = 587;
                smtp.Send(mm);
            }
        }
    }
}



Use of  Mail API

using CationHRManagementSystem.WebAPI;

protected void sendMail()
{
 SendMailAPI mailObj = new SendMailAPI();
 mailObj.SendEmail(txtTo.Text, txtSubject.Text, txtBody.InnerText);
}

Validate Upload file type using Javascript

Validate Upload file type using Javascript

function()

{  

           var fup = document.getElementById('FileName');

            var fileName = fup.value;

            var ext = fileName.substring(fileName.lastIndexOf('.') + 1);

            if (ext == "pdf" || ext == "JPEG" || ext == "jpeg" || ext == "jpg" || ext == "JPG") {

                return true;

            }

            else {

                alert("Upload pdf or images only");

                fup.focus();

                return false;

            }

}

Function for close Popup Window using Javascript

function()

{

window.close();

}