Programming Solution

.NET in Sql Server

Creating Role in SQL Server Database using C#

Forms Authentication Workaround for Accessing SSAS Cube Data

.NET Windows Application

ListView, ListViewItem and ListViewSubItem in .Net Windows Form

SelectionChangeCommitted and SelectedIndexChanged events: System.NullReferenceException while closing Windows Form

C#

JSON to Object Conversion in C#

JSON to c-sharp Object Conversion and c-sharp Object to JSON Conversion (Part II)

Parallel Programming not supported with Entity Framework

Cannot Implicitly Convert Type IQueryable

Lambda Restriction Functions

Design Pattern

When to use Abstract Factory and Factory Method Pattern

Excel

Regular Expression in Excel Find and Replace Window

Enter a Newline in a Value of a Cell in Excel

HTML

HTML Div tag inside HTML P (paragraph) tag does not work correctly

IE6-IE7 Radio Button JQuery Problem: Selection does not work

Javascript

Javascript variable declaration with Var and without Var: Global variable Vs Local Variable

URL Parsing Using JavaScript: Get Domain Name, Port Number and Virtual Directory Path

JavaScript Object Array Sorting: Using Custom Comparison Function

Implement JavaScript HashTable

IE6-IE7 Radio Button JQuery Problem: Selection does not work

Casting JQuery Object to JavaScript Object and JavaScript Object to JQuery Object

JQuery Input Tag Dom Creation Problem

JQuery Accordion Item Height Adjust Based on Content

JSON to c-sharp Object Conversion and c-sharp Object to JSON Conversion (Part II)

Inheritance in JavaScript

JavaScript Object Expected Error-Casting a Variable to JQuery Object and Assigning in Same Variable in IE6

JQuery Sortable Event Firing Sequence

Variable Length Argument in JavaScript Function Call

JavaScript MVC – Step by Step Approach

Dynamically Accessing JavaScript Object Property and Object Member Method Using Variable

JavaScript Array Extension

JavaScript Add Array to Array

jquery

JQuery Accordion Item Height Adjust Based on Content

JQuery Sortable Event Firing Sequence

JavaScript MVC – Step by Step Approach

MDX Query

MDX query for Subtree Members and Leaf Members

Finding Last n members of a Particular month of a particular Year from a Time Dimension using MDX

Hierarchical Ordering of Members of a Hierarchy

VBA Functions in MDX

miscellaneous

Iframe src error: Content was blocked because it was not signed by a valid security certificate

mssql xquery

Getting XML node names and values in tabular format

SQL Server Xquery FLWOR Examples

TSQL XQUERY LET

XQuery Function Requires Singleton or Empty Sequence

XQuery Concat Function

SharePoint

Get Detail Error Message for “An unexpected error has occurred”

Where to get VseWss 1.3 Service

Ajax call in SharePoint does not reach Web Service Method

Sql Server 2008

SQL Partition By clause

Comma Separated string of all the rows of a column

EXECUTE sp_executesql

Use of SQL Case Statement in SQL Update Statement

Using sql case statement in order by clause

XML Data into a Relational Table Using OPENXML

Getting XML node names and values in tabular format

Date formatting in SQL Server

SQL Server Xquery FLWOR Examples

Inserting Rows into SQLServer from CSV file

Nested case when statement

Comma Separated List (CSV) of rows of a column using FOR XML PATH

Integer sequence generator, Date sequence generator

SQL Server Pivot: Converting Rows to Columns with Dynamic Query

Removing Leading and Trailing White Space Characters in TSQL

SQL Server Array: Implementing TSQL Array Using XML and XQuery

Finding All the Unicode Character Values Used in a String using TSQL

Finding Invisible Characters in TSQL String and Remove Invisible Characters with Space

TSQL String Functions

Comparison between TSQL Inner Join and Left/Right Join

User Does Not Have Permission to Create a New Object in SQL Server

Finding and Deleting the Duplicate Rows Using SQL Partition By

ISALPHA and ISINT in SQL Server

Update Statement Performance Improvement for Large Table

SQL Order By

Data Generation Plan to Generate Dummy Test Data for SQL Server Tables

