Nishant Rana

Creating and Calling ASP.NET AJAX Web Service

Hi,

First we will create a new ASP.NET Ajax web site(Visual Studio 2005)
or ASP.NET web site (version 3.5) if we are using Orcas(Visual studio 2008)

Through Add New Item, add a new web service WebService.asmx in the website.

1) Add this in webservice.cs

using System.Web.Script.Services;

2) Add a ScriptService attribute to our webservice class

[ScriptService]
[WebService(Namespace = “http://tempuri.org/”)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

3) We’ll use the default Hello World web service. So just save the web service and view it in browser to test it.

Now change the url of the webservice in browser and append “/js” to it
i.e.
Change
http://d-0824/AjaxinAction/WebService.asmx
to
http://d-0824/AjaxinAction/WebService.asmx/js

Press enter it will ask you to save the file,
just save and open it in notepad

We will see something like this

var WebService=function() {
WebService.initializeBase(this);
this._timeout = 0;
this._userContext = null;
this._succeeded = null;
this._failed = null;
}
WebService.prototype={
_get_path:function() {
…………………………
……………………….

This is a javascript proxy class which the our client side ajax library will use to make call to our web service.

4) Add a script manager control to our default.aspx page ( if not already there), and add the reference to our web service

<asp:ScriptManager ID=”ScriptManager1″ runat=”server”>
<Services>
<asp:ServiceReference Path=”WebService.asmx” />
</Services>
</asp:ScriptManager>
5) Now add a simple html text control on the default.aspx page. We will be calling our web service and fill the text box value returned from the webservce (“Hello World”).

6) Put this script on your default.aspx page

<script type=”text/javascript”>

function pageLoad()
{
WebService.HelloWorld(onSuccess);
}

function onSuccess(result)
{
$get(“Text1”).value=result;
}

</script>

7) Now just view the page in browser we should see the textbox filled with “Hello World” string returned from our web service.

8) Or if we want to call it in the click of the button. add a html button control.

9) Replace the above script with this one

function btn_click(sender,e)
{
WebService.HelloWorld(onSuccess);
}

function onSuccess(result)
{
$get(“Text1”).value=result;
}
</script>

In the above example we saw a client centric approach, the same thing can be done very easily by making use of server centric approach in ASP.NET AJAX framework.

https://nishantrana.wordpress.com/2007/11/06/creating-a-simple-hello-world-aspnet-ajax-web-page/

Bye

Creating Word document using C#

Create a new windows application project and add a button to it.

On click of that button, we will create a new document(word) and write a simple Hello World text in it.

To create a word document using C# we need to first reference the following DLL(com)

DLL

After adding reference, add this directive

using Microsoft.Office.Interop.Word;

Put this code on button click

private void button1_Click(object sender, EventArgs e)

