Change All Data Type of column from different table to new one

Things that we considered

  • Default Constraint
  • Nullable
  • Renamed Default constraint name to df_tablename_column_name format
declare @tData varchar(25) = ' decimal(14,2) ';
--1. for cols without default constraint
SELECT N'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] +
' alter column ' + C.[name] + @tData +
case when C.[is_nullable] = 0 then '
not null ' else ' null ' end + '; '
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]
INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]
WHERE T.[is_ms_shipped] = 0 AND DTY.name = 'decimal' and C.default_object_id = 0
ORDER BY T.[name], C.[column_id]
--2. for cols with default constraint
SELECT N'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' drop constraint ' + df.[name] + '; ' +
'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] +
' alter column ' + C.[name] + @tData +
case when C.[is_nullable] = 0 then '
not null ' else ' null; ' end +
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) + '.' + T.[name] + ' ADD CONSTRAINT DF_'+ T.[name] +'_' + C.[name] + ' DEFAULT 0 FOR ' + c.[name] + '; '
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]
INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id] AND C.[user_type_id] = DTY.[user_type_id]
inner join sys.default_constraints df on C.default_object_id = df.[object_id]
WHERE T.[is_ms_shipped] = 0 AND DTY.name = 'decimal'
ORDER BY T.[name], C.[column_id]

handy css classes with js function

  • clickLoadAjaxModal
  • clickLoadPage
  • [data-poload]
  • ajaxLoad
  • showLoading
  • hideLoading
  • submitForm
  • cmdPrint
  • CheckAll
  • ajaxSubmit
  • showLoading [Type: function]: this function can be called anytime before calling ajax stuffs, it will simply show loading icon in screen
  • hideLoading [type: function]:
    This function can be used to close loading icon in screen once ajax process is completed
  • cmdPrint [type: css class]:
    this class can be assigned to any a tag or btn, and it will trigger print dialog box
  • ajaxLoad [type: class]
    this class can be assigned to any div and it will load page content specified on data-url attribute on page load. to reload the page content again, we can trigger refresh event also
  • clickLoadPage [type: class]
    this class can be assigned to any div or btn, it will load content of page that has been specified in data-url attribute or href attribute to div refered on data-target attribute
  • clickLoadAjaxModal [type: class]
    this class can be used to any btn, it will load content of page that has been specified in data-url attribute and load to modal body and show modal dialog box
  • submitForm [type: class]
    this class can be used to any btn, it will find the target form mentioned in data-target attribute and submit it after validation
  • ajaxSubmit [type: class]
    this class can be used to any form, it will then enable ajax submission of form instead of regular submit.

Notes for Migrating WP Site from one domain to another

for migration of cpanel of wordpress site of one domain to another ->

  1. copy files from old cpanel -> to new cpanel
  2. export db from old cpanel -> import in new cpanel
  3. for above step 2 -> create db in new server,
  4. for above step 2 -> create db user and db pass in new server,
  5. for above step 2 -> assign db user with db access,
  6. using phpmyadmin in new server, fix site_url in wp_options table

In some cases we also need to replace/fix image path location inside post/pages also. For that we will use sample codes like ->

update table wp_posts set content = replace(content, ‘old_path’, ‘new_path’)

Help Doc For Dynamic ShowHideColumns Related to DataTables

Snippet

Sample HTMl
Snippet
<div class="dropdown showHideColumns" data-target="mainTable1">     <button class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
         <i class="fa fa-gear"></i>
         Show Hide Columns
         <span class="caret"></span>
     </button>
     <ul class="dropdown-menu"></ul> 
</div> 

Here data-target=”mainTable1″ -> here mainTable1 represents the id for datatable

