JanaDotNet: 2015

Sunday 27 December 2015

SQL SERVER – Script: Remove Spaces in Column Name in All Tables

I have written the script and share with him.

SELECT 'EXEC SP_RENAME ''' + B.NAME + '.' + A.NAME

+ ''', ''' + REPLACE(A.NAME, ' ', '')

+ ''', ''COLUMN'''

FROM   sys.columns A

INNER JOIN sys.tables B

ON A.OBJECT_ID = B.OBJECT_ID

AND OBJECTPROPERTY(b.OBJECT_ID, N'IsUserTable') = 1

WHERE  system_type_id IN (SELECT system_type_id

FROM   sys.types)

AND CHARINDEX(' ', a.NAME) <> 0

Above script would NOT make the change, but provide a script which can be verified before executing. This is a good practice to verify the objects and scripts rather than executing it directly. Here is the test run of the script. I have created database and a few tables which have space between columns.

CREATE DATABASE SpaceRemoveDB

GO

USE SpaceRemoveDB

GO

CREATE TABLE [dbo].[AWBuildVersion] (

[SystemInformationID] [tinyint] IDENTITY(1, 1) NOT NULL

,[Database Version] [nvarchar](25) NOT NULL

,[VersionDate] [datetime] NOT NULL

,[ModifiedDate] [datetime] NOT NULL

,[NewCOlumn] [nchar](10) NULL

,[c2] [int] NULL

,CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED ([SystemInformationID] ASC)

)

GO

CREATE TABLE [dbo].[Employee] (

[First name] [varchar](100) NULL

,[Last Name] [varchar](100) NULL

) ON [PRIMARY]

GO

Here is the database, table and columns in SSMS. I have highlighted the column which have spaces.

column space 01 SQL SERVER Script: Remove Spaces in Column Name in All Tables

Now, we can run the script provided earlier to test.

column space 02 SQL SERVER Script: Remove Spaces in Column Name in All Tables

As expected, the script is showing three columns from two tables which has space. Output can be run after verification.

Once we run below, the goal is achieved.

EXEC sp_RENAME 'AWBuildVersion.Database Version', 'DatabaseVersion', 'COLUMN'

EXEC sp_RENAME 'Employee.First name', 'Firstname', 'COLUMN'

EXEC sp_RENAME 'Employee.Last Name', 'LastName', 'COLUMN'

We would get below warning three times (one for each sp_rename)

Caution: Changing any part of an object name could break scripts and stored procedures.

And here is the SSMS after running script.

column space 03 SQL SERVER Script: Remove Spaces in Column Name in All Tables

Do you have a similar script to share with other blog readers? I think we can surely learn from each other here too.

Friday 18 December 2015

ASP.NET MVC: Custom Validation by DataAnnotation with client side

You could write a custom validation attribute:

public class CombinedMinLengthAttribute: ValidationAttribute
{
    public CombinedMinLengthAttribute(int minLength, params string[] propertyNames)
    {
        this.PropertyNames = propertyNames;
        this.MinLength = minLength;
    }

    public string[] PropertyNames { get; private set; }
    public int MinLength { get; private set; }

    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        var properties = this.PropertyNames.Select(validationContext.ObjectType.GetProperty);
        var values = properties.Select(p => p.GetValue(validationContext.ObjectInstance, null)).OfType<string>();
        var totalLength = values.Sum(x => x.Length) + Convert.ToString(value).Length;
        if (totalLength < this.MinLength)
        {
            return new ValidationResult(this.FormatErrorMessage(validationContext.DisplayName));
        }
        return null;
    }
}

and then you might have a view model and decorate one of its properties with it:

public class MyViewModel
{
    [CombinedMinLength(20, "Bar", "Baz", ErrorMessage = "The combined minimum length of the Foo, Bar and Baz properties should be longer than 20")]
    public string Foo { get; set; }
    public string Bar { get; set; }
    public string Baz { get; set; }
}

client side

---------------



<script src="@Url.Content("~/Scripts/jquery-1.8.2.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.js")" type="text/javascript"></script>



