Monday, September 30, 2013

Sql Bulk Insert

Sql Bulk Insert -




use master
DECLARE @TESTPB TABLE (Lr_no Varchar (50), Bkg_Stn Varchar(30) , FrmShort Varchar(5))
DECLARE @xml XML;
SELECT @xml = (
 SELECT * FROM OPENROWSET(
 BULK 'D:\newxf.xml',
 SINGLE_BLOB) x)
INSERT INTO @TestPB(LR_no,Bkg_Stn,FrmShort)
SELECT  X.product.value('LR_no[1]', 'Varchar(50)'),
  X.product.value('Bkg_Stn[1]', 'Varchar(30)'),
  X.product.value('FrmShort[1]', 'Varchar(5)')
FROM
@xml.nodes('NewDataSet/LRTest') AS X(product);
INSERT INTO Master.dbo.IExml
Select * From @TestPB



/* Create some tables to work with */
CREATE TABLE dbo.orders
(
      order_id      INT IDENTITY(1,1)   Not Null
    , orderDate     DATETIME            Not Null
    , customer_id   INT                 Not Null
 
    CONSTRAINT PK_orders
        PRIMARY KEY CLUSTERED(order_id)
);
 
CREATE TABLE dbo.orderDetails
(
      orderDetail_id    INT IDENTITY(1,1)   Not Null
    , order_id          INT                 Not Null
    , lineItem          INT                 Not Null
    , product_id        INT                 Not Null
 
    CONSTRAINT PK_orderDetails
        PRIMARY KEY CLUSTERED(orderDetail_id)
 
    CONSTRAINT FK_orderDetails_orderID
        FOREIGN KEY(order_id)
        REFERENCES dbo.orders(order_id)
);
Go
 
/* Create a new procedure using an XML parameter */
CREATE PROCEDURE dbo.insert_orderXML_sp
      @orderDate        DATETIME
    , @customer_id      INT
    , @orderDetailsXML  XML
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @myOrderID INT;
 
    INSERT INTO dbo.orders
    (
          orderDate
        , customer_id    
    )
    VALUES
    (
          @orderDate
        , @customer_id
    );
 
    SET @myOrderID = SCOPE_IDENTITY();
 
    INSERT INTO dbo.orderDetails
    (
          order_id
        , lineItem
        , product_id
    )
    SELECT @myOrderID
         , myXML.value('./@lineItem', 'int')
         , myXML.value('./@product_id', 'int')
    FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML);
 
    SET NOCOUNT OFF;
 
END
GO
 
 
/* Call our stored procedure */
EXECUTE dbo.insert_orderXML_sp
      @orderDate = '2008-01-01'
    , @customer_id = 101
    , @orderDetailsXML = 
        '<orderDetail lineItem="1" product_id="123" />
         <orderDetail lineItem="2" product_id="456" />
         <orderDetail lineItem="3" product_id="789" />
         <orderDetail lineItem="4" product_id="246" />
         <orderDetail lineItem="5" product_id="135" />';
 
 
/* Check our data */
SELECT * FROM dbo.orders;
SELECT * FROM dbo.orderDetails;
 
 
/* Clean up our mess */
DROP PROCEDURE insert_orderXML_sp;
DROP TABLE dbo.orderDetails;
DROP TABLE dbo.orders;

Monday, April 8, 2013

Inserted, Deleted Logical table(magic Table) in SQL Server

Inserted, Deleted Logical table(magic Table) in SQL Server

As per my studies, I found that there are two(2) Magic Tables(logical tables) - 

      1. Inserted
      2. Deleted
What happens actually when you execute the update command or update the table? 
when you execute the update command or update the table then it will first delete the record and then re-insert the new values or the reverse of that.

Magic Tables(logical tables) can be used with the following commands:
  1. Insert
  2. Delete
  3. Update
Note: to use Magic Tables you have to use OUTPUT in the query.

Magic tables are automatically managed and created by Microsoft SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.
 
These are not physical tables, only internal tablesMagic tables(Logical tables) is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only but, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.

Use of  Magic Tables :

1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference b/w the state of table before and after the data modification and take 
   actions based on the difference.

Tuesday, March 19, 2013

Extension methods in c#


Today, we are going to take a look on extension methods. This feature is introduced in C#.NET 3.0. Extension methods defined in the "System.Linq" namespace. For LINQ developers, the use of extension methods begins when you start working with LINQ itself.

What are Extension Methods?


Extension methods are a special kind of static method that allows you to "add" methods to existing types without creating a new derived type, recompiling, or otherwise modifying the original type.
                                                                       Or
Extension methods are a special kind of static method that allows you to add new methods to existing types without creating derived types.

All the extension methods have that little blue down arrow as part of their icon, and the tool tip will have the word "(extension)" in front of the method name.



Number of reasons behind the popularity of extension methods. flexibility is one of them. However there are two vital reasons to use extension methods-

 You can add new functionality in extension methods -
  • Without recompiling the existing type.
  • Without touching the original assembly.
Extension Methods
  • Defined in a static class
  • Defined as static
  • Use “this” keyword before its argument to specify the class to be extended.
  • Can also be called from statically through the defining static class.
  • Extension methods can’t access the private methods in the extended type. 

Extension Methods with Example

public static class WordCount
{
     public static int WordsCount(this String mStr) 
     {
        return mStr.Split(new char[]{ ' ', '.', '?' },
        StringSplitOptions.RemoveEmptyEntries).Length;
     } 
}


Complete Code:

using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;

namespace extensionmethods
{
   public static class WordCount
   {
     public static int WordsCount(this String mStr)
     {
        return mStr.Split(new char[] { ' ', '.', '?' },
        StringSplitOptions.RemoveEmptyEntries).Length;
      }
   }


 class Program
 {
     static void Main(string[] args)
     {
           string strName =
"Hello Friends";
           Console.WriteLine(strName .WordsCount());
           Console.ReadLine();
     }
  }



Binding Extension Methods at Compile Time

You can use extension methods to extend a class or interface, but not to override them. An extension method with the same name and signature as an interface or class method will never be called.


At compile time, extension methods always have lower priority than instance methods defined in the type itself. In other words, if a type has a method named WordsCount (string name) and you have an extension method with the same signature, the compiler will always bind to the instance method.
When the compiler encounters a method invocation, it first looks for a match in the type's instance methods. If no match is found, it will search for any extension methods that are defined for the type, and bind to the first extension method that it finds.