Integrating salesforce Using the DeveloperForce Toolkit for .NET

Salesforce can be integrated with any .NET based application through the salesforce DeveloperForce Toolkits for .NET. This article will show how to connect to salesforce and manipulate data within ASP.NET application hosted outside Salesforce.com.

Salesforce SOAP base API

Salesforce can be integrated through the salesforce SOAP API (http://www.ashishblog.com/integrating-salesforce-soap-api-using-c-net/)

[ad#post]
Before we begin, there are a set of prerequisites that you need to get done in order to interact with salesforce using the DeveloperForce Toolkits for .NET.

Username and Password (salesforce logins)
Security Token
ConsumerKey and ConsumerSecret
DeveloperForce Toolkits for .NET

Username and Password

You don’t need to use live company data and risk making a mess of things. You can register for a developer license in case you don’t have a user name and password at https://developer.salesforce.com/signup
Security Token:
My Setting > Personal > Reset My Security Token

SaleforceSecurityToken

ConsumerKey and ConsumerSecret

The Consumer Key and Consumer Secret are available on the Connected App Details page.
Setup > Create > Apps > Connected Apps > New

connectedapps

Now fill Basic Information and API (Enable OAuth Setting) then Click ”SAVE

Salesforce ConsumerKey and ConsumerSecret

Salesforce Consumer Key Token page

DeveloperForce Toolkits for .NET

This Toolkit is open source and available on GitHub (https://github.com/developerforce/Force.com-Toolkit-for-NET). The easiest way to get this toolkit is to use the NuGet packages.

Install-Package DeveloperForce.Common
Install-Package DeveloperForce.Force

.NET Web Application

Let’s start exploring the DeveloperForce Toolkits for .NET functionality by creating a sample .Net web application.

Create new Project and select ASP.NET Empty Web Application in Visual Studio

To add DeveloperForce Toolkits for .NET, open up NuGet packages dialog and search “DeveloperForce”.

Saleforce_DeveloerForce.Force

Install “DeveloperForce.Force” as show in above image and “DeveloperForce.Common” will automatically install in our website as well.

Saleforce_DeveloerForce.Force_NuGet

Now that the base web Application is created and a reference setup to salesforce, we can transition to writing code that calls Salesforce data.

Here is example code showing how to authenticate using DeveloperForce Toolkits for .NET
[ad#post]

using System;
using System.Linq;
using System.Text;
using Salesforce.Common;
using Salesforce.Force; 
using System.Dynamic;
 
namespace AB_SalesForce_NET_Toolkits
{ 
public partial class Default : System.Web.UI.Page
{
    private static readonly string SecurityToken = "xxxxxxxxxxxxxxxxxxxxxxxx";
    private static readonly string ConsumerKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    private static readonly string ConsumerSecret = "xxxxxxxxxxxxxxxxxxx";
    private static readonly string Username = "[email protected]";
    private static readonly string Password = "xxxxxxxxxxxxx";
    private static readonly string IsSandboxUser = "false";

    protected async void Page_Load(object sender, EventArgs e)
    {
        StringBuilder sb = new StringBuilder();
        try
        {
            var auth = new AuthenticationClient();
 
             // Authenticate with Salesforce              
                sb.Append("Authenticating with Salesforce");
                var url = IsSandboxUser.Equals("true", StringComparison.CurrentCultureIgnoreCase)
                    ? "https://test.salesforce.com/services/oauth2/token"
                    : "https://login.salesforce.com/services/oauth2/token";

                await auth.UsernamePasswordAsync(ConsumerKey, ConsumerSecret, Username, Password + SecurityToken, ".net-api-client", url);
                sb.Append("<br/>"); sb.Append("Connected to Salesforce");

                var client = new ForceClient(auth.InstanceUrl, auth.AccessToken, auth.ApiVersion);     
 
        }
        catch (Exception ex)
        {
            sb.Append("<br/>"); sb.Append(ex.Message);
                sb.Append("<br/>"); sb.Append(ex.StackTrace);

                var innerException = ex.InnerException;
                while (innerException != null)
                {
                    sb.Append("<br/>"); sb.Append(innerException.Message);
                    sb.Append("<br/>"); sb.Append(innerException.StackTrace);

                    innerException = innerException.InnerException;
                }
        } 
            Response.Write(sb.ToString());
    } 
}
}

That’s it! You have just successfully logged in to the salesforce and create ForceClient to conduct further operations on the salesforce. The following code will use this to create, retrieve, update and delete data of product from .NET.

Product Class:

public class Product2
    {
        public const String SObjectTypeName = "Product2"; 
        public String Id { get; set; }
        public String Name { get; set; }
        public String ProductCode { get; set; }
        public String Description { get; set; }
        public bool? IsActive { get; set; }
    }

More information on Salesforce object https://www.salesforce.com/us/developer/docs/api/index_Left.htm#CSHID=sforce_api_objects_product2.htm|StartTopic=Content%2Fsforce_api_objects_product2.htm|SkinName=webhelp

INSERT Product

Product2 insertProduct = new Product2();
insertProduct.IsActive = true;
insertProduct.Name = "Test Product x";
insertProduct.Description = "Test Product x";
insertProduct.ProductCode = "Testx"; 
insertProduct.Id = await client.CreateAsync(Product2.SObjectTypeName, insertProduct);
if (insertProduct.Id == null)
{
    sb.Append("<br/>"); sb.Append("Failed to create product record.");
 }
 else
 {
    sb.Append("<br/>Insert Product Successfully!!!");
 }

You can also use the dynamic ExpandoObject to build up an object.

  dynamic c = new ExpandoObject();
c.IsActive = true;
c.Name = "Test Product x";
c.Description = "Test Product x";
c.ProductCode = "Testx";
c.Id = await client.CreateAsync("Product2", insertProduct);

Query Products

var readProducts = await client.QueryAsync<Product2>("SELECT ID, Name,Description,ProductCode,IsActive FROM Product2 where ProductCode='Testx'");
  if (readProducts.totalSize > 0)
  {
      Product2 readProduct = readProducts.records[0];
      productid = readProduct.Id; // save id show we can use in update and delete product
      sb.Append("Product Found!!<br/>");
      sb.Append("Name:" + readProduct.Name);
      sb.Append("<br/>ProductCode:" + readProduct.ProductCode);
      sb.Append("<br/>ID:" + readProduct.Id);
   }
   else
   {
      //put some code in here to handle no records being returned
       string message = "No records returned.";
       sb.Append("<br/>" + message);
   }

UPDATE Product

var success = await client.UpdateAsync(Product2.SObjectTypeName, productid, new { Name = "Test Product 1" });
 if (!success)
 {
     sb.Append("<br/>"); sb.Append("Failed to update product!");
 }
 else
 {
     sb.Append("<br/>UPDATE Product Successfully!!!");
 }

Delete Product

success = await client.DeleteAsync(Product2.SObjectTypeName, productid);
  if (!success)
  {
      sb.Append("<br/>"); sb.Append("Failed to delete the product by ID!");   
   }
   else
   {
       sb.Append("<br/>DELETE Product Successfully!!!");
   }

[ad#post]

Error:

If you see below screen of death

error

Then Add Async =”True” on Design page.

error_Solution

Download Code:

[wpdm_file id=7]

Thanks

How to set focus on first textbox of the page

Below I have given the complete sample code where the cursor will be placed on first textbox when the page loads.

Code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Ashish's Blog</title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('form input:text:enabled:first').focus();
        }); 
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:TextBox ID="TextBox1" runat="server" />
 
    <asp:TextBox ID="TextBox2" runat="server" />
 
    <asp:TextBox ID="TextBox3" runat="server" />
 
    <asp:TextBox ID="TextBox4" runat="server" />
 
    </form>
</body>
</html>

UPDATE:

If you first textbox is disabled

$(document).ready(function() {
    $('input[type=text]:enabled:first').focus();
});

If you first textbox is not visible

$(document).ready(function() {
    $('input[type=text]:visible:first').focus();
});

Thanks.

Read Excel file using c#.Net

In this article, I am going to show how to read Excel file using c#. To achieve that we are going to use
Excel Data Reader (http://exceldatareader.codeplex.com/) DLL.
To get Excel Data Reader DLL to your project use NuGet
readexcel
Code:

public void BtnUploadClick(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            uploadPath = Server.MapPath("~/Excel/");            
                if (FileUpload1.HasFile)
                {
                    String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                    String validExt = sAllowedExt;
                    if (validExt.IndexOf("," + fileExtension + ",") != -1)
                    { 
                        if (FileUpload1.FileBytes.Length <= maxsize)
                        {
                            filePath = uploadPath + FileUpload1.FileName; 
                            FileUpload1.PostedFile.SaveAs(filePath); 
                            Response.Write("File uploaded successfully.");
 
                            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                            IExcelDataReader excelReader;
                            if (filePath.Contains(".xlsx"))
                            {
                                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 
                            }  //...
                            else
                            {
                                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                            } //...
                            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                            //DataSet result = excelReader.AsDataSet();
                            //...
                            //4. DataSet - Create column names from first row
                            excelReader.IsFirstRowAsColumnNames = true;
                            DataSet result = excelReader.AsDataSet();
                            //5. Data Reader methods
                            //foreach (DataRow item in result.Tables[0].Rows)
                            //{
                            //    //item[1].ToString();
                            //}
                            GridView1.DataSource = result;
                            GridView1.DataBind();
                        }
                        else
                        {
                            Response.Write("File size exceeded the permitted limit.");
                            return;
                        }
                    }
                    else
                    {
                        Response.Write("This file type is not accepted.");
                        return;
                    }      
                }
        }

Thanks

Download

[wpdm_file id=4]

Import XML file to Database Table

In this article, I talked about how to parse XML or import XML to SQL Server Database Table.
XML File:

<br />
<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders><br />

To Query XML File we need to store in to xml variable @MyXML:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />

Now we use SQL query to get Order header from @MyXML:

<br />
SELECT Col.value('@OrderNumber', 'int') AS 'Order',<br />
Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
FROM @MyXML.nodes('/Orders/Order') AS T(Col)<br />

Now we use SQL query to get Order Items from @MyXML:

<br />
SELECT<br />
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,<br />
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

Full Code:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />
   SELECT<br />
    Col.value('@OrderNumber', 'int') AS 'Order',<br />
    Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)</p>
<p>   SELECT<br />
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,<br />
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

XML_To_DB

Thanks

Using REPLACE in an UPDATE statement

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
Normally, doing a search and replace in SQL is not radically difficult. You basically do an update using the replace() function. For example:

  Update Product
  Set Description = replace(Description, 'old text is this', 'new text will be this')    
  where Description like '%old text is this%' 

[ad#post]
However, if the Description column in your table is a ntext field, the above code is going to error out. The correct way around this is to cast() the column as a maximum-sized varchar() type. So the above will now look like the following:

    Update Product   
    Set Description = replace(cast(Description as varchar(8000)), 'old text is this', 'new text will be this')   
    where Description like ('%old text is this%') 

Thanks