<script type="text/javascript">
    // we add a custom jquery validation method
    jQuery.validator.addMethod('greaterThan', function (value, element, params) {
        if (!/Invalid|NaN/.test(new Date(value))) {
            return new Date(value) > new Date($(params).val());
        }
        return isNaN(value) && isNaN($(params).val()) || (parseFloat(value) > parseFloat($(params).val()));
    }, '');

    // and an unobtrusive adapter
    jQuery.validator.unobtrusive.adapters.add('futuredate', {}, function (options) {
        options.rules['greaterThan'] = true;
        options.messages['greaterThan'] = options.message;
    });

   
    $.validator.addMethod("exclude", function (value, element, exclude) {
        if (value) {
            for (var i = 0; i < exclude.length; i++) {
                if (jQuery.inArray(exclude[i], value) != -1) {
                    return false;
                }
            }
        }
        return true;
    });

    $.validator.unobtrusive.adapters.addSingleVal("exclude", "chars");

</script>
  public class custome
    {
        [CombinedMinLengthAttribute("name ", ErrorMessage="fdgd")]
        public string name { get; set; }
        public string address { get; set; }
        [FutureDate(ErrorMessage = "Should be in the future")]
        public DateTime Date { get; set; }
    }

public class FutureDateAttribute : ValidationAttribute, IClientValidatable
    {
        public override bool IsValid(object value)
        {
            if (value == null || (DateTime)value < DateTime.Now)
                return false;

            return true;
        }

        public IEnumerable<ModelClientValidationRule> GetClientValidationRules(ModelMetadata metadata, ControllerContext context)
        {
            yield return new ModelClientValidationRule
            {
                ErrorMessage = this.ErrorMessage,
                ValidationType = "futuredate"
            };
        }
    }

MVC 4: Custom Validation Data Annotation Attribute

Data Annotations
Validation in MVC can be done using Data Annotations that are applied to both the client and server side.

Data Annotation attributes are used to validate the user inputs when posting the form. All the Data Annotation attributes like Required, Range are derived from the ValidationAttribute class that is an abstract class. The ValidationAttribute base class lives in the System.ComponentModel.DataAnnotations namespace.

This article is showing how to create a custom Data Annotations step by step:

Step 1

Step 2

Step 3

IsValid

CustomEmailValidator.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Text.RegularExpressions;
namespace Custom_DataAnnotation_Attribute.Models
{
public class CustomEmailValidator : ValidationAttribute
{
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
{
if (value != null)
{
string email = value.ToString();
if (Regex.IsMatch(email, @"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}", RegexOptions.IgnoreCase))
{
return ValidationResult.Success;
}
else
{
return new ValidationResult("Please Enter a Valid Email.");
}
}
else
{
return new ValidationResult("" + validationContext.DisplayName + " is required");
}
}

Step 4

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
namespace Custom_DataAnnotation_Attribute.Models
{
public class EmployeeModel
{
public string Name { get; set; }
[CustomEmailValidator]
public string Email { get; set; }
public string Password { get; set; }
public string Mobile { get; set; }
}
}

Step 5

ManageEmployeeController

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Custom_DataAnnotation_Attribute.Models;
namespace Custom_DataAnnotation_Attribute.Controllers
{
public class ManageEmployeeController : Controller
{
//
// GET: /ManageEmployee/
public ActionResult Index()
{
return View();
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Index(EmployeeModel model)
{
if (ModelState.IsValid)
{
ViewBag.Name = model.Name;
ViewBag.Email = model.Email;
ViewBag.Password = model.Password;
ViewBag.Mobile = model.Mobile;
}
return View(model);
}
public ActionResult Register()
{
return View();
}
}
}

Step 6

Step 7

@model Custom_DataAnnotation_Attribute.Models.EmployeeModel
@{
ViewBag.Title = "Register Employee";
}
<h2>Register</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>EmployeeModel</legend>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Email)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Email)
@Html.ValidationMessageFor(model => model.Email)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Password)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Password)
@Html.ValidationMessageFor(model => model.Password)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Mobile)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Mobile)
@Html.ValidationMessageFor(model => model.Mobile)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}

Now run the application:

email is require

Image 3

Image 4

Get all the tables on which a stored procedure depends sql server

SELECT DISTINCT
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.GI_VALIDATION', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U', 'V')

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name

Thursday 10 December 2015

Custom Validation Message Helper in ASP.NET MVC

Introduction
I wrote an earlier post about Creating Custom Html Helpers in ASP.NET MVC which emphasized on how we can write custom html helper extensions in ASP.NET MVC according to our need, so that we can reuse them in our complete application, instead of writing plain html in View.

