Friday, August 9, 2013

Query to update multiple rows with particular data/string in SQL

update tablename set columnname=REPLACE(tablename.columnname,'string to search','string to replace')

Consider the below table with name 'Employee'
ID Name
1 sa
2 sa krishna
3 sa kumar
4 sa ganesh

After executing the update statement
update Employee set Name=REPLACE(Employee.Name,'sa','sai')  
then the final result will be
ID Name
1 sai
2 sai krishna
3 sai kumar
4 sai ganesh

Monday, July 22, 2013

Send Email to multiple receipts individually.

SmtpClient smtpclient = new SmtpClient();
            System.Net.Mail.MailMessage mailMsg = new System.Net.Mail.MailMessage();
            MailAddress mailAddress = new MailAddress("EMailID", "Admin", 
System.Text.Encoding.UTF8);
            mailMsg.From = mailAddress;
            if (dtemail.Rows.Count > 0)
            {
                StringBuilder br = new StringBuilder();
                for (int i = 0; i < dtemail.Rows.Count; i++)
                {
                    string strTO = dtemail.Rows[i][0].ToString();
                    br.Append(dtemail.Rows[i][0].ToString()+";");
                }
                mailMsg.IsBodyHtml = true;
                mailMsg.Subject = Session["Subject"].ToString();
                mailMsg.Body = "<html><body><table><tr><td>" + Session["DecodeOriginal"] + "</td></tr></table></body></html>";
                smtpclient.Credentials = new System.Net.NetworkCredential("EMailID", "password");
                smtpclient.Host = "smtp.gmail.com";
                smtpclient.Port = 25;
                smtpclient.EnableSsl = true;
                string strtoaddress = br.ToString();
                string[] Addresses = strtoaddress.Split(';');
                foreach (string address in Addresses)
                {
                    if (address != "")
                    {
                        mailMsg.To.Add(new System.Net.Mail.MailAddress(address));
                        smtpclient.Send(mailMsg);
                        mailMsg.To.Clear();
                    }
                }
                Response.Write(@"<script language='javascript'>alert('Your Email has been sent successfully - Thank You.');</script>");
                btnSendMail.Visible = false;
            }
            else
            {
                Response.Write(@"<script language='javascript'>alert('Please select Email-ID.');</script>");
            }

Monday, July 15, 2013

Plugin for SetState Message

In MS CRM 2011 we have  a  common requirement  for plugins setstate message i.e  when the  status  of  a  record changes (like activate/deactivated change of a  record etc.)
 So, here we  will look at some  plugin codes which executes to  update certain field when a record state changes ie activated/deactivated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages;

namespace SetState
{
   public class SetState:IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));        
            try
            {
                if (context.InputParameters.Contains("EntityMoniker") )
                {
                    // Work with the Moniker
                    var targetEntity = (EntityReference)context.InputParameters["EntityMoniker"];
                    if (targetEntity.LogicalName != "new_activeacc")
                    { return; }
                    IOrganizationService service = factory.CreateOrganizationService(context.UserId);
                    Entity ac = service.Retrieve("new_activeacc", context.PrimaryEntityId, new ColumnSet(true));
                    ac.Attributes["new_checkamt"] = 0;
                    service.Update(ac);
                }
            }
            catch(Exception e)
            {
                throw new InvalidPluginExecutionException("An error occured for SetState plugin "+e.Message + e.InnerException);
            }            
          }
    }
}

Till now, the plugin code is perfect.
But we need to take some points into consideration while registering the plugin for setstate message

For the  above plugin to work we need to register the plugin for  both setstate and setstatedynamic message.
The reason why both messages are required is both messages perform the same action in CRM and  as there is no typical thumbrule  for which action setstate/setstatedynamic  mesage is  fired,its better to register the plugin for  both messages  so that  our plugin works properly.

Another Example
Here is a sample plugin that would prevent an inactive contact record from getting activated.

Plugin has been registered against contact entity and two steps have been registered one on setstate and other on setstatedynamicentity messages on Pre event.
public void Execute(IPluginExecutionContext context)
{
// In case of SetState and SetStateDynamicEntity message InputParameter
// would contain EntityMoniker parameter 
Moniker entity = null;
if (context.InputParameters.Properties.Contains("EntityMoniker") &&
context.InputParameters.Properties["EntityMoniker"] is Moniker)
{
entity = (Moniker)context.InputParameters.Properties["EntityMoniker"];
// Get the state to which record is to be changed
// If Active the record is being activated 
// If Inactive the record is being deactivated
string  state=
(string)context.InputParameters.Properties[ParameterName.State];
// Verify that the entity represents an account.
if (entity.Name == EntityName.contact.ToString() && state=="Active")
{
    throw new InvalidPluginExecutionException("Record can't be activated");
}
}

}

Thursday, June 20, 2013

Transfer data from one database to another database using scripts

Option 1 
Right click on the database you want to copy
Choose 'Tasks' > 'Generate scripts'
'Select specific database objects'
Check 'Tables' 
Mark 'Save to new query window'
Click 'Advanced'
Set 'Types of data to script' to 'Schema and data' 
Next, Next
You can now run the generated query on the new database.

Option 2
Right click on the database you want to copy
'Tasks' > 'Export Data'
Next, Next
Choose the database to copy the tables to
Mark 'Copy data from one or more tables or views'
Choose the tables you want to copy
Finish

To copy the Schema:
Right click on the database you want to copy
Choose 'Tasks' > 'Generate scripts'
'Select specific database objects' click Next,Next....

Wednesday, June 12, 2013

To find your PF Balance just click the below link

http://members.epfoservices.in/

JSON

Characterstics of JSON
Easy to read and write JSON.
Lightweight text based interchange format
Language independent.

Uses of JSON
It is used when writing JavaScript based application which includes browser extension and websites.
JSON format is used for serializing & transmitting structured data over network connection.
This is primarily used to transmit data between server and web application.
Web Services and API.s use JSON format to provide public data.
It can be used with modern programming languages.

JSON supports following two data structures:
Collection of name/value pairs: This Data Structure is supported by different programming language.
Ordered list of values: It includes array, list, vector or sequence etc.
There are following datatypes supported by JSON format:
Type Description
Number double- precision floating-point format in JavaScript
String double-quoted Unicode with backslash escaping
Boolean true or false
Array an ordered sequence of values
Value it can be a string, a number, true or false, null etc
Object an unordered collection of key:value pairs
Whitespace can be used between any pair of tokens
null empty
Simple Example in JSON
Example shows Books information stored using JSON considering language of books and there editions:
{
    "book": [
    {
       "id":"02",
       "language": "CRM",
       "edition": "4.0",
       "author": "Sai Krishna"
    },
    {
       "id":"05",
       "language": "CRM",
       "edition": "2011"
       "author": "Sai"
    }]
}