public void AddParameter(string name, DB2Type db2Type, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value) { DB2Parameter param = CreateParameter(name, DbType.AnsiString, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value); param.DB2Type = db2Type; this.command.Parameters.Add(param); }
/// <summary> /// 创建一个 <see cref="DbParameter"/> 脚本查询参数对象。 /// </summary> /// <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> /// <returns>一个 <see cref="DbParameter"/> 脚本查询参数对象。</returns> protected DbParameter CreateParameter(string name, DB2Type dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value) { DB2Parameter parameter = this.CreateParameter(name) as DB2Parameter; ConfigureParameter(parameter, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value); return(parameter); }
public DB2Parameter(string name, DB2Type type) { direction = ParameterDirection.Input; sourceVersion = DataRowVersion.Current; this.ParameterName = name; this.DB2Type = type; }
public DB2Parameter(string name, DB2Type type, int size, string sourceColumn) { direction = ParameterDirection.Input; sourceVersion = DataRowVersion.Current; this.ParameterName = name; this.DB2Type = type; this.Size = size; this.SourceColumn = sourceColumn; }
/// <summary> /// 往 <paramref name="command"/> 对象中加入一个查询参数; /// </summary> /// <param name="command">表示一个 <see cref="DbCommand"/> 对象,其必须为一个 <see cref="DB2Command"/> 实例。</param> /// <param name="name">表示被加入的查询参数的参数名。</param> /// <param name="dbType">表示被加入的查询参数的参数数据类型。</param> /// <param name="size">参数长度。</param> public void AddOutParameter(DbCommand command, string name, DB2Type dbType, int size) { ParameterDirection direction = ParameterDirection.Output; string conns = ConnectionString.ToUpper(CultureInfo.CurrentCulture); if (conns.Contains("SERVERTYPE=UNIDATA") && direction == ParameterDirection.Output) { direction = ParameterDirection.InputOutput; } // standard set of OutParameter for DB2 and IDS and UniVerse this.AddParameter(command, name, dbType, size, direction, true, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value); }
/// <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); } }
public DB2Parameter(string parameterName, DB2Type db2Type, int size, ParameterDirection parameterDirection, bool isNullable, byte precision, byte scale, string srcColumn, DataRowVersion srcVersion, object value) { this.ParameterName = parameterName; this.DB2Type = db2Type; this.Size = size; this.Direction = parameterDirection; this.IsNullable = isNullable; this.Precision = precision; this.Scale = scale; this.SourceColumn = srcColumn; this.SourceVersion = srcVersion; this.Value = value; }
/// <summary> /// Obtain the integer value of the database type corresponding to the given system type. /// The value returned must be castable to a valid type for the current persistence engine. /// </summary> /// <param name="type">The system type.</param> /// <returns>The corresponding database type.</returns> public override long GetDbType(Type type) { DB2Type result = DB2Type.Integer; if (type.Equals(typeof(int)) || type.IsEnum) { result = DB2Type.Integer; } else if (type.Equals(typeof(long))) { result = DB2Type.BigInt; } else if (type.Equals(typeof(float))) { result = DB2Type.Real; } else if (type.Equals(typeof(double))) { result = DB2Type.Double; } else if (type.Equals(typeof(decimal))) { result = DB2Type.Decimal; } else if (type.Equals(typeof(DateTime))) { result = DB2Type.Timestamp; } else if (type.Equals(typeof(bool)) || type.Equals(typeof(Byte)) || type.Equals(typeof(byte))) { result = DB2Type.SmallInt; } else if (type.Equals(typeof(string))) { result = DB2Type.VarChar; } else if (type.Equals(typeof(byte[])) || type.Equals(typeof(Byte[]))) { // hmm.. possible type size loss here as we dont know the length of the array result = DB2Type.Blob; } else { Check.Fail(Error.UnsupportedPropertyType, type.Name, provider.Name); } return((long)result); }
/// <summary> /// 从DataTable导入数据到数据库表(适用于小批量数据导入) /// </summary> /// <param name="tableName">表名</param> /// <param name="dt">数据表(字段名通过ColumnName来指定)</param> /// <returns></returns> public override int LoadDataInDataTable(string tableName, DataTable dt) { Log.BLog.Write(Log.BLog.LogLevel.DEBUG, "00"); if (dt == null || dt.Rows.Count < 1) { return(0); } int rowsCount = dt.Rows.Count; int colsCount = dt.Columns.Count; List <string> columnNames = new List <string>(); //行列转换 object[][] allData = new object[colsCount][]; for (int col = 0; col < colsCount; col++) { allData[col] = new object[rowsCount]; columnNames.Add(dt.Columns[col].ColumnName); } for (int row = 0; row < rowsCount; row++) { for (int col = 0; col < colsCount; col++) { allData[col][row] = dt.Rows[row][col]; } } DB2Command command = _conn.CreateCommand(); command.CommandTimeout = base.CommandTimeout; command.CommandType = CommandType.Text; command.ArrayBindCount = rowsCount; command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, string.Join(", ", columnNames), "@" + string.Join(", @", columnNames)); int colIndex = 0; Dictionary <string, int> dicMaxLength = GetColumnsMaxLength(dt); foreach (DataColumn col in dt.Columns) { int maxLength = 0; dicMaxLength.TryGetValue(col.ColumnName, out maxLength); DB2Type type = GetDbType(dt, col, maxLength); DB2Parameter colParameter = new DB2Parameter(col.ColumnName, type); colParameter.Direction = ParameterDirection.Input; colParameter.Value = allData[colIndex]; command.Parameters.Add(colParameter); colIndex++; } return(command.ExecuteNonQuery()); }
public DB2Parameter Add(string paramName, DB2Type dbType, int size, string sourceColumn) { return(Add(new DB2Parameter(paramName, dbType, size, sourceColumn))); }
public DB2Parameter Add(string paramName, DB2Type dbType, int size) { return(Add(new DB2Parameter(paramName, dbType, size))); }
public DB2Parameter Add(string paramName, DB2Type type) { return(Add(new DB2Parameter(paramName, type))); }
/// <summary> /// Determine the data type based on the value /// </summary> private void InferType() { if (Value == null) { throw new ArgumentException("No DB2Parameter.Value found"); } if (Value is IConvertible) { switch (((IConvertible)Value).GetTypeCode()) { case TypeCode.Char: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_WCHAR; break; case TypeCode.Boolean: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_BIT; break; case TypeCode.SByte: case TypeCode.Byte: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_UTINYINT; break; case TypeCode.UInt16: case TypeCode.Int16: dbType = DbType.Int16; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_SMALLINT; break; case TypeCode.UInt32: case TypeCode.Int32: dbType = DbType.Int32; db2Type = DB2Type.Integer; db2DataType = DB2Constants.SQL_INTEGER; break; case TypeCode.UInt64: case TypeCode.Int64: dbType = DbType.Int64; db2Type = DB2Type.BigInt; db2DataType = DB2Constants.SQL_BIGINT; break; case TypeCode.Single: dbType = DbType.Single; db2Type = DB2Type.Float; db2DataType = DB2Constants.SQL_REAL; break; case TypeCode.Double: dbType = DbType.Double; db2Type = DB2Type.Double; db2DataType = DB2Constants.SQL_DOUBLE; break; case TypeCode.Decimal: dbType = DbType.Decimal; db2Type = DB2Type.Decimal; db2DataType = DB2Constants.SQL_DECIMAL; break; case TypeCode.DateTime: dbType = DbType.DateTime; db2Type = DB2Type.Timestamp; db2DataType = DB2Constants.SQL_TYPE_TIMESTAMP; break; case TypeCode.String: dbType = DbType.String; db2Type = DB2Type.VarChar; db2DataType = DB2Constants.SQL_WCHAR; break; case TypeCode.Object: case TypeCode.DBNull: case TypeCode.Empty: throw new SystemException("Unknown data type"); default: throw new SystemException("Value is of unknown data type"); } } else if (Value is byte[]) { dbType = DbType.Binary; db2Type = DB2Type.VarBinary; db2DataType = DB2Constants.SQL_VARBINARY; } else if (Value is TimeSpan) { dbType = DbType.Time; db2Type = DB2Type.Time; db2DataType = DB2Constants.SQL_TYPE_TIME; } else { throw new NotSupportedException("Value is of unsupported data type"); } }
private static void SetInValueInternalWithOdbType <TKey>(this DB2ParametersWrapper <TKey> paramWrapper, TKey key, object value, DB2Type odbType, ParameterDirection pDir = ParameterDirection.Input) { value = value ?? DBNull.Value; //aggiorno if (paramWrapper.ContainsKey(key)) { paramWrapper[key].Value = value; } else { var p = new DB2Parameter { Direction = pDir, ParameterName = "@par_" + key, DB2Type = odbType, Value = value, }; paramWrapper[key] = p; } }
/// <summary> /// 往 <paramref name="command"/> 对象中加入一个查询参数; /// </summary> /// <param name="command">表示一个 <see cref="DbCommand"/> 对象,其必须为一个 <see cref="DB2Command"/> 实例。</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> public virtual void AddParameter(DbCommand command, string name, DB2Type dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value) { DbParameter parameter = this.CreateParameter(name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value); command.Parameters.Add(parameter); }
/// <summary> /// 把数据转成指定类型 /// </summary> /// <param name="value">数据</param> /// <param name="dbType">指定类型</param> /// <returns></returns> public string ConvetTo(string value, DbType dbType) { DB2Type sdb = (DB2Type)dbType; return("cast(" + value + " as " + sdb + ")"); }
/// <summary> /// 创建一个 <see cref="DbParameter"/> 脚本查询参数对象。 /// </summary> /// <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> /// <returns>一个 <see cref="DbParameter"/> 脚本查询参数对象。</returns> protected DbParameter CreateParameter(string name, DB2Type dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value) { DB2Parameter parameter = this.CreateParameter(name) as DB2Parameter; ConfigureParameter(parameter, name, dbType, size, direction, nullable, precision, scale, sourceColumn, sourceVersion, value); return parameter; }
/// <summary> /// Determine the data type based on the value /// </summary> private void InferType() { if(Value == null) throw new ArgumentException("No DB2Parameter.Value found"); if(Value is IConvertible) { switch(((IConvertible)Value).GetTypeCode()) { case TypeCode.Char: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_WCHAR; break; case TypeCode.Boolean: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_BIT; break; case TypeCode.SByte: case TypeCode.Byte: dbType = DbType.Byte; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_UTINYINT; break; case TypeCode.UInt16: case TypeCode.Int16: dbType = DbType.Int16; db2Type = DB2Type.SmallInt; db2DataType = DB2Constants.SQL_SMALLINT; break; case TypeCode.UInt32: case TypeCode.Int32: dbType = DbType.Int32; db2Type = DB2Type.Integer; db2DataType = DB2Constants.SQL_INTEGER; break; case TypeCode.UInt64: case TypeCode.Int64: dbType = DbType.Int64; db2Type = DB2Type.BigInt; db2DataType = DB2Constants.SQL_BIGINT; break; case TypeCode.Single: dbType = DbType.Single; db2Type = DB2Type.Float; db2DataType = DB2Constants.SQL_REAL; break; case TypeCode.Double: dbType = DbType.Double; db2Type = DB2Type.Double; db2DataType = DB2Constants.SQL_DOUBLE; break; case TypeCode.Decimal: dbType = DbType.Decimal; db2Type = DB2Type.Decimal; db2DataType = DB2Constants.SQL_DECIMAL; break; case TypeCode.DateTime: dbType = DbType.DateTime; db2Type = DB2Type.Timestamp; db2DataType = DB2Constants.SQL_TYPE_TIMESTAMP; break; case TypeCode.String: dbType = DbType.String; db2Type = DB2Type.VarChar; db2DataType = DB2Constants.SQL_WCHAR; break; case TypeCode.Object: case TypeCode.DBNull: case TypeCode.Empty: throw new SystemException("Unknown data type"); default: throw new SystemException("Value is of unknown data type"); } } else if(Value is byte[]) { dbType = DbType.Binary; db2Type = DB2Type.VarBinary; db2DataType = DB2Constants.SQL_VARBINARY; } else if(Value is TimeSpan) { dbType = DbType.Time; db2Type = DB2Type.Time; db2DataType = DB2Constants.SQL_TYPE_TIME; } else { throw new NotSupportedException("Value is of unsupported data type"); } }
/// <summary> /// 往 <paramref name="command"/> 对象中加入一个查询参数; /// </summary> /// <param name="command">表示一个 <see cref="DbCommand"/> 对象,其必须为一个 <see cref="DB2Command"/> 实例。</param> /// <param name="name">表示被加入的查询参数的参数名。</param> /// <param name="dbType">表示被加入的查询参数的参数数据类型。</param> /// <param name="direction"></param> /// <param name="sourceColumn"></param> /// <param name="sourceVersion"></param> /// <param name="value">表示被加入的查询参数的参数值。</param> public void AddParameter(DbCommand command, string name, DB2Type dbType, ParameterDirection direction, string sourceColumn, DataRowVersion sourceVersion, object value) { this.AddParameter(command, name, dbType, 0, direction, false, 0, 0, sourceColumn, sourceVersion, value); }
public static string AsCSharpType(this DB2Type type, bool isNullable = false) { //return "string"; switch (type) { case DB2Type.VARCHAR: return("string"); case DB2Type.CHAR: return("string"); case DB2Type.CLOB: return("string"); case DB2Type.DBCLOB: return("string"); case DB2Type.GRAPHIC: return("string"); case DB2Type.LONGVARC: return("string"); case DB2Type.LONGVARCHAR: return("string"); case DB2Type.LONGVARG: return("string"); case DB2Type.LONGVARGRAPHIC: return("string"); case DB2Type.VARG: return("string"); case DB2Type.VARGRAPHIC: return("string"); case DB2Type.DECIMAL: return("decimal" + ((isNullable) ? "?" : "")); case DB2Type.NUMERIC: return("decimal" + ((isNullable) ? "?" : "")); case DB2Type.DOUBLE: return("decimal" + ((isNullable) ? "?" : "")); case DB2Type.FLOAT: return("double" + ((isNullable) ? "?" : "")); case DB2Type.BIGINT: return("Int64" + ((isNullable) ? "?" : "")); case DB2Type.INTEGER: return("Int32" + ((isNullable) ? "?" : "")); case DB2Type.SMALLINT: return("Int16" + ((isNullable) ? "?" : "")); case DB2Type.BINARY: return("byte[]"); case DB2Type.BLOB: return("byte[]"); case DB2Type.VARBIN: return("byte[]"); case DB2Type.VARBINARY: return("byte[]"); case DB2Type.TIME: return("TimeSpan" + ((isNullable) ? "?" : "")); case DB2Type.TIMESTAMP: return("DateTime" + ((isNullable) ? "?" : "")); case DB2Type.DATE: return("DateTime" + ((isNullable) ? "?" : "")); case DB2Type.REAL: return("single"); default: throw new NotImplementedException("Property Type unexpected"); } }
private static void ThrowArgumentException <TKey>(TKey key, DB2Type oraType) { throw new ArgumentException(string.Format( $"Output parameter key {key} was registered as {Enum.GetName(typeof(DB2Type), oraType)}")); }
public DB2Parameter Add(string paramName, DB2Type dbType, int size) { return Add(new DB2Parameter(paramName, dbType, size)); }
/// <summary> /// 往 <paramref name="command"/> 对象中加入一个查询参数; /// </summary> /// <param name="command">表示一个 <see cref="DbCommand"/> 对象,其必须为一个 <see cref="DB2Command"/> 实例。</param> /// <param name="name">表示被加入的查询参数的参数名。</param> /// <param name="dbType">表示被加入的查询参数的参数数据类型。</param> /// <param name="value">表示被加入的查询参数的参数值。</param> public void AddInParameter(DbCommand command, string name, DB2Type dbType, object value) { this.AddParameter(command, name, dbType, ParameterDirection.Input, String.Empty, DataRowVersion.Default, 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; }
/// <summary> /// 往 <paramref name="command"/> 对象中加入一个查询参数; /// </summary> /// <param name="command">表示一个 <see cref="DbCommand"/> 对象,其必须为一个 <see cref="DB2Command"/> 实例。</param> /// <param name="name">表示被加入的查询参数的参数名。</param> /// <param name="dbType">表示被加入的查询参数的参数数据类型。</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> public void AddInParameter(DbCommand command, string name, DB2Type dbType, string sourceColumn, DataRowVersion sourceVersion) { this.AddParameter(command, name, dbType, 0, ParameterDirection.Input, true, 0, 0, sourceColumn, sourceVersion, null); }
public DB2Parameter Add(string paramName, DB2Type type) { return Add(new DB2Parameter(paramName, type)); }
public DB2Parameter Add(string paramName, DB2Type dbType, int size, string sourceColumn) { return Add(new DB2Parameter(paramName, dbType, size, sourceColumn)); }