C# - String convert/Parse to double/Currency where conatins "," as decimal separator

in C# when we try to convert a string to double say 123,45, Convert/Parse operator consider "," as a string format character and it will ignore "," from the string and return 12345.

double dbl = Double.TryParse("123,45")

output : 12345

Above piece of code works in German/France locale. Because decimal separator in GE/FR is comma(",") instead of "dot" (".")

To resolve this issue, it will be good to parse the string with culture like below

double dbConvertNumber= 0;

Double.TryParse("123,45", System.Globalization.NumberStyles.Any,
System.Globalization.NumberFormatInfo.CurrentInfo, dbConvertNumber)

Console.WriteLine (dbConvertNumber)

output : 0 in "us\UK" locale

             123,45 in "GE\FR" locale 

Power Script Connect SQL server throught OLEDB

Below is simple power shell command for SQL server DB connection 

cls

 $sqlinstance = "[ServerName]\[InstanceName]" 

 $auth = “Integrated Security=SSPI;”

 $connString = “Provider=SQLNCLI11.1;Auto Translate=False;” + “Data Source=$sqlinstance; ” + “Initial Catalog=myDB; ” + “$auth; “

 $connString

 $conn = New-Object System.Data.OleDb.OleDbConnection $connString

 $conn.Open()

    if($conn.State -eq "Open")

    {

       

        Write-Host "Test connection successful"

        $conn.Close()

    }

SQL Server extended Properties

Extended properties is one way to store documentation and information relate to the SQL Server object.

Why we need extended properties? when we can use extended property?

               suppose
                 - I want to perform DB shrink to a DB,
                 - when DB shrink is in progress, i have to block any other tasks to perform on my DB.
                 - or in other word, I should know if DBShrink is running on my DB before I do any operation in it

In such scenario, it will be good, create an extended property on my DB, say "DBStatus" with value "DBShrink" before start actual DB shrink on my DB, so that my application can read this property and stop perform any other operation on the DB until DB Shrink completes. Once I complete DBshrink, i could remove/change status from "DBShrink" to "string.Empty".

above example explain apply extended properties in DB level, but you could apply extended property in Table. Table Column, Procedure, Views etc

You could view extended properties, by checking your DB properties (DBname->Properties)

We could create Extended Properties in 2 ways

1. SQL command

        Use [DatabaseName]

exec sp_addextendedproperty @ name = N'DBStatus' ,@value = N'DBShrink'

To get extended properties use below query

select * from sys.extended_properties where NAME = 'DBStatus' 

2. SMO with C# 

   Refer 2 assemblies 

         1. Microsoft.SqlServer.Smo

         2. Microsoft.SqlServer.ConnectionInfo

   Code as Below

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public void AddCustomPropertiesTo_DB()
{
            string connstring = @"Server=localhost;user=sa;password=Password!;";
          System.Data.SqlClient.SqlConnection conn = new            
          System.Data.SqlClient.SqlConnection(connstring);
          ServerConnection servConn = new ServerConnection(conn);
          Server server = new Server(servConn);
            
            Database db = server.Databases["{MyDatabaseName}"];  //your database name
            db.ExtendedProperties.Refresh(); //always good to refresh before use. 

            ExtendedProperty extProp  = new ExtendedProperty() 
                                                                       { Parent = db, 
                                                                           Name = "DBStatus", 
                                                                            Value = "DBShrink" 
                                                                        };
           extProp.Create();

           //Update property value

            ExtendedProperty extProp1 = db.ExtendedProperties["DBStatus"];
            extProp1.Value = "DBShrink Completed";
            extProp1.Alter();

           //Drop an extended property

              ExtendedProperty extProp1 = db.ExtendedProperties["DBStatus"];
              extProp1.Drop();
}

Open XML quick Reference

I am here put some code pieces, which might help to play with OPENXML for excel application
Note, this is ONLY a reference,  you should implement more checking and logic to satisfy your requirement.

First step

try to write all openXML code inside using  block, like below

