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;
            }
        }

Featured Post

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management https://docs.microsoft.com/en-us/azure/api-management/api-...

Popular posts