Sharepoint 11 - Javascript in sharepoint edit form(1)

During my work, in the sharepoint edit form, I want one field be composed from another 2 fields:

I have some explanation of why I did in this way instead of using Calculated field:
1. The purpose here is to build the client side scripts to get some field can be intelligent input. So the value shows on the page only and they are not stored in server side.
--username, currently the template is not easy to use and it jumps out a dialog and we may also change to intelligent input in this direction
2. Some fields are not avaliable to the calculated field such as look up fields, sometimes username et al.
3. We want to strictly get the values on form/page, retrieve from server may change the values.
4. In my following case, it provides a intelligent input. As long as the user don't change the scroll down selection items. The text will not changed. And the user can still changed the title as he want before storing the data by click "OK" button.



The step is:

Step 1: In the item editform display page, add 2 paramters to the URL: PageView=Shared&ToolPaneView=2

Step 2: Add the Content Editor Webparts

Step 3. I wrote code like:

< Script>
var e = document.getElementById('ctl00_m_g_c7e99c14_1b78_4b18_95db_a232d74a99f4_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_Lookup');
var txt1= e.options[e.selectedIndex].innerText;
e.onchange=function(){myfunc()};


var ee = document.getElementById('ctl00_m_g_c7e99c14_1b78_4b18_95db_a232d74a99f4_ctl00_ctl04_ctl02_ctl00_ctl00_ctl04_ctl00_DropDownChoice');
var txt2= ee.options[ee.selectedIndex].innerText;
ee.onchange=function(){myfunc()};


var tbox = document.getElementById('ctl00_m_g_c7e99c14_1b78_4b18_95db_a232d74a99f4_ctl00_ctl04_ctl00_ctl00_ctl00_ctl04_ctl00_ctl00_TextField');
if (tbox)
{
tbox.value = txt1+'('+txt2+')';
}

function myfunc()
{
txt1= e.options[e.selectedIndex].innerText;

txt2= ee.options[ee.selectedIndex].innerText;

tbox.value = txt1+'('+txt2+')';
}

< /Script>

There is little issue with above code, as innertext of IE and FF are use different functions. We need change here.

We can write the function like the below to fix the issue.

function getObjInnerText (obj)
{
return (obj.innerText) ? obj.innerText : (obj.textContent) ? obj.textContent : "";
}

WPF(1) Use of PageFunction

I just built a sample code here:

http://groups.google.com/group/jiezhu0815myfiles/web/PageFunctionExample.rar

I will do some explanation about this later.

Passing Parameters in Pages and Asp.net State Management

I am going to do summary about the state management and pass parameters between pages in asp.net.

1. Use PreviousPage Property:

For example we have a button the property: PostBackUrl navigate to a sencond page, or a HyperLink or the first page use the Server.Transfer() method to open the sencond page. We want to pass the parameters in the first page to the 2nd page.

We can use the 2nd page's PreviousPage property. (2nd page has a label with name mylbl, and first page has a textbox name mytxt).

Code pretty much like:

mylbl.Text=(TextBox)PreviousPage.FindControl("mytxt").Text.

sometimes we place textbox in a panel(or some controls implemented INamingContainer), forexample our id for pane is mypanel.

We can use pretty much like:

Panel p=(Panel)PreviousPage.FindControl("mypanel");
TextBox tbox=p.FindControl("mytxt");

sometimes we can writh the previous page property in the page declaration, like:

< %@ PreviousPageType VirtualPath="~/.....aspx" % >

Notice one thing: Response.Redirect("sencondpage.aspx"), the PreviousPage property doesn't work here.

There are some client Side state management techniques can do parameters passing:

The advantage of the client side state management techniques is it stores the data in client side which will get server not that heavy burdon, the disadvantage is security.

Some techniques of statemanagement in client side are:

ViewState: but notice ViewState cannot pass parameters in the different pages, it can only pass the parameters in the same page.
ControlState and Hidden Field, they are like ViewState and cannot pass parameters across pages.

