T-SQL Enhancements in SQL Server 2008 phase-4.2

Hello,

In my previous post, we have seen table value parameters, how to define it in sql server and asp.net, How to use Table Valued Parameters, Advantages of Table Valued Parameters, Limitations to passing Table Parameters etc.

Today we will see the example of Table Valued Parameters

I have used two tables Orders and OrderDetails. You can see the User Defined Table Type with the same structure.

I have created procedure “InsertOrders”. You need to pass the Table Type Variable as a parameter from asp.net

Example:

CREATE TABLE [Orders]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	CustomerId INT NOT NULL,
	OrderedDate DATE NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TABLE [OrderDetails]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	ProductId INT NOT NULL,
	Quantity INT NOT NULL,
	Price MONEY NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TYPE OrderUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	CustomerId INT,
	OrderedDate DATE
)
GO

CREATE TYPE OrderDetailUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	ProductId INT,
	Quantity INT,
	Price MONEY
)
GO

CREATE PROCEDURE InsertOrders
(
	@OrderHeaders AS OrderUdt READONLY,
	@OrderDetails AS OrderDetailUdt READONLY
)
AS
BEGIN

 -- Bulk insert order header rows from TVP
 INSERT INTO [Orders]
 SELECT *, SYSDATETIME() FROM @OrderHeaders

 -- Bulk insert order detail rows from TVP
 INSERT INTO [OrderDetails]
 SELECT *, SYSDATETIME() FROM @OrderDetails

 END
GO

SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1

/*DROP TYPE OrderDetailUdt
DROP TYPE OrderUdt*/

SELECT * FROM Orders
SELECT * FROM OrderDetails

Example of xml and xsl

Hello all,

All we know use of xml. Xml is use for store data it will consume less space and easy to transfer. But, most of us confuse in xsl use. Xsl is style sheet of xml. XSL is use to represent your xml data in html, pdf, doc or plain text. Without use of css how to represent xml data in html at that you can use xsl.

Today i want to share one simple example to understand xml and xsl. Following is xml we will use to transform in html.





SQL
http://www.blogfornet.com/sql/


HTML
http://www.blogfornet.com/html/


ASP.Net
http://www.blogfornet.com/asp-net/


C#
http://www.blogfornet.com/c-2/


Now, create following xsl file name with example.xsl.











XSL Example

That’s it. This is simple example of XML XSL use. We will discuss advance XSLT in next phase…

T-SQL Enhancements in SQL Server 2008 phase-4.1

Hello,

we have seen some features of sql server 2008 in my previous article like Grouping Sets, cube and rollup, grouping_ID, merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe Table-Valued Parameters.

Table-Valued Parameters

  • Helps address the need to pass “array” of elements to stored procedure / function
  • Solutions pre-2008
    1. Dynamic SQL : Suffers from security and performance problems:
    a. Security: SQL Injection
    b. Performance: inefficient reuse of execution plans
    2. UDF that splits array to individual elements
    a. Overcomes security and performance issues
    b. Complicated

Advantages of Table Valued Parameters

  • It is easier to maintain and provides better performance than the use of temporary tables
  • Reduces multiple round trips between client and the server
  • It is much easier to program and use Table Valued Parameters
  • It doesn’t cause the statements to recompile
  • It enables you to include complex business logic in a single routine
  • They have a well defined scope at the end of which they are automatically cleared

How to use Table Valued Parameters

  • Create a User Defined Table Type with the same structure as that of the table, which can be passed as a Table Valued Parameter to a SP or a Function
  • Create a stored procedure which accepts table type as a parameter
  • Declare a variable as Table Type and refer the Table Type which you have created earlier
  • Populate the Table Type Variable by using an insert statement
  • Once the Table Type Variable is created and populated then the next step will be to pass the Table Type Variable as a parameter to the stored procedure or a function

