Wednesday, 6 January 2016

Easy SQL Server Tool Using C#

Reader Level:

Article

Easy SQL Server Tool Using C#

By Syed Shanu on Jan 04, 2016

In this article we will see how to create an easy SQL Server Tool.

Download Files:

shanuEasySQLTool.zip

Download Aspose, it really helps

Introduction

Kindly view my YouTube Video Link to learn about my Easy SQL SERVER Tool Using C#.

My previous article explained how to design a form at runtime,

Easy Form Design at Run Time C# Windows Forms

In this article we will see how to create an Easy SQL Server Tool.

Easy SQL Server Tool will allow users to create SQL Server Database, Delete Database, Load all Database Names from a SQL selected Server, Create a New Table, Insert records to Table, Select Records from Table and bind the result to grid, Export the selected result to CSV file format, Delete Table, Delete ALL Records from a table without writing any SQL Script.
We have used SQL Server Management Objects (SMO) to programmatically create SQL Server Database, Table, etc.
What is SQL Server Management Objects (SMO)?
SQL Server Management Objects (SMO) is a collection of objects to manage SQL Server programmatically. Using SMO objects we can programmatically connect to SQL server, Get collections of all Database names, Create new Database, Backup Database, Restore Database, Create Table, Get collections of Table Name for a given Database, Get all Column details of a given Table, etc.
Reference website: To learn more detail about SQL Server SMO kindly view all these links,

Features in Shanu Easy SQL Server Tool (SESST)

Connect to SQL Server.
Table Details
SQL Script Tab (Create Table tab / Insert and Delete records tab / Select Query tab).
Output Window

Here we will see details of each part,

Step 1: Connect to SQL Server,

SQL Server Connect We can connect to any SQL Server within our network. We need to give SQL Server Name or IP Address, SQL Server Login ID and password to connect to SQL Server.
Create New Database
Check that the user entered Database name exist in SQL Server or not. If it exists, then display the message to user. If there is no database with user entered name then new Database will be created.

Load Database
Load all the Database Name to combobox for creating Table.
Delete Database
User can delete the selected Database from SQL Server. Before deleting confirmation box will be displayed to user to delete or not. If user click on ok then the selected database will be deleted.

Database Backup Backup the selected Database to any folder as you have selected.

Database Restore User can restore the database by selecting “.bak” file from the selected folder.

Step 2: Table Details

Load Table:

User need to first select the Database to list all the Table Names. After selecting the database and by clicking on the Load Table Names all the Table Names to the Combobox will be loaded. Delete Table
User can delete the selected Table from SQL Server. Before deleting confirmation box will be displayed for user to delete or not. If user click on ok then the selected Table will be deleted.

Step 3: SQL Script Tab

Here we will be having 3 tab, first tab is for creating Table, second tab is for Insert and Delete Records and the last third tab is for selecting query. We will see all three tab details here.
Create Table Tab

Here user can create their own table without writing SQL Query. Firstly, user need to select the Database where the table needs to be created. After selecting the Database user can enter their SQL Table Name in the textbox. After entering the table name user need to add columns for that Table. Without adding columns the Table cannot be created. Adding Columns

User can add columns for creating new Table. All these columns will be added locally to the grid for finally creating a Table. User can add any number of columns, delete the selected column and also delete all columns by clicking on Clear Columns. Adding and deleting columns do not deal with database so this will be as temp place to add columns for creating Table. After adding all columns for the Table user can click on Create Table button to create a new Table with given column details. Datatype:
Here for now only 3 datatypes have been used.

Int (Numbers)
varchar (for text)
nVarchar (for Numbers and text)

User can also add each column data size. For integer the size will not be used, but for Varchar and nVarchar Datatype user can add the column data size. User can also set Max size for both Varchar and nVarchar type by selecting the Max Size checkbox.
For example, we can see the following image. Here we have added 4 columns to the grid and adding one more new column we can see each column Datatype and Size from the grid. After adding all column, we click the Create Table Button. We can see the confirmation message as Table was created and also we can see our new Table has been added in the Table List Combobox.

Insert and Delete Records Tab We will be using the recently created test Table for inserting demo.

