Very good tutorial for WPF

Simple but very informative tutorial by Christian Moser.

http://www.wpftutorial.net/LearnWPFin14Days.html

Calling Webservice from CLR Stored Procedure

Below is a nice article regarding calling a webservice from CLR Stored Procedure.


Calling webservice from CLR Stored Procedure

CLR Stored Procedure

Exploring consuming a webservice from a CLR stored procedure. Quite challenging.
Nice link for the beginners.
http://msdn.microsoft.com/en-us/library/ms131094.aspx 

Web Camp

Luckly I got the following link regarding Microsoft Web Camp from asp.net yesterday.  I have registered for this two days event. Eagerly wating for June 4th and June 5th.

http://www.webcamps.ms/#london_panel

Superfast Opera Browser

We know that Opera is the superfast browser now. But another cool stuff what I liked in Opera is the Visual Tabs. Below is the screenshot.  Cool stuff…

http://www.opera.com/browser/tips/?feature=visualtabs

Bulk Insert in SQL Server using FormatFile

In my previous project I have used bulk insert command in SQL Server 2005. The FormatFile option in BULK INSERT is really a useful feature. I would like to explain how to use this feature to import the data into SQL Server table.

I have created a table called tblProducts. We will import the records from the csv file to this table by using BULK INSERT command.



Before using the BULK INSERT command, let me explain about the FormatFile parameter. This parameter accepts two kind of file formats. One is Fmt format file and another one is XML file. We will see the later one in this article. Below is the sample XML file. It maps the column name in the table and the fields of the data file.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ProductId" xsi:type="SQLSMALLINT"/>
<COLUMN SOURCE="2" NAME="Description" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="UOM" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Price" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="2"/>
</ROW>
</BCPFORMAT>

In this example, we are going to import the following records into the tblProducts table from the Products_Data.csv file. Below is the format of the csv file.

ProductId,Description,UOM,Price 100,Product1,Kg,10 101,Product2,lb,200 102,Product3,lb,300 103,Product4,Kg,2004444.50

Looking closely at the data you can observe that the price for the product id 103 exceeds the maximum length what we have specified in the XML file. i.e the precision is 5 and the scale is 2. So if we execute the bulk insert command what will be the outcome? It is quite interesting. SQL Server will ignore the erroneous records and imports only the valid records. The erroneous records can be diverted to the error file.

We are going to execute the following T-SQL command from SQL Management Studio.

BULK INSERT tblProducts
     FROM 'C:\Vivek\Blog\Products\Products_Data.csv'
     WITH (FIELDTERMINATOR  = ',' , FIRSTROW = 2,  FormatFile='C:\Vivek\Blog\Products\Products_DataFormat.xml')

Once the about command is executed, we can see the first three records in the tblProducts table. But the fourth record is ignored by the SQL Server. Below is the screenshot of imported data.

Microsoft free e-Learning

I found the following link yesterday when I was browsing. Microsoft is offering many             e-Learning courses at free of cost. I added a course regarding WCF and spent few hours.  Really very informative.

https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=213184

SQL Server 2005 and XML

As many of us know in SQL Server 2005 we can generate an XML output from a query very easily. Here are few examples to show how to generate XML from SQL Server.
Simple XML

SELECT TOP 3 ProductID, [Name], ProductNumber,SafetyStockLevel
FROM Production.Product
FOR XML RAW
The output is as below. 

<row ProductID="1" Name="Adjustable Race" ProductNumber="AR-5381" 
SafetyStockLevel="1000" /> 
<row ProductID="2" Name="Bearing Ball" ProductNumber="BA-8327" 
SafetyStockLevel="1000" /> 
<row ProductID="3" Name="BB Ball Bearing" ProductNumber="BE-2349" 
SafetyStockLevel="800" /> 

Column Name as Elements

The above XML is good. But I want an XML with the column name as elements. This can be done by using PATH.

SELECT TOP 3 ProductID, [Name], ProductNumber,SafetyStockLevel
FROM Production.Product
FOR XML PATH('Product')

<Products>
<ProductID>1</ProductID>
<Name>Adjustable Race</Name>
<ProductNumber>AR-5381</ProductNumber>
<SafetyStockLevel>1000</SafetyStockLevel>
</Products>
<Products>
<ProductID>2</ProductID>
<Name>Bearing Ball</Name>
<ProductNumber>BA-8327</ProductNumber>
<SafetyStockLevel>1000</SafetyStockLevel>
</Products>
<Products>
<ProductID>3</ProductID>
<Name>BB Ball Bearing</Name>
<ProductNumber>BE-2349</ProductNumber>
<SafetyStockLevel>800</SafetyStockLevel>
</Products>

The above XML is much better than the XML what we have generated in the previous example. But still there is a problem. This XML is not a well formatted one. There is no root element in the above XML.

The below query will solve our problem.

XML with Root Element

SELECT TOP 5 ProductID, [Name], ProductNumber,SafetyStockLevel
FROM Production.Product
FOR XML PATH('Product'), ROOT('Products')

Here is the XML generated from the above query. Here is the XML with the root element.