{

object missing = System.Reflection.Missing.Value;

object Visible=true;

object start1 = 0;

object end1 = 0;

ApplicationClass WordApp = new ApplicationClass();

Document adoc = WordApp.Documents.Add(ref missing, ref missing, ref missing, ref missing);

Range rng = adoc.Range(ref start1, ref missing);

try

{

rng.Font.Name = “Georgia”;

rng.InsertAfter(“Hello World!”);

object filename = @”D:\MyWord.doc”;

adoc.SaveAs(ref filename, ref missing, ref missing, ref missing, ref missing, ref missing,

ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

WordApp.Visible = true;

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

The easiest way to write code for office interoperability is to make use of VBA code.

Say you want to insert a picture in a word document what you can do is

open the word document – Go to Tools ->Macro-> Record New Macro

Now click on insert menu and insert the picture. Stop the recording, again go to Macro -Macros-> Select your Macro and click on edit

You will find the vba code over there

Sub Macro1()

Selection.InlineShapes.AddPicture FileName:= _
“C:\Documents and Settings\nishantr1\My Documents\My Pictures\untitled.bmp” _
, LinkToFile:=False, SaveWithDocument:=True
End Sub

Now to write the same code in c# you will do something like this

Range rngPic = adoc.Tables[1].Range;

rngPic.InlineShapes.AddPicture(@”C:\anne_hathaway.jpg”, ref missing, ref missing, ref missing);

Bye

Saving and Retrieving richtextbox control’s content (.rtf file) in database

Let’s start with a very simple application.

Form

Drop OpenFileDialog control in the application.

When user click on Pick File button using OpenFileDialog he will select a rtf file to display in richtextbox control.

The content of richtextbox control will be saved in the sql server table.

This is how our table looks like.

Table

File content is the image data type column where we will store our rtf content.

<!–[if gte vml 1]> <![endif]–>

Put the following code in Pick file button click.

if(openFileDialog1.ShowDialog() == DialogResult.OK)

{

try

{

rtfContent.LoadFile(openFileDialog1.FileName);

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

Put the following code in Save Button Click

SqlConnection conn=new SqlConnection(“Data Source=D-0824;Initial Catalog=WordInterOp; uid=sa;pwd=sa;”);

SqlCommand cmd=new SqlCommand();

cmd.CommandText = “Insert into RtfStore (fileName,fileContent) values ( @fname,@fcontent)”;

cmd.Connection=conn;

SqlParameter fileName=new SqlParameter(“@fname”,openFileDialog1.SafeFileName);

rtfContent.SaveFile(@”c:\temp.rtf”, RichTextBoxStreamType.RichText);

FileStream stream = new FileStream(@”c:\temp.rtf”, FileMode.Open, FileAccess.Read);

int size = Convert.ToInt32(stream.Length);

Byte[] rtf = new Byte[size];

stream.Read(rtf, 0, size);

SqlParameter fileContent=new SqlParameter();

fileContent.ParameterName=”@fcontent”;

fileContent.SqlDbType=SqlDbType.Image;

fileContent.Size=rtf.Length;

fileContent.Value=rtf;

cmd.Parameters.Add(fileName);

cmd.Parameters.Add(fileContent);

conn.Open();

int success=cmd.ExecuteNonQuery();

if(success==1)

{

MessageBox.Show(“Entered data successfully”);

}

openFileDialog1.SafeFileName- To get the name of file instead of the complete path.

rtfContent.SaveFile- Saving the content of the control in a rtf file.

RichTextBoxStreamType.RichText– This tells that we are saving richtext having some formatting and not just plain text.

Finally through filestream we are reading that file and saving it’s content in our sqlserver table.

Similary to read the rtf content from the database and display it in Richtextbox control, this is what we need to do

// cmd.commandText=”select fileContent from RtfStore where filename=’xyz’ “;

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

Byte[] rtf = new Byte[Convert.ToInt32((dr.GetBytes(0, 0,

null, 0, Int32.MaxValue)))];

long bytesReceived = dr.GetBytes(0, 0, rtf, 0, rtf.Length);

ASCIIEncoding encoding = new ASCIIEncoding();

rtfContent.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));

}

Bye

Understanding Normalizations in Database(RDBMS)

Let’s try understanding normalization

How many normal forms are there?

There are seven normal forms.

They are

First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal Form
Sixth or Domain-key Normal form

Why do we need to do normalization?

To eliminate redundancy of data i.e. having same information stored at multiple places, which eventually be difficult to maintain and will also increase the size of our database.

With normalization we will have tables with fewer columns which will make data retrieval and insert, update and delete operations more efficient.

What do we mean when we say a table is not in normalized form?

Let’s take an example to understand this,

Say I want to create a database which stores my friends name and their top three favorite artists.

This database would be quite a simple so initially I’ll be having only one table in it say friends table. Here FID is the primary key.

FID	FNAME	FavoriteArtist
1	Srihari	Akon, The Corrs, Robbie Williams.
2	Arvind	Enigma, Chicane, Shania Twain

This table is not in normal form why?

FavoriteArtist column is not atomic or doesn’t have scalar value i.e. it has having more that one value.

Let’s modify this table

FID	FNAME	FavoriteArtist1	FavoriteArtist2	FavoriteArtist3
1	Srihari	Akon.	The Corrs	Robbie Williams.
2	Arvind	Enigma	Chicane	Shania Twain

This table is also not in normal form why?

We have now changed our table and now each column has only one value!! (So what’s left?)

Because here we are having multiple columns with same kind of value.

I.e. repeating group of data or repeating columns.

So what we need to do to make it normal or at least bring it in First Normal Form?

We’ll first break our single table into two.
Each table should have information about only one entity so it would be nice if we store our friend’s information in one table and his favorite artists’ information in another

(For simplicity we are working with few columns but in real world scenario there could be column like friend’s phone no, email , address and favorites artists albums, awards received by them, country etc. So in that case having two different tables would make complete sense)

FID	FNAME
1	Srihari
2	Arvind

FID	Favorite Artist
1	Akon.
1	The Corrs
1	Robbie Williams
2	Enigma
2	Chicane
2	Shania Twain

FID foreign key in FavoriteArtist table which refers to FID in our Friends Table.

Now we can say that our table is in first normal form.

Remember For First Normal Form

Column values should be atomic, scalar or should be holding single value

No repetition of information or values in multiple columns.

So what does Second Normal Form means?

For second normal form our database should already be in first normal form and every non-key column must depend on entire primary key.

Here we can say that our Friend database was already in second normal form l.

Why?

Because we don’t have composite primary key in our friends and favorite artists table.

Composite primary keys are- primary keys made up of more than one column. But there is no such thing in our database.

But still let’s try to understand second normal form with another example

This is our new table

Gadgets	Supplier	Cost	Supplier Address
Headphone	Abaci	123$	New York
Mp3 Player	Sagas	250$	California
Headphone	Mayas	100$	London

In about table ITEM+SUPPLIER together form a composite primary key.
Let’s check for dependency

If I know gadget can I know the cost?

No same gadget is provided my different supplier at different rate.

If I know supplier can I know about the cost?

No because same supplier can provide me with different gadgets.

If I know both gadget and supplier can I know cost?

Yes than we can.

So cost is fully dependent (functionally dependent) on our composite primary key (Gadgets+Supplier)

Let’s start with another non-key column Supplier Address.

If I know gadget will I come to know about supplier address?

Obviously no.

If I know who the supplier is can I have it address?

Yes.

So here supplier is not completely dependent on (partial dependent) on our composite primary key (Gadgets+Supplier).

This table is surely not in Second Normal Form.

So what do we need to do to bring it in second normal form?
Here again we’ll break the table in two.

Gadgets	Supplier	Cost
Headphone	Abaci	123$
Mp3 Player	Sagas	250$
Headphone	Mayas	100$

Supplier	Supplier Address
Abaci	New York
Sagas	California
Mayas	London

We now how to normalize till second normal form.

But let’s take a break over here and learn some definitions and terms.

Composite Key: -Composite key is a primary key composed of multiple columns.

Functional Dependency – When value of one column is dependent on another column.

So that if value of one column changes the value of other column changes as well.

e.g. Supplier Address is functionally dependent on supplier name. If supplier’s name is changed in a record we need to change the supplier address as well.

S.Supplier–àS.SupplierAddress

“In our s table supplier address column is functionally dependent on the supplier column”

Partial Functional Dependency – A non-key column is dependent on some, but not all the columns in a composite primary key.

In our above example Supplier Address was partially dependent on our composite key columns (Gadgets+Supplier).

Transitive Dependency– A transitive dependency is a type of functional dependency in which the value in a non-key column is determined by the value in another non-key column.

With these definitions in mind let’s move to Third Normal Form.

For a table in third normal form

It should already be in Second Normal Form.
There should be no transitive dependency, i.e. we shouldn’t have any non-key column depending on any other non-key column.

Again we need to make sure that the non-key columns depend upon the primary key and not on any other non-key column.

Album	Artist	No. of tracks	Country
Come on over	Shania Twain	11	Canada
History	Michael Jackson	15	USA
Up	Shania Twain	11	Canada
MCMXC A.D.	Enigma	8	Spain
The cross of changes	Enigma	10	Spain

Although the above table looks fine but still there is something in it because of which we will normalize it further.

Album is the primary key of the above table.

Artist and No. of tracks are functionally dependent on the Album(primary key).

But can we say the same of Country as well?

In the above table Country value is getting repeated because of artist.

So in our above table Country column is depended on Artist column which is a non-key column.

So we will move that information in another table and could save table from redundancy i.e. repeating values of Country column.

Album	Artist	No. of tracks
Come on over	Shania Twain	11
History	Michael Jackson	15
Up	Shania Twain	11
MCMXC A.D.	Enigma	8
The cross of changes	Enigma	10

Artist	Country
Shania Twain	Canada
Michael Jackson	USA
Enigma	Spain

Normally this is considered enough and we don’t really go on applying the other normal forms.

Most of real-world application has databases which are in third normal forms.

Bye

References:-

Head First SQL,

Murach Sql for Sql server,

http://www.stuart.edu/courses/im510/database/2NF.htm

Using Dhtml inside CRM

Hi,

We can make use of Dhtml inside our CRM onLoad, onSave and onChange javascripts.

Using Dhtml, number of possible ways of customizing CRM increases.

But the only thing with them is that they are unsupported.

Just for fun try putting this code in form load of your contact form and see what it does.

window.status=”Hello CrmUser!”;
window.resizeTo(600,600);
function G_GlowField()
{
event.srcElement.runtimeStyle.backgroundColor=”Yellow”;
var p=window.createPopup()
var pbody=p.document.body
pbody.style.backgroundColor=”lime”
pbody.style.border=”solid black 1px”
pbody.innerHTML=”Mandatory Field”
p.show(150,150,200,50,document.body)

}
function G_RevertField()
{
event.srcElement.runtimeStyle.backgroundColor=””;
}
crmForm.all.firstname.attachEvent(“onmouseover”,G_GlowField);
crmForm.all.firstname.attachEvent(“onmouseout”,G_RevertField);
crmForm.all.firstname.attachEvent(“onmousedown”,G_RevertField);
Or Better check this link

Using the attachEvent method to show users context sensitive help

Bye

Link Entity, Query Expression and FetchXml Wizard

Let say we need to make use of CrmService to give us the name of the opportunity where owner’s fullname=’someName’;

Name is an attribute of Opportunity Entity.
FullName is an attribute of SystemUser Entity.

So we need to make use of LinkEntity class over here to link from opportunity entity to systemuser entity.

Now let’s use queryExpression for the same.

But plzzzz don’t write it yourself, use FetchXmlBuilder to write it for you.
Plzz understand it and download it from this link

http://www.stunnware.com/crm2/topic.aspx?id=FindingData6
(The best site for all the CrmDevelopers)

This is how the queryExpression code will look like

QueryExpression query = new QueryExpression();

query.EntityName = “opportunity”;

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { “name” };
query.ColumnSet = columns;

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = “opportunity”;
linkEntity1.LinkFromAttributeName = “owninguser”;
linkEntity1.LinkToEntityName = “systemuser”;
linkEntity1.LinkToAttributeName = “systemuserid”;

linkEntity1.LinkCriteria = new FilterExpression();
linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “fullname”;
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { “Nishant Rana” };

linkEntity1.LinkCriteria.Conditions = new ConditionExpression[] { condition1 };

query.LinkEntities = new LinkEntity[] { linkEntity1 };

Now say we have a requirement where we need

The name of all the opportunity along with the fullname of the owner.

Let’s try doing it with a queryExpression class and using the FetchXmlBuilder.

This is the code which we’ll get

………….

LinkEntity linkEntity1 = new LinkEntity();

linkEntity1.JoinOperator = JoinOperator.Natural;

linkEntity1.LinkFromEntityName = “opportunity”;

linkEntity1.LinkFromAttributeName = “owninguser”;

linkEntity1.LinkToEntityName = “systemuser”;

linkEntity1.LinkToAttributeName = “systemuserid”;

//You have specified columns for this link-entity. This is not supported in query expressions

This means it’s not possible using queryExpression

So in this case we need to make use of FetchXml to fetch use the fullname of the owning user.

The fetch xml looks like this

string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
</link-entity>
</entity>
</fetch>”;

// Finally the code

CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
</link-entity>
</entity>
</fetch>”;
String result = service.Fetch(fetchXml);

Now the only problem is we need to parse the xml on our own.

We can use something like this

if this is the result we are receiving

to fetch the new_id we can do this

XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlNodeList xnodlist=doc.GetElementsByTagName(“new_id”);
XmlNode xnodRoot=xnodlist.Item(0);
string val=xnodRoot.InnerText;

Bye.