update user_sdo_geom_metadata

update user_sdo_geom_metadata
    set DIMINFO=
  (
select diminfo from user_sdo_geom_metadata where table_name = upper('LB_INTERNATIAL_BORDERS_GIS')
),
SRID=4326
where table_name like '%VU' ;

Export html to PDF and Excel

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

using GisSolution.App_Code;

using System.Web;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.tool.xml;

using iTextSharp.text.html.simpleparser;

namespace Application

{

    public partial class ParcelPoints : Page

    {

        string BQP_CODE;

        const string query = "select رقم_الطلب, NO_POINT,COORDINATE_X,COORDINATE_Y from IDENTIFY_SUR_POINTS where BQP_CODE='{0}'";

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                BindGrid();

                ScriptManager scriptManager = ScriptManager.GetCurrent(Page);

                scriptManager.RegisterPostBackControl(btnExport);

                scriptManager.RegisterPostBackControl(btnExportToPDF);

            }

        }

        private void BindGrid()

        {

            //if (string.IsNullOrEmpty(hf_BQP_CODE.Value))

            BQP_CODE = Request.QueryString["BQP_CODE"];

            hf_BQP_CODE.Value = BQP_CODE;

            if (!string.IsNullOrEmpty(BQP_CODE))

            {

                DBConnection dataAccess = new DBConnection();

                using (DataTable dt = dataAccess.GetData(string.Format(query, BQP_CODE)))

                {

                    GV_ParcelsPoints.DataSource = dt;

                    GV_ParcelsPoints.DataBind();

                }

            }

        }

        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)

        {

            GV_ParcelsPoints.PageIndex = e.NewPageIndex;

            BindGrid();

        }

        protected void ExportToExcel(object sender, EventArgs e)

        {

            if (!string.IsNullOrEmpty(hf_BQP_CODE.Value))

            {

                DBConnection dataAccess = new DBConnection();

                ExportToExcel(dataAccess.GetData(string.Format(query, hf_BQP_CODE.Value)), string.Format(@"Parcel_{0}_Points_{1}", hf_BQP_CODE.Value,DateTime.Now));

            }

        }

        protected void btnExportToPDF_Click(object sender, EventArgs e)

        {

            if (!string.IsNullOrEmpty(hf_BQP_CODE.Value))

            {

                DBConnection dataAccess = new DBConnection();

                ExportGridToPDF(dataAccess.GetData(string.Format(query, hf_BQP_CODE.Value)), string.Format(@"Parcel_{0}_Points_{1}", hf_BQP_CODE.Value, DateTime.Now));

            }

        }

        protected void ExportToExcel(DataTable tblData, string fileName)

        {

            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");

            HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

            HttpContext.Current.Response.Clear();

            HttpContext.Current.Response.Buffer = true;

            HttpContext.Current.Response.Charset = "UTF-8";

            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            StringWriter stringWrite = new StringWriter();

            HtmlTextWriter htmlWrite =

            new HtmlTextWriter(stringWrite);

            DataGrid grdExcel = new DataGrid();

            grdExcel.AllowPaging = false;

            grdExcel.DataSource = tblData;

            grdExcel.DataBind();

            foreach (DataGridItem i in grdExcel.Items)

            {

                foreach (TableCell tc in i.Cells)

                    tc.Attributes.Add("class", "text");

            }

            grdExcel.RenderControl(htmlWrite);

            HttpContext.Current.Response.Write("");

            string style = @" ";

            HttpContext.Current.Response.Write(style);

            HttpContext.Current.Response.Write(stringWrite.ToString());

            HttpContext.Current.Response.Flush();

            HttpContext.Current.Response.End();

        }

        private void ExportGridToPDF(DataTable tblData, string fileName)

        {

            Response.ContentType = "application/pdf; charset=utf-8\"";

            Response.AddHeader("content-disposition", "attachment;filename=UserDetails.pdf");

            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            StringWriter sw = new StringWriter();

            HtmlTextWriter hw = new HtmlTextWriter(sw);

            GV_ParcelsPoints.AllowPaging = false;

            BindGrid();

            int cc=GV_ParcelsPoints.Rows.Count;

            GV_ParcelsPoints.RenderControl(hw);

            StringReader sr = new StringReader(sw.ToString());

            Document pdfDoc = new Document(PageSize.A4, 7f, 7f, 7f, 0f);

            HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

            PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

            pdfDoc.Open();

            htmlparser.Parse(sr);

            pdfDoc.Close();

            Response.Write(pdfDoc);

            Response.End();

        }

        //Confirms that an HtmlForm control is rendered for the

        //specified ASP.NET server control at run time.

        public override void VerifyRenderingInServerForm(Control control)

        { }

    }

}

