Ashish Gupta

This user hasn't shared any biographical information

Homepage: http://ashishrocks.net

Maximum connections exceeded!!!! – How to know who are logged on to a server and not letting you in?

We all hate to see this message when we want to login to the server :-
“The terminal server has exceeded the maximum number of allowed connections.”

image

All you need is the PsLoggedOn utility from sysInternals (now owned by Microsoft). Another reason we should all love Mark Russinovich and SysInternal tools.
Just follow the steps :-

  1. Download PSTools from here.
  2. Extract the zip file to a folder.
  3. Open command prompt and navigate to the extracted folder.
  4. Run the following command. Keep the \\ and replace the MachineName with the fully qualified name of the machine where you want to see who are currently logged on.

5.   You will see who are logged on to that machine.

psloggedon \\MachineName 

This saves you sending emails to a group to find out who are logged into the server. Rather – because you know who are logged in, you would ping/email those specific users, asking them to log off.

,

Leave a comment

2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 9,200 times in 2013. If it were a concert at Sydney Opera House, it would take about 3 sold-out performances for that many people to see it.

Click here to see the complete report.

,

Leave a comment

SQL Server–Change Data Capture [CDC] for “Watching” database tables for DML operations

In scenarios when we want to watch tables for any inserts, updates and deletes, we implement triggers. Triggers – not only needs database development effort and needs to be correctly written, It places locks on tables and slows things down.

In SQL Server 2008, Microsoft introduced a new capability called “Change Data Capture” [CDC] to watch and track changes on tables for any inserts, updates and deletes. This requires almost no database development effort and is more efficient than triggers. A very nice thing about CDC is that It makes use of transaction log which has all the data about any changes made to the database alreadySo why reinvent the wheel?

Basically, first you enable CDC on the database. Then enable CDC on the table (e.g. Account) you want to watch which will automatically create the a change tracking table (“Account_CT”) for the watched table. Any changes in your watched table (e.g. Account) will get recorded in the change tracking table (e.g Account_CT) and you can use the tracking table for all your queries.

“Talk is cheap. Show me the code.” – Linus Torvalds

1. Preparing sample data

/*Create sample database*/
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N’ChangeDataCaptureTest’)
DROP DATABASE [ChangeDataCaptureTest]
GO
/****** Object:  Database [ChangeDataCaptureTest]    Script Date: 10/23/2013 17:25:04 ******/
CREATE DATABASE [ChangeDataCaptureTest]

/*Create the sample table – This is the table we will be watching for inserts, updates and deletes*/
USE ChangeDataCaptureTest
GO
CREATE TABLE [Account]
(
    Id INT Primary Key IDENTITY(1,1),
    [Description] VARCHAR(500),
    [Active] BIT
)
GO

2. Enable CDC on the database

This looks scary, but all it is doing it is checking an already existing flag in sys.databases table in the master database. If you execute the following, you can see in which database CDC is currently enabled. In the below example, It is not enabled in any of the database:-
USE master
GO
SELECT [name], database_id, is_cdc_enabled 
FROM sys.databases      
GO    

image

Now we can enable CDC on our sample database.
USE ChangeDataCaptureTest
GO
EXEC sys.sp_cdc_enable_db
GO

If you execute the following, you can see the “is_cdc_enabled” column for our sample database is enabled:-
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

image

3. Enable CDC on the table you want to watch for Insert/Update/Delete

Now we need to enable the CDC on the table we want to watch.

USE ChangeDataCaptureTest
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’Account‘,
@role_name     = NULL
GO

When we execute the above, we see two Sql jobs created and started automatically.

image

image

cdc.ChangeDataCaptureTest_capture
This job watches the table “Accounts” and put changes in the tracking table Account_CT
cdc.ChangeDataCaptureTest_cleanup –
This job cleans up the tracking table Account_CT and can be scheduled as per the requirement.

At this point If we query the sys.tables :-
USE ChangeDataCaptureTest
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO

image

4. Testing the results

Let us insert/update/delete data in the watched table [Account] and see the tracked changes in the [Account_CT] table.

