private void BindParams()
{
if (parameters.Count > 0)
{
statementParametersMemorySize = 0;
int offset = 0;
short sqlRet;
for (int i = 0; i < parameters.Count; i++)
{
if (commandType == CommandType.StoredProcedure)
{
commandText = AddCallParam(commandText);
}
DB2Parameter param = parameters[i];
param.CalculateRequiredmemory();
statementParametersMemorySize += param.requiredMemory + 8;
param.internalBuffer = Marshal.AllocHGlobal(param.requiredMemory);
offset += param.requiredMemory;
param.internalLengthBuffer = Marshal.AllocHGlobal(4);
Marshal.WriteInt32(param.internalLengthBuffer, param.requiredMemory);
sqlRet = param.Bind(this.hwndStmt, (short)(i + 1));
DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "Error binding parameter in DB2Command: ", db2Conn);
}
binded = true;
}
}
public DB2Command GetUpdateCommand()
{
DataTable dt = GetSchema();
if (updateCommand == null)
{
string sets = "";
string wheres = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (sets.Length != 0 && !((bool)dt.Rows[i]["IsAutoIncrement"]))
{
sets += ", ";
}
if (i != 0)
{
wheres += " and ";
}
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (!((bool)dr["IsAutoIncrement"]))
{
sets += String.Format("{0} = ? ", column.ColumnName);
}
wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
}
if (tableName == String.Empty)
{
tableName = (string)dt.Rows[0]["BaseTableName"];
}
DB2Command cmdaux = new DB2Command("update " + tableName + " set " + sets + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (!((bool)dr["IsAutoIncrement"]))
{
DB2Parameter aux = new DB2Parameter("s_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Current;
cmdaux.Parameters.Add(aux);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
DB2Parameter aux = new DB2Parameter("w_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Original;
cmdaux.Parameters.Add(aux);
}
updateCommand = cmdaux;
}
return(updateCommand);
}
public DB2Parameter Add(DB2Parameter value)
{
if (value.ParameterName == null)
{
throw new ArgumentException("parameter must be named");
}
if (IndexOf(value.ParameterName) >= 0)
{
throw new ArgumentException("parameter name is already in collection");
}
base.Add(value);
return(value);
}
public override int Add(object obj)
{
DB2Parameter value = (DB2Parameter)obj;
if (value.ParameterName == null)
{
throw new ArgumentException("parameter must be named");
}
if (IndexOf(value.ParameterName) >= 0)
{
throw new ArgumentException("parameter name is already in collection");
}
return(base.Add(value));
}
public DB2Command GetInsertCommand()
{
DataTable dt = GetSchema();
if (insertCommand == null)
{
string fields = "";
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = dt.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (fields.Length != 0 && !((bool)dr["IsAutoIncrement"]))
{
fields += ", ";
values += ", ";
}
if (!((bool)dr["IsAutoIncrement"]))
{
fields += column.ColumnName;
//values += ":v_" + column.ColumnName;
values += "?";
}
}
if (tableName == String.Empty)
{
tableName = dt.TableName;
}
DB2Command cmdaux = new DB2Command("insert into " + tableName + " (" + fields + ") values (" + values + ")", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = dt.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (!((bool)dr["IsAutoIncrement"]))
{
DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
cmdaux.Parameters.Add(aux);
}
}
insertCommand = cmdaux;
}
return(insertCommand);
}
public DB2Command GetDeleteCommand()
{
DataTable dt = GetSchema();
if (deleteCommand == null)
{
string wheres = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = row.Table.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (i != 0)
{
wheres += " and ";
}
//wheres += String.Format("(({0} is null) or ({0} = v_{0}))", column.ColumnName);
wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
}
if (tableName == String.Empty)
{
tableName = (string)dt.Rows[0]["BaseTableName"];
}
DB2Command cmdaux = new DB2Command("delete from " + tableName + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Original;
cmdaux.Parameters.Add(aux);
}
deleteCommand = cmdaux;
}
return(deleteCommand);
}
object ICloneable.Clone()
{
DB2Parameter clone = new DB2Parameter();
clone.dbType = dbType;
clone.db2Type = db2Type;
clone.db2DataType = db2DataType;
clone.db2LastUsedDataType = db2LastUsedDataType;
clone.direction = direction;
clone.db2Direction = db2Direction;
clone.nullable = nullable;
clone.parameterName = parameterName;
clone.sourceColumn = sourceColumn;
clone.sourceVersion = sourceVersion;
clone.dataVal = dataVal;
clone.scale = scale;
clone.precision = precision;
clone.size = size;
if (dataVal is ICloneable)
{
clone.dataVal = ((ICloneable)dataVal).Clone();
}
return(clone);
}
/// <summary>
/// 将一个对象数组分配给db2Parameter参数数组.
/// </summary>
/// <param name="commandParameters">要分配值的db2Parameter参数数组</param>
/// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>
private static void AssignParameterValues(DB2Parameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
// 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("参数值个数与参数不匹配.");
}
// 给参数赋值
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// If the current array value derives from IDbDataParameter, then assign its Value property
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
/// <summary>
/// 将DataRow类型的列值分配到DB2Parameter参数数组.
/// </summary>
/// <param name="commandParameters">要分配值的db2Parameter参数数组</param>
/// <param name="dataRow">将要分配给存储过程参数的DataRow</param>
private static void AssignParameterValues(DB2Parameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
return;
}
int i = 0;
// 设置参数值
foreach (DB2Parameter commandParameter in commandParameters)
{
// 创建参数名称,如果不存在,只抛出一个异常.
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
// 从dataRow的表中获取为参数数组中数组名称的列的索引.
// 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
/// <summary>
/// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details.
/// </summary>
public override void AddParameter( IDbCommand cmd, string name, long dbType )
{
try
{
DB2Command myc = (DB2Command) cmd;
DB2Parameter param = new DB2Parameter( name, (DB2Type) dbType );
param.Direction = ParameterDirection.Input;
myc.Parameters.Add( param );
}
catch( Exception e )
{
Check.Fail( Error.Unspecified, e.Message );
throw new GentleException( Error.Unspecified, "Unreachable code." );
}
}
/// <summary>
/// 探索运行时的存储过程,返回DB2Parameter参数数组.
/// 初始化参数值为 DBNull.Value.
/// </summary>
/// <param name="connection">一个有效的数据库连接</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">是否包含返回值参数</param>
/// <returns>返回DB2Parameter参数数组</returns>
private static DB2Parameter[] DiscoverSpParameterSet(DB2Connection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
DB2Command cmd = new DB2Command(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
// 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
DB2CommandBuilder.DeriveParameters(cmd);
connection.Close();
// 如果不包含返回值参数,将参数集中的每一个参数删除.
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
// 创建参数数组
DB2Parameter[] discoveredParameters = new DB2Parameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化参数值为 DBNull.Value.
foreach (DB2Parameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
public DB2Command GetUpdateCommand ()
{
DataTable dt = GetSchema();
if (updateCommand == null)
{
string sets = "";
string wheres = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (sets.Length != 0 && !((bool)dt.Rows[i]["IsAutoIncrement"]))
{
sets += ", ";
}
if (i != 0)
{
wheres += " and ";
}
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if(!((bool)dr["IsAutoIncrement"])){sets += String.Format("{0} = ? ", column.ColumnName);}
wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
}
if (tableName == String.Empty)
{
tableName = (string)dt.Rows[0]["BaseTableName"];
}
DB2Command cmdaux = new DB2Command("update " + tableName + " set " + sets + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (!((bool)dr["IsAutoIncrement"]))
{
DB2Parameter aux = new DB2Parameter("s_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Current;
cmdaux.Parameters.Add(aux);
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
DB2Parameter aux = new DB2Parameter("w_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Original;
cmdaux.Parameters.Add(aux);
}
updateCommand = cmdaux;
}
return updateCommand;
}
/// <summary>パラメタライズドクエリにパラメタを設定する。</summary>
/// <param name="parameterName">パラメタライズドクエリのパラメタ名</param>
/// <param name="obj">パラメタの値</param>
/// <param name="dbTypeInfo">パラメタの型（DB2Type）（設定しない場合は、nullを指定）</param>
/// <param name="size">パラメタのサイズ（設定しない場合は、-1を指定）</param>
/// <param name="paramDirection">パラメタの方向</param>
/// <remarks>通常、Ｄａｏ経由で利用する。</remarks>
public override void SetParameter(string parameterName, object obj,
object dbTypeInfo, int size, ParameterDirection paramDirection)
{
if (this.IsDPQ)
{
// 動的パラメタライズド クエリ
// パラメタを保存（実行時に解析してまとめて設定する）
this._parameter.Add(parameterName, obj);
this._parameterType.Add(parameterName, dbTypeInfo);
this._parameterSize.Add(parameterName, size);
this._parameterDirection.Add(parameterName, paramDirection);
}
else
{
// 通常のパラメタライズド クエリ
// パラメタ・ライズド・クエリにパラメタを設定する。
DB2Parameter param = new DB2Parameter();
#region dbTypeInfo
// nullの場合は、設定しない。
if (dbTypeInfo != null)
{
// フラグ
bool isDbType = false;
// 設定方法の選択
string sqlDotnetTypeInfo =
GetConfigParameter.GetConfigValue(PubLiteral.SQL_DOTNET_TYPEINFO);
// デフォルト値対策：設定なし（null）の場合の扱いを決定
if (sqlDotnetTypeInfo != null)
{
// ON / OFF
if (sqlDotnetTypeInfo.ToUpper() == PubLiteral.ON)
{
// ON扱い = DbType
isDbType = true;
}
else if (sqlDotnetTypeInfo.ToUpper() == PubLiteral.OFF)
{
// OFF扱い = DB2Type
//isDbType = false;
}
else
{
// パラメータ・エラー（書式不正）
throw new ArgumentException(
String.Format(PublicExceptionMessage.SWITCH_ERROR, PubLiteral.SQL_DOTNET_TYPEINFO));
}
}
if (isDbType)
{
// ON扱い = DbType
param.DbType = (DbType)dbTypeInfo;
}
else
{
// OFF扱い = DB2Type
param.DB2Type = (DB2Type)dbTypeInfo;
//param.DB2TypeOutputとは？
//ParameterDirection.Outputのとき？
}
}
#endregion
// -1の場合は、設定しない。
if (size != -1)
{
param.Size = size;
}
// パラメタの方向
param.Direction = paramDirection;
// 最後に名前と値を設定（Oracleの件に準拠）
// DB2の場合は、他のデータ プロバイダと異なり、
// パラメタ名に、パラメタの先頭記号が必要になる。
param.ParameterName = DamDB2._paramSign.ToString() + parameterName;
param.Value = obj;
this._cmd.Parameters.Add(param);
}
}
/// <summary>
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
/// </summary>
/// <param name="command">要处理的db2Command</param>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">一个有效的事务或者是null值</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名或都T-SQL命令文本</param>
/// <param name="commandParameters">和命令相关联的db2Parameter参数数组,如果没有参数为'null'</param>
/// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
private static void PrepareCommand(DB2Command command, DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// 给命令分配一个数据库连接.
command.Connection = connection;
// 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText;
// 分配事务
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// 设置命令类型.
command.CommandType = commandType;
// 分配命令参数
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
private Dictionary<string, DbParameterAccessor> ReadIntoParams(String[] queryParameters, String query, String addPrefix)
{
DbCommand dc = CurrentConnection.CreateCommand();
dc.Transaction = CurrentTransaction;
dc.CommandText = query;
dc.CommandType = CommandType.Text;
for (int i = 0; i < queryParameters.Length; i++)
{
AddInput(dc, i.ToString(), queryParameters[i].ToLower());
}
DbDataReader reader = dc.ExecuteReader();
Dictionary<String, DbParameterAccessor>
allParams = new Dictionary<string, DbParameterAccessor>();
int position = 0;
while (reader.Read())
{
String paramName = (reader.IsDBNull(0)) ? null : reader.GetString(0).ToUpper();
String dataType = reader.GetString(1);
int length = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2);
String direction = reader.GetString(3);
DB2Parameter dp = new DB2Parameter();
dp.Direction = GetParameterDirection(direction);
if (paramName != null)
{
dp.ParameterName = addPrefix+paramName; dp.SourceColumn = paramName;
}
else
{
dp.Direction = ParameterDirection.ReturnValue;
}
dp.DB2Type= GetDBType(dataType);
if (length > 0)
{
dp.Size = length;
}
else
{
if (!ParameterDirection.Input.Equals(dp.Direction) || typeof(String).Equals(GetDotNetType(dataType)))
dp.Size = 4000;
}
allParams[NameNormaliser.NormaliseName(paramName)] =
new DbParameterAccessor(dp, GetDotNetType(dataType), position++, dataType);
}
return allParams;
}
protected override void PrepareParameters()
{
// Prepare parameters
SQLParameter parameter;
for (int index = 0; index < _parameterIndexes.Length; index++)
{
parameter = Parameters[_parameterIndexes[index]];
IBM.DB2Parameter dB2Parameter = (IBM.DB2Parameter)_command.CreateParameter();
dB2Parameter.ParameterName = String.Format("@{0}{1}", parameter.Name, index.ToString());
switch (parameter.Direction)
{
case SQLDirection.Out: dB2Parameter.Direction = System.Data.ParameterDirection.Output; break;
case SQLDirection.InOut: dB2Parameter.Direction = System.Data.ParameterDirection.InputOutput; break;
case SQLDirection.Result: dB2Parameter.Direction = System.Data.ParameterDirection.ReturnValue; break;
default: dB2Parameter.Direction = System.Data.ParameterDirection.Input; break;
}
if (parameter.Type is SQLStringType)
{
dB2Parameter.DB2Type = IBM.DB2Type.VarChar;
dB2Parameter.Size = ((SQLStringType)parameter.Type).Length;
}
else if (parameter.Type is SQLBooleanType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Integer;
}
else if (parameter.Type is SQLIntegerType)
{
switch (((SQLIntegerType)parameter.Type).ByteCount)
{
case 1: dB2Parameter.DB2Type = IBM.DB2Type.Integer; break;
case 2: dB2Parameter.DB2Type = IBM.DB2Type.SmallInt; break;
case 8: dB2Parameter.DB2Type = IBM.DB2Type.BigInt; break;
default: dB2Parameter.DB2Type = IBM.DB2Type.Integer; break;
}
}
else if (parameter.Type is SQLNumericType)
{
SQLNumericType type = (SQLNumericType)parameter.Type;
dB2Parameter.DB2Type = IBM.DB2Type.Decimal; // could not be decimal because of issue with DB2/400
dB2Parameter.Scale = type.Scale;
dB2Parameter.Precision = type.Precision;
}
else if (parameter.Type is SQLFloatType)
{
SQLFloatType type = (SQLFloatType)parameter.Type;
if (type.Width == 1)
{
dB2Parameter.DB2Type = IBM.DB2Type.Real;
}
else
{
dB2Parameter.DB2Type = IBM.DB2Type.Double;
}
}
else if (parameter.Type is SQLBinaryType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Blob;
}
else if (parameter.Type is SQLTextType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Clob;
}
else if (parameter.Type is SQLDateTimeType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Timestamp;
}
else if (parameter.Type is SQLDateType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Date;
}
else if (parameter.Type is SQLTimeType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Time;
}
else if (parameter.Type is SQLGuidType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Char;
dB2Parameter.Size = 24;
}
else if (parameter.Type is SQLMoneyType)
{
dB2Parameter.DB2Type = IBM.DB2Type.Decimal;
dB2Parameter.Scale = 28;
dB2Parameter.Precision = 8;
}
else
{
throw new ConnectionException(ConnectionException.Codes.UnknownSQLDataType, parameter.Type.GetType().Name);
}
_command.Parameters.Add(dB2Parameter);
}
}
public DB2Command GetDeleteCommand ()
{
DataTable dt = GetSchema();
if (deleteCommand == null)
{
string wheres = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = row.Table.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (i != 0)
{
wheres += " and ";
}
//wheres += String.Format("(({0} is null) or ({0} = v_{0}))", column.ColumnName);
wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
}
if (tableName == String.Empty)
{
tableName = (string)dt.Rows[0]["BaseTableName"];
}
DB2Command cmdaux = new DB2Command("delete from " + tableName + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
aux.SourceVersion = DataRowVersion.Original;
cmdaux.Parameters.Add(aux);
}
deleteCommand = cmdaux;
}
return deleteCommand;
}
/// <summary>
/// 将db2Parameter参数数组(参数值)分配给db2Command命令.
/// 这个方法将给任何一个参数分配DBNull.Value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param name="command">命令名</param>
/// <param name="commandParameters">DB2Parameters数组</param>
private static void AttachParameters(DB2Command command, DB2Parameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (DB2Parameter p in commandParameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
/// <summary>
/// Add a parameter to the Sql Command
/// </summary>
/// <param name="name">Parameter name</param>
/// <param name="value">Value for the parameter</param>
/// <param name="dbType">Type of parameter in SQL Server</param>
/// <param name="direction">Parameter direction (default is input)</param>
public void AddParameter(string name, object value, DB2Type dbType, ParameterDirection direction = ParameterDirection.Input)
{
DB2Parameter parameter = new DB2Parameter();
parameter.Direction = direction;
parameter.ParameterName = name;
parameter.DB2Type = dbType;
parameter.Value = value;
if (!Parameters.Contains(parameter))
{
Parameters.Add(parameter);
}
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是DB2Helper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">DB2Parameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由DB2Helper提供</param>
/// <returns>返回包含结果集的DB2DataReader</returns>
private static DB2DataReader ExecuteReader(DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, DB2ConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// 创建命令
DB2Command cmd = new DB2Command();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
DB2DataReader dataReader;
if (connectionOwnership == DB2ConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the DB2Reader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
bool canClear = true;
foreach (DB2Parameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
public DB2Command GetInsertCommand ()
{
DataTable dt = GetSchema();
if (insertCommand == null)
{
string fields = "";
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
//DataColumn column = dt.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (fields.Length != 0 && !((bool)dr["IsAutoIncrement"]))
{
fields += ", ";
values += ", ";
}
if(!((bool)dr["IsAutoIncrement"]))
{
fields += column.ColumnName;
//values += ":v_" + column.ColumnName;
values += "?";
}
}
if (tableName == String.Empty)
{
tableName = dt.TableName;
}
DB2Command cmdaux = new DB2Command("insert into " + tableName + " (" + fields + ") values (" + values + ")", dataAdapter.SelectCommand.Connection);
for (int i = 0;i < dt.Rows.Count;i++)
{
//DataColumn column = dt.Columns[i];
DataRow dr = dt.Rows[i];
DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
if (!((bool)dr["IsAutoIncrement"]))
{
DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
aux.Direction = ParameterDirection.Input;
aux.SourceColumn = column.ColumnName;
cmdaux.Parameters.Add(aux);
}
}
insertCommand = cmdaux;
}
return insertCommand;
}
/// <summary>
/// DB2Parameter参数数组的深层拷贝.
/// </summary>
/// <param name="originalParameters">原始参数数组</param>
/// <returns>返回一个同样的参数数组</returns>
private static DB2Parameter[] CloneParameters(DB2Parameter[] originalParameters)
{
DB2Parameter[] clonedParameters = new DB2Parameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (DB2Parameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
object ICloneable.Clone ()
{
DB2Parameter clone = new DB2Parameter();
clone.dbType = dbType;
clone.db2Type = db2Type;
clone.db2DataType = db2DataType;
clone.db2LastUsedDataType = db2LastUsedDataType;
clone.direction = direction;
clone.db2Direction = db2Direction;
clone.nullable = nullable;
clone.parameterName = parameterName;
clone.sourceColumn = sourceColumn;
clone.sourceVersion = sourceVersion;
clone.dataVal = dataVal;
clone.scale = scale;
clone.precision = precision;
clone.size = size;
if(dataVal is ICloneable)
{
clone.dataVal = ((ICloneable)dataVal).Clone();
}
return clone;
}
public DB2Parameter Add(DB2Parameter value)
{
if(value.ParameterName == null)
throw new ArgumentException("parameter must be named");
if(IndexOf(value.ParameterName) >= 0)
throw new ArgumentException("parameter name is already in collection");
base.Add(value);
return value;
}
/// <summary>
/// 设置 <see cref="DB2Parameter"/> 参数对象的值。
/// </summary>
/// <param name="parameter">一个<see cref="DB2Parameter"/> 参数对象</param>
/// <param name="name">表示被加入的查询参数的参数名。</param>
/// <param name="dbType">表示被加入的查询参数的参数数据类型。</param>
/// <param name="size">参数长度。</param>
/// <param name="direction">一个表示参数为输入还是输出类型的枚举值。</param>
/// <param name="nullable">参数是否可为空(DBNull.Value)。</param>
/// <param name="precision">表示查询参数的小数精度。</param>
/// <param name="scale">表述参数的 Scale 属性。</param>
/// <param name="sourceColumn">参数的源列名称，该源列映射到 <see cref="System.Data.DataSet"/> 并用于加载或返回 <seealso cref="System.Data.Common.DbParameter.Value"/>。</param>
/// <param name="sourceVersion">指示参数在加载 <seealso cref="System.Data.Common.DbParameter.Value"/> 时使用的 <see cref="System.Data.DataRowVersion"/>。</param>
/// <param name="value">表示被加入的查询参数的参数值。</param>
protected virtual void ConfigureParameter(DB2Parameter parameter, string name, DB2Type dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
{
parameter.DB2Type = dbType;
parameter.Size = size;
parameter.Value = value ?? DBNull.Value;
parameter.Direction = direction;
parameter.IsNullable = nullable;
parameter.Precision = precision;
parameter.Scale = scale;
parameter.SourceColumn = sourceColumn;
parameter.SourceVersion = sourceVersion;
}