Passing lists to SQL server stored procedures

  • 0

Passing lists to SQL server stored procedures

Visit : http://www.zonixsoft.com (our official wbsite)

Article is about:

The ability to pass “a list of values” from .Net as a parameter to a T-SQL based stored procedure.

Scenarios:

There are lots of scenarios where we need to pass a list of values to save in database. Here’s a couple of obvious ones:

· INSERT a list of values into the database in one “chunky” call (e.g. some IDs from a CheckBoxList)

· SELECT rows where IDs are IN (<list of IDs>)

Some general Approaches:

Taking the INSERT statements as an example, there are various general approaches that we adopt to achieve the desired result:

· Use dynamic / Inline SQL!  But ideally say, dynamic / Inline SQL is rarely the ideal solution for obvious reasons.

· Make a stored proc call for each ID to insert. This is the most common approach we see in various projects, mainly because it is the easiest to implement. The drawback of course is if we were to insert 60 values, it would result in 60 “chatty” calls to the database.

· Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy “string splitting” in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.

· Pass the list as an XML parameter. This is nicer and is the point of this article.

Coming to the main Point, Using XML:

Using XML for “list passing” has a number of benefits, in particular the ability to pass lists of more “complex types” rather than just single values.

Lets take an example. Suppose we are having 2 CheckedListBox; one is list of Users and another is the list of tasks / roles that can be assigned to Users. We want to store the values in Table which is having Fields UserID and TaskID. The Stored Procedure will accept Parameter with XML Datatype as,