Insert Operation
USE ChangeDataCaptureTest
GO
INSERT INTO Account
VALUES (‘Test’, 1)

Select to verify results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image

Value for “__$operation” column is 2 indicates “Insert”. We can see the values in the columns “Description” and “Active”.

Update operation
USE ChangeDataCaptureTest
GO
UPDATE Account
SET Active= 0
WHERE Id = 1
Select to verify the results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image
Value for “__$operation” column :-
3 = update (captured column values are those before the update operation).
4 = update (captured column values are those after the update operation)

DELETE Operations
USE ChangeDataCaptureTest
GO
DELETE Account
WHERE id = 1
Select to verify results
USE ChangeDataCaptureTest
GO
SELECT * FROM Account
GO
SELECT * FROM cdc.dbo_Account_CT
GO

image

Value for “__$operation” column is 2 indicates “Delete”.

Time based search on table changes
Need to see changes in a table based on given timestamp? No problem. When we enabled the table for change tracking, It also added a system table named “cdc.lsn_time_mapping” which has all the transaction with the timestamp.
Just join the change tracking table (Account_CT) with the system table “cdc.lsn_time_mapping” table on transaction id (start_lsn) and have the transaction filter criteria on the same.

image

USE ChangeDataCaptureTest
GO
SELECT B.*, A.tran_begin_time, A.tran_end_time 
FROM cdc.lsn_time_mapping A
INNER JOIN cdc.dbo_Account_CT B
ON A.start_lsn = B.__$start_lsn

image

Note :- In CDC, there’s no way to trace the user who causes each transaction.

, , ,

Leave a comment

SqlDbType.Structured [another gem in ADO.NET] and Bulk insert using Table Valued Parameters

Use case
You want to insert multiple rows (say from 2 to 999 rows) from your .NET application to a database table.
You are using .NET Framework 3.5 or above with SQL Server 2008 or above.

Approaches
Insert one row at a time
As you can imagine, this would have a performance hit because of too many connections getting opened/closed – more chatty
Use a CSV of rows
This approach is chunkier than the above approach. Overall, below is the approach:-
– Create a comma separated string of rows from the application
– Send CSV to a stored procedure from application
– The stored procedure would make use of a UDF to parse the CSV into a table variable
– The stored procedure would insert data from the table variable to the actual table
Use SqlDbType.Structured
The above approach solves the problem of the application being too chatty to the database. However, It’s not elegant and involves too much of “manual” parsing of string.

In .NET Framework 3.5 and onwards, SqlCommand can make use of another parameter type named SqlDbType.Structured which enables a .NET application to send a DataTable (yes, a “System.Data.DataTable” object) directly to the stored procedure which can be directly used as a table inside the stored procedure as If It was a table in the database.

In the below example we will send a DataTable of email addresses from .NET application to a database stored procedure which would insert data directly from this table to the actual table.

Steps
Database changes:-
/*Create a user-defined table type which will hold the contents of the DataTable passed from the application.
This will be used as a Table Valued Parameter.
*/

CREATE TYPE [dbo].[EmailAddressList] AS TABLE
(
[EmailAddress] [NVARCHAR](100) NULL
);

/*Create the actual table to which we will insert data from the DataTable*/
CREATE TABLE EmailAddressDetails
(
EmailAddress NVARCHAR(100),
CreatedOn DateTime DEFAULT GetDate()
)

/*Create the stored procedure which will be called from the application*/
CREATE PROCEDURE EmailAddresses_InsertBatch
@EmailAddressBatch [EmailAddressList] READONLY
AS
BEGIN
INSERT INTO EmailAddressDetails (EmailAddress)
SELECT E.EmailAddress FROM @EmailAddressBatch E
END

Application changes
//Function to create a DataTable with dummy email addresses
//The DataTable created here should match the schema of the User defined table type created above.

private DataTable CreateEmailAddressDataTable()
{
DataTable emailAddressDT = new DataTable();
emailAddressDT.Columns.Add(“EmailAddress”, typeof(string));
int emailAddrressCount = 100;
for (int i = 0; i < emailAddrressCount; i++)
{
DataRow row = emailAddressDT.NewRow();
row[“EmailAddress”] = i.ToString() + “.something@xyz.com”;
emailAddressDT.Rows.Add(row);
}
return emailAddressDT;
}

