SQL Command using SQLCMD utility to run the large SQL Script Files

c:/>sqlcmd -U <username> -P <pwd> -S <servername> -i <SQL Script File Path>

Query to All / Selected tables using undocumented SQL Procedure sp_msforeachtable

In this article, we will see basic use of undocumented procedure sp_MSforeachtable 
E.g. Adding any column to each user-defined table in SQL Server:

exec sp_msforeachtable 'alter table ? add flag bit not null default 0';

Great Link to get loaders :)

http://www.ajaxload.info/

Entity Framework -- Returning Multiple Result Sets from Stored Procedure.

Returning Multiple Result Sets from Stored Procedure in Entity Framework, is no more a difficult task.

Here's the way how we can do:

Sample Procedure Returning multiple Result-Sets:

CREATE PROCEDURE dbo.GetPostsByDateRange
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
AS
BEGIN
    SELECT
       *
    FROM
       Posts
    WHERE
       PostDate BETWEEN @StartDate AND @EndDate

    SELECT
       *
    FROM
       Comments
       JOIN Posts
          ON Comments.Post_Id = Posts.Id
    WHERE
       PostDate BETWEEN @StartDate AND @EndDate
END

Sample Entities that we might have in Entity Framework:

public class PostModel
{
    public int Id {get; set;}
    public string Title {get; set;}
    public IList<CommentModel> Comments {get; set;}
   
    public PostModel()
    {
        Comments = new List<CommentModel>();
    }
}

public class CommentModel
{
    public int Id {get; set;}
    public string Author {get; set;}
    public string Body { get; set;}
}


Extenstion Class with Methods, to fetch Multiple Result Sets in EF:

public static class DbContextExtensions
{
    public static MultiResultSetReader MultiResultSetSqlQuery(this DbContext context, string query, params SqlParameter[] parameters)
    {
        return new MultiResultSetReader(context, query, parameters);
    }
}

public class MultiResultSetReader : IDisposable
{
    private readonly DbContext _context;
    private readonly DbCommand _command;
    private bool _connectionNeedsToBeClosed;
    private DbDataReader _reader;

    public MultiResultSetReader(DbContext context, string query, SqlParameter[] parameters)
    {
        _context = context;
        _command = _context.Database.Connection.CreateCommand();
        _command.CommandText = query;

        if (parameters != null && parameters.Any()) _command.Parameters.AddRange(parameters);
    }


    public void Dispose()
    {
        if (_reader != null)
        {
            _reader.Dispose();
            _reader = null;
        }

        if (_connectionNeedsToBeClosed)
        {
            _context.Database.Connection.Close();
            _connectionNeedsToBeClosed = false;
        }
    }

    public ObjectResult<T> ResultSetFor<T>()
    {
        if (_reader == null)
        {
            _reader = GetReader();
        }
        else
        {
            _reader.NextResult();
        }

        var objContext = ((IObjectContextAdapter) _context).ObjectContext;

        return objContext.Translate<T>(_reader);
    }

    private DbDataReader GetReader()
    {
        if (_context.Database.Connection.State != ConnectionState.Open)
        {
            _context.Database.Connection.Open();
            _connectionNeedsToBeClosed = true;
        }

        return _command.ExecuteReader();
    }
}

Using the above extension methods, we can achieve our goal. Here we come:

Sample Code:

var query = "EXEC dbo.GetPostsByDate @StartDate, @EndDate";
var parameters = new[]
{
    new SqlParameter("@StartDate", startDate),
    new SqlParameter("@EndDate", endDate),
};

//Assume _context is your EF DbContext
using (var multiResultSet = _context.MultiResultSetSqlQuery(query, parameters))
{
    var posts = multiResultSet.ResultSetFor<PostModel>().ToDictionary(x => x.Id);

    var comments = multiResultSet.ResultSetFor<CommentModel>().ToArray();

    foreach (var comment in comments)
    {
        posts[comment.PostId].Comments.Add(comment);
    }
   
    return posts.Values.ToArray();
}


Hope it helps.
Reference from here

Search string as the combinations of words, from the collections / List using ExtentionMethods and LINQ

Normally, User searches the records by typing any word and if that matches any record in the list, it fetches and shows those records in the appropriate records.
But there is another Search. What If User enters more than one word, and let the system to fetch the records that finds any combination of those words from the list (JUST LIKE GOOGLE Search), then we have to do something better than the above approach implemented:

Some Class:
public class Users
{
public int ID {get;set;}
public string Name {get;set;}
public string Username {get;set;}
}