Here we can see in the above image we have selected the Table “test” from Table List. To insert record to selected Table we need to click on the load table Details to insert button. By clicking this we will be displaying all the column name with textbox. Depending on the Datatype of each column we will be adding textbox with maxlength. If the column datatype is Integer then we will be adding Numeric textbox for that column' by this user can enter only numbers to that textbox. For Varchar and nVarchar we will check for each Column Size if its max then we set the textbox maxlength as 200; by this user can enter only 200 characters. If size is set for column then we will be setting the column size for textbox maxlength. Insert:

After loading all the column details with TextBox user can enter the value to be inserted for each column. Once user entered the value click on the Insert into Selected Table button for inserting a new record. Same like this user can enter any number of records for the selected table. If you want to add more functionality from the code you can add your functionality, for example, like checking duplicate value before insert and etc. Delete All Records: User can delete all records of the selected table. Here in this sample we are not checking any condition for deleting the records. If you need to add more functionality you can change from the code and add your own functions, for example like Delete records of a table based on condition and etc.
Select Query Tab

For selecting the records from a Database and display the result user need to first select the Database and then select the Table from the Table List to create a SQL Select query.

User can select the details using three features,

1) All Columns: If All Columns checkbox is checked then all column details result will be displayed to the output window grid. (This is same like * in select query, for example select * from tablename). We can see from the above image The All Column Check box will be loaded by default. After selecting the table and when user clicks on Run Select Query all column details will be loaded in the following output window grid.

2) User selected Columns: If user want to display only particular columns of the table then they can click on load Table Columns to Select button to display all the column name of the tables to CheckedListbox. Here user can select the columns they need and by default checked all the Column Names. User can uncheck the columns which are not needed and also uncheck the All Columns Checkbox to display only selected columns as a result.

User can Write SQL Select Query: User can also write their SQL Select query to display the output.

For example, if user want to write their own SQL query to join 3 table and display the result. To write SQL Select query user need to check the SQL Select Query checkbox and after writing select query user can click on Run Select Query button to see the result. In the above image we can see, we wrote a sample SQL Join query to join the three table and by clicking the Run Select Query the result has been bind in the grid.

SQL Injection Checking in user entered select query:

We have also checked for the SQL Injection before executing user entered SQL Select query. We have created an array list to add all SQL injection string and we will be checking any of the array word is matching with user entered select query. For example, we can see the above image since after select query we have entered drop query. But when we click on Run Select Query button we display the message as drop is not accepted in select query.

Here is the list of SQL Injection string I am checking if you need you can add or remove as per your requirement.

string[] sqlInjectionArray = { "create", "drop", "delete", "insert", "update", "truncate","grant ","print","sp_executesql ,"objects","declare","table","into",
"sqlcancel","sqlsetprop","sqlexec","sqlcommit","revoke","rollback","sqlrollback","values","sqldisconnect","sqlconnect","user","system_user","use","schema_name","schemata","information_schema","dbo","guest","db_owner","db_","table","@@","Users","execute","sysname","sp_who","sysobjects","sp_","sysprocesses ","master","sys","db_","is_","exec", "end", "xp_","; --", "/*", "*/", "alter", "begin", "cursor", "kill","--" ,"tabname","or","sys"};

Save Select Query: User can also save the Select Query as text file to selected folder for using the select query later.

Open saved Select Query: User can also open the saved Select Query to execute the select script.

Export the Result to CSV file Format: User can also export the selected result to CSV format. Here in the following image we can see the result has been exported as CSV file.

Prerequisites

Visual Studio 2015: You can download it from here.

SQL SERVER SMO Reference: For working with SQL Server SMO we need to add 4 important reference to our project,

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.SqlEnum.dll

You can find the reference from this path (Here we have used SQL Server 2014),
C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\

Add all these 4 Assembly reference to your project,

Code part The main aim was to create a simple and easy to use Easy SQL Tool. All the functionalities has been added in one main form with Tab. We have created 2 class:

