예제 #1
0
        virtual public Recordset ExecuteSql(string sql)
        {
            Recordset       oRS    = new Recordset();
            OleDbConnection cn     = null;
            OleDbDataReader reader = null;

            try
            {
                cn = new OleDbConnection(dbRoot.ConnectionString);
                cn.Open();
                try
                {
                    cn.ChangeDatabase(this.Name);
                }
                catch { } // some databases don't have the concept of catalogs. Catch this and throw it out

                OleDbCommand command = new OleDbCommand(sql, cn);
                command.CommandType = CommandType.Text;

                reader = command.ExecuteReader();

                DataTable schema;
                string    dataType, fieldname;
                int       length;
                bool      firstTime = true;

                while (reader.Read())
                {
                    if (firstTime)
                    {
                        schema = reader.GetSchemaTable();

                        foreach (DataRow row in schema.Rows)
                        {
                            fieldname = row["ColumnName"].ToString();
                            dataType  = row["DataType"].ToString();
                            length    = Convert.ToInt32(row["ColumnSize"]);

                            oRS.Fields.Append(fieldname, GetADOType(dataType), length,
                                              FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value);
                        }

                        oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1);

                        firstTime = false;
                    }
                    oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (reader[i] is System.Guid)
                        {
                            oRS.Fields[i].Value = "{" + reader[i].ToString() + "}";
                        }
                        else
                        {
                            oRS.Fields[i].Value = reader[i];
                        }
                    }
                }

                cn.Close();
                //Move to the first record
                if (!firstTime)
                {
                    oRS.MoveFirst();
                }
                else
                {
                    oRS = null;
                }
            }
            catch (Exception ex)
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                    reader = null;
                }
                if ((cn != null) && (cn.State == ConnectionState.Open))
                {
                    cn.Close();
                    cn = null;
                }
                throw ex;
            }

            return(oRS);
        }
예제 #2
0
        protected Recordset ExecuteIntoRecordset(string sql, IDbConnection cn)
        {
            Recordset   oRS    = new Recordset();
            IDataReader reader = null;

            try
            {
                IDbCommand command = cn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

                reader = command.ExecuteReader();

                DataTable schema;
                string    dataType, fieldname;
                int       length;
                bool      firstTime = true;

                // Skip columns contains the index of any columns that we cannot handle, array types and such ...
                Hashtable skipColumns = null;

                while (reader.Read())
                {
                    if (firstTime)
                    {
                        skipColumns = new Hashtable();
                        schema      = reader.GetSchemaTable();

                        int colID = 0;
                        foreach (DataRow row in schema.Rows)
                        {
                            fieldname = row["ColumnName"].ToString();
                            dataType  = row["DataType"].ToString();
                            length    = Convert.ToInt32(row["ColumnSize"]);

                            try
                            {
                                oRS.Fields.Append(fieldname, GetADOType(dataType), length,
                                                  FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value);
                            }
                            catch
                            {
                                // We can't handle this column type, ie, Firebird array types
                                skipColumns[colID] = colID;
                            }

                            colID++;
                        }

                        oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1);

                        firstTime = false;
                    }

                    oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                    for (int i = 0, j = 0; i < reader.FieldCount; i++)
                    {
                        // Skip columns that we cannot handle
                        if (!skipColumns.ContainsKey(i))
                        {
                            if (reader[j] is System.Guid)
                            {
                                oRS.Fields[j].Value = "{" + reader[j].ToString() + "}";
                            }
                            else
                            {
                                try
                                {
                                    oRS.Fields[j].Value = reader[j];
                                }
                                catch
                                {
                                    // For some reason it wouldn't accept this value?
                                    oRS.Fields[j].Value = DBNull.Value;
                                }
                            }

                            j++;
                        }
                    }
                }

                cn.Close();

                //Move to the first record
                if (!firstTime)
                {
                    oRS.MoveFirst();
                }
                else
                {
                    oRS = null;
                }
            }
            catch (Exception ex)
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                    reader = null;
                }
                if ((cn != null) && (cn.State == ConnectionState.Open))
                {
                    cn.Close();
                    cn = null;
                }
                throw ex;
            }

            return(oRS);
        }