Friday, August 24, 2018

Sample Use Case document for any development


User case is important role play in our any new development. The developer well know the post and per-condition of what he want to develop to get some clear idea. Without any use case or wire-frame if we develop any application then defiantly have more modification/Changes after the release. So best before start any new module developer should have Use Case document.

The below one sample template for use case diagram.

USE CASE ID:
UC-01
DESCRIPTION:
This use case describes the xxxxxx xxxxx sample tool
REQUIREMENT ID(S):
RE-1.1, RE-1.2 ….. RE-1.9
ACTOR(S):
 User
PRE-CONDITIONS:
Configured tool and page to submit
POST-CONDITIONS:
Post conditions.
BASIC FLOW:

 Submit registration.
Verify the user name
Save the user to database
Display the result
EXCEPTION FLOW 1:

User name or email unavailable.
Not valid data entered.

Wednesday, June 14, 2017

How to Convert Dataset or DataTable to List collection

Some time we need to convert Dataset or DataTable values to List items for easy filter or any search in the data. List collection have more feature compare then Dataset. Like we can select top 3 users or role is admin or etc. So we can write own LINQ query in the list and get relevant information without using any loop. This will help to improve you application performance as well.

The given below example I have one userdetails class have properties of user information and another one is data set to list conversion method. If you pass dataset to the function then the function will return collection of userdetails list.

Sample Code:


Convert DataSet to List Function:
        public IList ConvertToList(DataSet ds)
        {
            List lst = new List();
            try
            {
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var myData = ds.Tables[0].AsEnumerable().Select(r => new UserDetails
                    {
                        UserID = r.Field("UserID"),
                        UserName = r.Field("Username"),
                        EmailID = r.Field("EmailID"),
                        Name = r.Field("DisplayName"),
                        RoleID = r.Field("RoleID").ToString(),
                        Sex = r.Field("Sex"),
                        DOB = r.Field("DOB"),
                        Mobile = r.Field("Mobile"),
                        IsActive = r.Field("IsActive")
                    });
                    lst = myData.ToList();
                }
            }
            catch (Exception ex)
            {
                Comman.ErrorLog(ex.Message);
            }
            return lst;
        }








  
 
UserDetails.cs    
//User detail Class information
        public class UserDetails
        {
            public string UserID { get; set; }
            public string UserName { get; set; }
            public string EmailID { get; set; }
            public string Name { get; set; }
            public string RoleID { get; set; }
            public string Sex { get; set; }
            public DateTime? DOB { get; set; }
            public string Mobile { get; set; }
            public bool IsActive { get; set; }
        }



Monday, June 12, 2017

How to Generate SQL Query in excel sheet two column



Today I have faced one problem in excel sheet. I have a situation need to update user email id details from excel to my client database. The users email list is more than 1000 users.

For this update to excel we can do line by line update to the database or import into database and write the join query for update. But the simple way is we can generate INSERT/UPDATE SQL query and execute in to the database. For my situation I have already user data in the database. So I want to update the emailID based on the employeeID contition.

For that the below Excel comment will help to generate the SQL UPDATE query with simple steps and I can update with in 5mintus.

=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")


Excel Sheet:

Emp ID Name EMail id Project
1110 Karthik Karthik@gmail.com Sprots Domain
1111 Kumar Kumar@gmail.com Sprots Domain
1112 Vijay Vijay@gmail.com Sprots Domain
1113 Yuvan Yuvan@gmail.com Education Domain


Excel Sheet Query Generator:
=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Output SQL Query :
SQL Query
UPDATE tb_employee set EMPL_EMAILID= ('Karthik@gmail.com') WHERE EMPL_ID= (1110);
UPDATE tb_employee set EMPL_EMAILID= ('Kumar@gmail.com') WHERE EMPL_ID= (1111);
UPDATE tb_employee set EMPL_EMAILID= ('Vijay@gmail.com') WHERE EMPL_ID= (1112);
UPDATE tb_employee set EMPL_EMAILID= ('Yuvan@gmail.com') WHERE EMPL_ID= (1113);

Finlay copy all the sql query from excel sheet and execute into the database. Its take 5 mints to complete your task.

Try this and let me know your feedback.

Friday, May 19, 2017

Cannot Start Microsoft Office Outlook. Cannot open the Outlook window. Invalid XML, The View Cannot be Loaded.

Today when i switch on my computer and opened the outlook application. suddenly one pop up message displayed "Cannot Start Microsoft Office Outlook. Cannot open the Outlook window. Invalid XML, The View Cannot be Loaded." . I dont know what should i do next?

Then google helped me to get my problem resolved. Below is the way to get the problem get solved.

Start-> Run or press the Windows Logo + R on your keyboard.
outlook.exe /resetnavpane (note the SPACE)


Note: outlook.exe and need to give (SPACE)  then /resetnavpane and type enter. Then the outlook will start with out any issue.

Enjoy the day.

Thursday, December 15, 2016

Display number of days/weeks in the year using SQL Query

Each time if i develop any report i need to write SQL Query for list out number of days or week in the year. So here i am giving you the simplified way to get the number of days name,date and week number of each date. One of my requirement is to display each week the sales report for the product. So the below query will help you to list out number of weeks in the year with week name.


SQL Query:

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS DateList ,
DATENAME(dw, @FirstDateOfYear) AS DayName

UNION ALL

SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.DateList),
DATENAME(dw, DATEADD(d, 1 ,cte.DateList)) AS DayName
FROM cte
WHERE DATEADD(d,1,cte.DateList) < @LastDateOfYear

)
SELECT *  ,'Week '+Convert(nvarchar,DATEPART(wk,DateList)) as WeekName
FROM CTE

OPTION (MaxRecursion 370)


The Result :


DayID DateList DayName WeekName
1 1-Jan-16 Friday Week 1
2 2-Jan-16 Saturday Week 1
3 3-Jan-16 Sunday Week 2
4 4-Jan-16 Monday Week 2
5 5-Jan-16 Tuesday Week 2
6 6-Jan-16 Wednesday Week 2
7 7-Jan-16 Thursday Week 2
"" "" "" ""
"" "" "" ""
366 31-Dec-16 Saturday Week 53


The above SQL query we will get the day count start from 1 to end with 365 or 366 and display the date and the Date name. Finlay added week number in the list so when you want only week of the day or number of weeks in the year you can use only the WeekName column.You can use DISTINCT option to display only week number on the display result. We can make this query to our convenience way to get proper result set.