Stream xlsFileStream = new FileStream("myfile.xlsx", FileMode.Open);
using (SpreadsheetDocument myspreadSheetDocument = SpreadsheetDocument.Open(ExportTemplateStream, true))
{
              //all your code write here
}

WorksheetPart and WorkSheet

 var sheets = myspreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>() .Elements<Sheet>().Where(s => s.Name == "My Sheet Name");
if (sheets.Count() == 0)
            {
                return null;
            }

  var relationshipId = sheets.First().Id.Value;

WorksheetPart myWorksheetPart=myspreadSheetDocument.WorkbookPart.GetPartById(relationshipId) as WorksheetPart

Worksheet myWorkSheet= myWorksheetPart.Worksheet;

Create new Row


var sheetData = myWorkSheet.GetFirstChild<SheetData>();

//Insert new row
int rowindex = //put rowindex
Row row = new Row { RowIndex = rowindex) };
sheetData.Append(row);

//Insert after a row
Row newRow = new Row { RowIndex = rowindex };
Row currentRow = sheetData.Elements<Row>().Where(x => x.RowIndex.Value == rowindex).FirstOrDefault();
sheetData.InsertAfter(newRow, currentRow );

Get Row

//here code will get Row of a particular sheet
 var sheet = myspreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>() .Elements<Sheet>().Where(s => s.Name == "My Sheet Name").First();

WorkSheetData WrkSheetData=sheet.GetFirstChild();

Row row=WrkSheetData.Elements<Row>().Where(x => x.RowIndex.Value == RowIndex).FirstOrDefault();

Create new Cell

one thing you needs to understand here is create empty cell even your text does not have any value, otherwise you will end up writing cell data in wrong cell column. Dont fully trust cell.reference :)

//Ensure there is a Row exists before create a cell. Explained above how to create a row

var rows = myworksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);

var row = rows.First();

Cell myCell = new Cell() {CellReference = "A1"};
myCell.DataType =CellValues.String;
CellValue cellValue = new CellValue("your text");
myCell.Append(cellValue);
row.Append(myCell);

Get a Cell
//this will help to get a cell from a particular row, Column

var row = myWorksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

string cellReference = "A1";

Cell myCell = row.Elements<Cell>()
                    .Where(c => string.Compare(c.CellReference.Value,cellReference, true) == 0).First();

adding Style

//get a style from one cell and apply it in another cell

//get style cell
Cell styleCell = //getting an existing cell is explained above

Cell newCell = //create a new Cell explained ablve

newCell.StyleIndex = styleCell.StyleIndex;

Last Row Index

int rowIndex = myWorkSheet.GetFirstChild<SheetData>().Elements().Max(x => x.RowIndex.Value);

Merge Cells

//Assume you want to merge cells C1 to C5

MergeCells mergeCells = new MergeCells();
myWorkSheet.InsertAfter(mergeCells, wrksheet.Elements<SheetData>().First());

MergeCell cellsToMerge = new MergeCell() { Reference = new DocumentFormat.OpenXml.StringValue("C1:C5") };

mergeCells.Append(cellsToMerge);

Apply Formula

Cell formulacell = //Cell which needs to apply formula
string strformula = "A1+A2";
CellFormula formula = new CellFormula(strformula);
formulacell.Append(formula);

Add New Sheet

