/// <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 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>
/// <param name="targetSqlType">The SQL type to be sent to the database.</param>
public void SetObject(OleDbCommand command, int parameterIndex, Object parameter, int targetSqlType)
{
if (command.Parameters.Count < parameterIndex)
command.Parameters.Add(command.CreateParameter());
command.Parameters[parameterIndex - 1].Value = parameter;
command.Parameters[parameterIndex - 1].OleDbType = (OleDbType) targetSqlType;
}
/// <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);
}