Limitations to passing Table Parameters

  • You must use the READONLY clause when passing in the table valued variable into the procedure.
  • Data in the table variable cannot be modified. you can use the data in the table for any other operation.
  • Also, you cannot use table variables as OUTPUT parameters. you can only use table variables as input parameters.

That’s it. I will explain example in my next article.

Reading XML file in c#

Hello friends,

Today i will show you an example of reading xml file. To generate xml file read my previous blog.

I know there are many ways to read xml. But today we will use xDocument to read xml file.

Following is xml file we have generated.



  
    0
    Sunil
    Gavaskar
    abc@xyz.com
    Sunil Gavaskar
    100
    2013-07-18
  

Now, to read this xml we will use following function.

        public string xmlRead()
        {
            try
            {
                StringBuilder xmlData = new StringBuilder();

                XmlDocument oReqDoc = new XmlDocument();
                oReqDoc.Load("c:/sendRequest.xml");

                XDocument rcvDoc = XDocument.Parse(oReqDoc.InnerXml);

                foreach (var itemTrn in rcvDoc.Descendants("Content").Descendants("Customer"))
                {

                    xmlData.AppendLine(" Gender = " + itemTrn.Element("Gender").Value.ToString());
                    xmlData.AppendLine(" Firstname = " + itemTrn.Element("Firstname").Value);
                    xmlData.AppendLine(" Lastname = " + Convert.ToString(itemTrn.Element("Lastname").Value));
                    xmlData.AppendLine(" Mail = " + Convert.ToString(itemTrn.Element("Mail").Value));
                    xmlData.AppendLine(" AccountName = " + Convert.ToString(itemTrn.Element("AccountName").Value));
                    xmlData.AppendLine(" Amount = " + Convert.ToInt64(itemTrn.Element("Amount").Value));
                    xmlData.AppendLine(" Currency = " + Convert.ToString(itemTrn.Element("Amount").Attribute("Currency").Value));
                    xmlData.AppendLine(" CollectDate = " + Convert.ToDateTime(itemTrn.Element("CollectDate").Value));

                    // your code goes here

                }
                return xmlData.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

In above code i have load xml document and parse it in xDocument. Then by Descendants method you can read xml data you want. Descendants method is use to read specific data element from xml documents.

Some have error like data at the root level is invalid. line 1 position 1 it will occurs when you are using LoadXML method. If you will getting this error use Load method and error will gone.

Hope, it will help you.

Genearate xml file in c#

To generate xml file we will use system.xml. There are many ways to generate xml file with xmlDocument with xdocument.

I have prepare one sample to generate xml file. Suppose we want to generate customer data xml as follow.



  
    0
    Sunil
    Gavaskar
    abc@xyz.com
    Sunil Gavaskar
    100
    2013-59-18
  

Use System.Xml and System.Xml.Linq namespace.
To generate above xml we will use following code.

        public void xmlGenerate()
        {
            XmlDocument doc = new XmlDocument();
            XmlNode declaration = doc.CreateNode(XmlNodeType.XmlDeclaration, "encoding", "utf-8");
            doc.AppendChild(declaration);

            // Content element
            XmlElement content = doc.CreateElement("Content");
            doc.AppendChild(content);

            // Customer Element
            XmlElement customer = doc.CreateElement("Customer");
            content.AppendChild(customer);

            // Customer sub Element
            XmlElement gender = doc.CreateElement("Gender");
            //XmlText genderText = doc.CreateTextNode("0"); // 0= Unknown,1 = Male,2 = Female
            gender.InnerText = "0";
            customer.AppendChild(gender);
            //gender.AppendChild(genderText);

            // Customer sub Element
            XmlElement firstname = doc.CreateElement("Firstname");
            XmlText firstnameText = doc.CreateTextNode("Sunil");
            customer.AppendChild(firstname);
            firstname.AppendChild(firstnameText);

            // Customer sub Element
            XmlElement lastname = doc.CreateElement("Lastname");
            XmlText lastnameText = doc.CreateTextNode("Gavaskar");
            customer.AppendChild(lastname);
            lastname.AppendChild(lastnameText);

            // Customer sub Element
            XmlElement mail = doc.CreateElement("Mail");
            XmlText mailText = doc.CreateTextNode("abc@xyz.com");
            customer.AppendChild(mail);
            mail.AppendChild(mailText);

            // Customer sub element
            XmlElement accountName = doc.CreateElement("AccountName");
            XmlText accountNameText = doc.CreateTextNode("Sunil Gavaskar");
            customer.AppendChild(accountName);
            accountName.AppendChild(accountNameText);

            // Customer sub element
            XmlElement amount = doc.CreateElement("Amount");
            XmlText amountText = doc.CreateTextNode("100");
            customer.AppendChild(amount);
            amount.AppendChild(amountText);

            //Attribute amount
            XmlAttribute currency = doc.CreateAttribute("Currency");
            currency.Value = "EUR";
            amount.Attributes.Append(currency);

            // Customer sub element
            XmlElement collectDate = doc.CreateElement("CollectDate");
            XmlText collectDateText = doc.CreateTextNode(DateTime.Now.ToString("yyyy-mm-dd"));
            customer.AppendChild(collectDate);
            collectDate.AppendChild(collectDateText);

            doc.Save(@"c:/sendRequest.xml");

            // More customer then use below code and comment all xmlText node.
            //string xmlDoc = doc.InnerXml;
            //XDocument doc1 = XDocument.Parse(xmlDoc);

            //foreach (var itemCus in doc1.Descendants("Customer"))
            //{
            //    itemCus.Element("Gender").SetValue("0"); // 0= Unknown,1 = Male,2 = Female
            //    itemCus.Element("Firstname").SetValue("Sunil");
            //    itemCus.Element("Lastname").SetValue("Gavaskar");
            //    itemCus.Element("Mail").SetValue("abc@xyz.com");
            //    itemCus.Element("AccountName").SetValue("123456");
            //    itemCus.Element("Amount").SetValue("100");
            //    itemCus.Element("CollectDate").SetValue(DateTime.Now.ToString("yyyy-MM-dd"));
            //}

            //doc1.Save(@"c:/sendRequest.xml");
                        
        }

We can generate xml document from xmldocument. Save method save our xml file. XmlElement value or text save by two method first is use xmlTitle method or by node innerText method. We can set text by both way.

If you want to set text or value after generating xml document than use comment part. after generating xmlDocument element set it into xdocument. In xDocument set customer data by loop.

In next phase i will show you how to read xmlDocument.

Encript string using MD5 in asp.net

If we want to store some sensitive data in our database then we use encryption technique.

MD5 is a common hash algorithm in asp.net.

It was developed by Ronald Rivest in 1991. The hash size for this algorithm is 128 bits.

See below example which converts a string to an MD5 hash.

public static string EncryptString(string input)
{
    // Create a new instance of the MD5CryptoServiceProvider object.
    MD5 md5Hasher = MD5.Create();
    // Convert to a byte array and get the hash.
    byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));
    StringBuilder sBuilder = new StringBuilder();
    // Loop through each byte of the hashed data // and format each one as a hexadecimal string.
    for (int i = 0; i < data.Length; i++)
    {
        sBuilder.Append(data[i].ToString("x2"));
    }
    // Return the hexadecimal string.
    return sBuilder.ToString();
}