smoSQLServerClass: In this class we have created function to Connect, Disconnect to SQL Server, Create Database, Delete Database, Backup Database, Write all the Exception to Log text file to executable folder, Create Table etc.
sqlBizClass: In this class we perform all business logic for our Main form for example like creating Dynamic query, Creating Dynamic Controls and bind to panel, Bind all Table names returned from SMOSQLServerClass to Combobox controls ,Check SQL injection for Select query and return the result as true or false etc.

Here we will see some important code part used in the Easy SQL Server Tool. All the code part has been well-commented, so the user can easily understand the code.

Connect to SQL Server
In this article I have mentioned for using SQL Server SMO objects we need to add 4 important Assembly reference to our project. Using SMO ServerConnection we can connect to SQL server. We need to set the SQL Server name to ServerInstance and set SQL Server login and password. After connecting we check for Connection is open and return the Boolean value to our main function to display the appropriate message to user.

public bool SqlServerConnect()
{
try
{
servConn = new ServerConnection();
servConn.ServerInstance = serverName;
servConn.LoginSecure = false;
servConn.Login = LoginID;
servConn.Password = password;
servConn.Connect();
if(servConn.IsOpen)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return false;
}

Write Exception Message to Log File: We will be writing all exception error message to Log file with Date and Time.

public void writeLogMessage(String logMessage)
{
string path = Application.StartupPath + @"\LogFile.txt";
logMessage = logMessage + " - on " + DateTime.Now.ToString();
if (!File.Exists(path))
{
using (StreamWriter tw = File.CreateText(path))
{
tw.WriteLine(logMessage);
tw.Close();
}
}
else
{
StreamWriter tr = new StreamWriter(path);
tr.WriteLine(logMessage);
tr.Close();
}
}

Load Database Name In Load Database Name button click event, we will pass the comboBox control to the Biz class method. In biz class method we will call the SMO Class to get all the database name and add all the database name to combobox. Here in this method I will check for Master Database and will not add the name for security reason, for example user can delete the database from our main form. Similarly you can change the code to restrict any database that show it in our main form.

public void loaddbNames(ComboBox cbo)
{
//return objSQL.loaddbNames();
DatabaseCollection dbnamesCol = objSQL.loaddbNames();
cbo.Items.Clear();
cbo.Items.Add("");
if (dbnamesCol != null)
{
string dbnames = "";
int ival = 0;
foreach (Database db in dbnamesCol)
{
if (db.Name != "master")
{
cbo.Items.Add(db.Name);
} }
}
cbo.SelectedIndex = 0;
}

In SMO Class we will be loading all the Database name for a given SQL Server and return as DatabaseCollection to calling class.

public DatabaseCollection loaddbNames()
{
DatabaseCollection dbNames = null;
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
dbNames = srv.Databases;
SqlServerDisconnect();
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return dbNames;
}

Create Database In this function first we check for Database already exist with the user entered name. If database with same name do not exist we will create a new database in our SQL Server. If Database already exist in the SQL Server, then return the message as Database exist.

public string createourDatabase(string DatabaseName)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
if (database == null)
{
database = new Database(srv, DatabaseName);
database.Create();
database.Refresh();
SqlServerDisconnect();
return "Database Created Successfully !";
}
else
{
SqlServerDisconnect();
return "Database Already Exist";
}
}
else
{
return "Enter valid SQL Connection Details";
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return "Sorry Error While creating DB";
}

Create Table In Create button click we will pass the user selected Database Name, User entered Table Name with Column Details as DataTable to SMO Class. In this function we will check for table already exist or not; if exist return false message to user and if not, then we will create a new Table with column details.

public string createTable(string DatabaseName, string TableName,DataTable dtColumns)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
if (database != null)
{
bool tableExists = database.Tables.Contains(TableName);
if (tableExists)
{
SqlServerDisconnect();
return "Table Already Exist.kindly Enter Different Table Name";
}
else
{
Table tbl = new Table(database, TableName);
foreach (DataRow dr in dtColumns.Rows)
{
string columnName = dr["ColumName"].ToString();
string DataType = dr["DataType"].ToString();
string dataSize = dr["Size"].ToString();
Microsoft.SqlServer.Management.Smo.Column columntoAdd =null;
switch (DataType)
{
case "Varchar":
if(dataSize=="max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarChar(Convert.ToInt32(dataSize)));
}
break;
case "Int":
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.Int);
break;
case "nVarchar":
if (dataSize == "max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarChar(Convert.ToInt32(dataSize)));
}
break;
}
if(columntoAdd!=null)
{
tbl.Columns.Add(columntoAdd);
}
}
tbl.Create();
SqlServerDisconnect();
return "Table Created Successfully !";
}
}
}
else
{
return "Enter valid SQL Connection Details";
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return "Sorry Error While Creating Table";
}