<Products>
<Product>
<ProductID>1</ProductID>
<Name>Adjustable Race</Name>
<ProductNumber>AR-5381</ProductNumber>
<SafetyStockLevel>1000</SafetyStockLevel>
</Product>
<Product>
<ProductID>2</ProductID>
<Name>Bearing Ball</Name>
<ProductNumber>BA-8327</ProductNumber>
<SafetyStockLevel>1000</SafetyStockLevel>
</Product>
<Product>
<ProductID>3</ProductID>
<Name>BB Ball Bearing</Name>
<ProductNumber>BE-2349</ProductNumber>
<SafetyStockLevel>800</SafetyStockLevel>
</Product>
</Products>

How simple is it to generate an XML from SQL Server 2005? I am keen to explore the enhancements in SQL Server 2008.

SSIS Execute SQL Task

SSIS Execute SQL task is the very often used task in SSIS. But I found it is bit tricky to call a stored procedure by using OLE DB connection.
Here is a simple example that shows how to call a stored procedure by using Execute SQL task.
Scenario: Execute a stored procedure by passing ProcessName as an input parameter and store the output parameter’s value in a global variable.
Below is the stored procedure that I am going to call from SQL Execute task.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pr_GetFileLocation]
(
@ProcessName NVARCHAR(500),
@Location NVARCHAR(1000) OUTPUT
)
AS
SELECT @Location = [Path] FROM tblDataFeedConfig 
WHERE FeedName = '' + @ProcessName + ''

Step 1: Declare the Location and ProcessName variables. The default value for the ProcessName is “ProductFeed”.





 Step 2:  Drag and drop a Execute SQL task to the Control Flow.





Step 3: Double click on the Execute SQL task. It will bring the task editor window like the below picture. Need to set the properties as below to call the stored procedure.

a) ResultSet - None.

b) ConnectionType - OLE DB.

c) Connection – OLE DB connection.

d) SQLSourceType - Direct Input.

e) SQL Statement – Command to call the stored procedure. This will be explained in the next step.

Step 4:  To execute the stored procedure type the following command in the Enter SQL Query window.

                Exec dbo.pr_GetFileLocation ?, ? OUTPUT. 





Step 5: Click on the Parameter Mapping and add the user defined variables. Here ProcessName is input parameter and Location is the output parameter. This step is very important to get back the value from stored procedure.





Now if you execute the task, the returned value will be stored in the Location variable.

C# 4.0 – Optional Parameters

Optional parameters - one of the coolest feature in C# 4.0. It is similar to the optional parameters in SQL Server stored procedure.

Since this feature is not in the earlier versions, we need to use method overloading. Now, in C# 4.0 instead of method overloading we can use optional parameters.

How to implement an optional parameters in C# 4.0? Here is a simple example.

public void GetSupplierAddress(int SuppNo, string city = null)

Please note the second parameter. It has the null value as default. It is not possible to assign a value like this in the earlier versions of C#. So while calling this method it is possible to ignore the second parameter.

        SupplierData objSuppliers = new SupplierData(); 
        objSuppliers.GetSupplierAddress(1); 
        objSuppliers.GetSupplierAddress(1, "London");

You can find the source code below.

Source Code - Optional Parameters

Wonderful Video Tutorial for jQuery

Here is the wonderful video tutorial for jQuery. This video gives an elaborate overview about jQuery and it is really well presented by Ben Nadal.

http://www.bennadel.com/resources/presentations/jquery/video/index.htm

I have started to follow Ben Nadal’s blog.

www.bennadel.com

SSIS - Error Message

I got the following error when I started to create a simple SSIS package to load data from a csv file to an SQL Server table. This task is quite simple. But I was wondering why I was getting this error message?

Error Message:


 "Cannot create connector. The destination component does not have any available inputs for use in creating path"

What does this error message really mean?

Finally I realised my silly mistake. Here I was using OLE DB Source, whereas I should be using OLE DB destination task.

But I think Microsoft can give a more meaningful error message...

Good link for ASP.NET MVC

Now, Microsoft is giving two options to build web applications. One is the traditional ASP.NET and another one is ASP.NET MVC.

The following are the common questions about ASP.NET MVC framework.

What is ASP.NET MVC? What does really MVC means?

      Is ASP.NET MVC better than ASP.NET?      Why I need to use ASP.NET MVC?
      Which one to choose for my web application?
      Is ASP.NET is deprecated?
      Still lot of questions…

Please visit the following link. It answers many of the above questions and it has presentations, labs and demos.


http://www.microsoft.com/downloads/details.aspx?familyid=1E0BE0B2-910A-4676-9F3A-41E4D9C0FC08&displaylang=en

jQuery in Action

I read this book about jQuery few months back. good one.

jQuery and ASP.NET MVC

jQuery! It is really amazing.

It is a simplified version of JavaScript and has made the web developer's life easy. By using jQuery it is possible to build high speed web applications easily.

ASP.NET MVC includes jQuery. It is the perfect match to build high speed web applications. By using jQuery plungins you can build very robust web applications.

It is worth learning and implementing jQuery.

Please visit: www.jquery.com

Create XML by using LINQ

Is it possible to create an XML from a single statement? Yes, it is possible through LINQ. I think this is the easiest way to create an XML string. Is there any other method much easier than this?

Here is the code.

var xml = new XElement("Products",
     new XElement("Product",
            new XElement("ProductId", "1001"),
                   new XElement("ProductDesc","Product1"),
                           new XElement("UOM", "20")));

Here is the output.

<Products>
          <Product>
                  <ProductId>1001</ProductId>
                 <ProductDesc>Product1</ProductDesc>
                 <UOM>20</UOM>
          </Product>
</Products>