Please note that you need to add following libraries System.Text and System.Security.Cryptography.

Validate credit card using luhn algorithm

The Luhn algorithm is also known as the modulus 10 or mod 10 algorithm, is a simple checksum formula used to validate a variety of identification numbers such as credit card numbers, IMEI numbers, National Provider Identifier numbers in US and Canadian Social Insurance Numbers. It was created by IBM scientist Hans Peter Luhn

How Luhn algorithm validate a credit card number?
1. Starting from the right digit, which is the check digit, moving left, double the value of every second digit
2. if product of this doubling operation is two digit number e.g. 5 * 3 = 15, then sum the digits of the products e.g. 15=> 1 + 5 = 6.
3. Add together doubled digits with the un-doubled digits.
4. If the total modulo 10 is equal to 0 then the number is valid according to the Luhn formula; else it is invalid.

Example :

public bool IsCreditCardValid(string cardNumber)
{
      const string allowed = "0123456789";
      int i;

      StringBuilder cleanNumber = new StringBuilder();
      for (i = 0; i < cardNumber.Length; i++)             
      {                 
          if (allowed.IndexOf(cardNumber.Substring(i, 1)) >= 0)
                    cleanNumber.Append(cardNumber.Substring(i, 1));
      }
      if (cleanNumber.Length < 13 || cleanNumber.Length > 16)
          return false;

      for (i = cleanNumber.Length + 1; i <= 16; i++)
                cleanNumber.Insert(0, "0");

      int multiplier, digit, sum, total = 0;
      string number = cleanNumber.ToString();

      for (i = 1; i <= 16; i++)
      {   
            multiplier = 1 + (i % 2);     
            digit = int.Parse(number.Substring(i - 1, 1));       
            sum = digit * multiplier;      
            if (sum > 9)
                    sum -= 9;
                total += sum;
      }
      return (total % 10 == 0)
}

