コード例 #1
1
ファイル: TestsBase.cs プロジェクト: kingpong/NETProvider
        private static void InsertTestData(string connectionString)
        {
            FbConnection connection = new FbConnection(connectionString);
            connection.Open();

            StringBuilder commandText = new StringBuilder();

            commandText.Append("insert into	test (int_field, char_field, varchar_field,	bigint_field, smallint_field, float_field, double_field, numeric_field,	date_field,	time_field,	timestamp_field, clob_field, blob_field)");
            commandText.Append(" values(@int_field,	@char_field, @varchar_field, @bigint_field,	@smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field,	@timestamp_field, @clob_field, @blob_field)");

            FbTransaction transaction = connection.BeginTransaction();
            FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);

            try
            {
                // Add command parameters
                command.Parameters.Add("@int_field", FbDbType.Integer);
                command.Parameters.Add("@char_field", FbDbType.Char);
                command.Parameters.Add("@varchar_field", FbDbType.VarChar);
                command.Parameters.Add("@bigint_field", FbDbType.BigInt);
                command.Parameters.Add("@smallint_field", FbDbType.SmallInt);
                command.Parameters.Add("@float_field", FbDbType.Double);
                command.Parameters.Add("@double_field", FbDbType.Double);
                command.Parameters.Add("@numeric_field", FbDbType.Numeric);
                command.Parameters.Add("@date_field", FbDbType.Date);
                command.Parameters.Add("@time_Field", FbDbType.Time);
                command.Parameters.Add("@timestamp_field", FbDbType.TimeStamp);
                command.Parameters.Add("@clob_field", FbDbType.Text);
                command.Parameters.Add("@blob_field", FbDbType.Binary);

                command.Prepare();

                for (int i = 0; i < 100; i++)
                {
                    command.Parameters["@int_field"].Value = i;
                    command.Parameters["@char_field"].Value = "IRow " + i.ToString();
                    command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
                    command.Parameters["@bigint_field"].Value = i;
                    command.Parameters["@smallint_field"].Value = i;
                    command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
                    command.Parameters["@double_field"].Value = Math.Log(i, 10);
                    command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
                    command.Parameters["@date_field"].Value = DateTime.Now;
                    command.Parameters["@time_field"].Value = DateTime.Now;
                    command.Parameters["@timestamp_field"].Value = DateTime.Now;
                    command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
                    command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());

                    command.ExecuteNonQuery();
                }

                // Commit transaction
                transaction.Commit();
            }
            catch (FbException)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                command.Dispose();
                connection.Close();
            }
        }
コード例 #2
1
		public void ParameterDescribeTest()
		{
			string sql = "insert into TEST (int_field) values (@value)";

			FbCommand command = new FbCommand(sql, this.Connection);
			command.Prepare();
			command.Parameters.Add("@value", FbDbType.Integer).Value = 100000;

			command.ExecuteNonQuery();

			command.Dispose();
		}
