Hi,
I coded a simple access to routines written in Cach�, to perform updates of
datasets.
If you are interested, see the code below, or contact me directly.
I hope it will help.
Regards
JM
Cach� side: a "portal" class named CGDispatch:
Class BSC.CGDispatch Extends %RegisteredObject [ ProcedureBlock ]
{
(four methods are found: CGInsertCmd to insert a new object, CGUpdate,
CGDelete and CGExecuteCmd to execute stored procs)
ClassMethod CGExecuteCmd(cacheBSCClass, cmd, msgIn As %String(MAXLEN=65536),
Output msgOut As %String(MAXLEN=65536)) [ SqlProc ]
{
s Z1=$ZH
do
##class(UsrJournal.Log).FAppend("class="_cacheBSCClass_";cmd="_cmd_";msgIn="
_msgIn,"CG",9)
s cacheError="" //error code (format is code;description)
s rets="" // return multidimensional array that will be converted to a
string
s sqlErrorCode=0 //SQL error to be returned
s $zt="CGEC"
//de-serialisation of arguments in a multidimensional in the form of
hashtable: args( mykey ) = myvalue
s i=1
s s1=$P(msgIn,$C(31),1)
while (s1'="")
{
s args($P(s1,$C(28),1))=$P(s1,$C(28),2)
s i=i+1
s s1=$P(msgIn,$C(31),i)
}
//call of the method
//be careful to call an existing method, but a catch prevents any crash
s cacheError=$zobjclassmethod(cacheBSCClass,cmd,.args,.rets)
CGEC2
s $zt=""
//append error code and description in the return string
i (cacheError="") s cacheError="0;"
s errorCode=$P(cacheError,";",1), errorDesc=$P(cacheError,";",2)
s
msgOut="ERROR_CODE"_$C(28)_errorCode_$C(31)_"ERROR_DESC"_$C(28)_errorDesc_$C
(31)
//serialise return hashtable in a string
//note the separators to separe pairs and to to separe key and value
//this has to be interpreted by .NET
s i=$O(rets(""))
while (i'="")
{
s msgOut=msgOut_i_$C(28)_rets(i)_$C(31)
s i=$O(rets(i))
}
if ($L(msgOut)>0) { s msgOut=$E(msgOut,1,$L(msgOut)-1) }
if (errorCode<0) s sqlErrorCode=-460 // General error
// Return of %SQLProcContext
If ($g(%sqlcontext)'=$$$NULLOREF)
{
Set %sqlcontext.SQLCode=sqlErrorCode
If (sqlErrorCode'=0)
{
// if an error occured, we also add it to the ODBC error message
(throw an informative exception in .NET)
Set %msg=cacheError
}
do ##class(UsrJournal.Log).FAppend("fin:"_(($ZH-Z1)*1000)_",
msgOut="_msgOut,"CG",9)
q
CGEC
do ##class(UsrJournal.Log).Append("Trap CG
Command","cacheBSCClass,cmd,msgIn",8,"CG")
s cacheError=^CGErrorDefs(-100)
goto CGEC2
}
}
The .NET side (full source including DataSet fill/update):
using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.IO;
namespace MPL.Common.Business.DataConnection
{
public class CommanderBSDAC
{
protected OdbcDataAdapter m_DataAdapter=null;
protected OdbcCommand m_ExecuteCmd=null;
protected const int MAX_BUFFER_SIZE=65536;
protected OdbcConnection m_Conn=null;
protected string m_ClsName;
protected string m_SessionId="";
#endregion
#region Constructeur
/// <summary>
/// CommanderBSDAC constructor
/// </summary>
/// <param name="session">client side session ID (used to authenticate
access)</param>
public CommanderBSDAC(string session)
{
m_SessionId=session;
m_DataAdapter=new OdbcDataAdapter();
Init();
}
#endregion
#region Generic commands
/// <summary>
/// Call of stored procedure
/// </summary>
/// <param name="clsName">Cache classname containing the method to
call</param>
/// <param name="cmd">method name</param>
/// <param name="htParametersIn">hashtable containing the parameters (key
and value)</param>
/// <param name="htParametersOut">returned hashtable</param>
/// <param name="htKeyTypOut">hashtable containing same keys as in
htParametersOut with values containing the data type. this allows to
directly retrieve typed data</param>
public void CGExecuteCmd(string clsName,string cmd,Hashtable
htParametersIn,
out Hashtable htParametersOut,Hashtable htKeyTypOut)
{
//include current session
htParametersIn["SESSIONID"]=m_SessionId;
if (htKeyTypOut!=null)
{
htKeyTypOut["SESSIONID"]=typeof(string);
htKeyTypOut["ERROR_CODE"]=typeof(long);
htKeyTypOut["ERROR_DESC"]=typeof(string);
}
SetClsName(clsName);
string s_in=ParamsFormatter.Serialize(htParametersIn);
OdbcCommand execute_cmd=m_ExecuteCmd;
execute_cmd.Parameters["clsName"].Value=clsName;
execute_cmd.Parameters["cmd"].Value=cmd;
execute_cmd.Parameters["msgIn"].Value=s_in;
using(m_Conn)
{
m_Conn.Open();
execute_cmd.ExecuteNonQuery();
m_Conn.Close();
}
string s_out="";
s_out=(string)execute_cmd.Parameters["msgOut"].Value;
htParametersOut=ParamsFormatter.Unserialize(s_out,htKeyTypOut);
}
/// <summary>
/// Update a Cache class with a DiffGram DataSet
/// </summary>
/// <param name="clsName">Nom de la classe Cach�. Ex: Mpl.Natures</param>
/// <param name="ds">DataSet de changement</param>
public void CGUpdateDs(string tblName,string clsName,DataSet ds)
{
SetClsName(clsName);
using(m_Conn)
{
m_DataAdapter.Update(ds,tblName);
}
}
/// <summary>
/// Fill a DataSet using a query in Cach�
/// </summary>
/// <param name="qryName"></param>
/// <param name="htParametersIn">Param�tres d'appel de la commande</param>
/// <param name="ds"></param>
public void CGFillDs(string tblName,string qryName,Hashtable
htParametersIn,DataSet ds)
{
string qry_params="";
OdbcCommand cmd=new OdbcCommand();
OdbcParameterCollection parameters=cmd.Parameters;
if(htParametersIn!=null)
{
int n=htParametersIn.Count;
string[] s_arr=new string[n];
int i=0;
foreach(string s_key in htParametersIn.Keys)
{
OdbcParameter
param=parameters.Add(s_key,OdbcType.Char,MAX_BUFFER_SIZE,s_key);
param.Direction=ParameterDirection.Input;
param.Value=(string)htParametersIn[s_key];
s_arr[i++]="?";
}
qry_params=string.Join(",",s_arr);
}
else
{
qry_params="";
}
string qry="{CALL "+qryName+"("+qry_params+")}";
OdbcDataAdapter da=new OdbcDataAdapter();
cmd.Connection=new OdbcConnection("DSN=MPL");
cmd.CommandText=qry;
cmd.CommandType=CommandType.StoredProcedure;
da.SelectCommand=cmd;
using(cmd.Connection)
{
da.Fill(ds,tblName);
}
}
#endregion
#region Initialisation of connection and commands
private void Init()
{
InitHandlers();
InitCommands();
InitConnection();
}
private void InitHandlers()
{
m_DataAdapter.RowUpdating+=new
OdbcRowUpdatingEventHandler(RowUpdatingEventHandler);
m_DataAdapter.RowUpdated+=new
OdbcRowUpdatedEventHandler(RowUpdatedEventHandler);
}
private void InitConnection()
{
m_Conn=new OdbcConnection("DSN=MPL");
m_DataAdapter.InsertCommand.Connection=m_Conn;
m_DataAdapter.UpdateCommand.Connection=m_Conn;
m_DataAdapter.DeleteCommand.Connection=m_Conn;
m_ExecuteCmd.Connection=m_Conn;
}
private void SetClsName(string clsName)
{
m_ClsName=clsName;
}
private void InitCommands()
{
////////////////////////////////////////////
/// Init commande INSERT
////////////////////////////////////////////
OdbcCommand cmd=new OdbcCommand();
cmd.CommandType=CommandType.StoredProcedure;
OdbcParameterCollection parameters=cmd.Parameters;
parameters.Clear();
OdbcParameter
p=parameters.Add("clsName",OdbcType.Char,MAX_BUFFER_SIZE,"clsName");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgIn",OdbcType.Char,MAX_BUFFER_SIZE,"msgIn");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgOut",OdbcType.Char,MAX_BUFFER_SIZE,"msgOut");
p.Direction=ParameterDirection.Output;
cmd.CommandText="{CALL BSC.CGDispatch_CGInsertCmd(?,?,?)}";
cmd.CommandType=CommandType.StoredProcedure;
m_DataAdapter.InsertCommand=cmd;
////////////////////////////////////////////
/// Init commande UPDATE
////////////////////////////////////////////
cmd=new OdbcCommand();
parameters=cmd.Parameters;
parameters.Clear();
p=parameters.Add("clsName",OdbcType.Char,MAX_BUFFER_SIZE,"clsName");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgIn",OdbcType.Char,MAX_BUFFER_SIZE,"msgIn");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgOut",OdbcType.Char,MAX_BUFFER_SIZE,"msgOut");
p.Direction=ParameterDirection.Output;
cmd.CommandText="{CALL BSC.CGDispatch_CGUpdateCmd(?,?,?)}";
cmd.CommandType=CommandType.StoredProcedure;
m_DataAdapter.UpdateCommand=cmd;
////////////////////////////////////////////
/// Init commande DELETE
////////////////////////////////////////////
cmd=new OdbcCommand();
parameters=cmd.Parameters;
parameters.Clear();
p=parameters.Add("clsName",OdbcType.Char,MAX_BUFFER_SIZE,"clsName");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgIn",OdbcType.Char,MAX_BUFFER_SIZE,"msgIn");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgOut",OdbcType.Char,MAX_BUFFER_SIZE,"msgOut");
p.Direction=ParameterDirection.Output;
cmd.CommandText="{CALL BSC.CGDispatch_CGDeleteCmd(?,?,?)}";
cmd.CommandType=CommandType.StoredProcedure;
m_DataAdapter.DeleteCommand=cmd;
////////////////////////////////////////////
/// Init commande EXECUTECMD
////////////////////////////////////////////
m_ExecuteCmd=new OdbcCommand();
parameters=m_ExecuteCmd.Parameters;
parameters.Clear();
p=parameters.Add("clsName",OdbcType.Char,MAX_BUFFER_SIZE,"clsName");
p.Direction=ParameterDirection.Input;
p=parameters.Add("cmd",OdbcType.Char,MAX_BUFFER_SIZE,"cmd");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgIn",OdbcType.Char,MAX_BUFFER_SIZE,"msgIn");
p.Direction=ParameterDirection.Input;
p=parameters.Add("msgOut",OdbcType.Char,MAX_BUFFER_SIZE,"msgOut");
p.Direction=ParameterDirection.Output;
m_ExecuteCmd.CommandText="{CALL BSC.CGDispatch_CGExecuteCmd(?,?,?,?)}";
m_ExecuteCmd.CommandType=CommandType.StoredProcedure;
}
#endregion
#region DataSet update callbacks
//transfer one Dataset row to the hastable
private void RowUpdatingEventHandler(object sender,
OdbcRowUpdatingEventArgs e)
{
DataRow row=e.Row;
Hashtable ht_parameters=new Hashtable();
//inclut la session en cours
ht_parameters["SESSIONID"]=m_SessionId;
foreach(DataColumn dc in row.Table.Columns)
{
object obj=null;
if(row.RowState==DataRowState.Deleted)
{
obj=row[dc,DataRowVersion.Original];
}
else
{
obj=row[dc];
}
string key=dc.ColumnName;
ht_parameters[key]=obj;
}
// serialize
string s=ParamsFormatter.Serialize(ht_parameters);
OdbcCommand cmd=e.Command;
cmd.Parameters["msgIn"].Value=s;
cmd.Parameters["clsName"].Value=m_ClsName;
}
//transfer one hashtable to a DataSet row
private void RowUpdatedEventHandler(object sender, OdbcRowUpdatedEventArgs
e)
{
// Q313540
if(e.StatementType==StatementType.Insert)
{
e.Status=UpdateStatus.SkipCurrentRow;
}
/*
* 1�) Initialize the parameters types table
*/
Hashtable ht_key_typ=new Hashtable();
DataRow row=e.Row;
/*
* Pour chaque colonne, copier le type de la colonne dans
* le type de param�tre.
*/
foreach(DataColumn dc in row.Table.Columns)
{
string key=dc.ColumnName;
ht_key_typ[key]=dc.DataType;
}
ht_key_typ["ERROR_CODE"]=typeof(long);
ht_key_typ["ERROR_DESC"]=typeof(string);
ht_key_typ["SESSIONID"]=typeof(string);
/*
* 2�) D�s�rialiser msgOut.
* 3�) Pour chaque colonne assigner la valeur trouv�e dans msgOut.
*/
OdbcCommand cmd=e.Command;
if(cmd.Parameters["msgOut"].Value!=null)
{
string s=(string)cmd.Parameters["msgOut"].Value;
Hashtable ht_parameters=ParamsFormatter.Unserialize(s,ht_key_typ);
foreach(DataColumn dc in row.Table.Columns)
{
string key=dc.ColumnName;
if(ht_parameters.ContainsKey(key))
{
if(ht_parameters.ContainsKey(key))
{
row[key]=ht_parameters[key];
}
else
{
row[key]=string.Format("CommanderBSDACError:RowUpdatedEventHandler:N/A Key
in ht_parameters='{0}'",key);
}
}
}
}
if (e.Errors!=null) throw(e.Errors);
}
#endregion
}
}
