Hallo,
ich hab mal eine Frage zu C# und Postgresql.
Wie kann ich eine Funktion/StoredProcedure ausführen?
Mein C# Code:
Meine Funktion in Postgresql
Ich hoffe ihr könnt mir weiterhelfen...
Danke schonmal
Gruß
Timur
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