Question

C# SQL文中有变量存在，在什么地方设置

SELECT userid,username,sex
WHERE userid = :userid
AND password = :pwd
。。。。。
C#的复制语句省略
。。。。。
:userid 和 :pwd 这样的变量是在链接库的时候定义在哪里的。值可以赋给userid 和 pwd ，但是数据库找不到这两个变量，因而无法解析。当做WHERE条件为假处理。

Answer 1

1：
private void button1_Click(object sender, EventArgs e)
{
ExecutResult exeRs = new ExecutResult();
List<OracleParameter> orac = new List<OracleParameter>();
OracleParameter oracSn = new OracleParameter("SN", OracleType.VarChar);
oracSn.Value = textBox1.Text.Trim().ToUpper();
orac.Add(oracSn);
string sqlText = @"select * from sfism4.r_wip_tracking_t where serial_number =:SN";
exeRs = dbOracle.OracleSelectUpdateDelete(sqlText, orac);
DataSet ds = new System.Data.DataSet();
ds = (DataSet)exeRs.Anthing;
dataGridView1.DataSource = ds.Tables[0];
}

2：dbOracle.OracleSelectUpdateDelete的定义
public ExecutResult OracleSelectUpdateDelete(string sqlText,List<OracleParameter> oraParams)
{
OracleParameter [] oracleParamsArray=new OracleParameter[oraParams.Count];
oracleParamsArray = oraParams.ToArray();
ExecutResult executResult = new ExecutResult();
OracleConnection oracleconn = new OracleConnection(logion);
OracleDataAdapter moracledap = new OracleDataAdapter();
OracleCommand mysqlcmd = new OracleCommand(sqlText, oracleconn);
mysqlcmd.Parameters.AddRange(oracleParamsArray);
try
{
moracledap = new OracleDataAdapter(mysqlcmd);
moracledap.Fill(executResult.Ds);
executResult.Anthing = executResult.Ds;
executResult.ResultMessage = "OK";
executResult.ResultError = string.Empty;
executResult.Result = true;
oracleconn.Dispose();
mysqlcmd.Dispose();
moracledap.Dispose();
oracleParamsArray = null;
oraParams = null;
}
catch (Exception ex)
{
executResult.ResultMessage = "NO";
executResult.ResultError = ex.Message;
executResult.Result = false;
}
return executResult;
}

3：Oracle 执行带参数的SQL时接受一个参数的数组oracleParamsArray
OracleParameter [] oracleParamsArray=new OracleParameter[oraParams.Count];
参数也要有类型和值的定义
//参数的类型
OracleParameter oracSn = new OracleParameter("SN", OracleType.VarChar);
//参数的赋值
oracSn.Value = textBox1.Text.Trim().ToUpper();
//参数的执行
OracleCommand mysqlcmd = new OracleCommand(sqlText, oracleconn);
mysqlcmd.Parameters.AddRange(oracleParamsArray);

Answer 2

试试下面代码：

string query = "SELECT userid,username,sex FROM yourTable WHERE userid = :userid AND password = :pwd";
string password = "";
string useid = "";
using (OleDbConnection con = new OleDbConnection(ConnectionString))
{
    using (OleDbCommand cmd = new OleDbCommand(query, con))
    {
        con.Open();
        cmd.Parameters.Add(":userid", OleDbType.VarChar, 13);
        cmd.Parameters.Add(":pwd", OleDbType.VarChar, 36);
        cmd.Parameters[":userid"].Value = useid;
        cmd.Parameters[":pwd"].Value = password;
        cmd.ExecuteNonQuery();
    }
}