Mahmoud ElMansy: knowledge meant to be free

TSQL – Concatenate Rows Into On Column Using For XML Path()

This case always happen when you are working  with large business that you need Concatenate rows into one string .

one of the methods to do that is to Use XML Path() to turn rows into one XML with no tags.

As follows
1)  Declare To Tables With one to many relation

DECLARE @Categories TABLE
(
CategoryID INT,
Category_Code VARCHAR(16),
Notes VARCHAR(128)
);

DECLARE @Products TABLE
(
ProductID INT,
CategoryID INT,
Product_Code VARCHAR(16),
Notes VARCHAR(128)
);

2) Fill Tables with Dummy Data

INSERT @Categories SELECT 1,'Cat_1','Notes Notes Notes'
UNION ALL SELECT 2,'Cat_2','Notes Notes Notes'
UNION ALL SELECT 3,'Cat_3','Notes Notes Notes';

INSERT @Products SELECT 1,1,'Product_1','Notes Notes Notes'
UNION ALL SELECT 1,1,'Product_1','Notes Notes Notes'
UNION ALL SELECT 2,1,'Product_2','Notes Notes Notes'
UNION ALL SELECT 3,1,'Product_3','Notes Notes Notes'
UNION ALL SELECT 4,2,'Product_4','Notes Notes Notes'
UNION ALL SELECT 5,2,'Product_5','Notes Notes Notes'
UNION ALL SELECT 6,2,'Product_6','Notes Notes Notes'
UNION ALL SELECT 7,3,'Product_7','Notes Notes Notes';

3) Run The Query

SELECT *,
STUFF( (SELECT ', ' + p.Product_Code FROM @Products AS p WHERE p.CategoryID=cat.CategoryID FOR XML PATH('')),1,1,'') Products
FROM @Categories AS cat

The result
8-21-2014 11-05-14 AM

8-21-2014 11-05-27 AM

8-21-2014 11-06-01 AM

How to make fixed length digit

this is one of the easy posts for me i will talk about how to fixed length digit .
means i have column stored in the DB and i want to show hime with fiexd lenght like “00001 or 00123″

that is pretty much easy using one of the sql functions Called STUFF

this function as discriped in the MSDN as “The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

writing the code

DECLARE @replaced VARCHAR(64)='123'

SELECT STUFF('0000000000', 11-LEN(@replaced), LEN(@replaced), @replaced);
-- note that 11 here is the length of your fixed string +1

Dynatree Lazy Load Example With asp.net

I faced issue on my work we use jquery Dynatreeat one of our projects and for performance issue i decide to use the lazy load feature that comes with Dynatree Plugin ,but i found no example in asp.net to how to achieve this.

So i made an example and will go in details how to make it up and running.

First : here is a link for code.google And plugins.jquery

And final link Plugin Homepage

And now my favorite part.

Second : We will make asp.net web site add page and add new aspx webpage call it “Dynatree.aspx” we will reference.

    <link href="CSS/Jquery.css" rel="stylesheet" type="text/css" />

    <script src="/Scripts/jquery-1.8.2.js" type="text/javascript"></script>

    <script src="/Scripts/ui.core.js" type="text/javascript"></script>

    <script src="/Scripts/jquery.dynatree.js" type="text/javascript"></script>

Third: in the page code behind file put this method.

///
/// Function get Tree leaves
///

//////
[System.Web.Services.WebMethod]
//this part is important to make asp convert result to json for us
[System.Web.Script.Services.ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Json)]
public static List GetNodes(string key)
{

List lstDynaTreeNodes= new List();
string NodeTitle = "Tree Node ";
//First State Meanes load the first Row of the tree
if (key=="0")
{
//load data for intial the tree firt row of data
}
else
{
// load data (the sub items) for the given tree branch
}
int NodeNum;
for (int i = 1; i &lt;= 5; i++)
{
NodeNum = new Random().Next(i, i * 20);
lstDynaTreeNodes.Add(
new DynaTreeNode()
{
isFolder = true,
isLazy = true,
key = NodeNum.ToString(),
Title = NodeTitle + NodeNum.ToString()
}<strong>
);
}

return lstDynaTreeNodes;
}

Fourth : Now to the ajax call the will initiate the tree and get data.

