using IBatisNet.DataMapper;

using System.Reflection;

using System.IO;

using IBatisNet.DataMapper.Configuration;

using IBatisNet.DataMapper.Configuration.Statements;

using IBatisNet.DataMapper.MappedStatements;

using IBatisNet.DataMapper.Scope;

using System.Data;

using IBatisNet.Common;

using System;

namespace SQLMaps

{

public class SQLHelper

{

public static ISqlMapper SqlMap;

private static readonly object syncObj = new object ();

/// <summary>

/// ISqlMapper对象生成

/// </summary>

static SQLHelper()

{

if (SqlMap == null )

{

lock (syncObj)

{

if (SqlMap == null )

{

Assembly asembly = Assembly.Load( " SQLMaps " );

Stream stream = asembly.GetManifestResourceStream( " SQLMaps.sqlmap.config " ); // 得到xml数据流

DomSqlMapBuilder builder = new DomSqlMapBuilder();

SqlMap = builder.Configure(stream);

}

}

}

}

/// <summary>

/// 返回DataSet数据集

/// </summary>

public static DataSet QueryForDataSet( string tag, object paramObject)

{

DataSet ds = new DataSet();

IDbCommand command = GetDbCommand(tag,paramObject);

SqlMap.LocalSession.CreateDataAdapter(command).Fill(ds);

return ds;

}

/// <summary>

/// 返回DataTable数据集

/// </summary>

public static DataTable QueryForDataTable( string tag, object paramObject)

{

return QueryForDataSet(tag, paramObject).Tables[ 0 ];

}

/// <summary>

/// 用于分页的DataTable数据集

/// </summary>

public static DataTable QueryForDataTable( string tag, object paramObject, int PageSize, int curPage, out int recCount)

{

IDataReader dr = null ;

bool isSessionLocal = false ;

string sql = QueryForSql(tag,paramObject);

string strCount = " select count(*) " + sql.Substring(sql.ToLower().IndexOf( " from " ));

IDalSession session = SqlMap.LocalSession;

DataTable dt = new DataTable();

if (session == null )

{

session = new SqlMapSession(SqlMap);

session.OpenConnection();

isSessionLocal = true ;

}

try

{

IDbCommand cmdCount = GetDbCommand(tag, paramObject);

cmdCount.Connection = session.Connection;

cmdCount.CommandText = strCount;

object count = cmdCount.ExecuteScalar();

recCount = Convert.ToInt32(count);

IDbCommand cmd = GetDbCommand(tag, paramObject);

cmd.Connection = session.Connection;

dr = cmd.ExecuteReader();

dt = QueryForPaging(dr, PageSize, curPage);

}

finally

{

if (isSessionLocal)

{

session.CloseConnection();

}

}

return dt;

}

/// <summary>

/// 获取数据

/// </summary>

protected static DataTable QueryForPaging(IDataReader dataReader, int PageSize, int curPage)

{

DataTable dt = new DataTable();

int colCount = dataReader.FieldCount;

for ( int i = 0 ; i < colCount; i ++ )

{

dt.Columns.Add( new DataColumn(dataReader.GetName(i),dataReader.GetFieldType(i)));

}

// 读取数据，将DataReader中的数据读取到DataTable中去

object [] vald = new object [colCount];

int iCount = 0 ; // 临时记录变量

while (dataReader.Read())

{

// 当前记录在当前页记录范围内

if (iCount >= PageSize * (curPage - 1 ) && iCount < PageSize * curPage)

{

for ( int i = 0 ; i < colCount; i ++ )

{

vald[i] = dataReader.GetValue(i);

}

dt.Rows.Add(vald);

}

else if (iCount > PageSize * curPage)

{

break ;

}

iCount ++ ;

}

if ( ! dataReader.IsClosed)

{

dataReader.Close();

dataReader.Dispose();

}

return dt;

}

}

}

public IList < Person > GetAllPerson()

{

return SqlMap.QueryForList < Person > ( " SelectAllPerson " , null );

}