Integer sequence generator, Date sequence generator

SQL Server Management Studio (SSMS)

SQL Server Table Design: Saving changes is not permitted

User Does Not Have Permission to Create a New Object in SQL Server

SSAS 2008

Finding Last n members of a Particular month of a particular Year from a Time Dimension using MDX

MDX query for Subtree Members and Leaf Members

Period Dimension (Time Dimension) Creation with Year, Month, Day Hierarchy

Creating Role in SQL Server Database using C#

Hierarchical Ordering of Members of a Hierarchy

Creating Local Cube or Offline Cube from SSAS 2008

Retrieve SSAS Cube Data in Tabular Format from Stored Procedure using Linked Server

How to setup the server where the cube will be deployed

Forms Authentication Workaround for Accessing SSAS Cube Data

SSAS 2008 with Excel 2007 Pivot Table

Creating Connection to an Analysis Service Database or Cube from Excel 2007

Introducing the Design Tab of Pivot Table Tools in Excel 2007

SSRS

Changing Default MDX Query Generated by SSRS Query Designer to Avoid set with more than 4,294,967,296 tuples error

Changing the default plus sign to minus while the report is still expanded: InitialToggleState

InvalidOperationException: Client found response content type of ‘’, but expected ‘text/xml’. The request failed with an empty response.

TSQL

Padding TSQL String Using a Character for a Particular Length

Delete Rows of a Table Matching Multiple Columns of Another Table

ISALPHA and ISINT in SQL Server

Update Statement Performance Improvement for Large Table

SQL Order By

Removing the last character from a TSQL string

Use of Recursive CTE (Common Table Expression) in a Hierarchical Table to Find Descendant Elements

Easiest way to find and delete duplicate rows from a table using CTE in SQL Server

Integer Sequence Generator Using CTE (Common Table Expression)

IsPalindrome in T-SQL

All Character Occurrence in TSQL

nth Occurrence of a character in a string

TSQL CLR

TSQL CLR: Custom Aggregate Function (Concat)

VBA

Checking a Date whether it is weekend or monthend in VBA

Create Folder if the Folder does not Exist in VBA

Loop through all files in a folder using VBA

Column Letter from Index and Column Index from Column Letter in VBA

VBA Power Function

VBA For Loop/For Each Loop: Different Way of using VBA For Loop

July 13, 2012

Lambda Restriction Functions

Filed under: C# — maeenul @ 11:37 pm

Lambda Restriction Functions

Where – Simple 1

This sample uses where to find all elements of an array less than 5.

public void Lambda1()

{

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

var lowNums = numbers.Where(n => n < 5);

Console.WriteLine(“Numbers < 5:”);

foreach (var x in lowNums)

{

Console.WriteLine(x);

}

}

Where – Simple 2

This sample uses where to find all products that are out of stock.

public void Lambda2()

{

List<Product> products = Data.productList;

var soldOutProducts = products.Where(p => p.UnitsInStock == 0);

Console.WriteLine(“Sold out products:”);

foreach (var product in soldOutProducts)

{

Console.WriteLine(“{0} is sold out!”, product.ProductName);

}

}

Where – Simple 3

This sample uses where to find all products that are in stock and cost more than 3.00 per unit.

Method1:

public void Lambda3()

{

List<Product> products = Data.productList;

var expensiveInStockProducts = products.Where(p => p.UnitsInStock > 0).Where(p => p.UnitPrice > 3.00M);

Console.WriteLine(“In-stock products that cost more than 3.00:”);

foreach (var product in expensiveInStockProducts)

{

Console.WriteLine(“{0} is in stock and costs more than 3.00.”, product.ProductName);

}

}

Method2:

public void Lambda3()

{

List<Product> products = Data.productList;

var expensiveInStockProducts = products.Where(p => p.UnitsInStock > 0 && p.UnitPrice > 3.00M);

Console.WriteLine(“In-stock products that cost more than 3.00:”);

foreach (var product in expensiveInStockProducts)

{

Console.WriteLine(“{0} is in stock and costs more than 3.00.”, product.ProductName);

}

}

Where – Drilldown

This sample uses where to find all customers in Washington and then uses the resulting sequence to drill down into their orders.

