Asp.net C# , Sqlserver Professional: 2016

Thursday 29 December 2016

Data Encryption and Decryption in SQL Server

--Step 1: Create a Master Key
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';

GO
--Step 2: Create Certificate
CREATE CERTIFICATE Cert_Password
ENCRYPTION BY PASSWORD = 'Password!2'
WITH SUBJECT = 'Password protection',
EXPIRY_DATE = '12/31/2099';

--Step 3: Create Symmetric Key
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_Password;

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 256

GO
--Step 4: Encrypt Data

OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';

DECLARE @tempSecurity AS TABLE (UserID VARCHAR(200), Password NVARCHAR(1000))
INSERT INTO @tempSecurity (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'PASSWORD'))
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

--Step 5: Decrypt Data
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT UserID, CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM @tempSecurity
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

GO
--DROP ALL THE KEY
DROP SYMMETRIC KEY Sym_password
GO
DROP CERTIFICATE Cert_Password
GO
DROP MASTER KEY

Tuesday 15 November 2016

Proper case in sql server

CREATE Function [dbo].[fn_get_ProperCase](@InputString as varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)

IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != '''' OR UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')

END

Friday 23 September 2016

Force browsers to get latest js and css files in asp.net

1. Crete a class named FileUtility

using System.Web;

public class FileUtility
{
public static string SetJsVersion(HttpContext context, string filename)
{
string version = GetJsFileVersion(context, filename);
return filename + version;
}

private static string GetJsFileVersion(HttpContext context, string filename)
{
if (context.Cache[filename] == null)
{
string filePhysicalPath = context.Server.MapPath(filename);

string version = "?v=" + GetFileLastModifiedDateTime(context, filePhysicalPath, "yyyyMMddhhmmss");

return version;
}
else
{
return string.Empty;
}
}

public static string GetFileLastModifiedDateTime(HttpContext context, string filePath, string dateFormat)
{
return new System.IO.FileInfo(filePath).LastWriteTime.ToString(dateFormat);
}
}

2.Rendered HTML

For Css : <link href="<%=FileUtility.SetJsVersion(Context,"css/bootstrap.css")%>" rel="stylesheet" />

For js : <script type="text/javascript" src='<%=FileUtility.SetJsVersion(Context,"/js/json.js") %>'></script>

SQL Server thousands separator for a column

SELECT CONVERT(VARCHAR,CONVERT(MONEY, 234234234),1)

Tuesday 30 August 2016

Filter or Search ASP.Net DropDownList Using JavaScript

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DemoChosen.aspx.cs" Inherits="Demo.DemoChosen" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>

<link href="http://cdnjs.cloudflare.com/ajax/libs/chosen/1.1.0/chosen.min.css" rel="stylesheet" />
<script src="http://cdnjs.cloudflare.com/ajax/libs/chosen/1.1.0/chosen.jquery.min.js"></script>

<title></title>

<style>
.panel-heading
{
cursor: pointer;
}

select.form-control + .chosen-container.chosen-container-single .chosen-single
{
display: block;
width: 100%;
height: 34px;
padding: 6px 12px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #fff;
border: 1px solid #ccc;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0,0,0,0.075);
box-shadow: inset 0 1px 1px rgba(0,0,0,0.075);
-webkit-transition: border-color ease-in-out .15s,box-shadow ease-in-out .15s;
transition: border-color ease-in-out .15s,box-shadow ease-in-out .15s;
background-image: none;
}

select.form-control + .chosen-container.chosen-container-single .chosen-single div
{
top: 4px;
color: #000;
}

select.form-control + .chosen-container .chosen-drop
{
background-color: #FFF;
border: 1px solid #CCC;
border: 1px solid rgba(0, 0, 0, 0.15);
border-radius: 4px;
-webkit-box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
background-clip: padding-box;
margin: 2px 0 0;
}

select.form-control + .chosen-container .chosen-search input[type=text]
{
display: block;
width: 100%;
height: 34px;
padding: 6px 12px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #FFF;
border: 1px solid #CCC;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
-webkit-transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
background-image: none;
}

select.form-control + .chosen-container .chosen-results
{
margin: 2px 0 0;
padding: 5px 0;
font-size: 14px;
list-style: none;
background-color: #fff;
margin-bottom: 5px;
}

select.form-control + .chosen-container .chosen-results li,
select.form-control + .chosen-container .chosen-results li.active-result
{
display: block;
padding: 3px 20px;
clear: both;
font-weight: normal;
line-height: 1.428571429;
color: #333;
white-space: nowrap;
background-image: none;
}

select.form-control + .chosen-container .chosen-results li:hover,
select.form-control + .chosen-container .chosen-results li.active-result:hover,
select.form-control + .chosen-container .chosen-results li.highlighted
{
color: #FFF;
text-decoration: none;
background-color: #428BCA;
background-image: none;
}

select.form-control + .chosen-container-multi .chosen-choices
{
display: block;
width: 100%;
min-height: 34px;
padding: 6px;
font-size: 14px;
line-height: 1.428571429;
color: #555;
vertical-align: middle;
background-color: #FFF;
border: 1px solid #CCC;
border-radius: 4px;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075);
-webkit-transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
transition: border-color ease-in-out 0.15s, box-shadow ease-in-out 0.15s;
background-image: none;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-field input[type="text"]
{
height: auto;
padding: 5px 0;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-choice
{
background-image: none;
padding: 3px 24px 3px 5px;
margin: 0 6px 0 0;
font-size: 14px;
font-weight: normal;
line-height: 1.428571429;
text-align: center;
white-space: nowrap;
vertical-align: middle;
cursor: pointer;
border: 1px solid #ccc;
border-radius: 4px;
color: #333;
background-color: #FFF;
border-color: #CCC;
}

select.form-control + .chosen-container-multi .chosen-choices li.search-choice .search-choice-close
{
top: 8px;
right: 6px;
}

select.form-control + .chosen-container-multi.chosen-container-active .chosen-choices,
select.form-control + .chosen-container.chosen-container-single.chosen-container-active .chosen-single,
select.form-control + .chosen-container .chosen-search input[type=text]:focus
{
border-color: #66AFE9;
outline: 0;
-webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075),0 0 8px rgba(102, 175, 233, 0.6);
box-shadow: inset 0 1px 1px rgba(0, 0, 0, 0.075),0 0 8px rgba(102, 175, 233, 0.6);
}

select.form-control + .chosen-container-multi .chosen-results li.result-selected
{
display: list-item;
color: #ccc;
cursor: default;
background-color: white;
}
</style>

</head>
<body>
<form id="form1" runat="server">
<div>
<div class="form-group">
<label for="id_tipo_equipo" class="control-label">Tipo</label>
<select name="id_tipo_equipo" id="id_tipo_equipo" class="form-control chosen" data-placeholder="[Tipo]">
<option value="1">Access Point</option>
<option value="2">Adaptador</option>
<option value="3">Adaptador DVI a VGA</option>
<option value="4">Adaptador Electrico para Discos</option>
<option value="5">Aire Acondicionado</option>
<option value="6">Amplificador</option>
<option value="7">Antena</option>
<option value="8">Antena Poe</option>
<option value="9">Bases de Monitor</option>
<option value="10">Bluetooth</option>
<option value="11">Cable de Poder</option>
<option value="12">Cable USB</option>
<option value="13">Cable UTP</option>
<option value="14">Cables Conectores</option>
<option value="15">Camara de Fotos</option>
<option value="16">Camara de Video</option>
</select>

</div>

<button onclick=" return getVal();">Get Selected Value</button>
</div>
</form>
</body>
<script>
$(document).ready(function () {
// $('select').chosen({ width: '100%' });
var config = {
'#id_tipo_equipo': {},
'.chosen-select-deselect': { allow_single_deselect: true },
'.chosen-select-no-single': { disable_search_threshold: 10 },
'.chosen-select-no-results': { no_results_text: 'Oops, nothing found!' },
'.chosen-select-width': { width: "95%" }
}

for (var selector in config) {
$(selector).chosen(config[selector]);
}
});

function getVal() {
alert(document.getElementById("id_tipo_equipo").value);
return false;
}
</script>
</html>

Wednesday 27 July 2016

Reading and write a text file with SQL Server 2008

1. /*-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
*/

2. --------INSERT DATA INTO SERVER AND STORE THE TEXT FILE INTO SERVER

DECLARE
@saveas VARCHAR(2048)
,@query VARCHAR(2048)
,@bcpquery VARCHAR(2048)
,@bcpconn VARCHAR(64)
,@bcpdelim VARCHAR(2)

SET @query = 'select * from tbl_name'
SET @saveas = 'D:\tbl_name.txt'
SET @bcpdelim = '|'
SET @bcpconn = '-T' -- Trusted
SET @bcpconn = '-U sql_database -P sql_password -d database_name -S server_name' -- SQL authentication

SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -c -t^' + @bcpdelim + ' ' + @bcpconn
EXEC master..xp_cmdshell @bcpquery

3. --------TAKE COPY THE TXT FILE FROM SERVER AND PAST Destination machine
BULK INSERT dbo.tbl_bulkinsert
FROM 'D:\tbl_name.txt'
WITH ( FIELDTERMINATOR ='|', FIRSTROW =1 )

4. SELECT * FROM tbl_bulkinsert

Sunday 6 March 2016

Find all children for multiple parents in SQL query

CREATE TABLE #tbl_product(
product_id INT IDENTITY(1,1) NOT NULL,
product_name nvarchar(200),
belongs_to INT
)

INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Electronics',NULL)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Furniture',NULL)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('MObile',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Samsung Edge',3)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Lenovo GT',3)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Body Trimmer',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Car Fregnance',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Car Fregnance',1)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Chair',2)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('NIlkomal Chair',9)
INSERT INTO #tbl_product(product_name,belongs_to) VALUES('Queen size bed',2)

;WITH cte AS(
SELECT
product_id,
product_name,
belongs_to,
[level] = 0,
Sortid = CAST(product_id AS VARCHAR(MAX))
FROM #tbl_product
WHERE ISNULL(belongs_to,0) = 0
UNION ALL
SELECT
a.product_id,
a.product_name,
a.belongs_to,
[level] = [level] + 1,
Sortid = Sortid + CAST(a.product_id AS VARCHAR(MAX))
FROM #tbl_product a
JOIN cte b
ON a.belongs_to = b.product_id
)
SELECT
product_id,
REPLICATE(' ¦ ', [level]) + product_name,
belongs_to
FROM cte
ORDER BY Sortid

DROP TABLE #tbl_product

Monday 18 January 2016

C# Regular Expression Get Text Between Brackets

//string regularExpressionPattern = @"\<(.*?)\>"; // FOR right angles < >
//string regularExpressionPattern = @"$(.*?)$"; // FOR parenthesis ( )
//string regularExpressionPattern = @"\{(.*?)\}"; // FOR curly brackets { }

string regularExpressionPattern = @"\[(.*?)\]"; //FOR []

string inputText = "Find string inside brackets [test1] and [test2] in [ASP.net]";

Regex re = new Regex(regularExpressionPattern);

foreach (Match m in re.Matches(inputText))
{
//Put Your LOGIC
}

Asp.net C# , Sqlserver Professional