Sunday, October 16, 2016

Physical Location of Records in SQL Server

The unit of data storage in SQL Server is the page.The disk space allocated to  mdf file in a database is logically divided into pages numbered contiguously from 0 to n and all disk I/O operations are performed at the page level.

The page size is 8 KB (8192 bytes). So SQL Server databases have 128 pages/MB.

Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page.

To do so, use the following undocumented T-SQL %%physloc%% virtual column:


SELECT *, %%physloc%% AS physloc
FROM Person.Address
ORDER BY physloc

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

FROM Person.AddressType 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

this query will provide the hexadecimal address in form of page_id,slot_id

However this system tabular function fn_PhysLocCracker is available only in 2008 and above.

Original Article:

Sunday, July 3, 2016

Creating custom object and consuming in webapi - Sales force

Creating custom object and consuming in webapi 

There are numbers of standard objects in Sales force. Depending on the requirement, sometime we might require to create some custom objects in sales force.

This article is all about "how we can create the custom objects in sales force and how we can consume them in webapi"

Custom Object: -

Custom objects are database tables that allow you to store data specific to your organisation in Salesforce. You can use custom objects to extend Salesforce functionality or to build new application functionality. 
Once you have created a custom object, you can create a custom tab, custom related lists, reports, and dashboards for users to interact with the custom object data. You can also access custom object data through the API.

Creating Custom Object:-

in
Move to [SetUp] >> Build >> Create >> Objects and click on "New Custom Object"

provide required details and choose from optional permission handing (i.e. enable note on record etc..) and click same

Custom objects named "MyCustomObject" is ready. You can note down the API Name for cutom objects as it will be required while querying on this objects from api.

Only following standard fields created when custom object get created 

  • CreatedBy
  • LastModifyBy
  • Name
  • Owner

Creating Custom fields:-

We had added new custom objects but that does have only standard fields yet but we require fields to store data. The user defined fields on a custom objected termed as custom fields in sales force. It is very easy to create simple field or look up field.

[Custom Fields & Relationship] >> [New]

Same we create a field/column in db, we have information 
  • what type of column it would be
  • what type on constraint can be applied on it
  • what will be length of column
  • it is be a look up column
very similar way sales force is asking detail while creating custom fields.because at the end it will be column in sales force db somewhere.

Choose data type and click next.
Set field level security in step 3 and last step show  basis detail of field , can set page layout.

View all Custom Objects:-

[SetUp] >> Build >> Create >> Objects

Performing read on custom object from web api:-

Code to access:-

namespace MyFirstSalesForceAPI.Controllers
    public class CustomSalesForceObjController : ApiController
        private static string _consumerKey = "3MVG9ZL0ppGP5UrB3oyPJP28HLRAP8yV0mp4qqcVRgbZ7EdGT96I0t6AHO22xth8C_R8tkPI0qo270C5eH9R7";//your consumer key
        private static string _consumerSecret = "2970133464515046181";//your key secret
        private static string _userName = "";

        private static string _securityToken = "N2fwK2ZEZBwH0blYMm4zYQptP";//your securitytoken
        private static string _password = "YourPassword" + _securityToken;

        // GET api/CustomObject/objectName
        public IEnumerable<dynamic> Get()
            var auth = new Salesforce.Common.AuthenticationClient();
            auth.UsernamePasswordAsync(_consumerKey, _consumerSecret, _userName, _password).Wait();

            var client = new ForceClient(auth.InstanceUrl, auth.AccessToken, auth.ApiVersion);
            Task<QueryResult<dynamic>> res = client.QueryAsync<dynamic>("Select Id,Name,MyFSFApp__Email__c, MyFSFApp__Mobile__c from  MyFSFApp__Student__c");

            return res.Result.Records.AsQueryable();


View in sales force


Saturday, May 28, 2016

Building WebAPI Using the Sales force Toolkit for .NET- PART 2 (Interaction with Sales force and ASP.NET WebAPI)

CRUD Operation in sales force's contact through ASP.NET WebAPI using sales force api.

if your client has apps on sales force cloud then something you may require deal with an /mobile application to interact with their sales force cloud.

This article will show you “How to perform CRUD with sales force api in”

Let's describe first the step involved:-
  • ·         Create a web api project.
  • ·         Add sales force nuget package.
  • ·         Add new api controller.
  • ·         Start developing get/add/edit/delete operation.
o   Get Operation: get all the contacts
o   Get a contact based on email id.
o   Add new contact.
o   Update contact.
o   Delete contact.

Create a web api project. >> Open visual studio 2010/2012/2013. And add empty solution. Thereafter add a web api project to it

Add sales force nuget package. >> DeveloperForce.Force

Add new API controller. >>

 Start developing get/add/edit/delete operation. >> Trying CRUD for contacts ,view your sales force cloud contact tab first as we are going to perform operations for same.

 Navigate to [Contacts]

Get Operation: get all the contacts. >>



Let us map result  to class object


Get contact based on email id. >>


Add new contact. >>


View New contact in sales force

Update contact detail. >> updating last name for contact in this example


View update last name in sales force

Delete contact. >>



Check in sales force                                                                                                                                                                                                  

compeleted crud operations for contacts similary can use same with little modification for leads opportunities, cutom reports etc..


How to perform CRUD with sales force api in

Building WebAPI Using the Sales force Toolkit for .NET- PART 1 (Interaction with Sales force and ASP.NET WebAPI)

Building WebAPI Using the Sales force Toolkit for .NET- PART I

This article is all about the setting up account so that can interact thought  salesforce’s api in outside applications.

 Part II of this article will perform the CRUD operation by web api  for contacts.

The Following are the steps:-

  • ·        Signup for sales force developer account.
  • ·         Sign-in.
  • ·         Update your profile.
  • ·         Set/reset security password.
  • ·         Create a connected app.
  • ·         Get consumer key and consumer secret

Signup for sales force developer account:- Register on and get a free 30 days account.
Open and click for “Free trial” and follow all steps

 Sign-in:- open and click login. Provide your user credentials

 Home screen

Update profile:- Navigate to [Your Name] > My Settings 
Set/Reset Security token:- Security token is required along with user password while interacting with api.
Navigate to [Your Name] > My Settings > Personal > Reset My Security Token

Create a connected app:-
Navigate to [Setup] > Apps > Connected app and click for new connected app. Fill the required detail to get key/secret. 

 click add for new connected app and fill all required details. It will accept only secure callback url.

Newly added app                                                                                                                                

Get consumer key /secret:-
Navigate to [Setup] > Apps > Connected app >Your connected app (say MyFirstSalesForceApp) and find key and secret, click on reveal to see consumer secret.

Now got salesforce ac username, password, security token, consumer key and consumer secret so can go for development of ASP.NET Web /api.