Thursday, September 24, 2015

SQL Server Joins With Examples

Types of Joins
  1. Inner Join
  2. Outer Join
    • Left outer Join
    • Right outer Join
    • Full outer Join
  3. Cross Join
  4. Equi Join
  5. Self Join

CREATE TABLE EmployeeDetails (
  EmpId int PRIMARY KEY,
  EmpFirstName varchar(50),
  EmpLastName varchar(50),
  Department varchar(50),
  DepartID int
)

CREATE TABLE EmpSalary (
  EmpID int,
  EmpFullName varchar(80),
  EmpSalary int,
  EmpWorkingYears int
)

SELECT
  *
FROM EmpSalary

INSERT INTO EmpSalary (EmpId, EmpFullName)
  VALUES (1007, 'Naresh Choulla')

INSERT INTO EmpSalary (EmpId, EmpFullName)
  VALUES (1008, 'Choulla Naresh')


INSERT INTO EmployeeDetails
  VALUES (1001, 'Naresh', 'Choulla', 'IT', 2)
INSERT INTO EmployeeDetails
  VALUES (1002, 'Sachin', 'Tendulkar ', 'IT', 2)
INSERT INTO EmployeeDetails
  VALUES (1003, 'Prince', 'Mahesh', 'Accounts', 3)
INSERT INTO EmployeeDetails
  VALUES (1004, 'Shruti', 'hassan', 'HR', 1)
INSERT INTO EmployeeDetails
  VALUES (1005, 'anushka', 'sharma', 'IT', 2)
INSERT INTO EmployeeDetails
  VALUES (1006, 'Hyd', 'Hitech', 'Accounts', 3)
-----------------------------------
SELECT
  *
FROM EmpSalary

INSERT INTO EmpSalary
  VALUES (1001, 'naresh choulla', 35000, 3)
INSERT INTO EmpSalary
  VALUES (1002, 'Varun Sharma', 25000, 2)
INSERT INTO EmpSalary
  VALUES (1003, 'Jaspreet Sharma', 20000, 2)
INSERT INTO EmpSalary
  VALUES (1004, 'Shruti Sharma', 18000, 1)
INSERT INTO EmpSalary
  VALUES (1005, 'Shaili Sharma', 25000, 2)
INSERT INTO EmpSalary (EmpFullName, EmpSalary, EmpWorkingYears)
  VALUES ('Shaili Sharma', 6000, 1)


SELECT
  *
FROM EmpSalary
SELECT  * FROM EmployeeDetails
-- \inner join
SELECT
  EmployeeDetails.EmpId,
  EmployeeDetails.EmpFirstName,
  EmpSalary.EmpSalary
FROM EmployeeDetails
JOIN EmpSalary
  ON EmployeeDetails.EmpId = EmpSalary.EmpID

--  Left outer join

SELECT
  e.EmpId,
  e.EmpFirstName,
  e.DepartID,
  s.EmpSalary
FROM EmployeeDetails e
LEFT OUTER JOIN EmpSalary s
  ON e.EmpId = s.EmpID

SELECT
  e.EmpId,
  e.EmpFirstName,
  e.DepartID,
  s.EmpSalary
FROM EmployeeDetails e
RIGHT OUTER JOIN EmpSalary s
  ON e.EmpId = s.EmpID

SELECT
  e.EmpId,
  e.EmpFirstName,
  e.DepartID,
  s.EmpSalary
FROM EmployeeDetails e
FULL OUTER JOIN EmpSalary s
  ON e.EmpId = s.EmpID

-- Cross join
SELECT
  EmployeeDetails.EmpId,
  EmployeeDetails.EmpFirstName,
  EmpSalary.EmpSalary
FROM EmployeeDetails
CROSS JOIN EmpSalary
--on EmployeeDetails.EmpId = EmpSalary.EmpID

-- Equi Join

SELECT
  *
FROM EmployeeDetails
JOIN EmpSalary
  ON EmployeeDetails.EmpId = EmpSalary.EmpID

-- Self Join
SELECT
  *
FROM EmployeeDetails
ALTER TABLE EmployeeDetails
ADD ManagerID int

UPDATE EmployeeDetails
SET ManagerID = 1002
WHERE empid = 1001
UPDATE EmployeeDetails
SET ManagerID = 1002
WHERE empid = 1005
UPDATE EmployeeDetails
SET ManagerID = 1006
WHERE empid = 1003
UPDATE EmployeeDetails
SET ManagerID = 1004
WHERE empid = 1007
UPDATE EmployeeDetails
SET ManagerID = 1004
WHERE empid = 1008

SELECT
  *
FROM EmployeeDetails

