Efficient ways to select rows from sys.partition_range_values or from sql_variant data type column

Often we select records with below statement.
Select * from XYZTable

But it may not work for sys.partition_range_values, when partition scheme is created with different data ranges, like numberic (int, bigint etc...) or datetime in same database. Select query
execution result will end up with error output.

SELECT * FROM sys.partition_range_values

Output will be.
An error occurred while executing batch. Error message is: Input string was not in a correct format.

Why it is like this, error in SQL server Management Studio? Answer is NO, below is the schema structure of the system table of sys.partition_range_values, here sql_variant data type can store value with any data type, hence while retrieving SQL doesn’t know what data-type casting is needed.

So what are the ways to retrieve records?  Here are the ways…..

1.       Write below query to simply convert data in varchar…

SELECT function_id

,boundary_id

,parameter_id,

CAST(Value AS varchar) As Value FROM sys.partition_range_values

But it may not get the data in form in it is required.

2.  Determine the base type of target data which will be stored as sql_variant with the help of SQL_VARIANT_PROPERTY, after that write down select case statement to fetch the data with the specific data type.
       SELECT function_id

,boundary_id

,parameter_id,SQL_VARIANT_PROPERTY(Value,'BaseType') as DataType,

       CASE SQL_VARIANT_PROPERTY(Value,'BaseType')

         WHEN 'datetimeoffset' THEN CAST(Value AS datetimeoffset) END AS Value

FROM sys.partition_range_values
In above sample query datetimeoffset has been demonstrated, it can be replaced with bigint, int, time, nchar etc..

This can apply to any table which is using sql_variant data type, one thing to know here is to use system function SQL_VARIANT_PROPERTY that will help to differentiate the data based on its type, even the return value can be given to front-end[.Net, Java etc..] if need to segregate the  data based on their type.

Securing PDF with not to Print and not to Copy content

Software application is aiding business in accelerating the day to day activities as well as makes so many things easy which eliminates delay in time, costs in $ etc... 

Most of business need data which can be shared over to other in universal or portal format, PDF document is the most common way to share the data across. These data is necessary to each participating roles in business, those roles can be customer or business owner or other business organization and many ....

Now the Data is very critical in all business applications, sharing them on PDF is very easy now a day you get your bank statement by email  and that is in PDF format which is password protected, until you don't enter correct password you cannot view or print that document.

But what if you want to make that PDF just read only i.e. disable print and content copy paste?

