Reading XLSX format file from C#

The best guide for reading excel file without Excel instance exist here.

Pay attentions! In the final example in the loop ForEach missed validation of  not string values and NULL value cells, threfore change it to:

foreach (XElement cell in cells)

{

    if (cell.IsEmpty)

    {

        continue;

    }

    string cellPosition = cell.Attribute("r").Value;

    int index = IndexOfNumber(cellPosition);

    string column = cellPosition.Substring(0, index);

    int row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));

    if (cell.HasElements)

    {

        if (cell.Attribute("t") != null && cell.Attribute("t").Value == "s")

        {

            // Shared value

            int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value);

            parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));

        }

        else

        {

            string value = cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value;

            parsedCells.Add(new Cell(column, row, value));

        }

    }

    else

    {

        parsedCells.Add(new Cell(column, row, ""));

    }

}

Error in SecurityMode = None in WsHttpBinding

Define next attributes in the binding:

<bindings>

      <wsHttpBinding>

        <binding name="wsHttpBindingConfig">

          <security mode ="None"/>

          <reliableSession enabled="true" />

        binding>

      wsHttpBinding>

    bindings>

WCF error messages - the guide.

Whenever web developers hear the term session, we think of Http Session which is used to store data between multiple HTTP requests....to full article click here!

Extension for expression (List == null || List.Count > 0)

This one and more extensions exist here.

Could not load file or assembly 'ASSEMBLY_NAME' or one of its dependencies. An attempt was made to load a program with an incorrect format.

The error occurred as transfer from 32-bit to 64-bit platforms. A solution is very easy:

1. Go to "ASSEMBLY_NAME" project in solution
2. Open project properties
3. Open "Build" tab
4. Change "Platform target" to "Any CPU"
5. Compile solutions.

Enjoy!

How to prevent duplicates in 2 columns in one table

We know how to define column as unique, but what to do where you need to prevent duplicate in 2 columns?
This is a state:

CREATE TABLE #tmp
(
Id INT,
Wine VARCHAR(50),
Country VARCHAR(50)
)

INSERT INTO #tmp VALUES 
(1,'Merlot','France')
INSERT INTO #tmp VALUES
(2,'Merlot','Argentina')
INSERT INTO #tmp VALUES
(3,'Malbec','Argentina')

SELECT * FROM #tmp
DROP TABLE #tmp

A result is:

Id |  Wine    |  Country
----------- ------------------
1 |  Merlot   |  France
2 |  Merlot   |  Argentina
3 |  Malbec |  Argentina

A target to prevent:

Id |  Wine    |  Country
----------- ------------------

1 |  Merlot   |  France
2 |  Merlot   |  Argentina
3 |  Malbec |  Argentina

4 |  Merlot   |  France
5 |  Caberne   |  France

That means table allow duplicate Wines and duplicate Countries, but must prevent double rows.
A solution is to add next row after table creation:

ALTER TABLE  #tmp      ADD UNIQUE(Wine, Country)

Enjoy

Generic Type Convert Function

It's simple method can be use as extension method for conversion types :

