Generate SQL insert statements from Excel / Csv file online

Lets say you get a Microsoft Excel file containing the following information:

image

 

If you want to import these record in a SQL table, just save the excel file as CSV.

Then upload it to http://www.convertcsvtomysql.com/

 

 

image

 

This will generate the following SQL script:

CREATE TABLE IF NOT EXISTS ‘product’
(
‘name’ VARCHAR(255) NOT NULL,
‘description’ VARCHAR(255) NOT NULL
)

INSERT INTO ‘product’ VALUES
(‘Car’, ‘This is the car description’),
(‘Bike’, ‘This is the bike description’),
(‘Bus’, ‘This is the bus description’);

 

Remove the "create table" peace if your table already exists.

Kendo UI – jQuery and dataSource–transport–contentType

The way a kendo datasource retrieves data can be configured via the transport object.

This object contains configuration objects for specifying the way CRUD operations are executed.

Each of these configuration objects contain the properties

contentType (Type of the HTTP request)

dataType (Type of the HTTP response request)

 

The way you configure these properties defines the way how the C# REST Service code must be written. If you use the default contentType (application/x-www-form-urlencoded; charset=UTF-8). the data is sent inside the forms collection with the name "models" of the request, the name of this property is set in the parameterMap function:

var dataSource = new kendo.data.DataSource({
transport: {
update: {
// The default HTTP request (send) type.
contentType: "application/x-www-form-urlencoded; charset=UTF-8",
// The expected HTTP response (receive) type.
dataType: "json"    
}
},
parameterMap: function (options, operation) {
if (operation !== "read" && options.models) {
return { models: JSON.stringify(options.models) };
}
}
});

C# code to handle this update request:

public List<MyDto> Post()
{
    string json = _request["models"];
var records = new List<MyDto>();
if (!string.IsNullOrWhiteSpace(json))
{
records = JsonConvert.DeserializeObject<List<MyDto>>(json);
// Update data in database …
}
return records;
}

MyDto is just a POCO class.

 

If you want to sent the data as a parameter of the Post method, you should use "contentType:"application/json"":

var dataSource = new kendo.data.DataSource({
transport: {
update: {
// The default HTTP request (send) type.
contentType: "application/json",
// The expected HTTP response (receive) type.
dataType: "json"    
}
},
parameterMap: function (options, operation) {
if (operation !== "read" && options.models) {
return { models: JSON.stringify(options.models) };
}
}
});

C# code to handle this update request:

public List<MyDto> Post(List<MyDto> records)
{
// Update records in database ...
return records;
}

SSIS 2012 not showing correct Available External Columns in Xml Source Task.

 

The “ORDER” type in the following XML document will not be shown in the available external columns dialog in SSIS 2012.

<?xml version=”1.0″ encoding=”utf-8″?>
<ORDER>
  <ID>1</ID>
  <ORDERLINE>
    <ID>1</ID>
      <PRODUCTNAME>Test1</PRODUCTNAME>
  </ORDERLINE>
  <ORDERLINE>
    <ID>2</ID>
      <PRODUCTNAME>Test2</PRODUCTNAME>
  </ORDERLINE>
</ORDER>

 

Result

image

 

If you want the ORDER type to show up, add a root element to the xml document:

 

<?xml version=”1.0″ encoding=”utf-8″?>
<ROOT>
    <ORDER>
      <ID>1</ID>
      <ORDERLINE>
        <ID>1</ID>
          <PRODUCTNAME>Test1</PRODUCTNAME>
      </ORDERLINE>
      <ORDERLINE>
        <ID>2</ID>
          <PRODUCTNAME>Test2</PRODUCTNAME>
      </ORDERLINE>
    </ORDER>
</ROOT>

 

Now you will see both ORDER and ORDERLINE show up:

image

 

To add a root node to an XML document in SSIS you can use the following script code:

public void Main()
{
try
{
string selectedXmlFile = Dts.Variables[@"User::SelectedXmlFile"].Value.ToString();
string AlteredXmlFile = (String.Format("{0}x", selectedXmlFile));
Dts.Variables[@"User::AlteredXmlFile"].Value = selectedXmlFile;
XmlDocument oldDoc = new XmlDocument();
oldDoc.Load(selectedXmlFile);
XmlDocument newDoc = new XmlDocument();
XmlElement root = newDoc.CreateElement("ROOT");
newDoc.InsertAfter(root, null);
XmlNode content = newDoc.ImportNode(oldDoc.ChildNodes[2], true);
root.AppendChild(content);
newDoc.Save(AlteredXmlFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Variables[@"User::LogMessage"].Value = ex.ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

How to use a password field in a Kendo grid

The demo at http://demos.kendoui.com/web/grid/editing-custom.html shows how to create a custom editor function. We can use this function to show a password field as editor in a Kendo grid.

function passwordEditor(container, options)
{
$('<input type="password" required data-bind="value:' + options.field + '"/>').appendTo(container);
};

In the column that should be used as password, use:

$("#grid").kendoGrid({
dataSource: dataSource,
pageable: true,
height: 430,
toolbar: ["create"],
columns: [
{ field: "ProductName", title: "Product Name" },
{ field: "Category", title: "Category", width: "160px", editor: categoryDropDownEditor, template: "#=Category.CategoryName#" },
{ field: "UnitPrice", title: "Unit Price", format: "{0:c}", width: "120px" },
{ field: "UserPassword", title: "Password", editor: passwordEditor, template: "…" },
{ command: "destroy", title: " ", width: "90px" }],
editable: true
});

 

The template: "…" ensure no data is shown to the user, when the password is changed.

 

An example would be:

image

How to add a CSS class to a kendo grid column

If you want to add a class to a kendo grid column, use the columns.attributes:

 

<div id="grid"></div>
<script>
$("#grid").kendoGrid({
columns: [ {
field: "name",
title: "Name",
attributes: {
"class": "table-cell",
style: "text-align: right; font-size: 14px"
}
} ],
dataSource: [ { name: "Jane Doe" }, { name: "John Doe" }]
});
</script>

 

http://docs.kendoui.com/api/web/grid

How to create deployment zip packages for any Microsoft Visual Studio 2012 project type, simply by editing the *.csproj files.

If you want to create deployment zip packages for any Microsoft Visual Studio project type (like, Web Site, Web Service, Windows Service, Console Application etc. then just follow the steps below:

 

In this example I will be zipping all content files and *.dll files found in the "bin" folder of an ASP .NET MVC 4 Web Application, but the same will work for other project types. This example will be using the MSBuild Community Zip Task and at this point will work for  .NET projects <= 4.5.

 

Create a new ASP .NET MVC 4 Web Application

In Microsoft Visual Studio 2012, choose:

File > New > Project…

image

Choose Internet Application

image

 

Add the nuget package"MSBuild Community Tasks"

Click click the solution in the Solution Explorer and choose "Manage NuGet Packages for Solution…"

image

Choose MSBuildTasks

image

 

 

Edit MSBuild.Community.Tasks.targets

Edit the MSBuild.Community.Tasks.targets file, so it will use the MSBuild.Community.Tasks.dll found in the .build folder created by the NuGet package.

image

 

Change:

<MSBuildCommunityTasksLib>$(MSBuildCommunityTasksPath)\MSBuild.Community.Tasks.dll</MSBuildCommunityTasksLib>

To

<MSBuildCommunityTasksLib>MSBuild.Community.Tasks.dll</MSBuildCommunityTasksLib>

Result

image

 

 

Edit the *.csproj file

Unload project (right click project > Unload Project)

image

 

Edit *.csproj file

image

Scroll to the bottom of the file and

change:

<!-- To modify your build process, add your task inside one of the targets below and uncomment it. 
Other similar extension points exist, see Microsoft.Common.targets.
<Target Name="BeforeBuild">
</Target>
<Target Name="AfterBuild">
</Target> -->

 

To:

<!-- Start Zip target --> <ItemGroup> <!-- Add all *.dll files found in the root of the "bin" folder as link files. - Use Files="@(Content);@(Link)" in the zip target the include both content files as build output in the zip content.
- Using the "@(Content);@(Link)" notation, will merge ItemGroup Content and ItemGroup Link into one property.
- The ".\" before the "bin" folder name in the Include, results in a zip package containing the bin folder. - If you do not want to include the "bin" folder itself but only the files, use "bin\*.dll". - If you want to include subfolders use ".\bin\**\*.dll" --> <Link Include=".\bin\*.dll" />
<Link Include=".\bin\*.exe" />
<Link Include=".\bin\*.exe.config" />
</ItemGroup> <!-- A relative path will start from the *.csproj file location, so to get to the MSBuild.Community.Tasks.Targets file, we must add "..\.build\". --> <Import Project="..\.build\MSBuild.Community.Tasks.Targets" /> <!-- The afterbuild target will only be executed, when the project is build in "Release" mode. --> <Target Name="AfterBuild" Condition="'$(Configuration)' == 'Release'"> <PropertyGroup> <ReleasePath>bin</ReleasePath> </PropertyGroup> <Zip Files="@(Content);@(Link)" WorkingDirectory="$(ReleasePath)" ZipFileName="output\$(AssemblyName).zip" ZipLevel="9" /> </Target> <!-- End Zip target -->

 

Reload project

image

 

Build project project in "Release" configuration

image

 

An output folder should be created containing the MVC1Application.zip.

image

 

Zip file:

image

Extracted

image

How to solve: Unable to open Transact-SQL file in custom editor in Microsoft Visual Studio 2012

In my case the error "Unable to open Transact-SQL file in custom editor", was caused by the fact, that I installed BIDS voor Microsoft Visual Studio 2012, but not the SSDT tools.

After installing the SSDT tools, the error was resolved: http://msdn.microsoft.com/en-us/data/hh297027. This Microsoft download sites, works best on Internet Explorer. I had some problems with Chrome.

 

 

clip_image002

How to create a Zip file with the .NET 4.5 System.IO.Compression classes in C#

If you want to create a zip file in C# with the OOB BCL, in C# you can use the following code:

 

public void CreateZipFile()
{
string zipPath = @"C:\Test.zip";
string entryName = "Readme.txt";
string content = "Hello world!";
using (var zipToOpen = new System.IO.FileStream(zipPath, System.IO.FileMode.CreateNew))
{
using (var archive = new System.IO.Compression.ZipArchive(zipToOpen, System.IO.Compression.ZipArchiveMode.Create))
{
System.IO.Compression.ZipArchiveEntry readmeEntry = archive.CreateEntry(entryName);
using (var writer = new System.IO.StreamWriter(readmeEntry.Open()))
{
writer.Write(content);
}
}
}
}