Well this can be a business need to, that can be done with itextsharp and here is the sample code/method [in C #] which will secure pdf.

//fileBytes :  can be from service over soap or tcp, it can be stored in DB as file stream or in var binary 

public static byte[] SecurePrintAndExport(byte[] fileBytes)

{

       byte[] securedFilebytes;

       string securedKey = Guid.NewGuid().ToString();

       using (MemoryStream report = new MemoryStream(fileBytes))

       {

              PdfReader reader = new PdfReader(report);

              int numberOfPages = reader.NumberOfPages;

              using (MemoryStream newStream = new MemoryStream())

              {

                     UTF8Encoding encoding = new UTF8Encoding();

                     PdfStamper stamper = new PdfStamper(reader, newStream);

                     stamper.SetEncryption(null, encoding.GetBytes(securedKey),PdfWriter.AllowCopy, PdfWriter.STRENGTH40BITS);

                     stamper.SetEncryption(null, encoding.GetBytes(securedKey),PdfWriter.ALLOW_ASSEMBLY | PdfWriter.ALLOW_SCREENREADERS, PdfWriter.STRENGTH40BITS);

                     stamper.Writer.CloseStream = false;

                     stamper.Close();

                     newStream.Flush();

                     newStream.Position = 0;

                     securedFilebytes = new byte[newStream.Capacity];

                     securedFilebytes = newStream.ToArray();

              }

              reader.Close();

       }

       return securedFilebytes;

}

//How to make call of it...............

byte[] fileBytes = System.IO.File.ReadAllBytes(@"D:\Temp\TestPDF.pdf");

byte[] securedPdf = SecurePrintAndExport(fileBytes);

Making own Serializable class

To make the object to serialize in your own way better to implement ISerializableinside the class, specifying the members whose data need to be serialize inside method GetObjectData (a ISerializable member method) , parameter ofSerializationInfo will hold the key value pair kind of information to allow the Serialization.

Also class needs to have constructor having same signature of GetObjectData, so that at the time of de-serializing the serialized data will be passed via parameter of the constructor, as data are already added in Key Value pare kind of form in GetObjectData its easy to get restore the state to its member.

To illustrate the usage in application, the base class is used to make the member serialize

using System;

using System.IO;

using System.Reflection;

using System.Runtime.Serialization;

using System.Runtime.Serialization.Formatters.Binary;

using System.Collections.Generic;

namespace SerializationExample

{

    class Program

    {

        static void Main(string[] args)

        {

            JaiHo objNewCompany = new JaiHo();

            objNewCompany.Country = "INDIA";

            objNewCompany.Employee.Add(new Employee("A", 1));

            objNewCompany.Employee.Add(new Employee("B", 2));

            objNewCompany.Name = "New Song";

            objNewCompany.NatureOfBusiness = "Music";

            Console.WriteLine("Before Serializing the object..");

            PrintObjectInfo(objNewCompany);

            SerializerHelper objSerializer = new SerializerHelper();

            objSerializer.Serialize(objNewCompany);

            JaiHo objDeserializedCompany = (JaiHo)objSerializer.DeSerialize();

            Console.WriteLine("After De-Serializing the object..");

            PrintObjectInfo(objDeserializedCompany);

            Console.ReadLine();

        }

        static void PrintObjectInfo(JaiHo objJaiHo)

        {

            string strInfo = string.Format("\r\n************************************" +

                "\r\nName of Company : {0} \r\nNaure of Business : {1}\r\n" +

                "Located In : {2} \r\n", objJaiHo.Name, objJaiHo.NatureOfBusiness, objJaiHo.Country);

            foreach (Employee objEmp in objJaiHo.Employee)

            {

                strInfo += string.Format("\tEmployee ID : {0}\t Employee Name : {1}\r\n", objEmp.ID, objEmp.Name);

            }

            Console.WriteLine(strInfo);

        }

    }

    class SerializerHelper : IDisposable

    {

        FileStream objStream = new FileStream("Data.dep", FileMode.Create);

        public void Serialize(object obj)

        {

            try

            {

                BinaryFormatter objFormatter = new BinaryFormatter();

                objFormatter.Serialize(objStream, obj);

            }

            catch (SerializationException e)

            {

                Console.WriteLine("Failed to serialize : " + e.Message);

                throw;

            }

        }

        public object DeSerialize()

        {

            object objReturn = null;

            try

            {

                BinaryFormatter objFormatter = new BinaryFormatter();

                objStream.Position = 0;

                objReturn = objFormatter.Deserialize(objStream);

            }

            catch (SerializationException e)

            {

                Console.WriteLine("Failed to serialize : " + e.Message);

                throw;

            }

            return objReturn;

        }

        public void Dispose()

        {

            objStream.Close();

        }

    }

    [Serializable]

    abstract class Company : ISerializable

    {

        private List<Employee> objEmployee;

        private string strName;

        public List<Employee> Employee

        {

            get { return objEmployee; }

            set { objEmployee = value; }

        }

        public string Name

        {

            get { return strName; }

            set { strName = value; }

        }

        /// Normal Constructor

        public Company()

        {

            objEmployee = new List<Employee>();

        }

        ///

        /// This Constructor required to restore the object state when de-serializing it.

        protected Company(SerializationInfo info, StreamingContext context)

        {

            Type objType = this.GetType();

            foreach (PropertyInfo propInfo in objType.GetProperties())

            {

                propInfo.SetValue(this, info.GetValue(propInfo.Name, propInfo.PropertyType), null);

            }

        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)

        {

            Type objType = this.GetType();

            foreach (PropertyInfo propInfo in objType.GetProperties())

            {

                info.AddValue(propInfo.Name, propInfo.GetValue(this, null), propInfo.PropertyType);

            }

        }

    }

    [Serializable]

    class JaiHo : Company

    {

        public JaiHo()

        { }

        /// This Constructor required to restore the object state when de-serializing it.

        private JaiHo(SerializationInfo info, StreamingContext context)

            : base(info, context)

        { }

        private string strNatureOfBusiness;

        private string strCountry;

        public string NatureOfBusiness

        {

            get { return strNatureOfBusiness; }

            set { strNatureOfBusiness = value; }

        }

        public string Country

        {

            get { return strCountry; }

            set { strCountry = value; }

        }

    }

    [Serializable]

    class Employee

    {

        private string strName;

        private int nID;

        public Employee()

        {

        }

        public Employee(string name, int ID)

        {

            this.strName = name;

            this.nID = ID;

        }

        public string Name

        {

            get { return strName; }

            set { strName = value; }

        }

        public int ID

        {

            get { return nID; }

            set { nID = value; }

        }

    }

}