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; } }
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; } }