TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case – it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure’s parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008).

Ok, let’s suppose that we have list of items and we need to filter this items by categories (“TV”, “TV game device”, “DVD-player”) and by firms (“Firm 1”, “Firm2”, “Firm 3). It will look at database like this

So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers – it is creating SQL-instruction with C# code, it can be like this

List<int> categories = new List<int>() { 1, 2, 3 };

 

StringBuilder sbSql = new StringBuilder();

sbSql.Append(

  @"

    select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName 

    from Item i

      inner join Firm f on i.FirmId = f.FirmId

      inner join Category c on i.CategoryId = c.CategoryId

    where c.CategoryId in (");

if (categories.Count > 0)

{

  for (int i = 0; i < categories.Count; i ++)

  {

    if (i != 0)

      sbSql.Append(",");

    sbSql.Append(categories[i]);

  }

}

else

{

  sbSql.Append("-1"); // It is for empty result when no one category selected

}

sbSql.Append(")");

 

string sqlQuery = sbSql.ToString();

 

DataTable table = new DataTable();

using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))

{

  connection.Open();

  using (SqlCommand command = new SqlCommand(sqlQuery, connection))

  {

    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

    {

      dataAdapter.Fill(table);

    }

  }

}

 

//TODO: Working with table


We will filter items only by categories. Just want to write less code. In the previous example first line is list of categories identifiers, chosen by user (user can select checkboxes). Problems of this solution are: a) sql-injections in some cases (user can change identifiers, which we get from web-form); b) not really good code support at feature when categories can be a really big set. One more problem – it will be hard to place this code to stored procedure (of course you can use exec statement at sql-server, but it will be not a good choice). So we can name this solution like “Solution #0”, because you can use it only if you are very lazy guy, or because this solution is very fast written.


Read more… (Solution #1. String – parameters separated by comma., Solution #2. BULK INSERT, Solution #3. Table-Valued Parameters (Database Engine))

Config Transformation Tool: Using XDT Transformation

XDT Transformation is a new feature of ASP.NET 4.0 named Web.Config Transformation.


Scott Guthrie: “In most real-world deployment scenarios, the web.config file you use for development is different than the one you use for production deployment. Typically you want to change environment settings like database connection-strings, making sure debug is turned off, and enabling custom errors so that end-users (and hackers) don’t see the internals of your application.


But the chief problem of this feature – is working only with web.config files.


I investigated this problem, and wrote Config Transformation Tool, which gives opportunity to use XDT Transformation Syntax like at Deployment Web Application Project for any files. This tool is very easy, it just run msbuild task, which do this transformation. Read more…

Working with CodePlex. How to save password for project?

I want to make public one small project on CodePlex. Everybody knows that you can work with CodePlex like with TFS client and with SVN client as well. But really I can’t work with it from SVN client. When I tried to add some binary file (it was dll) I got the error “Server sent unexpected return value (200 OK) in response to PUT request for …”. How to solve this problem I don’t know, I found thread at CodePlex’s discussions Can’t commit binary files using TortoiseSVN, but last message was at past year. Ok, I’m working with TFS at work, so for me it will be not a problem connect to CodePlex with TFS. But there are some distressing case, Visual Studio always ask credentials at every new connection to project. And I didn’t find a checkbox “Save credentials”. But I found the method. Read more…

RESTful WCF Service – How to get browser version at server code

At our product we have a client Silverlight part and server-code part, which contains a lot of WCF methods. We don’t use ASP.NET Compatible mode, because we want to leave an opportunity to deploy server part to server without web-server role (without IIS). Really, I don’t know why we chose this way, because all of our installations at current moment are on IIS. But we have what we have, so we haven’t ASP.NET Compatible mode, and as an expected result we can’t get HttpContext.Current instance at server WCF methods. One of WCF Service is a RESTful service, which at his methods returns report files, so it can handle GET-queries from browsers. Read more…