Moving data by using distributed SQL

If you want to move data with different binary pages and page sizes across platforms and you have expertise in using distributed SQL, you can use INSERT and SELECT SQL statements to transfer the data.

Important: Do not use INSERT and SELECT statements to move data if the database contains BLOB data types.

Prerequisites: A network connection must exist between database server instances.

To move data using INSERT and SELECT statements with fully qualified table names:

1. Capture the complete database schema from the source database server.
2. Alter the extent sizing and, if necessary, the lock modes on tables from page to row.
3. Create and verify the schema on the target database server.
4. Disable logging on both source and target servers where necessary.
5. Create and run the following scripts:
1. Create and run separate scripts for:
• Disabling select triggers on the source server
• Disabling indexes, triggers and constraints for each table on the target database server.
2. Create and run one script per table for the fully-qualified INSERT and SELECT statements.

For example:

INSERT INTO dbname@target:owner.table SELECT *

FROM dbname@source:owner.table

You can run the scripts in parallel. In addition, for larger tables, you can create multiple scripts that can partition the table to run in parallel.

3. Create and run separate scripts for enabling indexes, triggers and constraints for each table
5. Run UPDATE STATISTICS on system catalog tables and stored procedures and functions on the target database server.
6. Adjust starting values for all tables that have serial columns on the target database server.
7. Turn on transaction logging on the source and target database servers.
8. Return the source and target database servers to multi-user mode.
9. Validate the data that was transferred to the target database server.

Using Merge in SQL Server 2008 R2

To Merge Data of different tables we can use merge statement in sql which first checks the content of both the tables and adds the data in the Target table which are missing from Soucre Table.

Syntax:

MERGE INTO [TARGETTABLE] AS Target
    USING [SOURCETABLE] AS SOURCE
    ON
        --unique key filtering condition
        Target.COLUMN1=SOURCE.COLUMN1 AND
        Target.COLUMN2=SOURCE.COLUMN2 AND
        Target.COLUMN3 =SOURCE.COLUMN3 AND
        Target.COLUMN4 =SOURCE.COLUMN4
     WHEN MATCHED THEN
        --update or delete query
        UPDATE SET
            Target.ColumnA=SOURCE.ColumnA,
            Target.ColumnB=SOURCE.ColumnB,
            Target.ColumnC=SOURCE.ColumnC

    WHEN NOT MATCHED THEN
        --INSERT QUERY
        INSERT (COLUMNA, ColumnB, ColumnC, ColumnD)
        VALUES(
                        SOURCE.ColumnA,
                        SOURCE.ColumnB,
                        SOURCE.ColumnC,
                        SOURCE.ColumnD                    )
    ;

Random String Generator C#

