Post Updated values only, using Entity Framework C#

Scenario:
What if One wants to update the values from new model to an existing Entity.

E.g. We have User Entity as follows:

Public class User
{
 public long Id { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }
}

/*************** OLD APPROACH *********************/

Update(user updatedUser)
{
var model = context.Users.Where(u => u.Id == Id).FirstOrDefault();
if(model !=null)
{
model.FirstName =updatedUser.FirstName;
model.LastName =updatedUser.LastName;

context.Users.Update(model);
await context.SaveChangesAsync();
}
}



/*************** BETTER APPROACH *********************/


//Generic Method
/// <summary>
        /// This function actually updates the properties of 'target' object based on the values in the 'Source' Object, with the condition: Both Objects must have same type
        /// </summary>
        /// <typeparam name="T">The Generic Entity</typeparam>
        /// <param name="source">The current Object having new Values</param>
        /// <param name="target">The current Object to which new values are to be updated</param>
        public static void CopyValues<T>(T source, T target)
        {
            Type t = typeof(T);

            var properties = t.GetProperties().Where(prop => prop.CanRead && prop.CanWrite);

            foreach (var prop in properties)
            {
                var value = prop.GetValue(source, null);
                if (value != null)
                    prop.SetValue(target, value, null);
            }
        }

//Update Method

Update(user updatedUser)
{
var model = context.Users.Where(u => u.Id == Id).FirstOrDefault();
if(model !=null)
{
model.FirstName =updatedUser.FirstName;
model.LastName =updatedUser.LastName;

CopyValues(updatedUser,model);

context.Users.Update(model);
await context.SaveChangesAsync();
}
}


Happy Coding!!

Convert different Date format to the SQL Acceptable one.

Declare @DateTimeValue nvarchar(100)= '03/01/2017 13:15'


--Date Value
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,LEFT(@DateTimeValue,10),105),101);

--Date Time Value
Select Cast(CONVERT(VARCHAR(24), CONVERT(DATETIME, max(@DateTimeValue), 103), 101)+' '+ CONVERT(VARCHAR(5), CONVERT(DATETIME, max(@DateTimeValue), 103), 108) as datetime)

SQL: Trimming all the column values using LTRIM and RTRIM dynamically

The below SQL Dynamic Query will remove all the white spaces from each column of datatype varchar or nvarchar using LTRIM and RTRIM.

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'Users'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
    AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'nvarchar')

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL

PRINT @SQL
EXEC(@SQL)

Display basic time value (in 12-hours format) using jquery in .Net Web Apps.

Design view (.cshtml Razor View where the div was taken to show the time value)

@{
                        DateTime dt = DateTime.Now;
                        var time = dt.ToString("hh:mm tt");
                        var tArr = time.Split(' ');
                    }

<div class="date_time">
                       @* Displaying the date value *@
                        <div class="date" id="divDateOnly"><i class="fa fa-calendar" aria-hidden="true"></i>@dt.ToString("MMM - dd - yyyy")</div>

                       @* Displaying the time value and this needs to be refreshed every second. *@
                        <div class="time" id="divTimeOnly"><i class="fa fa-clock-o" aria-hidden="true"></i>@tArr[0]_@tArr[1]</div>
</div>


/* function to convert the date to time value in 12-hour format */
function formatAMPM(date) {
            var hours = date.getHours();
            var minutes = date.getMinutes();
            var ampm = hours >= 12 ? 'pm' : 'am';
            hours = hours % 12;
            hours = hours ? hours : 12; // the hour '0' should be '12'
            hours = hours < 10 ? '0'+ hours : hours;
            minutes = minutes < 10 ? '0'+ minutes : minutes;
            var strTime = hours + ':' + minutes + ' ' + ampm;
            return strTime;
        }

/* set the interval at 1 second at document ready function */
$(document).ready(function(){
     setInterval(function() {
                var d = new Date();
                var monthName = monthNames[d.getMonth()];
                var day = d.getDate();
                var year = d.getFullYear();
                var time = formatAMPM(d);
                var tArray = time.split(' ');
                console.log(time);
                $("#divTimeOnly").html('<i class="fa fa-clock-o" aria-hidden="true"></i>' + tArray[0] +                      '_{' + tArray[1] + '}');
                },1000);
});

C# Code Builder from Stored Procedure

Generic Stored Procedure to write front-end code at DAL Layer.

Usage:

EXEC SPROC_GenerateCSCodeBuilder 'Name of Stored Procedure'

Output:

It will return the front-end C# code with the usage of that Stored Procedure referenced, with sql parameters if any.

Example:

/* Use database */ 

Use YourDatabaseName

/* Calling the Proc, with the Stored Procedure name as input parameter */

EXEC SPROC_GenerateCSCodeBuilder 'TestSProcName'

/* OUTPUT */

try

   {

   SqlParameter[] sqlParams = new SqlParameter[4];

 

   sqlParams[0] = new SqlParameter("@pOldName", SqlDbType.NVarChar);

   sqlParams[0].Value = ?;

   sqlParams[0].Size=510;

   sqlParams[1] = new SqlParameter("@pNewName", SqlDbType.NVarChar);

   sqlParams[1].Value = ?;

   sqlParams[1].Size=510;

   sqlParams[2] = new SqlParameter("@pDirPath", SqlDbType.NVarChar);

   sqlParams[2].Value = ?;

   sqlParams[2].Size=2000;

   sqlParams[3] = new SqlParameter("@pExtension", SqlDbType.NVarChar);

   sqlParams[3].Value = ?;

   sqlParams[3].Size=40;

 

   SqlHelper.ExecuteNonQuery(sqlCon, CommandType.StoredProcedure,"SPROC_UpdateFileName", sqlParams);

 

   }

