SharePoint @ Big Scholar

In your web part render override, create a link that looks something like this:

<a href="http://your_intranet/path/to/page/with/webpart/Pages/default.aspx?getfileid=5">Get File with ID 5</a>

I'm hard coding the above because I'm assuming that you know how to query the database and display the relevant link on your web part.

Now, the code to download the file once the above link is clicked.

        protected override void Render(HtmlTextWriter writer)
        {
            writer.Write("<a href="http://your_intranet/path/to/page/with/webpart/Pages/default.aspx?getfileid=5">Get File with ID 5</a>");
            try
            {
                if (Page.Request.QueryString["getfileid"] != null)
                {
                    OpenSQLConnection();
                    cmd = new SqlCommand("SELECT FileName, Content, ContentType FROM TableName WHERE ID=" + Page.Request.QueryString["getfileid"], connection);
                    cmd.CommandType = CommandType.Text;
                    rdr = cmd.ExecuteReader();
                    if (rdr.Read())
                    {
                        try
                        {
                            byte[] fileData = (byte[])rdr["Content"];

                            Page.Response.Clear();
                            Page.Response.Buffer = true;
                            Page.Response.ContentType = rdr["ContentType"].ToString();
                            Page.Response.AddHeader("content-disposition", "attachment;filename=" + rdr["FileName"].ToString());
                            Page.Response.Charset = "";
                            Page.Response.Cache.SetCacheability(HttpCacheability.NoCache);
                            Page.Response.BinaryWrite(fileData);
                            Page.Response.End();
                        }
                        catch (Exception ex)
                        {
                            // Error trying to download attachment.
                        }
                    }
                    else
                    {
                        // file not found.
                        writer.Write("File not found.");
                    }
                    rdr.Close();
                    CloseSQLConnection();
                }
            }
            catch (Exception ex)
            {
                CloseSQLConnection();
                // problem with connecting to db to get the file
            }
        }

On clicking that link, you will now be prompted to download the file and choose a location where you want to save that file (or just open it for viewing).

Firstly, make sure that you have a varbinary and/or image field in your table since we are going to store the file you upload in binary form.

Again, let's start with the important variables:

        private HtmlInputFile inputFile = new HtmlInputFile();
        private Button btnSubmitFile = new Button();

 Then within the CreateChildControls Override:

        protected override void CreateChildControls()
        {
                inputFile.ID = "_fileUpload";
                this.Controls.Add(inputFile);

                btnSubmitFile.Text = "Submit Request";
                btnSubmitFile.Click += new EventHandler(btnSubmitFile_Click);
                this.Controls.Add(btnSubmitFile);
        }

Then within the Render Override:

        protected override void Render(HtmlTextWriter writer)
        {
                inputFile.RenderControl(writer);
                btnSubmitFile.RenderControl(writer);
        }

Then within the btnSubmitFile event which runs when the button is clicked

        void btnSubmitRequest_Click(object sender, EventArgs e)
        {
            // check for allowed extensions
            string AllowedExtensions = "'.doc','.xls','.bmp','.gif','.jpg','.pdf'";
            if (String.IsNullOrEmpty(inputFile.PostedFile.FileName))
            {
                // This means there is no file submitted
            }
            else
            {
                // check the extension of the file before uploading
                if (AllowedExtensions.Contains(System.IO.Path.GetExtension(inputFile.PostedFile.FileName)))
                {
                    // is one of the allowed extensions
                    try
                    {
                        UploadAttachments();
                    }
                    catch (Exception ex)
                    {
                        // problem uploading the file... handle the exception
                    }
                }
                else
                {
                    // file extension is not allowed
                }
            }
        }

        private void UploadAttachments()
        {
            // now check if there are any attachments.
            try
            {
                if (inputFile.PostedFile.FileName.Length != 0)
                {
                    // then there is a file to upload
                    HttpPostedFile objHttpPostedFile = inputFile.PostedFile;

                    // Find its length and convert it to byte array
                    int intContentlength = objHttpPostedFile.ContentLength;

                    // Create Byte Array
                    Byte[] bytFile = new Byte[intContentlength];

                    // Read Uploaded file in Byte Array
                    objHttpPostedFile.InputStream.Read(bytFile, 0, intContentlength);

                    OpenSQLConnection();
                    SqlCommand cmd = new SqlCommand("INSERT INTO TableName(FileName,ContentType,Content) VALUES(@FileName,@ContentType,@Content)", connection);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@FileName", inputFile.PostedFile.FileName));
                    cmd.Parameters.Add(new SqlParameter("@ContentType", inputFile.PostedFile.ContentType));
                    cmd.Parameters.Add(new SqlParameter("@Content", bytFile));

                    cmd.ExecuteNonQuery();
                    CloseSQLConnection();
                }
            }
            catch (Exception ex)
            {
                CloseSQLConnection();
                // Problem uploading the file into the database... handle the exception
            }
        }

        private void OpenSQLConnection()
        {
            connection = new SqlConnection("server=localhost;database=DatabaseName;Integrated Security=SSPI");
            connection.Open();
        }

        private void CloseSQLConnection()
        {
            connection.Close();
        }

Note that the code above checks only for the file extension, which can be easily changed by renaming the file. You could also check their content types (inputFile.PostedFile.ContentType) if you want to be sure that the file being uploaded is really what it claims to be.

Next blog will explain how to place a link in a web part to download the file that was just uploaded.

This is useful if you want to give the client the ability to design their own web part, format it in any way they like while pulling merge fields from xml. Not sure if that was very clear... let me try again. The web part will create XML that contains fields that can be used by XSL, which can be edited by the client within the properties of the web part to display the results in any way they choose.

Firstly we should declare a few variables and properties

        public string feedXsl = "";

        [WebBrowsable(true), WebPartStorage(Storage.Shared)]
        [Personalizable(PersonalizationScope.Shared)]
        [FriendlyNameAttribute("Xsl Template")]
        public string FeedXsl
        {
            get { return feedXsl; }
            set { feedXsl = value; }
        }

This code above is what allows the user to edit the XSL via the web part properties. Note that this will only give the user a single line to enter all their XSL code. You can click on the ... to get a text area. In future, I'll describe how you can customise the web part properties area and place items like drop down boxes, buttons, text areas and other form items within the web part properties section.

Now that we have the variables and the properties out of the way, we can start coding the main section of the web part.

Within the Render Override, you need to firstly get the xml. In this example, I'll hard code the XML, but you will probably fill the XML with variables that you want to make available to the client for your particular web part.

        protected override void Render(HtmlTextWriter writer)
        {
            try
            {
                #region Create the XML contents
                string xmlfile = "<?xml version="1.0"?>";
                xmlfile += "<UserDetails>";
                xmlfile += "<UserInfo>";
                xmlfile += "<Username>jsmith</Username>";
                xmlfile += "<PreferredName>John Smith</PreferredName>";
                xmlfile += "<Email>john.smith@example.com</Email>";
                xmlfile += "</UserInfo>";
                xmlfile += "<UserInfo>";
                xmlfile += "<Username>jdoe</Username>";
                xmlfile += "<PreferredName>Jane Doe</PreferredName>";
                xmlfile += "<Email>jane.doe@example.com</Email>";
                xmlfile += "</UserInfo>";
                xmlfile += "</UserDetails>";

                string strHTML = string.Empty;
                XslCompiledTransform xslt = new XslCompiledTransform();
                if (!((FeedXsl.ToString() == null) || (FeedXsl.ToString() == string.Empty) || (FeedXsl.ToString() == "")))
                {
                    TextReader tr = new StringReader(FeedXsl.ToString().Trim());

                    XmlReader xreader = new XmlTextReader(tr);

                    strHTML = TransformXML(xmlfile, xreader);
                }
                else
                {
                    strHTML = "Please associate a valid XSL Template";
                }

                writer.Write(SPEncode.HtmlDecode(strHTML).ToString());
            }
            catch (Exception ex)
            {
                // handle the exception (or just display it in the web part for your information, which I did below)
                writer.Write(ex);
            }
        }

Within the Render Override, I called a function named TransformXML(xmlfile, xreader). Here's the code for that function:

        private string TransformXML(string strXML, XmlReader strXSL)
        {    //Create a IO Stream
            System.IO.StringWriter oSW = new System.IO.StringWriter();

            try
            {
                StringReader stream;
                stream = new StringReader(strXML);

                System.Xml.XmlTextReader oXR = new System.Xml.XmlTextReader(stream);
                System.Xml.Xsl.XslCompiledTransform oXSLT = new System.Xml.Xsl.XslCompiledTransform();

                oXSLT.Load(strXSL);

                System.Xml.XPath.XPathDocument oXPath = new System.Xml.XPath.XPathDocument(oXR);

                if (oXPath != null)
                {
                    oXSLT.Transform(oXPath, null, oSW);
                }
            }
            catch (Exception ex)
            {
                // there is an error with the transformation...
                oSW = new StringWriter(new StringBuilder("You have an error in your XSL Template."));
            }
            return oSW.ToString();
        }

Building the XSLT Code

When you compile the code and view the web part, you'll get the message You have an error in your XSL Template. This is because you haven't set up the XSL yet. View the web part properties and under Miscellaneous, you'll see a text field. Click on it then click on the ... button next to it.

In the window that pops up, enter your XSL code in there. I've included a simple sample below of what can go in here to transform the xml to html:

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema"
               version="1.0" exclude-result-prefixes="xsl ddwrt msxsl rssaggwrt"
               xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"
               xmlns:rssaggwrt="http://schemas.microsoft.com/WebParts/v3/rssagg/runtime"
               xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt">

  <xsl:template match="/">

    <table>
      <xsl:attribute name="width">100%</xsl:attribute>
      <xsl:for-each select="UserDetails/UserInfo">
        <tr>
          <td>
            <xsl:attribute name="align">center</xsl:attribute>
            <div>
              <b>
                <xsl:value-of select="PreferredName"/>
              </b>
            </div>
            <div>
              <xsl:value-of select="Email"/>
            </div>
          </td>
        </tr>
      </xsl:for-each>
    </table>

  </xsl:template>
</xsl:stylesheet>

Ok, this one is fairly basic... I'm just putting it here because I use this bit of code really, really often.

SPWeb mySite = SPContext.Current.Web;
SPListItemCollection listItems = mySite.Lists["NameOfList"].Items;

int countResults = 0;

for (int i = 0; i < listItems.Count; i++)
{
    SPListItem item = listItems[i];
    writer.Write(item["Title"].ToString()+"<br />");
}

Nice and simple...

Most discussions on data grids were for ASP.net and not in web parts and that annoyed me... so here's a few things you need to know about datagrids in web parts in SharePoint.

Firstly, the datagrid needs to be bound in CreateChildControls.

                dgRecords = new DataGrid();
                dgRecords.EnableViewState = true;
                dgRecords.Width = new Unit("100%");
                dgRecords.AutoGenerateColumns = false;
                dgRecords.BorderStyle = BorderStyle.None;
                dgRecords.FooterStyle.BorderStyle = BorderStyle.None;
                dgRecords.HeaderStyle.CssClass = "ms-formlabel";
                dgRecords.HeaderStyle.Font.Size = FontUnit.XXSmall;
                dgRecords.CellPadding = 3;
                dgRecords.ItemStyle.Height = 30;

                dgRecords.AllowPaging = true;
                dgRecords.PageSize = 25; // results returned per page
                dgRecords.PagerStyle.Mode = PagerMode.NumericPages;
                dgRecords.PagerStyle.HorizontalAlign = HorizontalAlign.Center;
                dgRecords.PageIndexChanged += new DataGridPageChangedEventHandler(dgRecords_PageIndexChanged);
                this.Controls.Add(dgRecords);

                OpenSQLConnection();
                SqlCommand cmd = getSqlCommand();

                cmd.CommandType = CommandType.Text;
                SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

                dsRecords = new DataSet();
                myAdapter.Fill(dsRecords, "TableName");

                dv = new DataView(dsRecords.Tables["TableName"]);

                HyperLinkColumn linkColumn = new HyperLinkColumn();
                linkColumn.DataTextField = "fldID";
                linkColumn.DataTextFormatString = "{0}";
                linkColumn.DataNavigateUrlField = "fldID";
                linkColumn.DataNavigateUrlFormatString = "http://your_intranet/Pages/default.aspx?ticketid={0}";
                linkColumn.HeaderText = "ID#";

                BoundColumn itemColumn = new BoundColumn();
                itemColumn.DataField = "fldItem";
                itemColumn.DataFormatString = "{0}";
                itemColumn.HeaderText = "Item Ordered";

                BoundColumn priceColumn = new BoundColumn();
                priceColumn.DataField = "fldPrice";
                priceColumn.DataFormatString = "{0}";
                priceColumn.HeaderText = "Price";

                BoundColumn quantityColumn = new BoundColumn();
                quantityColumn.DataField = "fldQuantity";
                quantityColumn.DataFormatString = "{0}";
                quantityColumn.HeaderText = "Quantity Ordered";

                TemplateColumn totalColumn = new TemplateColumn();
                totalColumn .ItemTemplate = new TotalColumnTemplate();

                dgRecords.Columns.Add(linkColumn);
                dgRecords.Columns.Add(itemColumn);
                dgRecords.Columns.Add(priceColumn);
                dgRecords.Columns.Add(quantityColumn);
                dgRecords.Columns.Add(totalColumn);

                dgRecords.HeaderStyle.BackColor = Color.FromArgb(198, 226, 255);
                dgRecords.HeaderStyle.ForeColor = Color.White;
                dgRecords.AlternatingItemStyle.BackColor = Color.FromArgb(247, 246, 243);
                dgRecords.AlternatingItemStyle.ForeColor = Color.FromArgb(33, 33, 33);
                dgRecords.ItemStyle.ForeColor = Color.FromArgb(28, 47, 75);

                dgRecords.DataSource = dv;
                // bind only on page load
                if (!Page.IsPostBack) dgRecords.DataBind();

                CloseSQLConnection();

Notice that in the code above, you are using BoundColumn a lot. This is all ok and well if you are just querying the database and retrieving the values stored in the database without needing to make any calculations on returned values. If you do need to make calculations (for example, price x quantity, then you need a TemplateColumn), which complicates things a little.

Also note that we are only binding this if it isn't a postback. The reason for this is because we need to bind it to different "data" when an event happens such as column sorting or a page change. I will go through the paginations a bit later, but won't go through the column sorting until another post in the future.

So, now if you copy and paste the above code, you will find that you don't have methods for OpenSQLConnection(), getSqlCommand(), TotalColumnTemplate() and CloseSQLConnection(). The code for all but the TotalColumnTemplate() is below:

        private void OpenSQLConnection()
        {
            connection = new SqlConnection("server=localhost;database=hwsupport;Integrated Security=SSPI");
            connection.Open();
        }

        private void CloseSQLConnection()
        {
            connection.Close();
        }

         private SqlCommand getSqlCommand()
        {
            SPWeb mySite = SPContext.Current.Web;
            SqlCommand cmd = new SqlCommand("SELECT * FROM TableName", connection);
            return cmd;
        }

For TotalColumnTemplate(), we need to create a new class. Right click on your project and add a class file. Name it TotalColumnTemplate.cs since that is what this template is referred to as in the code. Copy the following into TotalColumnTemplate.cs through VS:

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    class LightColumnTemplate : ITemplate
    {
        public void InstantiateIn(Control container)
        {
            LiteralControl ouputLiteral = new LiteralControl();
            ouputLiteral.DataBinding += new EventHandler(myLiteral_DataBinding);
            container.Controls.Add(ouputLiteral);
        }

        public void myLiteral_DataBinding(object sender, EventArgs e)
        {
            LiteralControl myLiteral = ((LiteralControl)(sender));
            DataGridItem container = ((DataGridItem)(myLiteral.NamingContainer));
            int Price = int.Parse(DataBinder.Eval((((DataGridItem)(container))).DataItem, "fldPrice").ToString());
            int Quantity = int.Parse(DataBinder.Eval((((DataGridItem)(container))).DataItem, "fldQuantity").ToString());

            string strExactOutput;

            strExactOutput = Convert.ToString(Price*Quantity);

            myLiteral.Text = strExactOutput;
        }
    }
}