using System;
    using System.Security.Cryptography;

    /// <summary>
    /// TODO: Update summary.
    /// </summary>
    public class RandomStringGenerator
    {
        public bool UseSpecialCharacters { get; set; }

        public RandomStringGenerator()
        {
            UseSpecialCharacters = true;
        }

        private static int DEFAULT_MIN_PASSWORD_LENGTH = 8;
        private static int DEFAULT_MAX_PASSWORD_LENGTH = 10;

        // Define supported password characters divided into groups.
        // You can add (or remove) characters to (from) these groups.
        private const string PASSWORD_CHARS_LCASE = "abcdefgijkmnopqrstwxyz";

        private const string PASSWORD_CHARS_UCASE = "ABCDEFGHJKLMNPQRSTWXYZ";
        private const string PASSWORD_CHARS_NUMERIC = "23456789";
        private const string PASSWORD_CHARS_SPECIAL = "*$-+?_&=!%{}/";

        /// <summary>
        /// Generates a random password.
        /// </summary>
        /// <returns>
        /// Randomly generated password.
        /// </returns>
        /// <remarks>
        /// The length of the generated password will be determined at
        /// random. It will be no shorter than the minimum default and
        /// no longer than maximum default.
        /// </remarks>
        public string Generate()
        {
            return Generate(DEFAULT_MIN_PASSWORD_LENGTH,
                            DEFAULT_MAX_PASSWORD_LENGTH);
        }

        /// <summary>
        /// Generates a random password of the exact length.
        /// </summary>
        /// <param name="length">
        /// Exact password length.
        /// </param>
        /// <returns>
        /// Randomly generated password.
        /// </returns>
        public string Generate(int length)
        {
            length = 8;
            return Generate(length, length);
        }

        /// <summary>
        /// Generates a random password.
        /// </summary>
        /// <param name="minLength">
        /// Minimum password length.
        /// </param>
        /// <param name="maxLength">
        /// Maximum password length.
        /// </param>
        /// <returns>
        /// Randomly generated password.
        /// </returns>
        /// <remarks>
        /// The length of the generated password will be determined at
        /// random and it will fall with the range determined by the
        /// function parameters.
        /// </remarks>
        public string Generate(int minLength,
                                      int maxLength)
        {
            // Make sure that input parameters are valid.
            if (minLength <= 0 || maxLength <= 0 || minLength > maxLength)
                return null;

            // Create a local array containing supported password characters
            // grouped by types. You can remove character groups from this
            // array, but doing so will weaken the password strength.

            char[][] charGroups = new char[][]
            {
                PASSWORD_CHARS_LCASE.ToCharArray(),
                PASSWORD_CHARS_UCASE.ToCharArray(),
                PASSWORD_CHARS_NUMERIC.ToCharArray(),
            };
            if (UseSpecialCharacters)
            {
                charGroups.Append(PASSWORD_CHARS_SPECIAL.ToCharArray());
            }

            // Use this array to track the number of unused characters in each
            // character group.
            int[] charsLeftInGroup = new int[charGroups.Length];

            // Initially, all characters in each group are not used.
            for (int i = 0; i < charsLeftInGroup.Length; i++)

                charsLeftInGroup[i] = charGroups[i].Length;

            // Use this array to track (iterate through) unused character groups.
            int[] leftGroupsOrder = new int[charGroups.Length];

            // Initially, all character groups are not used.
            for (int i = 0; i < leftGroupsOrder.Length; i++)

                leftGroupsOrder[i] = i;

            // Because we cannot use the default randomizer, which is based on the
            // current time (it will produce the same "random" number within a
            // second), we will use a random number generator to seed the
            // randomizer.

            // Use a 4-byte array to fill it with random bytes and convert it then
            // to an integer value.
            byte[] randomBytes = new byte[4];

            // Generate 4 random bytes.
            var rng = new RNGCryptoServiceProvider();

            rng.GetBytes(randomBytes);

            // Convert 4 bytes into a 32-bit integer value.
            int seed = (randomBytes[0] & 0x7f) << 24 |

                       randomBytes[1] << 16 |

                       randomBytes[2] << 8 |

                       randomBytes[3];

            // Now, this is real randomization.
            Random random = new Random(seed);

            // This array will hold password characters.
            char[] password = null;

            // Allocate appropriate memory for the password.
            if (minLength < maxLength)

                password = new char[random.Next(minLength, maxLength + 1)];

            else

                password = new char[minLength];

            // Index of the next character to be added to password.
            int nextCharIdx;

            // Index of the next character group to be processed.
            int nextGroupIdx;

            // Index which will be used to track not processed character groups.
            int nextLeftGroupsOrderIdx;

            // Index of the last non-processed character in a group.
            int lastCharIdx;

            // Index of the last non-processed group.
            int lastLeftGroupsOrderIdx = leftGroupsOrder.Length - 1;

            // Generate password characters one at a time.
            for (int i = 0; i < password.Length; i++)
            {
                // If only one character group remained unprocessed, process it;
                // otherwise, pick a random character group from the unprocessed
                // group list. To allow a special character to appear in the
                // first position, increment the second parameter of the Next
                // function call by one, i.e. lastLeftGroupsOrderIdx + 1.
                if (lastLeftGroupsOrderIdx == 0)
                    nextLeftGroupsOrderIdx = 0;
                else
                    nextLeftGroupsOrderIdx = random.Next(0,
                                                         lastLeftGroupsOrderIdx);

                // Get the actual index of the character group, from which we will
                // pick the next character.
                nextGroupIdx = leftGroupsOrder[nextLeftGroupsOrderIdx];

                // Get the index of the last unprocessed characters in this group.
                lastCharIdx = charsLeftInGroup[nextGroupIdx] - 1;

                // If only one unprocessed character is left, pick it; otherwise,
                // get a random character from the unused character list.
                if (lastCharIdx == 0)
                    nextCharIdx = 0;
                else
                    nextCharIdx = random.Next(0, lastCharIdx + 1);

                // Add this character to the password.
                password[i] = charGroups[nextGroupIdx][nextCharIdx];

                // If we processed the last character in this group, start over.
                if (lastCharIdx == 0)

                    charsLeftInGroup[nextGroupIdx] =

                                              charGroups[nextGroupIdx].Length;

                // There are more unprocessed characters left.
                else
                {
                    // Swap processed character with the last unprocessed character
                    // so that we don't pick it until we process all characters in
                    // this group.
                    if (lastCharIdx != nextCharIdx)
                    {
                        char temp = charGroups[nextGroupIdx][lastCharIdx];

                        charGroups[nextGroupIdx][lastCharIdx] =

                                    charGroups[nextGroupIdx][nextCharIdx];

                        charGroups[nextGroupIdx][nextCharIdx] = temp;
                    }

                    // Decrement the number of unprocessed characters in
                    // this group.
                    charsLeftInGroup[nextGroupIdx]--;
                }

                // If we processed the last group, start all over.
                if (lastLeftGroupsOrderIdx == 0)
                    lastLeftGroupsOrderIdx = leftGroupsOrder.Length - 1;
                // There are more unprocessed groups left.
                else
                {
                    // Swap processed group with the last unprocessed group
                    // so that we don't pick it until we process all groups.
                    if (lastLeftGroupsOrderIdx != nextLeftGroupsOrderIdx)
                    {
                        int temp = leftGroupsOrder[lastLeftGroupsOrderIdx];

                        leftGroupsOrder[lastLeftGroupsOrderIdx] =

                                    leftGroupsOrder[nextLeftGroupsOrderIdx];

                        leftGroupsOrder[nextLeftGroupsOrderIdx] = temp;
                    }
                    // Decrement the number of unprocessed groups.
                    lastLeftGroupsOrderIdx--;
                }
            }

            // Convert password characters into a string and return the result.
            return new string(password);
        }
    }

    /// <summary>
    /// Illustrates the use of the RandomPassword class.
    /// </summary>
    ////////public class RandomPasswordTest
    ////////{
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    ////////[STAThread]
    ////////private static void Main(string[] args)
    ////////{
    ////////    // Print 100 randomly generated passwords (8-to-10 char long).
    ////////    for (int i = 0; i < 100; i++)
    ////////        Console.WriteLine(RandomPassword.Generate(8, 10));
    ////////}
    ////////}

SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC()

SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always

What is the difference between EXEC and EXECUTE?

They are the same. Both of them executes stored procedure when called as
EXEC sp_help
GO
EXECUTE sp_help
GO

I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.
EXEC('EXEC sp_help')
GO

Escape Sequences in MSSQL

User can escape single quote using two single quotes (NOT double quote).
e.g. SELECT 'Hi, My Name''s Steve.'
will result:
 Hi, My Name's Steve.

Using Coalesce and quotename in sql

COAlesce(expression n)
works same as isnull but
 returns highest precision value of not null value as result
also if all the value are null coalesce returns null value but isnull returns not null value.
coalesce esecutes more than once to get result
but isnull is a function and hence executes only ones to get result

QUOTENAME(ColumnName)
returns [ColumnName]
use: to get name of columns for pivoting.

example:
Declare @JVNO nvarchar(200)
select @JVNO=
COALESCE(@JVNO+', ','') +QuoteName(JVNo,'''')
from (select distinct JVNo     from tbl_incomejournaldetails inner join tbl_incomeJournalmaster on tbl_incomeJournalmaster .JournalVoucherID =tbl_incomejournaldetails .JournalVoucherID
inner join tbl_LedgerMaster on tbl_incomejournaldetails .LedgerMasterID=tbl_LedgerMaster .LedgerMasterID
where tbl_LedgerMaster .Code='Previous Year') as B
Order By B.JVNo
--print @JVNO

declare @sql nvarchar(max)
set @sql='select * from tbl_incomejournaldetails inner join tbl_incomeJournalmaster
on tbl_incomeJournalmaster.JournalVoucherID =tbl_incomejournaldetails.JournalVoucherID
where JVNo   in ('+@JVNO+')'
--print @sql
exec( @sql)

Sql joins

c# interview question

1. What is C#?

C# is an object oriented, type safe and managed language that is compiled by .Net framework to generate Microsoft Intermediate Language.

2.       What are the types of comment in C# with examples?

Single line

Eg:

[csharp]   //This is a Single line comment[/csharp]

ii. Multiple line (/* */)

Eg:

[csharp] /*This is a multiple line comment
We are in line 2
Last line of comment*/[/csharp]

iii. XML Comments (///).

Eg:

[csharp]/// summary;
///  Set error message for multilingual language.
/// summary[/csharp]

3.       Can multiple catch blocks be executed?

No, Multiple catch blocks can’t be executed. Once the proper catch code executed, the control is transferred to the finally block and then the code that follows the finally block gets executed.

4.       What is the difference between public, static and void?

All these are access modifiers in C#. Public declared variables or methods are accessible anywhere in the application. Static declared variables or methods are globally accessible without creating an instance of the class. The compiler stores the address of the method as the entry point and uses this information to begin execution before any objects are created. And Void is a type modifier that states that the method or variable does not return any value.

5.       What is an object?  

An object is an instance of a class through which we access the methods of that class. “New” keyword is used to create an object. A class that creates an object in memory will contain the information about the methods, variables and behavior of that class.

6.       Define Constructors?  

A constructor is a member function in a class that has the same name as its class. The constructor is automatically invoked whenever an object class is created. It constructs the values of data members while initializing the class.

7.       What is Jagged Arrays?

The array which has elements of type array is called jagged array. The elements can be of different dimensions and sizes. We can also call jagged array as Array of arrays.

8.       What is the difference between ref & out parameters?

An argument passed as ref must be initialized before passing to the method whereas out parameter needs not to be initialized before passing to a method.

9.       What is the use of using statement in C#?  

The using block is used to obtain a resource and use it and then automatically dispose of when the execution of block completed.

10.   What is serialization?  

When we want to transport an object through network then we have to convert the object into a stream of bytes. The process of converting an object into a stream of bytes is called Serialization. For an object to be serializable, it should inherit ISerialize Interface.
De-serialization is the reverse process of creating an object from a stream of bytes.

11.   Can “this” be used within a static method?  

We can’t use ‘This’ in a static method because we can only use static variables/methods in a static method.

12.   What is difference between constants and read-only?  

Constant variables are declared and initialized at compile time. The value can’t be changed after wards. Read-only variables will be initialized only from the Static constructor of the class. Read only is used only when we want to assign the value at run time.

13.   What is an interface class?  

Interface is an abstract class which has only public abstract methods and the methods only have the declaration and not the definition. These abstract methods must be implemented in the inherited classes.

14.   What are value types and reference types?  

Value types are stored in the Stack whereas reference types stored on heap.
Value types:

[csharp] int, enum , byte, decimal, double, float, long[/csharp]

Reference Types:

[csharp] string , class, interface, object.[/csharp]

15.   What are Custom Control and User Control?  

Custom Controls are controls generated as compiled code (Dlls), those are easier to use and can be added to toolbox. Developers can drag and drop controls to their web forms. Attributes can be set at design time. We can easily add custom controls to Multiple Applications (If Shared Dlls), If they are private then we can copy to dll to bin directory of web application and then add reference and can use them.
User Controls are very much similar to ASP include files, and are easy to create. User controls can’t be placed in the toolbox and dragged – dropped from it. They have their design and code behind. The file extension for user controls is ascx.

16.   What are sealed classes in C#?  

We create sealed classes when we want to restrict the class to be inherited. Sealed modifier used to prevent derivation from a class. If we forcefully specify a sealed class as base class then a compile-time error occurs.

17.   What is method overloading?  

Method overloading is creating multiple methods with the same name with unique signatures in the same class. When we compile, the compiler uses overload resolution to determine the specific method to be invoke.

18.   What is the difference between Array and Arraylist?  

In an array, we can have items of the same type only. The size of the array is fixed. An arraylist is similar to an array but it doesn’t have a fixed size.

19.   Can a private virtual method be overridden?  

No, because they are not accessible outside the class.

20. Describe the accessibility modifier “protected internal”.

Protected Internal variables/methods are accessible within the same assembly and also from the classes that are derived from this parent class.

21. What are the differences between System.String and System.Text.StringBuilder classes?

System.String is immutable. When we modify the value of a string variable then a new memory is allocated to the new value and the previous memory allocation released. System.StringBuilder was designed to have concept of a mutable string where a variety of operations can be performed without allocation separate memory location for the modified string.

22. What’s the difference between the System.Array.CopyTo() and System.Array.Clone() ?

Using Clone() method, we creates a new array object containing all the elements in the original array and using CopyTo() method, all the elements of existing array copies into another existing array. Both the methods perform a shallow copy.

23. How can we sort the elements of the array in descending order?

Using Sort() methods followed by Reverse() method.

24. Write down the C# syntax to catch exception?

To catch an exception, we use try catch blocks. Catch block can have parameter of system.Exception type.

Eg:

[csharp]try
{
GetAllData();
}
catch(Exception ex)
{
}[/csharp]

In the above example, we can omit the parameter from catch statement.

25.   What’s the difference between an interface and abstract class?

Interfaces have all the methods having only declaration but no definition. In an abstract class, we can have some concrete methods. In an interface class, all the methods are public. An abstract class may have private methods.

 26.   What is the difference between Finalize() and Dispose() methods?

Dispose() is called when we want for an object to release any unmanaged resources with them. On the other hand Finalize() is used for the same purpose but it doesn’t assure the garbage collection of an object.

27.   What are circular references?

Circular reference is situation in which two or more resources are interdependent on each other causes the lock condition and make the resources unusable.

28.   What are generics in C#.NET?

Generics are used to make reusable code classes to decrease the code redundancy, increase type safety and performance. Using generics, we can create collection classes. To create generic collection, System.Collections.Generic namespace should be used instead of classes such as ArrayList in the System.Collections namespace. Generics promotes the usage of parameterized types.

29.   What is an object pool in .NET?

An object pool is a container having objects ready to be used. It tracks the object that is currently in use, total number of objects in the pool. This reduces the overhead of creating and re-creating objects.

30.   List down the commonly used types of exceptions in .Net?

ArgumentException, ArgumentNullException , ArgumentOutOfRangeException, ArithmeticException, DivideByZeroException ,OverflowException , IndexOutOfRangeException ,InvalidCastException ,InvalidOperationException , IOEndOfStreamException , NullReferenceException , OutOfMemoryException , StackOverflowException etc.

31.   What are Custom Exceptions?

Sometimes there are some errors that need to be handeled as per user requirements. Custom exceptions are used for them and are used defined exceptions.

32.   What are delegates?

Delegates are same are function pointers in C++ but the only difference is that they are type safe unlike function pointers. Delegates are required because they can be used to write much more generic type safe functions.

33.   How do you inherit a class into other class in C#?

Colon is used as inheritance operator in C#. Just place a colon and then the class name.

[csharp] public class DerivedClass : BaseClass[/csharp]

34.   What is the base class in .net from which all the classes are derived from?

[csharp]System.Object[/csharp]

35.   What is the difference between method overriding and method overloading?

In method overriding, we change the method definition in the derived class that changes the method behavior. Method overloading is creating a method with the same name within the same class having different signatures.

36. What are the different ways a method can be overloaded?

Methods can be overloaded using different data types for parameter, different order of parameters, and different number of parameters.

37. Why can’t you specify the accessibility modifier for methods inside the interface?

In an interface, we have virtual methods that do not have method definition. All the methods are there to be overridden in the derived class. That’s why they all are public.

38.   How can we set class to be inherited, but prevent the method from being over-ridden?

Declare the class as public and make the method sealed to prevent it from being overridden.

39. What happens if the inherited interfaces have conflicting method names?

Implement is up to you as the method is inside your own class. There might be problem when the methods from different interfaces expect different data, but as far as compiler cares you’re okay.

40. What is the difference between a Struct and a Class?

Structs are value-type variables and classes are reference types. Structs stored on the stack, causes additional overhead but faster retrieval. Structs cannot be inherited.

41.   How to use nullable types in .Net?

Value types can take either their normal values or a null value. Such types are called nullable types.

[csharp]Int? someID = null;
If(someID.HasVAlue)
{
}
[/csharp]

42.   How we can create an array with non-default values?

We can create an array with non-default values using Enumerable.Repeat.

43.   What is difference between is and as operators in c#?

“is” operator is used to check the compatibility of an object with a given type and it returns the result as Boolean.

“as” operator is used for casting of object to a type or a class.

44.   What’s a multicast delegate?

A delegate having multiple handlers assigned to it is called multicast delegate. Each handler is assigned to a method.

 45.   What are indexers in C# .NET?

Indexers are known as smart arrays in C#. It allows the instances of a class to be indexed in the same way as array.

Eg:

[csharp]public int this[int index]    // Indexer declaration[/csharp]

46.   What is difference between the “throw” and “throw ex” in .NET?

“Throw” statement preserves original error stack whereas “throw ex” have the stack trace from their throw point. It is always advised to use “throw” because it provides more accurate error information.

47.   What are C# attributes and its significance?

C# provides developers a way to define declarative tags on certain entities eg. Class, method etc. are called attributes. The attribute’s information can be retrieved at runtime using Reflection.

48.   How to implement singleton design pattern in C#?

In singleton pattern, a class can only have one instance and provides access point to it globally.

Eg:

[csharp]
Public sealed class Singleton
{
Private static readonly Singleton _instance = new Singleton();
}
[/csharp]

49.   What is the difference between directcast and ctype?

DirectCast is used to convert the type of an object that requires the run-time type to be the same as the specified type in DirectCast.

Ctype is used for conversion where the conversion is defined between the expression and the type.

50.   Is C# code is managed or unmanaged code?

C# is managed code because Common language runtime can compile C# code to Intermediate language.