Web & Software Techniques and Tips

No creatable public component detected

2021-03-17T03:52:00.000-07:00

Error : "No creatable public component detected"

When trying to recompile a ActiveX.dll in VB6, I get the Message "No Creatable Public Component Detected There was no error in code .

Solution:

In the ActiveX DLL projects you need to have at least 1 class module with
the Instancing property set to MultiUse or GlobalMultiuse. Open up each
class module in the IDE and check the properties window

Just change the instancing to Multiuse or GlobalMultiuse:

Add an XML Sitemap to your Blogger Blog

2021-03-17T03:07:00.002-07:00

The XML Sitemap file is like a directory of all web pages that exist on your website or blog. Google, Bing and other search engines can use these sitemap files to discover pages on your site that their search bots may have otherwise missed during regular crawling.

The Problem with Blogger Sitemap Files

A complete XML sitemap file should mention all pages of a site but that’s not the case if your blog is hosted on the Blogger or blogspot platform.

Google accepts sitemaps in XML, RSS, or Atom formats. They recommend use both XML sitemaps and RSS/Atom feeds for optimal crawling.

The default atom RSS feed of any Blogger blog will have only the most recent blog posts – see example. That’s a limitation because some of your older blog pages, that are missing in the default XML sitemap file, may never get indexed in search engines. There’s however a simple solution to fix this problem.

Generate XML Sitemap for your Blogger Blog

This section is valid for both regular Blogger blogs (that have a blogspot.com address) and also the self-hosted Blogger blogs that use a custom domain (like postsecret.com).

Here’s what you need to do to expose your blog’s complete site structure to search engines with the help of an XML sitemap.

Open the Sitemap Generator and type the full address of your Blogger blog.
Click the Generate Sitemap button and this tool will instantly create the XML file with your sitemap. Copy the entire text to your clipboard.
Next, go to your Blogger.com dashboard, navigate to Settings –> Search Preferences, enable Custom robots.txt option (available in the Crawling and Indexing section). Paste the XML sitemap here and save your changes. And we are done. Search engines will automatically discover your XML sitemap files via the robots.txt file and you don’t have to ping them manually.

Internally, the XML sitemap generator counts all the blog posts that are available in your Blogger blog. It then splits the posts in batches of 500 posts each and generates multiple XML feed for each batch. Thus search engines will be able to discover every single post on your blog since it would be part of one of these XML sitemaps.

Install and uninstall Windows services using InstallUtil.exe utility

2021-03-16T04:00:00.001-07:00

Install using InstallUtil.exe utility

From the Start menu, select the Visual Studio <version> directory, then select Developer Command Prompt for VS <version>.
The Developer Command Prompt for Visual Studio appears.
Access the directory where your project's compiled executable file is located.
Run InstallUtil.exe from the command prompt with your project's executable as a parameter:

installutil <yourproject>.exe

you’re using the Developer Command Prompt for Visual Studio, InstallUtil.exe should be on the system path. Otherwise, you can add it to the path, or use the fully qualified path to invoke it. This tool is installed with the .NET Framework in %WINDIR%\Microsoft.NET\Framework[64]\<framework_version>.

For example:

For the 32-bit version of the .NET Framework 4 or 4.5 and later, if your Windows installation directory is C:\Windows, the default path is C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe.
For the 64-bit version of the .NET Framework 4 or 4.5 and later, the default path is C:\Windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe.

Uninstall using InstallUtil.exe utility

1. From the Start menu, select the Visual Studio <version> directory, then select Developer Command Prompt for VS <version>.

The Developer Command Prompt for Visual Studio appears.

2. Run InstallUtil.exe from the command prompt with your project's output as a parameter:

installutil /u <yourproject>.exe

3. After the executable for a service is deleted, the service might still be present in the registry. If that's the case, use the command sc delete to remove the entry for the service from the registry.

Refresh the list of remote branches in Visual Studio Team Explorer Visual Studio 2017/2015/2019

2021-03-15T04:33:00.000-07:00

Visual Studio 2019

By default the Visual Studio Team Explorer does not refresh the remote branches when you fetch updates from the server. The fix for this is pretty easy, you need to set "Prune remote branches during fetch" to true in the "Git Settings". You can do this in the global settings or per repository.

Go to Team Explorer settings
Select either "Global Settings" or "Repository Settings"
Set "Prune remote branches during fetch" to true

Prune remote branches during fetch And your done, now the remote branches will be updated every time you fetch or pull.

Visual Studio 2015/2017 - Refresh remotes/origin

Right-click on your local repo and select Open Command Prompt
run git fetch --prune

Calculate Date and Time Difference Between Two Dates C#

2021-03-15T04:13:00.002-07:00

A very common requirement is to display the Date and Time difference between two dates.

Let us see how to do it:

C#

public class Program