example of jqGrid

Hello all,

Recently i have implemented jqGrid in my one of project. I have prepare one simple example for jqGrid which i want to share with you all.

First download jqGrid from jquery or github. Extract it to your project. I have created Default.aspx to enter my grid. Add jqGrid reference files. After that add script tag and enter following code.

         jQuery("#colr").jqGrid({
                sortable: true,
                url: 'GetData.aspx',
                datatype: 'json',
                jsonReader: {
                    root: "rows",
                    page: "page",
                    total: "total",
                    records: "records",
                    repeatitems: true,
                    cell: "cell",
                    id: "id",
                },
                colNames: ['', 'ID', 'Title', 'Alternate', 'Starring', 'Added On', 'Barcode', 'Director', 'Writer', 'Dstributor', 'Studio', 'Runtime'],
                colModel: [
                            { name: 'select', width: 25, sortable: false, align: "center" },
                            { name: 'movieID', index: 'movieID', width: 55 },
                            { name: 'movieTitle', index: 'movieTitle', width: 90 },
                            { name: 'alternateTitle', index: 'alternateTitle', width: 100 },
                            { name: 'starring', index: 'starring', width: 80 },
                            { name: 'createdDate', index: 'createdDate', width: 80, align: "center" },
                            { name: 'barcode', index: 'barcode', width: 80 },
                            { name: 'director', index: 'director', width: 150, sortable: false },
                            { name: 'writer', index: 'writer', width: 100 },
                            { name: 'distributor', index: 'distributor', width: 100 },
                            { name: 'studio', index: 'studio', width: 100 },
                            { name: 'runtimtInMinute', index: 'runtimtInMinute', width: 100 }
                            ],
                rowNum: 20,
                rowList: [10, 20, 30, 50],
                pager: '#pcolr',
                sortname: 'movieID',
                viewrecords: true,
                sortorder: "desc",
                shrinkToFit: false,
                width: 900,
                height: 500,
                caption: "jqGrid Example"
            });

            jQuery("#colr").jqGrid('navGrid', '#pcolr', { add: false, edit: false, del: false, search: false });
            // Column Button
            jQuery("#colr").jqGrid('navButtonAdd', '#pcolr', {
                caption: "",
                buttonicon: "ui-icon-calculator",
                title: "Reorder Columns",
                onClickButton: function ()
                {
                    jQuery("#colr").jqGrid('columnChooser');
                }
            });

            // Add Button
            jQuery("#colr").navSeparatorAdd('#pcolr', {
                sepclass: "ui-separator",
                sepcontent: ''
            })
            .navButtonAdd('#pcolr', {
                caption: "",
                buttonicon: "ui-icon-plus",
                title: "Add",
                onClickButton: function ()
                {
                    alert('Add');
                },
                position: "last"
            })
            .navButtonAdd('#pcolr', {
                caption: "",
                buttonicon: "ui-icon-pencil",
                title: "Edit",
                onClickButton: function ()
                {
                    alert('Edit Row : ' + jQuery('#colr').jqGrid('getGridParam', 'selrow'));
                },
                position: "last"
            })
            .navButtonAdd('#pcolr', {
                caption: "",
                buttonicon: "ui-icon-cancel",
                title: "Delete",
                onClickButton: function ()
                {
                    alert('Delete Row : ' + jQuery('#colr').jqGrid('getGridParam', 'selrow'));
                },
                position: "last"
            });
        });

