Dynamic Web Service JSON return

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Web.Script.Serialization
Imports System.Runtime.Serialization.Json
Imports System.IO

_
_
_
Public Class WS
Inherits System.Web.Services.WebService
Private strConn As String

_
_
Public Function GetSummaryDynamic(ByVal startrowindex As String, ByVal maximumrows As String, ByVal searchstring As String, ByVal sortby As String) As String

Dim dtResults As New DataTable
Dim ms As New MemoryStream()

Try
Using sqlConn As IDbConnection = CreateConnection(ConnStr(0))
Using sqlCmd As IDbCommand = sqlConn.CreateCommand
sqlCmd.CommandText = “SPP_SUMMARY_ENQ”
sqlCmd.CommandType = CommandType.StoredProcedure
clsCONN.addInputParam(sqlCmd, “startrowindex”, startrowindex, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “maximumrows”, maximumrows, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “searchstring”, searchstring, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “sortby”, sortby, clsCONN.dtType.dbString)
Using sqlReader As IDataReader = sqlCmd.ExecuteReader
dtResults.Load(sqlReader)
End Using
End Using
End Using

Dim strJson As String = DataTableToJSONWithJavaScriptSerializer(dtResults)
Return strJson
Catch ex As Exception
Throw (New ExceptionMsg(GenerateErrNumber(), ex))
Finally
ms.Close()
End Try
End Function

Public Function DataTableToJSONWithJavaScriptSerializer(ByVal table As DataTable) As String
Dim jsSerializer As JavaScriptSerializer = New JavaScriptSerializer
Dim parentRow As List(Of Dictionary(Of String, Object)) = New List(Of Dictionary(Of String, Object))
Dim childRow As Dictionary(Of String, Object)
For Each row As DataRow In table.Rows
childRow = New Dictionary(Of String, Object)
For Each col As DataColumn In table.Columns
childRow.Add(col.ColumnName, row(col))
Next
parentRow.Add(childRow)
Next
Return jsSerializer.Serialize(parentRow)
End Function
#End Region

http://www.c-sharpcorner.com/UploadFile/9bff34/3-ways-to-convert-datatable-to-json-string-in-Asp-Net-C-Sharp/

Get huge email list from Active Directory

DirectoryEntry entry = new DirectoryEntry(“Domain”, “UserName”, “Password”, AuthenticationTypes.None);
DirectorySearcher dSearch = new DirectorySearcher(entry);
dSearch.SearchScope = System.DirectoryServices.SearchScope.Subtree;
dSearch.ReferralChasing = ReferralChasingOption.All;
dSearch.PageSize = 5000;
dSearch.Filter = “(&(objectClass=user))”;
foreach (SearchResult sResultSet in dSearch.FindAll())
{
if (sResultSet.Properties[“mail”].Count > 0)
strClientEmails += sResultSet.Properties[“mail”][0].ToString() + “,”;
}

WA Affiliate teach wordpress

https://my.wealthyaffiliate.com/kyle

Kyle, CEO & Co-founder of Wealthy Affiliate here! It’s so wonderful to meet you and I look forward to working with you in the coming days and helping you achieve success!

You are receiving this email because you have joined Wealthy Affiliate as a Starter Member, which I think you are going to find to be a really awesome place. By lesson 4 of our “Get Started Here” training you are going to have your very own “niche” website up and running.

Carson and I want to personally welcome you to Wealthy Affiliate and I want to give you a brief overview of what you can expect and how you should get rolling within the members area.

Your First Task…

The first thing you should do is acquaint yourself with the “Get Started Here” course within the main menu. It is the BIG GREEEN button.

This course is going to walk you through how Wealthy Affiliate works and also get you up and running with your very own business (which includes your very own website).

This will be your home base and will give you step-by-step instructions on what to do. Each lesson you will be given an associated “video” walk-through that is going to SHOW you the process.

I want you to promise me as you are going through the training and you ever feel stuck or you don’t quite understand something….

Ask for HELP!

We have many communication platforms that you have access to including live chat, classroom discussions, training, and blogs that you have exclusive access to within the first 7 days of your Starter membership.

