SQL: Executing a set of queries with different parameters in a loop

It has been quite long time since I did lot of SQL queries and recently had to get myself in one such task. There were some long set of queries to be executed in a loop that is supposed to run for around 50 times with different set of results.
With some search/research I found a quick solution using SQL Cursors and thought of sharing the same.
Example:
The scenario was as below:

DECLARE @Var1 NVARCHAR(100)

SET @Var1 = 'Some value'

SELECT VALUES FROM TABLENAME WHERE VALUE = @Var1
.
.
.
...-- many other similar queries using the value of @Var1

The challenge was to keep setting the @Var1 variable with 50 or more different values to get different set of results.
Instead of executing the above queries 50 odd times, the following steps could be used for once instead to achieve the same results:

1. The first step is to get the 50 or more odd values into a table using a condition that could be used further:
SELECT * INTO SETOFVALUESTABLE FROM SOMETABLE WHERE -- CONDITION

2. Next step is to declare a cursor that could accomplish our task:

DECLARE @Var1 NVARCHAR(100)

DECLARE db_cursor CURSOR FOR
SELECT COLUMNNAME FROM SETOFVALUESTABLE

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Var1

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT VALUES FROM TABLENAME WHERE VALUE = @Var1
.
.
.
...-- many other similar queries using the value of @Var1

FETCH NEXT FROM db_cursor INTO @Var1
END

CLOSE db_cursor
DEALLOCATE db_cursor

Using the above steps saved me a lot of time and made it so simple and productive instead of the 50+ times execution of queries.

There might still be better efficient ways to achieve the same with which I plan to optimize, but the above method is one quick way to get around the problem we faced.

HOW TO: Connect to AX 4.0 from a .NET Application using Business connector

This article demonstrates how a we can connect to Microsoft Dynamics AX 4.0 from a .NET based application.

You can find a good explanation of Microsoft Dynamics AX from here.

For this you will need to have Microsoft Dynamics AX Client installed on your machine. You must also have Dynamics AX .NET Business connector installed on your machine - to connect to AX from .NET.

Once you have the client components installed, you must configure your client to connect to an Axapta server from Control panel -> Administrative tools ->Microsoft Dynamics AX Configuration Utility.




Once configured, you can open the AX client UI from Start-> Programs-> Microsoft Dynamics AX.



Lets now see how to invoke a simple method in AX from a .NET application.

The following method should be written in AX inorder to be invoked from .NET:
1. Navigate to AX UI
2. Click on Axapta Object Tree (AOT)
3. Expand classes node.
4. Add the following class and method:



5. Test this method by executing it as a Job.
(AOT-> Jobs ->Create a simple job -> add the above code with an Info statement.)

Once the above steps are executed successfully, the following are the steps for invoking this AX method in .NET.
1. Open a new Console application using Visual Studio 2008
2. Add the following Reference to the Project:
Microsoft.Dynamics.BusinessConnectorNet
This dll is present in:
C:\Program Files\Microsoft Dynamics AX\40\Client\Bin
3. Naviate to Program.cs file and add the following namespace for reference:
using Microsoft.Dynamics.BusinessConnectorNet;
4. Add the following code in main() to invoke the AX method:

static void Main(string[] args)
{
try
{
Axapta ax = new Axapta();
ax.Logon(null, null, null, null);
string strName = " Microsoft Dynamics AX ";
AxaptaObject axObject;
axObject = ax.CreateAxaptaObject("NetConnector");
string resonseFromAxapta = (string)axObject.Call("SampleMethod", strName);
Console.WriteLine(resonseFromAxapta);
Console.ReadLine();
ax.Logoff();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

5. Compile and Run the Application.
Thats all about it! You now should see the data coming from Axapta in .NET.

HOW TO: Create a CSV File using System.IO classes

CSV (Comma Separated Values) file is a text based file in which data are separated by comma. It can be opened by excel so you can use excel functionality. Each row of data including the title is in separate line. Each row has data separated by comma.

If its an ASP.NET application, a CSV file can be easily created by setting the
HttpContext.Current.Response.ContentType = "text/csv";

For other Windows applications or a Windows based service, the above method cannot be used. The other possible way is to use the Interop Excel object to create. However, this may require an extra dll to be added to your app. As this is an Interop dll, its not managed and maintaining this dll may become difficult in future.

The following method gives an easier way of handling this - using the classes in System.IO namespace. The below is a code extract that shows how this can be done. We have used a simple example of getting a list of employees

using System.IO;
using System.Text;

private string[] empDetailsHeader = {
"Employee ID",
"Name",
"Salary"
};
private const string separator = ",";

private void CreateCSVFile(string filePath){
try {
StringBuilder csvString = new StringBuilder();

List empList = new List();
//Writing the Header

for (int i = 0; i < empDetailsHeader.Count(); i++){
csvString.Append(empDetailsHeader[i]);
csvString.Append(separator);
}
//Get the list containing the Employee details
empList = GetEmployeeList();

csvString.AppendLine();
foreach (Employee employee in EmpDetails){
csvString.Append(employee.ID.ToString());
csvString.Append(separator);
csvString.Append(employee.Name.ToString());
csvString.Append(separator);
csvString.Append(employee.Salary.ToString());
csvString.Append(separator);
//Next Line
csvString.AppendLine();
}
//save the file
SaveFile(filePath, csvString);
}


private void SaveFile(string filePath, StringBuilder stringBuidler) {
if (File.Exists(filePath)){
File.Delete(filePath);
}else{
StreamWriter streamWriter = new StreamWriter(filePath);
streamWriter.Write(stringBuidler.ToString());
streamWriter.Close();
}
}

Now how do we take care if there is a comma in the data that we need to get into the CSV? Any comma in data is taken as a delimiter for taking the data to the next column. As a work around for this - to still maintain the same data, we can wrap the text using double quotes.

For example, suppose the Salary column in the above data has a value 40,000. Some developers use the method of replacing this comma with space. By doing this we sometimes lose the authenticity of data.
To make sure that we still maintain the correct data, we need to wrap the string with double quotes.

char doubleQuote = '"';
if(employee.Salary.ToString().Contains(",")) {
csvString.Append(doubleQuote);
csvString.Append(employee.Salary.ToString());
csvString.Append(doubleQuote);
}
This will ensure that we have correct data in the CSV File that is created.

ASP.NET 2.0 - A Preview of Web LINQ - BLINQ - Part II

Part I of this article discussed on the basics of LINQ, and what DLINQ and XLINQ are.
The following is a step by step procedure in using BLINQ.

1. Download the MSI from:
http://www.asp.net/sandbox/app_blinq.aspx?tabid=622.
For using BLINQ, May 2006 CTP of LINQ must be installed in your machine.
Refer Part I of the article for this.
3. Install the MSI in your system. By default, BLINQ gets installed in C:\Program Files\Microsoft ASP.NET\BLINQ.
4. Open Visual Studio 2005. On the File Menu, point to New and then click Web Site.
5. Under Visual Studio installed templates, select ASP.NET Web Site.
6. In the Location drop down list, select File System and give the path as C:\BLINQDemo and then click OK. This creates a new Web Applcation.
7. Now click Start, and then click Run. Type cmd in the Open box and then click OK.
8. In the command prompt type cd "\Program Files\Microsoft ASP.NET\Blinq" .
9. Now run the BLinq tool on the created Web Site by typing the following command, replacing Servername,UserName and Password accordingly. Also copy the same
to a single line before running the command. I have split it here for posting
purposes:
Blinq /t:c:\BLINQDemo /database:Northwind /vDir:BLINQDemo
/server:ServerName /user:UserName /password:Password

10. You will be prompted if your web site can be over written. Type Y and then press enter.
11. After successful creation, you will get a message WebApp created at C:\Blinqdemo.And the web site opens up at http://localhost/blinqdemo/categories.aspx, as shown below:


You will also be able to perform all Insert,Update,Delete, View (CRUD) options on the entire Database. The image below shows the Update,Delete and View options in Categories.


BLINQ is definitely an exciting tool for every developer. Let us wait for the next release of this tool, integrated with Visual Studio.

ASP.NET 2.0 - A Preview of Web LINQ - BLINQ - Part I

LINQ has been the recent buzz word as its a part of the C# 3.0 family. In this article let us take a quick look at what LINQ, DLINQ and XLINQ are all about and what BINQ - Web LINQ offers us.
LINQ - Language Integrated Query is a technology that gives us the flexibility of performing query language operations in .NET. Formerly introduced as ObjectSpaces, LINQ is now available as a Community Technology Preview in http://msdn.microsoft.com/data/ref/linq.

The first step is to download the May 2006 CTP from the above link and install the same in your system. The CTP requires that Visual Studio 2005 is installed in your system. On installing the same, you will find a lot of samples and documents that are given as a part of the CTP.

LINQ provies a lot of features to improve the developer productivity by minimizing a lot of data access code.Following is a sample of a query operation over an array. Such a typer of operation is not possible now in C# 2.0 and you need to loop through all elements in the array to find out the words with length 5.

using System;
using System.Query;
using System.Collections.Generic;

class app {
static void Main() {
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };

IEnumerable expr = from s in names
where s.Length == 5
orderby s
select s.ToUpper();

foreach (string item in expr)
Console.WriteLine(item);
}
}

DLINQ is LINQ operated on relational data, such as data from SQL Server. The following is a sample on how
you can query the Products information in Northwind database. The metadata information of the tables
present in the Northwind database is generated using a tool called sqlmetal by pointing a particular database
to it. In the below snippet, NorthwindDataContext class contains all metadata about the entire Northwind
database.

NorthwindDataContext db = new NorthwindDataContext();

DataList1.DataSource = from p in db.Products
where p.UnitPrice > 20
orderby p.ProductName
select p;
DataList1.DataBind();

XLINQ is LINQ operating on XML data. Consider the following XML block:

<contacts>
<contact>
<name>Great Lakes Food</name>
<phone>(503) 555-7123</phone>
</contact>
…
</contacts>

Using today's C# 2.0, we need loop through each of the customer records in the collection to build an XML as
above. XLINQ significantly improves this process. A sample code using XLINQ to build a simple XML is given below:

XElement contacts = new XElement("contacts",
from c in customers
where c.Country == "USA"
select new XElement("contact",
new XElement("name", c.CompanyName),
new XElement("phone", c.Phone)
)
);

BLINQ is currently a tool, available for download at :
http://www.asp.net/sandbox/app_blinq.aspx?tabid=62

In the next article we will discuss on how to start using BLINQ.

ASP.NET - Maintaining Versions in Web Setup projects

During deployment of projects, many times we come across the situation to reinstall the same application with some changes to the code. Usually when we have an MSI build, the build often prompts us to uninstall the older version and reinstall the latest version.

In this article we will discuss on how to install different versions of the same application and not force overwriting the application. The steps for creating an MSI installer for ASP.NET 2.0 projects is explained in this article.

Everytime a new version of a product is installed the reason why the MSI prompts to uninstall the existing application and install a new version is due to the same ProductCode Guid that is present in both the older version and the newer version of the product.

To install different versions of the same application, follow these steps:
1. Add the setup project to your application and add the necessary primary outputs as described in this article. Name this project as MySetupProject.

2. Select MySetupProject in Solution Explorer and then press F4 key. This will open the Properties window.


3. Note the Version property in the left column. Everytime a newer version of the application needs to be installed, increment the version number, say from 1.0.0 to 1.0.1 and so on.When you change this number and press enter, you will get the following prompt:



4. Click Yes on the prompt. You will notice that ProductCode property value (a GUID) is changed once you change the version number.
5. Now notice the RemovePreviousVersions property. This property is for specifying whether to over write the different versions or to have the different versions separately.
Making the RemovePreviousVersions to true means that every new version of your product will be over written.
Making this property to False gives you the flexibility to have both your older version and the newer version separately installed.

Thus by incrementing the Version and making RemovePreviousVersions to False you can have different versions of the same product running on your machine.

Now with this setup, we must also ensure that only a particular version that we need is used after deployment. The next article discusses on this topic of using a preferred version of your application after deployment.

ASP.NET 2.0 Deployment : Installer for updating Web.Config settings during Setup - Part II

In Part I of the article we saw how Visual Studio 2005 Web Deployment Projects help in deployment of ASP.NET 2.0 applications. Let us now look at how we can make use of this add-on in building an MSI that also sets the configuration settings.

The first step is to add an installer class in your web project. This installer class will be used in setting the configuration settings in web.config. The following steps will discuss on adding the installer class:

1. Right click on your web project, and then click Add New Item.
2. Under Templates, click Class, and then give the name of the class as InstallerClass.cs.
3. This will get added in the App_Code folder in your web application.
4. Right click on the Web Project, and then click Add Reference. Add the following references to the Web project:
System.Configuration.Install
System.Configuration
System.Windows.Forms
5. Open InstallerClass.cs, and then replace it with the following code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration.Install;
using System.ComponentModel;
using System.Collections;
using System.Windows.Forms;
using System.Xml;
///

/// Summary description for InstallerClass
///

[RunInstaller(true)]
public class InstallerClass : System.Configuration.Install.Installer
{
private System.ComponentModel.Container components = null;
public InstallerClass()
{
components = new System.ComponentModel.Container();
}
protected override void OnBeforeInstall(IDictionary stateSaver)
{
string server = this.Context.Parameters["DATASOURCE"];
string userId = this.Context.Parameters["USERID"];
string password = this.Context.Parameters["PASSWORD"];
string newConnectionString = "User Id=" + userId + ";Password= " + password + ";Data Source =" + server + ";";
string xmlConfigFile = "";
xmlConfigFile = Context.Parameters["INSTALLDIR"] + "web.config";
UpdateConfigFile(xmlConfigFile, newConnectionString, "connectionString");
}
public void UpdateConfigFile(string filepath, string newValue, string keyName)
{
XmlReaderSettings xmlReaderSettings = null;
XmlDocument doc = null;
XmlReader xmlReader = null;
XmlWriterSettings xmlWriterSettings = null;
XmlWriter writer = null;
try
{
xmlReaderSettings = new XmlReaderSettings();
doc = new XmlDocument();
xmlReader = XmlReader.Create(filepath, xmlReaderSettings);
doc.Load(xmlReader);
xmlReaderSettings.CloseInput = true;
foreach (XmlNode node in doc.DocumentElement.ChildNodes)
{
if (node.Name == "connectionStrings")
foreach (XmlNode childNode in node.ChildNodes)
{
if (childNode.Name == "add" && childNode.Attributes["connectionString"] != null && childNode.Attributes["name"].Value == keyName)
{
string connStr = childNode.Attributes["connectionString"].Value.ToString();
string newConnStr = newValue;
childNode.Attributes["connectionString"].Value = newConnStr;
}
}
}
xmlReader.Close();
xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Indent = true;
xmlWriterSettings.CloseOutput = true;
writer = XmlWriter.Create(filepath, xmlWriterSettings);
doc.Save(writer);
}
catch (Exception)
{
//Handle exeptions
}
finally
{
writer.Close();
}
}
}

6. Save the file and build your web project.

In Part I of the article we discussed on how to add a Web deployment. Make sure the Web Deployment Project is added to the Web project and the settings are done as detailed in Part I of this article. Rebuild this Web Deployment project also.

The next step is to build an MSI. The following steps explain the same:

1. Right click on the solution node in Solution Explorer, point to Add, and then click New Project.
2. Select Setup and Deployment under Other Project Types from the Project Types box.
3. Select Web Setup Project under Templates, and then give a name MyNewWebSiteSetup.
4. Right click MyNewWebSiteSetup on Solution Explorer, point to Add, and then click Project Output.
5. From the Project dropdown, choose (i.e. the Web Deployment project that you have added and compiled). You will find that Precompiled Web Outputs from (Active) is added to your Setup project.
6. Right click MyNewWebSiteSetup on Solution Explorer, point to Add, and then click Assembly.
7. From the Browse tab, browse to the folder where your Web Deployment Project is present. Under the MyNewWebSite_deploy -> Debug -> Bin path you will find the single assembly of the Web Project that was created, named MyNewWebSiteAssembly
(refer Point 5 in Part I of the article).
8. Right click MyNewWebSiteSetup on Solution Explorer, point to View, and then click User Interface.
9. Under the Install node, right click on Start and then click Add Dialog.
10. From the Add Dialog pop up choose Textboxes (A) and then click OK.
11. Right click Textboxes (A) node and then click Properties Window.
12. In the Properties window, set the following properties:
BannerText - Configuration Settings
BodyText- Please provide values for Configuration Settings
Edit1Label-DataSource:
Edit1Property- DATASOURCE
Edit2Label-UserId:
Edit2Property- USERID
Edit3Label-Password:
Edit3Property- PASSWORD
Edit4Visible-False

13. After setting the above properties, close the Properties window.
14. Right click MyNewWebSiteSetup on Solution Explorer, point to View, and then click Custom Actions.
15. On the left pane, right click Install node under Custom Actions, and then click Add Custom Action.
16. In the Look in dropdown list in Select Item in Project dialog, select Web Application Folder.
17. Click the Add File button and then choose the single assembly dll that is present under the MyNewWebSite_deploy -> Debug -> Bin -> MyNewWebSiteAssembly.dll.

Note : If you have not set any path when adding, it would be present in C:\Documents and Settings\ My Documents\Visual Studio 2005\Projects\ GridViewWeb\ MyNewWebSite_deploy\ Debug\bin\ MyNewWebSiteAssembly.dll.

18. Right click MyNewWebSiteAssembly.dll on the left pane, and then click Properties Window.
19. In the Properties Window, set the CustomActionData property to the following:
/datasource=[DATASOURCE] / userid=[USERID] /password=[PASSWORD] /INSTALLDIR="[TARGETDIR]\"

These are precisely the properties we use in the InstallerClass.

20. Right click MyNewWebSiteSetup on Solution Explorer, and then click Build.
21. You can test the installation of MSI using the Install option when you right click MyNewWebSiteSetup on Solution Explorer.

You will find that you can set the connection string properties at runtime during installation of your Web Application.

22. Set your project in Release mode, and then take a build again to take the final installable.

While picking up the single assembly dll was straight forward in ASP.NET 1.1, in ASP.NET 2.0, this has been made possible with the Visual Studio Web Deployment Project Add on. This makes adding the Custom Actions possible.

ASP.NET 2.0 - Custom Paging and Custom Sorting for GridViews

Paging and sorting GridViews are the most sought after functionalities in most of the Web applications.When it comes to performance, for datacentric applications its very important to implement the custom paging/sorting mechanism instead of the
default paging/sorting for gridviews. This is because the page becomes heavily loaded when thousands of records are fetched at a strech from the
database.

To overcome this, we must implement a "Custom paging" mechanism which brings record only "on-demand". This article discusses how this can be
implemented in ASP.NET 2.0 with zero lines of code in presentation layer.

1. The first step is to write a stored proc, GetSortedItems that accepts these parameters and returns a resultset.

a. SortExpression
b. StartRow
c. EndRow

For Example, if the parameter values are ("ItemId desc", 5,10), which means the rows 5 to 10 have to be retrieved, with ItemId sorted in descending
order.

2. Another stored proc must be written, say GetItemsCount that returns the count of records that is returned from the resultset.

3. Next step is to call the stored proc in the Data Access layer, and return a Collection object.

The following is an example of a DAL method using ODP.NET of Oracle:

public QueryList GetSortedQueries(string sort, int startRowIndex, int maximumRows, int x, string y)
{
DataSet dsQueryList = null;
OdpHelper adoHelper = new OdpHelper();
QueryEntity queryEntity = null;
QueryList queryList = null;
string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
IDataParameter[] param = null;
param = adoHelper.GetDataParameters(4);
param[0] = adoHelper.GetRefCurParameter("p_cursor");
param[0].Direction = ParameterDirection.Output;
param[1] = adoHelper.GetParameter("P_SortExp",
DbType.String,
1000,
ParameterDirection.Input
);
param[1].Value = sort;
param[2] = adoHelper.GetParameter("BV_STARTROW",
DbType.Int32,
4,
ParameterDirection.Input
);
param[2].Value = startRowIndex;
param[3] = adoHelper.GetParameter("BV_ENDROW",DbType.Int32,4,ParameterDirection.Input);
int lastRow = startRowIndex + maximumRows;
param[3].Value = lastRow;
dsQueryList = adoHelper.ExecuteDataset(connectionString,"TestCustomPagingWithSorting",param);
queryList = new QueryList();
//loops through the dataset rows to load displaycontrols retrieved into the list
if (dsQueryList.Tables.Count > 0)
{
if (dsQueryList.Tables[0].Rows.Count > 0)
{
foreach (DataRow drQueryEntity in dsQueryList.Tables[0].Rows)
{
queryEntity = new QueryEntity();
if (drQueryEntity["queryid"] != DBNull.Value)
{
queryEntity.QueryId = Convert.ToInt32(drQueryEntity["queryid"].ToString());
}
if (drQueryEntity["querytypeid"] != DBNull.Value)
{
queryEntity.QueryTypeId = Convert.ToInt32(drQueryEntity["querytypeid"].ToString());
}
queryList.Add(queryEntity);
}
}
}
return queryList;
}

4. In the Presentation layer, all we have to do is to add an ObjectDataSource control and set the following properties:

• SelectMethod property to the GetSortedQueryList method - either from Data Access Layer or Business Facade
• TypeName property to the Data Access Layer or Business Facade class that has the specific method.
• SortParameterName property to the parameter name in which the Sort expression is passed.
• SelectCountMethod is the method in the Data Access Layer or Business Facade class that returns the count of records.
• The <SelectParameters> are the individual parameters that are present passed to the SelectMethod and SelectCountMethod.
• If the GridView has to be bound on triggering of an event, just do not specify the DataSourceID="ObjectDataSource1" in GridView, and add these
  two lines of code in the event.
  GridView1.DataSourceID = "ObjectDataSource1";
  GridView1.DataBind();

Note: The method signature of SelectCountMethod must necessarily contain all parameters of SelectMethod, even though these may not be used.

Ex:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSortedQueryList"
SortParameterName="sort" TypeName="CustomPagingService.CustomPagingService" SelectCountMethod="GetQueryListCount"
EnablePaging="True" >
<SelectParameters>
<asp:Parameter DefaultValue="Asc" Name="sort" Type="String" />
<asp:Parameter DefaultValue="1" Name="startRowIndex" Type="Int32" />
<asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" />
<asp:Parameter DefaultValue="10" Name="x" Type="Int32" />
<asp:Parameter DefaultValue="" Name="y" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>



<asp:GridView ID="GridView2" DataKeyNames="QueryId" runat="server" AllowPaging="True"
AllowSorting="true" AutoGenerateColumns="false"
PageSize="5">
<Columns>
<asp:BoundField DataField="QueryId" ReadOnly="true" HeaderText="QueryId" SortExpression="QueryId" />
<asp:BoundField DataField="QueryTypeId" ReadOnly="true" HeaderText="QueryTypeId"
SortExpression="QueryTypeId" />
</Columns>
<PagerSettings FirstPageText="First" LastPageText="Last" Mode="NumericFirstLast" />
</asp:GridView>

Thats it. Just compile and run your page. You will find that by just setting properties with no line of code in the presentation layer, Custom paging and sorting functionalites are achieved.