SQL SERVER – Introduction to Rollup Clause

Create a Database
Then new Script
Then Fire this.
------------------------------------------------------------------------------

CREATE TABLE tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] INT)
GO
INSERT INTO tblPopulation VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO tblPopulation VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO tblPopulation VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO tblPopulation VALUES('India','Karnataka','Bangalore',9.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO tblPopulation VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Pune',20)
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO tblPopulation VALUES('India', 'Maharastra','Nashik',6.5)
GO

---To Display 
SELECT 
ISNULL(Country,'All Country') AS Country,
ISNULL([State],'All State') AS State,
ISNULL(City,'All City') AS City,SUM ([Population (in Millions)]) AS [Population (in Millions)]FROM tblPopulationGROUP BY Country,[State],City WITH ROLLUP





Details: http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause
1 comment:

Upload Excel to SQL Server using OleDbConnection

importExcel.aspx Page
=====================================================================
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="importExcel.aspx.cs" Inherits="Eisk.Web.admin.importExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head runat="server">
    <title>Upload Excel File to Database </title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:FileUpload ID="fluInput" runat="server" />
    <asp:Button ID="btnImport" Text="Import" runat="server" OnClick="btnUploadFile_Click"
        CssClass="button medium green" />
    </form>
</body>
</html>
=====================================================================
Code Behind  importExcel.aspx.cs
===================================================================== 
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Eisk.BusinessEntities;
using Eisk.BusinessLogicLayer;

namespace Eisk.Web.admin
{
    public partial class importExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnUploadFile_Click(object sender, EventArgs e)
        {
            ConvertFile();
        }

        protected void ConvertFile()
        {
            if (fluInput.HasFile)
            {

                string strFileName = Server.HtmlEncode(fluInput.FileName);

                string strExtension = Path.GetExtension(strFileName);

                if (strExtension != ".xls" && strExtension != ".xlsx")
                {
                    Response.Write("<script>alert('Please select a Excel spreadsheet to import!');</script>");
                    return;
                }

                string strUploadFileName = "~/admin/Excell/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

                strUploadFileName = Server.MapPath(strUploadFileName);

                fluInput.SaveAs(strUploadFileName);

                string connectionString = "";

                if (strExtension == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 8.0;HDR=YES;'";
                }
                else
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                }

                try
                {
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    connection.Open();

                    string sheetName = "Sheet1";

                    string query = "SELECT * FROM [" + sheetName + "$]";

                    OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable();
                    adapter.Fill(ds);


                    dt = ds.Tables[0];
                    adapter.Fill(dt);
                    connection.Close();

                    int number_of_columns = dt.Columns.Count;
                    int number_of_rows = ds.Tables[0].Rows.Count / 2;
                    string[,] sheetEntries = new string[number_of_rows, number_of_columns];
                    string[] columnNames = new string[number_of_columns];

                    int startingColumn = 1;

                    for (int i = startingColumn; i < number_of_rows; i++)
                    {
                        for (int j = 0; j < number_of_columns; j++)
                            sheetEntries[i, j] = dt.Rows[i].ItemArray.GetValue(j).ToString();
                    }

                    List<ActivationCode> activationCodeList = CovertToList(sheetEntries, number_of_columns, number_of_rows);

                    ExportToSQLServerActivationCode(activationCodeList);

                    if (File.Exists(strUploadFileName))
                    {
                        File.Delete(strUploadFileName);
                    }
                }
                catch (Exception ex)
                {

                }
            }
        }

        private List<ActivationCode> CovertToList(string[,] sheetEntries, int number_of_columns, int number_of_rows)
        {
            List<ActivationCode> aclist = new List<ActivationCode>();
            ActivationCode aCode = null;

            DateTime toDay = DateTime.Now;

            for (int i = 0; i < number_of_rows; i++)
            {
                aCode = new ActivationCode();

                string activationCodeName = StringNullOrEmptyChecking(sheetEntries[i, 2]);
                string servicePlanSKU = StringNullOrEmptyChecking(sheetEntries[i, 5]);

                if (!string.IsNullOrEmpty(activationCodeName) && !string.IsNullOrEmpty(servicePlanSKU))
                {
                    aCode.ActivationCodeName = activationCodeName;
                    aCode.ServicePlanSKU = servicePlanSKU;
                    aCode.MinProductsNo = 1;

                    string maxProductsNo = StringNullOrEmptyChecking(sheetEntries[i, 11]);

                    if (!string.IsNullOrEmpty(maxProductsNo))
                    {
                        aCode.MaxProductsNo = Convert.ToInt16(maxProductsNo);
                    }
                    else
                    {
                        aCode.MaxProductsNo = 1;
                    }

                    aCode.IsUsed = false;
                    aCode.UploadDate = toDay;

                    aclist.Add(aCode);
                }
            }
            return aclist;
        }

        private string StringNullOrEmptyChecking(object value)
        {
            if (value == null)
                return null;

            string returnValue = null;

            if (value != null)
            {
                returnValue = value.ToString();
            }
            if (value == "")
            {
                returnValue = null;
            }

            return returnValue;
        }

        private void ExportToSQLServerActivationCode(List<ActivationCode> activationCodeList)
        {
            ActivationCodeBLL activationCodeBLL = new ActivationCodeBLL();
            ActivationCodeRejectedBLL activationCodeRejectedBLL = new ActivationCodeRejectedBLL();

            List<ActivationCode> allActivationCodeList = activationCodeBLL.GetAllActivationCodes();

            List<ActivationCode> allActivationCodeListSelected = new List<ActivationCode>();

            List<ActivationCodeRejected> activationCodeRejectedList = new List<ActivationCodeRejected>();

            foreach (ActivationCode item in activationCodeList)
            {
                ActivationCode isExists = allActivationCodeList.Find(x => x.ActivationCodeName == item.ActivationCodeName);

                if (isExists == null)
                {
                    allActivationCodeListSelected.Add(item);
                }
                else
                {
                    ActivationCodeRejected activationCodeRejected = new ActivationCodeRejected();

                    activationCodeRejected.ActivationCodeName = item.ActivationCodeName;
                    activationCodeRejected.ServicePlanSKU = item.ServicePlanSKU;
                    activationCodeRejected.MinProductsNo = item.MinProductsNo;
                    activationCodeRejected.MaxProductsNo = item.MaxProductsNo;
                    activationCodeRejected.IsUsed = item.IsUsed;
                    activationCodeRejected.UploadDate = item.UploadDate;

                    activationCodeRejectedList.Add(activationCodeRejected);
                }
            }

            foreach (var item in allActivationCodeListSelected)
            {
                activationCodeBLL.CreateNewActivationCode(item);
            }

            foreach (var item in activationCodeRejectedList)
            {
                activationCodeRejectedBLL.CreateNewActivationCodeRejected(item);
            }
        }
    }
}
No comments:
Subscribe to: Posts (Atom)