How to Bulk Insert in SQL using User Defined Table Types

Here is a quick way to Bulk Insert data into SQL with the help of User Defined Table Types

Step 1: Create a User Defined Table Types. 

CREATE TYPE UDT_MyTable AS TABLE 
(     
        Name varchar(100),
        Age Int 
 )

Step 2: Create a Stored procedure that accepts the UDT_MyTable as input parameter

CREATE PROC [dbo].[Usp_VUpdate]
       @UserDefineTable UDT_MyTable READONLY
AS
BEGIN

  INSERT INTO MyTargetTable
  (SELECT * FROM UDT_MyTable)
    
END;

Note: Here I am assuming that MyTargetTable is having exactly similar columns to the UDT_MyTable.

 

Happy Coding ...

How to create an Excel like interface using Handsontable plugin - jquery

I had a requirement to create a web application that should have an excel like user input to accept data and perform run-time calculations like Microsoft Excel Formulas handle the calculations.

I came across a jquery plugin that fits my requirement - HandsOnTable. The plugin is seamless and can handle large data, and works perfectly with the calculations that are based on Excel like formulas. They also have an excellent documentation

Lets go through the Quick start steps to use this plugin:

1. Install or reference the js/css for handsontable

<script src="//handsontable.com/dist/handsontable.full.js"></script>
<link rel="stylesheet" media="screen" href="//handsontable.com/dist/handsontable.full.css">

2. Create a div that acts as a placeholder for the control.

<div id="example"></div>

3. Add the following script to set the data and initialize the handsontable plugin with options.

var data = [
  ["", "Ford", "Volvo", "Toyota", "Honda"],
  ["2016", 10, 11, 12, 13],
  ["2017", 20, 11, 14, 13],
  ["2018", 30, 15, 12, 13]
];

var container = document.getElementById('example');
var hot = new Handsontable(container, {
  data: data,
  rowHeaders: true,
  colHeaders: true
});

4. Run the application to see the excel like UI.

There are many advance features available in the plugin... do check it out.

 

Happy Coding ... 

How to convert a List of Objects to DataTable in .NET

The following method will convert any List object to DataTable with simple properties.

In C#

public static DataTable getListDataTable<T>(List<T> items)
{

	DataTable dataTable = new DataTable(typeof(T).Name);

	//Get all the properties
	PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

	//Filter out the unwanted columns
	PropertyInfo[] PropsFiltered = Props.Where(x => x.Name != "ExtensionData").ToArray();

	foreach (PropertyInfo prop in PropsFiltered) {
		//Setting column names as Property names
		dataTable.Columns.Add(prop.Name);
	}
	foreach (T item in items) {
		dynamic values = new object[PropsFiltered.Length];
		for (int i = 0; i <= PropsFiltered.Length - 1; i++) {
			//inserting property values to datatable rows
			values(i) = PropsFiltered(i).GetValue(item, null);
		}
		dataTable.Rows.Add(values);
	}
	//put a breakpoint here and check datatable

	return dataTable;
}

 

In VB.NET

Public Shared Function getListDataTable(Of T)(ByVal items As List(Of T)) As DataTable

    Dim dataTable As DataTable = New DataTable(GetType(T).Name)

    'Get all the properties
    Dim Props As PropertyInfo() = GetType(T).GetProperties(BindingFlags.[Public] Or BindingFlags.Instance)

    'Filter out the unwanted columns
    Dim PropsFiltered As PropertyInfo() = Props.Where(Function(x) x.Name <> "ExtensionData").ToArray()

    For Each prop As PropertyInfo In PropsFiltered
        'Setting column names as Property names
        dataTable.Columns.Add(prop.Name)
    Next
    For Each item As T In items
        Dim values = New Object(PropsFiltered.Length - 1) {}
        For i As Integer = 0 To PropsFiltered.Length - 1
            'inserting property values to datatable rows
            values(i) = PropsFiltered(i).GetValue(item, Nothing)
        Next
        dataTable.Rows.Add(values)
    Next
    'put a breakpoint here and check datatable

    Return dataTable
End Function

 

 

In this example I am filtering out unwanted properties like "ExtensionData"

 

Happy Coding ....

How to detect Asynchronous postback events in ASP.NET using PageRequestManager

This javascript should be added below the scriptmanager control in ASP.NET page to detect the postback events.

var prm = Sys.WebForms.PageRequestManager.getInstance();

if (prm != null) {

    //For Begin Request
    prm.add_beginRequest(function (sender, e) {
       
    });
}

//For End Request
prm.add_endRequest(function (sender, e) {
    if (sender._postBackSettings.panelsToUpdate != null) {
    }
});

The add_beginRequest function will trigger once the ajax request is started and add_endRequest will be triggered once the ajax request is completed.

 

Happy Coding ...

How to add a Rich Text Editor to ASP.NET page (TinyMCE example)

Let us quickly add a Editor to the ASP.NET page. The editor that is being used here is TinyMCE.

