Get Organization Connection string

Hi,
Here is the example to get Organization connection string.

//get connection to mscrm_config DB
string connectionString = "Data Source="ServerName;Initial Catalog=MSCRM_CONFIG;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
//Select DB details for specific organization
string sqlString = string.Format("select databasename,* from organization with (nolock) where uniquename = '{0}'", organizationName);
SqlCommand command = new SqlCommand(sqlString, conn);
SqlDataReader reader = command.ExecuteReader();
if (reader == null)
return null;
if (!reader.Read())
return null;
object rslt = reader.GetValue(0);
//return organization connection string
return rslt;

Enjoy,

Rami heleg

Fetch or Retrieve?

Fetch or Retrieve?

The million dollars question what is better using fetch or RetrieveMultiple...

RetrieveMultiple faster than the Fetch method Retrieve doesn’t have to parse the query.

My tip:

Work direcly with CRM DB for Queries and update.

Important:
Use “nolock” for select command.
in this case database doesn’t lock the table
Use “rowlock” to update table lock only the specific record.

Example for select command:

Select firstname, lastname from contact with (nolock)

Example for update command:

Update incidentextensionbase WITH(rowlock) set new_firstname = ‘aaa’ where incidentid = ‘{fdkjdf98438e9w8euew987}

Enjoy,
Rami Heleg

Create custom page with CRM style

Hi,
here is an rxample to create your cutsom page like crm page.

The final page:




how to do it?
1. create new css file name: crmcs.css

paste start:

formButton
{
background-color: #6699cc;
padding: 2px 4px 3px 4px;
color: #000000;
font-size: 8pt;
font-family: tahoma;
height: 17px;
filter: progid:DXImageTransform.Microsoft.Gradient(GradientType=0, StartColorStr=#B4C5DF, EndColorStr=#91A9D0);
border-width: 0px;
}


TEXTAREA
{
font-size: 8pt;
font-family: tahoma;
width: 100%;
height: 100%;
border: 1px solid #6699CC;
}

.inputfields
{
font-size: 8pt;
font-family: tahoma;
width: 100%;
height: 19px;
border: 1px solid #6699CC;
}

INPUT.rad
{
width: 15px;
border: 0px;
cursor: hand;
}

DIV.tab
{
overflow-y: auto;
padding: 10px;
}

TD.sec
{
width: 100%;
color: #000000;
font-weight: bold;
padding-left: 0px;
padding-bottom: 2px;
text-overflow: ellipsis;
overflow: hidden;
}

TD
{
font-size: 11pt;
font-family: tahoma;
}

TD.bar
{
border-bottom: 1px solid #96B3DD;
}


SPAN.req
{
font-weight: bold;
color: #FF0000;
overflow: hidden;
text-overflow: ellipsis;
padding-top: 5px;
}

TD.statusBar
{
background-color: #80ABD4;
color: #000000;
background-image: url(images/statusbar_back.gif);
background-repeat: repeat-x;
padding-left: 5px;
height: 24px;
border-bottom: 1px solid #485673;
font-weight: bold;
}


LABEL
{
cursor: hand;
}

TD.radioLabel
{
padding-left: 2px;
padding-right: 10px;
}

TABLE.layout
{
table-layout: fixed;
width: 100%;
height: 100%;
}

div.tab
{
width: 100%;
height: 100%;
border: 1px solid #898C95;
background-color: #EAF3FF;
display: none;
}

body
{
font-size: 11px;
margin: 0px;
border: 0px;
background-image: url(images/form_back.gif);
background-repeat: repeat-x;
background-color: #acc0e9;
cursor: default;
}

td
{
font-size: 11px;
}

table
{
cursor: default;
}

a
{
color: #0000ff;
font-weight: bold;
}

.menubar
{
height: 44px;
}

.formtitle
{
padding-left: 10px;
}

TD.entitytype
{
font-weight: bold;
font-size: 11px;
padding-top: 2px;
}

TD.navtitle
{
font-weight: bold;
font-size: 18px;
padding-top: 1px;
color: #6699CC;
}
span.menu
{
height: 100%;
padding: 2px;
padding-left: 5px;
padding-right: 5px;
border: 1px solid #7288AC;
}

table.mnuBar
{
color: #000000;
height: 44px;
width: 100%;
background-image: url(images/form_toolbar_back.gif);
background-repeat: repeat-x;
}
paste end


Create new page ExamplePage.htm

paste start:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; CHARSET=UTF-8" />
<link rel="stylesheet" type="text/css" href="crmcs.css">
<title>Rami example</title>
</head>
<body>
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr class="menubar">
<td>
<table class="mnuBar" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td />
</tr>
</tbody>
</table>
<table class="formtitle" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td class="entitytype" nowrap="nowrap">
Contact: Rami Heleg
</td>
</tr>
<tr>
<td class="navtitle">
Update contact
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="padding: 10px;">
<table cellspacing="0" cellpadding="0" width="100%" height="100%">
<tbody>
<tr>
<td>
<div style="display: inline" class="tab">
<table style="table-layout: fixed" cellspacing="0" cellpadding="3" width="100%" height="100%">
<colgroup>
<col width="90">
<col>
<col width="10">
<col width="90">
<col>
</colgroup>
<tbody>
<tr>
<td>
Full Name
<span class="req">*</span>
</td>
<td colspan="2">
<input id="fullname" class="inputfields" tabindex="1" maxlength="200" name="fullname"
req="1">
</td>
</tr>
<tr>
<td class="sec bar" colspan="5">
Section 1
</td>
</tr>
<tr height="5">
<td>
</td>
</tr>
<tr>
<td>
First Name
</td>
<td>
<input id="firstname" class="inputfields" tabindex="8" maxlength="250" name="category">
</td>
<td>
</td>
<td>
Last Name
</td>
<td>
<input id="lastnane" class="inputfields" tabindex="9" maxlength="250" name="subcategory">
</td>
</tr>

<tr>
<td class="sec bar" colspan="5">
Section 2
</td>
</tr>
<tr height="5">
<td colspan="5">
</td>
</tr>
<tr>
<td>
Sex
</td>
<td colspan="4">
<table cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<input id="crmFormdonotemail0" class="rad" tabindex="200" value="0" type="radio"
name="crmFormdonotemail">
</td>
<td class="radioLabel">
<label for="crmFormdonotemail0">
Male</label>
</td>
<td>
<input id="crmFormdonotemail1" class="rad" tabindex="201" value="1" type="radio"
name="crmFormdonotemail">
</td>
<td class="radioLabel">
<label for="crmFormdonotemail1">
Female
</label>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td>
Country
</td>
<td>
<select style="width: 100%; font-family: tahoma,
Arial; font-size: 11px" class="selectBox">
<option selected>Isreal</option>
<option>USA</option>
</select>
</td>
<td>
</td>
<td>
Status
</td>
<td>
<select style="width: 100%; font-family: tahoma,
Arial; font-size: 11px" class="selectBox">
<option selected>Married</option>
<option>Single</option>
<option>Divorced</option>
</select>
</td>
</tr>
<tr>
<td>
Description
</td>
<td height="100%" colspan="4">
<textarea id="description" tabindex="2" name="description" maxlength="2000"></textarea>
</td>
</tr>
</tbody>
</table>
</div>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr height="23">
<td class="statusbar" colspan="2">
<b>Status:</b> Vip Contact
</td>
</tr>
</tbody>
</table>
</body>
</html>

paste End:

Enjoy,
Rami Heleg

How to change connection string for organization in CRM 4.0

Hi,
I want to change the connection string, What should i do?

The solution is very simple...

1. Open database MSCRM_CONFIG, table Organization.
2. This table contains information for all organization.
3. change in column ConnectionString to the new organization connection string.
4. reset CRM .

example:



BTW changing the register ( regedit) doesn't change the connection string.
it was relevant only for CRM 3.0

Enjoy,
Rami Heleg.

Update Method using Java Script

Hi,
Here is an example to update entoty request only in Client Side

// Prepare variables for updating a contact.
var contactId = "56914948-991C-DD11-AD3A-0003FF9EE217";
var newAddressLine1 = "34 Market St.";
var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+
authenticationHeader+
"<soap:Body>"+
"<Update xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+
"<entity xsi:type='contact'>"+
"<address1_line1>"+newAddressLine1+"</address1_line1>"+
"<contactid>"+contactId+"</contactid>"+
"</entity>"+
"</Update>"+
"</soap:Body>"+
"</soap:Envelope>";
// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Update");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
alert(msg);
}
// Display a confirmation message and open the updated contact.
else
{
alert("Contact with id = "+contactId+" successfully updated.");
window.open("/sfa/conts/edit.aspx?id={"+contactId+"}");
}

