Getting output paramater with ExecuteReader()

When you have a stored procedure that return both a result set and a return value, and you use ExecuteReader() to get the result set, you can only get the output parameter when you close the reader!

http://support.microsoft.com/kb/310070

SqlCommand sqcMyProc = new SqlCommand("[dbo].spMyProc", sqDBConnection);
sqcMyProc.CommandType = CommandType.StoredProcedure;
sqcMyProc.Parameters.Add(new SqlParameter("@nSomeParam", SqlDbType.BigInt));
sqcMyProc.Parameters["Result"].Direction = ParameterDirection.ReturnValue;
sqcMyProc.Prepare();
SqlDataReader sqrMyProcReader = sqcMyProc.ExecuteReader();
if (sqrMyProcReader.Read())
{
// read data
}
sqrMyProcReader.Close(); // output param only available after closing reader

(int)sqcMyProc.Parameters["Result"].Value; // here is the return value

How to Implement a Join between 2 Tables in T-SQL/SQL Server UPDATE Statement

Thanks for WineIsGood:

UPDATE tblA
SET tblA.a_myField = tblB.b_newField
FROM tblA, tblB
WHERE tblA.a_b_id = tblB.b_id

GigaSpaces XAP .NET-Java Interoperability

For all kind of data that you move between .NET and Java code that connects to GigaSpaces, please refer to this page to match types correctly:

http://www.gigaspaces.com/wiki/display/XAP66NET/.NET-Java+Interoperability

Database Connection Open/Close

It seems trivial that you first open your connection, and eventually close it, but what happens when you're doing it in a loop, and one miss causes havoc forever until restart?

Make sure you open only if it's not closed, you close only if it's not open, and handle every exception. See following code example:

try //catch (Exception E)
{
try //finally close DB
{
// open only if not already open
if (oSqlConn.State!=ConnectionState.Open)
oSqlConn.Open();

// *** READ FROM DATABASE ***

}
finally
{
// close only if not already closed
if (oSqlConn.State != ConnectionState.Closed)
oSqlConn.Close();
}
}
catch (Exception E)
{
// handle exception
}

Microsoft Visual Studio Error

After reintalling the pc I have faced an error while attempting to connect to the database (Microsoft SQL Server) from the Visual Studio.

The Error said:

'Microsoft.SqlServer.Managment.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified 

The solution was very simple and I found it on Agha Usman Ahmed's blogg

you just have to install some components from here "Microsoft SQL Server 2008 Feature Pack, August 2008"

let's see if it works 

C sharp 3 and LINQ

This morning I was in the Israeli office of some small start-up company from Seattle to listen to Pavel Yosifovich from "Hi-Tech College". The guy showed some interesting stuff about C# 3. Some of them are well known, the others are less known and very useful.

· Implicitly Typed Local Variables

Just like in javascript :
var keys = data.Keys; // Dictionary.KeyCollection

· Automatic Properties

public class Person { // C# 3.0

public string FirstName { get; set; }

public string LastName { get; set; }

public int Age { get; set; }

}

· Object Initializers

Person p = new Person() {

FirstName = "Bart", LastName = "Simpson", Age = 12

};

· Collection Initializers

var people = new List() {

new Person() { FirstName = "Bart", LastName = "Simpson", Age = 12 },

new Person() { FirstName = "Clark", LastName = "Kent", Age = 35 },

new Person() { FirstName = "Peter", LastName = "Parker", Age = 30 }

};

· Anonymous Types

ar people = new[] {

new { FirstName = "Clark", LastName = "Kent", Age = 36 },

new { FirstName = "Peter", LastName = "parker", Age = 26 },

new { FirstName = "Bart", LastName = "Simpson", Age = 11 }

};

foreach (var i in people)

Console.WriteLine("{0} {1} ({2})", i.FirstName, i.LastName, i.Age);

· And my favorite : Extension Methods

public static class MyExtensions {

public static string UpperLower(this string str, bool upperFirst) {

StringBuilder newString = new StringBuilder(str.Length);

for (int i = 0; i <>

newString.Append(upperFirst ? char.ToUpper(str[i]) :

char.ToLower(str[i]));

upperFirst = !upperFirst;

}

return newString.ToString();

}

}

…..

string input = Console.ReadLine();

Console.WriteLine("calling extension method for {0}: {1}", input,

input.UpperLower(true));

all those things look nice and handy , but they become really usefull when we are talking about LINQ :

The LINQ Project is a codename for a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities.

.. and what does it mean ?

Does it mean that we can finally quit using SQL. We are programmes! We know objects and hate sql!!!

This is how the famous Northwind database looks like

..each box ,is translated to a class and then translated to something like this

[Table(Name="dbo.Categories")]

public partial class Category : INotifyPropertyChanging, INotifyPropertyChanged

{

private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

private int _CategoryID;

private string _CategoryName;

private string _Description;

private System.Data.Linq.Binary _Picture;

private EntitySet _Products;

#region Extensibility Method Definitions

partial void OnLoaded();

partial void OnValidate(System.Data.Linq.ChangeAction action);

partial void OnCreated();

partial void OnCategoryIDChanging(int value);

partial void OnCategoryIDChanged();

partial void OnCategoryNameChanging(string value);

partial void OnCategoryNameChanged();

partial void OnDescriptionChanging(string value);

partial void OnDescriptionChanged();

partial void OnPictureChanging(System.Data.Linq.Binary value);

partial void OnPictureChanged();

#endregion

public Category()

{

this._Products = new EntitySet(new Action(this.attach_Products), new Action(this.detach_Products));

OnCreated();

}

and finally might be used like this

NorthwindDataContext ctx = new NorthwindDataContext();

ctx.DeferredLoadingEnabled = false;

DataLoadOptions opts = new DataLoadOptions();

opts.LoadWith(c => c.Products);

ctx.LoadOptions = opts;

-------

var categories = from c in ctx.Categories

select c;

foreach (var category in categories) {

Console.WriteLine(category.Products.Count());

}

var result = ctx.SalesByCategory("Beverages", "1998");

foreach(var r in result)

Console.WriteLine("{0}, {1}", r.ProductName, r.TotalPurchase);

Hibernate Unique Ids

It seems that hibernate does not really like working with unique ids in the database (we experiemented it when using the embedded version in Gigaspaces).

A way to overcome the issue, is adding a new field to the database, which will be used as unique field, and its value will be generated on the fly in the application level.

The unique value can be generated using the relevant API (GUID in MS) and UUID in Java:

import java.util.UUID;

public class GenerateUUID {
public static final void main(String... aArgs) {
//generate random UUIDs
UUID idOne = UUID.randomUUID();
UUID idTwo = UUID.randomUUID();
log("UUID One: " + idOne);
log("UUID Two: " + idTwo);
}
private static void log(Object aObject){
System.out.println( String.valueOf(aObject) );
}
}

Hibernate: Log SQL Statements

It's very useful when debbuging Hiberante based system to be aware of the SQL output that is being sent to the database (like the old phrase Garbage In Garbage Out)
There are two methods to do that:
1. Enable hiberante logging
2. Enable the RDBMS log files, for example: Enable MySQL SQL logging

SVN best practices and branching

Subversion Best Practices