/// ------------------------------------------------------------------------------------
/// <summary>
/// Return a byte array read from the specified column
/// </summary>
/// <param name="odc">The odc.</param>
/// <param name="icol">ZERO-based column index.</param>
/// <returns>null or byte array</returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
/// ------------------------------------------------------------------------------------
public static byte[] ReadBytes(IOleDbCommand odc, int icol)
{
using (ArrayPtr prgch = MarshalEx.ArrayToNative(4000, typeof(byte)))
{
uint cbSpaceTaken;
bool fIsNull;
odc.GetColValue((uint)(icol + 1), prgch, prgch.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
return(null);
}
return((byte[])MarshalEx.NativeToArray(prgch, (int)cbSpaceTaken, typeof(byte)));
}
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Read the icol'th column from the current row as an integer. Return zero if the
/// value is null.
/// </summary>
/// <param name="odc">The odc.</param>
/// <param name="icol">ZERO-based column number</param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
/// ------------------------------------------------------------------------------------
public static int ReadInt(IOleDbCommand odc, int icol)
{
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
odc.GetColValue((uint)(icol + 1), rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
return(0);
}
return(IntFromStartOfUintArrayPtr(rgHvo));
}
}
/// <summary>
/// Execute the query sql, which contains zero or one string parameter whose value is
/// supplied as param (or null). The result is a single rowset, which may contain zero or
/// more rows, each containing cols columns, each an integer value. Read the entire rowset,
/// returning a List of int[cols] arrays.
/// </summary>
/// <param name="cache"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="cols"></param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
static public List <int[]> ReadIntArray(FdoCache cache, string sql, string param, int cols)
{
List <int[]> resultList = new List <int[]>();
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
while (fMoreRows)
{
int[] result = new int[cols];
for (int i = 0; i < cols; ++i)
{
bool fIsNull;
uint cbSpaceTaken;
odc.GetColValue((uint)(i + 1), rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (!fIsNull)
{
result[i] = IntFromStartOfUintArrayPtr(rgHvo);
}
}
resultList.Add(result);
odc.NextRow(out fMoreRows);
}
}
}
finally
{
ShutdownODC(ref odc);
}
return(resultList);
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Internals the read ints from command.
/// </summary>
/// <param name="cache">The cache.</param>
/// <param name="sql">The SQL.</param>
/// <param name="param">The param.</param>
/// <returns></returns>
/// ------------------------------------------------------------------------------------
protected virtual List <int> InternalReadIntsFromCommand(FdoCache cache, string sql, string param)
{
List <int> list = new List <int>();
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
while (fMoreRows)
{
odc.GetColValue(1, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (!fIsNull)
{
list.Add(IntFromStartOfUintArrayPtr(rgHvo));
}
odc.NextRow(out fMoreRows);
}
}
}
finally
{
ShutdownODC(ref odc);
}
return(list);
}
/// <summary>
/// Execute the query sql, which contains one required integer parameter whose value is
/// supplied as param. The result is a single rowset, which may contain zero or
/// more rows. Read an integer from each row and return them as a List.
/// </summary>
/// <param name="cache"></param>
/// <param name="sql"></param>
/// <param name="param">Required integer parameter.</param>
/// <returns>A List containing zero, or more, integers.</returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
static public List <int> ReadIntsFromCommand(FdoCache cache, string sql, int param)
{
List <int> list = new List <int>();
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
uint uintSize = (uint)Marshal.SizeOf(typeof(uint));
odc.SetParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
(ushort)DBTYPEENUM.DBTYPE_I4,
new uint[] { (uint)param },
uintSize);
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
while (fMoreRows)
{
odc.GetColValue(1, rgHvo, uintSize, out cbSpaceTaken, out fIsNull, 0);
if (!fIsNull)
{
list.Add(IntFromStartOfUintArrayPtr(rgHvo));
}
odc.NextRow(out fMoreRows);
}
}
}
finally
{
ShutdownODC(ref odc);
}
return(list);
}
/// <summary>
/// Execute the query sql, which may contain one string parameter whose value is
/// supplied as param. The result is a single rowset, which may contain one or
/// zero rows. If it contains zero rows, return 0.
/// If it contains (at least) one row, read a long from the first column
/// of the first row and return it. Return zero if it is null.
/// </summary>
/// <param name="cache">Cache to read from.</param>
/// <param name="sql"></param>
/// <param name="param">optional string parameter...if null, query needs none.</param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
public static long ReadOneLongFromCommand(FdoCache cache, string sql, string param)
{
IOleDbCommand odc = null;
try
{
odc = MakeRowSet(cache, sql, param);
bool fMoreRows;
odc.NextRow(out fMoreRows);
if (!fMoreRows)
{
return(0);
}
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr src = MarshalEx.ArrayToNative(1, typeof(long)))
{
odc.GetColValue((uint)(1), src, src.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
return(0);
}
// Unfortunately this produces a long with the bytes reversed.
ulong revVal = (ulong)Marshal.ReadInt64(src.IntPtr);
ulong result = 0;
for (int i = 0; i < 8; i++)
{
ulong b = (revVal >> i * 8) % 0x100;
result += b << ((7 - i) * 8);
}
return((long)result);
}
}
finally
{
ShutdownODC(ref odc);
}
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Return an array of strings given an SQL query.
/// <param name="cache">The cache in use</param>
/// <param name="qry">An SQL query to execute</param>
/// </summary>
/// ------------------------------------------------------------------------------------
public static string[] ReadMultiUnicodeTxtStrings(FdoCache cache, string qry)
{
StringCollection col = new StringCollection();
IOleDbCommand odc = null;
cache.DatabaseAccessor.CreateCommand(out odc);
try
{
uint cbSpaceTaken;
bool fMoreRows;
bool fIsNull;
uint uintSize = (uint)Marshal.SizeOf(typeof(uint));
odc.ExecCommand(qry, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
odc.NextRow(out fMoreRows);
while (fMoreRows)
{
using (ArrayPtr prgchName = MarshalEx.ArrayToNative(4000, typeof(char)))
{
odc.GetColValue(1, prgchName, prgchName.Size, out cbSpaceTaken, out fIsNull, 0);
byte[] rgbTemp = (byte[])MarshalEx.NativeToArray(prgchName, (int)cbSpaceTaken, typeof(byte));
col.Add(Encoding.Unicode.GetString(rgbTemp));
}
odc.NextRow(out fMoreRows);
}
}
finally
{
DbOps.ShutdownODC(ref odc);
}
string[] strings = new string[col.Count];
for (int i = 0; i < col.Count; ++i)
{
strings[i] = col[i];
}
return(strings);
}
/// <summary>
/// Execute the query sql, which optionally contains one string parameter whose value is
/// supplied as param. The result is a single rowset, which may contain zero or
/// more rows, each containing a pair of integers. The row set represents multiple
/// sequences. A sequence is defined by consecutive rows with the same value for the first
/// item. Each sequence is entered into the values dictionary, with the column 1 value
/// as the key, and a list of the column 2 values as the value.
/// Rows where either value is null or key is zero will be ignored.
/// </summary>
/// <param name="cache"></param>
/// <param name="sql"></param>
/// <param name="param">May be null, if not required.</param>
/// <param name="values"></param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
static public void LoadDictionaryFromCommand(FdoCache cache, string sql, string param, Dictionary <int, List <int> > values)
{
// As of 11/30/2006, all callers of this method are looking for reference sequence data,
// so this List of ints cannot be a set.
List <int> list = null;
IOleDbCommand odc = null;
try
{
cache.DatabaseAccessor.CreateCommand(out odc);
if (param != null)
{
odc.SetStringParameter(1, // 1-based parameter index
(uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISINPUT,
null, //flags
param,
(uint)param.Length); // despite doc, impl makes clear this is char count
}
odc.ExecCommand(sql, (int)SqlStmtType.knSqlStmtSelectWithOneRowset);
odc.GetRowset(0);
bool fMoreRows;
odc.NextRow(out fMoreRows);
bool fIsNull;
uint cbSpaceTaken;
int currentKey = 0;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
for (; fMoreRows; odc.NextRow(out fMoreRows))
{
int key, val;
odc.GetColValue(1, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
continue;
}
key = IntFromStartOfUintArrayPtr(rgHvo);
if (key == 0)
{
continue;
}
odc.GetColValue(2, rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
{
continue;
}
val = IntFromStartOfUintArrayPtr(rgHvo);
if (key != currentKey)
{
list = new List <int>();
currentKey = key;
values[currentKey] = list;
}
list.Add(val);
}
}
}
finally
{
ShutdownODC(ref odc);
}
}
/// <summary>
/// Update modified row or add a new one, but only if it is a custom field.
/// </summary>
public void UpdateDatabase()
{
// We do nothing for builtin fields or rows that have not been modified.
if (m_isDirty && IsCustomField)
{
String sqlCommand;
IOleDbCommand odc = null;
m_cache.DatabaseAccessor.CreateCommand(out odc);
try
{
// TODO: Maybe check for required columns for custom fields.
if (IsInstalled)
{
// Update (or delete) existing row.
if (m_doDelete)
{
sqlCommand = string.Format("DELETE FROM Field$ WITH (SERIALIZABLE) WHERE Id={0}",
m_id);
// TODO KenZ(RandyR): What should happen to the data, if any exists?
}
else
{
// Only update changeable fields.
// Id, Type, Class, Name, Custom, and CustomId are not changeable by
// the user, once they have been placed in the DB, so we won't
// update them here no matter what.
uint index = 1; // Current parameter index
sqlCommand = string.Format("UPDATE Field$ WITH (SERIALIZABLE)" +
" SET Min={0}, Max={1}, Big={2}, UserLabel={3}," +
" HelpString={4}, ListRootId={5}, WsSelector={6}, XmlUI={7}" +
" WHERE Id={8}",
AsSql(m_min), AsSql(m_max), AsSql(m_big), AsSql(m_userlabel, odc, ref index),
AsSql(m_helpString, odc, ref index), AsSql(m_listRootId), AsWSSql(m_wsSelector),
AsSql(m_xmlUI, odc, ref index), m_id);
}
odc.ExecCommand(sqlCommand, (int)SqlStmtType.knSqlStmtNoResults);
}
else
{
// ================ Added Start ===========================
// First use a stored procedure to determine what the Name field should
// be, passing in the UserLabel for possible/future use.
string sqlQuery = "declare @res nvarchar(400)"
+ " exec GenerateCustomName @res OUTPUT"
+ " select @res";
uint cbSpaceTaken;
bool fMoreRows;
bool fIsNull;
using (ArrayPtr rgchUsername = MarshalEx.ArrayToNative(100, typeof(char)))
{
odc.ExecCommand(sqlQuery,
(int)SqlStmtType.knSqlStmtStoredProcedure);
odc.GetRowset(0);
odc.NextRow(out fMoreRows);
// odc.GetColValue calls are all 1-based... (post error comment)
odc.GetColValue(1, rgchUsername, rgchUsername.Size, out cbSpaceTaken, out fIsNull,
0);
byte[] rgbTemp = (byte[])MarshalEx.NativeToArray(rgchUsername,
(int)cbSpaceTaken, typeof(byte));
m_name = Encoding.Unicode.GetString(rgbTemp);
}
// ================ Added End ===========================
// Note: There is no need to worry about deletion, as this one isn't in
// the DB. Make new row in DB.
// Use SP to create the new one: .
uint uintSize = (uint)Marshal.SizeOf(typeof(uint));
uint index = 1;
odc.SetParameter(index++, (uint)DBPARAMFLAGSENUM.DBPARAMFLAGS_ISOUTPUT,
null, (ushort)DBTYPEENUM.DBTYPE_I4,
new uint[1] {
0
}, uintSize);
sqlCommand = string.Format("exec AddCustomField$ ? output, {0}, {1}, {2}, " +
"{3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}",
AsSql(m_name, odc, ref index), m_type, m_class, AsSql(m_dstCls), AsSql(m_min),
AsSql(m_max), AsSql(m_big), AsSql(m_userlabel, odc, ref index), AsSql(m_helpString, odc, ref index),
AsSql(m_listRootId), AsWSSql(m_wsSelector), AsSql(m_xmlUI, odc, ref index));
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
odc.ExecCommand(sqlCommand, (int)SqlStmtType.knSqlStmtStoredProcedure);
odc.GetParameter(1, rgHvo, uintSize, out fIsNull);
m_id = (int)(((uint[])MarshalEx.NativeToArray(rgHvo, 1,
typeof(uint)))[0]);
}
}
}
finally
{
DbOps.ShutdownODC(ref odc);
}
// Before continuing, we have to close any open transactions (essentially the
// File/Save operation). Otherwise, lots of things can break or timeout...
if (m_cache.DatabaseAccessor.IsTransactionOpen())
{
m_cache.DatabaseAccessor.CommitTrans();
}
if (m_cache.ActionHandlerAccessor != null)
{
m_cache.ActionHandlerAccessor.Commit();
}
}
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Return a byte array read from the specified column
/// </summary>
/// <param name="odc">The odc.</param>
/// <param name="icol">ZERO-based column index.</param>
/// <returns>null or byte array</returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
/// ------------------------------------------------------------------------------------
public static byte[] ReadBytes(IOleDbCommand odc, int icol)
{
using (ArrayPtr prgch = MarshalEx.ArrayToNative(4000, typeof(byte)))
{
uint cbSpaceTaken;
bool fIsNull;
odc.GetColValue((uint)(icol + 1), prgch, prgch.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
return null;
return (byte[])MarshalEx.NativeToArray(prgch, (int)cbSpaceTaken, typeof(byte));
}
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Read the icol'th column from the current row as an integer. Return zero if the
/// value is null.
/// </summary>
/// <param name="odc">The odc.</param>
/// <param name="icol">ZERO-based column number</param>
/// <returns></returns>
/// <remarks>The SQL command must NOT modify the database in any way!</remarks>
/// ------------------------------------------------------------------------------------
public static int ReadInt(IOleDbCommand odc, int icol)
{
bool fIsNull;
uint cbSpaceTaken;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
odc.GetColValue((uint)(icol + 1), rgHvo, rgHvo.Size, out cbSpaceTaken, out fIsNull, 0);
if (fIsNull)
return 0;
return IntFromStartOfUintArrayPtr(rgHvo);
}
}
/// ------------------------------------------------------------------------------------
/// <summary>
/// Read one int from a column in a result set. Indexes are 1-based.
/// </summary>
/// <param name="odc"></param>
/// <param name="icol"></param>
/// <returns></returns>
/// ------------------------------------------------------------------------------------
private int ReadColVal(IOleDbCommand odc, uint icol)
{
bool fIsNull;
uint cbSpaceTaken;
uint uintSize = (uint)System.Runtime.InteropServices.Marshal.SizeOf(typeof(uint));
uint[] uIds;
using (ArrayPtr rgHvo = MarshalEx.ArrayToNative(1, typeof(uint)))
{
odc.GetColValue(icol, rgHvo, uintSize, out cbSpaceTaken, out fIsNull, 0);
uIds = (uint[])MarshalEx.NativeToArray(rgHvo, 1, typeof(uint));
}
return (int)uIds[0];
}