{

public static void Main()

{

DateTime date1 =

System.Convert.ToDateTime("2009-1-01 11:08:22");

DateTime date2 =

System.Convert.ToDateTime("2009-1-02");

TimeSpan ts = new TimeSpan();

ts = date2.Subtract(date1);

Console.WriteLine(ts.Days + " Days " + ts.Hours + " Hours " + ts.Seconds + " Seconds ");

}

Output:

0 Days 12 Hours 38 Seconds

Copy paste not working issue in Remote Desktop (RDP)

2021-03-15T03:46:00.000-07:00

Problem/Issue

The clipboard sharing is handled using a small application running on the remote machine, called "RDPCLIP.EXE". If this application fails to do the job, your clipboard sharing will stop working and you will not be able to copy paste text, data or files from local computer to remote server using remote desktop connection.

Solution:

Here is how you can solve this problem:

1. Login to remote computer using Remote Desktop (RDP)
2. Open Task Manager in the remote machine
3. Click the "Process" tab
4. Locate a program called "rdpclip.exe"
5. Right click and select "End process" to kill this program
6. Click on "File" menu in the task manager and select "New Task (Run)"
7. Type rdpclip.exe and press the button to start the process.

By killing existing instance of the rdpclip.exe and restarting the program, you can get your copy paste work again in your remote desktop.

Trim last character from a string or each word C#

2021-03-15T03:39:00.001-07:00

Input:

I have a string say

"Hello! world!"

I want to do a trim or a remove to take out the ! off world but not off Hello.

Solution:

"Hello! world!".TrimEnd('!');

PHP: short_open_tag Not working in php 5.3.1

2021-03-15T03:38:00.000-07:00

<? ?> tags not working in php 5.3.1

Issue/problem

I just installed php 5.3.1 in server and now my old work which I used to write with <? ?> tags is not working at all..

Solution:

WAMP Server

Click on the wampserver tray icon.
Go to PHP. Then PHP Settings
Click short open tag. (When it's on it will have a checkmark next to it.)

XAMPP

If you are using xampp in windows then please do following

Open XAMPP control panel.
Click on CONFIG button.
Go to PHP (php.ini) option.

Find short_open_tag using ctrl+f utility

You will found ;short_open_tag

kindly remove the semicolon (;) from line.

and keep it as short_open_tag = on

Finally, restart your Apache server

If you use wamp or xamp or iis 7 -> php manger , it's really easy to activate them. Just click on icon->php server->setting->allow short tag open

Remember to set UpdatePanel's UpdateMode to Conditional

2021-03-15T03:36:00.000-07:00

UpdateMode property of the UpdatePanel

Since the cool thing about the ASP.NET Ajax UpdatePanel is that its contents are updated asynchronously when an event that would normally generate a postback is raised inside, one would think that this is its default behavior.

But it's not: the UpdateMode property of the UpdatePanel has 2 possible values:

Always
Conditional

and the default value is Always.

When set to Always, the UpdatePanel is updated on every postback raised from anywhere in the page, so from controls inside the panel, inside other panels or just on the page.
When set to Conditional, the UpdatePanel will be updated only on postback originated by controls inside the panel or from the triggers specified.

So, if you have multiple update panels and you don't want to update all of them to be updated every time, you have to set the UpdateMode to Conditional:

<asp:UpdatePanel ID="UpdatePanel1" 
                  UpdateMode="Conditional"
                  runat="server">

GROUPING SETS Clause in SQL Server

2021-03-15T03:35:00.001-07:00

Introduction

We all know the use of a "group by" statement. Normally a GROUP BY statement is used in conjunction with aggregate functions (like sum, count, avg etc.) to group the result-set by one or more columns. In a sort, whenever data is to be summarized, the developer uses a Group By clause. The GROUP BY clause is also used to get DISTINCT rows from any result set.

GROUPING SET is introduced in SQL Server 2008. GROUPING SET is able to generate a result set that can be generated by a UNION ALL of multiple simple GROUP BY clauses. It is capable of generating a result set that is equivalent to the result set generated by ROLL UP or CUBE operations.

GROUPING SETS Equivalents

Specifying GROUPING SETS as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. We can also use GROUPING SETS in replacement of ROLLUP and CUBE.

Example

In this example, an Employee Master table is created and some dummy values are inserted:

CREATE TABLE #EmployeeMaster
(
      EmployeeCode varchar(10),
      EmployeeName varchar(25),
      DepartmentCode varchar(10),
      LocationCode varchar(10),
      salary int
)

INSERT into #EmployeeMaster(EmployeeCode, EmployeeName, DepartmentCode, LocationCode ,salary)
VALUES
('E0001', 'Jignesh', 'IT','GNR', 2000),
('E0002', 'Tejas', 'IT','GNR', 5000),
('E0003', 'Rakesh', 'QA','BVN', 4000),
('E0004', 'Bhavin', 'QA','BVN', 2000),
('E0005', 'Sandip', 'HR','ABD', 3000),
('E0005', 'Tarun', 'HR','ABD', 5000)

Now I want some summarized data, like total cost by Employee, total cost by Department, total cost by location and total cost for all employees with all locations in a single result set.

Before SQL Server 2008, to do this we must write a different query and UNION these queries (this is the same as the following query).

SELECT EmployeeCode, DepartmentCode, LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY EmployeeCode, DepartmentCode, LocationCode
UNION
SELECT NULL AS EmployeeCode, DepartmentCode, NULL AS LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY DepartmentCode
UNION
SELECT NULL AS EmployeeCode, NULL AS DepartmentCode, LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY LocationCode
UNION
SELECT NULL AS EmployeeCode, NULL AS DepartmentCode, NULL AS LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster

With SQL Server 2008:

SELECT EmployeeCode, DepartmentCode, LocationCode, SUM(salary) TotalCost
from #EmployeeMaster
Group BY
  GROUPING SETS
  (
    (EmployeeCode, DepartmentCode, LocationCode)

   ,(DepartmentCode)
   ,(LocationCode)
   ,()
  )

Conclusion

Using GROUPING SETS, we can write multiple "Group By" clauses within a single query and get a single result set. Also it can be used as equivalent to as well as with ROLLUP and CUBE.

How to get first record out of each group from the result retrieved by using group by command

2021-03-12T07:25:00.001-08:00

Suppose if Input is:

ID	GROUP ID	QTY
1	1	100
2	1	200
3	1	300
4	2	98
5	2	198
6	3	175
7	3	275
8	3	375
9	4	215

Output should be

ID	GROUP ID	QTY
1	1	100
4	2	98
6	3	175
9	4	215

Solution:

Get first record out of each group from the result:

declare @T table (ID int, GroupID int, Qty int)
insert into @T values
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 98),
(5, 2, 198),
(6, 3, 175),
(7, 3, 275),
(8, 3, 375),
(9, 4, 215)

;with cte as
(
  select
    ID,
    GroupID,
    Qty,
    rank() over(partition by GroupID order by ID) as rn
  from @T
)  
select ID, GroupID, Qty
from cte
where rn = 1

PHP Coupon Script v 4.0

2021-03-11T03:30:00.000-08:00

PHP Coupon Script

Easy-to-use SEO friendly coupon script which allow you to create Discounts/Promotion Coupons web site.

Earn affiliate commissions while offering discounts for your visitors!

With 9 different designs you will be able to create perfect coupon web site in any niche!

Add website/store with Seo title, keywords and affiliate url
Activate or inactivate any coupon
Moderate each coupon and comment
You can change design for your site with single click
Change Site Title, Keywords and Description
Google Analytics And google adsense code intergration
Control Number of listing display on home page
Insert Your Affiliate link as a merchant address
Earn Affiliate Commissions from all sales

Coupon Script Features

Site Settings

Change Site Title, Keywords and Description
Change site Logo
Active/Inactive coupons Approval when user submit it on site.
Active/Inactive comments Approval when user submit it on site.
Google Analytics: Simply Put good analytics code or any other code for tracking.

Google adsense and Banners

Here You Simply put google adsense code or any affiliate code and static banner code to display on different position in site.

Listing settings

In this section you control Number of listing display for each page that include Home Page Popular Stores, Featured Stores, featured coupons and new coupons.

Site Themes/Design

You can change design for your site with single click. You can also request us for custom design.

Websites/Stores Listing

Add website/store with Seo title, keywords and affiliate url
Image Upload with thumbnail creation
Select multiple tags for each website/store
Edit , Make Feature websitse/stores
Activate or inactivate any website/store

Coupons

Add Coupons
Edit Coupons
Delete Coupon
Activate or inactivate any coupon

Users

Users can post coupons
Users can post comments
Moderate each coupon and comment. or Let it display instant when user submit.

Comments

Approve comments
Delete comments
Activate or inactivate any comment

Admins/Moderators

Add admins with email and password
Create unlimited admins to moderate different parts of site.
Admin can moderate, Coupons, Comments, Sites/Stores, Tags, Static Pages, Site Settings

Coupon Script v 4.0 Nulled Demo:

Demo
Coupon Script v 4.0 Nulled

The SQL Server (RELYONDB) service on local computer started and then stopped.

2021-03-11T00:33:00.000-08:00

Error:

The SQL Server (RELYONDB) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and alert service.

Causes:

SQL Server [RELYONDB] is corrupted. Need to install it again. Later attach the database files.

Solution 1:

Select Microsoft SQL Server 2005, and click Remove.
In the first dialog, make sure to select the “RELYONDB: Database Engine” radio button.
If RELYONDB is the only instance, then select all check boxes & Click Next and Finish to proceed with the uninstall.
If there is no other SQL instance, I would go on to clean out all SQL components with the following steps.
From Add/Remove Programs, uninstall Microsoft SQL Server VSS Writer.
Uninstall Microsoft SQL Server Setup Support Files.
Uninstall MIcrosoft SQL Server Native Client.

Using the Microsoft Web Platform Installer

2021-03-11T00:06:00.000-08:00

Introduction

The Microsoft® Web Platform Installer (Web PI) makes it easy for you to download, install, and keep up to date on the latest software components of the Microsoft® Web Platform for development and application hosting on the Windows® operating system. Web PI does the work of comparing the newest available components across the Microsoft Web Platform against what is already installed on your computer; you can see what is new and what you haven't yet installed. You can use Web PI to learn more about different components and install one or more components in a chained installation, with Web PI handling reboots and logging failures where applicable. The currently available software components include Microsoft WebMatrix, Internet Information Services (IIS), the latest versions of PHP 5.2 and PHP 5.3, Microsoft® SQL Server® 2008 R2 Express, the Microsoft®.NET Framework, and Microsoft® Visual Web Developer 2010 Express Edition with Service Pack 1.

Web PI also provides an interface to the Windows Web App Gallery, a community-driven hub of the most popular open-source and community Web applications that run on Windows. Accessible from anywhere through the Microsoft Web Platform site, the Windows Web App Gallery provides a streamlined way for users to explore, discover, and install Microsoft® ASP.NET, PHP, and other types of Web applications for the Windows operating system, providing Web developers with access to millions of Web users worldwide. The Windows Web App Gallery lets developers submit their own applications for inclusion; once accepted, a Web application can appear in Web PI for anyone to download.

System requirements for Web PI include an Internet connection and one of the following supported operating systems:

Windows Server® 2008 R2 or Windows Server® 2008

Windows Server® 2003 Service Pack 2 (SP2)

Windows Vista® Service Pack 2 (SP2)

Windows® XP Service Pack 3 (SP3)

Windows® 7

Web PI for Hosters

Web PI can help in the following scenarios:

Building reference machines used for the creation of images for dedicated or virtual dedicated offerings.

Building the Web Server reference machine for a shared hosting environment.

Updating existing machines with the latest components.

Deploying an internal development or testing environment.

Referring customers to use Web PI to setup their own dedicated machines or local environments for development and testing before deploying to hosted environments.

Web PI for Web Hosting Customers

Web PI can help in the following scenarios:

Deploying a development environment with the entire Web Platform stack of components. Includes the latest Web, database, and development tools and technologies.

Updating the development environment with the latest components.

Install the Web Platform

Web PI automates the discovery and installation of the Microsoft Web Platform, which includes the Web Server, Frameworks and Runtimes, Database, and Tools sections. In version 3.0, the Microsoft Web Platform also installs the latest 5.2 and 5.3 versions of PHP.

Download Web PI. WebPI starts up on the "Spotlight" tab, where we highlight products and applications that we think will be the most interesting for our users

Figure 1: "WebPI Spotlight" tab

WebPI separates the components you can install into two main categories: Products and Applications. In the Products tab, you will find all the components you need to build and maintain your web sites. The Applications tab is home to our wide collection of open-source applications that you can use as a great starting place for developing your sites

The "Products" tab shows the four main components: Server, Frameworks, Database and Tools.

Figure 2: "Products" tab

Server

The Internet Information Services (IIS) Web server ships in the Windows client and server operating systems, providing a secure, easy-to-manage Web platform for reliably hosting rich Web applications and Web services.

As of the IIS 7.0 release (which shipped in Windows Server 2008), IIS began using its new extensible architecture to release free Web extensions, such as the Web Deployment Tool and Database Manager. Microsoft fully supports these extensions for the lifetime of the operating system and also has plans of localizing the extensions for international customers.

WebPI currently installs the latest IIS release (version 7.5, which shipped with Windows Server 2008 R2).

Also included in WebPI is IIS Express 7.5, the lightweight self-contained version optimized for developers. IIS Express makes it easy to use the most current version of IIS to develop and test websites. It has all the core capabilities of IIS 7.5 as well as additional features designed to ease website development including:

It doesn't run as a service or require administrator user rights to perform most tasks
IIS Express works well with ASP.NET and PHP applications
Multiple users of IIS Express can work independently on the same computer

Web PI simplifies the discovery and installation of Web server components by:

Automatically configuring the IIS Web server components that ship in Windows. On Windows 7 and Windows Vista, the Web server components are "Optional Windows Components." Optional Windows components are part of the Windows installation but are not activated until the user explicitly goes through the process of activating the IIS Windows feature. Likewise, with Windows Server® 2003 and Windows Server 2008, a systems administrator uses Server Manager to install the Web Server role (which installs IIS). With Web PI, setting up the Windows components involves selecting a single product entry
Automatically "discovering" IIS Extensions that ship on the Web.
The IIS team ships extensions that expand the management and runtime capabilities of the IIS Web server and applications hosted on IIS. Users can select from the IIS Web extensions that appear alongside the IIS components that ship in Windows. This integration makes it easy to find and install the Web extensions that the IIS product team delivers on a quarterly basis.

To see all the IIS components available for installation, just search for IIS or to install the recommended set, search for and install the IIS 7 Recommended Configuration

Figure 3: Searching for IIS components in WebPI

You can learn more about the IIS extension by double-clicking the extension name. For example, on the details page for an IIS component, you can learn more about what the extension does before installing it, and there are links to even more detailed descriptions.

Figure 4: More information on the IIS: FTP Publishing Service 7.5

Frameworks

The Frameworks section provides developers with programming frameworks for application development.

Figure 5: Frameworks

Web Pl 3.0 currently installs the latest community PHP 5.2.and 5.3 from the community PHP download page. Web PI installs PHP along with the following optional PHP components:

Web PI also installs ASP.NET MVC 3.0. ASP.NET is a powerful framework for building dynamic Web applications. As a programming framework, ASP.NET is adaptable to a wide variety of projects and development styles. ASP.NET is part of the .NET Framework, and when coding ASP.NET applications developers have access to the entire .NET Framework. The Model View Controller (MVC) extension enables users to build MVC applications by using the ASP.NET framework.

Database

The Database section includes the SQL Server tools for application development and management.

SQL Server 2008 R2 is a complete database engine providing ease of use and manageability for running high-performance Web applications. You can select SQL Server 2008 Express SP1 for a flexible runtime environment for database programming.

SQL Server 2008 R2 Management Studio Express gives developers tools to more easily manage databases in development, staging, or production environments—you can use this option if the SQL Server Express runtime itself is already installed to simplify the management of databases.

Figure 6: Database

The SQL Server 2008 R2 Management Objects gets pulled in as a dependency for some extensions and/or applications. (Typically, a user will not install this option.)

Web PI includes the Microsoft® SQL Server® 2008 Driver v 2.0 for PHP, which enables reliable, scalable integration with SQL Server for PHP applications deployed on the Windows platform. The extension allows the reading and writing of data from within PHP scripts and provides a procedural interface for accessing data in all editions of SQL Server 2008 (including Express). The extension also supports the use of PHP streams to read and write large objects. Information, including the source code, is available on CodePlex.

Tools

The Tools section provides developer tools to build Web applications for the Microsoft Web Platform.

Visual Web Developer Express is a free Microsoft® Visual Studio® Web development environment for building and testing next-generation, standards-based Web applications and services. With full support for Web standards, JavaScript, and ASP.NET, Visual Web Developer Express enables developers to quickly build out new applications.

The Microsoft® Silverlight™ 4 Tools for Visual Studio provide a development environment for Microsoft® Silverlight™ programmers. The Silverlight Tools are free add-ons to the free download version of Visual Web Developer Express or the full version of Microsoft® Visual Studio® 2010 SP1.

Tools

Install Applications from the Microsoft Web Application Gallery

The Web Platform Installer 3.0 simplifies discovery and deployment of ASP.NET and PHP applications in the Windows Web App Gallery.

To learn more about the Web Application Gallery, see Introducing the Windows Web Application Gallery.

When an application is added to the Windows Web Application Gallery, the application integrates into an Atom feed which Web PI consumes; every time a user opens the Web Applications tab, the user sees the latest applications in the Web Application Gallery.

Figure 8: Web Applications

By default, you see the full list of applications in the Windows Web Application Gallery. You can also browse by category to discover applications. These applications are free of charge (although the application provider can offer premium paid support) and follow the Windows Web Application Gallery Principles for running well on Windows.

When you select an application, Web PI automatically installs the prerequisite Windows components and other components (such as PHP) to run the application. The list of required Web Platform dependencies for WordPress is displayed for you before installation. In this example, Wordpress is being installed on a Windows 7 machine. By default, WebPI will bring along Microsoft WebMatrix, a free, solid and reliable platform for customizing popular web applications

The prerequisite information comes from the application developer. As part of the application submission process, the application provider gives Microsoft information such as a prerequisite environment and a URL to the Microsoft Web Deployment–enabled package. Web PI uses that information to make sure that the environment is set up properly to run the application on Windows, using the application package that is publicly available for download on the application community site.

For more developer information on Web Application Gallery, see the Windows Web App Gallery Developer site.

Reference :https://docs.microsoft.com/en-us/iis/install/web-platform-installer/using-the-microsoft-web-platform-installer

PHP: Tags Not Working in PHP 5.3.x under UNIX / Linux

2021-03-10T23:06:00.002-08:00

Problem / Issue

I have recently installed php 5.3.5 in my UNIX box and my old working php code not working. It displays code in pages which is in <? … my code … ?> php tags. How do I fix this problem/Issue?

Solution

You need to allow the php tag by editing php.ini file. Using short tags should be avoided when developing applications or libraries that are meant for redistribution, or deployment on PHP servers which are not under your control, because short tags may not be supported on the target server. For portable, redistributable code, be sure not to use short tags.

# vi php.ini
Make the changes as follows:

 
short_open_tag = On

Save and close the file .Reload or restart the apache web server:
# apachectl -k restartOR
# service httpd restart

SQL - Calculating time difference in Minutes, Hours, Days, Weeks, Months, Years

2021-03-10T22:03:00.001-08:00

Introduction

In this article I will show you how you can easily in SQL, calculate time difference between two dates in minutes, hours, days, and even weeks, months and years!

The key of this calculation is in Modulo operator, %. It returns the remainder (NOT the result!) of one number divided by another!

Simple example

The following example divides the number 38 by 5. This results in 7 as the integer portion of the result, and demonstrates how modulo returns the remainder of 3.

SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder;

Integer Remainder

----------- -----------

7 3

(1 row(s) affected)

We will calculate the difference between two dates: ARTICLE created date - "2012-04-01 08:10:16", and NOW datetime which we will be set as variable - "2012-04-10 11:35:36".

In real world instead of last date, you would probably use GETDATE() SQL function, that returns real datetime.

DECLARE @articleDT DATETIME;

DECLARE @nowDate DATETIME;

-- Time of the ARTICLE created

SET @articleDT = '2012-04-01 08:10:16';

-- Simulation of NOW datetime

-- (in real world you would probably use GETDATE())

SET @nowDate = '2012-04-10 11:35:36';

-- Created 9 days ago.

SELECT 'Created ' + CAST(DATEDIFF(day, @articleDT, @nowDate) AS NVARCHAR(50)) + ' days ago.';

-- Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.

SELECT 'Created '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ' weeks, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ' days, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 / 60 % 24  AS NVARCHAR(50)) + ' hours, '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) / 60 % 60 AS NVARCHAR(50)) + ' minutes and '

    + CAST(DATEDIFF(second, @articleDT, @nowDate) % 60 AS NVARCHAR(50)) + ' seconds ago.';