コード例 #3
0
ファイル: FbDataReader.cs プロジェクト: eksotama/NETProvider
        public override DataTable GetSchemaTable()
        {
            CheckState();

            if (_schemaTable != null)
            {
                return(_schemaTable);
            }

            DataRow schemaRow    = null;
            var     tableCount   = 0;
            var     currentTable = string.Empty;

            _schemaTable = GetSchemaTableStructure();

            /* Prepare statement for schema fields information	*/
            var schemaCmd = new FbCommand(
                GetSchemaCommandText(),
                _command.Connection,
                _command.Connection.InnerConnection.ActiveTransaction);

            schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31);
            schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31);
            schemaCmd.Prepare();

            _schemaTable.BeginLoadData();

            for (var i = 0; i < _fields.Count; i++)
            {
                var isKeyColumn  = false;
                var isUnique     = false;
                var isReadOnly   = false;
                var precision    = 0;
                var isExpression = false;

                /* Get Schema data for the field	*/
                schemaCmd.Parameters[0].Value = _fields[i].Relation;
                schemaCmd.Parameters[1].Value = _fields[i].Name;

                using (var r = schemaCmd.ExecuteReader())
                {
                    if (r.Read())
                    {
                        isReadOnly   = (IsReadOnly(r) || IsExpression(r)) ? true : false;
                        isKeyColumn  = (r.GetInt32(2) == 1) ? true : false;
                        isUnique     = (r.GetInt32(3) == 1) ? true : false;
                        precision    = r.IsDBNull(4) ? -1 : r.GetInt32(4);
                        isExpression = IsExpression(r);
                    }
                }

                /* Create new row for the Schema Table	*/
                schemaRow = _schemaTable.NewRow();

                schemaRow["ColumnName"]    = GetName(i);
                schemaRow["ColumnOrdinal"] = i;
                schemaRow["ColumnSize"]    = _fields[i].GetSize();
                if (_fields[i].IsDecimal())
                {
                    schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
                    if (precision > 0)
                    {
                        schemaRow["NumericPrecision"] = precision;
                    }
                    schemaRow["NumericScale"] = _fields[i].NumericScale * (-1);
                }
                schemaRow["DataType"]        = GetFieldType(i);
                schemaRow["ProviderType"]    = GetProviderType(i);
                schemaRow["IsLong"]          = _fields[i].IsLong();
                schemaRow["AllowDBNull"]     = _fields[i].AllowDBNull();
                schemaRow["IsRowVersion"]    = false;
                schemaRow["IsAutoIncrement"] = false;
                schemaRow["IsReadOnly"]      = isReadOnly;
                schemaRow["IsKey"]           = isKeyColumn;
                schemaRow["IsUnique"]        = isUnique;
                schemaRow["IsAliased"]       = _fields[i].IsAliased();
                schemaRow["IsExpression"]    = isExpression;
                schemaRow["BaseSchemaName"]  = DBNull.Value;
                schemaRow["BaseCatalogName"] = DBNull.Value;
                schemaRow["BaseTableName"]   = _fields[i].Relation;
                schemaRow["BaseColumnName"]  = _fields[i].Name;

                _schemaTable.Rows.Add(schemaRow);

                if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation)
                {
                    tableCount++;
                    currentTable = _fields[i].Relation;
                }

                /* Close statement	*/
                schemaCmd.Close();
            }

            if (tableCount > 1)
            {
                foreach (DataRow row in _schemaTable.Rows)
                {
                    row["IsKey"]    = false;
                    row["IsUnique"] = false;
                }
            }

            _schemaTable.EndLoadData();

            /* Dispose command	*/
            schemaCmd.Dispose();

            return(_schemaTable);
        }
コード例 #4
0
		public void PrepareTest()
		{
			// Create a	new	test table
			FbCommand create = new FbCommand("create table PrepareTest(test_field varchar(20));", Connection);
			create.ExecuteNonQuery();
			create.Dispose();

			// Insert data using a prepared	statement
			FbCommand command = new FbCommand(
				"insert	into PrepareTest(test_field) values(@test_field);",
				Connection);

			command.Parameters.Add("@test_field", FbDbType.VarChar).Value = DBNull.Value;
			command.Prepare();

			for (int i = 0; i < 5; i++)
			{
				if (i < 1)
				{
					command.Parameters[0].Value = DBNull.Value;
				}
				else
				{
					command.Parameters[0].Value = i.ToString();
				}
				command.ExecuteNonQuery();
			}

			command.Dispose();

			try
			{
				// Check that data is correct
				FbCommand select = new FbCommand("select * from	PrepareTest", Connection);
				FbDataReader reader = select.ExecuteReader();
				int count = 0;
				while (reader.Read())
				{
					if (count == 0)
					{
						Assert.AreEqual(DBNull.Value, reader[0], "Invalid value.");
					}
					else
					{
						Assert.AreEqual(count, reader.GetInt32(0), "Invalid	value.");
					}

					count++;
				}
				reader.Close();
				select.Dispose();
			}
			catch (Exception)
			{
				throw;
			}
			finally
			{
				// Drop	table
				FbCommand drop = new FbCommand("drop table PrepareTest", Connection);
				drop.ExecuteNonQuery();
				drop.Dispose();
			}
		}
