Excel Date and Time - Validating date time range

Excel Date and Time: Previous date and some certain time

We have some scheduled job, but if we would like to cancel the job, we have to make the cancellation before the previous day 3:00pm. For example, for the job scheduled on 1/11/2017 1:00:00 PM, the cancellation before 1/10/2017 3:00:00 PM is valid. The question for us is to judge each cancellation validation.

Step 1: Extract the date part from scheduled on column and get one day ahead.

 =DATEVALUE(MONTH(A2) & "/" & DAY(A2-1) & "/" & YEAR(A2))

Step 2: To understand the date format better, I have to know what the date value is in the general format. Change the format for column C to general, and then you will see below.  Oh, yes, the dates are just some number but in date format.

Step 3: Until here, you may think about, how is 3:00:00 PM ‘s  number value. It’s easy. Let’s just type 3:00:00 PM in any empty cell, and then change it to number format. You can see it’s 0.625.

 Step 4: Let’s add the 0.625 to the dates we just got. That’ll be the number value for the previous day 3:00:00 PM.

Step 5: Just compare the Cancelled On column with the Scheduled On (Date and Time) column to get the final result.

Some comments:

If you want to calculate date, transfer a date from date format to a serial number by DATEVALUE is usually helpful.

Amazon SES Cross-Region Resiliency

With the recent outage of AWS in the east-1 region, it took down not only the Simple Storage Service (S3) but also others services including the Amazon Simple Email Service. SES can be a critical piece in applications as they won't be able to send any notifications to the users.

To avoid outage or  just improving theway to distribute the emails delivery, I highly recommend set up the service in more than one region.

SES is region based. You have to verify your domains or email addresses on each region.

 As of 3/3/2017, it is only availble on the following regions

Region name	API (HTTPS) endpoint	SMTP endpoint
US East (N. Virginia)	email.us-east-1.amazonaws.com	email-smtp.us-east-1.amazonaws.com
US West (Oregon)	email.us-west-2.amazonaws.com	email-smtp.us-west-2.amazonaws.com
EU (Ireland)	email.eu-west-1.amazonaws.com	email-smtp.eu-west-1.amazonaws.com

These are the steps to enable SES on another region on domain level

1. Log into AWS Console
2. Type SES on the dashboard search and select the first result
1. 
3. Now, on the top-right side of the page, click on the regions menu and select the region that you will want to use SES. It should show only the regions that have SES available
1. 
4. Select "Domains" on the left menu, you will see that the domain that you have verified in your main region is not in the new region, that's where most of us think it should be available without doing anything, but that is not the case, we have to verify the domain on each region. 
1. 
5. Now select "Verify a New Domain" and type your domain name, you can also check the Generate DKIM Settings if you want your emails to comply with DMARC.
1. 
6. After clicking on "Verify This Domain", you should get the TXT record that have to be added on the DNS.
1. 
7. Log into your DNS provider where you have your domain and proceed to add the txt record.
8. It takes AWS up to 72 hours to verify the domain.
1. 
9.  Once AWS has verified the domain, you should receive an email with a subject like "Amazon SES Domain Verification SUCCESS for {Your domain} in region US West (Oregon)"
10. You can now check and confirm on AWS console.
1.  
11. AWS by default will put the new domain under the "Sandbox" mode, thus, you won't be able to send emails to reciepients without confirming. You will have to create a support ticket and ensure you select the option "Service Limit Increase" and Limit type to "Limit Type".
1. 
12. Once AWS approves the request, you should be able to send emails without confirming the recipients.

Note: These steps work also when you are setting up the SES for the first time.

I would also recommend set up another email service as backup like SendGrid or Mandrill. The chance that SES goes down on two regions is very low, but if it happens we'll face the same situation we did on Feb 28th 2017. 

Task scheduler, when, why and how.

Recently, I found two reasons that led me to write this post. The first reason is a friend asked me to help him to set up an application that needs to be run periodically, and the second is, I was handling an old project, which needs to execute an application every night, but it was done in windows services with a thread with timer, my guess was that the previous developer looked that was easier that scheduling a task, seriously?. Textually, I found this post. " definition a Windows Service.
Service
A program, routine, or process that performs a specific system function to support other programs, particularly at a low (close to the hardware) level. When services are provided over a network, they can be published in Active Directory, facilitating service-centric administration and usage. Some examples of services are the Security Accounts Manager service, File Replication service, and Routing and Remote Access service.
". So, unless we have a real reason to use a windows service as a scheduled task otherwise the best approach is to use a Task Scheduler.

Search stored procedures by text

While working with a big database with hundreds of stored procedures, it is a pain if you don't remember the name of certain methods. So, a way to look for the method is searching  by text.

[USE DATABASE_NAME]

SELECT OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%text_to_search%'




Reference:
http://stackoverflow.com/questions/4812962/text-search-in-stored-proc-sql-server

Insert, Update and Delete using Entity Framework in Asp.MVC

In this tutorial, I will walk through all the steps necessary to create a

simple CRUD process in Asp.net MVC using Entity Framework

We'll start from adding a new Item to the final view

Go to Add New Item menu

We'll add an ADO.NET Entity Dta Model located in the data tab

Since we are generating the Model from database, we'll select the first option

the second option is to create a new model without previous database set up.

It's a good option if you are starting a new project from scratch

Next step is choose the database to be used in the project, .Net will

generate automatically the connection string

Now, you have to select the objects that you want to use in your project.

For this example, we'll use only the person table

This is the result object

Now, returning to MVC, I've added the Controller, Model and Views for person

