Seasar - DI Container with AOP -

S2Container.NET TOP page

S2Container.NET S2ADO

S2ADO was created to enable developers to freely execute SQL commands without the complexity of using ADO.NET.

Data Manipulation

Query(BeanList)
Query(Bean)
Query(DictionaryList)
Query(Dictionary)
Update

NULL Handling
Parameter Marker(@, :, ?)

Data Manipulation

Query(BeanList)

Let's try querying several objects. To query, BasicSelectHandler is used. It has the following properties:

Seasar.Extension.ADO.Impl.BasicSelectHandler

Property	Description	S2Container Example
DataSource	DataSource	Set normally by default
Sql	SQL command to execute	"SELECT * FROM emp"
DataReaderHandler	Handler class to process System.Data.IDataReader	To return several objects, BeanListDataReaderHandler is used. <component class="Seasar.Extension.ADO.Impl.BeanListDataReaderHandler"> <arg>Seasar.Examples.Reference.ADO.Employee</arg> </component>
CommandTimeout	Time in seconds before command is executed. Set to -1 to disable.

Seasar.Examples.Reference.ADO.SelectBeanList.dicon

<components>
  <include path="Seasar.Examples/Ado.dicon" />
  <component name="SelectBeanListClient" class="Seasar.Examples.Reference.ADO.SelectBeanListClient"/>
  <component name="SelectBeanListHandler" class="Seasar.Extension.ADO.Impl.BasicSelectHandler">
    <property name="Sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp"</property>
    <property name="DataReaderHandler">
      <component class="Seasar.Extension.ADO.Impl.BeanListDataReaderHandler">
        <arg>Seasar.Examples.Reference.ADO.Employee</arg>
      </component>
    </property>
  </component>
</components>

Seasar.Examples.Reference.ADO.SelectBeanListClient

using System;
using System.Collections;
using Seasar.Extension.ADO;
using Seasar.Framework.Container;
using Seasar.Framework.Container.Factory;

namespace Seasar.Examples.Reference.ADO
{
    public class SelectBeanListClient
    {
        private const string PATH = "Seasar.Examples/Reference/ADO/SelectBeanList.dicon";

        public void Main()
        {
            IS2Container container = S2ContainerFactory.Create(PATH);
            container.Init();
            try
            {
                ISelectHandler handler = (ISelectHandler) container.GetComponent("SelectBeanListHandler");
                IList result = (IList) handler.Execute(null);
                for (int i = 0; i < result.Count; ++i)
                {
                    Console.Out.WriteLine(result[i]);
                }
            }
            finally
            {
                container.Destroy();
            }
        }
    }
}

Result:

DEBUG 2006-06-19 23:26:59,890 [9] Logical connection opened
DEBUG 2006-06-19 23:26:59,984 [9] Logical connection closed
7369, SMITH, CLERK, 7902, 1980/12/17 0:00:00, 800, , 20, 
7499, ALLEN, SALESMAN, 7698, 1981/02/20 0:00:00, 1600, 300, 30, 
7521, WARD, SALESMAN, 7698, 1981/02/22 0:00:00, 1250, 500, 30, 
7566, JONES, MANAGER, 7839, 1981/04/02 0:00:00, 2975, , 20, 
7654, MARTIN, SALESMAN, 7698, 1981/09/28 0:00:00, 1250, 1400, 30, 
7698, BLAKE, MANAGER, 7839, 1981/05/01 0:00:00, 2850, , 30, 
7782, CLARK, MANAGER, 7839, 1981/06/09 0:00:00, 2450, , 10, 
7788, SCOTT, ANALYST, 7566, 1982/12/09 0:00:00, 3000, , 20, 
7839, KING, PRESIDENT, , 1981/11/17 0:00:00, 5000, , 10, 
7844, TURNER, SALESMAN, 7698, 1981/09/08 0:00:00, 1500, 0, 30, 
7876, ADAMS, CLERK, 7788, 1983/01/12 0:00:00, 1100, , 20, 
7900, JAMES, CLERK, 7698, 1981/12/03 0:00:00, 950, , 30, 
7902, FORD, ANALYST, 7566, 1981/12/03 0:00:00, 3000, , 20, 
7934, MILLER, CLERK, 7782, 1982/01/23 0:00:00, 1300, , 10,