コード例 #5
0
        public override DataTable GetSchemaTable()
        {
            this.CheckState();

            if (this.schemaTable != null)
            {
                return(this.schemaTable);
            }

            #region Variables
            DataRow schemaRow    = null;
            int     tableCount   = 0;
            string  currentTable = string.Empty;
            this.schemaTable = GetSchemaTableStructure();
            const Int16         batchLimit      = 90;                                             //Could be adjusted as needed.
            Int16               paramCounter    = 0;                                              //Counter for the whole batch process
            Int16               batchRounds     = 0;                                              //counter for each batch (limited by batchlimit)
            Hashtable           relationList    = new Hashtable();                                //HashTable to store the query's unique Field Tables Names.
            List <RDBTableInfo> fieldList       = new List <RDBTableInfo>(this.fields.Count + 1); //List to store the whole statement Schema Field Values.
            const Int16         metadataColSize = 31;                                             //Firebird MAX Column Size.
            Int16               batchID         = 0;                                              //Batch marker. When batchlimit reaches its limit it increases by one the value.
            StringBuilder       sb = new StringBuilder();                                         //Stores dynamic generated schema query.
            #endregion


            // Prepare statement for schema fields information
            //Asign current active schema command connection and transaccion
            FbCommand schemaCmd = new FbCommand();
            schemaCmd.Connection  = this.command.Connection;
            schemaCmd.Transaction = this.command.Connection.InnerConnection.ActiveTransaction;

            for (paramCounter = 0; paramCounter < this.FieldCount; paramCounter++)
            {
                if (batchRounds >= batchLimit) //Process field params until batch limit is reached.
                {
                    batchID++;
                    batchRounds = 0;
                }

                RDBTableInfo rdbinfo = new RDBTableInfo();
                rdbinfo.Ordinal      = paramCounter;
                rdbinfo.FieldName    = this.fields[paramCounter].Name;
                rdbinfo.RelationName = this.fields[paramCounter].Relation;
                rdbinfo.BatchID      = batchID;
                fieldList.Add(rdbinfo);

                batchRounds++;
            }

            //Process batch schema query
            for (Int16 i = 0; i <= batchID; i++)
            {
                sb.Length = 0;
                relationList.Clear();
                List <RDBTableInfo> rdblBatch = new List <RDBTableInfo>(this.fields.Count + 1);
                //Find all RDBTableInfo elements according to batchID
                rdblBatch = fieldList.FindAll(rdbti => rdbti.BatchID == i);

                //Just add the needed tables according to the fieldnames on the current batch.
                for (Int16 j = 0; j < rdblBatch.Count; j++)
                {
                    //Keep a list of unique relation names (tables) from all the fieldlist.
                    if (!relationList.ContainsValue(rdblBatch[j].RelationName))
                    {
                        relationList.Add(relationList.Count, rdblBatch[j].RelationName);
                    }
                }

                if (schemaCmd.Parameters.Count > 0) //Clear previous command parameters.
                {
                    schemaCmd.Parameters.Clear();
                }

                //Get the Base Squema query to start generating Dynamic Schema query
                sb.Append(GetSchemaCommandTextBase());

                //Perform batch field query against table schema
                //Add relation (table names) to schemaCmd
                for (int j = 0; j < relationList.Count; j++)
                {
                    if (j > 0) //More than one table in query statement
                    {
                        sb.Append(" OR ");
                    }

                    List <RDBTableInfo> tmpList = rdblBatch.FindAll(rdbti => rdbti.RelationName.Equals(relationList[j]));
                    sb.AppendFormat(" (rfr.rdb$field_name in {0} AND rfr.rdb$relation_name='{1}') ", GetParamExpression(tmpList.Count), relationList[j]);

                    for (int k = 0; k < tmpList.Count; k++)
                    {
                        schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, metadataColSize).Value = tmpList[k].FieldName;
                    }

                    tmpList = null;
                }
                //set order to schema query
                sb.Append(" ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position");

                schemaCmd.CommandText = sb.ToString();
                schemaCmd.Prepare();
                schemaTable.BeginLoadData();

                //Reset Column Values
                int Ordinal    = 0;
                int batchCount = 0;

                //perform batch query
                using (FbDataReader r = schemaCmd.ExecuteReader())
                {
                    batchCount = 0;//reset batch counter
                    while (r.Read())
                    {
                        rdblBatch[batchCount].isReadOnly   = (IsReadOnly(r) || IsExpression(r)) ? true : false;
                        rdblBatch[batchCount].isKeyColumn  = (r.GetInt32(2) == 1) ? true : false;
                        rdblBatch[batchCount].isUnique     = (r.GetInt32(3) == 1) ? true : false;
                        rdblBatch[batchCount].precision    = r.IsDBNull(4) ? -1 : r.GetInt32(4);
                        rdblBatch[batchCount].isExpression = IsExpression(r);
                        batchCount++;
                    }
                }

                for (int j = 0; j < rdblBatch.Count; j++)
                {
                    Ordinal = rdblBatch[j].Ordinal;
                    // Create new row for the Schema Table
                    schemaRow = schemaTable.NewRow();
                    schemaRow["ColumnName"]    = this.GetName(Ordinal);
                    schemaRow["ColumnOrdinal"] = Ordinal;

                    schemaRow["ColumnSize"] = this.fields[Ordinal].GetSize();
                    if (fields[Ordinal].IsDecimal())
                    {
                        schemaRow["NumericPrecision"] = schemaRow["ColumnSize"];
                        if (rdblBatch[j].precision > 0)
                        {
                            schemaRow["NumericPrecision"] = rdblBatch[j].precision;
                        }
                        schemaRow["NumericScale"] = this.fields[Ordinal].NumericScale * (-1);
                    }
                    schemaRow["DataType"]        = this.GetFieldType(Ordinal);
                    schemaRow["ProviderType"]    = this.GetProviderType(Ordinal);
                    schemaRow["IsLong"]          = this.fields[Ordinal].IsLong();
                    schemaRow["AllowDBNull"]     = this.fields[Ordinal].AllowDBNull();
                    schemaRow["IsRowVersion"]    = false;
                    schemaRow["IsAutoIncrement"] = false;
                    schemaRow["IsReadOnly"]      = rdblBatch[j].isReadOnly;
                    schemaRow["IsKey"]           = rdblBatch[j].isKeyColumn;
                    schemaRow["IsUnique"]        = rdblBatch[j].isUnique;
                    schemaRow["IsAliased"]       = this.fields[Ordinal].IsAliased();
                    schemaRow["IsExpression"]    = rdblBatch[j].isExpression;
                    schemaRow["BaseSchemaName"]  = DBNull.Value;
                    schemaRow["BaseCatalogName"] = DBNull.Value;
                    schemaRow["BaseTableName"]   = this.fields[Ordinal].Relation;
                    schemaRow["BaseColumnName"]  = this.fields[Ordinal].Name;

                    schemaTable.Rows.Add(schemaRow);

                    if (!String.IsNullOrEmpty(this.fields[Ordinal].Relation) && currentTable != this.fields[Ordinal].Relation)
                    {
                        tableCount++;
                        currentTable = this.fields[Ordinal].Relation;
                    }
                }
                schemaTable.EndLoadData();
                rdblBatch = null;
            }//Finish Batch Round Iteration


            schemaCmd.Close();
            if (tableCount > 1)
            {
                foreach (DataRow row in schemaTable.Rows)
                {
                    row["IsKey"]    = false;
                    row["IsUnique"] = false;
                }
            }

            //Dispose command
            schemaCmd.Dispose();
            relationList = null;
            fieldList    = null;
            return(schemaTable);
        }