CREATE PROCEDURE [dbo].[usp_InsertUserTask]
@UserTaskXML XML
AS
BEGIN
INSERT INTO UserTasks (UserID,TaskID)
SELECT
UserTaskTab.UserTaskCol.value(‘UserID[1]’,’int’) AS UserID,
UserTaskTab.UserTaskCol.value(‘TaskID[1]’,’int’) AS TaskID
FROM @UserTaskXML.nodes(‘//UserTaskList/UserTaskData’) AS UserTaskTab(UserTaskCol)
END

To call this in Stored Procedure, you would have something like this:

EXEC    [dbo].[usp_InsertUserTask]
@UserTaskXML = ‘<UserTaskList>
<UserTaskData>
<UserID>1</UserID>
<TaskID>100</TaskID>
</UserTaskData>
<UserTaskData>
<UserID>2</UserID>
<TaskID>200</TaskID>
</UserTaskData>
</UserTaskList>’

In your application, your C# calling code could be:

SqlConnection sqlCN = new SqlConnection();
sqlCN.ConnectionString = ConfigurationManager.AppSettings[“DBConn”].ToString();
string strQuery = “usp_InsertUserTask”;
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter(“@UserTaskXML”, GetStudyDataXMLString());
SqlHelper.ExecuteNonQuery(sqlCN, CommandType.StoredProcedure, strQuery, sqlParams);
if (sqlCN.State == ConnectionState.Open)
sqlCN.Close();
sqlCN.Dispose();

which calls the method below to translate the UserID and TaskID from CheckBoxLists into an XML String:

private string GetUserTaskListXML()
{
try
{
StringBuilder XMLString = new StringBuilder();
XMLString.AppendFormat(“<UserTaskList>”);
for (int iUserCount = 0; iUserCount < UserCheckBoxList.Items.Count; iUserCount++)
{
if(UserCheckBoxList.Items[iUserCount].Selected)
{
for (int iTaskCount = 0; iTaskCount < TaskCheckBoxList.Items.Count; iTaskCount++)
{
if(TaskCheckBoxList.Items[iTaskCount].Selected)
{
XMLString.AppendFormat(“<UserTaskData>”);
XMLString.AppendFormat(“<UserID>{0}</UserID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“<TaskID>{0}</TaskID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“</UserTaskData>”);
}
}
}
}
XMLString.AppendFormat(“</UserTaskList>”);
}
catch (Exception Ex)
{
throw Ex;
}
return XMLString.ToString();
}

Here, StringBuilder is used for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach. In summary, it performs very well and is adaptable for various lists of objects and more complex structures.


Bookmark and Share


  • 6

GZipStream Class to Compress and Decompress Stream Data

Provides methods and properties used to compress and decompress streams.

Namespace: System.IO.Compression
Assembly: System (in system.dll)

 

This class represents the gzip data format, which uses an industry standard algorithm for lossless file compression and decompression. The format includes a cyclic redundancy check value for detecting data corruption. The gzip data format uses the same algorithm as the DeflateStream class, but can be extended to use other compression formats. The format can be readily implemented in a manner not covered by patents. The format for gzip is available from the RFC 1952, “GZIP .” This class cannot be used to compress files larger than 4 GB.

 

Notes to Inheritors When you inherit from GZipStream, you must override the following members: CanSeek, CanWrite, and CanRead.

 

 Example

The following code example shows how to use the GZipStream class to compress and decompress a file.

using System;
using System.IO;
using System.IO.Compression;

public class GZipTest
{
    public static int ReadAllBytesFromStream(Stream stream, byte[] buffer)
    {
    // Use this method is used to read all bytes from a stream.
    int offset = 0;
    int totalCount = 0;
        while (true)
        {
        int bytesRead = stream.Read(buffer, offset, 100);
            if ( bytesRead == 0)
            {
            break;
            }
    offset += bytesRead;
    totalCount += bytesRead;
        }
    return totalCount;
    }

    public static bool CompareData(byte[] buf1, int len1, byte[] buf2, int len2)
    {
        // Use this method to compare data from two different buffers.
        if (len1 != len2)
        {
        Console.WriteLine(“Number of bytes in two buffer are different {0}:{1}”, len1, len2);
        return false;
        }

        for ( int i= 0; i< len1; i++)
        {
            if ( buf1[i] != buf2[i])
            {
            Console.WriteLine(“byte {0} is different {1}|{2}”, i, buf1[i], buf2[i]);
            return false;
            }
        }
    Console.WriteLine(“All bytes compare.”);
    return true;
    }

    public static void GZipCompressDecompress(string filename)
    {
    Console.WriteLine(“Test compression and decompression on file {0}”, filename);
    FileStream infile;
        try
        {
        // Open the file as a FileStream object.
        infile = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);
        byte[] buffer = new byte[infile.Length];
        // Read the file to ensure it is readable.
        int count = infile.Read(buffer, 0, buffer.Length);
            if ( count != buffer.Length)
            {
            infile.Close();
            Console.WriteLine(“Test Failed: Unable to read data from file”);
            return;
            }
        infile.Close();
        MemoryStream ms = new MemoryStream();
        // Use the newly created memory stream for the compressed data.
        GZipStream compressedzipStream = new GZipStream(ms , CompressionMode.Compress, true);
        Console.WriteLine(“Compression”);
        compressedzipStream.Write(buffer, 0, buffer.Length);
        // Close the stream.
        compressedzipStream.Close();
        Console.WriteLine(“Original size: {0}, Compressed size: {1}”, buffer.Length, ms.Length);

        // Reset the memory stream position to begin decompression.
        ms.Position = 0;
        GZipStream zipStream = new GZipStream(ms, CompressionMode.Decompress);
        Console.WriteLine(“Decompression”);
        byte[] decompressedBuffer = new byte[buffer.Length + 100];
        // Use the ReadAllBytesFromStream to read the stream.
        int totalCount = GZipTest.ReadAllBytesFromStream(zipStream, decompressedBuffer);
        Console.WriteLine(“Decompressed {0} bytes”, totalCount);

        if( !GZipTest.CompareData(buffer, buffer.Length, decompressedBuffer, totalCount) )
        {
        Console.WriteLine(“Error. The two buffers did not compare.”);
        }
    zipStream.Close();
        } // end try
        catch (InvalidDataException)
        {
            Console.WriteLine(“Error: The file being read contains invalid data.”);
        }
        catch (FileNotFoundException)
        {
            Console.WriteLine(“Error:The file specified was not found.”);
        }
        catch (ArgumentException)
        {
            Console.WriteLine(“Error: path is a zero-length string, contains only white space, or contains one or more invalid characters”);
        }
        catch (PathTooLongException)
        {
            Console.WriteLine(“Error: The specified path, file name, or both exceed the system-defined maximum length. For example, on Windows-based platforms, paths must be less than 248 characters, and file names must be less than 260 characters.”);
        }
        catch (DirectoryNotFoundException)
        {
            Console.WriteLine(“Error: The specified path is invalid, such as being on an unmapped drive.”);
        }
        catch (IOException)
        {
            Console.WriteLine(“Error: An I/O error occurred while opening the file.”);
        }
        catch (UnauthorizedAccessException)
        {
            Console.WriteLine(“Error: path specified a file that is read-only, the path is a directory, or caller does not have the required permissions.”);
        }
        catch (IndexOutOfRangeException)
        {
            Console.WriteLine(“Error: You must provide parameters for MyGZIP.”);
        }
    }
    public static void Main(string[] args)
    {
        string usageText = “Usage: MYGZIP <inputfilename>”;
        //If no file name is specified, write usage text.
        if (args.Length == 0)
        {
            Console.WriteLine(usageText);
        }
        else
        {
            if (File.Exists(args[0]))
                GZipCompressDecompress(args[0]);
        }
    }
}
   