//Function to call the stored procedure with DataTable
private void AddEmailAddressToDb()
{
DataTable dataTable = CreateEmailAddressDataTable();
string connectionString = “Server=YourServerName;Database=YourDatabaseName;UserId=ashish;Password=ashish;”;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = “EmailAddresses_InsertBatch”;
command.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter(“@EmailAddressBatch”, SqlDbType.Structured);
                   param.TypeName = “dbo.EmailAddressList”;
param.Value = dataTable;
command.Parameters.Add(param);
command.ExecuteNonQuery();
}
}
}

The above line highlighted in green is the gem in ADO.NET. 🙂 When we send DataTable the stored procedure, It populates the user defined table type which we can directly use in the stored procedure – inserting from it to the actual table in this case. No parsing of CSV in UDF – takes away big pain when you have a complex structure.
2018-07-04 00_35_19-Edit Post ‹ Ashish Gupta — WordPress.com
Note:– Microsoft recommends this to be used when you are inserting less than 1000 rows. For more rows, consider using SqlBulkCopy.

,

1 Comment

From “Yes”SQL to NoSQL with Raven Db

What is “Yes”Sql?

Question :- Do you know what we can use to store all employees data?
Answer :- Yes! I would use a RDBMS (Relational Database Management System) software like Sql Server,
MySql, Oracle etc. I would store all your employee data in different database tables with relationships between them.

Question :- Do you know how can we can access and manage employees data?
Answer :- Yes! I would use a query language named SQL (Structured Query Language) to query and manage data.

If you have been also answering “Yes” to the above questions, you have been doing “Yes”SQL. I have been doing the “Yes”SQL for quite some time and feeling like the frog in its well of RBMS’s  without even realizing the outside world has become better and should be explored.

image
source :- http://bit.ly/VVxmMU

What is NoSql

Stop being the “frog in the well”!! Take the red pill and find out how deep the rabbit hole goes.image

What is NoSQL

As we have been doing “Yes”SQL for quite some time, we will first take a look at what we have been doing for
storing and retrieving data.

Following is very simplified employee database tables.

image

Add some data:-
BEGIN TRY  
    BEGIN TRAN  
    INSERT INTO Department(Id, Name) VALUES(1, ‘Accounts’)  
    INSERT INTO Department(Id, Name) VALUES(2, ‘Engineering’)  
    INSERT INTO Employee (ID, Name, DepartmentID) VALUES (1, ‘Ashish’, 1)  
    INSERT INTO Employee (ID, Name, DepartmentID) VALUES (2, ‘John’, 2) 
    COMMIT 
  END TRY 
  BEGIN CATCH 
    ROLLBACK 
  END CATCH

If we want to retrieve name of the employee and the department in which he/she works using the employee id,
need to join the two tables :-
SELECT E.Name AS EmployeeName, D.Name AS DepartmentName FROM Employee E
INNER JOIN Department D ON  E.DepartmentID = D.Id
WHERE E.Id = 1

Why we needed to join the tables? because all the information for that particular employee is not stored at one table. This is the main characteristic of a RDBMS and this is first thing we need to get off our minds in NoSQL.
Now, there are different types of NoSQL databases – Raven DB, Mongo DB etc. However, in all of them, the basic element is same – No relationship!
In Raven Db, the all data for this employee would be stored in a “document”.
employees/1 
{  
          “Name”: “Ashish”,  
          “Department”:   
            { 
            “Id”: 1,  
            “Name”: “Accounts”  
            }  
}

As you see – all data (which includes department details as well) for employee with id “1” is stored in one
place – a “document”, in RavenDB terms.  This is similar to a “row” in the RDBMS except the fact that data is not distributed in different tables, rather all data is at one place – in a document. For each employee, there would be be one document.

More on this next.

NoSQL using RavenDB

What is Raven Db