コード例 #6
0
        /// <summary>
        /// Clears the quadruples of the store
        /// </summary>
        public override RDFStore ClearQuadruples() {

            //Create command
            var command = new FbCommand("DELETE FROM Quadruples", this.Connection);

            try {

                //Open connection
                this.Connection.Open();

                //Prepare command
                command.Prepare();

                //Open transaction
                command.Transaction = this.Connection.BeginTransaction();

                //Execute command
                command.ExecuteNonQuery();

                //Close transaction
                command.Transaction.Commit();

                //Close connection
                this.Connection.Close();

            }
            catch (Exception ex) {

                //Rollback transaction
                command.Transaction.Rollback();

                //Close connection
                this.Connection.Close();

                //Propagate exception
                throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);

            }

            return this;
        }
コード例 #7
0
        /// <summary>
        /// Removes the quadruples with the given literal as object
        /// </summary>
        public override RDFStore RemoveQuadruplesByLiteral(RDFLiteral literalObject) {
            if (literalObject  != null) {

                //Create command
                var command     = new FbCommand("DELETE FROM Quadruples WHERE ObjectID = @OBJID AND TripleFlavor = @TFV", this.Connection);
                command.Parameters.Add(new FbParameter("OBJID", FbDbType.BigInt));
                command.Parameters.Add(new FbParameter("TFV",   FbDbType.Integer));

                //Valorize parameters
                command.Parameters["OBJID"].Value = literalObject.PatternMemberID;
                command.Parameters["TFV"].Value   = RDFModelEnums.RDFTripleFlavors.SPL;

                try {

                    //Open connection
                    this.Connection.Open();

                    //Prepare command
                    command.Prepare();

                    //Open transaction
                    command.Transaction = this.Connection.BeginTransaction();

                    //Execute command
                    command.ExecuteNonQuery();

                    //Close transaction
                    command.Transaction.Commit();

                    //Close connection
                    this.Connection.Close();

                }
                catch (Exception ex) {

                    //Rollback transaction
                    command.Transaction.Rollback();

                    //Close connection
                    this.Connection.Close();

                    //Propagate exception
                    throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);

                }

            }
            return this;
        }