You'll see their content in the upcoming screenshots

In the controller we have 4 actions, Index, Edit (get), Edit (post) and Delete

The model

Here is where the Entity object takes action.

First you'll need an instance of the entity object.


We'll use linq to performance SELECT statements, for INSERT

EF provdes a method AddObject(EntityObject), for UPDATE

ApplyCurrentValues(EntityObject), but before you have to

set to which row to want to update the values, DELETE

is done by calling DeleteObject(EntityObject)

In order to apply the changes in the database you have

to call the function SaveChanges.

And the Views

Index Views

Edit

If you have set everything correctly, the views should like like

Index

Add New Person

Edit Person

Delete

By clicking on delete, a confirmation dialog pops up

it'll delete only when hit on Yes. It's very important

ask for confirmation processes that involve update or delete

information

View refreshed

Refereces

http://msdn.microsoft.com/en-us/data/ee712907

Membership, Profile and RoleManager on ASP.NET MVC II

We have coded more than once applications that need to handler user authentication and autorization in a certain way.

Perhaps we have done this using MembershipProvider in WebForm technology. However, Microsoft launched MVC Framework

that immediately became a big choice for Web developer who look for a fast and organized way to create Web Applications. Now,

we wonder how to implement MembershipProvider in MVC.



In the following tutorial will guide you to create a MembershipProvider using your AspNetSqlRoleProvider.

In other words, you will use your own database to store the user authentication information. Furthemore,

you will be able to manage the autorization based on role in the application, allowing user to access

specific views.





Let’s start.

  Step 1: Open the Web.Config file and look for the connectionStrings tag, you will find  the ApplicationServices connection string,

  if you don't, make sure your project is connected to a database server.

  Also, make sure on the connection string you have specified the correct database. As the example below you see that it is connected to TestDb.

<add name="ApplicationServices"

connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TestDb;Integrated Security=True"

providerName="System.Data.SqlClient" />

Step 2: Check on the web.config file if there is already a tag for authentication, membership,profile and roleManager.

As you can see on the example below the membership provider used on the application is AspNetSqlMembershipProvider

and it is linked this connection string  "ApplicationServices". You can customize its name

  <authentication mode="Forms">

  <forms loginUrl="~/Account/LogOn" timeout="2880" />

  </authentication>

  <membership>

  <providers>

  <clear/>

  <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"

    enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"

    maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"

    applicationName="/" />

  </providers>

  </membership>

  <profile>

  <providers>

  <clear/>

  <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />

  </providers>

  </profile>

  <roleManager enabled="false">

  <providers>

  <clear/>

  <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />

  <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />

  </providers>

  </roleManager>

 Until here you should not have done anything, unless you needed to change the connection string.

Step 3: Here is when the party starts. Run this executable [drive:]\%windir%\Microsoft.Net\Framework\v2.0.50727\aspnet_regsql.exe.

This will create tables, views and stored procedure on the database that you specify.

   

Step 4: when you execute the app I mentioned above, it'll pop up this window

   
   

Step 5: Since we want to create the sql server application, select the first option  "Configure Sql Server for application services" and Hit next

       

Step 7: Now we need to set the server name and the target database, be careful with choosing the target database,

 you might not want to create the bunch of objects in a wrong location, that may bring problem further.

   

Step 8: Click on Next

Step 9: If everything went okay, you will see the success message

   

Step 10: In order to check  what the app did on the database, open an instance of Microsoft SQL Server Management Studio,

you will see a set of tables, views and stored procedures were created.

   

Step 11: Now, returning to Asp.net mvc application, on Project menu select the last option "Asp.NET Configuration".

The Web Site Administration Tool lets you view and manage the Web site configuration through a simple Web interface.

Here is a good   documentation
what this tool does for us

       
   

Step 12: On the interface, we go directly the Security tab

Step 13: Create an user, you need to set up a password with minumn length of 6, otherwise you won't pass its validation.

Step 14: Now is time to enable the Role Manager, click on Security tab to go to the main screen. Click on Enable Roles

   

Step 15: Hit Create or Manage roles

   

Step 15: Add role

   

Step 16: Now, here we should manage the role, in other words, assign role to the user

Step 17: look for the user that you want to set role. In our example, we will set the role for the user "test"

Step 18: Check  User is in Role and we are done.

   

Until this point, we should enable the  the membership provider as well as role manager for our application.

Step 19: Going back to Visual Studio, a window should pop up, let's say Yes to All.

This is because we were changing the web.config file via interface.

Step 20: It's time to test it. Create a new controller. I called it "TestRoleController"

Step 21: Here is the important part, on the top of controller tag, we add the following [Authorize(Roles="admin")],

admin is the role that we defined previously.

Step 22: Now, you need to add the View. Create a folder under View folder, then add a new view called Index.aspx

Step 23: On Index.aspx add some text

Step 24: F5 to run the project

Step 25: You'll get into the home page

Step 26: Now when you try to get into a restricted page, you'll be redirected to the login page

 Step 27: If we provide the correct information, we should be able to get to the TestRole index page,

we will use the same user authentication we created on Step 13.

 Step 28: Eureka...

 
  I hope this tutorial could help you to have a better idea how to implement Membership provider and Role manager in asp.net mvc

    References
   

   

   
     http://msdn.microsoft.com/en-us/library/yy40ytx0.aspx   

     http://msdn.microsoft.com/en-us/library/yy40ytx0.aspx

     http://johnnycoder.com/blog/2010/01/04/getting-started-with-aspnet-membership-profile-and-rolemanager/