The example in that article was using ActionLink, today I am going to tell how we can implement custom Validation Message helper. I wanted to modify the validation message displaying in my application so that it displays * in front of required fields and the error message in tooltip of it like,

sign up

For that, add a class in the project and add an extension method which will render our custom html that will be displayed for error message,

namespace CustomValidationMessageHelper.Helpers
{
public static class Validator
{
public static MvcHtmlString MyValidationMessageFor < TModel,
TProperty > (this HtmlHelper < TModel > helper,
Expression < Func < TModel, TProperty >> expression)
{
TagBuilder containerDivBuilder = new TagBuilder("div");
containerDivBuilder.AddCssClass("tip_trigger");
containerDivBuilder.InnerHtml = "*";
TagBuilder midDivBuilder = new TagBuilder("div");
midDivBuilder.AddCssClass("classic");
midDivBuilder.AddCssClass("tip");
midDivBuilder.InnerHtml = helper.ValidationMessageFor(expression).ToString();
containerDivBuilder.InnerHtml += midDivBuilder.ToString(TagRenderMode.Normal);
return MvcHtmlString.Create(containerDivBuilder.ToString(TagRenderMode.Normal));
}
}
}

and then define the following CSS in a CSS file, in my case it is site.css or you can add it in the view,

.validated
{
border - color: #DCE4EC!important;
}
textarea, input[type = "text"], input[type = "password"],
input[type = "datetime"],
input[type = "datetime-local"], input[type = "date"],
input[type = "month"],
input[type = "time"], input[type = "week"],
input[type = "number"], input[type = "email"],
input[type = "url"], input[type = "search"],
input[type = "tel"], input[type = "color"],
.uneditable - input {
padding: 3 px 3 px;
border: 1 px solid# DCE4EC;
}
.tip
{
background: none repeat scroll 0 0# FFFFFF;
border: 1 px solid #808080;
border-radius: 10px;
box-shadow: 0 1px 10px rgba(32, 32, 32, 0.5);
color: red;
display: none;
font-size: 12px;
font-style: normal;
margin-left: 10px;
margin-top: -24px;
padding: 4px;
position: absolute;
z-index: 999999;
}
.tip_trigger
{
width: 10px;
float: right;
color: red;
margin-left: 3px;
}

Now we have to add client side code, which I have written in jQuery in a js file or directly in view. In my case, I have it in CustomValidation.js file,

$(document).ready(function()
{
//Tooltips
var tip;
$(".tip_trigger").hover(function()
{
console.log("hovered");
tip = $(this).find('.tip');
console.log($(this).find('.tip').find('span').html())
if ($(this).find('.tip').find('span').html() != '')
{
$(this).find('.tip').show(); //Show tooltip
}
}, function()
{
$(this).find('.tip').hide(); //Hide tooltip
});
////Required fields
$('input').each(function()
{
var req = $(this).attr('data-val-required');
if (undefined != req) {
$(this).css("border-color", "#DA9BA2")
}
if ($(this).val() != '')
{
$(this).addClass("validated");
}
});
$('input').blur(function()
{
if ($(this).val() != '')
{
$(this).addClass("validated");
} else {
$(this).css("border-color", "#DA9BA2")
}
});
});

Now in the View, add the reference to the related js and css files in the head section of View:

<link href="@Url.Content(" ~/Content/site.css ")" rel="stylesheet" />
<script src="@Url.Content(" ~/Scripts/jquery-1.9.1.js ")"></script>
<script src="@Url.Content(" ~/Scripts/jquery.unobtrusive-ajax.js ")"></script>
<script src="@Url.Content(" ~/Scripts/jquery.validate.js ")" type="text/javascript"></script>
<script src="@Url.Content(" ~/Scripts/jquery.validate.unobtrusive.js ")" type="text/javascript">
</script>
<script src="@Url.Content(" ~/Scripts/CustomValidation.js ")" type="text/javascript"></script>

Now in your view, add using statement of the namespace and now you can access the Helper method in the View,

@model CustomValidationMessageHelper.ViewModels.SignUpViewModel
@using CustomValidationMessageHelper.Helpers
@ {
Layout = null;
}
@Html.TextBoxFor(model => model.FirstName, new
{
@class = "form-control input-sm"
})
@Html.MyValidationMessageFor(model => model.FirstName)

