Thursday, January 29, 2015

Convert a datetime to specific timezone using Java Script, C#.Net, SQL

Convert a datetime to specific timezone using Java Script, C#.Net, SQL
Here for example, I am taking Korea Time Zone.
1.  In java script: - to get Korea time zone - 3600000
2.  In C#:- to get Korea time zone: - TimeZoneInfo.FindSystemTimeZoneById("Korea Standard Time");
3.  In stored procedure: - to get Korea time zone: - +09:00

Java Script:-
<link type="text/css" rel="stylesheet" href="http://code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.11.1/jquery-ui.js"></script>
<script type="text/javascript">
$(function () {
        $('#<%=txtDt.ClientID %>').datepicker({
            onSelect: function () {
                var dtPickerDate = this.value.split('/');
                $('#<%=txtDt.ClientID %>').val(getDateTime(dtPickerDate[0], dtPickerDate[1], dtPickerDate[2]));
            },
            showOn: "button",
            buttonImage: "/_layouts/images/Calendar.png",
            buttonImageOnly: true,
            buttonText: "Select date",
            dateFormat: 'mm/dd/yy',
            minDate: '0'
        });
    });
</script>
<script type="text/javascript"> 
var offset = '+9';
var month;
var day;
var var_hour;
var var_minutes;
var am_pm; 
function getDateTime(month, day, year) { 
    d = new Date();
    d.setMonth(month - 1);
    d.setDate(day);
    d.setFullYear(year);
    utc = d.getTime() + (d.getTimezoneOffset() * 60000);
    nd = new Date(utc + (3600000 * offset)); 
    //  "The local time in " + city + " is " + nd.toLocaleString(); 
    //        var var_hour = nd.getHours();
    //        var var_minutes = nd.getMinutes();
    //        var am_pm = ''; 
    //        if (parseInt(var_hour) > 11) {
    //            var_hour = parseInt(var_hour) - 12;
    //            am_pm = 'PM';
    //        }
    //        else {
    //            am_pm = 'AM';
    //        } 
    month = nd.getMonth() + 1;
    month = month < 10 ? '0' + month : month;
    day = nd.getDate() < 10 ? '0' + nd.getDate() : nd.getDate(); 
    //var newDate = nd.getMonth() + 1 + "/" + nd.getDate() + "/" + nd.getFullYear() + " " + var_hour + ":" + var_minutes + " " + am_pm; 
    return month + "/" + '' + day + "/" + nd.getFullYear();
}
</script> 

C# Code Behind:- 
internal static DateTime getDateTime()
        {
            DateTime dateTime = DateTime.Now;
            try
            {
                TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Korea Standard Time");
                dateTime = TimeZoneInfo.ConvertTime(DateTime.Now, timeZoneInfo);
                //strToday = dateTime.ToString("MM/dd/yyyy");               
            }
            catch (Exception ex)
            {                
            }
            return dateTime;
        }

For Stored Procedure:- 
ALTER FUNCTION [dbo].[Get_Converted_Date_Time]
(
      -- Add the parameters for the function here
     )
RETURNS [nvarchar] (255)
AS
BEGIN
      -- Declare the return variable here
      DECLARE @Result [nvarchar] (255)

      -- Add the T-SQL statements to compute the return value here
      SELECT @Result = CONVERT(varchar(10), SWITCHOFFSET(convert(datetimeoffset,GETUTCDATE()),'+09:00'), 101)

      -- Return the result of the function
      RETURN @Result 
END

Friday, January 23, 2015

Insert multiple records to a Stored Procedure in SQL Server using XML in C#

Insert multiple records to a Stored Procedure in SQL Server using XML in C#

SharePointProject2.aspx:-
<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>
<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls"
    Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ApplicationPage1.aspx.cs"
    Inherits="SharePointProject2.Layouts.SharePointProject2.ApplicationPage1" DynamicMasterPageFile="~masterurl/default.master" %>

<asp:Content ID="PageHead" ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">
</asp:Content>
<asp:Content ID="Main" ContentPlaceHolderID="PlaceHolderMain" runat="server">
    <table id="tbl" runat="server">
        <tr>
            <td>
                <asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
            </td>
        </tr>
    </table>
</asp:Content>
<asp:Content ID="PageTitle" ContentPlaceHolderID="PlaceHolderPageTitle" runat="server">
    Application Page
</asp:Content>
<asp:Content ID="PageTitleInTitleArea" ContentPlaceHolderID="PlaceHolderPageTitleInTitleArea"
    runat="server">
    My Application Page
</asp:Content>