SQL Training - 08

DENSE_RANK, With

DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking

With :Specifies a temporary named result set

With R as

(select salary, DENSE_RANK() over (order by salary desc) as SalaryRank

from Employees

)

select salary from R where R.SalaryRank=1

with Ties

Used when you want to return two or more rows that tie for last place in the limited results set

Like Tawjihi Results,,
select top 10 with Ties *

from employees

order by salary

 Trigger 
Level 1: on Database Level
new query window and type the following  

Create trigger [tr_createTable]

on Database

for Create_Table, Alter_Table, Drop_Table, Table_Rename

as

begin

 rollback

 Print 'you are prohibited from  create, alter, drop, rename a table'

end

GO

ENABLE TRIGGER [tr_createTable] ON DATABASE

DROP TRIGGER [tr_createTable] ON DATABASE

Level 2: on Table Level

You can use one of the following to insert in Audit via trigger 

1-

Create trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Declare @Id int

 Declare @Name varchar(50)

 Declare @Salary float

 select @Id= id, @Name=_name, @Salary=salary

 from inserted

 Insert into Employees_Audit values(@id,@name, @Salary, GETDATE());

end

2-

Create trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Insert into Employees_Audit

 select id, _name, salary, GETDATE()

 from Inserted;

end

You can use one of the following to insert in Audit via trigger  and prevent insert using condition 

ALTER trigger [dbo].[tr_employeeInsert]

on [dbo].[Employees]

for Insert

as

begin

 Declare @Id int

 Declare @Name varchar(50)

 Declare @Salary float

 select @Id= id, @Name=_name, @Salary=salary

 from inserted

 if(@Salary=0)

 begin

  RaisError('Invalid salary', 16, 1)

  rollback

  return

 end

 Insert into Employees_Audit values(@id,@name, @Salary, GETDATE(),'Insert');

end

L To insert from view

use instead of insert to override SQL default insert statement

create view [dbo].[vu_emp] as

SELECT        Employees.id as empid, Employees._name, Employees.salary, depts.dept

FROM            Employees inner JOIN

                         depts on Employees.deptid= depts.deptid

create TRIGGER [dbo].[tr_inseadofinsert]

   ON  [dbo].[vu_emp]

   instead of insert

AS

BEGIN

       declare @deptid  int

       select @deptid=depts.deptid

       from depts inner join inserted on depts.dept=inserted.dept

       Insert into Employees

       select _name, salary, @deptid

       from inserted

END

Level 3: on Server Level
instead

create trigger tr_serverlevel

on all server

for create_table

as

begin

select  EventData()

end

after clicking on the result link it will open in xml format contains the following

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2018-03-29T08:51:39.217</PostTime>

  <SPID>59</SPID>

  <ServerName>CI....</ServerName>

  <LoginName>MicrosoftAccount\moh...</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>Person</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>test_server_trigger</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>create table test_server_trigger( id int)</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

No lock and tran

select * from Employees with (nolock)

begin tran

       update Employees set salary=2000

       where id=2

       update Person

       set FName='A-A'

       where Id=136

commit tran