There is no reason to get frustrated as you have access to a community at Wealthy Affiliate that has many experts, many millionaire marketers, and many selfless folks that would be more than happy to help you out. Simply ask your questions as they come to mind or as you feel stuck. Someone will be there to help, including Carson and myself.

In the coming days our hope is that you will have the best experience possible…and it is our goal to facilitate this. We will be sending you some helpful emails to help you get up and running and familiarize yourself with Wealthy Affiliate ,Internet marketing, and how to get started and build success online.

We care about every single member within the Wealthy Affiliate community and I think this is going to be quite evident as you immerse yourself.

Wealthy Affiliate, The Full Walk-through

Today I want to start things off for you so that you are not left with a feeling of being overwhelmed. I have created a “video walk-through” that explains exactly what Wealthy Affiliate is.

==> Watch Kyle Walk You Through the WA System

Take a few minutes to watch this, it will make your life within the community much easier and reveal how powerful Wealthy Affiliate is going to be to your online success!

Let’s Get Rolling…

When you become a member of WA, you are automatically enrolled in the Getting Started training which is included with your Starter Membership. This is our flagship business building course that walks you through the step-by-step process of building a successful business online that earns revenue.

By the time you complete Lesson 10 of this Course, you will have developed many key web assets that will lead you to your ultimate success. You can access this course by clicking the “Get Started Here” button inside of WA, or directly through the following link.

==> Access the Getting Started training HERE!

The Getting Started (Level 1) course is going to lead to the following accomplishments:

Setting up your account profile at Wealthy Affiliate
Interaction with fellow expert marketers and aspiring marketers via live chat
Understanding the process of earning money online
Choosing your own niche from scratch
Creating your very OWN niche website
Setting your website for massive success in search engines
Activating website extensions that add many new website features
Understanding how to create, find, and install one of over 20,000 plugins on your website
Create your very first page of content on your niche website
Create relationships with others here at WA and developed your own following
Receive timely help/support when you needed it
Understand the process of finding low competition, high traffic keywords
Create a list of a bunch of awesome keywords
Create valuable content within your niche that is getting RANKED in Google
Learn new skills that will lead you to long term SUCCESS

If a particular “lesson” takes you several days, that is OK!! If you are able to accomplish several lessons in a day, that is awesome too!. Work at your own pace through this training. The most important thing is that you take action on the tasks.

Action equals success!

Without the action, no success is possible. A good deal of the actual learning process will also take place during the action phase. Over the years the #1 reason I have seen people succeed online is because they take consistent action.

Make sure you not only read through and watch the video training, that you take action on the tasks within each lesson. This is very important and will be critical to your success.

One Last Thing…

What better opportunity to say hello and introduce yourself than on Day 1.

Don’t be shy, don’t be a stranger…

You are going to find that the community at Wealthy Affiliate is full of a lot of great people, people that you can learn from, people that are willing to help, and people that have likely had your same questions at one point along the way.

We are a community that truly cares about fellow members success and you will see that being quite evident in the coming days.

Oh ya, I almost forgot. To access your membership area at any time you will use the following details. You can use your membership from any computer, or device with an Internet connection, including laptops, tablets, or your smartphone:

In the coming days I am going to be emailing you some really powerful videos, tips, and will be inviting you to some “popular” discussions that will be highly beneficial to you and your success. 🙂

Again, welcome to Wealthy Affiliate! We look forward to a long lasting and highly successful relationship with you !

Cheers,

Kill All Active Connections To A Database

To kill all the connections to your database you can use the ALTER DATABASE command. Sometimes you want to restore the database from a backup but can’t because people are connected. Here is one way which will kick off all the users immediately

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER

The code below will wait 60 seconds

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER
The reason this is better than looping over sysprocesses or sys.dm_exec_sessions is that nobody will connect while that looping piece of code runs

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database

Crowdo (Referral Unique Code: ECF003282)

Why should I invest?

