SqlDataReader Contd.. GetOrdinal(), IsDBNull()

In a previous post "ADO.Net - SqlDataReader" I have given an intro to the SQL Data Reader. Here will go through some usages in the data reader. In the Below code I use some built in methods to iterate through the items returned by the reader.

            using (var connection = new SqlConnection(@"Password=****;Persist Security Info=True;User ID=**;Initial Catalog=****;Data Source=****;Min Pool Size=100;"))
            {
                var command = new SqlCommand("SELECT contact_fax, firstname, lastname, phone from employess where empid = 'E1290'", connection);
             
                connection.Open();
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    // display middle name only if not null
                    if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                        Console.Write(" {0}", reader.GetString(reader.GetOrdinal("firstname")));
                }
                connection.Close();
            }

Here, the IsDBNull() method is used to check if the column contains null's. It accepts an integer and returns true or false based on the column value availability.

The GetOrdinal() method returns the position of the column in the table retrieved by the data reader. Columns are numbered starting from zero. As the firstname column is the second column specified in my select statement, the method returns 1.

If the column is not available this method fires a IndexOutOfRangeException.

And now you combine these two methods together. Specify the column name in the GetOrdinal(). If the column contains a non-null value the condition becomes true, control goes in and prints the column value.

Add New Entry to Host File

Go to C:\Windows\System32\drivers\etc

1. Edit the host file
2. Add the new entry in the following format

    <IP Address> <URL>
    Eg:
    172.11.11.11 abc.shaamil.lk

3. Save and close the file

4. In the Command prompt type in ipconfig /flushdns
You will get a message as "Successfully flushed the DNS Resolver Cache."

Done.. :)

If you are unable to edit the file in the given location, copy the file somewhere else do the changes and paste it back to the original location.

T SQL - Playing around with Image Type ( Create, Insert, Select )

This post briefly describes about creating tables containing image, storing and retrieving images in SQL Server

1) Create a table containing image type

CREATE TABLE dbo.MyImages

(ImageId    INT IDENTITY(1,1) NOT NULL,

 uid             BIGINT NULL, 

 picture       IMAGE NULL

 PRIMARY KEY(ImageId ))

2) Insert an image into the DataBase table. 

INSERT INTO MyImages(picture)

SELECT * FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

OR you can go with the full row insert as:

INSERT INTO MyImages(uid, picture)

SELECT 2000, PicColumn FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

Here, you need to specify the path to the image in the drive, for it to be captured by the insert.

3) Retrieving for display

It's best that you have a handler for retrieving images from DB. For an example could use a generic handler (.ashx file).

3.1
In the handler's (.ashx file's) ProcessRequest() method set

        context.Response.Clear();
        context.Response.ContentType = "image/jpeg";

3.2
In the ashx file create a method to retrieve image from DB. The query string would be straight forward.

Eg: "SELECT uid, picture FROM dbo.MyImages WHERE" + uid

Capture the resulting row may be in a DataTable.

3.3
get the image into a byte array

byte[] imageByteArray=null;
imageByteArray=(byte[])sampleDataTable.Rows[0][1];

3.4
Include the byte[] to  memory stream, and set it for output

MemoryStream memStream = new MemoryStream(imageByteArray, false);
System.Drawing.Image img = System.Drawing.Image.FromStream(memStream);
img .Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Png);

3.5
In the aspx page's calling the method ( Eg: inside an item data bound method ).
Set the image url by pointing to the handler

myImage.ImageUrl = "/pathtothe ashpage/sample.ashx?uid=" + uid;

Custom Field Property Display using CAML - SharePoint 2007

SharePoint 2007 Custom field rendering at List Item View is done through the CAML code. In the custom field definition XML file, there is a section where display is defined... and that is within the  <RenderPattern>. Usually you can access the filed properties in the filed through simple CAML code. For an instance:

<RenderPattern Name="DisplayPattern">
  <HTML><![CDATA[</div><br/><br/><div>Name Value:</div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Name" HTMLEncode="True" />
  </ScriptQuote>

  <HTML><![CDATA[<div>Custom Property 1 Value</div><br/>]]></HTML>
  <ScriptQuote>  
      <Property Select="MyCustomProperty1" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Custom Property 2 Value</div><br/>]]></HTML>
  <ScriptQuote>
      <Property Select="MyCustomProperty2" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Description : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Description" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Required : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Required" HTMLEncode="True" />
  </ScriptQuote>
  <Column HTMLEncode="TRUE"/>