The sample project can be downloaded from here.

Monday 2 November 2015

Using Dynamic SQL in Stored Procedures--select query by filter condition using if

Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks.

A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]')
            AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
    @CustomerID INT = NULL,
    @ContactID INT = NULL,
    @debug bit = 0 )
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT    @ParameterDefinition = '
    @CustomerParameter INT,
    @ContactParameter INT
';

SELECT    @SQL = N'
SELECT    [SalesOrderID], [OrderDate], [Status],
    [CustomerID], [ContactID]
FROM    [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND CustomerID = @CustomerParameter ';

IF @ContactID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND ContactID = @ContactParameter ';

IF @debug = 1
    PRINT @SQL

EXEC sp_executeSQL
    @SQL,
    @ParameterDefinition,
    @CustomerParameter = @CustomerID,
    @ContactParameter = @ContactID;
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724

A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
   AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
 @CustomerID INT = NULL,
 @ContactID INT = NULL,
 @debug bit = 0 )
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT @ParameterDefinition = '
 @CustomerParameter INT,
 @ContactParameter INT
';

SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status], 
 [CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND CustomerID = @CustomerParameter ';
 
IF @ContactID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND ContactID = @ContactParameter ';
 
IF @debug = 1
 PRINT @SQL
 
EXEC sp_executeSQL 
 @SQL,
 @ParameterDefinition,
 @CustomerParameter = @CustomerID,
 @ContactParameter = @ContactID; 
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724

- See more at: http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf

A simple example of a stored procedure with dynamic SQL is:

use AdventureWorks
GO
IF  EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
   AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
 @CustomerID INT = NULL,
 @ContactID INT = NULL,
 @debug bit = 0 )
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT @ParameterDefinition = '
 @CustomerParameter INT,
 @ContactParameter INT
';

