October 26, 2014

Entity Framework: DBContext ExecuteSqlCommand and Stored-procedure does not return value

I have stored-procedures for CRUD and I expect all of my insert, update and delete stored-procedures would return an integer value after it is executed but the Entity Framework's DBContext ExecuteSqlCommand does not get the value that I use the command return in my stored-procedure.

Solution for now, I use an output parameter in my stored-procedures and I get the value that I return.
Ex:

My stored-procedure:

PROCEDURE [dbo].[TestReturnScalarValue]
    @par1 int,

    @ReturnValue INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @ReturnValue = @par1
END
My C# code:

public virtual int ExecuteStoredProcedureInt(string query, SqlParameter Output, params SqlParameter[] parameters)
        {
            DbContext.Database.ExecuteSqlCommand(query, parameters);
            return (int)Output.Value;
        }


To execute the above behavior in C#:

var par1= new SqlParameter("par1", 11);

var ReturnValue = new SqlParameter("ReturnValue", System.Data.SqlDbType.Int)
            {
                Direction = System.Data.ParameterDirection.Output
            };

var returnValue = ExecuteStoredProcedureInt(";EXEC [dbo].[TestReturnScalarValue] @ReturnValue, @par1", ReturnValue, par1); 

October 10, 2014

One Algorithm per day (C#): Merge two sorted Arrays and keep duplicate values


int[] Arr= {1, 5, 6, 8, 10, 15, 20, 23, 27, 29, 30, 32};
int[] Arr2 = {5};

int i = 0;
int j = 0;
int k = 0;
int m = Arr.Count() + Arr2.Count();
int[] Arr3 = new int[m];

while(k < m){
if(i < Arr.Count() && j < Arr2.Count()){
 if(Arr[i] < Arr2[j]){
  Arr3[k] = Arr[i];
  i++;
 }else if(Arr[i] < Arr2[j]){
  Arr3[k] = Arr2[j];
  j++;
 }else{
  Arr3[k] = Arr2[j];
  Arr3[k+1] = Arr[i];
  i++;
  j++;
  k++;
 }
}else if(i < Arr.Count()){
 Arr3[k] = Arr[i];
 i++;
}else{
 Arr3[k] = Arr2[j];
 j++;
}
k++;
}

foreach (int num in Arr3)
 Console.WriteLine(num);

One Algorithm per day (C#): Find the first repeating element in an array of integers

int[] Arr= {1, 2, 3, 7, 8, 9, 10, 11, 3, 7, 13};

int i = 0;
while(i < Arr.Count()){
 for(int j = i + 1; j < Arr.Count(); j++){
  if(Arr[i] == Arr[j]){
   Console.WriteLine(Arr[i]);
   i = Arr.Count();
   break;
  }
 }
 i++;
}

One Algorithm per day (C#): Create a new array which the started index Num = array.Count() - n

//Ex: int[10] array1;
// n = 2;
// => Num = 8;
// if n = 12;
// => Num = 8;

List<int> Nums = new List<int>();
List<int> result = new List<int>();

int num = 10;
int n = 12;

for(int i = 0; i < num; i++)
 Nums.Add(i+1);

int m = num;
int j = num - (n % num);

while( m > 0)
{
 result.Add(Nums[j]);
 m--;
 j++;
 j = j % num;
}

foreach(int i in result)
 Console.WriteLine(i);

One Algorithm per day (C#): What would be the last element of index n=3 in a List of integer numbers

List elements = new List();
int m = 10;
int n = 3;
for(int i = 0; i < m; i++){
 elements.Add(i + 1);
}

int k = 0;
int elementsLeft = m;
int j = 0;

while (elementsLeft > 1){
 k++;
 j++;
 j = j % elementsLeft;
 
 if(k % n == 0){
  elements.Remove(elements[j]);
  k = 0;
  elementsLeft--;
 }
}

Console.WriteLine(elements[0]);

October 08, 2014

Context Database SqlQuery executes Stored Procedure and get Return Values

The method you are trying to use only works for results of a query. It can't put the values of the output parameters into a new object for you automatically.
You have to create the parameters explicitly and read their values after the stored procedure has been run.
So if you have a stored prodcudure like this:
CREATE PROCEDURE dbo.insertemployee
(
    @iName varchar(500),
    @OEmployeeId int OUTPUT,  
    @OSalary Money OUTPUT
)
AS
BEGIN
    SELECT @OEmployeeId = 1337;
    SELECT @OSalary = 1000;
END
... you can execute it and get the results of the parameters like this:
using (var ctx = new Context())
{
    var nameParam = new SqlParameter("iName", "TestName");

    var employeeIdParam = new SqlParameter("OEmployeeId", SqlDbType.Int) 
    { 
        Direction = System.Data.ParameterDirection.Output 
    };

    var salaryParam = new SqlParameter("OSalary", SqlDbType.Money) 
    { 
        Direction = System.Data.ParameterDirection.Output 
    };

    ctx.Database.ExecuteSqlCommand(
        "insertemployee @iName, @OEmployeeId out, @OSalary out", 
        nameParam, employeeIdParam, salaryParam);

    var employeeId = (int)employeeIdParam.Value;
    var salary = (decimal)salaryParam.Value;
}

“this” in function parameter

This is the syntax for declaring extension methods, a new feature of C# 3.0.
An extension method is part code, part compiler "magic", where the compiler with the help of intellisense in Visual Studio make it appear that your extension method is actually available as an instance method on the object in question.
Let me give an example.
There's no method on the String class that is named GobbleGobble, so let's create an extension method:
public static class StringExtensions
{
    public static void GobbleGobble(this string s)
    {
        Console.Out.WriteLine("Gobble Gobble, " + s);
    }
}
The class name is just my naming convention, it isn't necessary to name it like that, but it has to be static, as do the method.

Reference: http://stackoverflow.com/questions/846766/use-of-this-keyword-in-formal-parameters-for-static-methods-in-c-sharp

October 05, 2014

Bootstrap 3 Navigation bar with Search


MS SQL: Create Stored-Procedure to get Data as pagination

CREATE PROCEDURE GetProductLimit 
 @PageNum INT,   -- Ex: 1, 2, 3, 4
 @PageSize INT   -- Ex: 10 per page
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    DECLARE
  @fromNum int,
  @toNum int

  SET @fromNum = (@PageSize * (@pageNum - 1)) + 1
  SET @toNum = @pageSize * @pageNum

 ;WITH Product AS
 (
  SELECT ID, ProductName, [Description], Price, Qty,
  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
  FROM dbo.Products 
 ) 
 SELECT ID, ProductName, [Description], Price, Qty  
 FROM Product 
 WHERE RowNumber BETWEEN @fromNum AND @toNum;
 END
GO

Grant Execute Permission on Stored Procedure in MSSQL

GRANT EXECUTE ON [dbo].[StoredProcedure] TO [Public]