But for client side techniques, we can use cookie and querystring to pass parameters between different pages:

2. Cookie:

Code pretty much like:

Response.Cookie["txt"].Value=mytxt.Text.ToString(); //notice cookie value is string

We can also define expiration time for cookie,
Response.Cookie["txt"].Expires=System.DateTime.Now.AddDays(1);

in the sencond page:

mylbl.Text=Request.Cookie["txt"].Value;

But there are many bad things about cookie such as security issues.

3. Use QueryString.

In the first page:

Response.Redirect("~/Page2.aspx?para1="+mylbl.Txt.ToString()+"¶2=....",

In the 2nd page just use:

Request.QueryString["para1"] ...

there are also problems with QueryString such as length limitation et al.

There are also some state management object in server side, such as Session, Application, Cache. The good thing about the server side state management object is secure.

They can all be used to pass parameters between pages.

4. Session.

just the first page Session["txt"]=mytextbox.Text;
sencond page mylbl=Session["txt"].ToString();

Notice that session object is stored in server side, but session ID is stored in cookie. sometimes we use cookieless setting, we can set the session ID in querystring.

Session state can also be stored InProc(by default in server side), it can also be stored in StateServer(we can see there is an windows service called ASP.NET State Service, it runs the aspnet_state.exe), and it can be stored in SQLServer.

Use ADO.net Transaction Object

Sometimes we need to use ADO.net Transaction object because if a sql command execute, it may fail and we don't want the application or database changes with error in halfway, and we want to set to the initial state before our sql command execute.

There are famous ACID properties for Transaction object.(Atomicity, Consistency, Isolation, Durability).

Here is a paper which use SqlTransaction dealing with SqlBulkCopy, it is a good article:
http://www.aspdotnetcodes.com/SqlBulkCopy_With_SqlTransaction.aspx

I make notes how to use SqlTransation object below, we can write code like:

string constr=@"...."

using (SqlConnection con=....)
{
con.Open();
using (SqlTransaction tran=con.BeginTransation())
{
try
{
using (SqlCommand cmd=con.CreateCommand())
{
cmd.Transaction=tran;
cmd.Execute....;
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}

That is, if a command execute sucessfully, we use SqlTransation Commit() method, and if it failed, we use its Rollback() method to rollback the transaction.

Use DataAdapter Update Table that doesn't have primary keys

If the Table has primary key, We just need to use the SqlCommandBuilder, and DataAdpter Update() method:

Here we create a table in sql server:

CREATE TABLE [dbo].[TestTable](
[First Name] [nvarchar](50) NOT NULL,
[Last Name] [nchar](50) NULL,
[Score] [int] NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[First Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And let's insert 2 records in the table:
Insert into TestTable Values ('JayNair', 'SomeLastName' 99);
Insert into TestTable Values ('Bala', 'AnotherLastName',99);

Notice the table has Primary key, so update the table is very simple, the sampe code is like following:

using System;
using System.Data;
using System.Data.SqlClient;

string constr = @"Data Source=myserver;Initial Catalog=mydb;User Id=jiezhu;Password=mypass;";

SqlConnection con = new SqlConnection(constr);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Select * from TestTable";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet("MyDS");

SqlCommandBuilder brd = new SqlCommandBuilder(da);

da.Fill(ds, "MyTable");

ds.Tables["MyTable"].Rows[0]["Score"] = 95;

ds.Tables["MyTable"].Rows.Add("Jie", "Zhu", 88);

da.Update(ds, "MyTable");

Notice here, set PK at FirstName is not reasonable. Many times, we need to update a table which doesn't have primary key. But for DataAdpter, UpdateCommand, InsertCommand properties, they use PK to do update and Insert. So in order to manipulate the table which doesn't have primary key, we need to define the UpdateCommand and InsertCommand ourselves:

Let's remove PK in our table:

CREATE TABLE [dbo].[TestTable](
[First Name] [nvarchar](50) NOT NULL,
[Last Name] [nchar](50) NULL,
[Score] [int] NULL
) ON [PRIMARY]

And in our C# code, the complete code is like following:

using System;
using System.Data;
using System.Data.SqlClient;

string constr = @"Data Source=myserver;Initial Catalog=mydb;User Id=jiezhu;Password=mypass;";

SqlConnection con = new SqlConnection(constr);

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "Select * from TestTable";

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet("MyDS");

SqlCommandBuilder brd = new SqlCommandBuilder(da);

da.Fill(ds, "MyTable");



ds.Tables["MyTable"].Rows[0]["Score"] = 95;

ds.Tables["MyTable"].Rows.Add("Jie", "Zhu", 88);

da.UpdateCommand = new SqlCommand("UPDATE TestTable SET [First Name] = @FirstName, [Last Name] = @LastName, Score=@Score " +
"WHERE [First Name] = @FirstName and [Last Name] = @LastName", con);
da.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.NChar, 50, "First Name");
da.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NChar, 50, "Last Name");
da.UpdateCommand.Parameters.Add("@Score", SqlDbType.Int, 4, "Score");

da.InsertCommand = new SqlCommand("INSERT INTO TestTable ([First Name], [Last Name], Score) " +
"VALUES (@FirstName, @LastName, @Score)", con);
da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.NChar, 50, "First Name");
da.InsertCommand.Parameters.Add("@LastName", SqlDbType.NChar, 50, "Last Name");
da.InsertCommand.Parameters.Add("@Score", SqlDbType.Int, 4, "Score");

da.Update(ds, "MyTable");

Use Multiple Active Result Sets(MARS) to Execute Multiple Commands on a Connection in ADO.net

In ADO.net, if you try to execute another command while the first command is executing,thre will have an InvalidOperationException: "There is already an open DataReader associated with this Connection which must be closed first".

We can use Mutilple Active Result Sets(MARS) to solve this issue.

The only change is in the connection string:

string constring=@"Data Source=....; MultipleActiveResultSets=True";

So in our code, we can write:

SqlConnection con=new SqlConnection(constring);
SqlCommand cmd1=....;
SqlCommand cmd2=...;
con.Open();
SqlDataReader reader=cmd1.ExecuteReader();
while(reader.Read())
{
cmd2.ExecuteScalar();
....
}
con.Close();

Notice that MARS will affect performace negatively. So if we don't need use MARS we shouldnot use it.

But in the following case MARS benefits:
1. Like the above example, if we don't use MARS, we open connection twice. That is we open the connection, loop the collection and read the results to a collection. Close the connection. Open the collection again do the second sqlcommand. If we don't use MARS we will loop the collection twice which is not efficient.
2. Some times it is expensive to create two connections. Some third party may charge twices for 2 connections.

Use the tool aspnet_regiis.exe to encrypt and decrypt connection string in web.config

We place connection string in web.config like:

< ?xml version="1.0"?>
< configuration xmlns="http..... ">
< appSettings/>
< connectionStrings>

< connectionStrings/>
< system.web>
...
< /system.web>
< /configration>

We can use Visual Studio Command prompt to run the following command to encrypt and decrypt connection string in web.config:

aspnet_regiis -pef "connectionStrings" "C:\....\EncryptWebSite"

aspnet_regiis -pdf "connectionStrings" "C:\....\EncryptWebSite"

ADO.net Connection Pool (2)

Something more about Connection Pool:

1. Connection Polling is the process of reusing existing active connections instead of creating new connections when a request is made to the database.

2. When the connection manager recieves a request for a new connection, it checks its pool for available connections. If a connection is available, it returns, if no connections available, and the maximum pool size has not been reached, a new connection is created and returned.

3. If the maximal connection pool size has been reached, the connection requested is added to the queue.

4. Minimal pool size default is 0. When your application requires consistence, sometimes we set this value to be 5 even the application is inactive for long period of time.

5. Maximal pool size default 100.

6. To implement connection pooling. The connection string must be exactly the same, case sensitive and even space in the string should be the same.

7. The user ID must be the same for every user or service in the pool.

8. The process ID must be the same, it is impossible to share connection across processes.

9. Where is pool located? Connection pool is a client side technology, which means the database doesn't know anything about the connection pools involved in your application.

10. When is pool created? When the first connection is instantiated, a connection pool group is created, but the first connection pool is not created until the first connection is opened.

StreamReader read txt file from a http link

using System;
using System.IO;
using System.Net;

WebRequest request = WebRequest.Create(url);
request.Credentials = new NetworkCredential("username", "password");
//if there is credential.
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (StreamReader sr
= new StreamReader(response.GetResponseStream()))
{
string tstr = sr.ReadToEnd();
......
}

Sort Multiple Columns of DataTable using DataView object

DataTable's View has Sort Property.
During my work, I want to build a gridview which has Datatable sorted by first column, and then the second column, and then the third column.

I write the code like following:

Drage a GridView Control to the desinger with name GridView1.

GridView1.AutoGenerateColumns = false;

BoundField bf1 = new BoundField();
bf1.HeaderText = "Company Name";
bf1.DataField = "Company Name";
GridView1.Columns.Add(bf1);


BoundField bf2 = new BoundField();
bf2.HeaderText = "First Name";
bf2.DataField = "First_Name";
GridView1.Columns.Add(bf2);

BoundField bf3 = new BoundField();
bf3.HeaderText = "Last Name";
bf3.DataField = "Last_Name";
GridView1.Columns.Add(bf3);

BoundField bf4 = new BoundField();
bf4.HeaderText = "Salary";
bf4.DataField = "Salary";
GridView1.Columns.Add(bf4);

//The above code to build a GridView can also written in page sources

DataTable dt = new DataTable();

dt.Columns.Add("Company Name");
dt.Columns.Add("First_Name");
dt.Columns.Add("Last_Name");
dt.Columns.Add("Salary");
dt.Columns["Salary"].DataType = typeof(int);


DataRow dr1 = dt.NewRow();
dr1["Company Name"] = "Microsoft";
dr1["First_Name"] = "John";
dr1["Last_Name"] = "Smith";
dr1["Salary"] = 700000;
dt.Rows.Add(dr1);

DataRow dr2 = dt.NewRow();
dr2["Company Name"] = "Google";
dr2["First_Name"] = "Jay";
dr2["Last_Name"] = "Wan";
dr2["Salary"] = 1000000;
dt.Rows.Add(dr2);

DataRow dr3 = dt.NewRow();
dr3["Company Name"] = "Microsoft";
dr3["First_Name"] = "Eric";
dr3["Last_Name"] = "White";
dr3["Salary"] = 750000;
dt.Rows.Add(dr3);

DataRow dr4 = dt.NewRow();
dr4["Company Name"] = "Apple";
dr4["First_Name"] = "Jessica";
dr4["Last_Name"] = "Black";
dr4["Salary"] = 800000;
dt.Rows.Add(dr4);

DataRow dr5 = dt.NewRow();
dr5["Company Name"] = "Apple";
dr5["First_Name"] = "Jessica";
dr5["Last_Name"] = "White";
dr5["Salary"] = 500000;
dt.Rows.Add(dr5);

DataRow dr6 = dt.NewRow();
dr6["Company Name"] = "Apple";
dr6["First_Name"] = "Jessica";
dr6["Last_Name"] = "White";
dr6["Salary"] = 1500000;
dt.Rows.Add(dr6);

dt.DefaultView.Sort = "Company Name ASC,First_Name ASC,Last_Name DESC,Salary DESC";

GridView1.DataSource = dt;

GridView1.DataBind();

The results is like:



Notice, the DataView has the RowFilter, RowStateFilter Properties.

We can add:

dt.DefaultView.RowFilter="First_Name like 'J%' and Salary>600000" to display the data which has the first name like 'J%' and Salary>600000.(It is T sql where clause).