Demo Application:

 

Default.aspx :

 

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”Default.aspx.vb” Inherits=”_Default” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<html xmlns=”http://www.w3.org/1999/xhtml” >

<head runat=”server”>

<title>Compression Demo</title>

</head>

<body style=”margin-top:10; margin-left:0″>

<form id=”form1″ runat=”server”>

<div>

<asp:Panel ID=”Panel1″ runat=”server” Height=”500px” Width=”100%” HorizontalAlign=”Center”>

<asp:Label ID=”Label1″ runat=”server” Font-Bold=”True” Font-Size=”Large” Font-Underline=”True”

Text=”File Upload & Compression Tutorial”></asp:Label><br />

<br />

<table style=”width: 100%”>

<tr>

<td align=”right”>

<asp:Label ID=”Label2″ runat=”server” Text=”File to upload and compress: “ Width=”100%”></asp:Label></td>

<td style=”width: 100px” align=”left”>

<input id=”fileUpload” type=”file” runat=”server” />

</td>

</tr>

<tr>

<td>

</td>

<td style=”width: 50%” align=”left”>

<asp:Button ID=”btnUpload” runat=”server” Text=”Upload” />

<asp:Button ID=”btnCompress” runat=”server” Text=”Upload & Compress” /></td>

</tr>

<tr>

<td>

</td>

<td align=”left”>

<asp:Label ID=”lblResult” runat=”server” Font-Bold=”True” ForeColor=”RoyalBlue” Width=”100%”></asp:Label></td>

</tr>

</table>

</asp:Panel>

</div>

</form>

</body>

</html>

Default.aspx.cs :

Imports System.IO

Imports System.IO.Compression

 

Partial Class _Default

Inherits System.Web.UI.Page

 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click

If fileUpload.PostedFile Is Nothing Then

Me.lblResult.Text = “No File Selected to Upload.”

Exit Sub

End If

‘Retrieve file information and upload to server.

Dim strName As String

strName = System.IO.Path.GetFileName(fileUpload.PostedFile.FileName)

Try

fileUpload.PostedFile.SaveAs(Server.MapPath(strName))

Me.lblResult.Text = “””” + strName + “”” was uploaded successfully.”

Catch ex As Exception

Me.lblResult.Text = “An Error Occured While Uploading File.”

End Try

End Sub

Protected Sub btnUploadCompressed_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCompress.Click

If fileUpload.PostedFile Is Nothing Then

Me.lblResult.Text = “No File Selected to Upload.”

Exit Sub

End If

Try

‘A String object reads the file name (locally)

Dim strName As String = System.IO.Path.GetFileName(fileUpload.PostedFile.FileName)

‘Create a stream object to read the file.

Dim myStream As Stream = fileUpload.PostedFile.InputStream

‘Allocate space in buffer for use according to length of file.

Dim myBuffer(myStream.Length) As Byte

‘Read the file using the Stream object and fill the buffer.

myStream.Read(myBuffer, 0, myBuffer.Length)

myStream.Close()

‘Change the extension of the file creating a FileStream object.

Dim myCompressedFile As FileStream = File.Create(Server.MapPath(Path.ChangeExtension(strName, “rar”)))

‘GZip object that compress the file

Dim myStreamZip As New System.IO.Compression.GZipStream(myCompressedFile, CompressionMode.Compress)

‘Write Back

myStreamZip.Write(myBuffer, 0, myBuffer.Length)

myStreamZip.Close()

Me.lblResult.Text = “””” + strName + “”” was uploaded successfully and compressed.”

Catch ex As Exception

Me.lblResult.Text = “An Error Occured While Uploading File.”

End Try

End Sub

End Class


Bookmark and Share


  • 10

OOPS Concepts and .NET Part 2 Inheritance, Abstraction, Polymorphism

Summary

The following article is the second of a three-part article series that presents definitions and samples for different Object-Oriented Programming (OOP) concepts and its implementation in .NET. The first part examined the concepts of classes, objects, and structures. This part examines the concepts of inheritance, abstraction, and polymorphism. The third and last part will examine the concepts of interface, multiple interface inheritance, collections, and overloading.

