/// ------------------------------------------------------------------------------------ /// <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]; }