If there is a bind variable, it is specified as the parameter to the Execute(object[]). Since there isn't any bind variable in this example, parameter is set to null. S2Container will construct the components so it is only necessary to invoke the Execute() method. If there is an underscore (_) included in the column name, it is mapped to a property whose name is the column name without the underscore character.

Query(Bean)

To just query 1 object, BasicSelectHandler is used in a similar manner as in the Query(BeanList) except BeanDataReaderHandler is used and an object is returned instead of a list.

Seasar.Examples.Reference.ADO.SelectBean.dicon

<components>
  <include path="Seasar.Examples/Ado.dicon" />
  <component name="SelectBeanClient" class="Seasar.Examples.Reference.ADO.SelectBeanClient"/>
  <component name="SelectBeanHandler" class="Seasar.Extension.ADO.Impl.BasicSelectHandler">
    <property name="Sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, 
deptno FROM emp WHERE empno = @empno"</property>
    <property name="DataReaderHandler">
      <component class="Seasar.Extension.ADO.Impl.BeanDataReaderHandler">
        <arg>Seasar.Examples.Reference.ADO.Employee</arg>
      </component>
    </property>
  </component>
</components>

Seasar.Examples.Reference.ADO.SelectBeanClient

using System;
using Seasar.Extension.ADO;
using Seasar.Framework.Container;
using Seasar.Framework.Container.Factory;

namespace Seasar.Examples.Reference.ADO
{
    public class SelectBeanClient
    {
        private const string PATH = "Seasar.Examples/Reference/ADO/SelectBean.dicon";

        public void Main()
        {
            IS2Container container = S2ContainerFactory.Create(PATH);
            container.Init();
            try
            {
                ISelectHandler handler = (ISelectHandler) container.GetComponent("SelectBeanHandler");
                Employee result = (Employee) handler.Execute(new object[] { 7788 });
                Console.Out.WriteLine(result);
            }
            finally
            {
                container.Destroy();
            }
        }
    }
}

Result:

DEBUG 2006-06-19 23:34:54,234 [9] Logical connection opened
DEBUG 2006-06-19 23:34:54,249 [9] Logical connection closed
7788, SCOTT, ANALYST, 7566, 1982/12/09 0:00:00, 3000, , 20,

Query(DictionaryList)

Several Dictinary may be retrieved. DictionaryListDataReaderHandler is used as a DataReaderHandler.

Seasar.Examples.Reference.ADO.SelectDictionaryList.dicon

<components>
  <include path="Seasar.Examples/Ado.dicon" />
  <component name="SelectDictionaryListClient" 
class="Seasar.Examples.Reference.ADO.SelectDictionaryListClient"/>
  <component name="SelectDictionaryListHandler" 
class="Seasar.Extension.ADO.Impl.BasicSelectHandler">
    <property name="Sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp"</property>
    <property name="DataReaderHandler">
      <component class="Seasar.Extension.ADO.Impl.DictionaryListDataReaderHandler"/>
    </property>
  </component>
</components>

Seasar.Examples.Reference.ADO.SelectDictionaryListClient

using System;
using System.Collections;
using Seasar.Extension.ADO;
using Seasar.Framework.Container;
using Seasar.Framework.Container.Factory;

namespace Seasar.Examples.Reference.ADO
{
    public class SelectDictionaryListClient
    {
        private const string PATH = "Seasar.Examples/Reference/ADO/SelectDictionaryList.dicon";

        public void Main()
        {
            IS2Container container = S2ContainerFactory.Create(PATH);
            container.Init();
            try
            {
                ISelectHandler handler = 
                    SelectHandler) container.GetComponent("SelectDictionaryListHandler");
                IList result = (IList) handler.Execute(null);
                for (int i = 0; i < result.Count; ++i)
                {
                    Console.Out.WriteLine(DictionaryUtil.ToDecorateString((IDictionary) result[i]));
                }
            }
            finally
            {
                container.Destroy();
            }
        }
    }
}