Introduction

In Part 1 of Object-Oriented Programming Concepts and .NET, I defined the concepts of class, object, and structure. In addition to defining the concepts, I explained real world samples and presented sample code in C# and VB.NET to create classes and structs. The first article also explains objects as independent building blocks.

In Part 2 of Object-Oriented Programming Concepts and .NET, I will explain the concepts of inheritance, abstraction, and polymorphism. I will also present a Unified Model Language (UML) class diagram to represent an object model that will help as a visual aid to explain some concepts. The purpose of this article is to explain a series of relationships between objects.

Inheritance

In the real world there are many objects that can be specialized. In OOP, a parent class can inherit its behavior and state to children classes. This concept was developed to manage generalization and specialization in OOP and is represented by a is-a relationship.

The following OO terms are commonly used names given to parent and child classes in OOP:

·       Superclass: Parent class.

·       Subclass: Child class.

·       Base class: Parent class.

·       Derived class: Child class

The most common real world sample to explain inheritance is the geometric shapes object model. Squares, circles, triangles, rectangles, pentagons, hexagons, and octagons are geometric shapes. The following figure shows a sample set of geometric figures:

Figure 1. Geometric shapes.

The concept of generalization in OOP means that an object encapsulates common state an behavior for a category of objects. The general object in this sample is the geometric shape. Most geometric shapes have area, perimeter, and color. The concept of specialization in OOP means that an object can inherit the common state and behavior of a generic object; however, each object needs to define its own special and particular state an behavior. In Figure 1, each shape has its own color. Each shape has also particular formulas to calculate its area and perimeter.

Inheritance makes code elegant and less repetitive. If we know that all shapes have color, should we program a color attribute for each shape? The answer is no! Would it be a better idea to create a shape class that has a color attribute and to make all the specialized shapes to inherit the color attribute? The answer is yes!

An object model for this sample could have a shape parent class and a derived class for each specific shape. The following UML class diagram shows the set of classes needed to model the geometric shapes sample. Observe the field, properties, and methods for each class:

 

Figure 2. The Shape class is the parent class. Square, Rectangle, and Circle are derived classes that inherit from Shape. The triangle-connector in the diagram represents an is-a relationship.

The .NET framework has many base classes. Everything is derived from System.Object. You can create almost anything you imagine using the built-in functionality provided in the .NET Framework Class Library.

To create a derived class in C#, the class declaration should be done as:

class child: parent 

To create a derived class in VB.NET, the class declaration should be done as:

Class child
Inherits
parent
End
Class

Multiple inheritance

Multiple inheritance is the possibility that a child class can have multiple parents. Human beings have always two parents, so a child will have characteristics from both parents.

In OOP, multiple inheritance might become difficult to handle because it allows ambiguity for the compiler. There are programming languages such as C++ that allow multiple inheritance; however, other programming languages such as Java and the .NET Framework languages do not allow multiple inheritance. Multiple inheritance can be emulated in .NET using Multiple Interface Inheritance, which I will explain in Part 3 of this series.

Sealed class

A sealed class is a class that does not allow inheritance. Some object model designs need to allow the creation of new instances but not inheritance, if this is the case, the class should be declared as sealed.

To create a sealed class in C#, the class declaration should be done as:

sealed class Shape

To create a sealed class in VB.NET, the class declaration should be done as:

NonInheritable Class Shape

Abstraction

Abstraction is “the process of identifying common patterns that have systematic variations; an abstraction represents the common pattern and provides a means for specifying which variation to use” (Richard Gabriel).

An abstract class is a parent class that allows inheritance but can never be instantiated. Abstract classes contain one or more abstract methods that do not have implementation. Abstract classes allow specialization of inherited classes.

Figure 2 shows a Shape class, which is an abstract class. In the real world, you never calculate the area or perimeter of a generic shape, you must know what kind of geometric shape you have because each shape (eg. square, circle, rectangle, etc.) has its own area and perimeter formulas. The parent class shape forces all derived classes to define the behavior for CalculateArea() and CalculatePerimeter(). Another great example is a bank account. People own savings accounts, checking accounts, credit accounts, investment accounts, but not generic bank accounts. In this case, a bank account can be an abstract class and all the other specialized bank accounts inherit from bank account.

To create an abstract class in C#, the class declaration should be done as:

abstract class Shape

To create an abstract class in VB.NET, the class declaration should be done as:

MustInherit Class Shape

To following code shows a sample implementation of an abstract class:

/// C#
using System;
namespace
DotNetTreats.OOSE.OOPSamples
{
public
abstract class Shape
{
private
float _area;
private
System.Drawing.Color _color;
private
float _perimeter;
public
float Area
{
get
{
return
_area;
}
set
{
_area = value;
}
}
public
System.Drawing.Color Color
{
get
{
return
_color;
}
set
{
_color = value;
}
}
public
float Perimeter
{
get
{
return
_perimeter;
}
set
{
_perimeter = value;
}
}
public
abstract void CalculateArea();
public
abstract void

CalculatePerimeter();
}
}
Listing 1. The Shape abstract class in C#.
 

 

Polymorphism

Polymorphism allows objects to be represented in multiple forms. Even though classes are derived or inherited from the same parent class, each derived class will have its own behavior. Polymorphism is a concept linked to inheritance and assures that derived classes have the same functions even though each derived class performs different operations.

Figure 2 shows a Rectangle, a Circle, and Square. All of them are shapes and as shapes their area and perimeter can be calculated; however, each shape calculates its area in a specialized way. Declaring a member as abstract allows polymorphism. The Shape class defines the CalculateArea() and CalculatePerimeter() methods as abstract, this allows each derived class to override the implementation of the parent’s methods.

To following sample code shows an implementation of a derived class (rectangle). The specific CalculateArea() and CalculatePerimeter() methods for the rectangle class illustrate polymorphism:

/// C#
using System;
namespace
DotNetTreats.OOSE.OOPSamples
{
class
Rectangle : Shape
{
private
float _height;
private
float _width;
public
rectangle(float height, float width)
{
_height = height;
_width = width;
}
public
float Height
{
get
{
return
_height;
}
set
{
_height = value;
}
}
public
float Width
{
get
{
return
_width;
}
set
{
_width = value;
}
}
public
override void CalculateArea()
{
this.Area = _height * _width;
}
public
override void

CalculatePerimeter()
{
this.Perimeter = (_height * 2) + (_width * 2);
}
}
}
Listing 2. Polymorphism represented in the Rectangle’s methods.
 

 

Virtual keyword

The virtual keyword allows polymorphism too. A virtual property or method has an implementation in the base class, and can be overriden in the derived classes.

To create a virtual member in C#, use the virtual keyword:

public virtual void Draw()

To create a virtual member in VB.NET, use the Overridable keyword:
Public Overridable Function Draw()
 

 

Override keyword

Overriding is the action of modifying or replacing the implementation of the parent class with a new one. Parent classes with virtual or abstract members allow derived classes to override them.

To override a member in C#, use the override keyword:

public override void CalculateArea()

To override a member in VB.NET, use the Overrides keyword:

Public Overrides Function CalculateArea()

Conclusion

Inheritance allows developers to manage a generalization and specialization relationship between objects. OOP concepts such as abstraction and polymorphism help to define better object models where object hierarchies are designed with reusability in mind. In this article, I examined the concept of inheritance, abstraction, and polymorphism. The third and last part of this series will examine the concepts of interface, multiple interface inheritance, collections, and overloading.

Courtesy: The Seo Guru, A Software Development Company, Best OOPS Blog Site, Link Submission, Thanks to Shopping  Site for Link Exchanging


Bookmark and Share


  • 0

The Google File System – Google’s core storage platform

Google File System – large distributed log structured file system in which they throw in a lot of data. Reliable scalable storage is a core need of any application. GFS is Google’s core storage platform.Google File System (GFS) is a proprietary distributed file system developed by Google for its own use. Its point is both to assure reliablity by using redundant copies and to allow individual most used data to selectively receive more resources (more dedicated hardware or/and redundant copies).GFS is optimized for Google’s core data storage needs, web searching, which can generate enormous amounts of data that needs to be retained; Google File System grew out of an earlier Google effort, “BigFiles”, developed by Larry Page and Sergey Brin in the early days of Google, while it was still located in Stanford. The data is stored persistently, in very large, multiple gigabyte-sized files (around 100GB) which are only extremely rarely deleted, overwritten, or shrunk; files are usually appended to or read. It is also designed and optimized to run on Google’s computing clusters, the nodes of which consist of cheap, “commodity” computers, which means precautions must be taken against the high failure rate of individual nodes and the subsequent data loss. Other design decisions select for high data throughputs, even when it comes at the cost of latency.

read more…


Bookmark and Share