Enjoy,
Rami Heleg

RetrieveMultiple Method Using Java Script

Hi,
Here is an example to RetrieveMultiple only in Client Side

// Prepare variables to retrieve the contacts.
var searchCity = "Sammamish";
var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+
authenticationHeader+
"<soap:Body>"+
"<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+
"<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query'"+
" xsi:type='q1:QueryExpression'>"+
"<q1:EntityName>contact</q1:EntityName>"+
"<q1:ColumnSet xsi:type='q1:ColumnSet'>"+
"<q1:Attributes>"+
"<q1:Attribute>fullname</q1:Attribute>"+
"<q1:Attribute>contactid</q1:Attribute>"+
"</q1:Attributes>"+
"</q1:ColumnSet>"+
"<q1:Distinct>false</q1:Distinct>"+
"<q1:Criteria>"+
"<q1:FilterOperator>And</q1:FilterOperator>"+
"<q1:Conditions>"+
"<q1:Condition>"+
"<q1:AttributeName>address1_city</q1:AttributeName>"+
"<q1:Operator>Like</q1:Operator>"+
"<q1:Values>"+
"<q1:Value xsi:type='xsd:string'>"+searchCity+"</q1:Value>"+
"</q1:Values>"+
"</q1:Condition>"+
"</q1:Conditions>"+
"</q1:Criteria>"+
"</query>"+
"</RetrieveMultiple>"+
"</soap:Body>"+
"</soap:Envelope>";
// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
alert(msg);
}
Enjoy,
Rami Heleg

Retrieve Method Using JScript

Hi,
Here is an example to retrieve data only in Client Side

// Prepare variables for a contact to retrieve.
var contactid = "4696f8cb-9a1c-dd11-ad3a-0003ff9ee217";
var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+
authenticationHeader+
"<soap:Body>"+
"<Retrieve xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+
"<entityName>contact</entityName>"+
"<id>"+contactid+"</id>"+
"<columnSet xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:ColumnSet'>"+
"<q1:Attributes>"+
"<q1:Attribute>fullname</q1:Attribute>"+
"</q1:Attributes>"+
"</columnSet>"+
"</Retrieve>"+
"</soap:Body>"+
"</soap:Envelope>";
// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Retrieve");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
alert(msg);
}
// Display the retrieved value.
else
{
alert(resultXml.selectSingleNode("//q1:fullname").nodeTypedValue);
}
The following is an example of a successful response:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<RetrieveResponse xmlns="http://schemas.microsoft.com/crm/2007/WebServices">
<RetrieveResult xmlns:q1="http://schemas.microsoft.com/crm/2007/WebServices" xsi:type="q1:contact">
<q1:fullname>Jesper Aaberg</q1:fullname>
<q1:contactid>{4696F8CB-9A1C-DD11-AD3A-0003FF9EE217}</q1:contactid>
<q1:owningbusinessunit>{0EA35030-3EC9-DC11-A8D2-0003FF9EE217}</q1:owningbusinessunit>
</RetrieveResult>
</RetrieveResponse>
</soap:Body>
</soap:Envelope>

Enjoy,
Rami Heleg