C# OracleParameter 传参 实例

 1.注：Oracle：OracleParameter，参数命名以“:”作为标识；

         MSSQL：SqlParameter，参数命名以“@”作为标识，根据数据库不同调用不同的接口。      

2.附Parameter.Add()和Parameter.AddRange()方法的区别：

Parameters.Add将指定的 SqlParameter 对象添加到 SqlParameterCollection 中

SqlParameterCollection.AddRange 将值数组添加到 SqlParameterCollection 的末尾。 

在群体操作时，使用AddRange取代Add。

Oracle实例：

public static Boolean getLmt(string lmtd_name)
    {
        string usr_user = HttpContext.Current.Session["MJERP10username"].ToString();
        if (lmtd_name == "") { return true; }
        //string sql = @"select id from sys_lmtd where lmtd_mde||'_'||lmtd_name='" + lmtd_name + "' and lmtd_username='" + usr_user + "' union all select ro.id from role_mstr ro join usr_mstr on usr_role_id = ro.id where usr_user = '" + usr_user + "'";

        string sql1 = @"select id from sys_lmtd where lmtd_mde||'_'||lmtd_name=:lmtd_name and lmtd_username=:lmtd_username union all select ro.id from role_mstr ro join usr_mstr on usr_role_id = ro.id where usr_user =:usr_user";

        OracleParameter[] param = new OracleParameter[]{
            new OracleParameter(":lmtd_name",OracleType.VarChar,50),  
            new OracleParameter(":lmtd_username",OracleType.VarChar,50), 
            new OracleParameter(":usr_user",OracleType.VarChar,30)
        };
        param[0].Value = lmtd_name;
        param[1].Value = usr_user;
        param[2].Value = usr_user;

        DataSet ds = new DataSet();
        using (OracleConnection connection = new OracleConnection(ConfigurationManager.AppSettings["connString"]))
        {
            connection.Open();
            OracleCommand cmd = new OracleCommand(sql1,connection);
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            //cmd.Parameters.AddRange(param);
            OracleDataAdapter myda = new OracleDataAdapter(cmd);
            myda.Fill(ds);
            connection.Close();
        }

        if (ds.Tables[0].Rows.Count > 0)
        {
            ds.Dispose();
            return true;
        }
        else
        {
            ds.Dispose();
            return false;
        }

    }

View Code

MSSQL实例：

//取用户权限
    public static Boolean getLmt(string lmtd_name)
    {
        Dictionary<string, object> dic = new Dictionary<string, object>();
        string usr_user = HttpContext.Current.Session["MJERP10username"].ToString();
        if (lmtd_name == "") { return true; }
        //string sql = @"select id from sys_lmtd where lmtd_mde||'_'||lmtd_name='" + lmtd_name + "' and lmtd_username='" + usr_user + "' union all select ro.id from role_mstr ro join usr_mstr on usr_role_id = ro.id where usr_user = '" + usr_user + "'";

        string sql1 = @"select id from sys_lmtd where lmtd_mde||'_'||lmtd_name=:lmtd_name and lmtd_username=:lmtd_username union all select ro.id from role_mstr ro join usr_mstr on usr_role_id = ro.id where usr_user =:usr_user";
        SqlParameter[] param = new SqlParameter[]{
            new SqlParameter(":lmtd_name",SqlDbType.NVarChar,50),  
            new SqlParameter(":lmtd_username",SqlDbType.NVarChar,50), 
            new SqlParameter(":usr_user",SqlDbType.NVarChar,30)
        };
        param[0].Value = lmtd_name;
        param[1].Value = usr_user;
        param[2].Value = usr_user;

        DataSet ds = new DataSet();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["connString"]))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(sql1, connection);
            //for (int i = 0; i < param.Length; i++)
            //{
            //    cmd.Parameters.Add(param[i]);
            // }
            cmd.Parameters.AddRange(param);
            SqlDataAdapter myda = new SqlDataAdapter(cmd);
            myda.Fill(ds);
            connection.Close();
        }

        if (ds.Tables[0].Rows.Count > 0)
        {
            ds.Dispose();
            return true;
        }
        else
        {
            ds.Dispose();
            return false;
        }

    }

View Code

 

转载于:https://www.cnblogs.com/su1643/p/6592932.html