Load Column Details for Insert In Load Column details button click we will pass the Panel Control to our biz class to get all the column details of selected table and bind a text box with column name to panel. From our SMO Class we will get all the Column details of table and return as ColumnCollection. In biz class using foreach we will add all column details like Name as Label control to display the Column Name and add a Textbox for user input. In this method we will check for column type and column size. If the column type is Integer then we will set the Textbox as Numeric Textbox. If the column type is Varchar or NVarchar we check for column length and assign the length as TextBox Maxlenght.

public void loadTableColumnDetails(Panel pnControls, string DataBaseName,string TableName)
{
ColumnCollection tableColumnDetail = objSQL.loadTableColumnDetails(DataBaseName, TableName);
pnControls.Controls.Clear();
if (tableColumnDetail != null)
{
string dbnames = "";
int lableHeight = 20;
int textboxHeight = 20;
int lablewidth = 100;
int lableXVal = 10;
int lableYVal = 10;
foreach (Column colName in tableColumnDetail)
{
string s = colName.Name;
Random rnd = new Random();
int randNumber = rnd.Next(1, 1000);
//to add Column name to display as caption
Label ctrl = new Label();
ctrl.Location = new Point(lableXVal , lableYVal+6);
ctrl.Size = new Size(lablewidth , lableHeight);
ctrl.Name = "lbl_" + randNumber; ;
ctrl.Font = new System.Drawing.Font("NativePrinterFontA", 7F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrl.Text = colName.Name;
pnControls.Controls.Add(ctrl);
//to add textbox for user enter insert text
TextBox ctrltxt = new TextBox();
ctrltxt.Location = new Point(lableXVal+110, lableYVal);
ctrltxt.Size = new Size(lablewidth+40, lableHeight);
ctrltxt.Name = "txt_" + randNumber;
ctrltxt.Font = new System.Drawing.Font("NativePrinterFontA", 8F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrltxt.Text = "";
if (colName.DataType.Name== "int")
{
ctrltxt.MaxLength = 20;
ctrltxt.KeyPress += new KeyPressEventHandler(textBox_KeyPress);
}
else
{
if(colName.DataType.MaximumLength.ToString()!="-1")
{
ctrltxt.MaxLength = Convert.ToInt32(colName.DataType.MaximumLength.ToString());
}
else
{
ctrltxt.MaxLength =100;
}
}
pnControls.Controls.Add(ctrltxt);
//to add Column datatype as hidden field
Label ctrllbl = new Label();
ctrllbl.Location = new Point(lableXVal + 112, lableYVal + 6);
ctrllbl.Size = new Size(1, 1);
ctrllbl.Name = "_lblDT_" + randNumber; ;
ctrllbl.Font = new System.Drawing.Font("NativePrinterFontA", 7F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrllbl.Text = colName.DataType.Name;
ctrllbl.Visible = false;
pnControls.Controls.Add(ctrllbl);
if (lableXVal + 360 < pnControls.Width-110)
{
lableXVal = lableXVal + 270;
}
else
{
lableXVal = 10;
lableYVal = lableYVal + 40;
}
}
}
}
//for numeric textbox validation
private void textBox_KeyPress(object sender, KeyPressEventArgs e)
{
e.Handled = !char.IsDigit(e.KeyChar) && !char.IsControl(e.KeyChar);
}

In SMO class loadTableColumnDetails method we will get all the column details for given database and table name return the column details as ColumnCollection top biz class for adding dynamic controls for user input to insert records.

public ColumnCollection loadTableColumnDetails(string DatabaseName,string TableName)
{
ColumnCollection columnDetails = null;
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database db = srv.Databases[DatabaseName];
bool tableExists = db.Tables.Contains(TableName);
if (tableExists)
{
foreach (Table table in db.Tables)
{
if (table.Name == TableName)
{
columnDetails = table.Columns;
break;
}
}
}
SqlServerDisconnect();
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return columnDetails;
}

Insert Records In Insert button click we pass the user added insert details panel Control with Database Name and Table Name to biz Class. In biz class we will create a dynamic Insert query with adding all the column names and add all the insert values as parameter and pass the SqlCommand to SMO Class insertQuery method for inserting new record to the given Table.

public string saveTableInsertQuery(Panel pnControls, string DataBaseName, string TableName)
{
string result = "";
StringBuilder sqlQuery = new StringBuilder("INSERT INTO " + TableName );
StringBuilder Insert = new StringBuilder(" (");
StringBuilder values = new StringBuilder("VALUES (");
SortedDictionary<string, string> sd = new SortedDictionary<string, string>();
string columnName = "";
string colvalue = "";
string dataType = "";
int iCount = 0;
SqlCommand command = new SqlCommand();
foreach (Control p in pnControls.Controls)
{
if (p.Name.ToString().Substring(0, 4) == "lbl_")
{
columnName = p.Text;
}
else if (p.Name.ToString().Substring(0, 4) == "txt_")
{
colvalue = p.Text;
}
else if (p.Name.ToString().Substring(0, 4) == "_lbl")
{
Insert.Append(columnName);
Insert.Append(", ");
sd.Add(columnName, colvalue);
values.Append("@" + columnName);
values.Append(", ");
if (p.Text == "int")
{
command.Parameters.Add("@" + columnName, SqlDbType.Int).Value = colvalue;
}
else if (p.Text == "varchar")
{
command.Parameters.Add("@" + columnName, SqlDbType.VarChar).Value = colvalue;
}
else if (p.Text == "nvarchar")
{
command.Parameters.Add("@" + columnName, SqlDbType.NVarChar).Value = colvalue;
}
}
}
string sqlresult = Insert.ToString().Remove(Insert.Length - 2) + ") ";
sqlQuery.Append(sqlresult);
string valueresult = values.ToString().Remove(values.Length - 2) + ") ";
sqlQuery.Append(valueresult);
sqlQuery.Append(";");
command.CommandText = sqlQuery.ToString();
command.CommandType = CommandType.Text;
return objSQL.insertQuery(DataBaseName, sqlQuery.ToString(), command);
}

Select Query In Select button click we check for select query type, if SQL Select Query check box is not checked then we will pass all the column details to biz class selectRecordsfromTableQuery Method to create a dynamic SQL Select query. If user selected all Columns then we will be using “select * from tablename”. If user checked only few columns to display then we will create a dynamic query and add all user selected columns and after that return the result as DataTable to bind in grid.

public DataTable selectRecordsfromTableQuery(bool isAllColumns, CheckedListBox chkListBoxCols, string DataBaseName, string TableName)
{
string result = "";
StringBuilder sqlQuery = new StringBuilder("Select * FROM " + TableName);
string sqlresult = sqlQuery.ToString();
if (!isAllColumns)
{
sqlQuery = new StringBuilder("Select " );
foreach (object itemChecked in chkListBoxCols.CheckedItems)
{
string colsName = itemChecked.ToString();
sqlQuery.Append(colsName+", ");
}
sqlresult = sqlQuery.ToString().Remove(sqlQuery.Length - 2) + " FROM " + TableName;
}
SqlCommand command = new SqlCommand();
command.CommandText = sqlresult;
command.CommandType = CommandType.Text;
return objSQL.selectRecordsfromTableQuery(DataBaseName, command);
}

Points of Interest Hope you all liked this article. The main aim of creating this tool is for easy learning of SQL Server. Without writing SQL Script now members can connect to SQL Server, create Database, Table, Insert and select records. There are some limitations in this tool like we can add only 3 datatype for now, etc. There is no limitation for our expectation, now the code and Tool is in your hand you can add any number of functionality as per you need.

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