Required Dependent JS
Snippet
function ShowHideColumn(target, colIndex) {
	var mTable = '#' + target;
	var myDataTable = $(mTable).DataTable();
	// Get the column API object
	var column = myDataTable.column(colIndex);

	// Toggle the visibility
	column.visible(!column.visible());
}
$(document).on('click', 'a.toggle-vis', function (e) {
	e.preventDefault();
	ShowHideColumn($(this).data('target'), $(this).attr('data-column'));
});
$(document).on('change', '.toggle-vis', function (e) {
	e.preventDefault();
	ShowHideColumn($(this).data('target'), $(this).attr('data-column'));
});
$(document).ready(function (e) {
	$('.showHideColumns').each(function (item, index) {
		var t = $(this).data('target');
		var thDrop = $(this).find('.dropdown-menu');
		thDrop.html('');
		if ($('#' + t).length >= 1) {
			$('#' + t).find('thead th').each(function (index) {
				thDrop.append('<li><a href="#"><input type="checkbox" checked="" class="toggle-vis" data-column="' + index + '" data-target="' + t + '">' + $(this).text() + '</a></li>');
			});
		}
	});
});

EF Context CheckList

Normal General Processing Synchronous

  • EF DB Context Data Insert
  • EF DB Context Data Update
  • EF DB Context Data Select
  • EF DB Context Data Delete

Bulk Processing Synchronous

  • EF DB Context Data Insert
  • EF DB Context Data Update
  • EF DB Context Data Select
  • EF DB Context Data Delete

Normal General Processing Asynchronous

  • EF DB Context Data Insert
  • EF DB Context Data Update
  • EF DB Context Data Select
  • EF DB Context Data Delete

Bulk Processing Asynchronous

  • EF DB Context Data Insert
  • EF DB Context Data Update
  • EF DB Context Data Select
  • EF DB Context Data Delete

For Ajax Add Page

c# aspt.net controller part

region url for ajax load blank form

  • create async partiaviewresult action method
  • load respective add form in partial view

region url for ajax submit/save

  • Create async JSONResult action method
  • grab/get posted data
  • save them to table
  • if success return json with status true and data with recently added id
  • if fail return json with status false and errmsg with proper message

region url for ajax refresh combobox

  • create async jsonresult action method
  • get data from respective table/database
  • return json with id/value field related to dropdown