public DataTable GetAllPerson2()

{

int recCount = 0 ;

DataTable dt = SQLHelper.QueryForDataTable( " SelectAllPerson2 " , new Person { FirstName = " kilo " }, 10 , 1 , out recCount);

return dt;

}

public DataTable GetAllPerson3()

{

return SQLHelper.QueryForDataTable( " SelectAllPerson2 " , new Person { FirstName = " kilo " });

}

public Person GetPerson( int id)

{

return SqlMap.QueryForObject < Person > ( "" ,id);

}

public int Create(Person person)

{

int Id = ( int )SqlMap.Insert( " InsertPerson " ,person);

SqlMap.CommitTransaction();

return Id;

}

public void Update(Person person)

{

SqlMap.Update( " UpdatePerson " ,person);

}

public void Delete(Person person)

{

SqlMap.Delete( " DeletePerson " ,person);

}

<? xml version="1.0" encoding="utf-8" ?>

< sqlMap namespace ="Person" xmlns ="http://ibatis.apache.org/mapping" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" >

< alias >

< typeAlias alias ="Person" type ="Model.Person,Model" />

</ alias >

< resultMaps >

< resultMap id ="SelectAllResult" class ="Person" >

< result property ="Id" column ="PER_ID" />

< result property ="FirstName" column ="PER_FIRST_NAME" />

< result property ="LastName" column ="PER_LAST_NAME" />

< result property ="BirthDate" column ="PER_BIRTH_DATE" />

< result property ="WeightInKilograms" column ="PER_WEIGHT_KG" />

< result property ="HeightInMeters" column ="PER_HEIGHT_M" />

</ resultMap >

</ resultMaps >

< statements >

< select id ="SelectAllPerson" resultMap ="SelectAllResult" >

select

PER_ID,

PER_FIRST_NAME,

PER_LAST_NAME,

PER_BIRTH_DATE,

PER_WEIGHT_KG,

PER_HEIGHT_M

from PERSON

</ select >

< select id ="SelectAllPerson2" resultMap ="SelectAllResult" >

select

PER_ID AS Id,

PER_FIRST_NAME AS FirstName,

PER_LAST_NAME AS LastName,

PER_BIRTH_DATE AS BirthDate,

PER_WEIGHT_KG AS WeightInKilograms,

PER_HEIGHT_M AS HeightInMeters

from PERSON

< dynamic prepend ="WHERE" >

< isParameterPresent >

PER_FIRST_NAME LIKE #FirstName#+'%'

</ isParameterPresent >

</ dynamic >

</ select >

< select id ="SelectByPersonId" resultClass ="Person" parameterClass ="int" >

select

PER_ID,

PER_FIRST_NAME,

PER_LAST_NAME,

PER_BIRTH_DATE,

PER_WEIGHT_KG,

PER_HEIGHT_M

from PERSON

where PER_ID = #value#

</ select >

< insert id ="InsertPerson" parameterclass ="Person" >

< selectKey property ="Id" type ="post" resultClass ="int" >

${selectKey}

</ selectKey >

insert into Person

( PER_FIRST_NAME,

PER_LAST_NAME,

PER_BIRTH_DATE,

PER_WEIGHT_KG,

PER_HEIGHT_M)

values

(#FirstName#,#LastName#,#BirthDate#, #WeightInKilograms#, #HeightInMeters#)

</ insert >

< update id ="UpdatePerson"

parameterclass ="Person" >

<![CDATA[ update Person set

PER_FIRST_NAME =#FirstName#,

PER_LAST_NAME =#LastName#,

PER_BIRTH_DATE =#BirthDate#,

PER_WEIGHT_KG=#WeightInKilograms#,

PER_HEIGHT_M=#HeightInMeters#

where

PER_ID = #Id#　 ]]>

</ update >

< delete id ="DeletePerson" parameterclass ="Person" >

delete from Person

where

PER_ID = #Id#

</ delete >

</ statements >

</ sqlMap >