Result:

DEBUG 2006-06-19 23:43:24,749 [9] Logical connection opened
DEBUG 2006-06-19 23:43:24,765 [9] Logical connection closed
ename=SMITH, hiredate=1980/12/17 0:00:00, comm=null, deptno=20, job=CLERK, empno=7369,
 sal=800.00, mgr=7902
ename=ALLEN, hiredate=1981/02/20 0:00:00, comm=300.00, deptno=30, job=SALESMAN, empno=7499,
 sal=1600.00, mgr=7698
ename=WARD, hiredate=1981/02/22 0:00:00, comm=500.00, deptno=30, job=SALESMAN, empno=7521,
 sal=1250.00, mgr=7698
ename=JONES, hiredate=1981/04/02 0:00:00, comm=null, deptno=20, job=MANAGER, empno=7566,
 sal=2975.00, mgr=7839
ename=MARTIN, hiredate=1981/09/28 0:00:00, comm=1400.00, deptno=30, job=SALESMAN, empno=7654,
 sal=1250.00, mgr=7698
ename=BLAKE, hiredate=1981/05/01 0:00:00, comm=null, deptno=30, job=MANAGER, empno=7698,
 sal=2850.00, mgr=7839
ename=CLARK, hiredate=1981/06/09 0:00:00, comm=null, deptno=10, job=MANAGER, empno=7782,
 sal=2450.00, mgr=7839
ename=SCOTT, hiredate=1982/12/09 0:00:00, comm=null, deptno=20, job=ANALYST, empno=7788,
 sal=3000.00, mgr=7566
ename=KING, hiredate=1981/11/17 0:00:00, comm=null, deptno=10, job=PRESIDENT, empno=7839,
 sal=5000.00, mgr=null
ename=TURNER, hiredate=1981/09/08 0:00:00, comm=0.00, deptno=30, job=SALESMAN, empno=7844,
 sal=1500.00, mgr=7698
ename=ADAMS, hiredate=1983/01/12 0:00:00, comm=null, deptno=20, job=CLERK, empno=7876,
 sal=1100.00, mgr=7788
ename=JAMES, hiredate=1981/12/03 0:00:00, comm=null, deptno=30, job=CLERK, empno=7900,
 sal=950.00, mgr=7698
ename=FORD, hiredate=1981/12/03 0:00:00, comm=null, deptno=20, job=ANALYST, empno=7902,
 sal=3000.00, mgr=7566
ename=MILLER, hiredate=1982/01/23 0:00:00, comm=null, deptno=10, job=CLERK, empno=7934,
 sal=1300.00, mgr=7782

Query(Dictionary)

Used to retrieve 1 Dictionary. DictionaryDataReaderHandler is used as a DataReaderHandler.

Seasar.Examples.Reference.ADO.SelectDictionary.dicon

<components>
  <include path="Seasar.Examples/Ado.dicon" />
  <component name="SelectDictionaryClient" class="Seasar.Examples.Reference.ADO.SelectDictionaryClient"/>
  <component name="SelectDictionaryHandler" class="Seasar.Extension.ADO.Impl.BasicSelectHandler">
    <property name="Sql">
"SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno 
FROM emp WHERE empno = @empno"</property>
    <property name="DataReaderHandler">
      <component class="Seasar.Extension.ADO.Impl.DictionaryDataReaderHandler"/>
    </property>
  </component>
</components>

Seasar.Examples.Reference.ADO.SelectDictionaryClient

using System;
using System.Collections;
using Seasar.Extension.ADO;
using Seasar.Framework.Container;
using Seasar.Framework.Container.Factory;

namespace Seasar.Examples.Reference.ADO
{
    public class SelectDictionaryClient
    {
        private const string PATH = "Seasar.Examples/Reference/ADO/SelectDictionary.dicon";