// --- Initialize the sample tree
$("#tree").dynatree({
title: "Lazy Tree",
rootVisible: true,
fx: { height: "toggle", duration: 300 },
initAjax: {
type: 'POST',
url: "/Dynatree.aspx/getnodes",
data: JSON.stringify({ key: '0' }),
dataType: "json",
contentType: 'application/json; charset=utf-8'

},

onActivate: function (dtnode) {
$("#divCurrent").text(dtnode.data.title);
}
, onLazyRead: function (dtnode) {
dtnode.appendAjax({
type: 'POST',
url: "/Dynatree.aspx/getnodes",
data: JSON.stringify({ key: dtnode.data.key }),
dataType: "json",
contentType: 'application/json; charset=utf-8'
});
}

});

That is it you will have you lazy load Tree you can download
Full Example and enjoy the code

Implementing Custom Log4Net using AdoNetAppender and simple Wrapper with Asp.Net Mvc

Assalamo Alikom
This post comes after a quite long pause, but I found this very useful article which encouraged me to write in this topic.

In this post i will Illustrate how to make Wrapper class(mimic structure, but simply hide complexity/advanced/unwanted functionality) to manage Log4net Classes in an manageable and easy way .

First Log4Net for whom don’t know it as described on their homepage

The Apache log4net library is a tool to help the programmer output log statements to a variety of output targets.

I want talk more about Log4Net and you can review full Documentation Here

Now To Code :)

I will build simple Mvc Web Application to illustrate how to make Log4net Up and running and make easy Wrapper
to implement and catch all logs.

Enough talking open your visual studio 2013 IDE.

1) Create Mvc Project Called Log4Net_MVC.

  • And add new Project of type Class library name it Log4NetWrapper.
  • Download load Log4net DLL from Here
  • Add Folder name it Dlls and but the log4net.dll,log4net.xml files and add reference to them on class library project
  • Run the “Log.sql” Script to your database

2) In the Mvc Project

  • Add reference to Log4NetWrapper project.
  • Configure the Log4Net.config file to meet your requirement and here is some example of how to do this.

I configured my own File and here is some keys to understand the file .

I added two Appender “Ways to write logs”

   <appender name="DebugAppender" type="log4net.Appender.DebugAppender"> <!-- To write on the OutPut Window for Visual Studio To Debug Errors  -->
   <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
<!-- To write logs into DataBase -->

I added two Custom Parameters i need to write “[ProjectID],[UserID]”
and to do that i added them to ‘commandText’

   <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception],[ProjectID],[UserID]) VALUES (@log_date, @thread, @log_level, @logger, @message,@exception,@ProjectID,@UserID)"/>

And added them to the ‘Parameter’ collection .

 <parameter>
        <parameterName value="@ProjectID"/>
        <dbType value="String" />
        <size value="20" />
        <layout type="log4net.Layout.PatternLayout" >
          <conversionPattern value="%property{ProjectID}" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@UserID"/>
        <dbType value="String" />
        <size value="20" />
        <layout type="log4net.Layout.PatternLayout" >
          <conversionPattern value="%property{UserID}" />
        </layout>
      </parameter>

Now i will refer to two important Keys

1- bufferSize this key means log4net store logs in queue and this the number where reache it will start flush the queue and write them into your database this very good for performance Choose wisely .

 <!-- number of logs to immediate write --><bufferSize value="1" />

2- useTransactions this key true/false

<useTransactions value="false" />

3) Add the Log4Net.config file to the root of your Mvc project and modify the Log4netConnectionString to refer to your database.

4) Add new class name it CommonMethods.

5) Add this global function to the class “WriteLog“.

public void WriteLog(object pMessage, Gehbeez.BaseDataLayer.Logging.LogType pLogType)
{
Gehbeez.BaseDataLayer.Logging.Logger.Instance.WriteLog(pMessage, pLogType,
new Gehbeez.BaseDataLayer.Logging.loggingParameter()
{ UserID = CommonMethods.Instance.CurrentUserID.HasValue ? CommonMethods.Instance.CurrentUserID.Value.ToString() : "0", ProjectID = "12" });
}

Here it is important to say that the loggingParameter is a class holds your custom parameter

Note That Parameter name must match your custom configuration parameters and i added this method to enable Log4Net to catch their values

 private void PushLoggingProperties(object loggingParameter)
        {
            if (loggingParameter != null)
            {
                Type attrType = loggingParameter.GetType();
                System.Reflection.PropertyInfo[] properties = attrType.GetProperties(
                               System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
                for (int i = 0; i < properties.Length; i++)
                {
                    object value = properties[i].GetValue(loggingParameter, null);
                    if (value != null)
                        log4net.ThreadContext.Properties[properties[i].Name]=value.ToString();
                }
            }
        }

6) Add these two additional Propertes

public static CommonMethods Instance
{
get
{
return new CommonMethods();
}
}

