C# OleDbCommand.CreateParameter方法代码示例

 /// <summary>
 /// 增加参数列表
 /// </summary>
 /// <param name="cmd">cmd对象</param>
 /// <param name="parameterName">参数名</param>
 /// <param name="dbType">参数类型</param>
 /// <param name="value">参数值</param>
 public void AddInParameter(OleDbCommand cmd, string parameterName, DbType dbType, object value)
 {
     OleDbParameter dbParameter = cmd.CreateParameter();
     dbParameter.DbType = dbType;
     dbParameter.ParameterName = parameterName;
     dbParameter.Value = value;
     dbParameter.Direction = ParameterDirection.Input;
     cmd.Parameters.Add(dbParameter);
 }

        private static void Test_Command_CreateParameter()
        {
            using (OleDbCommand cmd = new OleDbCommand("select * from code where s_name=?;", conn))
            {
                OleDbParameter para = cmd.CreateParameter();
                para.Value = 'X';
                para.ParameterName = "s_name";
                cmd.Parameters.Add(para);

                OleDbDataReader reader = cmd.ExecuteReader();
                reader.Read();
                Assert.AreEqual(reader.GetString(1), "Mixed");
            }
        }

        private static void Test_Command_Prepare()
        {
            (new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
            (new OleDbCommand("create table t(id int)", conn)).ExecuteNonQuery();

            using (OleDbCommand cmd = new OleDbCommand("insert into t(id) value(?);", conn))
            {
                OleDbParameter para = cmd.CreateParameter();
                para.Value = 10;
                para.OleDbType = OleDbType.Integer;
                para.ParameterName = "id";
                cmd.Parameters.Add(para);

                cmd.Prepare();
                cmd.ExecuteNonQuery();

                int count = GetTableRowsCount("t",conn);
                Assert.AreEqual(count, 1);
            }
        }

 private void InitCmdUpdate()
 {
     CmdUpdate = new OleDbCommand();
     CmdUpdate.CommandText =
         "UPDATE Company SET  CompanyName= @CompanyName" +
         " WHERE ID = @ID";
     CmdUpdate.Connection = DBConnect.DBConnection;
     OleDbParameter prm = new OleDbParameter();
     prm.ParameterName = "CompanyName";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "ID";
     prm.OleDbType = OleDbType.Integer;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
 }

 private void InitCmdInsert()
 {
     CmdInsert = new OleDbCommand();
     CmdInsert.CommandText =
         "INSERT INTO Company (CompanyName,ID)" +
         " VALUES (@CompanyName,@ID)";
     CmdInsert.Connection = DBConnect.DBConnection;
     OleDbParameter prm = new OleDbParameter();
     prm.ParameterName = "CompanyName";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "ID";
     prm.OleDbType = OleDbType.Integer;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
 }

				/// <summary>
				/// Sets the value of a parameter using an object.  The given argument object will be converted to the
				/// corresponding SQL type before being sent to the database.
				/// </summary>
				/// <param name="command">Command object to be changed.</param>
				/// <param name="parameterIndex">One-based index of the parameter to be set.</param>
				/// <param name="parameter">The object containing the input parameter value.</param>
				public void SetObject(OleDbCommand command, int parameterIndex, Object parameter)
				{
					if (command.Parameters.Count < parameterIndex)
						command.Parameters.Add(command.CreateParameter());
					command.Parameters[parameterIndex - 1].Value = parameter;
				}

				/// <summary>
				/// Sets a parameter to SQL NULL.
				/// </summary>
				/// <param name="command">Command object to be changed.</param>
				/// <param name="parameterIndex">One-based index of the parameter to be set.</param>
				/// <param name="targetSqlType">The SQL type to be sent to the database.</param>
				public void SetNull(OleDbCommand command, int parameterIndex, int sqlType)
				{
					if (command.Parameters.Count < parameterIndex)
						command.Parameters.Add(command.CreateParameter());
					command.Parameters[parameterIndex - 1].Value = Convert.DBNull;
					command.Parameters[parameterIndex - 1].OleDbType = (OleDbType) sqlType;
				}

 private static void AssignParamsToCommand(OleDbCommand command, object[] args)
 {
     foreach (object arg in args)
     {
         OleDbParameter param = command.CreateParameter();
         param.Value = arg == null ? DBNull.Value : arg;
         command.Parameters.Add(param);
     }
 }

 /// <summary>
 /// 增加输出参数 适用于存储过程
 /// </summary>
 /// <param name="cmd">cmd对象</param>
 /// <param name="parameterName">参数名</param>
 /// <param name="dbType">参数类型</param>
 /// <param name="size">参数大小</param>
 public void AddOutParameter(OleDbCommand cmd, string parameterName, DbType dbType, int size)
 {
     OleDbParameter dbParameter = cmd.CreateParameter();
     dbParameter.DbType = dbType;
     dbParameter.ParameterName = parameterName;
     dbParameter.Size = size;
     dbParameter.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(dbParameter);
 }

 /// <summary>
 /// 增加返回参数 适用于存储过程
 /// </summary>
 /// <param name="cmd">cmd对象</param>
 /// <param name="parameterName">参数名</param>
 /// <param name="dbType">参数类型</param>
 public void AddReturnParameter(OleDbCommand cmd, string parameterName, DbType dbType)
 {
     OleDbParameter dbParameter = cmd.CreateParameter();
     dbParameter.DbType = dbType;
     dbParameter.ParameterName = parameterName;
     dbParameter.Direction = ParameterDirection.ReturnValue;
     cmd.Parameters.Add(dbParameter);
 }

 private void InitCmdUpdate()
 {
     CmdUpdate = new OleDbCommand();
     CmdUpdate.CommandText =
         "UPDATE ClientPassport SET PassSery = @PassSery, PassNum = @PassNum, Registred = @Registred, IssuedDate = @IssuedDate, IssuedCode =  @IssuedCode, IssuedBy = @IssuedBy, DateOfBirth = @DateOfBirth " +
         "WHERE(((ClientPassport.[ID]) = @ID)); ";
     CmdUpdate.Connection = DBConnect.DBConnection;
     OleDbParameter prm = new OleDbParameter();
     prm.ParameterName = "PassSery";
     prm.OleDbType = OleDbType.Char;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "PassNum";
     prm.OleDbType = OleDbType.Char;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "Registred";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "IssuedDate";
     prm.OleDbType = OleDbType.DBDate;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "IssuedCode";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "IssuedBy";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "DateOfBirth";
     prm.OleDbType = OleDbType.DBDate;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
     prm = CmdUpdate.CreateParameter();
     prm.ParameterName = "ID";
     prm.OleDbType = OleDbType.Integer;
     prm.Direction = ParameterDirection.Input;
     CmdUpdate.Parameters.Add(prm);
 }

 private void InitCmdInsert()
 {
     CmdInsert = new OleDbCommand();
     CmdInsert.CommandText =
         "INSERT INTO ClientPassport (PassSery,PassNum,Registred,ID,IssuedDate,IssuedCode,IssuedBy,DateOfBirth)" +
         "VALUES (@PassSery,@PassNum,@Registred,@ID,@IssuedDate,@IssuedCode,@IssuedBy,@DateOfBirth)";
     CmdInsert.Connection = DBConnect.DBConnection;
     OleDbParameter prm = new OleDbParameter();
     prm.ParameterName = "PassSery";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "PassNum";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "Registred";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "ID";
     prm.OleDbType = OleDbType.Integer;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "IssuedDate";
     prm.OleDbType = OleDbType.DBDate;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "IssuedCode";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "IssuedBy";
     prm.OleDbType = OleDbType.VarChar;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
     prm = CmdInsert.CreateParameter();
     prm.ParameterName = "DateOfBirth";
     prm.OleDbType = OleDbType.DBDate;
     prm.Direction = ParameterDirection.Input;
     CmdInsert.Parameters.Add(prm);
 }