Raven DB is a document database.  It has following characteristics :-

  • Non- Relational
  • All data is stored and represented in JSON
  • There is no schema
  • Transactional

Installation
http://hibernatingrhinos.com/builds/ravendb-stableimage

I downloaded the last stable build of Raven DB and extracted to my local “D:\Ashish\Research\RavenDB” folder. Look for /Server/RavenServer.exe.config and change the Anonymous access to “All”

image

Run the Start.cmd as an administrator.image

It should open a very nice looking management studio for Raven Db (performing function “similar” to SQL server management studio). It will ask you to create a new database as you don’t have any. Put a name for the database.image

The new database is created :-image

 

Client application to access and manage data in the Raven DB
Adding the RavenDB client to the client application via Nuget Package managerimage

Below is the client code which when executed creates one document per entity (in this example, a company). Notice that a DocumentStore is similar to a connection. We can use the same connection to create multiple documents. However, each document is bound to a session and that’s why It needs to be disposed before creating a new one. Also notice the RabenDb displays the documents created in the server admin browser.

image

, ,

Leave a comment

Connecting to MySql database and fetching records using JDBC connection in TIBCO BusinessWorks

This looks as naïve as connecting to a database from .NET using standard connection class. But, hey, that was interesting/exciting as well first time when you did that. In this example, we will connect to a MySql database from TIBCO Businessworks, fetch records and write the first record to a text file – as simple as that.

Create an empty project in TIBCO BusinessWorks designer:-
clip_image002

Save the project with the directory path :-
clip_image004
Your project pane would look something like this :-
clip_image006
Now, we need to add a JDBC connection. A JDBC Connection is a resource in TIBCO designer. Therefore for better organization, we create a “Resources” folder.

clip_image008

Add the JDBCConnection to the “Resources” folder as shown below.

clip_image010

JDBC Connection would be added as shown below and we need to select the driver and the connection string for the mysql database. Don’t forget the username and password. Click “Test Connection” to make sure the connection succeeds.

clip_image012

When you click “Test Connection”, you might see the below error :-

“BW-JDBC-100033 “Configuration Test Failed. Failed to find or load the JDBC driver. jdbc:mysql://localhost:3306/Research”

This really means BusinessWorks cannot see the MySql driver required for the connection. This driver is basically a .jar file [mysql-connector-java-5.1.23-bin.jar] which is located in the MySql installation directory. For my installation the directory is “C:\Program Files (x86)\MySQL\Connector J 5.1.23\mysql-connector-java-5.1.23-bin.jar”. All I needed to do is to copy that jar file to the TIBCO installation directory > Lib folder. For me, I needed to copy to “C:\tibco\bw\5.10\lib” directory. Save the project, close the designer and reopen the project. Now on the JDBC Connection, click “Test Connection”.

clip_image013

Voilà!!! Got connected this time. However, I am pretty sure there must be a better way (may be setting the classpath for the jar file) than copying the jar file itself.

Add a new folder named “Activities” to the root folder.

clip_image014

Add a “Process Definition” from the Palettes tab and double click on it to see the start and end points.

clip_image016

Add “JDBC Query” from the Palettes tab. In the configuration pane of the JDBC query, select the JDBC Connection as shown below :-

clip_image018

Add the Sql statement to get data from the mySql table. My sql statement was :-

SELECT ID, Name FROM Product

Writing the database query results to text file
Create a blank text file named “test.txt” in your project directory.

Add “Write File” from the Palettes tab and place it after the JDBC query. Connect them using the clip_image019 (“Create transition” button) as shown below.

clip_image021

In the “Input” tab of the “Write File”, enter the name of the blank text file we created earlier.

clip_image023

Now we need to set the contents of the file. The contents of the file would be the first record from the resultset returned by the JDBC query. Keep the cursor in the “textContent” textfield and click on the yellow pencil button.

clip_image025

XPath Formula Builder would open. In this, drag the Functions > String >Concat to the XPath Formula text field on the right.

clip_image027

Drag “Data > JDBC Query > resultSet > Record > Id” onto the <<string1>> and Drag “Data > JDBC Query > resultSet > Record > Name” onto the <<string2>>