There are a number of reasons you may want to invest in an Offer: 1) To diversify your investment portfolio; 2) To make a return on your investment; 3) To help a friend launch their business; 4) To help small Malaysia businesses. Investors should implement a diversification strategy when building an investment portfolio. Diversification involves spreading your money across multiple investments and will give you, as an investor, greater peace of mind that your investments will sustain in adverse market conditions and cushion losses. However, it will not lessen all types of risk.

https://investment.crowdo.com/

Table To JSON and back to Table TSQL MSSQL

TSQL

CREATE TABLE #TBL_ADM_USER
( user_id INT,
user_name NVARCHAR(1000)
)

INSERT INTO #TBL_ADM_USER
VALUES (1,’NAME1′)
INSERT INTO #TBL_ADM_USER
VALUES (2,’NAME2′)
INSERT INTO #TBL_ADM_USER
VALUES (3,’NAME3′)

DECLARE @XML_STR AS NVARCHAR(MAX)
SET @XML_STR = (SELECT user_id, user_name
FROM #TBL_ADM_USER
FOR XML PATH (‘row’))

SET @XML_STR = ‘
‘ + @XML_STR + ‘
‘;

DECLARE @cars xml;
SET @cars = @XML_STR

DECLARE @json AS NVARCHAR(MAX)
SET @json = (
SELECT Stuff( –we want to snip out the leading comma
(SELECT TheLine from –this is to glue each row into a string
(SELECT ‘,
{‘+ –this is the start of the row, representing the row object in the JSON list
–the local-name(.) is an eXPath function that gives you the name of the node
Stuff((SELECT ‘,”‘+coalesce(b.c.value(‘local-name(.)’, ‘NVARCHAR(255)’),”)+'”:”‘+
b.c.value(‘text()[1]’,’NVARCHAR(MAX)’) +'”‘
— ‘text()[1]’ gives you the text contained in the node
from x.a.nodes(‘*’) b(c) –get the row XML and split it into each node
for xml path(”),TYPE).value(‘(./text())[1]’,’NVARCHAR(MAX)’)
,1,1,”)+’}’–remove the first comma
from @Cars.nodes(‘/root/*’) x(a) –get every row
) JSON(theLine) –each row
for xml path(”),TYPE).value(‘.’,’NVARCHAR(MAX)’ )
,1,1,”)–remove the first leading comma
)

SELECT @json
SELECT * INTO #TBL_JSON FROM parseJSON(@json)

SELECT * FROM #TBL_JSON

DECLARE @SQL_STR AS NVARCHAR(MAX)
SET @SQL_STR = ‘
Select parent_ID,’

CREATE TABLE #TMP_COLUMN
(
ID INT IDENTITY(1,1),
COLUMN_NAME VARCHAR(100)
)

INSERT INTO #TMP_COLUMN (COLUMN_NAME)
SELECT DISTINCT NAME FROM #TBL_JSON
WHERE NAME ‘-‘

DECLARE @START_COLUMN_ID AS INT, @END_COLUMN_ID AS INT
SET @START_COLUMN_ID = 1
SET @END_COLUMN_ID = (SELECT MAX(ID) FROM #TMP_COLUMN)
DECLARE @TBL_COL_NAME AS NVARCHAR(1000)
WHILE @START_COLUMN_ID 0
BEGIN
SELECT @index=0,
–find the next hex escape sequence
@escape=PATINDEX(‘%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%’, @token collate SQL_Latin1_General_CP850_Bin)
IF @escape>0 –if there is one
BEGIN
WHILE @index<4 –there are always four digits to a \x sequence
BEGIN
SELECT –determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;

END
— and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
–now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
— and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
— all strings are now removed. Now we find the first leaf.
WHILE 1=1 –forever until there is nothing more to do
BEGIN

SELECT @parent_ID=@parent_ID+1
–find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)–object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 –find the innermost object or list…
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
–find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
–is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)–object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
—and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))0
BEGIN
IF @Type=’Object’ –it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(‘:’, ‘ ‘+@contents)–if there is anything, it will be a string-based name.
SELECT @start=PATINDEX(‘%[^A-Za-z@][@]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)–AAAAAAAA
SELECT @token=SUBSTRING(‘ ‘+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX(‘%[0-9]%’, @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(‘ ‘+@contents, LEN(‘ ‘+@contents+’|’)-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param –fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(‘,’, @contents)– a string-token, object-token, list-token, number,boolean, or null
IF @end=0
SELECT @end=PATINDEX(‘%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%’, @Contents+’ ‘ collate SQL_Latin1_General_CP850_Bin)
+1
SELECT
@start=PATINDEX(‘%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%’, ‘ ‘+@contents collate SQL_Latin1_General_CP850_Bin)
–select @start,@end, LEN(@contents+’|’), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+’ ‘, LEN(@contents+’|’)-@end)
IF SUBSTRING(@value, 1, 7)=’@object’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), ‘object’
ELSE
IF SUBSTRING(@value, 1, 6)=’@array’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), ‘array’
ELSE
IF SUBSTRING(@value, 1, 7)=’@string’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, ‘string’
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN (‘true’, ‘false’)
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘boolean’
ELSE
IF @value=’null’
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘null’
ELSE
IF PATINDEX(‘%[^0-9]%’, @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘real’
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, ‘int’
if @Contents=’ ‘ Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT ‘-‘,1, NULL, ”, @parent_id-1, @type

RETURN
END

TSQL to rename and script new modified stored procedure

SELECT ‘EXEC SP_RENAME ”’ + name + ”’ , ”’ + ‘X_20170223_’ + name + ””
FROM sys.objects
WHERE type = ‘P’
and modify_date > ‘2016-09-09 16:37:38.913’
and name not like ‘X_%’
order by modify_date desc

SELECT M.definition
FROM sys.procedures P WITH(NOLOCK)
INNER JOIN sys.sql_modules M WITH(NOLOCK)ON M.object_id = P.object_id
WHERE EXISTS (SELECT name, create_date, modify_date
FROM sys.objects O
WHERE type = ‘P’
and modify_date > ‘2016-09-09 16:37:38.913’
and name not like ‘X_%’
AND O.name = P.name )

SQL 2014 dont have data tools

Download SQL Server Data Tools (SSDT)

What happened to SQL Server Data Tools in SQL Server 2014?

SQL Server Data Tools in SQL Server 2014I’ve seen this question a few times and it is something that can be very confusing: You’ve installed SQL 2014 (a full version, not Express) and selected all components, yet when you go to your SQL Server 2014 program group, you can’t find SQL Server Data Tools.

In all versions of SQL Server between 2005 and 2012, the IDE for Business Intelligence projects has been included as part of the SQL Server installation. Based on Visual Studio, it gives you the ability to create BI projects in the Visual Studio Environment. In SQL Server versions 2005 through 2008 R2, it was called Business Intelligence Development Studio (BIDS). In SQL Server 2012 it was renamed to SQL Server Data Tools (SSDT) but was basically the same thing.

For some inexplicable reason, in SQL Server 2014 Microsoft decided not to include SSDT as part of the normal SQL Server installation. It is now a separate download. What is even more confusing is that there are different downloads depending on what you already have. If you go to the SQL Server Data Tools download page on MSDN, there are several options for you to choose from.

http://www.sqlsolutionsgroup.com/sql-server-data-tools/

How to connect to TFS through authenticated Web Proxy

To fix this issue you need a custom proxy module that provides the credentials, so I created a simple DLL with this class:

using System;
using System.Net;

namespace Rido.AuthProxy
{
public class AuthProxyModule : IWebProxy
{

ICredentials crendential = new NetworkCredential(“proxy.user”, “password”);

public ICredentials Credentials
{
get
{
return crendential;
}
set
{
crendential = value;
}
}

public Uri GetProxy(Uri destination)
{
return new Uri(“http://proxy:8080&#8221;, UriKind.Absolute);
}

public bool IsBypassed(Uri host)
{
return host.IsLoopback;
}

}
}
You should copy this DLL to the %PROGRAMFILES\Microsoft Visual Studio 10.0\Common7\IDE folder, and update the devenv.exe.config file to include the module:

https://blogs.msdn.microsoft.com/rido/2010/05/06/how-to-connect-to-tfs-through-authenticated-web-proxy/