        public void Main()
        {
            IS2Container container = S2ContainerFactory.Create(PATH);
            container.Init();
            try
            {
                ISelectHandler handler =
                    (ISelectHandler) container.GetComponent("SelectDictionaryHandler");
                IDictionary result = (IDictionary) handler.Execute(new object[] { 7788 });
                Console.Out.WriteLine(DictionaryUtil.ToDecorateString(result));
            }
            finally
            {
                container.Destroy();
            }
        }
    }
}

Result:

DEBUG 2006-06-19 23:46:25,640 [9] Logical connection opened
DEBUG 2006-06-19 23:46:25,656 [9] Logical connection closed
ename=SCOTT, hiredate=1982/12/09 0:00:00, comm=null, deptno=20, job=ANALYST, empno=7788,
 sal=3000.00, mgr=7566

Update

BasicUpdateHandler is used in an update. It has the following properties:

Seasar.Extension.ADO.Impl.BasicUpdateHandler

Property	Description	S2Container Example
DataSource	DataSource	Set normally by default
Sq��	SQL command to execute	"UPDATE emp SET ename = @ename WHERE empno = @empno "

Seasar.Examples.Reference.ADO.Update.dicon

<components>
  <include path="Seasar.Examples/Ado.dicon" />
  <component name="UpdateClient" class="Seasar.Examples.Reference.ADO.UpdateClient"/>
  <component name="UpdateHandler" class="Seasar.Extension.ADO.Impl.BasicUpdateHandler">
    <property name="Sql">"UPDATE emp SET ename = @ename WHERE empno = @empno"</property>
  </component>
</components>

Seasar.Examples.Reference.ADO.UpdateClient

using System;
using Seasar.Extension.ADO;
using Seasar.Framework.Container;
using Seasar.Framework.Container.Factory;

namespace Seasar.Examples.Reference.ADO
{
    public class UpdateClient
    {
        private const string PATH = "Seasar.Examples/Reference/ADO/Update.dicon";

        public void Main()
        {
            IS2Container container = S2ContainerFactory.Create(PATH);
            container.Init();
            try
            {
                IUpdateHandler handler = (IUpdateHandler) container.GetComponent("UpdateHandler");
                int result = (int) handler.Execute(new object[] { "SCOTT", 7788 });
                Console.Out.WriteLine(result);
            }
            finally
            {
                container.Destroy();
            }
        }
    }
}

Result:

DEBUG 2006-06-19 23:55:46,999 [9] Logical connection opened
DEBUG 2006-06-19 23:55:47,031 [9] Logical connection closed
1

NULL Handling

When mapping NET Framework type to a database column, if there is a column with a value NULL, exception will be thrown because when .NET Framework type is of a System.Int32 construct, NULL is not possible.

To use NULL in a column, refer to the following sections:

Nullable in NHibernateContrib
Null in System.Data.SqlTypes
System.Nullable(.NET 2.0 or higher)

Following table shows the relationship with the above methods with the database datatypes.

System.Data.DbType	.NET Framework type	Nullables	System.Data.SqlTypes	System.Nullable
Boolean	Boolean	NullableBoolean	SqlBoolean	Nullable<Boolean>
Byte	Byte	NullableByte	SqlByte	Nullable<Byte>
Binary	Byte[]	NullableByte	SqlBinary	Nullable<Byte>
DateTime	DateTime	NullableDateTime	SqlDateTime	Nullable<DateTime>
Decimal	Decimal	NullableDecimal	SqlDecimal	Nullable<Decimal>
Double	Double	NullableDouble	SqlDouble	Nullable<Double>
Single	Single	NullableSingle	SqlSingle	Nullable<Single>
Guid	Guid	NullableGuid	SqlGuid	Nullable<Guid>
Int16	Int16	NullableInt16	SqlInt16	Nullable<Int16>
Int32	Int32	NullableInt32	SqlInt32	Nullable<Int32>
Int64	Int64	NullableInt64	SqlInt64	Nullable<Int64>
String	String	-	SqlString	-
Time	TimeSpan	-	-	Nullable<TimeSpan>
SByte	SByte	NullableSByte	-	Nullable<SByte>