コード例 #8
0
        /// <summary>
        /// Removes the quadruples with the given predicate
        /// </summary>
        public override RDFStore RemoveQuadruplesByPredicate(RDFResource predicateResource) {
            if (predicateResource != null) {

                //Create command
                var command        = new FbCommand("DELETE FROM Quadruples WHERE PredicateID = @PREDID", this.Connection);
                command.Parameters.Add(new FbParameter("PREDID", FbDbType.BigInt));

                //Valorize parameters
                command.Parameters["PREDID"].Value = predicateResource.PatternMemberID;

                try {

                    //Open connection
                    this.Connection.Open();

                    //Prepare command
                    command.Prepare();

                    //Open transaction
                    command.Transaction = this.Connection.BeginTransaction();

                    //Execute command
                    command.ExecuteNonQuery();

                    //Close transaction
                    command.Transaction.Commit();

                    //Close connection
                    this.Connection.Close();

                }
                catch (Exception ex) {

                    //Rollback transaction
                    command.Transaction.Rollback();

                    //Close connection
                    this.Connection.Close();

                    //Propagate exception
                    throw new RDFStoreException("Cannot delete data from Firebird store because: " + ex.Message, ex);

                }

            }
            return this;
        }
コード例 #9
0
        /// <summary>
        /// Adds the given quadruple to the store, avoiding duplicate insertions
        /// </summary>
        public override RDFStore AddQuadruple(RDFQuadruple quadruple) {
            if (quadruple   != null) {

                //Create command
                var command  = new FbCommand("UPDATE OR INSERT INTO Quadruples (QuadrupleID, TripleFlavor, Context, ContextID, Subject, SubjectID, Predicate, PredicateID, Object, ObjectID) VALUES (@QID, @TFV, @CTX, @CTXID, @SUBJ, @SUBJID, @PRED, @PREDID, @OBJ, @OBJID) MATCHING (QuadrupleID)", this.Connection);
                command.Parameters.Add(new FbParameter("QID",    FbDbType.BigInt));
                command.Parameters.Add(new FbParameter("TFV",    FbDbType.Integer));
                command.Parameters.Add(new FbParameter("CTX",    FbDbType.VarChar, 1000));
                command.Parameters.Add(new FbParameter("CTXID",  FbDbType.BigInt));
                command.Parameters.Add(new FbParameter("SUBJ",   FbDbType.VarChar, 1000));
                command.Parameters.Add(new FbParameter("SUBJID", FbDbType.BigInt));
                command.Parameters.Add(new FbParameter("PRED",   FbDbType.VarChar, 1000));
                command.Parameters.Add(new FbParameter("PREDID", FbDbType.BigInt));
                command.Parameters.Add(new FbParameter("OBJ",    FbDbType.VarChar, 5000));
                command.Parameters.Add(new FbParameter("OBJID",  FbDbType.BigInt));

                //Valorize parameters
                command.Parameters["QID"].Value    = quadruple.QuadrupleID;
                command.Parameters["TFV"].Value    = quadruple.TripleFlavor;
                command.Parameters["CTX"].Value    = quadruple.Context.ToString();
                command.Parameters["CTXID"].Value  = quadruple.Context.PatternMemberID;
                command.Parameters["SUBJ"].Value   = quadruple.Subject.ToString();
                command.Parameters["SUBJID"].Value = quadruple.Subject.PatternMemberID;
                command.Parameters["PRED"].Value   = quadruple.Predicate.ToString();
                command.Parameters["PREDID"].Value = quadruple.Predicate.PatternMemberID;
                command.Parameters["OBJ"].Value    = quadruple.Object.ToString();
                command.Parameters["OBJID"].Value  = quadruple.Object.PatternMemberID;

                try {

                    //Open connection
                    this.Connection.Open();

                    //Prepare command
                    command.Prepare();

                    //Open transaction
                    command.Transaction = this.Connection.BeginTransaction();

                    //Execute command
                    command.ExecuteNonQuery();

                    //Close transaction
                    command.Transaction.Commit();

                    //Close connection
                    this.Connection.Close();

                }
                catch (Exception ex) {

                    //Rollback transaction
                    command.Transaction.Rollback();

                    //Close connection
                    this.Connection.Close();

                    //Propagate exception
                    throw new RDFStoreException("Cannot insert data into Firebird store because: " + ex.Message, ex);

                }

            }
            return this;
        }