public int? CurrentUserID
{
get
{
try
{
return int.Parse(HttpContext.Current.User.Identity.Name);
}
catch { return null; }
}
}

7) Add now new Controller name it HomeController.

8) Past this code to Ilestrate how it will work.

public ActionResult Index()
{
// Normal Log
Log4Net_MVC.App_Code.CommonMethods.Instance.WriteLog("Execute Home&gt;&gt;&gt;&gt;Index", Gehbeez.BaseDataLayer.Logging.LogType.EnterMethod);

try {
//Sample
throw new Exception("Custom Exception");
}
catch (Exception ex) {/* Catch Error */ Log4Net_MVC.App_Code.CommonMethods.Instance.WriteLog(ex, Gehbeez.BaseDataLayer.Logging.LogType.Error); }

return View();
}

 

Now the result will be like this

Log Table

Log Table

Download full working sample Log4Net_MVC

Using Log4net for logging with asp.net and MVC log into SQL Table

I will write this article to show how to use Log4net with MVC.
First you need to read about Log4net

And for shower log4net is one of the best solutions for logging.

Now let us start

1) Download the Dlls from the site.

2) Add reference to it into your project.

3) Create The table in the attached project to make log Table.

4) If you are using MVC site add this section into your AssemblyInfo.cs .

using log4net.Config;
// this for Log4net
[assembly: XmlConfigurator(ConfigFile = "Log4Net.config", Watch = true)]

 

5) If the file “Log4Net.config” will be in a custom folder just use this code.

System.IO.FileInfo l4net = new System.IO.FileInfo(Server.MapPath("~/Log4Net.config"));
//System.IO.FileInfo l4net = new System.IO.FileInfo("Log4Net.config");// if you are using console or desktop application
log4net.Config.XmlConfigurator.ConfigureAndWatch(l4net);

6) To declare the log4net class.

//logger = log4net.LogManager.GetLogger(typeof(Program));//the Program is the name of the current class
// or you can use
logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

logger.Info("Test message for Log4Net");
logger.Warn("Test message for Log4Net");
logger.Error("Test message for Log4Net");
logger.Debug("Test message for Log4Net");

You can download a Consol sample TestLog4net

That is all hope you enjoyed the article.

SQL Server – REPLICATE function (repeat string N numbers of times)

REPLICATE function this is one of the sql server string functions.
This function repeat string or char N number of time

REPLICATE ( string_expression ,integer_expression )

Example.

DECLARE @st VARCHAR(10)='@1';
SELECT Replicate(@st,4)

The result.

@1@1@1@1

Problem Steps Recorder in Windows 7

This is a very handy tool if you want to record some step for documentation or send bug report to developer if you Work In a company that makes it’s own software
Or you you are making documentation for some steps you do

Here is your solution
Just type “psr” in the run .

5-11-2013 12-04-15 AM

Start recording .
5-11-2013 12-04-37 AM

Here is sample record file

Click to Dwonload

Calculate the number of days between two dates in C#

Simple way to Calculate the date the separtates two dates .

DateTime date1 = DateTime.Now.AddDays(1), date2 = DateTime.Now;
            Console.WriteLine((date1 - date2).TotalDays);
            Console.WriteLine((date1 - date2).TotalHours);
            Console.WriteLine((date1 - date2).TotalMinutes);
            Console.WriteLine((date1 - date2).TotalSeconds);
            Console.WriteLine((date1 - date2).TotalMilliseconds);
            Console.WriteLine((date1 - date2).Days);
            Console.WriteLine((date1 - date2).Hours);
            Console.WriteLine((date1 - date2).Minutes);
            Console.WriteLine((date1 - date2).Seconds);
            Console.WriteLine((date1 - date2).Milliseconds);

The difference between TotalDays and Days .
TotalDays return double the exact different .
Days return the different .

Excel MATCH Function (Compare two array of Data)

Match Function

Is a very handy Excel Function

It can be used if you have to lest of data and you want to compaire them.

Meanes if you have common data in the two list and you want to fiend the

Similara data and the position and fiend the order index of  it And to know the data that is unique.

For all that we can use MATCH  Function

Useage

First copy you data to excel sheet

And write in an empty column =MATCH(,,,)

It takes 3 Parameters

First Select The Hole first column

Second Select The Hole  Second Column

Third is an Option For Comparison

0 Exact Match

1 Less Than

-1 Greater Than

And Highlight the column and Get The difference

MatchExcell

How To Assign Data To All Models Objects And Pass Common Data To View

First let’s answer this question.
In what order are filters executed in asp.net MVC?