Nullable in NHibernateContrib

To use Nullables in NHibernateContrib, add the following file (assemby) references.

s2container.net/lib/Nullables.dll

Example usage in a source file:

Import namespace Nullables
To check if it is a NULL, use the HasValue property
To retrieve a value, use the Value property

using System.Text;
using Nullables;

namespace Foo
{
    public class Bar
    {
        private NullableInt64 id;

        public NullableInt64 Id
        {
            get { return id; }
            set { id = value; }
        }

        private NullableBoolean boolType;

        public NullableBoolean BoolType
        {
            get { return boolType; }
            set { boolType = value; }
        }

        public override string ToString()
        {
            StringBuilder buf = new StringBuilder();
            buf.Append(id.HasValue ? id.Value.ToString() : "NULL").Append(", ");
            buf.Append(boolType.HasValue ? boolType.Value.ToString() : "NULL").Append(", ");
            return buf.ToString();
        }
    }
}

Null in System.Data.SqlTypes

Example usage in a source file:

Import namespace System.Data.SqlTypes
To check if it is a NULL, use the IsNull property
To retrieve a value, use the Value property

using System.Data.SqlTypes;
using System.Text;

namespace Foo
{
    public class Bar
    {
        private SqlInt64 id;

        public SqlInt64 Id
        {
            get { return id; }
            set { id = value; }
        }

        private SqlBoolean boolType;

        public SqlBoolean BoolType
        {
            get { return boolType; }
            set { boolType = value; }
        }

        public override string ToString()
        {
            StringBuilder buf = new StringBuilder();
            buf.Append(!id.IsNull ? id.Value.ToString() : "NULL").Append(", ");
            buf.Append(!boolType.IsNull ? boolType.Value.ToString() : "NULL").Append(", ");
            return buf.ToString();
        }
    }
}

System.Nullable(.NET 2.0 or higher)

.NET Framework 2.0 or higher supports Nullable datatype.

Example usage in a source file:

Import namespace System
To check if the value is a NULL, use the HasValue property
To retrieve a value, use the Value property

using System;
using System.Text;

namespace Foo
{
    public class Bar
    {
        private Nullable<Int64> id;

        public Nullable<Int64> Id
        {
            get { return id; }
            set { id = value; }
        }

        private Nullable<Boolean> boolType;

        public Nullable<Boolean> BoolType
        {
            get { return boolType; }
            set { boolType = value; }
        }

        public override string ToString()
        {
            StringBuilder buf = new StringBuilder();
            buf.Append(id.HasValue ? id.Value.ToString() : "NULL").Append(", ");
            buf.Append(boolType.HasValue ? boolType.Value.ToString() : "NULL").Append(", ");
            return buf.ToString();
        }
    }
}

Parameter Marker(@, :, ?)

Parameter markers that may be used in a SQL command depends on the ADO.NET data provider. As an example, SqlClient uses "@parmname", OracleClient uses ":parmname", OleDb uses "?", and Odbc uses "?".

S2ADO allows SQL commands to be independent of data provider by changing parameter markers depending on a data provider used.

Following is an example:

Data Provider	SQL command
(User's SQL command)	SELECT * FROM emp WHERE empno = @empno OR empno = :empno OR empno = ?
SqlClient	SELECT * FROM emp WHERE empno = @0 OR empno = @1 OR empno = @2
OracleClient	SELECT * FROM emp WHERE empno = :0 OR empno = :1 OR empno = :2
OleDb	SELECT * FROM emp WHERE empno = ? OR empno = ? OR empno = ?
Odbc	SELECT * FROM emp WHERE empno = ? OR empno = ? OR empno = ?

Note: Several types of parameter markers may be used in a SQL command but for readability, it is recommended to use one type.

Trans. by H.Ozawa