Exemple #1
2
        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);
        }
Exemple #2
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);



        }
Exemple #9
0
        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);
        }
Exemple #10
0
        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);
        }
Exemple #11
0
 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;
 }
Exemple #12
0
        /// <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;
        }
Exemple #13
0
        /// <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;
        }
Exemple #14
0
 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
        }
Exemple #16
0
 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);
 }
Exemple #17
0
        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();
        }
Exemple #18
0
 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);
 }
Exemple #19
0
        /// <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;
        }
Exemple #20
0
        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;
        }
Exemple #21
0
        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();
        }
Exemple #22
0
        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));
        }
Exemple #23
0
        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
        }
Exemple #25
0
        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();
        }
Exemple #26
0
        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();
        }
Exemple #27
0
 internal override IDataParameter CreateIDataParameter(string name, object value)
 {
     NpgsqlParameter p = new NpgsqlParameter();
     p.ParameterName = name;
     p.Value = value;
     return p;
 }
Exemple #28
0
        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();
        }
Exemple #29
0
        /// <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;
        }
Exemple #32
0
 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);                                                                                                     //
        }
Exemple #34
0
        //[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);
        }
Exemple #37
0
        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++;
        }
Exemple #38
0
/*
 *      /// <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));
        }
Exemple #39
0
 /// <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));
 }
Exemple #40
0
        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);
                }
            }
        }
Exemple #41
0
        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);
                }
            }
        }
Exemple #42
0
        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);
            }
        }
Exemple #43
0
        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);
                }
            }
        }
Exemple #44
0
 public abstract void Write(object value, WriteBuffer buf, NpgsqlParameter parameter);
Exemple #45
0
 public abstract int ValidateAndGetLength(object value, NpgsqlParameter parameter);
Exemple #46
0
 /// <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);
Exemple #47
0
 /// <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);
Exemple #48
0
 public override int ValidateAndGetLength(object value, ref LengthCache lengthCache, NpgsqlParameter parameter = null)
 {
     Contract.Requires(value != null);
     return(default(int));
 }
Exemple #49
0
 public override void PrepareWrite(object value, WriteBuffer buf, LengthCache lengthCache, NpgsqlParameter parameter = null)
 {
     Contract.Requires(buf != null);
     Contract.Requires(value != null);
 }
Exemple #50
0
        ///<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));
                        }
                    }
                }
            }
        }