C# Postgresql - Stored Procedure ausführen

emir

Grünschnabel
Hallo,

ich hab mal eine Frage zu C# und Postgresql.
Wie kann ich eine Funktion/StoredProcedure ausführen?

Mein C# Code:

Code:
    internal class Account
    {
        private string _Email = "test@sec-mail.net";
        private string _Firstname = "Max";
        private string _Lastname = "Mustermann";
        private char _Gender = 'M';
        private DateTime _Birthdate = DateTime.Today.Date;
        private string _Street = "Test";
        private string _Houseno = "1";
        private int _PLZ = 2;
        private string _Language = "de";
        private string _Country = "DE";
        private string _Password = "Test1234";

        DatabaseConnector dbConnector = new DatabaseConnector();

        internal void CreateAccount()
        {
            try
            {
                string hash = Encryption.HashEncryptedString(_Password);
                string salt = Encryption.GetSalt();

                Object[][] data =
                {
                    new Object[2] {"Email", _Email},
                    new Object[2] {"Firstname", _Firstname},
                    new Object[2] {"Lastname", _Lastname},
                    new Object[2] {"Gender", _Gender},
                    new Object[2] {"Birthdate", _Birthdate},
                    new Object[2] {"Street", _Street},
                    new Object[2] {"Houseno", _Houseno},
                    new Object[2] {"ZIP", _PLZ},
                    new Object[2] {"Language", _Language},
                    new Object[2] {"Country", _Country},
                    new Object[2] {"Hash", hash},
                    new Object[2] {"Salt", salt}
                };

                dbConnector.ExecuteStoredProcedure("sp_create_user", data);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }


Code:
public class DatabaseConnector
    {
        public bool ExecuteStoredProcedure(string pStoredProcedureName, Object[][] pParamsArray)
        {
            try
            {
                string connstring = "Server=localhost;Port=5432;User Id=test;Password=test;Database=groupware_db;";

                NpgsqlConnection _Conn = new NpgsqlConnection(connstring);
                _Conn.Open();

                //NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, _Conn);

                IDbDataParameter[] iDbDataParams = GetParameters(Convert.ToInt32(pParamsArray.Length));
                NpgsqlCommand _Command = new NpgsqlCommand("", _Conn);

                if (pParamsArray != null)
                {
                    IDbCommand SelectCommand = null;

                    SelectCommand = _Command;
                    SelectCommand.CommandType = CommandType.StoredProcedure;
                    SelectCommand.CommandText = pStoredProcedureName;

                    for (int a = 0; a < iDbDataParams.Length; a++)
                    {
                        iDbDataParams[a].ParameterName = Convert.ToString(pParamsArray[a][0]);
                        iDbDataParams[a].Value = pParamsArray[a][1];
                        SelectCommand.Parameters.Add(iDbDataParams[a]);
                    }
                    SelectCommand.ExecuteNonQuery();
                }
                _Conn.Close();
                return true;

            }
            catch (NpgsqlException psqlex)
            {
                Debug.WriteLine(psqlex.ToString());
                throw psqlex;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
                throw ex;
            }
            finally
            {
                //_Conn.Close();
            }
        }

        protected static IDbDataParameter[] GetParameters(int pParamsCount)
        {
            IDbDataParameter[] idbParams = new IDbDataParameter[pParamsCount];
            for (int i = 0; i < pParamsCount; ++i)
            {
                idbParams[i] = new NpgsqlParameter();
            }
            return idbParams;
        }
    }

Meine Funktion in Postgresql

Code:
-- Function: sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character)

-- DROP FUNCTION sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character);

CREATE OR REPLACE FUNCTION sp_create_user("Email" character varying, "Firstname" character varying, "Lastname" character varying, "Gender" character, "Birthdate" date, "Street" character varying, "Houseno" character varying, "ZIP" character, "Language" character, "Country" character, "Hash" character varying, "Salt" character)
  RETURNS bigint AS
$BODY$BEGIN

  -- tbl_user
  INSERT INTO tbl_user (loginid, emailaddress, gender)
  VALUES (Email, Email, Gender);

  -- tbl_userdetails
  INSERT INTO tbl_userdetails (userid,
			       firstname,
			       lastname,
			       birthdate,
			       street,
			       houseno,
			       zip_city,
			       country,
			       language
			       )
  VALUES (currval(tbl_user_userid_seq),
          Firstname,
          Lastname,
          Birthdate,
          Street,
          Houseno,
          ZIP,
          Country,
          Language
          );

  -- tbl_password
  INSERT INTO tbl_password (userid, hash, salt)
  VALUES ((SELECT userid
	   FROM tbl_user
	   WHERE emailaddress = Emailaddress),
	  Hash,
	  Salt);
  
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character) TO public;
GRANT EXECUTE ON FUNCTION sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character) TO postgres;
GRANT EXECUTE ON FUNCTION sp_create_user(character varying, character varying, character varying, character, date, character varying, character varying, character, character, character, character varying, character) TO test;

Ich hoffe ihr könnt mir weiterhelfen...
Danke schonmal

Gruß
Timur
 
Zurück