C# from SQL

Sharing Knowledge is always advisable

We all are familiar how to declare function in C# and SQL and call it. Today we are going to learn how to call C# function from SQL.

Figure 1 Select Project

Open Visual Studio and select Add New Project à and window will popup as Figure1 and from them select the Template Database and under this template select SQLServer. Select the Project shown in figure for starting this feature.

After you have selected the template when clicking on OK the window will popup asking for to add database reference as shown in figure below

Figure 2 Database Connection

You can select the connection from existing list or can also create the new by clicking on Add New References.

After selecting the database reference when you click OK the window will popup asking you to allow debugging or not.

Figure 3 SQL/CLR Debugging

It is upto you as what you want, I select no and proceed further. The project gets loaded as shown in figure.

Right click the project and select add new and select Stored Procedure from the window.

Figure 4 Stored Procedure

If you want you select any template from the required one here I am selecting stored procedure. After selecting the stored procedure the visual studio will prepare the class for you, I have created one method which will print the current time to sql message screen when executed as shown in the figure.

After this you need to build the project and deploy your code to SQL server.

Figure 5 Build & Deploy

Deploy will directly registered the .dll of your project to the SQL server so that you can execute your stored procedure. If your deployment failed then you can use the alternate method to register the .dll into the Database. Connect the database and open query window and write the following command

Create ASSEMBLY CLRTest

FROM ‘yourprojectdllpath’

Or you can also register the .dll by right clicking the assembly icon in your database and select New Assembly as shown in the figure.

Figure 6 Register Assembly

After registering the .dll we need to enable the clr to able execute the C# code for this write the following command

EXEC sp_configure 'clr enabled', 1;  RECONFIGURE WITH OVERRIDE;

To execute our stored procedure create stored procedure and write following query as shown below.

create procedure sp_CLRTest3

as

external name

[CLRTest].[StoredProcedures].sp_CLRTest1

Go

# Defnition Format – EXTERNAL NAME [AssemblyName].[ClassName].[MemberName]

After doing this you can easily see the procedure listed in the procedure tab as shown in the figure

Now simply execute your procedure by writing

exec sp_CLRTest1

when you excute this the message window will show the result

Current Time : 2017-09-08 9:18:21 AM

Count(*) vs Count(Column)

Difference between count(column) and count(*)

count(*) would return an extra row in the result that contains a null and the count of null values in the column.

for eg.

create table #demo(id int,id2 int)

insert #demo values(null,null)

insert #demo values(1,null)

insert #demo values(null,1)

insert #demo values(1,null)

insert #demo values(null,1)

insert #demo values(1,null)

insert #demo values(null,null)

select count(*),count(id),count(id2) from #demo

results 7 3 2

Another minor difference, between using * and a specific column, is that in the column case you can add the keyword DISTINCT, and restrict the count to distinct values

AngularJS Directive

Sharing Knowledge is always advisable

What are Directives?

Directives in AngularJS are used simplify DOM manipulation. They can modify the behavior of new and existing DOM elements, by adding custom functionality, like a datepicker or an autocomplete widget. AngularJS comes with its own set of built-in directives, as well as the ability to add your own ones.

Why are directives used in AngularJS?

Standard HTML has elements like <span>, <input>, and <button> that have fixed behavior. To make the <input> element behave like a datepicker, it takes custom CSS and JS calls from your Javascript code. AngularJS makes this easy by allowing you to wrap all this in what we call a directive. So instead of writing

<input id="datepickerElem">

and then calling
$('#datepickerElem').datepicker()

from some random place in your code, you can now instead write, directly in your HTML
<input datepicker>
or
<datepicker>

How to use directives in AngularJS?

Using directives in an AngularJS application is easy. You just need to add the directive name in whichever DOM element you want right in your HTML. For example, a datepicker Directive, depending on how it is defined, could be used as

<input datepicker>

or

<datepicker>

While declaring an AngularJS directive, the naming convention followed is camelCase. For example, we would define the name of the directive as ‘fundooDatepicker’. But when you actually use the directive in your HTML, it is the dash-separated version. That is, our widget would be ‘<fundoo-datepicker>’ and not ‘<fundooDatepicker>’

AngularJS’ in-built directives

ng-app : Initializes application.

ng-model : Binds HTML controls to application data.

ng-Controller : Attaches a controller class to view.

ng-repeat : Bind repeated data HTML elements. Its like a for loop.

ng-if : Bind HTML elements with condition.

ng-show : Used to show the HTML elements.

ng-hide : Used to hide the HTML elements.

ng-class : Used to assign CSS class.

ng-src : Used to pass the URL image etc.

Event Listeners

ng-click : Click event to bind on HTML elements.

ng-dbl-click

Mouse event listeners

ng-mousedown
ng-mouseup
ng-mouseenter
ng-mouseleave
ng-mousemove
ng-mouseover

Keyboard event listeners

ng-keydown
ng-keyup
ng-keypress
ng-change

this is not complete list. For complete one go to this link

https://docs.angularjs.org/api/ng/directive

ng-app : 

<!DOCTYPE html>

<html>

<head>

    <title></title>

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/angularjs/1.0.7/angular.min.js"></script>

</head>

<body ng-app>

    <div>

          Here is your AJAX-powered text: <span style="color: fuchsia;">{{ txt }}</span>

            <p>

                Enter message:

                <input type="text" ng-model="txt" /></p>

    </div>

   

</body>

</html>

the output will be

HTML document is loaded into the browser, and evaluated by the browser. At this time the AngularJS JavaScript file is loaded, the angular global object is created, and your JavaScript which registers controller functions is executed.

Second, AngularJS scans through the HTML to look for AngularJS apps and views. when it find it the page is now ready.

Now if we remove the ng-app directive from body tag the output will be

ng-repeat :

<div ng-app="" ng-init="names=['Jani','Hege','Kai']">
<ul>
<li ng-repeat="x in names">
{{ x }}
</li>
</ul>
</div>

output will be :- 

Looping with ng-repeat:

• Jani
• Hege
• Kai