Inserting specific parameters through Npgsql rather than inserting it in single line query with all the parameters which are declared inside a PostgreSQL.



 DataSet dsdata = new DataSet();
            string sqlQuery = "select * from systemtracker_dbo.sp_grp_comp_unit_data(par_p_oprtype:=" + opr_type;
            sqlQuery = sqlQuery + " ,par_p_user_id:='" + user_id + "'";
            sqlQuery = sqlQuery + " ,par_p_group_id:='" + group_id + "'";
            sqlQuery = sqlQuery + " ,par_p_comp_id:='" + comp_id + "'";
            sqlQuery = sqlQuery + " ,par_p_unit_id:='" + unit_id + "'";
            sqlQuery = sqlQuery + " ,p_refcur:='t1'";
            sqlQuery = sqlQuery + " );FETCH ALL IN t1;";
            dbHelper = new AI_Portal_Cloud.DAL.clsPSQLHelper();
            NpgsqlCommand sc = dbHelper.GetSqlQueryCommand(sqlQuery);
            dsdata = dbHelper.RemovePostGresRefCursor(dbHelper.LoadDataSet(sc));


Here the Parameters are only inserted which have their values from front end otherwise the extra parameter which are not assigned any values which are declared in PostgreSQL  function are set with some default value within the PostgreSQL  function in back end which are set to their default values when the function do not get from front end.

Also remember the format of writing the parameter value
as opr_type  is numeric type so no quotes are there
but user_id ,group_id, comp_id,unit_id   are in character varying format

Comments

Post a Comment

Popular posts from this blog

Npgsql query and format with output parameters

  public string UploadExceldata(string json)         {             string connectionString = String.Format(new Connection().ConnectionString);             DataSet ds = new DataSet();             BaseResultData oBaseResult = new BaseResultData();             var connection = new Npgsql.NpgsqlConnection(connectionString);             try             {                                if (!(connection.State == ConnectionState.Open))                 {                     connection.Open();                 }                 //NpgsqlTransaction trans = connection.BeginTransaction();                 var command = new Npgsql.NpgsqlCommand("systemtracker_dbo.pr_create_user_from_excel_json", connection);                 command.CommandType = System.Data.CommandType.StoredProcedure;                 IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();                 //Parameter Settings                 var parameter = command.CreateParameter();                 parameter.ParameterName =
Read more

Npgsql Helper class

 using System; using System.Collections.Generic; using System.Text; using System.Data; using Npgsql; using NpgsqlTypes; namespace AI_Portal_Cloud.DAL {     public class clsPSQLHelper : IDisposable     {         private NpgsqlConnection connection = null;         private String connectionString = "dbConnection";         private NpgsqlDataAdapter sqlData = new NpgsqlDataAdapter();         public NpgsqlCommand sqlCmd = new NpgsqlCommand();                  // {0} : IP, {1} : DB Name, {2} : userID, {3} : userPW         private String connectionFormat = "Server={0};Database={1};User Id={2};Password={3};Port={4}";         public String ConnectionString         {             set             {                 connectionString = value;             }         }         #region Constructors         public clsPSQLHelper()         {             //             // TODO: Add constructor logic here             //             string strConn = String.Format(new Connection().ConnectionS
Read more