catch(Exception excp)

   {

   }

finally

   {

   sqlCon.Dispose();

   sqlCon.Close();

   }

Here's that stored procedure:

Create  PROCEDURE SPROC_GenerateCSCodeBuilder
(
@objName nvarchar(100)
)
AS
/*
Name:   DAL Layer Method BUilder (currently based on SQLHelper class)
Description:
  Call this stored procedue passing the name of your
  database object that you wish to insert/update
  from .NET (C#) and the code returns code to copy
  and paste into your application. This version is
  for use with "Microsoft Data Application Block".
a) Updated to include 'UniqueIdentifier' Data Type
b) Support for 'ParameterDirection.Output'
*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)

--Change the following variable to the name of your connection instance
SET @connName='sqlCon'
SET @parameterAt=''
SET @outputValues=''
SELECT
  dbo.sysobjects.name AS ObjName,
  dbo.sysobjects.xtype AS ObjType,
  dbo.syscolumns.name AS ColName,
  dbo.syscolumns.colorder AS ColOrder,
  dbo.syscolumns.length AS ColLen,
  dbo.syscolumns.colstat AS ColKey,
  dbo.syscolumns.isoutparam AS ColIsOut,
  dbo.systypes.xtype
INTO #t_obj
FROM        
  dbo.syscolumns INNER JOIN
  dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
  dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE    
  (dbo.sysobjects.name = @objName)
  AND
  (dbo.systypes.status <> 1)
ORDER BY
  dbo.sysobjects.name,
  dbo.syscolumns.colorder

SET @parameterCount=(SELECT count(*) FROM #t_obj)


IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
BEGIN
  PRINT 'try'
  PRINT '   {'
  PRINT '   SqlParameter[] sqlParams = new SqlParameter[' + cast(@parameterCount as varchar) + '];'
  PRINT ''
 
  DECLARE @source_name nvarchar,@source_type varchar,
    @col_name nvarchar(100),@col_order int,@col_type varchar(20),
    @col_len int,@col_key int,@col_xtype int,@col_redef varchar(20), @col_isout tinyint

  DECLARE cur CURSOR FOR
  SELECT * FROM #t_obj
  OPEN cur
  -- Perform the first fetch.
  FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

  if(@source_type=N'U') SET @parameterAt='@'
  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN 'Image'
WHEN 35 THEN 'Text'
WHEN 36 THEN 'UniqueIdentifier'
WHEN 48 THEN 'TinyInt'
WHEN 52 THEN 'SmallInt'
WHEN 56 THEN 'Int'
WHEN 58 THEN 'SmallDateTime'
WHEN 59 THEN 'Real'
WHEN 60 THEN 'Money'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Float'
WHEN 99 THEN 'NText'
WHEN 104 THEN 'Bit'
WHEN 106 THEN 'Decimal'
WHEN 122 THEN 'SmallMoney'
WHEN 127 THEN 'BigInt'
WHEN 165 THEN 'VarBinary'
WHEN 167 THEN 'VarChar'
WHEN 173 THEN 'Binary'
WHEN 175 THEN 'Char'
WHEN 231 THEN 'NVarChar'
WHEN 239 THEN 'NChar'
ELSE '!MISSING'
END AS C)

--Write out the parameter
PRINT '   sqlParams[' + cast(@col_order-1 as varchar)
   + '] = new SqlParameter("' + @parameterAt + @col_name
   + '", SqlDbType.' + @col_redef
   + ');'

--Write out the parameter direction it is output
IF(@col_isout=1)
BEGIN
PRINT '   sqlParams['+ cast(@col_order-1 as varchar) +'].Direction=ParameterDirection.Output;'
SET @outputValues=@outputValues+'   ?=sqlParams['+ cast(@col_order-1 as varchar) +'].Value;'
END
ELSE
BEGIN
--Write out the parameter value line
    PRINT '   sqlParams['+ cast(@col_order-1 as varchar) + '].Value = ?;'
END
--If the type is a string then output the size declaration
IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
BEGIN
PRINT '   sqlParams[' + cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';'
END

-- This is executed as long as the previous fetch succeeds.
      FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
  END
  PRINT ''
  PRINT '   SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' + @objName + '", sqlParams);'
  PRINT @outputValues
  PRINT '   }'
  PRINT 'catch(Exception excp)'
  PRINT '   {'
  PRINT '   }'
  PRINT 'finally'
  PRINT '   {'
  PRINT '   ' + @connName + '.Dispose();'
  PRINT '   ' + @connName + '.Close();'
  PRINT '   }'
  CLOSE cur
  DEALLOCATE cur
 END
if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON

SQL Command using SQLCMD utility to run the large SQL Script Files

c:/>sqlcmd -U <username> -P <pwd> -S <servername> -i <SQL Script File Path>

Query to All / Selected tables using undocumented SQL Procedure sp_msforeachtable

In this article, we will see basic use of undocumented procedure sp_MSforeachtable 
E.g. Adding any column to each user-defined table in SQL Server:

exec sp_msforeachtable 'alter table ? add flag bit not null default 0';