As you can see, this algorithm will give you very useful text-like information about how much time has passed since the article was created.

--------------------------------------------------------------------

Created 9 days ago.

(1 row(s) affected)

----------------------------------------------------------------------------------------------

Created 1 weeks, 2 days, 3 hours, 25 minutes and 20 seconds ago.

(1 row(s) affected)

This functionality is also very useful when you must display the time in format like:

"News posted few hours ago..."
"Article posted two weeks ago..."
"Article posted six months ago...",
etc...

Parsing a URL with SQL Server Functions

2021-03-10T22:00:00.000-08:00

Problem

In my environment I have a few tables with thousands of rows that have URLs in particular columns. We are going through some changes in the environment and need to be able to parse out the data (base URL, file name, query parameters) from the specific columns then move the parsed data to another database design. Moving the data is no problem. I plan on using SQL Server 2005 Integration Services and I have a good sense of how to use that tool. By the way, all of the URLs are in varchar fields if that makes any difference. Can you provide any recommendations on how to parse that data?

Solution

Although there are probably a few different ways to approach this task, let's break it apart and see how we can code each one of these scenarios with T-SQL commands in SQL Server 2005. I assume you will be parsing the data from a single column to three new columns in a new database\table design. Since you are familiar with SQL Server 2005 Integration Services, I will not dig into those details and just focus on how to capture the data that you need. So let's take a look at each scenario and give an explanation on the T-SQL coding in SQL Server 2005.

