/// <summary>
/// Serialize the passed in value to be written out as valid SQL
/// </summary>
/// <typeparam name="TDotNetType">
/// The mapped .net type
/// </typeparam>
/// <param name="dbType">
/// The <see cref="NpgsqlDbType"/> datatype
/// </param>
/// <param name="isArray">
/// Indicates if this is an array parameter
/// </param>
/// <param name="value">
/// The parameter value to serialize to SQL string
/// </param>
/// <returns>
/// A serialized string that is a valid SQL representation of the value
/// </returns>
internal static string RetrieveValueString <TDotNetType>(NpgsqlDbType dbType, bool isArray, object value)
{
if (value == DBNull.Value)
{
return("null");
}
return(isArray
? string.Format("'{{{0}}}'::{1}[]", string.Join(", ", (IEnumerable <TDotNetType>)value), dbType.ToString().ToLower())
: string.Format("'{0}'::{1}", value, dbType.ToString().ToLower()));
}
/// <summary>
/// 将数组对象拼接成 array[''] 使用的条件
/// </summary>
/// <param name="items">待拼接的数组</param>
/// <param name="dbtype">数据库字段类型</param>
/// <param name="enumtype">指定的枚举类型</param>
/// <returns></returns>
protected string JoinTo(System.Collections.ICollection items, NpgsqlDbType dbtype, string enumtype)
{
string _dbType_text = dbtype == NpgsqlDbType.Enum ? enumtype : dbtype.ToString();
StringBuilder sb = new StringBuilder();
int i = 0;
foreach (var item in items)
{
string pName = Guid.NewGuid().ToString("N");
AddParameter(pName, item.ToString());
sb.Append("@" + pName + "::" + _dbType_text);
if (++i < items.Count)
{
sb.Append(",");
}
}
return(sb.ToString());
}
public static string GetNpgsqlDataTypeStringValue(NpgsqlDbType npgsqlDbType)
{
switch (npgsqlDbType)
{
case NpgsqlDbType.Char: return("character");
case NpgsqlDbType.Varchar:
case NpgsqlDbType.Text: return("character varying");
case NpgsqlDbType.Timestamp: return("timestamp without time zone");
case NpgsqlDbType.TimestampTZ: return("timestamp with time zone");
case NpgsqlDbType.Time: return("time without time zone");
case NpgsqlDbType.TimeTZ: return("time with time zone");
default: return(npgsqlDbType.ToString().ToLowerInvariant());
}
//return npgsqlDbTypes2.First(x => x.Item1 == npgsqlDbType).Item2;
}
public async Task Internal_uint_types(NpgsqlDbType npgsqlDbType)
{
var postgresType = npgsqlDbType.ToString().ToLowerInvariant();
using var conn = await OpenConnectionAsync();
using var cmd = new NpgsqlCommand($"SELECT @max, 4294967295::{postgresType}, @eight, 8::{postgresType}", conn);
cmd.Parameters.AddWithValue("max", npgsqlDbType, uint.MaxValue);
cmd.Parameters.AddWithValue("eight", npgsqlDbType, 8u);
using var reader = await cmd.ExecuteReaderAsync();
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
{
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(uint)));
}
Assert.That(reader.GetValue(0), Is.EqualTo(uint.MaxValue));
Assert.That(reader.GetValue(1), Is.EqualTo(uint.MaxValue));
Assert.That(reader.GetValue(2), Is.EqualTo(8u));
Assert.That(reader.GetValue(3), Is.EqualTo(8u));
}
public JsonNetTests(NpgsqlDbType npgsqlDbType)
{
_npgsqlDbType = npgsqlDbType;
_pgTypeName = npgsqlDbType.ToString().ToLower();
}
public static DbType PgsqlDbType2DbType(NpgsqlDbType pgsqlDbType_in) {
switch (pgsqlDbType_in) {
case NpgsqlDbType.Bigint:
return DbType.Int64;
case NpgsqlDbType.Integer:
return DbType.Int32;
case NpgsqlDbType.Smallint:
return DbType.Int16;
case NpgsqlDbType.Boolean:
return DbType.Boolean;
case NpgsqlDbType.Varchar:
case NpgsqlDbType.Text:
return DbType.String;
case NpgsqlDbType.TimestampTZ:
case NpgsqlDbType.Timestamp:
return DbType.DateTime;
case NpgsqlDbType.Real:
return DbType.Single;
case NpgsqlDbType.Double:
return DbType.Double;
case NpgsqlDbType.Numeric:
return DbType.Decimal;
case NpgsqlDbType.Bytea:
return DbType.Binary;
case NpgsqlDbType.Date:
return DbType.Date;
case NpgsqlDbType.Time:
return DbType.Time;
case NpgsqlDbType.Money:
return DbType.Decimal;
#region default: throw new Exception("...");
default: {
throw new Exception(string.Format(
"undefined variable type: {0}",
pgsqlDbType_in.ToString()
));
}
#endregion
}
}
public static string ToSql(this NpgsqlDbType dbType)
{
return(dbType.ToString().ToUpper());
}
/// <summary>
/// The add profile column.
/// </summary>
/// <param name="name">
/// The name.
/// </param>
/// <param name="columnType">
/// The column type.
/// </param>
/// <param name="size">
/// The size.
/// </param>
public static void AddProfileColumn([NotNull] string name, NpgsqlDbType columnType, int size)
{
// get column type...
string type = columnType.ToString();
if (size > 0)
{
type += "(" + size + ")";
}
string sql = "ALTER TABLE {0} ADD {1} {2}".FormatWith(
MsSqlDbAccess.GetObjectName("userprofile"), name, type);
using (var cmd = MsSqlDbAccess.GetCommand(sql, true))
{
cmd.CommandType = CommandType.Text;
MsSqlDbAccess.Current.ExecuteNonQuery(cmd);
}
}