</RenderPattern>

The code would give you the property values of the Description, the required field value and Name value. But.....not the values of the custom properties in your field......

It is possible to get the custom property values and display at DisplayPattern. For this it's required that the property schema's being updated during the field's onAdd() and onUpdate() methods. Solution s to override the two methods.

Then the custom property values become accessible.

A detailed explanation is available at this Link.

Storing integers in an enum

Example
  
enum DepartmentID
    {
        HumanResource = 1001,
        Marketing = 1002,
        Sales = 1003,
        Logistics = 1004,
        Operations = 1005,
        Finance = 2003,
        IT = 2002,
        Training = 2001,      
        SharedServices = 1006,
    }

On instances where you need the departmental id, you could easily retrieve the integer values of the enum by casting:

Eg:
(int)DepartmentID.Marketing


Hence, the code becomes easy to maintain.
On a side note: Comes in handy when building a query string for a sql select command.

Formatting ListView Items

Formatting can be done either through the ASPX page or on the code behind. Below are two different instances where formatting is done.

1) Formatting the <ItemTemplate> in the Aspx page


<div class="EmployeeName">

<asp:Label ID="sampleLabel" Text='<%# String.IsNullOrEmpty(DataBinder.Eval(Container.DataItem, "employeeName").ToString()) ? "" :  DataBinder.Eval(Container.DataItem, "employeeName").ToString().PadRight(140).Substring(0,140).TrimEnd() %>'
Runat="server"/>

</div>
   
Here....I'm getting values from the database. I do, an "empty or null" check. In case the condition is true, display nothing, else display the values retrieved from the DB while restricting text  to 141 characters.                  

2) Also, you could do stuff in the code behind. Below, I limit text on item data bound.

2.1) First create a event handler

<asp:ListView ID="SearchResultsListView" runat="server" ... OnItemDataBound="employeeListView_ItemDataBound">

2.2) Then...
In the employeeListView_ItemDataBound method add the relevant code. I have added some formatting code. The FindControl() method is used to capture the label.

protected void employeeListView_ItemDataBound(object sender, ListViewItemEventArgs e)
{
            string trailer = "...";
            string tempString = string.Empty;
            Label sampleLabel;

            sampleLabel = (Label)e.Item.FindControl("sampleLabel");
            if (sampleLabel.Text.Length > 100)
            {
                tempString = sampleLabel.Text.Substring(0, 96).Trim();
                sampleLabel.Text = tempString + trailer;
            }
}

JavaScript to Access List Item GUID in SharePoint 2010

The SharePoint 2010 client object model has provided JavaScript means to access SharePoint Lists, Libraries and items, etc.... Below is a sample where I access a SharePoint list item GUID information during a on click event on a list item. The item click is triggered at the XSLT code rendering the custom field. This is the related JavaScript.

Here the item GUID is captured.



var itemID = null;
var item = null;
var currentItemGUID = null;

//Assigned the itemID value coming from XSLT [JavaScript method call on a hyperlinks OnClick() method]

function MyTestFunction() {
    context = new SP.ClientContext.get_current();
     list = context.get_web().get_lists().getById(SP.ListOperation.Selection.getSelectedList());
     item = list.getItemById(itemID);

    context.load(item);
    context.executeQueryAsync(
        function(sender, args) {
            currentItemGUID = item.get_item('UniqueId');
        },
        function(sender, args) {
            alert('Request Failed' + args.get_message() + '\nStackTrace: ' + args.get_stackTrace());
        }, "sp.js");
    
}

Adding a Web Part Property

1) Declare a private  variable
        private bool mySampleCheckBox = true;

2) Add the browsable web part property

        [System.Web.UI.WebControls.WebParts.WebBrowsable(true),
        System.Web.UI.WebControls.WebParts.WebDisplayName("Property Display Name on Web Part Edit"),
        System.Web.UI.WebControls.WebParts.WebDescription("Description of the property"),
        System.Web.UI.WebControls.WebParts.Personalizable(
            System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared),
        System.ComponentModel.Category("Category to include the property"),
        System.ComponentModel.DefaultValue(true)
        ]
        public bool SampleCheckBox
        {
            get { return mySampleCheckBox; }
            set { mySampleCheckBox =  value; }
       }

It would be much cleaner to use two using directives to refer System.ComponentModel and System.Web.UI.WebControls.WebParts and then, remove the repeated code above.

You could set the personalization scope to either Shared or User depending on your requirement.
     
