Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Working with Parameters


The CUBRID .NET Driver provides support for using parameters in SQL commands.

As you probably know, in CUBRID there is no support for named parameters, but only for position-based parameters.

The CUBRID ADO.NET Driver implements support for position-based parameters, with any name you want, as long as the parameters are prefixed with the character "?". Remember, the parameter name is not important, as long as you declare and initialize them in the correct order. 

For example, assuming we have a table with 2 fields (int and varchar(32)) we can insert values using the following code:

using (CUBRIDCommand cmd = new CUBRIDCommand("insert into t values(?, ?)", conn))
{
    CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT);
    p1.Value = 1;
    cmd.Parameters.Add(p1);

    CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_STRING);
    p2.Value = "abc";
    cmd.Parameters.Add(p2);

    cmd.ExecuteNonQuery();
}

In the above example, the most important thing is the order in which the Add() methods are called!

Here is the complete example:

 

using CUBRID.Data.CUBRIDClient;

namespace ParameterExample
{
    class Program
    {
        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();
                using (CUBRIDCommand cmd = new CUBRIDCommand("create table t(a int, b varchar(20))", conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (CUBRIDCommand cmd = new CUBRIDCommand("insert into t values(?, ?)", conn))
                {
                    CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT);
                    p1.Value = 1;
                    cmd.Parameters.Add(p1);

                    CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_STRING);
                    p2.Value = "abc";
                    cmd.Parameters.Add(p2);

                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }
}

You can also specify the type of the parameter, see also.

using System;

namespace System.Data
{
    // Summary:
    //     Specifies the type of a parameter within a query relative to the System.Data.DataSet.
    public enum ParameterDirection
    {
        // Summary:
        //     The parameter is an input parameter.
        Input = 1,
        //
        // Summary:
        //     The parameter is an output parameter.
        Output = 2,
        //
        // Summary:
        //     The parameter is capable of both input and output.
        InputOutput = 3,
        //
        // Summary:
        //     The parameter represents a return value from an operation such as a stored
        //     procedure, built-in function, or user-defined function.
        ReturnValue = 6,
    }
}

The parameter type default value is: ParameterDirection.Input. For more information:   How to Call a Java Stored Function/Procedure in C#?.

comments powered by Disqus
Page info
viewed 1855 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated last year by
View revisions
Share this article