Example Table and Data

Here an example table and example data for this tip:

USE zCustomerDB
GO
CREATE TABLE ExampleURLs (URL_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,CompleteURL VARCHAR(500))GOINSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])VALUES ('http://www.test.com/page.asp?tip=1156')GOINSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])VALUES ('http://www.test.com/page.asp?tip=1299')GOINSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])VALUES ('http://www.test.com/page.asp?tip=1262')GOINSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])VALUES ('http://www.test.com/page.asp?tip=1240')GOINSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])VALUES ('http://www.test.com/page.asp?tip=1226')GO

cenario 1 - Base URL

As an example, let's say the data you are working with looks like 'http://www.mssqltips.com/sqlservertip/1156/opportunities-with-sql-server-2005-express-edition/' and you need to capture the 'www.mssqltips.com' portion of the column. Below is an example of what that code could look like. This code is fairly straight forward with a single SUBSTRING command performing all of the heavy lifting. The REVERSEcommand is used to find the last slash so the entire directory structure is returned. The CHARINDEX command is used to determine the position of characters such as '//' in 'http://' and '/' in the URL directory path.

SELECT URL_ID,CompleteURL, -- URL(CHARINDEX('//', CompleteURL, 1) + 1), -- Position of the double slashesCHARINDEX('/', REVERSE (CompleteURL), 1), -- Position of the last single slashSUBSTRING(CompleteURL,
(CHARINDEX('//', CompleteURL, 1) + 2), CHARINDEX('/', REVERSE (CompleteURL), 1) ) -- Final stringFROM zCustomerDB.dbo.ExampleURLs
GO

Scenario 2 - File Name

In this scenario let's capture file name which is the 'tip.asp' portion of the 'http://www.mssqltips.com/sqlservertip/1156/opportunities-with-sql-server-2005-express-edition/' URL. This code makes additional usage of the REVERSE and SUBSTRING commands, but the subtle portion of this code is positioning each portion of the SUBSTRING correctly. This is accomplished by using the '+1' in the starting position (second position) of the SUBSTRING command and using a '-1' in the length position (third position) of the SUBSTRING command. In this circumstance the CHARINDEX command is used to determine the position of the '?' and '/' characters. Here is an example of what that code could look like:

SELECT URL_ID,REVERSE (CompleteURL), -- Backwards version of the URLCHARINDEX('/', REVERSE (CompleteURL), 1), -- Position of the slashCHARINDEX('?', REVERSE (CompleteURL), 1), -- Position of the question mark((CHARINDEX('/', REVERSE (CompleteURL), 1)) - (CHARINDEX('?', REVERSE (CompleteURL), 1))-1), -- LengthREVERSE(RTRIM(SUBSTRING(REVERSE (CompleteURL),
(CHARINDEX('?', REVERSE (CompleteURL), 1)+1),
((CHARINDEX('/', REVERSE (CompleteURL), 1)) - (CHARINDEX('?', REVERSE (CompleteURL), 1))- 1)))) -- Final parsed valueFROM zCustomerDB.dbo.ExampleURLs
GO

Scenario 3 - Query Parameters

In the final example capturing the query parameters is based on finding the correct position for the question mark '?' in the URL. With the 'http://www.mssqltips.com/sqlservertip/1156/opportunities-with-sql-server-2005-express-edition/' sample data, you would need to capture the 'tip=1156' portion of the column. Here is an example of what that code could look like by leveraging the REVERSE, CHARINDEX and SUBSTRING commands once again in a little different context:

SELECT URL_ID,REVERSE (CompleteURL), -- Backwards version of the URLCHARINDEX('?', REVERSE (CompleteURL), 1), -- Position of the question markREVERSE(RTRIM(SUBSTRING(REVERSE (CompleteURL), 1, CHARINDEX('?', REVERSE (CompleteURL), 1) - 1))) -- Final parsed valueFROM zCustomerDB.dbo.ExampleURLs
GO

ASP.NET MVC - Data Binding for Hierarchical Views or Parent Child Relation C#.NET Core and .NET Standard

2021-03-10T21:57:00.002-08:00

Introduction

Many developers know that they can create forms on web pages with a minimum of code using ASP.NET model binding. Visual Studio's default MVC view templates will even create a standard list, create, edit, and delete views without any additional programming.

But the power of default model binding extends beyond the flat data model of a simple input form or list of records. Using a few straightforward coding techniques, developers can use ASP.NET to create forms and collect data for hierarchical entity relationships. In many applications, this can make the difference between leveraging the rapid development capabilities of ASP.NET MVC and strapping on the additional infrastructure and complexity of a client-side framework like Angular or React.

Using ASP.NET MVC model binding to present and collect hierarchical form data in a hierarchical structure.

Scope

Using ASP.NET MVC model binding to present and collect hierarchical form data in a hierarchical structure.

The code presented in this guide are based on the .NET Framework. Implementation details for .NET Core and .NET Standard will be covered in a different guide.

Structure

We'll begin with an overview of the case study entities and the principal views of the example solution used to prepare this guide.

Then, we'll see how to create a view model incorporating member fields of various primitive types and incorporating a field that is a collection of an object type.

Next, we'll look at the code required to present information to the end user.

We'll conclude with a close look at how to ensure that Razor code creates the correct HTML and we'll take a look how to use HtmlHelpers and CSS to apply formatting to the form fields created by the view.