Make sure you set the namespace to the same namespace that your base class is using.

If you deploy your project now, you will see a data grid with the values in it.

Datagrid Pagination in Web Parts

Let's assume now that you have thousands of results returned and you need to paginate.

In the first chunk of code, you'll see this:

                dgRecords.AllowPaging = true;
                dgRecords.PageSize = 25; // results returned per page
                dgRecords.PagerStyle.Mode = PagerMode.NumericPages;
                dgRecords.PagerStyle.HorizontalAlign = HorizontalAlign.Center;
                dgRecords.PageIndexChanged += new DataGridPageChangedEventHandler(dgRecords_PageIndexChanged);

This bit tells the datagrid that you want to enable paging and to run the dgRecords_PageIndexChanged event when a new page is selected.

        void dgRecords_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
        {
            OpenSQLConnection();
            SqlCommand cmd = getSqlCommand();

            cmd.CommandType = CommandType.Text;
            SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

            dsRecords = new DataSet();
            myAdapter.Fill(dsRecords, "TableName");

            dgRecords.DataSource = dsRecords.Tables["TableName"];

            DataView dv = new DataView(dsRecords.Tables["TableName"]);

            try
            {
                dgRecords.CurrentPageIndex = e.NewPageIndex;
            }
            catch
            {
                dgRecords.CurrentPageIndex = 0;
            }

            dgRecords.DataBind();

            CloseSQLConnection();
        }