clip_image029

Since we are getting only one record, change the following :-

concat($JDBC-Query/resultSet/Record/Id, $JDBC-Query/resultSet/Record/Name)

to this (index = 1 as XPath has starting index as 1):-

concat($JDBC-Query/resultSet/Record[1]/Id,”,”,$JDBC-Query/resultSet/Record[1]/Name)

Click “Apply” on the XPath Formula Builder and then on the Input pane of the “Write File”. Save the project. Run the project and you would see the file updated with the first record from the database.

,

Leave a comment

2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 11,000 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 18 years to get that many views.

Click here to see the complete report.

Leave a comment

Unit testing a static method which calls another static method using Moq

Recently I came across code like below in UI (code behind) which basically calls a static method on a repository :-

RandomPage.aspx.cs :-

protected void btnSave_Click(object sender, EventArgs e)
{
          Guid id = Guid.NewId();
          string name = txtName.Text;
          RandomRepository.RandomStaticMethod(id, name);
}

Now let us look at the “RandomStaticMethod()” method in the “RandomRepository”
class. The method just calls couple of another static methods in the “DataAccess” class.
RandomRepository.cs :-

/// <summary>
/// Repository for some random data access
/// </summary>
public class RandomRepository
{
     /// <summary>
     /// Name of the stored procedure to insert data in a table
     /// </summary>
     private const string SomeRandomTableInsertSP = "SomeRandomTable_Insert";

     /// <summary>
     /// The connection string
     /// </summary>
     private const string ConnectionString = "Random connection string";

     #region Static method
     /// <summary>
     /// A random static "Factory" method
     /// </summary>
     /// <param name="id">The value for the Id parameter.</param>
     /// <param name="name">The value for the Name parameter.</param>
     public static void RandomRepositoryMethod(Guid id, string name)
     {
         DataAccess.ExecuteNonQuery(
         ConnectionString,
         SomeRandomTableInsertSP,
         DataAccess.CreateParameter("@Id", SqlDbType.UniqueIdentifier, id),
         DataAccess.CreateParameter("@Name", SqlDbType.NVarChar, name));
     }
    #endregion  
}

DataAccess.cs:-

/// <summary>
/// Classes for the database operations
/// </summary>
public class DataAccess
{
    /// <summary>
    /// Creates the parameter.
    /// </summary>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="type">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    /// <returns>The SqlParameter</returns>
    public static SqlParameter CreateParameter(string name, SqlDbType type, object value)
    {
        return new SqlParameter { ParameterName = name, SqlDbType = type, Value = value };
    }

    /// <summary>
    /// Executes the query.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="storedProcedure">The stored procedure.</param>
    /// <param name="inParameters">The in parameters.</param>
    public static void ExecuteNonQuery(string connectionString, string storedProcedure, params DbParameter[] inParameters)
    {
        // Do some actual database operations here
    }
}

What do we need to test?
– RandomRepositoryMethod().
We need to test If It could call DataAccess.ExecuteNonQuery() with appropriate number/type/values of parameters.
Why? In this example, I have only two parameters. However, in real world, you might be using a lot more than that. I have seen about 20 parameters being passed and many time have made mistakes by copying pasting the same parameters and getting the error later. If you say you never made that mistake, you must be lying. Smile

Now, read on…

If you look at RandomRepositoryMethod() you see that the RandomRepository class is dependent on the DataAccess class which does the actual database operations. Since this is a unit test for RandomRepositoryMethod() and not for DataAccess and we don’t want to interact with the actual database, we need to “mock” the DataAccess class.

How to mock the DataAccess here?

  1. Add an interface “IDataAccess” which has the dependency methods – CreateParameter and ExecuteNonQuery.
  2. Explicitly implement the IDataAccess methods – CreateParameter() and ExecuteNonQuery() on the DataAccess class.
  3. The explicitly implemented interface methods – CreateParameter() and ExecuteNonQuery() should just call their static counter parts.
  4. Inject the dependency (DataAccess) to the dependent method.
  5. Write the unit test method using Moq.