1. Go to url (https://www.tinymce.com) and download the source code. 

2. Unzip the contents of the file to the ASP.NET solution.

3. Add the following script tag to the ASP.NET page where you need the Tiny MCE editor to be displayed.

<script type="text/javascript" src="tinymce/js/tinymce/tinymce.js"></script>
    <script type="text/javascript">
        tinymce.init({
            selector: 'textarea',
            height: 500,
            plugins: [
        'advlist autolink lists link image charmap print preview anchor',
        'searchreplace visualblocks code fullscreen',
        'insertdatetime media table contextmenu paste code'
      ],
            toolbar: 'insertfile undo redo | styleselect | bold italic | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image',
            content_css: [
        '//fast.fonts.net/cssapi/e6dc9b99-64fe-4292-ad98-6974f93cd2a2.css',
        '//www.tinymce.com/css/codepen.min.css'
      ]
        });
    </script>

4. Add a <textarea> </textarea> to the HTML body like this 

<body>
    <form id="form1" runat="server">
    <div>
        <textarea>
            
        </textarea>
    </div>
    </form>
</body>

Once the steps are complete run the application to see the Tiny MCE Editor in action ...

 

Happy Coding ...

How to serialize va DataTable to JSON

Using the System.Web.Script.Serialization namespace, we can access the JavascriptSerializer to convert the DataTable to serialized JSON format

Here is the code in VB.NET:

Public Function GetJson(ByVal dt As DataTable) As String

    Dim serializer As New JavaScriptSerializer()
    Dim rows As New List(Of Dictionary(Of String, Object))()
    Dim row As Dictionary(Of String, Object) = Nothing

    For Each dr As DataRow In dt.Rows
        row = dt.Columns.Cast(Of DataColumn)().ToDictionary(Function(col) col.ColumnName.Trim(), Function(col) dr(col))
        rows.Add(row)
    Next
    Return serializer.Serialize(rows)
    End Function

 

And in C#:

public string GetJson(DataTable dt)
{

	JavaScriptSerializer serializer = new JavaScriptSerializer();
	List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
	Dictionary<string, object> row = null;

	foreach (DataRow dr in dt.Rows) {
		row = dt.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName.Trim(), col => dr(col));
		rows.Add(row);
	}
	return serializer.Serialize(rows);
}

 

 

Happy Coding ...

How to create a SQL table with column values having character prefix

Here is a cool way to create a column which will be auto incremented as well as there will be a prefix character to the records.

CREATE TABLE [dbo].[employees](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[employee_number]  AS ('A'+right('00000'+CONVERT([varchar](5),[id],0),(5)))
)

The above query will create a employees table with 2 columns, id and employee_number

The id will be auto incremented and employee_number will be 6 characters long with values computed to A00001, A00002, etc...

Note that the datatype of the employee_number is computed:

 

 

Happy Coding ....

 

 

How to list all the stored procedures modified in SQL Servers

Usually I forget to keep a track of the stored procedures that were updated as part of some task or modification requirement. To promote the set of changes, I will back trace the changes and there are good chances that some changes might get missed out.

To prevent this mishap, we can list out all the stored procedures that got modified as part of the development by using the following simple query:

SELECT *
FROM sys.objects
WHERE type = 'P' AND 
	  DATEDIFF(D, create_date, GETDATE()) < 30;

The above query will list all the stored procedures that were modified in the last 30 days.

 

Happy Coding....

How to make a SelfCert / X.509 certificate for WCF

The task of creating a Self-cert / X.509 certificate is made easy by a pluralsight tool. This can be downloaded from SelfCert.zip

Note: Make sure you got Microsoft .NET framework 3.5 installed as the tool relies on it.

  1. Once you download the file, unzip it and open (run as administrator) SelfCert.exe
  2. Fill in all the details as shown below :

Select a distinguished name (common name).

Save to cert to appropriate Location and Store based on the requirement.

Once this step is completed, you will see the following confirmation window, from here you can get the location for the private key.

 

Next important step is to install the certificate generated to the client. This can be done via Microsoft Management Console.

  1. Start > Run > mmc
  2. On Microsoft Management Console > File > Add or Remove Snap-ins
  3. On Available snap-ins select "Certificates" and click on Add button. Click on OK
  4. Now expand the Console Root > Certificates > Trusted People > Certificates
  5. Right click on the Certificates > All Tasks > Import...
  6. This should give a wizard to select the certificate file (.pfx,.cer, etc...) and once you complete the wizard steps, the certificate will be available in the selected store.

Note: Password can be set on the self-cert tool and this needs to be entered again on the MMC console while importing the certificate.

 

Happy Coding...

 

Welcome to BlogEngine.NET

If you see this post it means that BlogEngine.NET is running and the hard part of creating your own blog is done. There is only a few things left to do.

Write Permissions

To be able to log in, write posts and customize blog, you need to enable write permissions on the App_Data and Custom folders. If your blog is hosted at a hosting provider, you can either log into your account’s admin page or call the support.

If you wish to use a database to store your blog data, we still encourage you to enable this write access for an images you may wish to store for your blog posts.  If you are interested in using Microsoft SQL Server, MySQL, SQL CE, or other databases, please see the BlogEngine docs to get started.

Security

When you`ve got write permissions set, you need to change the username and password. Find the sign-in link located either at the bottom or top of the page depending on your current theme and click it. Now enter "admin" in both the username and password fields and click the button. You will now see an admin menu appear. It has a link to the "Users" admin page. From there you can change password, create new users and set roles and permissions. Passwords are hashed by default so you better configure email in settings for password recovery to work or learn how to do it manually.

Configuration and Profile

Now that you have your blog secured, take a look through the settings and give your new blog a title.  BlogEngine.NET is set up to take full advantage of many semantic formats and technologies such as FOAF, SIOC and APML. It means that the content stored in your BlogEngine.NET installation will be fully portable and auto-discoverable.  Be sure to fill in your author profile to take better advantage of this.

Themes and Plugins

One last thing to consider is customizing the look and behavior of your blog. We have themes and plugins available right out of the box. You can install more right from admin panel under Custom.

On the web

You can find news about BlogEngine.NET on the official website. For tutorials, documentation, tips and tricks visit our docs site. The ongoing development of BlogEngine.NET can be followed at Github. You can also subscribe to our Youtube channel.

Good luck and happy writing.

The BlogEngine.NET team