First of all, to apply this article you have to create a document
library in your MOSS web application. Before that, you have to create a web
application. From MOSS central administration you will select Application
Management | Create or extend Web application,
and follow the steps. After you finish creating the web application you can
create a document library as the following.
Open your web application in the browser.
On the right side of the page you will find the site action
menu if you are the administrator on this site as below.
Figure 1
Select site settings, then under site administration select
site libraries and lists and you will see figure 2; select create new content.
Figure 2
Under libraries select the document library.
Figure 3
When you reach the create page fill the data relevant to
your requirements, for now we will put in pilot data like figure 4. Press
create after you finish filling data.
Figure 4
Web application will redirect you automatically to the
document library page.
Figure 5
Create voting web part for document library
After we have created the document library, you will upload
a document to test the web part we will create. We will use smartpart web part to save time in writing
controls, rendering, etc. Do not use AJAX smartpart
as it crashes.
Create a new web site in Visual Studio 2005.
Add to project user control and name it
"VotingOnDL.ascx."
Figure 6
In order to save votes data, we will need to add a table in
the same MOSS web application content database. You will open MOSS central administration
-> web application list -> select your web application -> under SharePoint
Web Application Management, select content databases -> write down your
database name or copy it, then open SQL Server 2005 Management Studio to view
the database. Notice that if you have configured SharePoint to use another SQL
instance not the default, you will connect to that instance as our pilot now;
our database is on another instance (local\officeserver) as shown below.
Figure 7
Right click on tables and add table (new table) AllVotings; add
columns as shown below.
Figure 8
Create procedure spInsertVoting to insert values into the
table and any new record and to update at the same time if it found the same FileId
for the same user identity (in order not to duplicate records).
Listing 1
Create procedure [dbo].[spInsertVoting]
@FileID nvarchar(50) ,
@FileName nvarchar(50) ,
@FolderID nvarchar(50),
@FolderName nvarchar(50),
@UserIdentity nvarchar(50),
@Choice nvarchar(50)
as
select * from allvotings where
useridentity = @UserIdentity and ID = @FileID
if @@Rowcount = 0
begin
insert into AllVotings
(ID , [FileName] , FolderID , FolderName ,UserIdentity , Choice)
values
(@FileID , @FileName , @FolderID , @FolderName ,@UserIdentity , @Choice)
end
else
begin
update AllVotings
set ID=@FileId ,
[FileName] = @FileName ,
FolderID=@FolderID ,
FolderName = @FolderName ,
userIdentity = @UserIdentity ,
Choice = @Choice
where
useridentity = @UserIdentity and ID = @FileID
end
Create the procedure spGetVotingResults as in listing 1. This
procedure counts voting on library files and gives the results by percentage.
Listing 2
Create procedure [dbo].[spGetVotingResults]
@FileId nvarchar(50), @FolderName nvarchar(50)
as
declare @Bad float
declare @Good float
declare @VeryGood float
declare @Total float
select @Good = count(choice) from allvotings where choice = 2
and [ID] = @FileId and FolderName = @FolderName
select @Bad = count(choice) from allvotings where choice = 1
and [ID] = @FileId and FolderName = @FolderName
select @VeryGood = count(choice) from allvotings where choice = 3
and [ID] = @FileId and FolderName = @FolderName
select @Total = count(choice) from allvotings where [ID] = @FileId
and FolderName = @FolderName
if @Total = 0
begin
set @Total = 1
end
select round (cast(((@Good/@Total)*100) as float ),1) as Good
,round (cast(((@Bad/@Total)*100) as float ),1) as Bad
,round (cast(((@VeryGood/@Total)*100) as float ),1) as VeryGood
Let us go back to our user control and write our code to use
the previous procedures. First of all, we are going to design the control (see
Figure 9). We have the DropDownList for folders in the library then a
DropDownList to sub folder; you can of course design as you like. We have a vote
button to list files to vote for it and we have two GridViews, one for voting
and another one for listing the results after voting.
Figure 9
Now, let uss see the asp.net code behind for binding for
GridView. First, the GridView code is for voting; we have the radio button list
and every vote has a value from 1 to 3, from bad to very good.
Listing 3
<asp:GridView ID="gvView" DataKeyNames="FileID" CssClass="ms-listdescription"
runat="server" Width="640px" AutoGenerateColumns="false" Visible="False"
OnRowCommand="gvView_RowCommand"
OnSelectedIndexChanged="gvView_SelectedIndexChanged">
<HeaderStyle BackColor="#99ccff" ForeColor=black />
<Columns>
<asp:BoundField DataField ="FileName" HeaderText="File Name">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:TemplateField HeaderText="Voting Levels">
<ItemTemplate >
<asp:RadioButtonList ID="rdlButton" runat="server" RepeatDirection="Horizontal" >
<asp:ListItem Value=1>
Bad
</asp:ListItem>
<asp:ListItem Value=2>
Good
</asp:ListItem>
<asp:ListItem Value=3>
Very Good
</asp:ListItem>
</asp:RadioButtonList>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:ButtonField CommandName="Vote" ButtonType="Button" Text=vote >
<ControlStyle Width="40px" />
<FooterStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:ButtonField>
</Columns>
</asp:GridView>
Second GridView shows results, colors distinguish votes (bad – good – very good)
Listing 4:
<asp:GridView ID="gvListResults" runat=server AutoGenerateColumns=false
CssClass="ms-listdescription" Width="640px" >
<Columns>
<asp:BoundField DataField="FileName" HeaderText="File Name">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:TemplateField>
<ItemTemplate>
<table bordercolor="#66ccff" border="1.5px" width= "100px" height="20px"
cellpadding=0 cellspacing=0 align=center>
<tr>
<td bgcolor="#0099ff" width="<%# float.Parse(Eval("Bad").ToString()) %>%">
</td>
<td bgcolor="#0066ff" width="<%# float.Parse(Eval("Good").ToString()) %>%">
</td>
<td bgcolor="#3300cc"width="<%# float.Parse(Eval("VeryGood").ToString()) %>%">
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Bad %" ItemStyle-ForeColor="#0099ff">
<ItemTemplate>
<%#Eval("Bad") %>%
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Good %" ItemStyle-ForeColor="#0066ff" >
<ItemTemplate>
<%#Eval("Good") %>%
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText = "Very Good %" ItemStyle-ForeColor="#3300cc">
<ItemTemplate>
<%#Eval("VeryGood") %>%
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
</Columns>
</asp:GridView>After finishing asp.net code, we have to write binging
methods to get data from the database and SharePoint library. We declare the web
application to access document library. We have to add a reference first to
Microsoft.Sharepoint dll; you will find this dll on SharePoint files in program
files.
Listing 4
using Microsoft.SharePoint; // sharepoint classes
using System.Security.Principal; // to get user identity
using System.Data.SqlClient; // to use MS SQL Procedures
Then we will add 3 lines to define which MOSS web
application we are going to work on.
Listing 5
SPSite oSite = new SPSite("http://wt-sw-nc01:22/"); // this the link of WP
SPWeb oWeb; // we will use it in the following steps
SPDocumentLibrary spDoc; // our sharepoint document library
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
oWeb = oSite.OpenWeb(); // open the web application to get document library
GetFolders();
}
}In previous listing we have the GetFolders() method to get
"main" folders in the document library, but how we will know which
document library in the web application to get? In the listing 7 you will
notice that we get lists of GUID list. But how can you get this GUID? I have
opened content database of the web application and there is a table for lists
but you can use its name directly or you can store it on web.config in case you
changed its name. You will notice that after getting the document library, we
treat folders as SPListItem as it is the main class that all other items like
folders inherit from.
Listing 6
void GetFolders()
{
try
{
ddlFolders.Items.Clear();
ddlFolders.Items.Add("Select Folder");
spDoc = (SPDocumentLibrary)oWeb.Lists[new Guid(
"0fbab79d-e64e-45b5-b8b3-0a9338831afe")];
foreach (SPListItem item in spDoc.Folders)
{
try
{
if (item.Folder.ParentFolder.ToString().Split('/').Length == 1)
ddlFolders.Items.Add(new ListItem(item.Folder.Name,
item.Folder.UniqueId.ToString()));
}
catch {}
}
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
} After we load the main folders, when user selects any folder
we will load the subfolders list. You will find more details in method
LoadSubFolders(); it does not differ from GetFolders() except it filters
subfolders relevant to the main folder.
After we finish loading folders and sub folders, we now need
to get files of the selected sub folder to begin to vote. In vote button event
handler we call method GetItems() which gets files and stores them in datatable
to bind it to GirdView.
Now you have all the files you want listed. To vote on files
we have added to GridView ButtonField and its CommandName = "vote" and
we have handled RowCommand event then we call method VoteForDocument as listing
7. In RowCommand I pass the value of voting and fileId from datakeys property
of GridView (which is why I used GridView not DataGrid), and call the insert
stored procedure.
Listing 7
private void VoteForDocument(int iIndex)
{
try
{
if (((RadioButtonList)gvView.Rows[iIndex].FindControl("rdlButton"))
.SelectedValue != "" && iIndex > - 1)
{
SqlCommand dataSource = new SqlCommand();
dataSource.Connection = new SqlConnection(
@"server=wt-sw-nc01\\officeservers ;
database =WSS_Content_b6617126-e1b4-40d0-9fa0-9a8affdb6c6c ;
trusted_connection = false;user id = dotnet;pwd=123 ");
dataSource.CommandText = "spInsertVoting";
dataSource.CommandType = CommandType.StoredProcedure;
#region Setting Paramters
string FileID = gvView.DataKeys[iIndex].Value.ToString();
string FileName = gvView.Rows[iIndex].Cells[0].Text;
string FolderID = ddlSubFolders.SelectedValue;
string FolderName = ddlSubFolders.SelectedItem.Text;
string strUserIdentity = WindowsIdentity.GetCurrent().Name;
string Choice = ((RadioButtonList)gvView.Rows[iIndex].FindControl(
"rdlButton")).SelectedValue;
SqlParameter paramFileId = new SqlParameter("@FileID", FileID);
SqlParameter paramFileName = new SqlParameter("@FileName", FileName);
SqlParameter paramFolderId = new SqlParameter("@FolderID", FolderID);
SqlParameter paramFolderName = new SqlParameter("@FolderName", FolderName);
SqlParameter paramUser = new SqlParameter("@UserIdentity", strUserIdentity);
SqlParameter paramChoice = new SqlParameter("@Choice", Choice);
dataSource.Parameters.Add(paramFileId);
dataSource.Parameters.Add(paramFileName);
dataSource.Parameters.Add(paramFolderId);
dataSource.Parameters.Add(paramFolderName);
dataSource.Parameters.Add(paramUser);
dataSource.Parameters.Add(paramChoice);
#endregion
dataSource.Connection.Open();
dataSource.ExecuteNonQuery();
dataSource.Connection.Close();
}
else
{
Label1.Text = "Please Choose Voting Level";
}
}
catch (Exception ex)
{
Label1.Text = ex.Message + "<br>" + ex.InnerException + "<br>" +
ex.StackTrace;
}
}After finalizing adding I call GetResults() to display the results
in the other GridView as in listing 8. GetResults method opens the database and
uses spGetVotingResults procedure to bind GridView.
You can, of course, put the connection string in the web
application web.config in appSettings tag to be dynamic. You might have noticed
that I connect to the web application content database and the user I use is
added to users on the database and also you can use the session to store data- it
is all up to you.
Listing 8
private void GetResults()
{
try
{
SqlCommand dataSource = new SqlCommand("spGetVotingResults");
dataSource.Connection = new SqlConnection(
@"server=wt-sw-nc01\\officeservers ;
database =WSS_Content_b6617126-e1b4-40d0-9fa0-9a8affdb6c6c ;
trusted_connection = false;user id = dotnet;pwd=123 ");
dataSource.CommandType = CommandType.StoredProcedure;
DataTable dtFilesList = GetItems();
DataTable dtVotingResults = new DataTable();
dtVotingResults.Columns.Add("FileName");
dtVotingResults.Columns.Add("Bad");
dtVotingResults.Columns.Add("Good");
dtVotingResults.Columns.Add("VeryGood");
dataSource.Connection.Open();
{
dataSource.Parameters.Add("@FileID", "none");
dataSource.Parameters.Add("@FolderName", ddlSubFolders.SelectedItem.Text);
foreach (DataRow drFile in dtFilesList.Rows)
{
dataSource.Parameters["@FileID"].Value = drFile["FileID"].ToString();
SqlDataReader rdReader = dataSource.ExecuteReader();
while (rdReader.Read())
{
DataRow drTemp = dtVotingResults.NewRow();
drTemp["FileName"] = drFile["FileName"].ToString();
drTemp["Bad"] = rdReader["Bad"].ToString();
drTemp["Good"] = rdReader["Good"].ToString();
drTemp["VeryGood"] = rdReader["VeryGood"].ToString();
dtVotingResults.Rows.Add(drTemp);
}
rdReader.Close();
}
}
gvListResults.DataSource = dtVotingResults;
gvListResults.DataBind();
gvListResults.Visible = true;
dataSource.Connection.Close();
}
catch (Exception ex)
{
Label1.Text = ex.Message + "<br/>" + ex.InnerException + "<br/>" +
ex.StackTrace;
}
}Now we have finished all the coding work we are going to
deploy on MOSS server. Before we deploy we have to generate DLL from our
project. To get *.DLL from project, right click on your project and select
publish as figure 10, it will take a while if you do not have enough disk space
like mine.
Figure 10
Now if you have installed smartpart, copy the .ascx file on
usercontrols folder in virtual folder of your MOSS web application on the
server and copy the *.dll that was generated in a previous step from publishing
to the bin folder.
Open your web application in Internet explorer, add new web
part page and add smartpart web part to the page. Modify the shared web parts
on the page editing menu (you have to be administrator or contributor to add
new page and edit on it) and then press edit and modify web the part. You will
see the properties on the right hand side and in the user control to display,
select your user control as in figure 11. Press OK and congratulations! Your
user control became a web part on your web application and you can vote!
Figure 11