IDataAccess interface :-

/// <summary>
/// An interface for DataAccess
/// </summary>
public interface IDataAccess
{
    /// <summary>
    /// Creates a SqlParameter with the given name, type, and value.
    /// </summary>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="type">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    /// <returns>Returns a SqlParameter created with the given arguments.</returns>
    SqlParameter CreateParameter(string name, SqlDbType type, object value);

    /// <summary>
    /// Executes the given stored procedure.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="storedProcedure">The stored procedure.</param>
    /// <param name="inParameters">The parameters to pass into the stored procedure.</param>
    void ExecuteNonQuery(string connectionString, string storedProcedure, params DbParameter[] inParameters);
}

Explicitly implementing the interface
Explicitly implemented methods are in line 35 and 47 which just call their static counterparts.

/// <summary>
/// Classes for the database operations
/// </summary>
public class DataAccess : IDataAccess
{
    /// <summary>
    /// Creates the parameter.
    /// </summary>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="type">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    /// <returns>The SqlParameter</returns>
    public static SqlParameter CreateParameter(string name, SqlDbType type, object value)
    {
        return new SqlParameter { ParameterName = name, SqlDbType = type, Value = value };
    }

    /// <summary>
    /// Executes the query.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="storedProcedure">The stored procedure.</param>
    /// <param name="inParameters">The in parameters.</param>
    public static void ExecuteNonQuery(string connectionString, string storedProcedure, params DbParameter[] inParameters)
    {
        // Do some database operation here
    }
  
    /// <summary>
    /// Calls the static ExecuteNonQuery() method
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="storedProcedure">The stored procedure.</param>
    /// <param name="inParameters">The in parameters.</param>
    void IDataAccess.ExecuteNonQuery(string connectionString, string storedProcedure, params DbParameter[] inParameters)
    {
        DataAccess.ExecuteNonQuery(connectionString, storedProcedure, inParameters);
    }
    
    /// <summary>
    /// Calls the static CreateParameter() method
    /// </summary>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="type">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    /// <returns>The SqlParameter</returns>
    SqlParameter IDataAccess.CreateParameter(string name, SqlDbType type, object value)
    {
        return DataAccess.CreateParameter(name, type, value);
    }
}   

Inject the dependency in the dependent method (Method injection)
Modify the dependent method to accept a parameter of type IDataAccess. You need to pass an instance of DataAccess from your UI code too.

/// <summary>
/// A random static "Factory" method
/// </summary>
/// <param name="id">The value for the Id parameter.</param>
/// <param name="name">The value for the Name parameter.</param>
/// <param name="dataAccess">The data access.</param>
public static void RandomRepositoryMethod(Guid id, string name, IDataAccess dataAccess)
{
    dataAccess.ExecuteNonQuery(
        ConnectionString,
        SomeRandomTableInsertSP,
        DataAccess.CreateParameter("@Id", SqlDbType.UniqueIdentifier, id),
        DataAccess.CreateParameter("@Name", SqlDbType.NVarChar, name));
}

Write the unit test method :-

/// <summary>
///A test for RandomRepositoryMethod
///</summary>
[TestMethod()]
public void RandomRepositoryMethodTest()
{
	Guid idValue = new Guid("73592249-AD57-4CDF-B5FC-9C30F65C2376");
	string nameValue = "test";
	var dataAccess = new Mock<IDataAccess>();
	IDataAccess actualDataAccess = new DataAccess();
	dataAccess.Setup(a => a.CreateParameter(It.IsAny<string>(), It.IsAny<SqlDbType>(), It.IsAny<object>()))
		.Returns<string, SqlDbType, object>((name, type, value) => actualDataAccess.CreateParameter(name, type, value));

	RandomRepository.RandomRepositoryMethod(idValue, nameValue, dataAccess.Object);
	dataAccess.Verify(
		d => d.ExecuteNonQuery(It.IsAny<string>(), "SomeRandomTable_Insert", new DbParameter[]{
			It.Is<SqlParameter>(p=>p.ParameterName == "@Id" && p.SqlDbType == SqlDbType.UniqueIdentifier && (Guid)p.Value == idValue),
			It.Is<SqlParameter>(p=>p.ParameterName == "@Name" && p.SqlDbType == SqlDbType.NVarChar && (string)p.Value == nameValue)}
			), Times.Once());
}

