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:- 

http://stackoverflow.com/questions/14149346/what-value-should-i-pass-into-timezoneinfo-findsystemtimezonebyidstring 

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

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

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;

            }

        }