The Normal Approach:

// This is my active list of all users
List<Users> allUsers = UserRepository.GetAll();

// This is my search string
string searchString = "Amit Jain";

// Here is all my users that match any words in the search string
List<string> foundUsers = allUsers.Where(s => s.Username.Contains(searchString)).ToList();


The above method will search for 'Amit Jain' as one word in the records of Users Table.



The Approach with combination of searched strings:

Extension Class:

public static class StringExtension

{

  public static bool ContainsAny(this string str, IEnumerable<string> searchTerms)

  {

    return searchTerms.Any(searchTerm => str.ToLower().Contains(searchTerm.ToLower()));

  } 

 

  public static bool ContainsAll(this string str, IEnumerable<string> searchTerms)

  {

    return searchTerms.All(searchTerm => str.ToLower().Contains(searchTerm.ToLower()));

  }

}

// Here is all my users that match any words in the search string
List<string> foundUsers = allUsers.Where(s => s.Username.ContainsAny(searchString.Split(' '))).ToList();


Here, it will search for individual two words i.e. Amit and Jain in the list of records...

Happy Coding !!!!

Programmatically Encrypt any xml Node of web.config in .Net

Suppose we have the web.config:

<connectionStrings>
    <add name="OurDb"
         connectionString="Data Source=(LocalDB)\MSSQLLocalDB;
         AttachDbFilename=|DataDirectory|\OurDb.mdf;
         Initial Catalog=OurDb;
         Integrated Security=True" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>

For encrypting it, we use the code below:

public static void EncryptConnString()
 {
     Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
     ConfigurationSection section = config.GetSection("connectionStrings");

     if (!section.SectionInformation.IsProtected)
     {
         section.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
         config.Save();
     }
 }

For Decrypting the same, use the below code:

public static void DecryptConnString()
 {
     Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
     ConfigurationSection section = config.GetSection("connectionStrings");
     if (section.SectionInformation.IsProtected)
     {
         section.SectionInformation.UnprotectSection();
         config.Save();
     }
 }

Sometimes you might get the following error in config.Save();:
"Failed to encrypt the section 'connectionStrings' using provider 'RsaProtectedConfigurationProvider".

To resolve this, here's the solution:
We have used the Provider RsaProtectedConfigurationProvider, you have to open the Visual Studio as Administrator.

Alternatively, we can use the other provider i.e. DataProtectionConfigurationProvider. 

Referenced from here..
     

Closer to Success!

I'm working as programmer and deals mostly with Microsoft Technologies. Having a good experience in various Cliental Solutions from different Industries such as Health Care, Facility Management and Fisheries, Social Networking, Finance and Sports.

Among them, Health Care is the one that gives a great exposure in terms of technical, functional, Managerial Skills and logistics. That was fortunate. It's developed in the Asp.Net MVC at the front end and SQL Server at the back end, with the big support of other technologies such as JQuery, Ajax, HighCharts API, Telerik Controls, LINQ, Resharper Tool (considering this a great tool in improving the Coding Approach). It's almost been 2 years working on this product. And this month, the application has been launched with a great hope of Success.

Now with a bunch of small knowledge that I gain so far, my mind keeps prompting me to have a more challenging and learning work. And so, I start looking for a new freelancing jobs over the wire and so, found one over twitter i.e. toptal. From the tweets and other information on the internet, Toptal looks nice and genuine platform for the freelancers.
Also, it seems toptal is going to be a great future for the individuals who wants to improve themselves at technology level.

Looking forward now...

Add Days to Specific Date in JQuery

Add Days to Specific Date in JQuery

//Current Date
var specificDate = new Date();

//Next Day of Current Date.
specificDate.setDate(specificDate.getDate() + 1);

Get the Count of User Defined Objects in SQL

SELECT 

    CASE TYPE 

        WHEN 'U' 

            THEN 'User Defined Tables' 

        WHEN 'S'

            THEN 'System Tables'

        WHEN 'IT'

            THEN 'Internal Tables'

        WHEN 'P'

            THEN 'Stored Procedures'

        WHEN 'PC'

            THEN 'CLR Stored Procedures'

        WHEN 'X'

            THEN 'Extended Stored Procedures'

    END, 

    COUNT(*)     

FROM SYS.OBJECTS

WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')

GROUP BY TYPE

Concatenate values of single column with multiple records into a single string using SQL

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ',', '') + itemRef FROM TableName Where activityRef = '123123'
Select @Names