Web part property types and their respective display controls in the web part properties pane are as below:

Property Type	Display
string	Textbox
bool	Checkbox
int	Textbox
float	Textbox
DateTime	Textbox
Enum	Dropdown

ADO.Net - SqlDataReader

Below is a sample usage of the SqlDataReader class in ADO.Net.
reader.HasRows checks if any rows are returned.


string connectionString = "Data Source=DataBaseServerName;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=UserName;Password=Password;";
string query = "select eid, name, email from dbo.Employee where status=1";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    connection.Open();

                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("Column1 : {0} | Column2 : {1} | Column3 : {2}", reader[0],                                          reader[1], reader[2]);
                        }
                    }
                    reader.Close();
                }
            }

Download file from SharePoint site to the Server Drive for processing

There could be times where one might need to download a file for processing in the server before rendering. May be for a file type conversion prior to rendering or so...During such instances this can be used. The below code block needs to be run within elevated privileges.


web.AllowUnsafeUpdates = true;
SPFile file = web.GetFile(fileUrl);

using (FileStream outStream = new FileStream("Specify the file Download location in server drive"), FileMode.Create))
{
    byte[] obj = (byte[])file.OpenBinary();
    outStream.Write(obj, 0, obj.Length);
}
web.AllowUnsafeUpdates = false;


Side Note:
You can specify the download location in the web.config <AppSettings> section and retrieved via:
ConfigurationManager.AppSettings["AppSettingName"].ToString();

Built-in functions in SQL - Part 1

There are some important built-in functions in T-SQL which are used on a daily basis. ISNULL() & DATEDIFF() are some of such

1) ISNULL()

ISNULL ( check_expression , replacement_value )
The first parameter is the expression to be checked, secondly we have the result to give out in case the expression is NULL. This can be used to null check column values.

For an instance:
ISNULL(columnName, 0) will give out zero to indicate the value of the particular column is null, so that it can be skipped

Applications:
In a select statement within the where clause ISNULL(columnName, 0)=1 could be used so that any row having null values for this particular column would be skipped.


2) DATEDIFF()

WHERE DATEDIFF(DAY, tableName.DateTimeCloumn, GETDATE()) > 7

DATEDIFF returns the time between two dates. The first parameter is the datepart (eg: day, year, month, etc). The second and third parameters are start date and end date respectively.

Programmatically Adding User to Site - SPRoleAssignment & RoleDefinitionBindings

In this post I'm adding a domain user to a SharePoint 2010 site programmatically.
I am using a simple console application for this purpose. The code is as below:

using Microsoft.SharePoint;

namespace SPTestConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            AddUser();
        }

        static void AddUser()
        {
            string siteUrl = "http://localhost:1000/SitePages/Home.aspx";

            SPSecurity.RunWithElevatedPrivileges(delegate() {
                using (SPSite site = new SPSite(siteUrl))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPRoleDefinitionCollection collroleDefinitions = web.RoleDefinitions;
                        SPRoleAssignmentCollection colllroleAssignments = web.RoleAssignments;

                        SPRoleAssignment oRoleAssignment = new SPRoleAssignment("DOMAIN\\username","user@company.lk", "UserName","Notes");
                        SPRoleDefinitionBindingCollection collRoleDefBinding=oRoleAssignment.RoleDefinitionBindings;
                        collRoleDefBinding.Add(collroleDefinitions["Read"]);
                        colllroleAssignments.Add(oRoleAssignment);
                    }
                }
            });
        }
    }
}


Roll Assignments -> are the permissions in the site &
RoleDefinition -> are the permission levels in the site

the name SPSecurity does not exist in the current context

In SharePoint when using SPSecurity you might come across this error "the name SPSecurity does not exist in the current context"


Probably you are using a sandbox solution. You cannot use SPSecurity in sandbox solutions. Instead, go with a Farm solution in order to run this code.

Creating Content Source and Scope - SharePoint 2007 [Part 1]

Creating a Content Source
In the Central Admin, go to Shared Services.

1) Go to Search Administration page.

2) Select Content Sources

Any web application you create is added into the address list in the default content source which is "Local Office SharePoint Server Sites" 

If you need to have separate search for you web application you can have a separate content source and add a separate search scope for that. This approach can also be used if you need to target your search to retrieve data from target lists.

3) create a new content source, by clicking on the "New Content Source"

Here I have created two new content sources targeting two different search results.