In html add following code. This is for jqGrid grid and pager.

    

Now create one GetData.aspx and write following code in pageLoad.

int page = int.Parse(Request.QueryString["page"] == null ? "1" : Request.QueryString["page"].ToString());
            int rp = int.Parse(Request.QueryString["rows"] == null ? "200" : Request.QueryString["rows"].ToString());
            string sortname = Request.QueryString["sidx"] == null ? "movieID" : Request.QueryString["sidx"].ToString();
            string sortorder = Request.QueryString["sord"] == null ? "desc" : Request.QueryString["sord"].ToString();

            string sort = String.Format("ORDER BY {0} {1}", sortname, sortorder);

            int rowPage = rp;

            if (page == 0)
                page = 1;

            if (rp == 0)
                rp = 200;

            int start = ((page - 1) * rp);

            if (start != 0)
            {
                rp = start + rp;
                start = start + 1;
            }

            string limit;// = String.Format( "LIMIT {0}, {1}", start, rp );
            //limit = "";
            limit = String.Format("WHERE row >= {0} and row <= {1}", start, rp);

            Response.ClearHeaders();
            Response.AppendHeader("Expires", "Mon, 26 Jul 2012 05:00:00 GMT");
            Response.AppendHeader("Last-Modified", DateTime.Now.ToLongDateString() + " " + DateTime.Now.ToLongTimeString());
            Response.AppendHeader("Cache-Control", "no-cache, must-revalidate");
            Response.AppendHeader("Pragma", "no-cache");
            //Response.AppendHeader("Content-type", "text/xml");
            Response.AppendHeader("Content-type", "text/json");

            List data = GetMovieDataTable(sort, limit);

            // Generating JSON Data

            StringBuilder jsonData = new StringBuilder();
            jsonData.AppendLine("{");
            jsonData.AppendLine(String.Format("\"total\": {0},", Math.Ceiling(Convert.ToDecimal(this.GetTotalRecords()) / rowPage)));
            jsonData.AppendLine("\"page\": \"" + page.ToString() + "\",");
            jsonData.AppendLine(String.Format("\"records\": {0},", this.GetTotalRecords()));
            jsonData.AppendLine("\"rows\": [");

            bool rc = false;

            foreach (Movie mObj in data)
            {
                if (rc)
                {
                    jsonData.AppendLine(",");
                }

                jsonData.Append(
                    String.Format("{{\"id\":\"{1}\",\"cell\":[\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\", \"{6}\", \"{7}\",\"{8}\", \"{9}\", \"{10}\", \"{11}\"]}}",
                        "",
                        mObj.movieID,
                        mObj.movieTitle == null ? "" : ("" + StringValidate(mObj.movieTitle) +
                        " "),
                        mObj.alternateTitle == null ? "" : StringValidate(mObj.alternateTitle),
                        mObj.starring == null ? "" : StringValidate(mObj.starring),
                        mObj.createdDate,
                        mObj.barcode,
                        mObj.director == null ? "" : StringValidate(mObj.director),
                        mObj.writer == null ? "" : StringValidate(mObj.writer),
                        mObj.distributor == null ? "" : StringValidate(mObj.distributor),
                        mObj.studio == null ? "" : StringValidate(mObj.studio),
                        mObj.runtimtInMinute
                        ));

                rc = true;
            }

            jsonData.AppendLine("]");
            jsonData.Append("}");

            Response.Write(jsonData.ToString());
            Response.End();

