ASHISH SRIVASTAVA: June 2017

Thursday 29 June 2017

How to remove empty lines in text In Visual Studio?

Visual Studio has ability to delete empty lines in replace operation using regular expressions.

Click Ctrl-H (quick replace)
Tick "Use Regular Expressions"
In Find specify ^$\n
In Replace box delete everything.
Click "Replace All"

All empty lines will be deleted.

Regular expression for empty line consist of

Beginning of line ^

End of line $

Line break \n

Monday 26 June 2017

SQL Database Full Text Search

DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit
SET @SearchStrColumnValue = '%OWNED%' /* use LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all, use LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
WHILE @TableName IS NOT NULL
BEGIN
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
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
INSERT INTO @ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
BEGIN
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
END
END
END
SET NOCOUNT OFF
SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Output:

How to get Visitor IP Address and Location in ASP.Net

HttpRequest request=base.Request;
String VisitorIP=request.UserHostAddress;

Export DataSet To Excel in MVC

NameSpace :
using System.Web.Hosting;
using ClosedXML.Excel;

Code :

[MTAThread]
private void ExportDataSetToExcel(DataSet ds, String excelName)
{

string path = HostingEnvironment.MapPath("~/FolderName/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
path = path + excelName;
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(ds.Tables[0]);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
wb.SaveAs(path, false);
}
}

Example:

DataSet objds = new Dataset();
ExportDataSetToExcel(objds, "FileName.xlsx");

DataTable To JSON With StringBuilder in MVC

Namespace

using System.Text;

code :

/// <summary>
/// Datatable to JSON
/// <CreatedBy>Ashish Srivastava</CreatedBy>
/// <Date>12 Dec 2016</Date>
/// </summary>
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public string DataTableToJSONWithStringBuilder(DataTable table)
{
var JSONString = new StringBuilder();
if (table.Rows.Count > 0)
{
JSONString.Append("{ \"status\":\"1\" ,\"result\": [");
for (int i = 0; i < table.Rows.Count; i++)
{
JSONString.Append("{");
for (int j = 0; j < table.Columns.Count; j++)
{
if (j < table.Columns.Count - 1)
{
JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
}
else if (j == table.Columns.Count - 1)
{
JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
}
}
if (i == table.Rows.Count - 1)
{
JSONString.Append("}");
}
else
{
JSONString.Append("},");
}
}
}
else
{
JSONString.Append("{ \"status\":\"0\" ,\"result\": [");
JSONString.Append("{\"value\":" + "\"No record found\"}");
}
JSONString.Append("]}");
return JSONString.ToString();
}

Saturday 10 June 2017

SQL Query Time Execute Check

SELECT
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;