Entities and Relationships

Note that the many-to-many relationship between Orders and Items is implemented through the use of a merge table with payload: in addition to maintaining the relationship between Orders and Items, the OrderItems table also contains information about the items included in an order, the price at which they were sold and the quantity which were sold.

Obviously, this isn't a complete order processing system; it's just meant to provide an example of hierarchical relationships in a familiar form.

The database is created and maintained using Entity Framework code-first design. Let's look at the Customer and Order entities:

Customer.cs

Namespace Entities.Customers {

public class Customer {

public Customer() {

Orders = new HashSet<Order>();

}

[Key]

[Column(Order = 0)]

[DatabaseGenerated(DatabaseGeneratedOption.None)]

public Guid CustomerID { get; set; }

[Required]

[MaxLength(128)]

public string CustomerName { get; set; }

[Required]

[MaxLength(3)]

public string CountryIso3 { get; set; }

[MaxLength(3)]

public string RegionCode { get; set; }

public virtual Country Country { get; set; }

public virtual Region Region { get; set; }

public virtual ICollection < Order > Orders { get; set; }

}

Note the following characteristics:

ComponentModel DataAnnotations are used to identify the key field for the database and to specify the size and other options.

The one-to-many relationship between Customers and Orders is created by the virtual member field comprised of a collection of Order entities.

The required relationship between Customers and Countries in the database is reflected in the field to hold the value CountryIso3 and the field to identify the relationship, Country, which is of type Country.

Order.cs

The Order entity is defined in a similar way:

namespace Entities.Orders
{
public class Order
{
public Order()
{
Items = new HashSet<Item>();
}
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public Guid OrderID { get; set; }
[Required]
public Guid CustomerID { get; set; }
[Required]
public DateTime OrderDate { get; set; }
[Required]
[MaxLength(128)]
public string Description { get; set; }
public virtual ICollection<Item> Items { get; set; }
public virtual Customer Customer { get; set; }
}
}

Note that:

The Order can only belong to one Customer, as reflected in the field for a single CustomerID and the navigation property Customer.

The one-to-many relationship between Orders and Items is implemented through the virtual property for the collection of Item entities.

Presenting Data

For the purposes of this guide, there are two notable views in the case study, one to display a list of customers and another to display a list of orders for each customer.

Customer/Index View

The view for the list of customers is a simple table displaying some basic information about the customer and an Html.ActionLink helper method to navigate to the list of orders for the customer:

Customer/Index view

Views\Customer\Index.cshtml

In the code for the view above, note that the list of customers is created with a foreach loop that iterates through the collection of CustomerDisplayViewModel entities. This is a standard way of presenting a list with a variable number of records:

@foreach(var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.CustomerID)
</td>
<td>
@Html.DisplayFor(modelItem => item.CustomerName)
</td>
<td>
@Html.DisplayFor(modelItem => item.CountryName)
</td>
<td>
@Html.DisplayFor(modelItem => item.RegionName)
</td>
<td>
@Html.ActionLink("Orders", "Index", "Order", new { customerid = item.CustomerID }, null)
</td>
</tr>
}

In the list of customers, the view model has a flat structure, it's just an enumerable list of objects that contain the customer information. Here's the @model directive from the beginning of Index.cshtml:

@model IEnumerable<Entities.Customers.ViewModels.CustomerDisplayViewModel>

Now let's take a closer look at that view model.

Customers.ViewModels\CustomerDisplayViewModel.cs

namespace Entities.Customers.ViewModels