Lines 07 and 08 set up the value for the “Id” and “Name” parameters.

Line 09 creates a mock of the IDataAccess interface.

Line 10 creates an instance of real DataAccess class.
Why do we need an instance of DataAccess class? – Because, the ExecuteNonQuery() calls CreateParameter() to return an instance of SqlParameter. We need to set that up to return a SqlParameter If we call CreateParameter() with “any” string parameter, “any” SqlDbType and “any” object value. That’s what line 11 and 12 (they are actually one statement) are doing.

Line 14 – Calls the static method.

Lines 15 – 19 (all lines are actually one statement)
Verifies that dataAccess.ExecuteNonQuery was called with “any” string (any connection string), the required stored procedure (“SomeRandomTable_Insert”) and
two SqlParameters – first having parameter name “@Id”, type SqlDbType.UniqueIdentifier and of value “73592249-AD57-4CDF-B5FC-C30F65C2376” AND second having parameter name “@Name”, type SqlDbType.NVarChar and of value “test”. It also verifies that the DataAccess.ExecuteNonQuery() was called exactly one time.

That’s it – you have unit tested your static method. There is one caveat – you need to pass the DataAccess object to each method you want to test (Method Injection). You couldn’t pass the dataAccess object to the class’s constructor because then that instance of dataAccess can not be accessed in the static method. It won’t compile.

public class RandomRepository
{
	/// <summary>
	/// DataAccess object
	/// </summary>
	IDataAccess dataAccess = null;

	/// <summary>
	/// Initializes a new instance of the <see cref="RandomRepository" /> class.
	/// </summary>
	/// <param name="dataAccess">The data access.</param>
	public RandomRepository(IDataAccess dataAccess)
	{
		this.dataAccess = dataAccess;
	}

	/// <summary>
	/// A random static "Factory" method
	/// </summary>
	/// <param name="id">The value for the Id parameter.</param>
	/// <param name="name">The value for the Name parameter.</param>
	public static void RandomRepositoryMethod(Guid id, string name)
	{
	    // Won't compile - "An object reference is required to access non-static member"
		dataAccess.ExecuteNonQuery(
			ConnectionString,
			SomeRandomTableInsertSP,
			DataAccess.CreateParameter("@Id", SqlDbType.UniqueIdentifier, id),
			DataAccess.CreateParameter("@Name", SqlDbType.NVarChar, name));
	}
}

Personally, I didn’t like the ExecuteNonQuery() and CreateParameter() being static. Sure, static methods have their own advantages. My personal preference is to limit them in the utility classes because I find them difficult (read “less easy”) to unit test. I just wanted to mention that performance difference between static method and instance method is negligible.
From http://msdn.microsoft.com/en-us/library/79b3xss3.aspx

A call to a static method generates a call instruction in Microsoft intermediate language (MSIL), whereas a call to an instance method generates a callvirt instruction, which also checks for a null object references. However, most of the time the performance difference between the two is not significant.

2 Comments

Generate random password

A simple way to generate the password

static string GenerateRandomPassword(int numberOfCharactersInPassword)
{
   if (numberOfCharactersInPassword > 32)
   {
	   throw new ArgumentException("A password of length more than 32 can't be generated");
   }
   string guidWithoutDashes = Guid.NewGuid().ToString("n");
   Console.WriteLine("Guid without dashes :- "+ guidWithoutDashes);
   var chars = new char[numberOfCharactersInPassword];
   var random = new Random();
   for (int i = 0; i < chars.Length; i++)
   {
	   chars[i] = guidWithoutDashes[random.Next(guidWithoutDashes.Length)];
   }
   Console.WriteLine("Random password :- " + new string(chars));
   return new string(chars);
}

Leave a comment

Dependency Injection

Sample code can be downloaded from here.