To add web application URL's to the content source you need to go inside the default content source and remove the web application URL first from there, save the content source, and then come back to the newly created content source and include the URL there.

New Farm Administrator - could not be updated because the current user is not a farm administrator

You might have come across situations where a new farm administrator via the Central Admin, but the granted rights to the user account does not work as you expected.

In SharePoint 2010, even though a user is added as farm administrator, you might get an error in the log as "could not be updated because the current user is not a farm administrator".

This is due to the ShrarePoint 2010 security feature, which blocks modifications to any object inheriting from SPPersistedObject in the Microsoft.SharePoint.Administration namespace.

To resolve this issue, run the below script in powershell, which grants access.


function RemoteAdministrator-GiveAccess()                                                                                                
{                                                                                                                                                                 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null                              
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration") > $null       
                                                                                                                                                                   
    $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService                        
    $contentService.RemoteAdministratorAccessDenied = $false                                                                   
    $contentService.Update()                                                                                                                        
}                                                                                                                                                                 
                                                                                                                                                                   
RemoteAdministrator-GiveAccess       




                                                                                                        

Error occurred in deployment step retract solution cannot start service SPUserCodeV4 - SP2010 Foundation

Developers might come across an error in Visual Studio where deployments fails with the error

"error occurred in deployment step retract solution cannot start service SPUserCodeV4 on computer .."

To resolve this issue in SharePoint foundation, follow the below steps:

1) Go to Central Administration
2) Click on "System Settings"
3) Click "Manage Services on Server"
4) The service "Microsoft SharePoint Foundation Sandboxed Code Service" might be stopped. Start it.

Execute Server Process from Server Side Code

Sometimes when dealing with certain scenarios, there comes the question, whether a server side process execution could be initiated through server side code. This could be done. In fact this could be done in SharePoint. The code needs to be run withing elevated privileges.



Process proc = new Process();
proc.StartInfo.FileName = string.Concat(ConfigurationManager.AppSettings["Get The File Path"].ToString(), "MYExecutable.exe");
 proc.StartInfo.Arguments = "\"" + ConfigurationManager.AppSettings["argumentOne"].ToString() + file.Name + "\"" + " -o \"" + ConfigurationManager.AppSettings["argumentTwo"].ToString() + myFile + "\"";

proc.StartInfo.UseShellExecute = false;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
proc.StartInfo.RedirectStandardOutput = true;

proc.Start();
proc.WaitForExit();

ASP.Net Repeater

Rendering a simple repeater

As this is a sample app, prepare a data source. In this example I have come up with a basic DataTable.

            DataTable table = new DataTable("SelectionTable");
            table.Columns.Add(new DataColumn("Name", typeof(string)));
            table.Columns.Add(new DataColumn("Email", typeof(string)));
            table.Columns.Add(new DataColumn("Address", typeof(string)));
            table.Columns.Add(new DataColumn("Phone", typeof(string)));


1)  Add a repeater in the aspx page

The tag in the aspx page when the repaeter is added will be as :

<asp:Repeater ID="EmployeesRepeater" runat="server">

2) Set the Data source to the repeater. 

Here, I set it at the page load method in the code behind page.

            if (table != null)
            {
                //Sample data
                table.Rows.Add("Shaamil", "shaamil@abc.com", "111 aaa", "0111");
                table.Rows.Add("Joe", "joe@abc.com", "222 bbb", "0222");
                table.Rows.Add("Ramesh", "samesh@abc.com", "333 ccc", "0333");
                table.Rows.Add("Rushdi", "rushdi@abc.com", "444 ddd", "0444");
                table.Rows.Add("Sam", "sam@abc.com", "555 eee", "0555");

                //Bind data source
                EmployeesRepeater.DataSource = table;
                EmployeesRepeater.DataBind();
            }

3) Formatting the repeater rendering

<asp:Repeater ID="EmployeesRepeater" runat="server">
   <HeaderTemplate>
     <ul>
  </HeaderTemplate>
  <ItemTemplate>
    <li>
      <span>
          <%# DataBinder.Eval(Container.DataItem, "Name") %>  &nbsp;
          <%# DataBinder.Eval(Container.DataItem, "Address") %>
      </span>
    </li>
  </ItemTemplate>
  <FooterTemplate>
   </ul>
  </FooterTemplate>
</asp:Repeater>


Result

Basic PowerShell for SharePoint and the Equivalent STSADM used in Earlier Version