Answer

1) Authorization filters.
2) Action filters.
3) Response filters.
4) Exception filters.

We will talk about Action Filters
We will find 4 override method when inherits this class “System.Web.Mvc.ActionFilterAttribute” and there order on execution is.

  • OnActionExecuting
  • OnActionExecuted
  • OnResultExecuting
  • OnResultExecuted

In this example we will use “OnActionExecuted
This action called after action Executed and will pass Model to view
Here is the complete code of the “ViewModelFilterAttribute.cs”

  public class ViewModelFilterAttribute : System.Web.Mvc.ActionFilterAttribute
    {
        //this is a sample property that i can pass to this class
        public string Controller
        {
            get;
            set;
        }

        public override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            base.OnActionExecuting(filterContext);
        }
       
        public override void OnActionExecuted(ActionExecutedContext filterContext)
        {
            Debug.WriteLine("Inside OnActionExecuted");
            // disable caching for this request
            filterContext.HttpContext.Response.Cache.SetNoServerCaching();
            filterContext.HttpContext.Response.Cache.SetNoStore();
            bool isAuthorized = filterContext.HttpContext.User.Identity.IsAuthenticated;
           
            if (isAuthorized)
            {
                if (filterContext.RouteData.Values["controller"] != null &&
                    filterContext.RouteData.Values["action"] != null &&
                    filterContext.RouteData.Values["controller"].ToString().Trim().Length > 0 &&
                    filterContext.RouteData.Values["action"].ToString().Trim().Length > 0)
                {
                    string controller = Controller;
                   
                    if (string.IsNullOrEmpty(Controller))
                        controller = filterContext.RouteData.Values["controller"].ToString();
                   
                    string action = filterContext.RouteData.Values["action"].ToString();
                    if (action.ToLower() != "delete")
                    {
                        string username = filterContext.HttpContext.User.Identity.Name;

                        //here is the main part t o get the model that is passed to view
                        var model = filterContext.Controller.ViewData.Model;

                        try
                        {
                            //here we can assign any data to the model and pass it to view
                            //note this done here only and i don't need to write it all over in every action
                            ((MvcViewModelFilterAttribute.Models.CommonModel)model).SecurityClass = new Models.SecurityClass();

                            ((MvcViewModelFilterAttribute.Models.CommonModel)model).SecurityClass.UserName = username;
                        }
                        catch { }
                    }
               

                }

            }
        }

        public override void OnResultExecuting(ResultExecutingContext filterContext)
        {
            base.OnResultExecuting(filterContext);
        }

        public override void OnResultExecuted(ResultExecutedContext filterContext)
        {
            base.OnResultExecuted(filterContext);
        }
    }

To focus on our sample The most important part is .

 //here we can assign any data to the model and pass it to view
 //note this done here only and i don't need to write it all over in every action
   ((MvcViewModelFilterAttribute.Models.CommonModel)model).SecurityClass = new Models.SecurityClass();
    ((MvcViewModelFilterAttribute.Models.CommonModel)model).SecurityClass.UserName = username;

First we cast our model object to “CommonModel” Class which is a class all models inherits and we assign data to this base class.

 public class CommonModel
    {
        public string ErrorMessage
        {
            get;
            set;
        }

        public SecurityClass SecurityClass
        {
            get;
            set;
        }
    }

The Model Calss

 public class AccountModel : CommonModel
    {
        public string UserName
        {
            get;
            set;
        }

        public string Email
        {
            get;
            set;
        }

        public string Password
        {
            get;
            set;
        }
    }

Conclusion
We can make base class that all models inherits and we can fill this base class with data

How to Use

 [MvcViewModelFilterAttribute.Filters.ViewModelFilter(Controller = "Account")]
    public class AccountController : Controller
    {
        //
        // GET: /Account/
   
        public ActionResult Index()
        {
            return View();
        }
     
        public ActionResult Create()
        {
            return View(SiteViewModels.AccountViewModels.Instance.GetAccountObject(null));
        }

        public ActionResult Edit(int? userID)
        {
           
            return View(SiteViewModels.AccountViewModels.Instance. EditAction(5));
        }
        public ActionResult Details(int? userID)
        {
            return View(SiteViewModels.AccountViewModels.Instance. EditAction(5));
        }
    }

We can see that we can pass any given parameters like “Controller” to get data with or action name.
Project on the run
run the project and type “localhost:{port}/account/create/” or “localhost:{port}/account/edit/”
ViewModelFilterAttribute2

ViewModelFilterAttribute3

You can download the complete project from This link MvcViewModelFilterAttribute