public static bool DeleteByModule(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_mediafile "); sqlCommand.Append("WHERE "); sqlCommand.Append("fileid "); sqlCommand.Append("IN ("); sqlCommand.Append("SELECT fileid FROM mp_mediafile WHERE trackid IN ("); sqlCommand.Append("SELECT trackid FROM mp_mediatrack WHERE playerid IN ("); sqlCommand.Append("SELECT playerid FROM mp_mediaplayer WHERE moduleid = :moduleid"); sqlCommand.Append(")"); sqlCommand.Append(")"); sqlCommand.Append(")"); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return (rowsAffected > -1); }
public void Range() { using (var conn = OpenConnection()) using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", conn)) { var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Range | NpgsqlDbType.Integer) { Value = NpgsqlRange<int>.Empty() }; var p2 = new NpgsqlParameter { ParameterName = "p2", Value = new NpgsqlRange<int>(1, 10) }; var p3 = new NpgsqlParameter { ParameterName = "p3", Value = new NpgsqlRange<int>(1, false, 10, false) }; var p4 = new NpgsqlParameter { ParameterName = "p4", Value = new NpgsqlRange<int>(0, false, true, 10, false, false) }; Assert.That(p2.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Range | NpgsqlDbType.Integer)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); using (var reader = cmd.ExecuteReader()) { reader.Read(); Assert.That(reader[0].ToString(), Is.EqualTo("empty")); Assert.That(reader[1].ToString(), Is.EqualTo("[1,11)")); Assert.That(reader[2].ToString(), Is.EqualTo("[2,10)")); Assert.That(reader[3].ToString(), Is.EqualTo("(,10)")); } } }
public DbDataReader GetSiteSettingsExList(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT e.* "); sqlCommand.Append("FROM mp_sitesettingsex e "); sqlCommand.Append("JOIN "); sqlCommand.Append("mp_sitesettingsexdef d "); sqlCommand.Append("ON "); sqlCommand.Append("e.keyname = d.keyname "); sqlCommand.Append("AND e.groupname = d.groupname "); sqlCommand.Append("WHERE "); sqlCommand.Append("e.siteid = :siteid "); sqlCommand.Append("ORDER BY d.groupname, d.sortorder "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = siteId; return AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), arParams); }
public DbDataReader GetUserCountByYearMonth(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("cast(date_part('year', datecreated) as int4) As y, "); sqlCommand.Append("cast(date_part('month', datecreated) as int4) As m, "); sqlCommand.Append("cast(date_part('year', datecreated) as varchar(10)) || '-' || cast(date_part('month', datecreated) as varchar(3)) As label, "); sqlCommand.Append("COUNT(*) As users "); sqlCommand.Append("FROM "); sqlCommand.Append("mp_users "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteid = :siteid "); sqlCommand.Append("GROUP BY cast(date_part('year', datecreated) as int4), cast(date_part('month', datecreated) as int4), cast(date_part('year', datecreated) as varchar(10)) || '-' || cast(date_part('month', datecreated) as varchar(3)) "); sqlCommand.Append("ORDER BY cast(date_part('year', datecreated) as int4), cast(date_part('month', datecreated) as int4) "); sqlCommand.Append("; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = siteId; return AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), arParams); }
public static void CreatePersonalizationBlob( Guid userGuid, Guid pathId, byte[] dataBlob, DateTime lastUpdateTime) { NpgsqlParameter[] arParams = new NpgsqlParameter[5]; arParams[0] = new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = Guid.NewGuid().ToString(); arParams[1] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = userGuid.ToString(); arParams[2] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pathId.ToString(); arParams[3] = new NpgsqlParameter("pagesettings", NpgsqlTypes.NpgsqlDbType.Bytea); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = dataBlob; arParams[4] = new NpgsqlParameter("lastupdate", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = lastUpdateTime; NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_sitepersonalizationperuser_insert(:id,:userid,:pathid,:pagesettings,:lastupdate)", arParams); }
public static Guid CreatePath(int siteId, String path) { Guid newPathID = Guid.NewGuid(); NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("pathid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = newPathID; arParams[1] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteId; arParams[2] = new NpgsqlParameter("path", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = path; arParams[3] = new NpgsqlParameter("loweredpath", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = path.ToLower(); NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_sitepaths_insert(:pathid,:siteid,:path,:loweredpath)", arParams); return newPathID; }
internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, NpgsqlParameter[] pars) { int result = 0; using (NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandType = cmdType; cmd.CommandText = CommandName; cmd.Parameters.AddRange(pars); try { if (con.State != ConnectionState.Open) { con.Open(); } result = cmd.ExecuteNonQuery(); } catch (Exception e) { //Log.Error(e); throw; } } } return (result > 0); }
public DbDataReader GetUserList(int siteId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT UserID, "); sqlCommand.Append("name, "); sqlCommand.Append("passwordsalt, "); sqlCommand.Append("pwd, "); sqlCommand.Append("email "); sqlCommand.Append("FROM mp_users "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteid = :siteid "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("email"); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Value = siteId; return AdoHelper.ExecuteReader( readConnectionString, CommandType.Text, sqlCommand.ToString(), arParams); }
public static bool AddUser( int roleId, int userId, Guid roleGuid, Guid userGuid ) { NpgsqlParameter[] arParams = new NpgsqlParameter[4]; arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = roleId; arParams[1] = new NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = userId; arParams[2] = new NpgsqlParameter("roleguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = roleGuid.ToString(); arParams[3] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = userGuid.ToString(); int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteScalar( ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_userroles_insert(:roleid,:userid,:roleguid,:userguid)", arParams)); return (rowsAffected > -1); }
public override DbParameter CreateParameter(string parameterName, AbstractDbType dbType, string?udtTypeName, bool nullable, object?value) { if (dbType.IsDate()) { if (value is DateTime dt) { AssertDateTime(dt); } else if (value is Date d) { value = new NpgsqlDate((DateTime)d); } } var result = new Npgsql.NpgsqlParameter(parameterName, value ?? DBNull.Value) { IsNullable = nullable }; result.NpgsqlDbType = dbType.PostgreSql; if (udtTypeName != null) { result.DataTypeName = udtTypeName; } return(result); }
public DataTable GetDataTable(string command, NpgsqlParameter [] parameters) { DataTable dt; Console.WriteLine(parameters[0].Value); dt = SqlHelper.ExecuteDataTable(this.ConnectionString, CommandType.StoredProcedure, command, parameters); return dt; }
/// <summary> /// Inserts a row in the mp_BannedIPAddresses table. Returns new integer id. /// </summary> /// <param name="bannedIP"> bannedIP </param> /// <param name="bannedUTC"> bannedUTC </param> /// <param name="bannedReason"> bannedReason </param> /// <returns>int</returns> public static int Add( string bannedIP, DateTime bannedUtc, string bannedReason) { NpgsqlParameter[] arParams = new NpgsqlParameter[3]; arParams[0] = new NpgsqlParameter("bannedip", NpgsqlTypes.NpgsqlDbType.Varchar, 50); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = bannedIP; arParams[1] = new NpgsqlParameter("bannedutc", NpgsqlTypes.NpgsqlDbType.Date); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = bannedUtc; arParams[2] = new NpgsqlParameter("bannedreason", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = bannedReason; int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_bannedipaddresses_insert(:bannedip,:bannedutc,:bannedreason)", arParams)); return newID; }
/// <summary> /// Inserts a row in the mp_Surveys table. Returns rows affected count. /// </summary> /// <param name="surveyGuid"> surveyGuid </param> /// <param name="siteGuid"> siteGuid </param> /// <param name="surveyName"> surveyName </param> /// <param name="creationDate"> creationDate </param> /// <param name="startPageText"> startPageText </param> /// <param name="endPageText"> endPageText </param> /// <returns>int</returns> public static int Add( Guid surveyGuid, Guid siteGuid, string surveyName, DateTime creationDate, string startPageText, string endPageText) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_surveys ("); sqlCommand.Append("surveyguid, "); sqlCommand.Append("siteguid, "); sqlCommand.Append("surveyname, "); sqlCommand.Append("creationdate, "); sqlCommand.Append("startpagetext, "); sqlCommand.Append("endpagetext )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":surveyguid, "); sqlCommand.Append(":siteguid, "); sqlCommand.Append(":surveyname, "); sqlCommand.Append(":creationdate, "); sqlCommand.Append(":startpagetext, "); sqlCommand.Append(":endpagetext "); sqlCommand.Append(")"); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[6]; arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = surveyGuid.ToString(); arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new NpgsqlParameter("surveyname", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = surveyName; arParams[3] = new NpgsqlParameter("creationdate", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = creationDate; arParams[4] = new NpgsqlParameter("startpagetext", NpgsqlTypes.NpgsqlDbType.Text); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = startPageText; arParams[5] = new NpgsqlParameter("endpagetext", NpgsqlTypes.NpgsqlDbType.Text); arParams[5].Direction = ParameterDirection.Input; arParams[5].Value = endPageText; int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return rowsAffected; }
public void Bug1011018() { var p = new NpgsqlParameter(); p.NpgsqlDbType = NpgsqlDbType.Time; p.Value = DateTime.Now; Object o = p.Value; }
public void Constructor1() { var p = new NpgsqlParameter(); Assert.AreEqual(DbType.String, p.DbType, "DbType"); Assert.AreEqual(ParameterDirection.Input, p.Direction, "Direction"); Assert.IsFalse(p.IsNullable, "IsNullable"); #if NET_2_0 //Assert.AreEqual (0, p.LocaleId, "LocaleId"); #endif Assert.AreEqual(string.Empty, p.ParameterName, "ParameterName"); Assert.AreEqual(0, p.Precision, "Precision"); Assert.AreEqual(0, p.Scale, "Scale"); Assert.AreEqual(0, p.Size, "Size"); Assert.AreEqual(string.Empty, p.SourceColumn, "SourceColumn"); #if NET_2_0 Assert.IsFalse(p.SourceColumnNullMapping, "SourceColumnNullMapping"); #endif Assert.AreEqual(DataRowVersion.Current, p.SourceVersion, "SourceVersion"); Assert.AreEqual(NpgsqlDbType.Text, p.NpgsqlDbType, "NpgsqlDbType"); #if NET_2_0 Assert.IsNull(p.NpgsqlValue, "NpgsqlValue"); #endif Assert.IsNull(p.Value, "Value"); #if NET_2_0 //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionDatabase, "XmlSchemaCollectionDatabase"); //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionName, "XmlSchemaCollectionName"); //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionOwningSchema, "XmlSchemaCollectionOwningSchema"); #endif }
public NpgQuery Insert(string table, string[] columns, IEnumerable<object[]> values) { var valuesArray = values as object[][] ?? values.ToArray(); var parameters = new NpgsqlParameter[columns.Length * valuesArray.Count()]; var insertDataScript = new string[valuesArray.Count()]; var parametersIndex = 0; for (var i = 0; i < valuesArray.Length; i++) { var i1 = i; insertDataScript[i] = string.Format( "({0})", string.Join(", ", columns.Select(c => string.Format(":{0}", c + i1)))); var data = valuesArray[i]; for (var j = 0; j < columns.Length; j++) { var column = columns[j]; var value = data[j]; parameters[parametersIndex++] = new NpgsqlParameter(column + i1, value); } } var scriptRow = string.Format( "INSERT INTO {0} ({1}) VALUES {2}", Quote(table), string.Join(", ", columns.Select(Quote)), string.Join(", ", insertDataScript)); return new NpgQuery(scriptRow, parameters); }
public void Uuid() { var expected = new Guid("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"); var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Uuid); var p2 = new NpgsqlParameter("p2", DbType.Guid); var p3 = new NpgsqlParameter { ParameterName = "p3", Value = expected }; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); p1.Value = p2.Value = expected; var reader = cmd.ExecuteReader(); reader.Read(); for (var i = 0; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetGuid(i), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<Guid>(i), Is.EqualTo(expected)); Assert.That(reader.GetValue(i), Is.EqualTo(expected)); Assert.That(reader.GetString(i), Is.EqualTo(expected.ToString())); Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(Guid))); } reader.Dispose(); cmd.Dispose(); }
public NpgQuery GetInsertScriptItem(TableData tableData) { if (tableData.Data.Length == 0) return null; var parameters = new NpgsqlParameter[tableData.Columns.Length*tableData.Data.Length]; var insertDataScript = new string[tableData.Data.Length]; var parametersIndex = 0; for (var i = 0; i < tableData.Data.Length; i++) { var i1 = i; insertDataScript[i] = string.Format( "({0})", string.Join(", ", tableData.Columns.Select(c => string.Format(":{0}", c + i1)))); var data = tableData.Data[i]; for (var j = 0; j < tableData.Columns.Length; j++) { var column = tableData.Columns[j]; var value = data[j]; parameters[parametersIndex++] = new NpgsqlParameter(column + i1, value); } } var scriptRow = string.Format( "INSERT INTO {0} ({1}) VALUES {2}", string.Format("\"{0}\"", tableData.TableName), string.Join(", ", tableData.Columns.Select(c => string.Format("\"{0}\"", c))), string.Join(", ", insertDataScript)); return new NpgQuery(scriptRow, parameters); }
/// <summary> /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value /// greater than the provided trackOrder. /// </summary> /// <param name="playerID">The ID of the Player.</param> /// <param name="trackOrder">The TrackOrder value.</param> /// <returns>The number of rows affected by the update.</returns> public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_mediatrack "); sqlCommand.Append("SET trackorder = trackorder - 1 "); sqlCommand.Append("WHERE "); sqlCommand.Append("playerid = :playerid "); sqlCommand.Append("AND trackorder > :trackorder "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("playerid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = playerId; arParams[1] = new NpgsqlParameter("trackorder", NpgsqlTypes.NpgsqlDbType.Integer); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = trackOrder; int rowsAffected = NpgsqlHelper.ExecuteNonQuery( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return rowsAffected; }
public static int Add( Guid guid, Guid siteGuid, string folderName) { NpgsqlParameter[] arParams = new NpgsqlParameter[3]; arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Varchar, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new NpgsqlParameter("foldername", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = folderName; int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.StoredProcedure, "mp_sitefolders_insert(:guid,:siteguid,:foldername)", arParams); return rowsAffected; }
public void AmbiguousFunctionParameterType() { ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ambiguousParameterType(int2, int4, int8, text, varchar(10), char(5)) returns int4 as ' select 4 as result; ' language 'sql'"); //NpgsqlConnection conn = new NpgsqlConnection(ConnectionString); NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", Conn); command.CommandType = CommandType.StoredProcedure; NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16); p.Value = 2; command.Parameters.Add(p); p = new NpgsqlParameter("b", DbType.Int32); p.Value = 2; command.Parameters.Add(p); p = new NpgsqlParameter("c", DbType.Int64); p.Value = 2; command.Parameters.Add(p); p = new NpgsqlParameter("d", DbType.String); p.Value = "a"; command.Parameters.Add(p); p = new NpgsqlParameter("e", NpgsqlDbType.Char); p.Value = "a"; command.Parameters.Add(p); p = new NpgsqlParameter("f", NpgsqlDbType.Varchar); p.Value = "a"; command.Parameters.Add(p); command.ExecuteScalar(); }
public void ImplicitSettingOfDbTypes() { var p = new NpgsqlParameter("p", DbType.Int32); Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); // As long as NpgsqlDbType/DbType aren't set explicitly, infer them from Value p = new NpgsqlParameter("p", 8); Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); Assert.That(p.DbType, Is.EqualTo(DbType.Int32)); p.Value = 3.0; Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double)); Assert.That(p.DbType, Is.EqualTo(DbType.Double)); p.NpgsqlDbType = NpgsqlDbType.Bytea; Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bytea)); Assert.That(p.DbType, Is.EqualTo(DbType.Binary)); p.Value = "dont_change"; Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bytea)); Assert.That(p.DbType, Is.EqualTo(DbType.Binary)); p = new NpgsqlParameter("p", new int[0]); Assert.That(p.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Integer)); Assert.That(p.DbType, Is.EqualTo(DbType.Object)); }
public void Roundtrip() { const string expected = "Something"; var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Text); var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Varchar); var p3 = new NpgsqlParameter("p3", DbType.String); var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected }; Assert.That(p2.DbType, Is.EqualTo(DbType.String)); Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text)); Assert.That(p3.DbType, Is.EqualTo(DbType.String)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); p1.Value = p2.Value = p3.Value = expected; var reader = cmd.ExecuteReader(); reader.Read(); for (var i = 0; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(string))); Assert.That(reader.GetString(i), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<string>(i), Is.EqualTo(expected)); Assert.That(reader.GetValue(i), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<char[]>(i), Is.EqualTo(expected.ToCharArray())); } reader.Close(); cmd.Dispose(); }
public void Constructor2_Value_DateTime() { var value = new DateTime(2004, 8, 24); var p = new NpgsqlParameter("address", value); Assert.AreEqual(DbType.DateTime, p.DbType, "B:DbType"); Assert.AreEqual(ParameterDirection.Input, p.Direction, "B:Direction"); Assert.IsFalse(p.IsNullable, "B:IsNullable"); #if NET_2_0 //Assert.AreEqual (0, p.LocaleId, "B:LocaleId"); #endif Assert.AreEqual("address", p.ParameterName, "B:ParameterName"); Assert.AreEqual(0, p.Precision, "B:Precision"); Assert.AreEqual(0, p.Scale, "B:Scale"); //Assert.AreEqual (0, p.Size, "B:Size"); Assert.AreEqual(string.Empty, p.SourceColumn, "B:SourceColumn"); #if NET_2_0 Assert.IsFalse(p.SourceColumnNullMapping, "B:SourceColumnNullMapping"); #endif Assert.AreEqual(DataRowVersion.Current, p.SourceVersion, "B:SourceVersion"); Assert.AreEqual(NpgsqlDbType.Timestamp, p.NpgsqlDbType, "B:NpgsqlDbType"); #if NET_2_0 // FIXME //Assert.AreEqual (new SqlDateTime (value), p.NpgsqlValue, "B:NpgsqlValue"); #endif Assert.AreEqual(value, p.Value, "B:Value"); #if NET_2_0 //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionDatabase, "B:XmlSchemaCollectionDatabase"); //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionName, "B:XmlSchemaCollectionName"); //Assert.AreEqual (string.Empty, p.XmlSchemaCollectionOwningSchema, "B:XmlSchemaCollectionOwningSchema"); #endif }
public void Bool() { var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Boolean); var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Boolean); var p3 = new NpgsqlParameter("p3", DbType.Boolean); var p4 = new NpgsqlParameter { ParameterName = "p4", Value = true }; Assert.That(p4.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Boolean)); Assert.That(p4.DbType, Is.EqualTo(DbType.Boolean)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); p1.Value = false; p2.Value = p3.Value = true; var reader = cmd.ExecuteReader(); reader.Read(); Assert.That(reader.GetBoolean(0), Is.False); for (var i = 1; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetBoolean(i), Is.True); Assert.That(reader.GetValue(i), Is.True); Assert.That(reader.GetProviderSpecificValue(i), Is.True); Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (bool))); Assert.That(reader.GetDataTypeName(i), Is.EqualTo("bool")); } reader.Close(); cmd.Dispose(); }
public void Roundtrip() { byte[] expected = { 1, 2, 3, 4, 5 }; var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Bytea); var p2 = new NpgsqlParameter("p2", DbType.Binary); var p3 = new NpgsqlParameter { ParameterName = "p3", Value = expected }; Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bytea)); Assert.That(p3.DbType, Is.EqualTo(DbType.Binary)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); p1.Value = p2.Value = expected; var reader = cmd.ExecuteReader(); reader.Read(); for (var i = 0; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof (byte[]))); Assert.That(reader.GetFieldValue<byte[]>(i), Is.EqualTo(expected)); Assert.That(reader.GetValue(i), Is.EqualTo(expected)); } reader.Close(); cmd.Dispose(); }
internal override IDataParameter CreateIDataParameter(string name, object value) { NpgsqlParameter p = new NpgsqlParameter(); p.ParameterName = name; p.Value = value; return p; }
public void Int32() { var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer); var p2 = new NpgsqlParameter("p2", DbType.Int32); var p3 = new NpgsqlParameter { ParameterName = "p3", Value = 8 }; Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer)); Assert.That(p3.DbType, Is.EqualTo(DbType.Int32)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); p1.Value = p2.Value = (long)8; var reader = cmd.ExecuteReader(); reader.Read(); for (var i = 0; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetInt32(i), Is.EqualTo(8)); Assert.That(reader.GetInt64(i), Is.EqualTo(8)); Assert.That(reader.GetInt16(i), Is.EqualTo(8)); Assert.That(reader.GetByte(i), Is.EqualTo(8)); Assert.That(reader.GetFloat(i), Is.EqualTo(8.0f)); Assert.That(reader.GetDouble(i), Is.EqualTo(8.0d)); Assert.That(reader.GetDecimal(i), Is.EqualTo(8.0m)); Assert.That(reader.GetValue(i), Is.EqualTo(8)); Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(8)); Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(int))); Assert.That(reader.GetDataTypeName(i), Is.EqualTo("int4")); } reader.Dispose(); cmd.Dispose(); }
/// <summary> /// Inserts a row in the mp_TaxClass table. Returns rows affected count. /// </summary> /// <param name="guid"> guid </param> /// <param name="siteGuid"> siteGuid </param> /// <param name="title"> title </param> /// <param name="description"> description </param> /// <param name="lastModified"> lastModified </param> /// <param name="created"> created </param> /// <returns>int</returns> public static int Create( Guid guid, Guid siteGuid, string title, string description, DateTime lastModified, DateTime created) { NpgsqlParameter[] arParams = new NpgsqlParameter[6]; arParams[0] = new NpgsqlParameter("guid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new NpgsqlParameter("title", NpgsqlTypes.NpgsqlDbType.Varchar, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = title; arParams[3] = new NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = description; arParams[4] = new NpgsqlParameter("lastmodified", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = lastModified; arParams[5] = new NpgsqlParameter("created", NpgsqlTypes.NpgsqlDbType.Timestamp); arParams[5].Direction = ParameterDirection.Input; arParams[5].Value = created; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_taxclass ("); sqlCommand.Append("guid, "); sqlCommand.Append("siteguid, "); sqlCommand.Append("title, "); sqlCommand.Append("description, "); sqlCommand.Append("lastmodified, "); sqlCommand.Append("created )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":guid, "); sqlCommand.Append(":siteguid, "); sqlCommand.Append(":title, "); sqlCommand.Append(":description, "); sqlCommand.Append(":lastmodified, "); sqlCommand.Append(":created "); sqlCommand.Append(")"); sqlCommand.Append(";"); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return rowsAffected; }
public static void AddFeature(Guid siteGuid, Guid featureGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_sitemoduledefinitions ("); sqlCommand.Append("siteid, "); sqlCommand.Append("moduledefid, "); sqlCommand.Append("siteguid, "); sqlCommand.Append("featureguid, "); sqlCommand.Append("authorizedroles "); sqlCommand.Append(")"); sqlCommand.Append(" VALUES ("); sqlCommand.Append("(SELECT siteid FROM mp_sites WHERE siteguid = :siteguid LIMIT 1), "); sqlCommand.Append("(SELECT moduledefid FROM mp_moduledefinitions WHERE guid = :featureguid LIMIT 1), "); sqlCommand.Append(":siteguid, "); sqlCommand.Append(":featureguid, "); sqlCommand.Append("'All Users' "); sqlCommand.Append(")"); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new NpgsqlParameter("featureguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = featureGuid.ToString(); NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); }
/// <summary> /// Sobrescreve o metodo de GetParametroRetorno, adiciona o parametro de "@RETURN_VALUE". /// </summary> /// <returns>Retorna uma colecao de parametros com o parametro "@RETURN_VALUE" </returns> internal override IDataParameter GetParametroRetorno() { NpgsqlParameter p = new NpgsqlParameter("@RETURN_VALUE",DbType.Int32); p.Direction = ParameterDirection.ReturnValue; return p; }
public void PrepareParametersForSchemaFetching(IDbCommand cmd) { // no notion of declaring parameters in Postgres foreach (var queryParam in FindUndeclaredParameters(cmd.CommandText)) { var myParam = new Npgsql.NpgsqlParameter(); myParam.ParameterName = queryParam.DbName; if (!string.IsNullOrEmpty(queryParam.DbType)) { myParam.DbType = (DbType)System.Enum.Parse(typeof(DbType), queryParam.DbType); } myParam.Value = DBNull.Value; cmd.Parameters.Add(myParam); } }
public virtual IQueryable <getCallsResult> GetCalls(Nullable <System.DateTime> beginDate, Nullable <System.DateTime> endDate) { /***************************************************postgreSql вариант*****************************************/ #if PostgreSQL var _beginDate = new Npgsql.NpgsqlParameter("@BeginDate", beginDate); var _endDate = new Npgsql.NpgsqlParameter("@EndDate", endDate); var res = getCallsResult.FromSqlRaw("SELECT * FROM getcalls (@BeginDate, @EndDate)", _beginDate, _endDate); #else /***************************************************MSSql вариант*****************************************/ var beginDateParameter = new SqlParameter("@BeginDate", beginDate); var endDateParameter = new SqlParameter("@EndDate", endDate); var res = getCallsResult.FromSqlRaw("dbo.getCalls @BeginDate, @EndDate", beginDateParameter, endDateParameter); //осталось разобраться с преобразованием данных #endif return(res); // }
//[System.Obsolete] public int ChangeObjectForUser(int _objectID, int _userID) { /***************************************************postgreSql вариант*****************************************/ #if PostgreSQL var userID = new Npgsql.NpgsqlParameter("@UserID", _userID); var objectID = new Npgsql.NpgsqlParameter("@ObjectID", _objectID); return(Database.ExecuteSqlCommand("SELECT * FROM ChangeObjectForUser (@UserID, @ObjectID)", userID, objectID)); #else /***************************************************MSSql вариант*****************************************/ var userID = new SqlParameter("@UserID", _userID); var objectID = new SqlParameter("@ObjectID", _objectID); return(Database.ExecuteSqlCommand("dbo.ChangeObjectForUser @UserID, @ObjectID", userID, objectID)); #endif //var res = Users.FromSqlRaw("EXEC dbo.ChangeObjectForUserTrue @UserID, @ObjectID", _param).ToList().FirstOrDefault(); //SqlParameter[] _param = { new SqlParameter("@UserID", _userID), new SqlParameter("@ObjectID", _objectID) }; //var res = Users.FromSqlRaw("EXEC dbo.ChangeObjectForUser @UserID, @ObjectID", _param).ToList().FirstOrDefault(); //Users. //return 1;// res.ObjectName; }
//never used //private string QualifiedTableName(string schema, string tableName) //{ // if (schema == null || schema.Length == 0) // { // return tableName; // } // else // { // return schema + "." + tableName; // } //} /* * private static void SetParameterValuesFromRow(NpgsqlCommand command, DataRow row) * { * foreach (NpgsqlParameter parameter in command.Parameters) * { * parameter.Value = row[parameter.SourceColumn, parameter.SourceVersion]; * } * } */ /// <summary> /// Applies the parameter information. /// </summary> /// <param name="p">The parameter.</param> /// <param name="row">The row.</param> /// <param name="statementType">Type of the statement.</param> /// <param name="whereClause">if set to <c>true</c> [where clause].</param> protected override void ApplyParameterInfo(DbParameter p, DataRow row, StatementType statementType, bool whereClause) { NpgsqlParameter parameter = (NpgsqlParameter)p; /* TODO: Check if this is the right thing to do. * ADO.Net seems to set this property to true when creating the parameter for the following query: * ((@IsNull_FieldName = 1 AND FieldName IS NULL) OR * (FieldName = @Original_FieldName)) * This parameter: @IsNull_FieldName was having its sourcecolumn set to the same name of FieldName. * This was causing ADO.Net to try to set a value of different type of Int32. * See bug 1010973 for more info. */ #if INVESTIGATE if (parameter.SourceColumnNullMapping) { parameter.SourceColumn = ""; } else { parameter.NpgsqlDbType = NpgsqlTypesHelper.GetNativeTypeInfo((Type)row[SchemaTableColumn.DataType]).NpgsqlDbType; } #endif }
internal DbCommand CreateDbCommand(Version serverVersion, DbCommandTree commandTree) { if (commandTree == null) { throw new ArgumentNullException(nameof(commandTree)); } var command = new NpgsqlCommand(); foreach (var parameter in commandTree.Parameters) { var dbParameter = new NpgsqlParameter { ParameterName = parameter.Key, NpgsqlDbType = NpgsqlProviderManifest.GetNpgsqlDbType(((PrimitiveType)parameter.Value.EdmType).PrimitiveTypeKind) }; command.Parameters.Add(dbParameter); } TranslateCommandTree(serverVersion, commandTree, command); return(command); }
async Task Write <T>([AllowNull] T value, NpgsqlParameter param, bool async, CancellationToken cancellationToken = default) { CheckReady(); if (_column == -1) { throw new InvalidOperationException("A row hasn't been started"); } if (value == null || value is DBNull) { await WriteNull(async, cancellationToken); return; } if (typeof(T) == typeof(object)) { param.Value = value; } else { if (param is not NpgsqlParameter <T> typedParam) { _params[_column] = typedParam = new NpgsqlParameter <T>(); typedParam.NpgsqlDbType = param.NpgsqlDbType; param = typedParam; } typedParam.TypedValue = value; } param.ResolveHandler(_connector.TypeMapper); param.ValidateAndGetLength(); param.LengthCache?.Rewind(); await param.WriteWithLength(_buf, async, cancellationToken); param.LengthCache?.Clear(); _column++; }
/* * /// <summary> * /// In methods taking an array as argument this method is used to verify * /// that the argument has the type <see cref="NpgsqlParameter">NpgsqlParameter</see>[] * /// </summary> * /// <param name="array">The array to verify</param> * private void CheckType(Array array) * { * NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CheckType", array); * if (array.GetType() != typeof (NpgsqlParameter[])) * { * throw new InvalidCastException( * String.Format(this.resman.GetString("Exception_WrongType"), array.GetType().ToString())); * } * } */ /// <summary> /// Report the offset within the collection of the given parameter. /// </summary> /// <param name="item">Parameter to find.</param> /// <returns>Index of the parameter, or -1 if the parameter is not present.</returns> public int IndexOf(NpgsqlParameter item) { return(InternalList.IndexOf(item)); }
/// <summary> /// Report whether the specified parameter is present in the collection. /// </summary> /// <param name="item">Parameter to find.</param> /// <returns>True if the parameter was found, otherwise false.</returns> public bool Contains(NpgsqlParameter item) { return(InternalList.Contains(item)); }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connection.Connector.ServerVersion; string query; string procedureName; string schemaName = null; var fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above query = serverVersion >= new Version(8, 1, 0) ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname" : "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); // The pg_temp pseudo-schema is special - it's an alias to a real schema name (e.g. pg_temp_2). // We get the real name with pg_my_temp_schema(). if (schemaName == "pg_temp") { using (var c = new NpgsqlCommand("SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()", command.Connection)) { schemaName = (string)c.ExecuteScalar(); } } } else { // proargsmodes is supported for Postgresql 8.1 and above query = serverVersion >= new Version(8, 1, 0) ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname" : "select proargnames, proargtypes from pg_proc where proname = :proname"; procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (var c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !string.IsNullOrEmpty(schemaName)) { var prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; uint[] types = null; char[] modes = null; using (var rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as string[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as uint[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as char[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0) { return; // Parameterless function } types = rdr.GetFieldValue <uint[]>(1); } } else { throw new InvalidOperationException(string.Format("{0} does not exist in pg_proc", command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); // TODO: Fix enums, composite types var npgsqlDbType = c.Connection.Connector.TypeHandlerRegistry[types[i]].NpgsqlDbType; if (npgsqlDbType == NpgsqlDbType.Unknown) { throw new InvalidOperationException(string.Format("Invalid parameter type: {0}", types[i])); } param.NpgsqlDbType = npgsqlDbType; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case 'i': param.Direction = ParameterDirection.Input; break; case 'o': param.Direction = ParameterDirection.Output; break; case 'b': param.Direction = ParameterDirection.InputOutput; break; case 'v': throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); case 't': throw new NotImplementedException("Cannot derive function parameter of type TABLE"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html command.Parameters.Clear(); using (var c = new NpgsqlCommand(DeriveParametersQuery, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = command.CommandText; string[] names = null; uint[] types = null; char[] modes = null; using (var rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as string[]; } if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as uint[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as char[]; } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0) { return; // Parameterless function } types = rdr.GetFieldValue <uint[]>(1); } } else { throw new InvalidOperationException($"{command.CommandText} does not exist in pg_proc"); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); // TODO: Fix enums, composite types var npgsqlDbType = c.Connection.Connector.TypeHandlerRegistry[types[i]].PostgresType.NpgsqlDbType; if (!npgsqlDbType.HasValue) { throw new InvalidOperationException($"Invalid parameter type: {types[i]}"); } param.NpgsqlDbType = npgsqlDbType.Value; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case 'i': param.Direction = ParameterDirection.Input; break; case 'o': case 't': param.Direction = ParameterDirection.Output; break; case 'b': param.Direction = ParameterDirection.InputOutput; break; case 'v': throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }
public void Insert_With_Function_Overloaded() { using (var connection = new NpgsqlConnection(connectionStr)) { var userId = Guid.NewGuid(); var user = new { UserId = userId, FirstName = "Bruce", LastName = "Lee", CreatedBy = "Locke", CreatedOn = DateTime.Now }; var result = connection.Sql(userInsSql) .Execute(user); Assert.Greater(result, 0); var parameters = new Npgsql.NpgsqlParameter[] { new NpgsqlParameter("p_content", "随便发点什么"), new NpgsqlParameter("p_title", "this is a pgslq blog"), new NpgsqlParameter("p_created_on", DateTime.Now), new NpgsqlParameter("p_created_by", userId), //new NpgsqlParameter("p_noexists", 10), //new NpgsqlParameter("p_blog_id", System.Data.DbType.Int32){ Direction = System.Data.ParameterDirection.Output } }; var blogId1 = connection.Procedure("blog_ins") .ExecuteScalar(parameters); //var blogId1 = int.Parse(parameters[4].Value.ToString()); Assert.Greater(int.Parse(blogId1.ToString()), 0); //#get blog just inserted var blog1 = connection.Sql("select * from blog where id = @id") .QueryOne <Blog>(new { id = blogId1 }); Assert.AreEqual(blog1.Content, "随便发点什么"); // use overloaded stored function that not content p_content parameter parameters = new Npgsql.NpgsqlParameter[] { new NpgsqlParameter("p_title", "this is a pgslq blog"), new NpgsqlParameter("p_content", "随便发点什么"), new NpgsqlParameter("p_created_on", DateTime.Now), new NpgsqlParameter("p_created_by", userId), new NpgsqlParameter("p_updated_by", userId), new NpgsqlParameter("p_blog_id", System.Data.DbType.Int32) { Direction = System.Data.ParameterDirection.Output } }; connection.Procedure("blog_ins") //.WithParameters(parameters) .Execute(parameters); var blogId2 = int.Parse(parameters[5].Value.ToString()); Assert.Greater(blogId2, 0); //#get blog just inserted var blog2 = connection.Sql("select * from blog where id = @id") .QueryOne <Blog>(new { id = blogId2 }); Assert.AreEqual(blog2.UpdatedBy, userId); } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connector.ServerVersion; String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } else { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"; } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; } procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; int[] types = null; string[] modes = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as int[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as String[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetString(1) == "") { return; // Parameterless function } types = rdr.GetString(1).Split().Select(int.Parse).ToArray(); } } else { throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo)) { throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } param.NpgsqlDbType = typeInfo.NpgsqlDbType; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case "i": param.Direction = ParameterDirection.Input; break; case "o": param.Direction = ParameterDirection.Output; break; case "b": param.Direction = ParameterDirection.InputOutput; break; case "v": throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); case "t": throw new NotImplementedException("Cannot derive function parameter of type TABLE"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }
public abstract void Write(object value, WriteBuffer buf, NpgsqlParameter parameter);
public abstract int ValidateAndGetLength(object value, NpgsqlParameter parameter);
/// <param name="value">the value to be examined</param> /// <param name="lengthCache">a cache in which to store length(s) of values to be written</param> /// <param name="parameter"> /// the <see cref="NpgsqlParameter"/> containing <paramref name="value"/>. Consulted for settings /// which impact how to send the parameter, e.g. <see cref="NpgsqlParameter.Size"/>. Can be null. /// </param> public abstract int ValidateAndGetLength(object value, ref LengthCache lengthCache, [CanBeNull] NpgsqlParameter parameter);
/// <param name="value">the value to be written</param> /// <param name="buf"></param> /// <param name="lengthCache">a cache in which to store length(s) of values to be written</param> /// <param name="parameter"> /// the <see cref="NpgsqlParameter"/> containing <paramref name="value"/>. Consulted for settings /// which impact how to send the parameter, e.g. <see cref="NpgsqlParameter.Size"/>. Can be null. /// <see cref="NpgsqlParameter.Size"/>. /// </param> public abstract void PrepareWrite(object value, WriteBuffer buf, LengthCache lengthCache, [CanBeNull] NpgsqlParameter parameter);
public override int ValidateAndGetLength(object value, ref LengthCache lengthCache, NpgsqlParameter parameter = null) { Contract.Requires(value != null); return(default(int)); }
public override void PrepareWrite(object value, WriteBuffer buf, LengthCache lengthCache, NpgsqlParameter parameter = null) { Contract.Requires(buf != null); Contract.Requires(value != null); }
///<summary> /// /// This method is reponsible to derive the command parameter list with values obtained from function definition. /// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown. /// Parameters name will be parameter1, parameter2, ... /// For while, only parameter name and NpgsqlDbType are obtained. ///</summary> /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param> public static void DeriveParameters(NpgsqlCommand command) { // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } String[] names = null; String[] types = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (!rdr.IsDBNull(1)) { types = rdr.GetString(1).Split(); } } } if (types == null) { throw new InvalidOperationException( String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } command.Parameters.Clear(); for (Int32 i = 0; i < types.Length; i++) { // skip parameter if type string is empty // empty parameter lists can cause this if (!string.IsNullOrEmpty(types[i])) { NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo)) { command.Parameters.Clear(); throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } if (names != null && i < names.Length) { command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType)); } else { command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType)); } } } } }