SELECT @SQL = N'
SELECT [SalesOrderID], [OrderDate], [Status], 
 [CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND CustomerID = @CustomerParameter ';
 
IF @ContactID IS NOT NULL
 SELECT @SQL = @SQL + N'
 AND ContactID = @ContactParameter ';
 
IF @debug = 1
 PRINT @SQL
 
EXEC sp_executeSQL 
 @SQL,
 @ParameterDefinition,
 @CustomerParameter = @CustomerID,
 @ContactParameter = @ContactID; 
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724

- See more at: http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf

use AdventureWorks GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') AND type in (N'P', N'PC')) DROP PROCEDURE [Sales].[GetSalesOrders] GO CREATE PROCEDURE [Sales].[GetSalesOrders] ( @CustomerID INT = NULL, @ContactID INT = NULL, @debug bit = 0 ) AS SET NOCOUNT ON; DECLARE @SQL NVARCHAR(4000); DECLARE @ParameterDefinition NVARCHAR(4000); SELECT @ParameterDefinition = ' @CustomerParameter INT, @ContactParameter INT '; SELECT @SQL = N' SELECT [SalesOrderID], [OrderDate], [Status], [CustomerID], [ContactID] FROM [Sales].[SalesOrderHeader] WHERE 1 = 1 '; IF @CustomerID IS NOT NULL SELECT @SQL = @SQL + N' AND CustomerID = @CustomerParameter '; IF @ContactID IS NOT NULL SELECT @SQL = @SQL + N' AND ContactID = @ContactParameter '; IF @debug = 1 PRINT @SQL EXEC sp_executeSQL @SQL, @ParameterDefinition, @CustomerParameter = @CustomerID, @ContactParameter = @ContactID; GO EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 11724 - See more at: http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures#sthash.Pj99nzti.dpuf

Tuesday 15 September 2015

Paging With Bootstrap In MVC 4

http://mvcpaging.apphb.com/

http://mvcpaging.apphb.com/#download

Friday 11 September 2015

SQL SERVER – Who Dropped Table or Database?

I used to run a series on Hero’s and Sherlock Holmes is one of my favorite. There is always a need to get to the bottom of the problem statement and whether you are a developer or a DBA, at least once you might have been asked one of the following “Who” question after a disaster.

Who dropped table in the database? From which application? When?
Who dropped database? What was the date and time?
Who created database on production server?
Who altered the database?
Who dropped the schema?
Who altered the schema?

And there are many other similar questions. Generally rich companies have auditing enabled and they have many tools which might be capturing all DDLs via either Auditing or via 3^rd party tools. Many of DBAs and developer don’t know that there is a default trace which runs in the background in every SQL Server installation

Here are few usage of default traces which are via SSMS.

SQL SERVER – SSMS: Configuration Changes History

SQL SERVER – SSMS: Schema Change History Report

Let’s look at the events captured by the default trace.

SELECT DISTINCT Trace.Event_ID, TraceEvents.NAME AS Event_Desc

FROM   ::fn_trace_geteventinfo(1) Trace

,sys.trace_events TraceEvents

WHERE Trace.eventID = TraceEvents.trace_event_id

Here is what we would get in SQL Server 2014

Event_ID	Event_Desc
18	Audit Server Starts And Stops
20	Audit Login Failed
22	ErrorLog
46	Object:Created
47	Object:Deleted
55	Hash Warning
69	Sort Warnings
79	Missing Column Statistics
80	Missing Join Predicate
81	Server Memory Change
92	Data File Auto Grow
93	Log File Auto Grow
94	Data File Auto Shrink
95	Log File Auto Shrink
102	Audit Database Scope GDR Event
103	Audit Schema Object GDR Event
104	Audit Addlogin Event
105	Audit Login GDR Event
106	Audit Login Change Property Event
108	Audit Add Login to Server Role Event
109	Audit Add DB User Event
110	Audit Add Member to DB Role Event
111	Audit Add Role Event
115	Audit Backup/Restore Event
116	Audit DBCC Event
117	Audit Change Audit Event
152	Audit Change Database Owner
153	Audit Schema Object Take Ownership Event
155	FT:Crawl Started
156	FT:Crawl Stopped
164	Object:Altered
167	Database Mirroring State Change
175	Audit Server Alter Trace Event
218	Plan Guide Unsuccessful

As we can see there are various interesting events. Object:Altered, Object:Created and Object:Deleted can help us in identifying who dropped, altered or created any object. Once we get event class ID for the event of interest, we can use below query and replace the Event class ID.

-- read all available traces.

DECLARE @current VARCHAR(500);

DECLARE @start VARCHAR(500);

DECLARE @indx INT;

SELECT @current = path

FROM sys.traces

WHERE is_default = 1;

SET @current = REVERSE(@current)

SELECT @indx = PATINDEX('%\%', @current)

SET @current = REVERSE(@current)

SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHNAGE FILER AS NEEDED

SELECT *

FROM::fn_trace_gettable(@start, DEFAULT)

WHERE EventClass IN (92,93) -- growth event

ORDER BY StartTime DESC

In above, I have used EventClass 92 and 93 to track database auto-growth events. Here is the query to find who dropped / created or altered object in database or database itself.

-- read all available traces.

DECLARE @current VARCHAR(500);

DECLARE @start VARCHAR(500);

DECLARE @indx INT;

SELECT @current = path

FROM sys.traces

WHERE is_default = 1;

SET @current = REVERSE(@current)

SELECT @indx = PATINDEX('%\%', @current)

SET @current = REVERSE(@current)

SET @start = LEFT(@current, LEN(@current) - @indx) + '\log.trc';

-- CHNAGE FILER AS NEEDED

SELECT CASE EventClass

WHEN 46 THEN 'Object:Created'

WHEN 47 THEN 'Object:Deleted'

WHEN 164 THEN 'Object:Altered'

END, DatabaseName, ObjectName, HostName, ApplicationName, LoginName, StartTime

FROM::fn_trace_gettable(@start, DEFAULT)

WHERE EventClass IN (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

ORDER BY StartTime DESC

Have you ever heard of someone being fired based on such auditing?

Google Translator

Download source - 285.8 KB

Note:
GoogleTranslator was originally written when screen scraping was the only way to access Google's online translation tools. As has been rightly pointed out in this article's forum, a more modern approach would be to use Google's AJAX APIs. See this link for more information. This screen scraper version of GoogleTranslator continues to be maintained at the request of CP readers.

This latest version of GoogleTranslator utilizes Google Translate's AJAX APIs to translate text and retrieves the translation by parsing the returned JSON content. Thanks to CPians @Member 9899010 and @bgsjust for pointing me to these APIs. The latest version of the code also includes the ability to speak the translation from the demo app. Because Google limits the speech to common words in a few languages, don't be surprised if the demo plays dumb when you try to speak your translated text!

What is it?

GoogleTranslator is an object that allows you to translate text using the power of Google's online language tools. The demo app also allows you to easily perform a reverse translation. The app can be used as a poor man's resource translator for simple phrases, but you'd be wise to confirm the translation with a native speaker before using the results.

How do I use it?

You use GoogleTranslator by constructing it and calling its Translate() method.

Hide Copy Code

 
    using RavSoft.GoogleTranslator;
    
    Translator t = new GoogleTranslator();
    string translation = t.Translate ("Hello, how are you?", "English", "French");
    Console.WriteLine (translation);
    Console.WriteLine ("Translated in " + t.TranslationTime.TotalMilliseconds + " mSec");
    Console.WriteLine ("Translated speech = " + t.TranslationSpeechUrl);

How it works

GoogleTranslator works by directly invoking Google's translation API called by its online translation form and parsing the results.

Hide Shrink

Copy Code

 
    // Initialize
    this.Error = null;
    this.TranslationSpeechUrl = null;
    this.TranslationTime = TimeSpan.Zero;
    DateTime tmStart = DateTime.Now;
    string translation = string.Empty;

    try {
        // Download translation
        string url = string.Format ("https://translate.google.com/translate_a/single?client=t&sl={0}" +
                                    "&tl={1}&hl=en&dt=bd&dt=ex&dt=ld&dt=md&dt=qc&dt=rw&dt=rm&dt=ss&dt=t" +
                                    "&dt=at&ie=UTF-8&oe=UTF-8&source=btn&ssel=0&tsel=0&kc=0&q={2}",
                                    Translator.LanguageEnumToIdentifier (sourceLanguage),
                                    Translator.LanguageEnumToIdentifier (targetLanguage),
                                    HttpUtility.UrlEncode (sourceText));
        string outputFile = Path.GetTempFileName();
        using (WebClient wc = new WebClient ()) {
            wc.Headers.Add ("user-agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 " +
                                          "(KHTML, like Gecko) Chrome/41.0.2228.0 Safari/537.36");
            wc.DownloadFile(url, outputFile);
        }

        // Get translated text
        if (File.Exists (outputFile)) {

            // Get phrase collection
            string text = File.ReadAllText(outputFile);
            int index = text.IndexOf (string.Format(",,\"{0}\"", Translator.LanguageEnumToIdentifier (sourceLanguage)));
            if (index == -1) {
                // Translation of single word
                int startQuote = text.IndexOf('\"');
                if (startQuote != -1) {
                    int endQuote = text.IndexOf('\"', startQuote + 1);
                    if (endQuote != -1) {
                        translation = text.Substring(startQuote + 1, endQuote - startQuote - 1);
                    }
                }
            }
            else {
                // Translation of phrase
                text = text.Substring(0, index);
                text = text.Replace("],[", ",");
                text = text.Replace("]", string.Empty);
                text = text.Replace("[", string.Empty);
                text = text.Replace("\",\"", "\"");
            }

            // Get translated phrases
            string[] phrases = text.Split (new[] { '\"' }, StringSplitOptions.RemoveEmptyEntries);
            for (int i=0; (i < phrases.Count()); i += 2) {
                string translatedPhrase = phrases[i];
                if (translatedPhrase.StartsWith(",,")) {
                    i--;
                    continue;
                }
                translation += translatedPhrase + "  ";
            }

            // Fix up translation
            translation = translation.Trim();
            translation = translation.Replace(" ?", "?");
            translation = translation.Replace(" !", "!");
            translation = translation.Replace(" ,", ",");
            translation = translation.Replace(" .", ".");
            translation = translation.Replace(" ;", ";");

            // And translation speech URL
            this.TranslationSpeechUrl = string.Format ("https://translate.google.com/translate_tts?ie=UTF-8"+
                                                       "&q={0}&tl={1}&total=1&idx=0&textlen={2}&client=t",
                                                       HttpUtility.UrlEncode (translation),
                                                       Translator.LanguageEnumToIdentifier (targetLanguage),
                                                       translation.Length);
        }
    }
    catch (Exception ex) {
        this.Error = ex;
    }

    // Return result
    this.TranslationTime = DateTime.Now - tmStart;
    return translation;