SELECT
  a.EmpId,
  a.EmpFirstName AS ManagerName,
  b.EmpId AS ManagerID,
  b.EmpFirstName AS EmployeeName
FROM EmployeeDetails a,
     EmployeeDetails b
WHERE a.EmpId = b.ManagerID

CREATE TABLE computer (
  CompID int,
  computerDes varchar(100),
  Price int
)

DELETE FROM computer
WHERE CompID = 2
INSERT INTO computer
  VALUES (1, 'Pentium 4,1GB RAM', 25000)
INSERT INTO computer
  VALUES (2, 'Dual Core,2GB RAM', 35000)
CREATE TABLE Addon (
  id int,
  description varchar(100),
  price int
)
INSERT INTO Addon
  VALUES (1, 'Speakers', 5000)
INSERT INTO Addon
  VALUES (2, 'printer', 15000)

SELECT
  *
FROM computer
SELECT
  *
FROM Addon


SELECT
  computer.computerDes,
  Addon.description,
  computer.Price + Addon.price AS totalprice
FROM computer
CROSS JOIN Addon
--on EmployeeDetails.EmpId = EmpSalary.EmpID

Tuesday, September 15, 2015

Import Data from Excel File to Asp.Net Mvc View and Read File


                                    @using (Html.BeginForm("viewname", "controller", FormMethod.Post, new { enctype = "multipart/form-data" }))
                                    {
                                        <div class="upload">
                                            @*<span class="btn btn-file">Upload<input type="file" /></span>
                                            <label class="btn btn-success">Fill Down columnM</label>*@
                                            <input type="file" class="btn btn-file" name="file" id="file" />
                                            <input type="submit" class="btn btn-success" value="Hiiiiii" />
                                            @*<input type="submit" class="btn btn-success" href="Calculation">Fill Down column </input>*@
                                        </div>
                                        <div class="row">
                                          
                                            <table id="tablepaging" class="table table-striped">
                                                <thead>
                                                    <tr>
                                                        <th>Id </th>
                                                        <th>Fname</th>
                                                        <th>Lname </th>

                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    @{
                                        if (ViewData["Excel"] != null)
                                        {
                                            System.Data.DataTable error = (System.Data.DataTable)ViewData["Excel"];
                                            int count = error.Rows.Count;
                                            for (int i = 0; i < count; i++)
                                            {
                                                System.Data.DataRow errorRow = error.Rows[i];
                                                int x = i % 2;
                                                if (x == 0)
                                                {
                                                    <tr class="even">
                                                        <td>@errorRow["Id"]</td>
                                                        <td>@errorRow["Fname"]</td>
                                                        <td>@errorRow["Lname"]</td>

                                                    </tr>
                                                }
                                                else
                                                {
                                                    <tr class="odd">
                                                        <td>@errorRow["Id"]</td>
                                                        <td>@errorRow["Fname"]</td>
                                                        <td>@errorRow["Lname"]</td>
                                                    </tr>
                                                }
                                            }
                                        }
                                                    }
                                                </tbody>
                                            </table>

                                            <div class="row">
                                                <div class="col-xs-6 col-sm-3">

                                                    <div class="form-group">
                                                        <a type="submit" class="btn btn-primary" href="Calculation">Save </a>
                                                    </div>
                                                </div>
                                            </div>
                                        </div>
                                   

                                    }
==================================
[HttpPost]
        [Authorize]
        public ActionResult Naresh(HttpPostedFileBase file)
        {
            string savedFileName = "";
            var r = new List<solutionname.model.ViewData>();
            foreach (string files in Request.Files)
            {
                HttpPostedFileBase hpf = Request.Files[files] as HttpPostedFileBase;
                if (hpf.ContentLength == 0)
                    continue;

                savedFileName = Path.Combine(Server.MapPath("~/Extensions"), Path.GetFileName(hpf.FileName));

                hpf.SaveAs(savedFileName); // Save the file

                r.Add(new CanPayApp.Models.ViewData()
                {
                    Name = hpf.FileName,
                    Length = hpf.ContentLength,
                    Type = hpf.ContentType
                });
            }

            // string csv_file_path = @"+savedFileName";
            DataTable csvData = new DataTable();
            using (TextFieldParser csvReader = new TextFieldParser(savedFileName))
            {
               
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                //read column names
                string[] colFields = csvReader.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }
                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
            ViewData["Excel"] = csvData;

            return View(r);
https://docs.google.com/viewer?a=v&pid=sites&srcid=ZGVmYXVsdGRvbWFpbnx3d3dhc3Bkb3RuZXRuYXJlc2hibG9nc3BvdGNvbXxneDoyNzhkNjcyY2U1NWYxOTYx
Subscribe to: Posts (Atom)