Sheets allsheets = myWorkBookPart.Workbook.GetFirstChild<Sheets>();
string relationshipID = myWorkBookPart.GetIdOfPart(WrkSheetPart);

            uint intsheetID = 1;

            if (allsheets.Elements<Sheet>().Count() > 0)
            {
                intsheetID = allsheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

Sheet newSheet = new Sheet()

            {

                Id = relationshipID,

                SheetId = intsheetID,

                Name = "My Sheet Name"

            };

            allsheets.Append(newSheet);

Remove Sheet

var sheets = myWorkPart.Workbook.GetFirstChild<Sheets>().Elements().Where(s => s.Name == "mySheetName");

if (sheets.Count() > 0)

            {

                Sheet sheet = sheets.First();

                sheet.Remove();

            }

Copy Row

// here row index and cell reference will be a villan. make sure after copy complete you are giving correct rowindex and cell reference to new row

//frst you need to know the source Row to be copied

Row refRow = //get this row from same sheet or any other sheet. Getting row is explained above

Row insertAfterRow //get this row. New row will be created after this row

Row newRow = (Row)refRow.CloneNode(true);

newRow.RowIndex = //provide proper rowindex

sheetData.InsertAfter(newRow, insertAfterRow);

//row index and cell reference should be change for all rows below the added row

// Below code will adjust Rowindex and cell reference

//Assume new Row is added at 5th Row

int rowIndex=5

IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);

            foreach (Row ExistingRow in rows)

            {

                uint newRowIndex = System.Convert.ToUInt32(ExistingRow.RowIndex.Value + 1);

                foreach (Cell currentcell in ExistingRow.Elements<Cell>())

                {

                    string cellReference = currentcell.CellReference.Value;

                    currentcell.CellReference = new StringValue(cellReference.Replace(ExistingRow.RowIndex.Value.ToString(), newRowIndex.ToString()));

                }

                ExistingRow.RowIndex = new UInt32Value(newRowIndex);

            }

HTML Drag n Drop text or Image

HTML5 supports drag and drop, that allows user to move a content from one side to another. Here I demonstrate drag and drop a text from one <div> to another <div>. 

please download full script from Download Source Code
below javascript code will do drag drop

<script>

function allowDrop(ev) {

ev.preventDefault();

}

function drag(ev, ctrl) {

ev.dataTransfer.setData("text", ev.target.id);

}

function drop(ev) {

ev.preventDefault();

var data = ev.dataTransfer.getData("text");

ev.target.appendChild(document.getElementById(data));

}

</script>

ev.PreventDefault() -   will prevent fire default event

ev.dataTransfer.setData("text", ev.target.id) - will set data type and value of dragged data

Below is HTML tag

<h2>Drag and Drop Text</h2>

<div id="div1" ondrop="drop(event)" ondragover="allowDrop(event)">

<div id="drag001" draggable="true" ondragstart="drag(event, this)" >Drag Item 1</div>

<div id="drag002" draggable="true" ondragstart="drag(event, this)" >Drag Item 2</div>

<div id="drag003" draggable="true" ondragstart="drag(event, this)" >Drag Item 3</div>

</div>

<div id="div2" ondrop="drop(event)" ondragover="allowDrop(event)"></div>

ondrop - event fires when release mouse left button
ondragover - event fires when mouse move over the control/HTML tag

draggable="true" - make the <div> element draggable
ondragstart="drag(event, this)" - fires when user start dragging

finally add below style to <div> tag so that it will show as box

<style>

#div1, #div2 {

float: left;

width: 100px;

height: 75px;

margin: 10px;

padding: 10px;

border: 1px solid black;

}

</style>

HTML internal storage with IndexedDB

One of the challenge in modern web application is to store data in client side, it can help to avoid un-necessary service call to server. IndexedDB is one of the mechanism which will store data in client browser (hence it is crucial that browser should support it). Below are some of scenario for keeping data local

1. Text Translation - load all translated text in browser and avoid service call for text translation
2. work offline - this will help to reduce round trip. Load all data at the time of application load (ex: Types/Categories etc) so that we dont needs to call service to fetch again.
3. Work Offline - store all data in indexedDB, and continue process, once everything is completed, single click can save data in your SQL/Oracle DB.

Fact about IndexedDB

1. It is not a RDBMS
2. Data are stored in the form of key/value
3. SQL query will not work
4. IndexedDB is not a replacement of your DB
5. User can group a set of key/value and Keypath will represent each key/value pair

lets start practical :)

below is HTML will create IndexedDB and store (try this in your chrome)
Full Source code available here

<html>

<head>

<meta http-equiv = "Content-Type" content = "text/html; charset = utf-8" />

<script type = "text/javascript">

//prefixes of implementation that we want to test

window.indexedDB = window.indexedDB || window.mozIndexedDB ||

window.webkitIndexedDB || window.msIndexedDB;

//prefixes of window.IDB objects

window.IDBTransaction = window.IDBTransaction ||