public void Lambda4()

{

List<Customer> customers = Data.customerList;

var waCustomers = customers.Where(c => c.Region == “WA”);

Console.WriteLine(“Customers from Washington and their orders:”);

foreach (var customer in waCustomers)

{

Console.WriteLine(“Customer {0}: {1}”, customer.CustomerID, customer.CompanyName);

foreach (var order in customer.Orders)

{

Console.WriteLine(“  Order {0}: {1}”, order.OrderID, order.OrderDate);

}

}

}



Some Other Posts

    Tags:

    July 12, 2012

    When to use Abstract Factory and Factory Method Pattern

    Filed under: Design Pattern — admin @ 4:06 am

    Factory pattern and abstract factory both can be used in the scenarios where you need to have dynamic object creation. Every scenario where you can use factory pattern, you can use abstract factory instead and vice versa. The difference between these 2 patterns are in approach, not in scenarios. Some like to have all the object creation methods separated in another class, they use abstract factory. Some things, I dont want to create another class, so they just create the objection creation methods in the same class. But to me, if you need to have many type of objects to be created dynamically, you just separate it into different class i.e. use abstract factory. If you need to have only 1 or 2 type of objects to be created dynamically, then probably creating another class hierarchy will be a overkill. So use factory method in these cases.



    Some Other Posts

      Tags:

      July 11, 2012

      nth Occurrence of a character in a string

      Filed under: TSQL — maeenul @ 11:29 pm

      In sql server we have a charindex function that returns us the index of a specific character inside a string. It returns the position of the first occurrence. But often we need to find out the position of nth occurrence of a character inside a string. There is no built in function for this. But we can easily write one.

      CREATE FUNCTION [dbo].[fn_NTH_OCCURRENCE] (@STR VARCHAR(MAX), @CHAR VARCHAR(1), @OCCURRENCE INT)

      RETURNS INT

      BEGIN

      SELECT @STR = STUFF(@STR, CHARINDEX(@CHAR, @STR), 1, ‘*’)

      FROM DBO.INTEGERSEQUENCE(1, @OCCURANCE-1)


      RETURN ISNULL(CHARINDEX(@CHAR, @STR), 0)

      END

      GO

      SELECT [dbo].[fn_NTH_OCCURRENCE](‘dbo.fn_PERCENTAGE([2109],[2103])’, ‘[‘, 2)

      The function will return 26. So this is skipping the first position and returning the position of the second occurrence.

      This function takes 3 parameters.

      1. @STR : this is the string where we want to find the nth occurrence of a character.
      2. @CHAR : this is the character that we want to find.
      3. @OCCURRENCE : this is an integer. We will get the @CHAR position for this occurrence.

      If the @OCCURRENCE is larger than the total number of occurrence inside the string, then the function returns 0.

      This function does not use any while loop. Instead of that it uses a table valued function to generate that generates integer sequence. To get this DBO.INTEGERSEQUENCE function, please see the following post.

      Integer Sequence Generator Using CTE (Common Table Expression)

      Integer sequence generator, Date sequence generator



      Some Other Posts

        Tags:

        July 9, 2012

        Parallel Programming not supported with Entity Framework

        Filed under: C# — maeenul @ 11:24 pm

        While working with Parallel programming in C# 4.0 together with Entity Framework, I faced a problem. I found that trying to do some lambda expression to query the entity model inside a parallel for or parallel foreach does not actually work. It may give different error at different time. All the error will occur at runtime. During compile time, you will not get any problem.

        I tried to run the following code. Trying to find out a specific test object searching it with its id. Of course I was trying to do it inside a parallel for block.

        Entities db = new Entities();

        PP.Parallel.For(0, 10, i =>

        {

        List<TEST> tests = db.TESTs.Where(t => t.TEST_ID == i).ToList();

        if (tests.Count != 0)

        {

        Console.WriteLine(“{0}”, tests[0].TEST_ID);

        }

        });

        Sometimes you may get the following error:

        EntityException was unhandled by user code

        The underlying provider failed on Open.

        Sometimes the following:

        MappingException was unhandled by user code

        The type ‘Parallel.TEST’ has been mapped more than once.

        public ObjectSet<TEST> TESTs

        {

        get

        {

        if ((_TESTs == null))

        {

        _TESTs = base.CreateObjectSet<TEST>(“TESTs”);

        }

        return _TESTs;

        }

        }

        private ObjectSet<TEST> _TESTs;

        Now why is that and what is the resolution of this problem. The reason of this problem is very simple. Entity model does not support parallel execution. Entity object model is not thread safe. So you cannot query an entity model inside a parallel for block.

        What is the solution? Very simple. Don’t use it until Microsoft makes this entity model thread safe. :)



        Some Other Posts

          Tags:

          July 7, 2012

          Cannot Implicitly Convert Type IQueryable

          Filed under: C# — maeenul @ 11:18 pm

          Cannot implicitly convert type ‘System.Linq.IQueryable<DBTestTool.Models.TEST>’ to ‘System.Collections.Generic.IList<DBTestTool.Models.TEST>’. An explicit conversion exists (are you missing a cast?)

          Let me explain the scenario where I got this error message.

          IList<TEST> tests = db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME));

          I have a DBContext named db and one entity is TESTS. I just want to find some specific tests that mathces with some criteria. The first and ultimate solution to this is to use Where() function in Lambda Expression. The where function uses the condition inside it and matches all the items in the entity object and returns the matching items. As my items are of TEST type, I am trying to hold it in a IList<TEST>, which is quite logical. But I face an error as mentioned at the top.

          Cannot implicitly convert type ‘System.Linq.IQueryable<DBTestTool.Models.TEST>’ to ‘System.Collections.Generic.IList<DBTestTool.Models.TEST>’. An explicit conversion exists (are you missing a cast?)

          So it says that I need to do and explicit conversion. Why?

          This is because lambda expressions are deferred execution expressions just as LINQ. When we write the lambda expression, it is immediately executed. That’s why it returns IQueryable<Test>, not IList<TEST>. So we need to explicitly cast it to IList<TEST>.

          Solution to this error:

          Solution 1:

          IList<TEST> tests = (IList<TEST>)db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME));

          Just as the error says, use an explicit casting to IList<TEST>. It will force the execution to be done at that time.

          Solution 2:

          IList<TEST> tests = db.TESTs.Where(t => t.VIEW_ID == testTemplate.VIEW_ID && String.IsNullOrEmpty(t.TEMPLATE_FILE_NAME)).ToList();

          Other than casting it, we can also call the ToList() method over the IQueryable<TEST>. This will also allow us to get the immediate execution and return the IList<TEST>.

          So, whenever you use lambda expression that you might think should return an IList<>, you will actually have to cast it and use ToList() to the actual list.



          Some Other Posts

            Tags:

            July 5, 2012

            All Character Occurrence in TSQL

            Filed under: TSQL — maeenul @ 11:00 pm

            In this post, we will see how we can find out what are the different characters available in a string and what is the number of occurrence of each individual character in the string. The first solution that comes to our mind is to use a while loop. This is very trivial solution and most of the beginners will code this way.

            CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))

            RETURNS @tbl TABLE (chr char, freq int)

            AS

            BEGIN

            declare @lp as int

            declare @tbl2 table (chr char)


            set @lp = 1


            while (@lp <= LEN(@str))

            begin

            insert @tbl2

            select SUBSTRING(@str,@lp,1)


            set @lp = @lp + 1

            end;


            insert @tbl

            select chr alpha, COUNT(*) freq

            from @tbl2

            group by chr


            return;

            END;

            GO

            Here we see a table valued function that takes a string as the parameter and finds the number of occurrence of each individual character in the string.

            Now we will see a different solution to this problem which is not very trivial, but seems to be very interesting. We want to get the result but dont want to use any loops. We will use pure sql to do this.

            CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))

            RETURNS @Characters TABLE (chr char, freq int)

            AS

            BEGIN

            insert into @Characters

            select chr, COUNT(*)

            from

            (

            select substring(@str, sequence, 1) as chr

            from dbo.IntegerSequence(1, LEN(@str))

            ) A

            group by chr


            return;

            END;

            GO

            SELECT * from dbo.AllCharacterOccurence (‘This should be very easy to do’);

            The output will be like this. Just the same as the previous function.

            Now let’s see how this works.

            In this function we have used another function dbo.IntegerSequence(1, LEN(@str)) that gives us the integer numbers starting from 1 to the length of the string. The Integer Sequence generator function can be written as follows.

            CREATE FUNCTION dbo.IntegerSequence(@start_number int,@end_number int )

            RETURNS @SequenceTable TABLE (sequence int)

            AS

            BEGIN

            ;With IntegerSequence( Number ) as – Common table expression

            (

            Select @start_number as Number


            union all


            Select Number + 1

            from IntegerSequence

            where Number < @end_number

            )


            insert into @SequenceTable

            Select N.number

            From IntegerSequence N

            Option (MaxRecursion 0)


            return;

            END;

            GO

            So we take the numbers in different rows. (Figure – 1)

            2 3 4
            (Figure – 1)
            (Figure – 2)
            (Figure – 3)

            Now we can use the string in another column together with this select. So we will get the same string in every row. (Figure – 2)

            SELECT sequence, @str

            from dbo.IntegerSequence (1, 30)

            Now we can easily use substring method using the string and the index position to find out the character at the specified position. (Figure – 3)

            SELECT sequence, @str, substring(@str, sequence, 1)

            from dbo.IntegerSequence (1, 30)

            The rest is really easy. You can just use the counting grouping by the characters.



            Some Other Posts

              Tags:

              July 3, 2012

              IsPalindrome in T-SQL

              Filed under: TSQL — maeenul @ 10:57 pm

              A palindrome is a word, phrase, number, or other sequence of units that can be read the same way in either direction. Some simple example of palindrome are

              1. Malayalam

              2. Amma

              3. Appa

              4. Madam

              5. Racecar

              To check whether a string is palindrome or not is very easy. We can create a function as follows:

              CREATE FUNCTION dbo.IsPalindrome (@str varchar(50))

              RETURNS int

              AS

              BEGIN

              if REVERSE(@str) = @str

              return (1)

              return (0)

              END;

              GO

              SELECT dbo.IsPalindrome(‘Malayalam’) – this is a palindrome

              SELECT dbo.IsPalindrome(‘Amma’) – this is a palindrome

              SELECT dbo.IsPalindrome(‘Appa’) – this is a palindrome

              SELECT dbo.IsPalindrome(‘Hey’) – this is not a palindrome

              SELECT dbo.IsPalindrome(‘Hola’) – this is not a palindrome

              SELECT dbo.IsPalindrome(‘Ammaa’) – this is not a palindrome

              The above function returns 1 if the string is a palindrome and returns 0 is the string is not a palindrome.

              To check whether a string is palindrome or not, we just need to reverse the string and check whether the reversed string is equal to the original string or not. As we know, in T-SQL we have a REVERSE function, this is actually a very easy task to do in SQL.



              Some Other Posts

                Tags:

                July 2, 2012

                Integer Sequence Generator Using CTE (Common Table Expression)

                Filed under: TSQL — maeenul @ 10:54 pm

                Often we need to have a sequence of integer numbers in t-sql. There are lots of ways to generate integer sequence in t-sql. You can see one solution in the following location.

                Integer sequence generator, Date sequence generator

                In this post, we will see how easily we can generate integer sequence using CTE (common table expression). This solution is very short and easy to implement. But if we need a large number of integers, i.e. 1 million numbers, then this CTE based solution will be slow and will consume a lot of memory. If we need a small sequence, then probably this CTE based solution will be our choice.

                Declare @start_number int, @end_number int

                Select @start_number=3, @end_number=10;

                With IntegerSequence( Number ) as – Common table expression

                (

                Select @start_number as Number

                union all

                Select Number + 1

                from IntegerSequence

                where Number < @end_number

                )

                Select N.number

                From IntegerSequence N

                Option (MaxRecursion 0)

                We will get the integer sequence as follows.

                We can also create a table valued function using is CTE based solution. In that case, we can avoid using the  Option (MaxRecursion 0) every time and we will be able to use the table valued function everywhere to generate any integer sequence we need.

                CREATE FUNCTION dbo.IntegerSequence (@start_number int, @end_number int )

                RETURNS @SequenceTable TABLE (sequence int)

                AS

                BEGIN


                ;With IntegerSequence( Number ) as – Common table expression

                (

                Select @start_number as Number


                union all


                Select Number + 1

                from IntegerSequence

                where Number < @end_number

                )


                insert into @SequenceTable

                Select N.number

                From IntegerSequence N

                Option (MaxRecursion 0)


                return;


                END;

                GO

                SELECT * from dbo.IntegerSequence (2, 1000);

                SELECT * from dbo.IntegerSequence (200, 789);

                This way we can use this table valued function anywhere and anytime we need to generate an integer sequence.



                Some Other Posts

                  Tags:

                  September 26, 2011

                  JavaScript Add Array to Array

                  Filed under: Javascript — admin @ 11:18 am

                  In this post we will see how to add a JavaScript array at the end of another array. In other words we will append an array to another array. This can be done in many ways. The shortest and easiest way to add an array to another array is using array.push.apply(). But this process is a little difficult to understand for the beginners. So in this post we will see the solution for adding an array to another array and then will explain how exactly this works.

                  Solution for Adding Array to Array

                  How Adding Array to Array Works

                  A Generic Solution in JavaScript to Add Multiple Arrays to Another Array

                   

                  For example we consider the following 2 arrays containing integer values. But for JavaScript array, we don’t need the arrays to be of same type. We will see examples for these cases later.

                  var array1 = [1, 2, 3, 4];

                  var array2 = [9, 8, 7, 6, 5];

                   

                  So what we want after adding array2 to array1 is array1 will contain the following items.

                  [1, 2, 3, 4, 9, 8, 7, 6, 5]

                   

                  Solution for Adding Array to Array:

                   

                  var array1 = [1, 2, 3, 4];

                  var array2 = [9, 8, 7, 6, 5];

                   

                  array1.push.apply(array1, array2);

                   

                  So simple. After executing this array1 will contain the items as we have mentioned before.

                   

                  How Adding Array to Array Works:

                   

                  First we will see what array.push() does? In JavaScript array object, there is a built-in function named push that actually adds an item at the end of the array.

                  var array1 = [1, 2, 3, 4];

                  array1.push(5);

                   

                  This code will add 5 at the end of array1, so array1 will now contain the following items.

                  [1, 2, 3, 4, 5]

                   

                  The good thing about push is it can take multiple items at the same call.

                   

                  var array1 = [1, 2, 3, 4];

                  array1.push(5, 6, 7);

                   

                  This code will add 5, 6 and 7 at the end of array1, so array1 will now contain the following items.

                  [1, 2, 3, 4, 5, 6, 7]

                   

                  Now about apply(). We can call a function in several ways. We can use the function name directly and passing the parameters, which is the most trivial way, just as we called the push method.

                  But there are 2 other ways for calling the push method. One is push.call() and the other is push.apply(). Both these will invoke the push method. But these two works in a little different way. As we are using push.apply() here, we will discuss how apply method works.

                   

                  The signature of apply is as follows:

                   

                  fun.apply(thisArg[, argsArray])

                   

                  So, we call the fun function using apply and apply takes 2 parameters. The first parameter is an object which will be considered as this inside the fun method. The second parameter is an array containing all the parameters that fun function expects. For example,

                   

                  array1.push(param1, param2, param3)

                   

                  Here the push is called on the array1 object. 3 parameters are passed in the push method. Inside push method the object that is considered as this is array1. So in other way, we can do the same calling in the following way using apply

                   

                  array1.push.apply(array1, [param1, param2, param3])

                   

                  So if we now see main example for adding an array to another array,

                   

                  var array1 = [1, 2, 3, 4];

                  var array2 = [9, 8, 7, 6, 5];

                   

                  array1.push(9, 8, 7, 6, 5);

                   

                  Or

                   

                  array1.push.apply(array1, [9, 8, 7, 6, 5]);

                   

                  Or

                   

                  array1.push.apply(array1, array2);

                   

                   

                   

                  A Generic Solution in JavaScript to Add Multiple Arrays to Another Array:

                   

                  Now we will see how we can add multiple arrays at the end of an array at the same call. For this we will extend the JavaScript Array. We name the extension function as Append.

                   

                  Array.Append:

                   

                  var array1 = [1, 2, 3, 4];

                  var array2 = [9, 8, 7];

                  var array3 = ['a', 'b', 6, 5];

                  var array4 = ['z', 1.2, -4.8];

                   

                  Array.prototype.Append = function () {

                  for (i = 0; i < arguments.length; i++) {

                  this.push.apply(this, arguments[i]);

                  }

                  }

                   

                  array1.Append(array2, array3, array4);

                   

                  The array1 will contain the following items after executing the above code.

                   

                  [1, 2, 3, 4, 9, 8, 7, 'a', 'b', 6, 5, 'z', 1.2, -4.8]

                   

                   

                  This way we can add any number of arrays at the end of an array.


                   

                  September 25, 2011

                  Data Generation Plan to Generate Dummy Test Data for SQL Server Tables

                  Filed under: Sql Server 2008 — Tags: — admin @ 10:49 am

                  Often we need some dummy test data in our database tables to test whether the system is working correctly. Sometimes if we have any query or process that might be a consideration of performance issue, then we need to have a large amount of data in our tables to check the performance. While developing we may not have enough data to test. And of course, it is time consuming to write some data generation process of our own.

                  Data generation plan is a new feature in Visual Studio 2010. The purpose of this item is to generate test data. In a short we can do the following things using the data generation plan.

                  1. We can generate as many rows of data we need.
                  2. The data is generated according to the data type defined in the table schema.
                  3. Often we would like to have data in a specific pattern, not just some random data. Data generation plan also provides facility to control the pattern of data we want.
                  4. Sometimes we need unique data in a column, sometimes duplicated data. Data Generation Plan also has the facility to control this.

                   

                  Now we will see how we can generate data in visual studio in a step by step approach.

                  1. From the File->New->Project menu option, select the Database Project Template. Select SQL Server and then SQL Server 2008 Database Project. We give the name of the project as DataGenerationDemo.

                  2. Then we first need to import the database table settings from database where we want the test data to be generated. For this, right click on the project name in the solution explorer and select Import Database Objects and Settings…

                     

                  3. The Import Database Wizard will open. If you already have a database connection defined, you can choose the database connection from the Source Database Connection drop down. If it is not in the drop down, then you can create the connection using the New Connection button.

                  4. Clicking on the New Connection button will open the Connection Properties window. Here we can specify the database server name and optionally instance name, if there is any. Then we will select the database that we want to be imported. Then we can click the test connection button to check whether our connection setting s works or not.

                  5. Then click on the Start button to start importing the database settings.

                  6. A status window will show the progress and when the import will be complete, click Finish to close this window.

                  7. When the database settings and objects are imported, you can now create a data generation plan for any of your database tables. In the Solution Explorer, right click on the Data Generation Plans Folder. Then click Add and then click Data Generation Plan.

                  8. Select the Data Generation Plan and give a name. In this demo we are using the name DataGenerationPlanDemo. Notice that the item has an extension of “.dgen”. In the fact this data generation plan is an xml file. But while in visual studio, we will have a nice design view where we can design our data generation plan.

                     

                     

                  9. In the design mode of Data Generation Plan, we will see all the tables from our database. The first panel is showing the tables and the second panel is showing the columns of the table that is selected. By default all the tables and all the columns are selected for data generation. But if we want.

                     

                  10. We can deselect the tables and columns also.

                     

                     

                  11. We will first see how to generate data for one single table. This can be done for all the tables we need. We have a shortcut for deselecting or selecting all the tables at a time. Right click on any of the table and it will show a context menu. We can click on “Exclude All Tables From Data Generation”.

                     

                   

                  First we will see how to generate data for a simple table that does not have any foreign key relations, meaning the table is independent from every table. This is explained in the next post. Follow the link below to see the details.

                  Data Generation Plan – Generating Data For a Simple Table With No Dependency



                  Some Other Posts

                    Tags:
                    Older Posts »

                    Powered by WordPress