How to use PostgreSql With .Net
Install Nuget Package
Install-Package Npgsql
In appsettings.json
"DBInfo": {
"Name": "coresample",
"ConnectionString": "User ID=postgres;Password=xxxxxx;Host=localhost;Port=5432;Database=coresample;Pooling=true;"
}
Register Connection in ConfigureServices which is in startup.cs
services.AddDbContext<AuthenticationContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("IdentityConnection")));
Get it in Dbconnection class
public static string connstr = Startup.StaticConfig.GetConnectionString("IdentityConnection");
Sample Code
public class DBConnection
{
public static string connstr = Startup.StaticConfig.GetConnectionString("IdentityConnection");
//string connstr = "User ID=lama;Password=Lama#2019;Host=10.87.3.49;Port=5432;Database=postgres;Pooling=true;";
User_Entity _userentity = new User_Entity();
private NpgsqlDataAdapter npg_adapter;
private NpgsqlConnection npg_connection;
private NpgsqlCommand npg_command;
DataTable dt_ERROR = new DataTable();
DataSet ds_ERROR = new DataSet();
DataTable dt;
DataSet ds;
int i;
/// </constructor>
/// <method>INSERT METHOD
public bool executeWithoutSelectQueryBoolpgsql(string _query, NpgsqlParameter[] sqlParameter)
{
// NgpSql Init //
npg_connection = new NpgsqlConnection(connstr);
npg_command = new NpgsqlCommand(_query, npg_connection);
// NgpSql Init //
try
{
npg_connection.Open();
NpgsqlTransaction tran = npg_connection.BeginTransaction();
npg_command.CommandType = CommandType.Text;
npg_command.Parameters.AddRange(sqlParameter);
int result = npg_command.ExecuteNonQuery();
tran.Commit();
if (result == 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
return false;
}
finally
{
npg_connection.Close();
}
}
public DataSet executeMultipleSelectQuery(string _query, NpgsqlParameter[] sqlParameter)
{
// NgpSql Init //
npg_connection = new NpgsqlConnection(connstr);
npg_command = new NpgsqlCommand(_query, npg_connection);
// NgpSql Init //
i = 0;
try
{
ds = new DataSet();
npg_connection.Open();
NpgsqlTransaction tran = npg_connection.BeginTransaction();
npg_command.CommandType = CommandType.Text;
npg_command.Parameters.AddRange(sqlParameter);
npg_command.ExecuteNonQuery();
foreach (NpgsqlParameter parm in sqlParameter)
{
if (parm.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Refcursor)
{
if (parm.Value.ToString() != "null" || parm.Value.ToString() != "NULL" || parm.Value.ToString() != "")
{
string parm_val = string.Format("FETCH ALL IN \"{0}\"", parm.Value.ToString());
npg_adapter = new NpgsqlDataAdapter(parm_val.Trim().ToString(), npg_connection);
ds.Tables.Add(parm.Value.ToString());
npg_adapter.Fill(ds.Tables[i]);
i++;
}
}
}
tran.Commit();
return ds;
}
catch (Exception ex)
{
ds_ERROR.Tables[0].Rows.Add(ex.ToString(), ex.Message.ToString());
return ds_ERROR;
}
finally
{
npg_connection.Close();
}
}
public DataTable executeSingleSelectQuery(string _query, NpgsqlParameter[] sqlParameter)
{
// NgpSql Init //
npg_connection = new NpgsqlConnection(connstr);
npg_command = new NpgsqlCommand(_query, npg_connection);
// NgpSql Init //
try
{
npg_connection.Open();
dt = new DataTable();
NpgsqlTransaction tran = npg_connection.BeginTransaction();
npg_command.CommandType = CommandType.Text;
npg_command.Parameters.AddRange(sqlParameter);
npg_command.ExecuteNonQuery();
foreach (NpgsqlParameter parm in sqlParameter)
{
if (parm.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Refcursor)
{
if (parm.Value.ToString() != "null" || parm.Value.ToString() != "NULL" || parm.Value.ToString() != "")
{
string parm_val = string.Format("FETCH ALL IN \"{0}\"", parm.Value.ToString());
npg_adapter = new NpgsqlDataAdapter(parm_val.Trim().ToString(), npg_connection);
npg_adapter.Fill(dt);
}
}
}
tran.Commit();
return dt;
}
catch (Exception ex)
{
dt_ERROR.Rows.Add(ex.ToString(), ex.Message.ToString());
return dt_ERROR;
}
finally
{
npg_connection.Close();
}
}
/// <method>
/// Insert Query
/// </method>
public bool executeInsertQuery(String _query, NpgsqlParameter[] NpgsqlParameter)
{
npg_connection = new NpgsqlConnection(connstr);
npg_command = new NpgsqlCommand(_query, npg_connection);
try
{
npg_connection.Open();
//myCommand.Connection = openConnection();
npg_command.CommandText = _query;
npg_command.Parameters.AddRange(NpgsqlParameter);
//npg_adapter.InsertCommand = myCommand;
//npg_command.ExecuteNonQuery();
_userentity.status = npg_command.ExecuteNonQuery();
if (_userentity.status == -1)
{
_userentity.flag = true;
}
else
{
_userentity.flag = false;
}
}
catch (NpgsqlException e)
{
// MessageBox.Show(e.Message, "Error Message - A179");
//Console.Write("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
return false;
}
finally
{
//myCommand.Connection.Close();
npg_connection.Close();
}
return _userentity.flag;
}
public bool update(String _query, NpgsqlParameter[] NpgsqlParameter)
{
try
{
npg_connection = new NpgsqlConnection(connstr);
npg_command = new NpgsqlCommand(_query, npg_connection);
npg_connection.Open();
npg_command.CommandType = CommandType.Text;
npg_command.Parameters.AddRange(NpgsqlParameter);
//int result = npg_command.ExecuteNonQuery();
//npg_command.ExecuteNonQuery();
_userentity.status = npg_command.ExecuteNonQuery();
if (_userentity.status == -1)
{
_userentity.flag = true;
}
else
{
_userentity.flag = false;
}
}
catch (Exception ex)
{
return false;
}
finally
{
npg_connection.Close();
}
return _userentity.flag;
}
}
Sample Control
public class WareHouse_Control
{
private DBConnection conn;
public WareHouse_Control()
{
conn = new DBConnection();
}
public DataTable GetPackInfo(string ref_code, string ref_number)
{
string query = string.Format(@"select * from get_pack_info(@pack_info,@ref_code,@ref_number)");
NpgsqlParameter[] NpgsqlParameters = new NpgsqlParameter[3];
NpgsqlParameters[0] = new NpgsqlParameter("@pack_info", NpgsqlDbType.Refcursor);
NpgsqlParameters[0].Value = Convert.ToString("pack_info");
NpgsqlParameters[1] = new NpgsqlParameter("@ref_code", NpgsqlDbType.Text);
NpgsqlParameters[1].Value = Convert.ToString(ref_code);
NpgsqlParameters[2] = new NpgsqlParameter("@ref_number", NpgsqlDbType.Text);
NpgsqlParameters[2].Value = Convert.ToString(ref_number);
return conn.executeSingleSelectQuery(query, NpgsqlParameters);
}
}
Sample Controller
[Route("api/[controller]")]
[ApiController]
public class WareHouseController : ControllerBase
{
private Dictionary<string, string> _jsonData;
WareHouse_Model _usermodel = new WareHouse_Model();
[HttpPost]
[Route("PackInformation")]//get table values
//[Produces("application/xml")]// xml formater
public IActionResult PackInformation([FromBody]JToken Message)
{
_jsonData = JsonConvert.DeserializeObject<Dictionary<string, string>>(Message.ToString());
//string RefCode = Convert.ToString(_jsonData["RefCode"].ToString());
//string RefNumber = Convert.ToString(_jsonData["RefNumber"].ToString());
string RefCode = _jsonData["RefCode"] == null ? "" : Convert.ToString(_jsonData["RefCode"].ToString());
string RefNumber = _jsonData["RefNumber"] == null ? "" : Convert.ToString(_jsonData["RefNumber"].ToString());
DataTable dt = new DataTable();
dt = _usermodel.GetPackInformation(RefCode, RefNumber);
if (dt.Rows.Count > 0)
{
var responseValue = new { status = "Success", response = dt, message = "Ok" };
return Ok(responseValue);
}
else
{
var responseValue = new { status = "Failed", response = dt, message = "Invalid" };
return Ok(responseValue);
}
}
Consume API
In Frontend Controller
[HttpGet]
public string GetPutWayDetails(string RefType, string RefNumber)
{
string Status = "";
List<GetPutWayDetail> liststr = new List<GetPutWayDetail>();
liststr.Add(new GetPutWayDetail { RefCode = RefType, RefNumber = RefNumber });
string strserialize = JsonConvert.SerializeObject(liststr);
string sstrserialize = strserialize.Replace("[", " ");
string ssstrserialize = sstrserialize.Replace("]", " ");
using (var httpClient = new HttpClient())
{
var response = httpClient.PostAsJsonAsync("http://10.87.3.49/LaMaWebAPI/api/WareHouse/PackInformation", ssstrserialize).Result;
using (HttpContent content = response.Content)
{
//string ApiToken = Convert.ToString(HttpContext.Current.Session["Access_Token"]);
//httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", ApiToken);
Task<string> result = content.ReadAsStringAsync();
string actualresponse = result.Result;
// dynamic d = JObject.Parse(actualresponse);
Status = actualresponse;
}
}
return Status;
}
Comments
Post a Comment