That’s it. You are ready with your code. I am attaching sample project in zip format to give more idea.
jqGridExample

show error in jqGrid

Hello all,

Show error in jqGrid use loadError attribute. When the grid is loading and any error occurs it will seen in loadError attribute.

To set loadError use following code.

....
        loadError: function (jqXHR, textStatus, errorThrown)
        {
            alert('HTTP status code: ' + jqXHR.status + '\n' +
              'textStatus: ' + textStatus + '\n' +
              'errorThrown: ' + errorThrown);
            alert('HTTP message body (jqXHR.responseText): ' + '\n' + jqXHR.responseText);
        }
....

It will help to show response error.

T-SQL Enhancements in SQL Server 2008 phase-3

Hello,

we have seen some features of sql server 2008 in my previous article like merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe grouping sets.

Describe Grouping Sets :

  • Grouping set: a set of group by columns
  • Helps supporting dynamic analysis of aggregates
  • SQL Server versions earlier than 2008 had limited support for grouping sets
  • SQL Server 2008 introduces several important enhancements supporting grouping sets

Grouping Sets Enhancements in 2008 :

  • New ISO-compliant subclauses in GROUP BY clause:
    • GROUPING SETS
    • CUBE
    • ROLLUP
  • New GROUPING_ID function identifies grouping set

GROUPING SETS Subclause :

  • Define multiple grouping sets in a single query
  • NULLs used as placeholders

CUBE and ROLLUP Subclauses :

  • CUBE: abbreviation for a power set of grouping sets constructed from input elements (2^n grouping sets for n elements)
  • ROLLUP: abbreviation for the subset of grouping sets that is relevant in a hierarchy scenario (n+1 grouping sets for n elements)

GROUPING_ID :

  • Accepts a list of elements as input
  • Produces an integer bitmap that identifies the grouping set
  • Each bit represents a different element
  • Bit off (0) – element participated in current grouping set
  • Bit on (1) – element did not participate in current grouping set

Example :

CREATE TABLE [dbo].[employee]
(
	[Employee_Number] [int] NOT NULL PRIMARY KEY,
	[Employee_Name] [varchar](30) NULL,
	[Salary] [float] NULL,
	[Department_Number] [int] NULL,
[Region] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO employee
VALUES
(1,'Sachin',5000,1,'Ahmedabad'),
(2,'Rahul',9000,1,'Baroda'),
(3,'Saurav',4000,2,'Ahmedabad'),
(4,'Yuvraj',8000,1,'Delhi'),
(5,'Zahir',8000,2,'Delhi')

SELECT Region, Department_number, AVG(salary) Average_Salary
FROM Employee
GROUP BY
GROUPING SETS
(
	(Region, Department_number),
	(Region),
	(Department_number) ,
	()                            
)

--UNION results of above query
SELECT Region, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Region, Department_number
UNION
SELECT Region, NULL, avg(salary) Average_Salary
from Employee
Group BY
Region
UNION
SELECT NULL, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Department_number
UNION
SELECT NULL, NULL, avg(salary) Average_Salary
from Employee

--CUBE  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
CUBE (Region, Department_Number)  

--ROLLUP  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Region, Department_Number) 
--(Region, Department_Number),(Region)()

SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Department_Number, Region) 
--(Department_Number, Region),(Department_Number)()

SELECT GROUPING_ID
(
	Region,						--2
	Department_Number			--1
)AS grp_ID, Region, Department_Number FROM Employee
Group BY
CUBE (Region, Department_Number)

SELECT GROUPING_ID
(
	Region,						--4
	Department_Number,			--2
	salary						--1
)AS grp_ID, Region, salary Department_Number FROM Employee
Group BY
GROUPING SETS(salary, 
CUBE (Region, Department_Number))

Hope you enjoyed this; Now I will explain phase-4 in coming days.