public static TK ConvertTo<TK>(this object value,bool isThrowException = false, 
                   TK defaultValue = default(TK))
{
  try
  {
    return (TK)Convert.ChangeType(value, typeof(TK));
  }
  catch (InvalidCastException ex)
  {
     if (isThrowException)
      {
        throw ex;
     }
  return defaultValue;
}

Using example:

string value = "15";
//Method will convert string to int. 
//In case val is not numeric will throw exception.
int result = value.ConvertTo();
//In case val is not numeric will return type default value.
int result1 = value.ConvertTo(false);
//In case val is not numeric will return your defined value (5).
int result2 = value.ConvertTo(false,5); 

Enjoy!

Entity Framework, web.config and error "The specified named connection is either not found in the configuration..."

Hi my friend,

If  you come to here from generic search, then you already know what is a real nightmare. Every new feature from Microsoft can include surprised items and will take hours (sometimes days) for understand how it work correct. Anyway...

For using Entity Framework as DLL class-library in win/console application you should work by next steps:

1. Create YOUR_NAME.edmx.
2. Open designer.
3. Click on background.
4. In property tab change property "Metadata artifact Processing" to "Copy to Output Directory".
5. Save project (Connection string will be changed from "res//*/" to ".\" ).
6. Copy connection string to main app.config as is.
7. Go to EF project properties. Choose "Build event" and in Post-Build event command line add next rows:

copy  YOUR_NAME.csdl $(SolutionDir)START_PROJECT\bin\Debug\
copy  YOUR_NAME.ssdl $(SolutionDir)START_PROJECT\bin\Debug\
copy  YOUR_NAME.msl $(SolutionDir)START_PROJECT\bin\Debug\

8. Remove app.config of EF project.

But the nightmare does not live in win application, because she's place in Web Application!

Next steps will help you to back out from hell:
Do it 1-6 steps as in win application case.
7. In web.config change  ".\" to "~\App_Data" in c/m/s files
8. Go to EF project properties. Choose "Build event" and in Post-Build event command line add next rows:

copy  YOUR_NAME.csdl $(SolutionDir)START_WEB_PROJECT\App_Data\
copy  YOUR_NAME.ssdl $(SolutionDir)START_WEB_PROJECT\App_Data\
copy  YOUR_NAME.msl $(SolutionDir)START_WEB_PROJECT\App_Data\

9. Remove app.config of EF project.
10. Cross fingers, run it and pray!

Enjoy!

Full solution to: The server committed a protocol violation. Section=ResponseStatusLine

In most answers to this error you will get same answer:
<system.net>
    <settings>      
      <httpWebRequest useUnsafeHeaderParsing="true" />
    </settings>
</system.net>

But not always a problem will be resolved. A full solutions to error present here. Just add next row to request variable:

   request.KeepAlive = false;

OR

   ((HttpWebRequest)request).KeepAlive = false; (Where request type is WebRequest)

LINQ2SQL Connection Strings with class library projects

1. Set connection string in Web/App.config in main external project.
2. Open up your DBML and click the designer service. Expand the Connection property, delete Connection String and set "Application Settings" to False.
3. Open designer file and add new construtor as:

  public DataClassesDataContext()
            : base(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString)
        {

            OnCreated();

        }

4. Enjoy!

Full article and inspiration exist here.

Check is IP in range

You can find countless examples how to check is IP in range. I would like to offer my way for solution with LINQ:

using System.Collections.Generic;
using System.Net;
using System.Linq;

 public class IPRange
    {
        public byte[] StartAddressBytes { get; private set; }
        public byte[] EndAddressBytes { get; private set; }

        public IPRange(IPAddress startAddress, IPAddress endAddress)
        {
            StartAddressBytes = startAddress.GetAddressBytes();
            EndAddressBytes = endAddress.GetAddressBytes();
        }
        public IPRange(string strartIp, string endIp)
        {
            IPAddress startAddress, endAddress;
            if (IPAddress.TryParse(strartIp, out startAddress) 
                && IPAddress.TryParse(endIp, out endAddress))
            {
                StartAddressBytes = startAddress.GetAddressBytes();
                EndAddressBytes = endAddress.GetAddressBytes();
            }
        }
    }
//--------------------------
public class IPRangeUtil
    {
        public static bool IsInRange(List<IPRange> ipRange , string clientIp)
        {
            IPAddress clientAddress;
            if (!IPAddress.TryParse(clientIp, out clientAddress))
            {
                return false;
            }
            return IsInRange(ipRange, clientAddress);
        }
        public static bool IsInRange(List<IPRange> ipRange, IPAddress clientAddress)
        {
            int clientIpSum = clientAddress.GetAddressBytes().Sum(item => item);
            return ipRange.Where(range =>
                clientIpSum >= range.StartAddressBytes.Sum(item => item)
                && clientIpSum <= range.EndAddressBytes.Sum(item => item)).Any() ;
        }
    }


A using is very easy:
 List ranges = new List
                    {
                        //You can replace "xxx.xxx.xxx.xxx" to IPAddress typed variable 
                          new IPRange("192.168.10.68", "192.168.10.255"),
                          new IPRange("192.168.0.68h", "192.168.10.68")
                    };


bool isInRange = IPRangeUtil.IsInRange(ranges, "192.168.10.70");




Enjoy!

What is Authentication and Authorization?

Simple video which explain who is who:

LINQ and #Temporary tables in Stored Procedure do not have return values

I have Stored Procedure which include #tmp table. I tried to drag it to DBML but every time i got message "bla-bla-bla ... do not have return values...".After countless attempts was found solution! (Thanks to Marc Gravell )
Replace the #tmp in variable, a meaning:

CREATE TABLE #tmp (Item in)
Replace it to:
DECLARE @tmp TABLE (Item int)

A using in query is:
SELECT yt.*, t.Item
FROM YUOR_TABLE yt, @tmp t


Enjoy!

How to encrypt and decrypt password in SQL Server

Here you can find nice and full solution to encrypt and decrypt password for use in SQL Server.

Using:

declare @value VARCHAR(8000)
--Encrypt
set @value =  dbo.fnEncDecRc4('Magic Word','password')
--Decrypt
select dbo.fnEncDecRc4('Magic Word',@value)

Enjoy!

Log Viewer Pro

Many years I’m use the best file manager Total Commander (Windows commander in the past). In the home site was found link to the site http://www.totalcmd.net which include additional plugins and utilities. But I would  present only one very usability utility – Log Viewer Pro.  Current application dedicated to viewing logs and available to using in every text file . Every level of Log may be define in different color, a program is free (for home users) and not required install.

Screenshot:

Main features of viewer:
Fast scrolling, eats low memory.
Supports any file size (4 Gb and larger).
Multitabbed interface.
Auto-reload file, Follow tail mode.
Allows to highlight some lines (e.g. "errors", "warnings").
Allows to view encodings: ANSI, OEM, Unicode LE, Unicode BE etc.
File search (both forward and backward).

Download here.

Size: 650 KB
Author: Alexey Torgashin

Snippet editor

In my work I'm often use snippets, but by old habit I created every time new snippet manual. Today found nice solutions to my  suffering -  Snippet Editor by BillMcC. Snippet editor ready to work with all version of Visual Studio, support C# and VB snippet, easy interface and big help to programmer.
Link to download.

Generate random string

Current code is generate string in defined length and include chars, numbers and symbols.

public static string GetRandomString(int size)
{
    StringBuilder builder = new StringBuilder();
    Random random = new Random();
    int[] symbol = new[] { 33, 36, 38, 64, 94 };
    int[] digits = new[] { 48, 49, 50, 51, 52, 53, 54, 55, 56, 57 };
    int symbolsAmount = size / 3;
    int digitAmount = size / 3;
    for(int i = 0; i < size; i++)
    {

        int switcher = random.Next(3);
        char ch;
        if(switcher == 0 && symbolsAmount > 0)
        {
            int id = random.Next(symbol.Length);
            ch = Convert.ToChar(symbol[id]);
            symbolsAmount--;
        }
        else if(switcher == 1 && digitAmount > 0)
        {
            int id = random.Next(digits.Length);
            ch = Convert.ToChar(digits[id]);
            digitAmount--;
        }
        else
        {
            bool isLower = random.Next(2) == 0 ? false : true;
            ch = Convert.ToChar(
                 Convert.ToInt32(
                 Math.Floor(26 * random.NextDouble() + ((isLower) ? 65 : 97))
                     )
                 );

        }
        builder.Append(ch);
    }

    return builder.ToString();
}

Range Operator in Linq

Simple example to big item. Click here to read article.

Combinatorics Utilities

Beautiful solution to combinatorics algorithm. Click here to read article.

VS 2008 SP1 deletes .dbml designer file – a solution

Also me was surprised after installation Visual Studio 2008 SP1. every little change in dbml hierarchy give raise to deleting designer file. Short googling return elegant solution:
In partial class move all using statements after namespace


Original code:

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace Test
{
partial class DataClassesDataContext
{
//...
}
}

New Code:

namespace Test
{
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

partial class DataClassesDataContext
{
//...
}
}

Right click on the .dbml  file and run “Run Custom Tool”.

Enjoy!