window.webkitIDBTransaction || window.msIDBTransaction;

window.IDBKeyRange = window.IDBKeyRange || window.webkitIDBKeyRange ||

window.msIDBKeyRange

if (!window.indexedDB) {

window.alert("Your browser doesn't support a stable version of IndexedDB.")

}

var db;

var request = window.indexedDB.open("empDB", 1);

request.onerror = function(event) {

console.log("error: ");

};

request.onsuccess = function(event) {

db = request.result;

console.log("db Created success: "+ db);

};

request.onupgradeneeded = function(event) {

var db = event.target.result;

var objectStore = db.createObjectStore("empstore", {keyPath: "empid"});

}

</script>

</head>

<body> </body>

</html>

save as HTML file and run.
Once page loaded, you will be seeing blank screen :), lets see what is happening indexedDB, press F12 and select application tab, your IndexedDB, store are ceated now :)

lets go through the code. Below code creates new Indexed DB empDB (line no: 17)

var request = window.indexedDB.open("empDB", 1);

below line will be creating an object store named empStore (line no 27). I set keypath as empID. KeyPath (empID) is one of property in key/value collection, which will represent the entire group (like PK in RDBMS)

var objectStore = db.createObjectStore("empstore", {keyPath: "empid"});

Now I am going to add/Find/Delete data from my IndexedDB

below, i am adding HTML tag to add/Find/Delete. I am adding 3 textbox which will have empID, Name and Age.

<body>

<label>Emp ID</label><input type="text" id="txtEmpID"><br/>

<label>Emp Name</label><input type="text" id="txtEmpName"><br/>

<label>Emp Age</label><input type="text" id="txtAge"><br/><br/>

<button onclick = "add()">Add data </button>

<button onclick = "read()">Find </button>

<button onclick = "remove()">Delete data </button>

</body>

Add to IndexedDB

function add() {

var _empID = txtEmpID.value;

var _empName=txtEmpName.value;

var _empAge=txtAge.value;

var request = db.transaction(["empstore"], "readwrite")

.objectStore("empstore")

.add({ empid: _empID.toString(), name: _empName.toString(), age: _empAge.toString()});

request.onsuccess = function(event) {

alert("Employee has been added to your database.");

};

request.onerror = function(event) {

alert("Unable to add data\r\nEmployee is aready exist in your database! ");

}

}

now refresh your indexedDB you could find the entry in key/value pair form like below

Find

function read() {

var _empID = txtEmpID.value;

var transaction = db.transaction(["empstore"]);

var objectStore = transaction.objectStore("empstore");

var request = objectStore.get(_empID.toString());

request.onerror = function(event) {

alert("Unable to retrieve daa from database!");

};

request.onsuccess = function(event) {

// Do something with the request.result!

if(request.result) {

alert("Name: " + request.result.name + ", Age: " + request.result.age );

} else {

alert("Employee couldn't be found in your database!");

}

};

}

Delete

function remove() {

var _empID = txtEmpID.value;

var request = db.transaction(["empstore"], "readwrite")

.objectStore("empstore")

.delete(_empID.toString());

request.onsuccess = function(event) {

alert("EMployee has been removed from your database.");

};

request.onerror = function(event) {

alert("Employee not found ");

}

}

ASP.Net MVC file downloader to download file at user machine

Here is a simple source what will download file in user machine on a Asp.Net web application.
This can be implement in Angular application also.

Step 1 - new action in asp.net MVC controller

Byte[] dashboardfileBytes = File.ReadAllBytes(Full File LOCATION);

response = new HttpResponseMessage(HttpStatusCode.OK);

response.Content = new ByteArrayContent(updatefilebytes);

response.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");

response.Content.Headers.ContentDisposition.FileName = repsourceFileInfo[0].Name;

response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/xlsm");

Step 2 - download from client side

download is very simple, you have a add a <a> tag and in href property you needs to send Asp.Net MVC url for the action you created above

ex:
<a hreff="mycontroller\FileDownloadAction" >download File </a>

the same logic you can implement in angular, instead of calling asp.net MVC controoler\action url, you can use WebAPI url.