SharePointProject2.aspx.cs
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SharePointProject2.Layouts.SharePointProject2
{
    public partial class ApplicationPage1 : LayoutsPageBase
    {
        SqlConnection conn = null;
        SqlDataReader rdr = null;
        SqlCommand cmd = null;
        string ConnectionString = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB_NAme;Data Source=Server_Name";
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            StringBuilder sbXML = new StringBuilder();
            sbXML.Append("<XML>");
            sbXML.Append("<Employee>");
            sbXML.Append("<Items>");

            sbXML.Append("<Item>");
            sbXML.Append("<ID>");
            sbXML.Append("1");
            sbXML.Append("</ID>");
            sbXML.Append("<Name>");
            sbXML.Append("emp name 1");
            sbXML.Append("</Name>");
            sbXML.Append("</Item>");

            sbXML.Append("<Item>");
            sbXML.Append("<ID>");
            sbXML.Append("2");
            sbXML.Append("</ID>");
            sbXML.Append("<Name>");
            sbXML.Append("emp name 2");
            sbXML.Append("</Name>");
            sbXML.Append("</Item>");

            sbXML.Append("</Items>");
            sbXML.Append("</Employee>");
            sbXML.Append("</XML>");

            using (conn = new SqlConnection(ConnectionString))
            {
                using (cmd = new SqlCommand("spEmployee_Insert", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@strRequestXML", sbXML.ToString()));

                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Open();
                    }

                    rdr = cmd.ExecuteReader();

                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
}

Create Table in SQL:-
CREATE TABLE [dbo].[Employee](
      [ID] [int] NOT NULL,
      [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create a Stored Procedure “spEmployee_Insert”:-
ALTER PROCEDURE [dbo].[spEmployee_Insert]
      -- Add the parameters for the stored procedure here
      @strRequestXML    XML   =     NULL
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      DECLARE @spsDoc   INT
           
      EXEC sp_xml_preparedocument @spsDoc OUTPUT, @strRequestXML

    -- Insert statements for procedure here
    BEGIN TRY
           
            BEGIN TRAN
            IF OBJECT_ID('tempdb..#TempEmployee') IS NOT NULL 
            DROP TABLE #TempEmployee
                                         
            SELECT      P.ID,
                        P.Name
            INTO  #TempEmployee          
            FROM  OPENXML(@spsDoc, 'XML/Employee/Items/Item')
            WITH 
            (
                  ID int  'ID',
                  Name [nvarchar] (50) 'Name'
            )AS P
                                               
            SELECT * FROM [#TempEmployee]
           
            INSERT INTO [dbo].[Employee]
            (
                  [ID],
                  [Name]
            )
            SELECT * FROM [#TempEmployee]
              
            COMMIT TRAN
            END TRY
            BEGIN CATCH
            SELECT
                  ERROR_NUMBER() AS ErrorNumber,
                  ERROR_SEVERITY() AS ErrorSeverity,
                  ERROR_STATE() AS ErrorState,
                  ERROR_PROCEDURE() AS ErrorProcedure,
                  ERROR_LINE() AS ErrorLine,
                  ERROR_MESSAGE() AS ErrorMessage;

            IF @@TRANCOUNT > 0
            ROLLBACK TRAN
        
      END CATCH
END

Friday, January 9, 2015

Adding list items to a folder with in the list, SharePoint add item to custom list results in “Invalid URL value. A URL field contains invalid data. Please check the value and try again”

protected void submit_click(object sender, EventArgs e)
        {
            using (SPSite site = SPContext.Current.Site)
            {
                SPWeb web = site.OpenWeb();
                web.AllowUnsafeUpdates = true;

                bool isFolderExist = false;
                DateTime dt = DateTime.Now;
                SPListItem folderColl_item;
                SPListItemCollection folderColl;

                SPList list = web.Lists["ListName"];

                folderColl = list.Folders;
                foreach (SPListItem splistitem in list.Folders)
                {
                    SPFolder folder = splistitem.Folder;
                    if (folder.Name.Equals(dt.Year.ToString()))
                    {
                        isFolderExist = true;
                    }
                }

                if (!isFolderExist)
                {
                    folderColl_item = list.Items.Add(list.RootFolder.ServerRelativeUrl, SPFileSystemObjectType.Folder, dt.Year.ToString());
                    folderColl_item.Update();
                    list.Update();
                }

                folderColl = list.Folders;
                foreach (SPListItem splistitem in list.Folders)
                {
                    SPFolder folder = splistitem.Folder;
                    if (folder.Name.Equals(dt.Year.ToString()))
                    {
                        SPListItemCollection itemColl = list.Items;
                        SPListItem item = itemColl.Add(folder.ServerRelativeUrl, SPFileSystemObjectType.File, null);

                        item["test"] = "test";
                        item.Update();
                        list.Update();
                    }
                }

                web.AllowUnsafeUpdates = false;
            }
        }