{

public class CustomerDisplayViewModel

{

[Display(Name = "Customer Number")]

public Guid CustomerID { get; set; }

[Display(Name = "Customer Name")]

public string CustomerName { get; set; }

[Display(Name = "Country")]

public string CountryName { get; set; }

[Display(Name = "State / Province / Region")]

public string RegionName { get; set; }

}

When the repository method populates this model it combines data from the Customers table with CountryNameEnglish from the Country table and RegionName from the Region table. In this way, the view model can present information that is more helpful to the user than the index values for country and region from the Customers table.

Order/Index View

The simple list of orders for a customer shows the customer information and the order number and date as read-only fields, and the purchase order/description as an editable field. By changing values in the editable field and saving we can see how model binding works when doing HttpPost actions.

Order/Index view

In this view, we're presenting information in a hierarchical structure. At the top level is the customer information. Underneath that is the list of orders for the customer.

Let's see how the data is presented in code.

Views\Order\Index.cshtml

For the top-tier data, pertaining to the customer, the fields are composed in a very standard way:
<div class="form-group">
@Html.LabelFor(model => model.CustomerName, new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.CustomerName, new { htmlAttributes = new { @class = "form-control", @readonly = "readonly" } })
</div>
</div>

Note that we're using the EditorFor HtmlHelper to let the Razor engine determine the correct type of HTML element for the data type. We're also applying the form-control CSS class to be sure the control picks up the appropriate styling. The field is changed from an editable textbox to a display-only field with the application of the @readonly HTML attribute.

For the second tier data, the list of orders, we're looping through the records in the view model. But in this case we're not using a foreach loop and we're not using the EditorFor HtmlHelper. We'll look at the reasons for these choices in more detail in the section on saving data.

@if (Model.Orders != null)
{
for (var i = 0; i < Model.Orders.Count(); i++)
{
<tr>
@Html.HiddenFor(x => Model.Orders[i].CustomerID)
<td>
@Html.TextBoxFor(x => Model.Orders[i].OrderID, new { @class = "form-control", @readonly = "readonly" })
</td>
<td>
@Html.TextBoxFor(x => Model.Orders[i].OrderDate, new { @class = "form-control", @readonly = "readonly" })
</td>
<td>
@Html.TextBoxFor(x => Model.Orders[i].Description, new { @class = "form-control" })
</td>
</tr>
}
}

You'll also note that we're using a for loop with a counting variable rather than a foreach loop. This is crucial to getting binding to work for the 'HttpPost' action, as we'll see soon.

CustomerOrdersDisplayViewModel.cs

The view model for customer orders reflects the hierarchical structure of the view shown above. It assembles the display information about the customer from the Customers, Countries, and Regions tables and includes a property that is a collection of OrderDisplayViewModel entities.

namespace Entities.Orders.ViewModels
{
public class CustomerOrdersListViewModel
{
[Display(Name = "Customer Number")]
public Guid CustomerID { get; set; }
[Display(Name = "Customer Name")]
public string CustomerName { get; set; }
[Display(Name = "Country")]
public string CountryNameEnglish { get; set; }
[Display(Name = "Region")]
public string RegionNameEnglish { get; set; }
public List<OrderDisplayViewModel> Orders { get; set; }
}
}

Lets take a look at the class that composes the Orders collection.

OrderDisplayViewModel.cs

Note that each entity in OrderDisplayViewModel is linked to the associated customer in CustomerOrdersListViewModel. When we transpose the entities into the view model structure we need to preserve the relationship between the entities (and the tables in the database).

namespace Entities.Orders.ViewModels
{
public class OrderDisplayViewModel
{
public Guid CustomerID { get; set; }
[Display(Name = "Order Number")]
public Guid OrderID { get; set; }
[Display(Name = "Order Date")]
public DateTime OrderDate { get; set; }
[Display(Name = "PO / Description")]
public string Description { get; set; }
}
}

Note also that there are no virtual properties in either of these classes to provide navigation between entities. The view models serve the functional purpose of the view and are uncoupled from the entity relationships of the classes and the database tables. Accordingly, when two view models are used together they reflect the relationship(s) between the view models, rather than the entities from which their data is drawn.

The repository methods take care of transposing the data from the structure of the entities to the structure of the view models and back again.

OrdersController Action for Index HttpGet

By using MVVM and the repository design pattern, we can make our controller actions succinct and provide separation of concerns between the presentation layer, business logic, and data. We can see that in action in the controller action that populates the Order/Index view.

Controllers\OrderController.cs

using System;

using System.Net;

using System.Web.Mvc;

using Data.Orders;

using Entities.Orders.ViewModels;

namespace Projects.Controllers

{

public class OrderController : Controller

{

// GET: Order

public ActionResult Index(string customerid)

{

if(!String.IsNullOrWhiteSpace(customerid))

{

if (Guid.TryParse(customerid, out Guid customerId))

{

var repo = new OrdersRepository();

var model = repo.GetCustomerOrdersDisplay(customerId);

return View(model);

}

return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

}

...

All that this controller action needs to do when passed a `CustomerID` from the Customer/Index view is pass that value to the appropriate repository method and take the resultant data model, an instance of the `CustomerOrdersListViewModel` class, and pass it to the view.

Saving Data

Saving data using default binding can be a tricky process -- the correct approach is not well-documented. This is also a situation where the code fails silently. Developers will see their web pages being populated with data correctly, but the values won't show up in the model when it arrives at the controller action for HttpPost.

To better understand this, we're first going to take a look at the problem, then show how to code the functionality correctly.

What Not to Do

In the Razor code for the list of customers above, we saw that we could populate the list using a foreach loop and the DisplayFor HtmlHelper method. If we used a foreach loop for the list of orders, the <table> element would look like this:

<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.Orders[0].OrderID)
</th>
<th>
@Html.DisplayNameFor(model => model.Orders[0].OrderDate)
</th>
<th>
@Html.DisplayNameFor(model => model.Orders[0].Description)
</th>
</tr>
@if (Model.Orders != null)
{
foreach (var order in Model.Orders)
{
<tr>
@Html.HiddenFor(x => order.CustomerID)
<td>
@Html.DisplayFor(x => order.OrderID)
</td>
<td>
@Html.DisplayFor(x => order.OrderDate)
</td>
<td>
@Html.EditorFor(x => order.Description)
</td>
</tr>
}
}
</table>

That's nice and concise, and seems to leverage the power of Razor HtmlHelper extension methods to "automagically" generate HTML. The problem is; it doesn't work.

The HTML produced by the preceding code would look like this:

Form Elements with Ambiguous Element Names and ID's

Look at the areas highlighted in yellow. Each row is a separate textbox on the form shown above for the Order/Index view. Each record has the same value for the name and id elements: order.Description. Without a way to identify the records distinctly, MVC gives up and returns null for the Orders field of the CustomerOrdersListViewModel to which the Order/Index view is bound.

Correctly Binding Collection Data

In the Razor code for the list of orders for a specific customer, we used a for loop with a local variable index value i. The loop looks like this:

Note the following particulars:

The index value i appears in the lambda expression for each form element being generated by the loop, for example:

(x => Model.Orders[i].OrderID)

The TextBoxFor HtmlHelper is used, rather than the more general (and automagic) DisplayFor and EditorFor.

The OrderID and OrderDate fields are set as readonly using the HTML class attribute, rather than using DisplayFor.

Bootstrap textbox styling is applied by adding the @class = "form-control" attribute.

The generated HTML looks like this:

Form Elements with a Distinct Name and ID Attributes

As the areas highlighted in yellow show, each field has a name and id attribute that is distinct for each record. The record index gives MVC something to use to bind the form data to the data model.

By setting a breakpoint in the HttpPost controller action for the Order/Index view we can see that the new data we entered, "expedite" in the Description field of record 0, is being posted back to the server along with the values for the readonly fields.

Visual Studio Debugging Showing Property Inspector Values for an OrderDisplayViewModel Entity

Posting Display Data

As we noted above, we're using the TextBoxFor HtmlHelper for read-only fields, rather than the DisplayFor helper. This is so we can return the data to the controller when the HttpPost event fires (when the Save button on the page is pressed).

When MVC generates the HTML for a DisplayFor HTML helper, it renders the element as simple text.

For example, a table cell coded like this:

<td>
@Html.DisplayFor(modelItem => item.OrderID)
</td>

would render like this:

That's just straight text, so there's no way for MVC to bind it to the model.

But a table cell for a display-only text field coded like this:

<td>
@Html.TextBoxFor(x => Model.Orders[i].OrderID, new { @class = "form-control", @readonly = "readonly" })
</td>

would render like this:

As an <input> field, this element will be passed back to the controller during the POST. Because it has a distinct id, the data in this readonly field can be bound to the view model just like data from an editable field. The values for CustomerID and OrderID give us the index values necessary to save the changes to the editable field, Description.

Note, also, that while we're displaying OrderDate as a readonly text field, and thereby returning it during the POST event, we don't have to. Only the index values necessary to save the changed data need to be returned in the POST event.

In our example, the OrderID field is displayed, but the CustomerID field is included in the form using the HiddenFor HtmlHelper. As coded, it looks like this:

@Html.HiddenFor(x => Model.Orders[i].CustomerID)
``
And the HTML rendered by it looks like this:
```html

side from being hidden on the HTML served to the client, this is a full-featured data element that is bound to the view model received by the HttpPost controller action for the Index view. Using HiddenFor is a convenient way of keeping form layout simple while including all the data necessary for identifying the records to be updated during a POST.

The astute reader may have realized that in our case study the CustomerID field isn't necessary to save changes to an Order object. Because OrderID is a GUID it inherently provides a unique identifier for an individual order.

HttpPost Controller Actions

By using the repository design pattern we can keep our controller actions simple. In the case of the HttpPost action for the Order/Index view, all we need to do is validate CustomerOrdersListViewModel and pass it to the appropriate repository method.

Controllers\OrdersController.cs

[HttpPost]

[ValidateAntiForgeryToken]

public ActionResult Index(CustomerOrdersListViewModel model)

{

if (ModelState.IsValid){

if (model.Orders != null)

{

var repo = new OrdersRepository();

repo.SaveOrders(model.Orders);

}

return View(model);

}

return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

}

...

Save Repository Method

The repository method associated with our order list view can also be simple. All we need to do is find the appropriate records in the Orders table and update them.

Data\Orders\OrdersRepository.cs

...

public void SaveOrders(List<OrderDisplayViewModel> orders)

{

if (orders != null)

{

using (var context = new ApplicationDbContext())

{

foreach (var order in orders)

{

var record = context.Orders.Find(order.OrderID);

if (record != null)

{

record.Description = order.Description;

}

context.SaveChanges();

}

...

ClosedXml Excel Process Examples C#

2021-03-10T21:56:00.000-08:00

Introduction

Example of Read an Excel file into a DataTable and Process Datatable. Finally SaveDatatable into excel file.

SAMPLE CODE

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

public class ClosedXmlExcelProcess
{
public bool GenerateOutput(DataTable dt, string OutputFilePath)
{
return ProcessOutput(dt, OutputFilePath);
}
public bool GenerateOutput(string InputFilePath, string OutputFilePath)
{
DataTable DtInput = ProcessInputFile(InputFilePath);
return ProcessOutput(DtInput, OutputFilePath);
}
private bool ProcessOutput(DataTable DtInput, string OutputFilePath)
{

if (DtInput.Rows.Count <= 0 || DtInput.Columns.Count < 8)
{
return false;
}

//Get Campaign Name from DataTable
string _CampaignColumnName = DtInput.Columns[2].ColumnName;
string _GroupColumnName = DtInput.Columns[3].ColumnName;
string _ClickColumnName = DtInput.Columns[4].ColumnName;
string _SalaryColumnName = DtInput.Columns[6].ColumnName;

//Get Distinct Column Values of Campaign
DataTable mDistinctdt = DtInput.DefaultView.ToTable(true, _CampaignColumnName);
if (mDistinctdt.Rows.Count > 0)
{
List<string> lstCampaignName = mDistinctdt.AsEnumerable().Select(r => r.Field<string>(_CampaignColumnName)).ToList();
if (lstCampaignName.Count > 0)
{

XLWorkbook wb = new XLWorkbook();

foreach (string CampaignVal in lstCampaignName)
{
DataView vwFilter = DtInput.DefaultView;
vwFilter.RowFilter = "[" + _CampaignColumnName + "] = '" + CampaignVal + "'";
DataTable dtFilteredRows = vwFilter.ToTable();

//Filter Value like Excel Pivot Table using Linq
List<OutputResult> lstCampaignSheet = new List<OutputResult>();

lstCampaignSheet = dtFilteredRows.AsEnumerable()
.Select(x =>
new
{
GroupName = x[_GroupColumnName],
ClickCount = x[_ClickColumnName],
Salary = x[_SalaryColumnName]
}
)
.GroupBy(s => new { s.GroupName })
.Select(g =>
new OutputResult
{
AddGroupView = g.Key.GroupName.ToString(),
Clicks = g.Sum(x => Convert.ToInt32(x.ClickCount)),
Salary = g.Sum(x => Convert.ToInt32(x.Salary)),
}
).ToList();

IXLWorksheet ws = wb.Worksheets.Add(CampaignVal);

ws.Cell(1, 1).Value = "Add Group View";
ws.Cell(1, 2).Value = "Clicks";
ws.Cell(1, 3).Value = "Views";
ws.Cell(1, 4).Value = "CR";

ws.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.Yellow;

lstCampaignSheet = lstCampaignSheet.Where(d => d.Clicks > 0).OrderByDescending(d => d.CR).ToList();
IXLRange range = ws.Cell(2, 1).InsertData(lstCampaignSheet.AsEnumerable());

ws.Range(range.FirstRow().RangeAddress.LastAddress, range.LastCellUsed().Address).AddConditionalFormat().WhenEqualOrGreaterThan(6)
.Fill.SetBackgroundColor(XLColor.Red);
ws.Range(range.FirstRow().RangeAddress.LastAddress, range.LastCellUsed().Address).AddConditionalFormat().WhenEqualOrLessThan(6)
.Fill.SetBackgroundColor(XLColor.Yellow);

ws.Columns().AdjustToContents();
}

wb.SaveAs(OutputFilePath);
}
}

return true;
}
private DataTable ProcessInputFile(string filePath)
{
//Create a new DataTable.
DataTable dt = new DataTable();

//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);

//Loop through the Worksheet rows.
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
//Use the first row to add columns to DataTable.
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
}

return dt;
}
}
public class OutputResult
{
public string AddGroupView { get; set; }
public int Clicks { get; set; }
public int Salary { get; set; }
public decimal CR { get { return Salary / Clicks; } }
}

Permutation Algorithm for Words C#

2021-03-10T21:55:00.001-08:00

Introduction

A permutation, also called an “arrangement number” or “order, ” is a rearrangement of the elements of an ordered list S into a one-to-one correspondence with S itself. A string of length n has n! permutation.

Below are the permutations of string ABC.
ABC ACB BAC BCA CBA CAB

Sample Code

String input = "A B C D";

List<string> str = input.Split(' ').ToList();
PermutationAlgorithm obj = new PermutationAlgorithm();
var lstPermute = obj.Permute(str);

Algorithm

using System.Collections.Generic;
using System.Text;
using System.Linq;
public class PermutationAlgorithm
{

public List<Permutation> Permute(List<string> str)
{
int n = str.Count;
StringBuilder sb = permute(str, 0, n-1, new System.Text.StringBuilder());

var lst = sb.ToString().TrimEnd('~').Split('~').ToList();
return lst.Select((x , index )=> new Permutation { KeyWords = x,Index = index+1 }).ToList();
}

/**
* permutation function
* @param str string to
calculate permutation for
* @param l starting index
* @param r end index
*/
private StringBuilder permute(List<string> str,
int l, int r, StringBuilder output)
{
if (l == r)
{
output.Append(string.Join(" ", str) + "~");
}
else
{
for (int i = l; i <= r; i++)
{
str = swap(str, l, i);
permute(str, l + 1, r, output);
str = swap(str, l, i);
}
}
return output;
}

/**
* Swap Characters at position
* @param a string value
* @param i position 1
* @param j position 2
* @return swapped string
*/
public List<string> swap(List<string> a,
int i, int j)
{
string temp;
temp = a[i];
a[i] = a[j];
a[j] = temp;
return a;
}

}
public class Permutation
{
public int Index { get; set; }
public string KeyWords { get; set; }
}

Operation is not valid due to the current state of the object Error

2012-12-10T22:53:00.003-08:00

I got this error when I tried to save a Page with lots of form fields to SQL Server Database
The default max number of form fields that can be post is 1000.
Solution:
In order to solve this error add this Line to your Web.Config File:

   1:  <appSettings>

   2:  <add key="aspnet:MaxHttpCollectionKeys" value="10000" />

   3:  </appSettings>

Programmatically Creating an ASP.NET GridView Control

2012-11-03T12:44:00.002-07:00

The biggest project I’ve worked on in the past year involved building almost all of the ASP.NET controls for a page programmatically in the code-behind and putting them into a Placeholder, rather than declaring them in the markup file.

Many ASP.NET controls are relatively easy to build this way. Buttons, TextBoxes, Labels, DropDownLists, even SqlDataSources can be created using a few simple lines of code.

Adding a print Button can be this simple:

Dim btnPrint As New Button
btnPrint.Attributes.Add("onclick", "javascript:window.print();")
btnPrint.Visible = False
Placeholder1.Controls.Add(btnPrint)

The instructions for adding simple controls such as these can be easily found on MSDN and many other sites. Unfortunately, instructions for building a complex GridView were nowhere to be found. Take heart: It can be done!

Adding a GridView control is generally much more complicated than this, and also requires many more attributes. Each column must be declared and added to the GridView prior to the GridView itself being added to the Form / Panel / Placeholder, etc. Also, attributes like sorting, paging, Edit / Cancel Buttons must be taken into consideration.

Here is an example of the VB code for a full-featured GridView:

Dim gvExample As New GridView
With gvExample
.ID = "gvExample"
.CssClass = "gridview"
.DataSource = dvExample
.AutoGenerateColumns = False
.CellPadding = 4
.DataKeyNames = New String() {"ExampleID"}
.ForeColor = Drawing.ColorTranslator.FromHtml("#2a2723")
.GridLines = GridLines.None
.Width = Unit.Percentage(100)
.AllowSorting = True
.AllowPaging = False

Dim strHeadBack As String = "#ffcb00"
Dim strPagerBack As String = "#009ddb"
Dim strForeColor As String = "#000000"

.HeaderStyle.BackColor = Drawing.ColorTranslator.FromHtml(strHeadBack)
.HeaderStyle.Font.Bold = True
.HeaderStyle.ForeColor = Drawing.ColorTranslator.FromHtml(strForeColor)

.RowStyle.BackColor = Drawing.ColorTranslator.FromHtml("#FFFBD6")
.RowStyle.ForeColor = Drawing.ColorTranslator.FromHtml("#2a2723")
.RowStyle.HorizontalAlign = HorizontalAlign.Center

.AlternatingRowStyle.BackColor = Drawing.Color.White

.BorderColor = Drawing.ColorTranslator.FromHtml("#d80073")
.BorderStyle = BorderStyle.Groove

.Columns.Clear()

Dim ViewButton As New ButtonField
ViewButton.HeaderText = "View"
ViewButton.ButtonType = ButtonType.Button
ViewButton.Text = "View"
ViewButton.CommandName = "ViewExample"
.Columns.Add(ViewButton)

Dim EditButton As New TemplateField
EditButton.HeaderText = "Edit"
EditButton.ItemTemplate = New MyButtonTemplate ' This is a user-defined class that creates this Button
EditButton.Visible = bEditVisible
.Columns.Add(EditButton)

Dim Voided As New CheckBoxField
Voided.HeaderText = "Voided"
Voided.DataField = "Voided"
Voided.ReadOnly = True
Voided.Visible = bVoidVisible
.Columns.Add(Voided)

Dim ExampleDate As New TemplateField
ExampleDate.HeaderText = "Date/Time"
ExampleDate.SortExpression = "Date_and_Time"
ExampleDate.ItemTemplate = New MyLabelTemplate ' This is a user-defined class that creates this Label
ExampleDate.ItemStyle.Wrap = False
.Columns.Add(ExampleDate)

Dim ShortColumn As New BoundField
ShortColumn.ItemStyle.CssClass = "left"
ShortColumn.HeaderText = "Short Column"
ShortColumn.SortExpression = "Short_Column"
ShortColumn.DataField = "Short_Column"
ShortColumn.ItemStyle.Wrap = True
ShortColumn.ItemStyle.Width = 150
.Columns.Add(ShortColumn)

Dim LongColumn As New BoundField
LongColumn.ItemStyle.CssClass = "left"
LongColumn.HeaderText = "Long Column"
LongColumn.SortExpression = "Long_Column"
LongColumn.DataField = "Long_Column"
LongColumn.ItemStyle.HorizontalAlign = HorizontalAlign.Left
LongColumn.ItemStyle.Wrap = True
LongColumn.ItemStyle.Width = 200
.Columns.Add(LongColumn)

Dim CreatedBy As New BoundField
CreatedBy.HeaderText = "Created By"
CreatedBy.SortExpression = "CreatedBy"
CreatedBy.DataField = "CreatedBy"
.Columns.Add(CreatedBy)

.Visible = True
.PageIndex = GridViewPageIndex ' I used a Session variable to store the page number
Placeholder1.Controls.Add(gvExample)
.DataBind()
End With

Separate routines that handle the Sorting and PageIndexChanging events must be manually created if you want these functions to be included with your GridView. In these modules, you can use Session variables to store the page numbers and sort direction. It might be possible to use ViewState variables instead, though I did not try that here. Also, a module that handles RowCommand events must be created if any buttons that are added to the GridView do something that is row-specific.

It is certain that creating a GridView programmatically is much more difficult than dragging and dropping from the Toolbar onto the Design page. However, that may not always be the best way if you are creating a highly dynamic application, such as one where even the fundamental design of a page is database-driven.

Add Normal Column and Template Column Dynamically in Asp.net GridView

2012-11-03T12:38:00.005-07:00

in most of case we build GridView statically or let asp.net do it it self but what if we want to add Columns to GridView Dynamically.like you may want to add TemplateField with Several controls within it with code too.

Fist we will see how to add Normal column to GridView at runtime.

BoundField FirstName = new BoundField();
FirstName.DataField = "Some Database Field";
FirstName.HeaderText = "First Name";

GridView1.Columns.Add(FirstName);

Next , How to add Field With Custom template.to add Column with custom template you need to First Define Template using code for that you need to implement ITemplate interface. here is sample code about doing it .

/* Create Template Field by Implementing ITemplate */
public class MyCustomTemplate:ITemplate
{
    public void InstantiateIn(System.Web.UI.Control container)
    {
        CheckBox cb = new CheckBox();
        cb.ID = "testCheckBox";
        cb.Text = "Test";
        container.Controls.Add(cb);
    }
}
/* Adding Field With Template */
TemplateField tf = new TemplateField();
tf.HeaderText = "Custom Template Field Column";
tf.ItemTemplate = new MyCustomTemplate();

GridView1.Columns.Add(tf);

above example We Created Template of Item within the column with one check box by Implementing ITemplate Interface which we can set as Item Template while we Add TemplateField in GridView.

How to create template columns dynamically in a grid view

2012-11-03T12:38:00.001-07:00

Sample Program:

Imports System.Data
Imports System.Web.UI.WebControls
Public Class MyCustomTemplate
Implements ITemplate
Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) Implements System.Web.UI.ITemplate.InstantiateIn
Dim cb As New CheckBox()
cb.ID = "PageID"
AddHandler cb.DataBinding, AddressOf CheckBox_DataBining
container.Controls.Add(cb)
End Sub
Public Sub CheckBox_DataBining(ByVal sender As Object, ByVal e As EventArgs)
Dim button As CheckBox = TryCast(sender, CheckBox)
Dim container As GridViewRow = CType(button.NamingContainer, GridViewRow)
Dim dataValue As Object = DataBinder.Eval(container.DataItem, "id")
If Not IsDBNull(dataValue) Then
button.Text = dataValue.ToString()
End If
End Sub
End Class
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub _Default_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
Dim tf As New TemplateField()
tf.HeaderText = "Custom Template Field Column"
tf.ItemTemplate = New MyCustomTemplate()
gvoutput.Columns.Add(tf)
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim dt As New DataTable

dt.Columns.Add("id", GetType(Long))
dt.Rows.Add(1)
dt.Rows.Add(2)
dt.Rows.Add(3)
dt.Rows.Add(4)

gvoutput.DataSource = dt
gvoutput.DataBind()

End If
End Sub

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Try
Dim a As Long = 0

For i As Integer = 0 To gvoutput.Rows.Count - 1
Dim row As GridViewRow = gvoutput.Rows(i)
Dim isChecked As Boolean = CType(row.FindControl("PageID"), CheckBox).Checked
If isChecked Then
a = a + 1
End If
Next

Response.Write(a)
Catch ex As Exception

End Try

End Sub
End Class

Display a certain number of rows to columns on crystal report

2012-11-03T12:21:00.003-07:00

For example

SQL Rows

Name

Fred

Jack

Dan

Bryan

Chris

Paul

William

This is what i need on the Crystal report

___Fred ___Jack ___Dan ___Bryan

___Chris ___Paul ___ William

Solution:

Here's what you need to do:

1) Place the fields on the details section

2) Go the Section Expert > select Details section > check "Format with multiple columns"

3) Then go to the Layout tab in the same window, set the width to 2.00 inches (you would need to play with this depending on the size of field)

4) Then check the option "Across then Down"

5) To draw the line, simply use the Line Object before the field

Web & Software Techniques and Tips

No creatable public component detected

Add an XML Sitemap to your Blogger Blog

Install and uninstall Windows services using InstallUtil.exe utility

Uninstall using InstallUtil.exe utility

Refresh the list of remote branches in Visual Studio Team Explorer Visual Studio 2017/2015/2019

Calculate Date and Time Difference Between Two Dates C#

Copy paste not working issue in Remote Desktop (RDP)

Trim last character from a string or each word C#

PHP: short_open_tag Not working in php 5.3.1

<? ?> tags not working in php 5.3.1

Remember to set UpdatePanel's UpdateMode to Conditional

UpdateMode property of the UpdatePanel

GROUPING SETS Clause in SQL Server

How to get first record out of each group from the result retrieved by using group by command

PHP Coupon Script v 4.0

PHP Coupon Script

Coupon Script Features

Site Settings

Google adsense and Banners

Listing settings

Site Themes/Design

Tags

Websites/Stores Listing

Coupons

Users

Comments

Admins/Moderators

The SQL Server (RELYONDB) service on local computer started and then stopped.

Using the Microsoft Web Platform Installer

Introduction

Figure 1: "WebPI Spotlight" tab

Figure 2: "Products" tab

Server

Figure 3: Searching for IIS components in WebPI

Figure 4: More information on the IIS: FTP Publishing Service 7.5

Frameworks

Figure 5: Frameworks

Database

Figure 6: Database

Tools

Tools

Install Applications from the Microsoft Web Application Gallery

Figure 8: Web Applications

PHP: Tags Not Working in PHP 5.3.x under UNIX / Linux

Solution

SQL - Calculating time difference in Minutes, Hours, Days, Weeks, Months, Years

Introduction

Simple example

Parsing a URL with SQL Server Functions

Problem

Solution

Example Table and Data

cenario 1 - Base URL

Scenario 2 - File Name

Scenario 3 - Query Parameters

ASP.NET MVC - Data Binding for Hierarchical Views or Parent Child Relation C#.NET Core and .NET Standard

Introduction

Scope

Using ASP.NET MVC model binding to present and collect hierarchical form data in a hierarchical structure.

Structure

Entities and Relationships

Customer.cs

Order.cs

Presenting Data

Customer/Index View

Customers.ViewModels\CustomerDisplayViewModel.cs

Order/Index View

Views\Order\Index.cshtml

CustomerOrdersDisplayViewModel.cs

OrderDisplayViewModel.cs

OrdersController Action for Index HttpGet

Controllers\OrderController.cs

All that this controller action needs to do when passed a CustomerID from the Customer/Index view is pass that value to the appropriate repository method and take the resultant data model, an instance of the CustomerOrdersListViewModel class, and pass it to the view.

Saving Data

What Not to Do

Correctly Binding Collection Data

Posting Display Data

Data\Orders\OrdersRepository.cs

ClosedXml Excel Process Examples C#

All that this controller action needs to do when passed a `CustomerID` from the Customer/Index view is pass that value to the appropriate repository method and take the resultant data model, an instance of the `CustomerOrdersListViewModel` class, and pass it to the view.