This is a simple list down of basic PowerShell commands used in command line deployments in SharePoint. [SP 2010 and 2013]  &
The equivalent STSADM commands used in SharePoint 2007 ... back in the day ;)


1. Adding a new WSP to farm solution

SP 2010/2013 PowerShell	Add-SPSolution -LiteralPath "C:\FolderName\Solution.wsp"
SP 2007  Stsadm	stsadm -o addsolution -filename "Solution.wsp" [Assuming command runs from the correct folder location]

2. Deploy WSP 

SP 2010/2013 PowerShell

Install-SPSolution -Identity "Solution.wsp" -WebApplication "http://WebSiteUrl" -CASPolicies -GACDeployment -Local -Force AllWebApplications vs WebApplication - All allows the wsp to be deployed in all SP web applocations. WebApplication - allows the wsp to be deployed to a target web app CASPolicies - Allows Code Access Security Policies to be deployed [optional] GACDeployment - Allows DLL installation in the global assembly cache [optional] Force [optional] Local - Deploys only in the current server [optional]

SP 2007  Stsadm

stsadm -o deploysolution -name "SolutionName" -url "Site URL" -immediate -allowgacdeployment -allowcaspolicies -force instead of -url, you can use -allcontenturls  instead of -immediate, you can use -time "time to deploy" GACDeployment - Allows DLL installation in the global assembly cache [optional]

3. Activate feature

SP 2010/2013 PowerShell	Enable-SPFeature –identity "FeatureName" -URL http://WebSiteUrl
SP 2007  Stsadm	stsadm -o activatefeature -id feature_ID -url http://WebSiteUrl -force [Instead of -Id, you can use -filename or -name with appropriate values]

4. De-activate feature

SP 2010/2013 PowerShell	Disable-SPFeature –identity "FeatureName" -URL http://WebSiteUrl
SP 2007  Stsadm	stsadm -o deactivatefeature -id feature_ID -url http://WebSiteUrl [Instead of -Id, you can use -filename or -name with appropriate values]

5. Retract WSP 

SP 2010/2013 PowerShell	Uninstall-SPSolution -Identity "Solution.wsp" -WebApplication http://WebSiteUrl
SP 2007  Stsadm	stsadm -o retractsolution -name "Solution.wsp" -url http://WebSiteUrl -immediate  [Instead of -immediate, you can use -time "TimeToRun"]

6. Remove WSP from SharePoint farm

SP 2010/2013 PowerShell	Remove-SPSolution -Identity "Solution.wsp"
SP 2007  Stsadm	stsadm -o deletesolution -name "Solution.wsp"

This version of Visual Studio does not have the following project types installed or does not support them

You might come across instances where you get the below message when opening SharePoint solutions in Visual Studio 2012.

"This version of Visual Studio does not have the following project types installed or does not support them"

1) The immediate remedy that pops into mind is to update the Visual Studio 2012 version.

The latest update for Visual Studio 2012, as of now is Update 4 (Released: November 13, 2013). Could get it from the link:  Visual Studio 2012 Update

2) Once that's done, you might get the following message on project load.

"An error occurred while trying to load some required components. Please ensure that prerequisite components are installed"

• Microsoft Web Developer Tools
• Microsoft Exchange Web Services

Possibilities are that either

  You don't have the "Microsoft Office Developer Tools for Visual Studio 2012" OR

  The installed tool is not complete.

Solution:

• If the tool is already installed, uninstall through Control panel,
• Download  Web Platform Installer   
• Use the Web Platform Installer to install the tool.

Join two tables in Update statement [T-SQL]

Two tables can be easily joined during an update, similar to a join used in a select statement. This comes in handy specially when one table needs to be updated based on a condition in another table. Below is one such example.

UPDATE C

SET C.permanentAddress='#000, Marine Drive, Galle, Sri Lanka'

FROM Customer AS C INNER JOIN users AS U

ON U.userId = C.customerId

WHERE U.loginName LIKE('%ashaamil%')


That's pretty much about it !

SharePoint Web Parts Maintenance Page

SharePoint web part maintenance pages allows to manage (reset, delete) the web parts embedded into a particular page. It's useful in situations where its required to remove any web parts from a page which has caused a page crash.

For an instance if the page urls is:

http://localhost:1000/SitePages/Home.aspx

you can get the maintenance page by typing in "?contents=1" at the end of the URL string. Below is a sample:

http://localhost:1000/SitePages/Home.aspx?contents=1

This works the same in  SharePoint 2007, 2010 and 2013