Enum to DataTable

I had a lot of ENUM-s and needed to make DataTable - s with the same structure for pretty much all of them.
I came up with this:

private static DataTable CreateDataTable < TheSQLtable > ()
{
Type t = typeof(TheSQLtable);
if (!t.IsEnum)
{
throw new InvalidOperationException("Type is not Enum");
}

DataTable dt = new DataTable();
dt.TableName = t.Name;
DataColumn dc;

string[] names = Enum.GetNames(t);
foreach (string name in names)
{
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = name;
dc.Unique = false;
dt.Columns.Add(dc);
}

return dt;
}

(with a little help from stackoverflow)

How to check in C# if a DataRow is Empty. (nothing in any column)

I was hoping i find something like dr.isEmpty(), but there's nothing "this nice". So you do something plain like:

bool AreAllColumnsEmpty(DataRow dr)
{
if (dr == null)
{
return true;
}
else
{
foreach(var value in dr.ItemArray)
{
if (value != null)
{
if (value.ToString() != "")
{
return false;
}
}
}
return true;
}
}

SQL Unique constraint

ALTER TABLE [CMST_Country]
ADD CONSTRAINT uc_CountryCode UNIQUE (Code)

SQL split

found this on the net

i like this very much

CREATE TABLE #t (UserName VARCHAR(50))

DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'INSERT INTO #t SELECT ''' + REPLACE(@UserList, ',', ''' UNION SELECT ''') + ''''
PRINT (@sql)
EXEC (@sql)

SELECT * FROM #t

IF OBJECT_ID('tempdb..#t') IS NOT NULL BEGIN DROP TABLE #t END

you can feedback also :)

Disable browser's back button ?

you can disable the back button, they say.

didn't test the code yet, will be back with feedback

< script type="text/javascript">

function noBack(){window.history.forward()}
noBack();
window.onload=noBack;
window.onpageshow=function(evt){if(evt.persisted)noBack()}
window.onunload=function(){void(0)}

< / script>

you can feedback also :)

Session state can only be used when enablesessionstate is set to true

I had a JavaScript function, that called a WebService, and I had this "Session state can only be used when enablesessionstate is set to true ..." error over and over again.

Everything was all right, in the web.config and in the "< % @ Page " too.

It was still giving me the error.

I made it work doing this:
start -- control panel -- administrative tools -- services -- asp.net state server

by default it is stopped. i started it.
that's it.

Select all from all tables all columns

I sometimes need to search for something that I know I added in one of the tables in a database, but i just don't know where I added it.
So for this one, I would need something to search in all the tables.
This case, only 'char', 'varchar', 'nchar', 'nvarchar' columns.

I searched on the internet, and found this guy.
The stored procedure is pretty cool, it works just the way I wanted.
I only added a SOUNDEX to it, so it can find even if you misspell the word.

it takes a while (around 10 seconds on a 400 tables database), but it is very cool.

I am thinking about how nice would it be to implement a search like this in your application (linking to the right screen, that might need a lot of parameters is probably the hard part)

So here's the code:

CREATE PROC ADMNSP_HotSearch
(
@SearchStr NVARCHAR(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630), Accuracy INT)

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110), @mySQL VARCHAR(8000)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @mySQL = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) , 1
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE SOUNDEX(' + @ColumnName + ') = SOUNDEX(''' + @SearchStr + ''') AND ABS( LEN(' + @ColumnName + ') - LEN(''' + @SearchStr + ''') ) < 10 '

/*PRINT (@mySQL)*/

INSERT INTO #Results
EXEC (@mySQL)
END
END
END

UPDATE #Results SET Accuracy = 0 WHERE CHARINDEX(@SearchStr, ColumnValue) > 0

SELECT DISTINCT ColumnName, ColumnValue, Accuracy, CHARINDEX(@SearchStr, ColumnValue) AS [CHARINDEX] FROM #Results Order by Accuracy, ColumnValue
DROP TABLE #Results
END