JS Part

  • Load Form
    • call refresh/load data for dropdown from refresh url
  • when clicked + button
    • show popup modal box with ajax loading of add form
    • when submit button of add form is clicked
      • validate
      • ajax submit [json
      • onsuccess
        • check result status
        • if true then
          • reload list of data in dropdown box
          • mark selected to recently added one [auto selected]
        • if false show error msg
        • close dialog box
      • on failure
        • show err msg
        • close dialog box

Getting Started with .Net MVC c#

Using DB First Approach

Database Part MSSQL Management Studio

  • Create DB Tables,
  • Views and
  • Stored Procedure as requirement

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create EDMX Model from Database to project in DAL Project
  • Build Project
  • Add scaffolding Controllers of simple tables
  • Test Application

Using Code First Approach

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create Models and DB Context required for matching database structure
  • Build Project
  • Add scaffolding Controllers
  • Test Application

Common Try outs

  • Analysis of Bind Values, Post Variable capture
  • Necessary change implementation of code for edit method of scaffolding for adapting changes related to hidden and readonly values
  • Add Scaffolding Controllers of Complex tables which has relation/related data with another table and should show its selection in dropdown
  • Know difference between Sync and Async Methods
  • Try Master/Detail Form data entry/edit pages
  • Try Form with Checkboxes and use some nice toggle buttons for checkboxes
  • Use EditorTemplates for Create/Edit Page
  • Use DisplayTemplates for Detail/Delete Page
  • Use of TempData for message display/variable sharing between pages
  • Use of Sessions for Tracking user logins and browsing sessions
  • Use of Partial views for AJAX page result
  • use of JSON Result format for better AJAX Result
  • Use of View-Models for display of data in combined/complex page designs
  • Use of Auto-Mappers for conversion between Domain Models -> View Models and vice-versa

UI Additional Tryouts

Use of bootstrap Component like

TABS, Navs, Chosen [with ajax search], multi select-chosen, CARDS, Panels, Tooltips, Modal, fa icons, collapsible panel, accordions, date-picker, time-picker, tags

Additional Tryouts

  • Separation of DAL project with UI Project
  • Addition of BAL project in solution for application logic
  • Addition of DAL Metadata for validation and label changes
    • Direct in class Indirect using partial class which will be un-affacted in case of db-first model updates
  • Use UIHint to change display of boolean values types to nice Yes/No label in display format and toggle button in editor format
  • Customization of db query with linq for server side filtration and querying for data with criteria
  • Grouping of List data with certain members that can be helpful for report generation
  • Use of Dictionary Data type for quick access of key value data types
  • Use of Dependency Injection for Repo/Service class initialization
  • Use of Repo Class to follow repository pattern
  • Use of Service Class in BAL project to add Business Logic Layer
  • Use of try/catch and Unit or Work Pattern
  • Use of Error Logging frameworks for tracking errors of applications
  • Use of Generic Repo and Generic Services for quick application development process.

Our Basic Classes Intro

  • Use of ViewHelper for formatting number/currencies
  • Use of Flashbag for quick support of TempData
  • Use of NepaliCalendar for General Date conversion
  • Use of RestSharp Nuget Package for quick API consumption

JS Charts

  • d3 charts [pie, line, etc]

Steps to add model for MFI UI project which is same as API Request Model

  • Add Common Models to MFI_API_MODEL Project
  • Build project and make error free
  • Change Nuget Package Version in MFI_API_MODEL.nuspec file
  • Generate Nuget Package from CMD using command:
    • nuget pack mfi_api_model.nuspec -prop configuration=release
  • Host Nuget Package to accessible server location in Ants or local computer
  • Open Own UI project,
  • Update Package version

Basic Controller Variables

  • If controller is related to central only i.e it contains dbCore only it should inherit from BaseAsyncController
  • If controller is related to Organisation also i.e it contains both dbCore and dbOrg it should inherit from BaseAsyncFOController
  • While inheriting Any Controller from BaseAsyncController or BaseAsyncFOController, it should initialise base controller with respective variables also.
  • eg For BaseAsyncFOController:
using System;
 
public class DataMaintenanceController : BaseAsyncFOController
{
    #region Constructor
    public DataMaintenanceController(
        CoreAccountingMasterEntities dbCore
        , OrgAccDBEntities dbOrg
        , IErrorLog ErrorLog
        , DBHelper DBHelper
        , IAuthServices AuthService
        //,... extra
        )
    : base(dbCore, AuthService, ErrorLog, dbOrg, DBHelper)
    {
        //..extra code
    }
    #endregion
}
  • eg For BaseAsyncController
using System;
 
public class DataMaintenanceController : BaseAsyncController
{
    #region Constructor
    public DataMaintenanceController(
        CoreAccountingMasterEntities dbCore
        ,IErrorLog ErrorLog
        ,IAuthServices AuthService
        //,.. extra
        )
    : base(dbCore, AuthService, ErrorLog)
    {
        //..extra code
    }
    #endregion
}
  • The Result controller must be inside autofac config/startup file in order to make it start automatically. File: App_start/AutoFacConfig.cs
  • Code to Init Controller:
  • builder.RegisterType<DataMaintenanceController>();//.InstancePerRequest();
  • If the controller already contains variable for dbCore or dbOrg or AuthService or ErrorLog or DBHelper, then we should rename that variable to matching variable name with our BaseAsyncFOController or BaseAsyncController.
  • Our Variable Name in BaseAsyncController and BaseAsyncFOController are:
  • _dbCore, _dbOrg, _AuthService, _ErrorLog, _DBHelper
  • Also Remove base Controller variable initialisation in derived controller.