参数绑定

很多场景下 SQL 语句不是一个固定的字符串,需要传入多个动态的参数变量,这种场景下就可以使用绑定参数的方式进行操作.

标识

在 SQL 语句中,用字符 @ 或 : 标识动态参数,例如

SQL

INSERT INTO MY_TABLE VALUES (@v1,@v2,@v3,@v4,@v5)

上述 SQL 语句中动态参数有 @v1,@v2,@v3,@v4,@v5

参数绑定

OscarCommand 属性 Parameters 缓存执行SQL绑定参数集, 添加方法 Add 和 AddRange.

示例

using System;
using System.Data.OscarClient;

namespace ConsoleApp10
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (var connect = new OscarConnection("Server=10.1.1.66;Port=2003;User Id=SYSDBA;Password=szoscar55;Database=OSRDB;"))
            {
                connect.Open();
                using (var command = connect.CreateCommand())
                {
                    command.CommandText = "CREATE TABLE MY_TABLE (ID INT, NAME VARCHAR(16), AGE INT, SEX BOOLEAN, MARK TIMESTAMP)";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO MY_TABLE VALUES (@id,@name,@age,@sex,@mark)";
                    command.Parameters.Add(new OscarParameter("id", OscarDbType.Int) { Value = 1 });
                    command.Parameters.Add(new OscarParameter("name", OscarDbType.VarChar) { Value = "小张" });
                    command.Parameters.Add(new OscarParameter("age", OscarDbType.Int) { Value = 20 });
                    command.Parameters.Add(new OscarParameter("sex", OscarDbType.Boolean) { Value = true });
                    command.Parameters.Add(new OscarParameter("mark", OscarDbType.TimeStamp) { Value = DateTime.Now });
                    var result = command.ExecuteNonQuery();
                    command.Parameters.Clear();

                    command.CommandText = "SELECT * FROM MY_TABLE";
                    using (OscarDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("ID    NAME     AGE     SEX     MARK");
                        while (reader.Read())
                        {
                            var vals = new object[5];
                            reader.GetValues(vals);
                            Console.WriteLine("{0}     {1}     {2}      {3}    {4}", vals);
                        }
                    }

                    command.CommandText ="DROP TABLE MY_TABLE";
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

输出

ID    NAME     AGE     SEX     MARK
1     小张      20      True    2021-12-13 11:24:24

引用

OscarConnection

OscarCommand

OscarParameter