Note that the dgRecords.DataBind() needs to be run in here to ensure that the rows returned for this page is all that is displayed. If you don't bind the data, the results will not be correct.

I remember that while I was doing this, changing pages worked, but when I go back to Page 1, nothing seems to happen. The reason is because of dgRecords.DataBind(); that I had placed in the CreateChildControls override. I had it such that it was binding when a new page is selected. It should have been if (!Page.IsPostBack) dgRecords.DataBind();. It then needs to be bound in the PageIndexChanged event handler (this will apply for column sorting too).

I wasted a few hours trying to work out why I can't go back to Page 1, so hopefully this will save you the few hours I wasted trying to figure this out.

Data Grid Web Part Alternating Row Colours

This wasted me a few hours too trying to figure out how to alternate row colours in a data grid.

In the end it was fairly simple... just look at the following code:

                dgRecords.HeaderStyle.BackColor = Color.FromArgb(198, 226, 255);
                dgRecords.HeaderStyle.ForeColor = Color.White;
                dgRecords.AlternatingItemStyle.BackColor = Color.FromArgb(247, 246, 243);
                dgRecords.AlternatingItemStyle.ForeColor = Color.FromArgb(33, 33, 33);
                dgRecords.ItemStyle.ForeColor = Color.FromArgb(28, 47, 75);

You can set alternating item styles background colors and foreground colors from RGB or from a predefined color. This one was really obvious, I don't know why it took me so long to figure out. Maybe I just didn't get enough sleep that day.