First – What is a dependency?
– We are talking about a class (say “ATest”) which needs another class (say “BTest”) to call Its (“ATest”) methods (See below example). Therefore, class “BTest” is a dependency for class “ATest”.

public class BTest
{
     public int DoBTestWork()
     {
           // Do some work
     }
}

public class ATest
{
        private readonly BTest bTest;  // Dependency
        public ATest() 
        {
            bTest = new BTest();
        }
        public void DoATestWork()
        {
            var  bTestOutput = bTest.DoBTestWork();
            // Do something with bTestOutput
        }      
}

image

 

Can we try decoupling class ATest and BTest.
Yes – try interfaces. Here in the below example. We have the same classes as above. However – this time we have the method in BTest coming from the interface IBTest. Class ATest is now have the object reference of interface IBTest instead of BTest class.

public interface IBTest
{
     int DoBTestWork()
}
public class BTest : IBTest
{
    public int DoBTestWork()
   {
   // Do some work
   }
}

public class ATest
{
    private readonly IBTest bTest;
    void ATest()
    {
      bTest = new BTest();
   }

   public void DoATestWork()
   {
      var bTestOutput = bTest.DoBTestWork();
     // Do something with bTestOutput
    } 
}

Is this good enough? No. Because we we still have the reference of BTest in the ATest class. See – the even though the object bTest is of type IBTest, It is getting instantiated from BTest. So the classes – ATest and BTest are still not decoupled.

image

What is Dependency injection?
– It’s a type of IoC which let us take out the dependencies of a class and handles their (dependencies) creation and binding. This way the class no longer has to have the references of the dependencies.

How to we implement it :-

  • Constructor Injection
  • Property/Setter Injection
  • Method Injection

Constructor Injection
Pass dependency into the dependent class via the constructor. Here, the constructor on ATest is taking an interface of IBTest and we don’t have any reference of the concrete class BTest.

public interface IBTest
{
        int DoBTestWork()
}

public class BTest : IBTest
{
          public int DoBTestWork()
          {
             // Do some work
          }
}

public class ATest
{
         private readonly IBTest bTest;
         void ATest (IBTest bTest)
         {
           this.bTest = bTest;
          }

          public void DoATestWork()
          {
            var bTestOutput = bTest.DoBTestWork();
            // Do something with bTestOutput
          } 
}

Following is the code which would call use ATest and pass the its dependency BTest in it.

IBTest bTest = new BTest();
ATest aTest = new ATest(bTest);
aTest.DoATestWork();

Property/Setter Injection
Don’t pass dependency in the constructor of the dependent class. Just have the interface dependency types as public properties in the dependent class.

public interface IBTest
{
       int DoBTestWork()
}

public class BTest : IBTest
{
       public int DoBTestWork()
      {
       // Do some work
      }
}

public class ATest
{
       public IBTest BTestObject { get; set; }
       public void DoATestWork()
       {
         var bTestOutput = BTestObject.DoBTestWork();
        // Do something with bTestOutput
        } 
}

Following is the code which would call use ATest. It just sets the the public property of dependency interface type (IBTest) to the concrete type(BTest).

IBTest bTest = new BTest();
ATest aTest = new ATest();
aTest.BTestObject = bTest;
aTest.DoATestWork();

We should use this have some optional properties and having those properties not assigned won’t impact calling methods.

Method Injection
Don’t pass dependencies in the constructor or set them on the properties of the dependent class. Just pass them in the method of the dependent class.

public interface IBTest
{
   int DoBTestWork()
}

public class BTest : IBTest
{
     public int DoBTestWork()
     {
        // Do some work
     }
}

public class ATest
{
       public void DoATestWork(IBTest bTest)
       {
          var bTestOutput = bTest.DoBTestWork();
         // Do something with bTestOutput
       } 
}

Following is the code which would call use ATest. It just passes the dependency interface type (IBTest) to the dependent ATest method DoATestWork.

ATest aTest = new aTest();
IBTest bTest = new BTest();
aTest.DoATestWork(bTest);

, ,

2 Comments

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands