Dot Net: February 2018

Here is my saving class details

using System;
using System.Collections.Generic;
using System.Text;
using EasyAccounts.DTO;
using Npgsql;
using EasyAccounts.DAL;
using System.Data;
using InventoryDTO;
using System.IO;
using System.Windows.Forms;

namespace InventoryDAL
{
public class GodownStockDAL
{
#region UserVariables
NpgsqlConnection con = new NpgsqlConnection(SqlHelper.SQLConnString);
NpgsqlTransaction trans;
int Prdcount = 0;

//Naveen
string storePath, FileName;
string StoreName = string.Empty;
string TempQuery = string.Empty;
string Tempbatchno = string.Empty;
string TempPrdID = string.Empty;
string VendorId = string.Empty;
string vendorName = string.Empty;
string DatePath = string.Empty;
string OfficeID = string.Empty;
string CusID = string.Empty;
#endregion

private string ManageQuote(string strMessage)
{
try
{
if (strMessage != null && strMessage != "")
{
strMessage = strMessage.Replace("'", "''");
}
}
catch (Exception)
{
return strMessage;
}
return strMessage;
}

public static string FormatDate(string strDate)
{
string Date = null;
string[] dat = null;
if (strDate != null)
{
if (strDate.Contains("/"))
{
dat = strDate.Split('/');
}
else if (strDate.Contains("-"))
{
dat = strDate.Split('-');
}
Date = dat[2] + "-" + dat[1] + "-" + dat[0];
}
return Date;
}

public string GenerateGSTId(string strtablename, string strcolname, int prefix, string strdate)
{
try
{
DateTime dt = Convert.ToDateTime(strdate);
string stryear = dt.Year.ToString();
string strFromdate = "01-APR-" + stryear;
if (Convert.ToDateTime(strdate) < Convert.ToDateTime(strFromdate))
{
strFromdate = Convert.ToDateTime(strFromdate).AddMonths(-12).ToString("dd-MMM-yyyy");
}
stryear = Convert.ToDateTime(strFromdate).Year.ToString();
string strToDate = Convert.ToDateTime(strFromdate).AddMonths(12).AddDays(-1).ToString("dd-MMM-yyyy");
string strId = SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT COALESCE(MAX(TO_NUMBER(SUBSTR(" + ManageQuote(strcolname) + "," + (prefix + 1) + " , LENGTH( " + ManageQuote(strcolname) + ")-(3+" + prefix + ")),'9999999'))+1,1) ||'/" + stryear.Substring(2) + "' FROM " + ManageQuote(strtablename) + " WHERE DDC_ORDERDATE BETWEEN '" + ManageQuote(strFromdate) + "' AND '" + ManageQuote(strToDate) + "'").ToString();
return strId;
}
catch
{
return null;
}
}

public List<InventoryDTO.StockTransferIssueDTO> GetToBranchDetails()
{
List<InventoryDTO.StockTransferIssueDTO> lstBranch = new List<InventoryDTO.StockTransferIssueDTO>();
try
{
//NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT DISTINCT VCHGODOWNNAME FROM TABGODOWN ORDER BY VCHGODOWNNAME");
NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT OFFICEID,OFFICENAME,status FROM TABOFFICES WHERE OFFICENAME NOT IN (SELECT NAME FROM TABBRANCH) AND TERRITORY IN(SELECT TERRITORY FROM TABBRANCH) AND STATUS='Y' ORDER BY OFFICENAME");
while (rdr.Read())
{
InventoryDTO.StockTransferIssueDTO objGodownName = new InventoryDTO.StockTransferIssueDTO();
objGodownName.ToGodownId = rdr["OFFICEID"].ToString();
objGodownName.ToGodown = rdr["OFFICENAME"].ToString();

lstBranch.Add(objGodownName);
}

rdr.Close();
return lstBranch;
}
catch
{
return null;
}
}

public DataSet GetProdTaxDetails(string ProdName)
{
DataSet dsProdTax = new DataSet();
dsProdTax = SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT A.CATEGORYTYPEID,A.CATEGORYTYPE,B.TAXRATE,CAST(B.TAXRATE||'% on '||B.CATEGORYTYPE AS VARCHAR(50)) AS TAXDETAILS FROM TABPRODUCT A JOIN TABTAXDETAILS B ON A.CATEGORYTYPEID=B.CATEGORYTYPEID WHERE PRDNAME='" + ManageQuote(ProdName) + "'", null);
return dsProdTax;
}

public int DuplicateName(string agentname, string category)
{
int cnt = -1;
try
{
cnt = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT count(*) FROM TABAGENTSETUP WHERE VCHNAME='" + ManageQoute(agentname) + "' AND vchcategory='" + ManageQoute(category) + "'"));
}
catch
{
}
return cnt;
}

public bool saveStockTransferIssue(List<StockTransferIssueDTO> lstItems, out string strNextId, out string Path)
{
strNextId = string.Empty;
string strddcid = string.Empty;
string strinvid = string.Empty;
string strJVNo = string.Empty;
Path = string.Empty;
TempQuery = string.Empty;

try
{
if (lstItems.Count > 0)
{
strddcid = "DDC" + GenerateGSTId("TABDIRECTDELIVERYCHALLAN", "DDC_DDCNO", 2, lstItems[0].TranDate);
strinvid = "INV" + GenerateGSTId("TABDIRECTDELIVERYCHALLAN", "INVOICENO", 2, lstItems[0].TranDate);

if (!string.IsNullOrEmpty(strddcid) && strddcid != null && strddcid.Length > 3)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

string Loginid = ManageQuote(lstItems[0].LoginId);
CusID = lstItems[0].ToGodownId;
// tabdirectdeliverychallan insert.
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABDIRECTDELIVERYCHALLAN(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_CUSNAME,TOTALAMOUNT,INVOICENO,DUEAMOUNT,OTHERCHARGES,GRDOTHERDISCOUNT,NUMAGENTPER,NUMCOMMAMT,VCHGODOWN,SALES_TYPE,DDC_VAT) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[0].ToGodownId) + "','" + ManageQuote(lstItems[0].TranDate) + "','" + ManageQuote(lstItems[0].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED','" + ManageQuote(lstItems[0].ToGodown) + "',cast(" + lstItems[0].TotAmount + " as numeric),'" + ManageQuote(strinvid) + "',cast(" + lstItems[0].TotAmount + " as numeric),0,0,0,0,'" + ManageQuote(lstItems[0].FromGodown) + "','STOCK ISSUED',cast(" + lstItems[0].VatAmount + " as numeric))", null);
NpgsqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT OFFICEID,OFFICENAME FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH)");

while (dr.Read())
{
vendorName = dr["officename"].ToString();
VendorId = dr["officeid"].ToString();
}
OfficeID = VendorId;

//naveen
//if ( VendorId=="" || VendorId=="OFF7"||VendorId==string.Empty)
//{
// string Name= SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT NAME FROM TABBRANCH").ToString();
// vendorName = Name == "DIAMONDPOINT" ? "HYDERABAD" : vendorName;
// OfficeID = VendorId = "OFF7";

//}

for (int k = 0; k < lstItems.Count; k++)
{

if (lstItems[k].Ptype == "V")
{
string warranty = string.Empty;
string Mfgdate = string.Empty;
string ExpDate = string.Empty;
string Mrp = string.Empty;
string SalePrice = string.Empty;
string Query1 = "INSERT INTO TABDIRECTDELIVERYCHALLANDETAILS(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_DDCNODETAILID,PRDID,PRDNAME,DDC_COSTPERITEM,DDC_AMOUNT,DDC_VAT,DDC_OTHERTAX,DDC_OTHERCHARGES,DDC_OTHERDISCOUNTS,DDC_TOTALAMOUNT,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_QUANTITY,INVOICENO,VATTYPE,VATDETAILS,VCHGODOWN,VCHBATCHNUMBER,PRDDISCOUNT,OTHERDISCOUNTS,TYPE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',1,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED'," + lstItems[k].Quant + ",'" + ManageQuote(strinvid) + "','INCLUDE','" + ManageQuote(lstItems[k].TaxDetails) + "','DIRECT','" + ManageQuote(lstItems[k].BatchNum) + "',0,0,'" + ManageQuote(lstItems[k].Ptype) + "')";
string[] str = lstItems[k].BatchNum.Split(',');
string str1 = "SELECT DSR_WARRANTY FROM TABDIRECTSTOCKRECEIPTDETAILS WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND DSR_DSRNO IN(SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHBATCHNUMBER IN('" + str[0] + "'))";
NpgsqlDataReader dr1 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, str1);

while (dr1.Read())
{
warranty = dr1["dsr_warranty"].ToString();
}

string QRY = "SELECT MFGDATE,EXPDATE,NUMMRP,NUMSALEPRICE FROM TABSTOCKMASTER WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER='" + str[0] + "'";

NpgsqlDataReader DR2 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, QRY);
while (DR2.Read())
{
Mfgdate = Convert.ToDateTime(DR2["MFGDATE"]).ToString("dd-MMM-yyyy");
ExpDate = Convert.ToDateTime(DR2["EXPDATE"]).ToString("dd-MMM-yyyy");
Mrp = DR2["NUMMRP"].ToString();
SalePrice = DR2["NUMSALEPRICE"].ToString();
}

TempQuery = TempQuery + "INSERT INTO TEMPTABDIRECTSTOCKRECEIPTDETAILS(DSR_ORDERDATE,PRDID,PRDNAME,DSR_INVOICENO,VCHBATCHNUMBER,DSR_COSTPERITEM,DSR_QUANTITY,DSR_AMOUNT,DSR_VAT,DSR_OTHERTAX,DSR_OTHERCHARGES,DSR_OTHERDISCOUNTS,VATTYPE,DSR_WARRANTY,DSR_TOTALAMOUNT,TYPE,VENID,VENDORNAME,FROMBRANCH,MFGDATE,EXPDATE,NUMSALEPRICE,NUMMRP,STATUS,DSR_LOGINDATE)VALUES('" + ManageQuote(lstItems[k].TranDate) + "','" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].BatchNum) + "',cast(" + lstItems[k].CostPerUnit + " as numeric)," + lstItems[k].Quant + ",cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,'INCLUDE','0',cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "','" + VendorId + "','" + vendorName + "','" + vendorName + "','" + Mfgdate + "','" + ExpDate + "','" + SalePrice + "','" + Mrp + "','Y',CURRENT_TIMESTAMP);";
TempPrdID = TempPrdID + "'" + ManageQuote(lstItems[k].ProdId) + "'" + ",";
StoreName = ManageQuote(lstItems[k].ToGodown);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, Query1, null);
}
else if (lstItems[k].Ptype == "Q")
{
string warranty = string.Empty;
string Query1 = "INSERT INTO TABDIRECTDELIVERYCHALLANDETAILS(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_DDCNODETAILID,PRDID,PRDNAME,DDC_COSTPERITEM,DDC_AMOUNT,DDC_VAT,DDC_OTHERTAX,DDC_OTHERCHARGES,DDC_OTHERDISCOUNTS,DDC_TOTALAMOUNT,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_QUANTITY,INVOICENO,VATTYPE,VATDETAILS,VCHGODOWN,PRDDISCOUNT,OTHERDISCOUNTS,TYPE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',1,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED'," + lstItems[k].Quant + ",'" + ManageQuote(strinvid) + "','INCLUDE','" + ManageQuote(lstItems[k].TaxDetails) + "','DIRECT',0,0,'" + ManageQuote(lstItems[k].Ptype) + "')";
string[] str = lstItems[k].BatchNum.Split(',');
string str1 = "SELECT DSR_WARRANTY FROM TABDIRECTSTOCKRECEIPTDETAILS WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND DSR_DSRNO IN(SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHBATCHNUMBER IN('" + str[0] + "'))";
NpgsqlDataReader dr1 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, str1);

while (dr1.Read())
{
warranty = dr1["dsr_warranty"].ToString();
}

TempQuery = TempQuery + "INSERT INTO TEMPTABDIRECTSTOCKRECEIPTDETAILS(DSR_ORDERDATE,PRDID,PRDNAME,DSR_INVOICENO,VCHBATCHNUMBER,DSR_COSTPERITEM,DSR_QUANTITY,DSR_AMOUNT,DSR_VAT,DSR_OTHERTAX,DSR_OTHERCHARGES,DSR_OTHERDISCOUNTS,VATTYPE,DSR_WARRANTY,DSR_TOTALAMOUNT,TYPE,VENID,VENDORNAME,FROMBRANCH,STATUS,DSR_LOGINDATE)VALUES('" + ManageQuote(lstItems[k].TranDate) + "','" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].BatchNum) + "',cast(" + lstItems[k].CostPerUnit + " as numeric)," + lstItems[k].Quant + ",cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,'INCLUDE','" + warranty + "',cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "','" + VendorId + "','" + vendorName + "','" + vendorName + "','Y',CURRENT_TIMESTAMP);";
TempPrdID = TempPrdID + "'" + ManageQuote(lstItems[k].ProdId) + "'" + ",";
StoreName = ManageQuote(lstItems[k].ToGodown);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, Query1, null);
}

if (lstItems[k].Ptype == "V")
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABSTOCKMASTER SET QUANTITY= (QUANTITY)-" + Convert.ToDouble(lstItems[k].Quant) + " WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND PRDNAME='" + ManageQuote(lstItems[k].ProdName) + "'and vchbatchnumber='" + lstItems[k].BatchNum + "'", null);

}
if (lstItems[k].Ptype == "Q")
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABSTOCKMASTER SET QUANTITY= (QUANTITY)-" + Convert.ToDouble(lstItems[k].Quant) + " WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND PRDNAME='" + ManageQuote(lstItems[k].ProdName) + "'", null);
}

string[] strSrlNmbrs = lstItems[k].BatchNum.Split(',');
foreach (string part in strSrlNmbrs)
{
string DSRNO = string.Empty;
//salebatchnumber insert
if (lstItems[k].Ptype == "V")
{
if (lstItems[k].RemainingQnty == 0)
{
DSRNO = SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS='N'").ToString(); // NAVEEN 12-3-15
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABPURCHASEBATCHNUMBER SET VCHSTATUS= 'Y' WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHPRNO='" + DSRNO + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS= 'N'", null); // NAVEEN 12-3-15
}
}
if (lstItems[k].Ptype == "Q")
{
DSRNO = SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS='N'").ToString(); // NAVEEN 12-3-15
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABPURCHASEBATCHNUMBER SET VCHSTATUS= 'Y' WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHPRNO='" + DSRNO + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "' AND VCHSTATUS= 'N'", null); // NAVEEN 12-3-15
}
decimal Hopurcost = 0;
Hopurcost = Convert.ToDecimal(SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT HOPURCOST FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHPRNO='" + DSRNO + "'"));
//SqlHelper.ExecuteNonQuery(trans,CommandType.Text,"INSERT INTO TABSALEBATCHNUMBER(DCNO,VCHSUTID,DATDCDATE,DATDATE,VCHPROID,VCHBATCHNUMBER,LOGINID,LOGINDATE,VCHSTATUS,VCHGODOWN) VALUES ('" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'Y','" + ManageQuote(lstItems[k].ToGodown) + "')", null);
//cast(" + lstItems[k].CostPerUnit + " as numeric)
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSALEBATCHNUMBER(DCNO,VCHSUTID,DATDCDATE,DATDATE,VCHPROID,VCHBATCHNUMBER,LOGINID,LOGINDATE,VCHSTATUS,VCHGODOWN,NUMQUANTITY,TYPE,SALEPRICE,HOPURCOST,DSR_DSRNO) VALUES ('" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'Y','" + ManageQuote(lstItems[k].ToGodown) + "',cast(" + lstItems[k].Quant + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),'" + Hopurcost + "','" + DSRNO + "')", null);
Tempbatchno = Tempbatchno + "INSERT INTO TEMPTABPURCHASEBATCHNUMBER(VCHPROID,VCHDCNO,VCHBATCHNUMBER,DATDCDATE,LOGINDATE,TRANSFERSALEPRICE,VCHVENID,VENDORNAME,FROMBRANCH,HOPURCOST,VCHSTATUS,TYPE) VALUES('" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,cast(" + lstItems[k].CostPerUnit + " as numeric),'" + VendorId + "','" + vendorName + "','" + vendorName + "','" + Hopurcost + "','Y','" + ManageQuote(lstItems[k].Ptype) + "');";
}
}
//FilePath = DatePath;
strNextId = strinvid;
Prdcount = lstItems.Count;
CreateFolders(strinvid, Loginid, out Path);
lstItems.Clear();
trans.Commit();
}
}
return true;

}
catch (NpgsqlException ex)
{
string except = ex.Message.ToString();
trans.Rollback();
return false;
}
finally
{
con.Close();
}
}

public List<string> checkSerilnumbers(List<string> lstserialnumbers)
{
List<string> objlistSerialNumberDetails = new List<string>();
for (int i = 0; i < lstserialnumbers.Count; i++)
{
//InventoryDTO.StockTransferIssueDTO objserialnumbersDTO = new InventoryDTO.StockTransferIssueDTO();
string strStatus = Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT vchstatus from TABPURCHASEBATCHNUMBER where vchbatchnumber='" + ManageQuote(lstserialnumbers[i].ToString()) + "'"));
if (strStatus == "Y")
{
//InventoryDTO.DirectSalesInvoiceDTO objserialnumbersDTO = new InventoryDTO.DirectSalesInvoiceDTO();
string lstnum = lstserialnumbers[i].ToString();
objlistSerialNumberDetails.Add(lstnum);

}
}
return objlistSerialNumberDetails;
}

public bool savedatatable(DataTable dt)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

NpgsqlDataAdapter da = new NpgsqlDataAdapter();
da.Update(dt);
//foreach (DataRow item in dt.Rows)
//{
// //SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tabstudent values('" + item["stuname"].ToString() + "','" + item["sloc"].ToString() + "')", null);

//}

trans.Commit();
return true;
}

public DataTable ShowData()
{
try
{
return SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT stuname,sloc from tabstudent").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
}

public DataSet GetProductName()
{
try
{
return SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT distinct p.PRDID,p.PRDNAME FROM TABPRODUCT as p join tabpurchasebatchnumber as bno on p.prdid=bno.vchproid WHERE p.STATUS='ACTIVE' and bno.vchstatus='N' ORDER BY PRDNAME");
}
catch (Exception ex)
{
throw ex;
}
}

public void CreateFolders(string strinvid, string loginID, out string fPath)
{
string CompanyName = vendorName;

DateTime Datetime = DateTime.Now;
fPath = string.Empty;

String Year = DateTime.Now.Year.ToString();
String Month = DateTime.Now.ToString("MMMM");
String day = DateTime.Now.ToString("dd-MMMM-yyyy");
string path = Application.StartupPath;
string FileGeneratorPath = path + "\\" + "TextFiles" + "\\";
//string CompanyPath = FileGeneratorPath + StoreName + "\\";
string Yearpath = FileGeneratorPath + Year + "\\";
string Monthpath = Yearpath + Month + "\\";
DatePath = Monthpath + day + "\\";
storePath = DatePath + StoreName + "\\";

try
{
// Create Folder With "TextFileGenerator" if not Exist
//if (!Directory.Exists(FileGeneratorPath))
//{
// //TxtFileGeneratorDir = Directory.CreateDirectory("\\\\192.168.2.77\\E$\\" + CompanyName + "\\");
// TxtFileGeneratorDir = Directory.CreateDirectory(path + "\\" + CompanyName + "\\");
//}

// Create Folder With "CompanyName(Selected Company)" if not Exist
//if (!Directory.Exists(CompanyPath))
//{
// Directory.CreateDirectory(CompanyPath);
//}

// Create Folder With "Year(Current Year)" if not Exist
if (!Directory.Exists(Yearpath))
{
Directory.CreateDirectory(Yearpath);
}

// Create Folder With "Month(Current Month)" if not Exist
if (!Directory.Exists(Monthpath))
{
Directory.CreateDirectory(Monthpath); ;
}

// Create Folder With "Date(Current Date)" if not Exist
if (!Directory.Exists(DatePath))
{
Directory.CreateDirectory(DatePath);
}

WriteToLog(DatePath, StoreName, CompanyName, strinvid, loginID, out fPath);
}
catch (Exception ex)
{
throw ex;
}

}

private void WriteToLog(string storePath, string StoreName, string CompanyName, string strinvid, string LoginID, out string path)
{
try
{
TempPrdID = TempPrdID.TrimEnd(',');
TempQuery.TrimEnd(';');
string[] Query = TempQuery.Split(';');
Tempbatchno.TrimEnd(';');
string[] Batchnum = Tempbatchno.Split(';');
string[] Invno = strinvid.Split('/');
FileName = Invno[0] + "-" + Invno[1] + ".txt";
//FileName = StoreName.Substring(0, 2) + RecordId + ".txt";
//string FileName = "(" + RecordId + ")" + DateTime.Now.ToString("dd-MMMM-yy").ToUpper() + ".txt";
string filepath = storePath + FileName;
path = DatePath;

StreamWriter writer = File.CreateText(filepath);
writer.WriteLine();
writer.Close();

if (File.Exists(filepath))
{
FileStream errStream1 = new FileStream(filepath, FileMode.Append, FileAccess.Write);
StreamWriter Writer1 = new StreamWriter(errStream1);
Writer1.WriteLine("CREATE OR REPLACE FUNCTION ExecuteScript()");
Writer1.WriteLine(" RETURNS void AS"); Writer1.WriteLine("$BODY$");
Writer1.WriteLine("DECLARE");
Writer1.WriteLine("BEGIN");
Writer1.WriteLine("IF ('" + OfficeID + "'<>(SELECT OFFICEID FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH)) AND '" + CusID + "'=(SELECT OFFICEID FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH))) THEN");

Writer1.WriteLine("IF " + Prdcount + "=(SELECT COUNT(*) FROM TABPRODUCT WHERE PRDID IN(" + TempPrdID + "))THEN");
Writer1.WriteLine("IF 0=(SELECT COUNT(*)FROM TABDATAIMPORT WHERE DESCRIPTION='STOCK TRANSFERRED BY " + ManageQuote(LoginID) + " FROM " + ManageQuote(CompanyName) + " TO " + ManageQuote(StoreName) + " WITH:" + ManageQuote(strinvid) + "')THEN");
Writer1.WriteLine();
foreach (string str in Query)
{
if (str.Length > 0)
{
Writer1.WriteLine(str + ";");
Writer1.WriteLine();
}
}
foreach (string batch in Batchnum)
{
if (batch.Length > 0)
{
Writer1.WriteLine(batch + ";");
Writer1.WriteLine();
}
}
string QUERY = "INSERT INTO TABDATAIMPORT VALUES('STOCK TRANSFERRED BY " + ManageQuote(LoginID) + " FROM " + ManageQuote(CompanyName) + " TO " + ManageQuote(StoreName) + " WITH:" + ManageQuote(strinvid) + "',CURRENT_TIMESTAMP);";
//Writer1.WriteLine("INSERT INTO TABDATAIMPORT VALUES('STOCK RECEIVED FROM'"+CompanyName+"',CURRENT_TIMESTAMP);");

Writer1.WriteLine(QUERY); Writer1.WriteLine("ELSE"); Writer1.WriteLine("RAISE NOTICE 'SCRIPT ALREADY EXECUTED CONTACT EDP';");
Writer1.WriteLine("END IF;");
Writer1.WriteLine("ELSE");
Writer1.WriteLine("RAISE NOTICE 'Product Names Not Existed... Please Contact Admin';");
Writer1.WriteLine("END IF;");

Writer1.WriteLine("ELSE");
Writer1.WriteLine("RAISE NOTICE 'You Canot Purchase..,These Product(s) Can Purchase in " + ManageQuote(StoreName) + " Store Only... Please Contact Admin';");
Writer1.WriteLine("END IF;");
Writer1.WriteLine(); Writer1.WriteLine("RETURN;"); Writer1.WriteLine("END;");
Writer1.WriteLine("$BODY$"); Writer1.WriteLine("LANGUAGE 'plpgsql' VOLATILE;"); Writer1.WriteLine(); Writer1.WriteLine("SELECT ExecuteScript();");
Writer1.WriteLine();
Writer1.Close();
}

storePath = string.Empty;
FileName = string.Empty; ;
StoreName = string.Empty;
TempQuery = string.Empty;
Tempbatchno = string.Empty;
TempPrdID = string.Empty;
VendorId = string.Empty;
vendorName = string.Empty;
DatePath = string.Empty;
OfficeID = string.Empty;
CusID = string.Empty;
}

catch (Exception ex)
{
throw ex;
}
}

#region StockPoint To StockPoint
/// <summary>
/// This Method is used for Generating NextId of STP
/// </summary>
/// <param name="strtablename"></param>
/// <param name="strcolname"></param>
/// <param name="prefix"></param>
/// <param name="strdate"></param>
/// <returns></returns>
public string GenerateSTPId(string strtablename, string strcolname, int prefix, string strdate)
{
try
{
DateTime dt = Convert.ToDateTime(strdate);
string stryear = dt.Year.ToString();
string strFromdate = "01-APR-" + stryear;
if (Convert.ToDateTime(strdate) < Convert.ToDateTime(strFromdate))
{
strFromdate = Convert.ToDateTime(strFromdate).AddMonths(-12).ToString("dd-MMM-yyyy");
}
stryear = Convert.ToDateTime(strFromdate).Year.ToString();
string strToDate = Convert.ToDateTime(strFromdate).AddMonths(12).AddDays(-1).ToString("dd-MMM-yyyy");
string strId = SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT COALESCE(MAX(TO_NUMBER(SUBSTR(" + ManageQuote(strcolname) + "," + (prefix + 1) + " , LENGTH( " + ManageQuote(strcolname) + ")-(3+" + prefix + ")),'9999999'))+1,1) ||'/" + stryear.Substring(2) + "' FROM " + ManageQuote(strtablename) + " WHERE TRANSDATE BETWEEN '" + ManageQuote(strFromdate) + "' AND '" + ManageQuote(strToDate) + "'").ToString();
return strId;
}
catch
{
return null;
}
}
/// <summary>
/// This Method is used to Get the Product Names. Here passing the Branch Name is not required
/// </summary>
/// <param name="BranchName"></param>
/// <returns></returns>
public List<InventoryDTO.StockTransferIssueDTO> GetSTPProductName()
{
List<InventoryDTO.StockTransferIssueDTO> lstProdName = new List<InventoryDTO.StockTransferIssueDTO>();
try
{
NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT DISTINCT PRDNAME FROM TABPRODUCT WHERE STATUS='ACTIVE'");
while (rdr.Read())
{
InventoryDTO.StockTransferIssueDTO objProdName = new InventoryDTO.StockTransferIssueDTO();
objProdName.ProdName = rdr["PRDNAME"].ToString();
lstProdName.Add(objProdName);
}

rdr.Close();
return lstProdName;
}
catch
{
return null;
}
}

public bool saveStockPointToStockPoint(List<StockTransferIssueDTO> lstItems, out string strNextId)
{
strNextId = string.Empty;
string strddcid = string.Empty;

string strJVNo = string.Empty;
try
{
if (lstItems.Count > 0)
{
strddcid = "STP" + GenerateSTPId("TABSTOCKPOINT", "STP_NO", 2, lstItems[0].TranDate);

if (!string.IsNullOrEmpty(strddcid) && strddcid != null && strddcid.Length > 3)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSTOCKPOINT(STP_NO ,VCHFROMOFFICE,VCHTOOFFICE,TRANSDATE ,TRANS_INVNO ,INV_TOT ,LOGINID,LOGINDATE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[0].FromGodown) + "','" + ManageQuote(lstItems[0].ToGodown) + "','" + ManageQuote(lstItems[0].TranDate) + "','" + ManageQuote(lstItems[0].ddc_id) + "',cast(" + lstItems[0].TotAmount + " as numeric),'" + ManageQuote(lstItems[0].LoginId) + "',CURRENT_TIMESTAMP)", null);

for (int k = 0; k < lstItems.Count; k++)
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSTOCKPOINTDETAILS(STP_NO,VCHPRDNAME,NUMTRANSQTY,PRDAMT,LOGINID,LOGINDATE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].Quant + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP)", null);
}

strNextId = strddcid;
trans.Commit();
}
}

return true;

}
catch (NpgsqlException ex)
{
string except = ex.Message.ToString();
trans.Rollback();
return false;
}
finally
{
con.Close();
}
}

#endregion
}
}
---------------------------------------------------------------

My Helper Class

//===============================================================================
// This file is based on the Microsoft Data Access Application Block for .NET
// For more information please go to
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//===============================================================================

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using Npgsql;
using System.Text;
using System.ComponentModel;

namespace EasyAccounts.DAL
{
public abstract class SqlHelper
{

static string database = ConfigurationManager.AppSettings["Database"].ToString();
static string host = ConfigurationManager.AppSettings["Server"].ToString();
static string uname = ConfigurationManager.AppSettings["UserId"].ToString();
static string pwd = ConfigurationManager.AppSettings["Password"].ToString();
static string cmdtimeout = ConfigurationManager.AppSettings["cmdtimeout"].ToString();
static string timeout = ConfigurationManager.AppSettings["timeout"].ToString();
static string port = ConfigurationManager.AppSettings["port"].ToString();
//static string maxPoolSize = ConfigurationManager.AppSettings["MaxPoolSize"].ToString();

//static string database1 = ConfigurationManager.AppSettings["Database1"].ToString();
//static string host1 = ConfigurationManager.AppSettings["Server1"].ToString();
//static string uname1 = ConfigurationManager.AppSettings["UserId1"].ToString();
//static string pwd1 = ConfigurationManager.AppSettings["Password1"].ToString();

//Database connection strings
public static string SQLConnString = "UserId=" + uname + ";Password=" + pwd + ";Server=" + host + ";port=" + port + ";Database=" + database + ";CommandTimeout=" + cmdtimeout + ";Timeout=" + timeout + "";
// public static string conMpruser = "UserId=" + uname1 + ";Password=" + pwd1 + ";Server=" + host1 + ";Database=" + database1 + "";

// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(NpgsqlConnection connection, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(NpgsqlTransaction trans, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// NpgsqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A NpgsqlDataReader containing the results</returns>
public static NpgsqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();
NpgsqlConnection conn = new NpgsqlConnection(connectionString);

// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}

/// <summary>
/// Create and prepare a NpgsqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection, on which to execute this command</param>
/// <param name="transaction">A valid NpgsqlTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of NpgsqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>NpgsqlDataReader containing the results of the command</returns>
public static NpgsqlDataReader ExecuteReader(NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
try
{
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Create a reader
NpgsqlDataReader dataReader;

dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

// Detach the NpgsqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (NpgsqlParameter commandParameter in cmd.Parameters)
{

if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}

if (canClear)
{
cmd.Parameters.Clear();
}

return dataReader;
}
catch
{
transaction.Connection.Close();
throw;
}
}

/// <summary>
/// Execute a NpgsqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();

using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}

/// <summary>
/// Execute a NpgsqlCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(NpgsqlConnection connection, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a 1x1 resultset) against the specified NpgsqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
//bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Execute the command & return the results
object retval = cmd.ExecuteScalar();

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

// Create & open a NpgsqlConnection, and dispose of it after we are done
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();

// Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the provided NpgsqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(connection, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the specified NpgsqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.CommandTimeout = 120;

PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters);

// Create the DataAdapter & DataSet
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
da.FillSchema(ds, SchemaType.Source);

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();

//if (mustCloseConnection)
// connection.Close();

// Return the dataset
return ds;
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the specified NpgsqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the provided NpgsqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(transaction, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the specified NpgsqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Create the DataAdapter & DataSet
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();

// Return the dataset
return ds;
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the specified
/// NpgsqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class SqlHelperParameterCache
{
#region private methods, variables, and constructors

//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelperParameterCache()"
private SqlHelperParameterCache() { }

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
/// Resolve at run time the appropriate set of NpgsqlParameters for a stored procedure
/// </summary>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
/// <returns>The parameter array discovered.</returns>
private static NpgsqlParameter[] DiscoverSpParameterSet(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

NpgsqlCommand cmd = new NpgsqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;

connection.Open();
NpgsqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

NpgsqlParameter[] discoveredParameters = new NpgsqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

// Init the parameters with a DBNull value
foreach (NpgsqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}

/// <summary>
/// Deep copy of cached NpgsqlParameter array
/// </summary>
/// <param name="originalParameters"></param>
/// <returns></returns>
private static NpgsqlParameter[] CloneParameters(NpgsqlParameter[] originalParameters)
{
NpgsqlParameter[] clonedParameters = new NpgsqlParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (NpgsqlParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

#endregion private methods, variables, and constructors

#region caching functions

/// <summary>
/// Add parameter array to the cache
/// </summary>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters to be cached</param>
public static void CacheParameterSet(string connectionString, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

string hashKey = connectionString + ":" + commandText;

paramCache[hashKey] = commandParameters;
}

/// <summary>
/// Retrieve a parameter array from the cache
/// </summary>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>An array of SqlParamters</returns>
public static NpgsqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

string hashKey = connectionString + ":" + commandText;

NpgsqlParameter[] cachedParameters = paramCache[hashKey] as NpgsqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}

#endregion caching functions

#region Parameter Discovery Functions

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <returns>An array of NpgsqlParameters</returns>
public static NpgsqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
public static NpgsqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <returns>An array of NpgsqlParameters</returns>
internal static NpgsqlParameter[] GetSpParameterSet(NpgsqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
internal static NpgsqlParameter[] GetSpParameterSet(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
using (NpgsqlConnection clonedConnection = (NpgsqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
}
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
private static NpgsqlParameter[] GetSpParameterSetInternal(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

NpgsqlParameter[] cachedParameters;

cachedParameters = paramCache[hashKey] as NpgsqlParameter[];
if (cachedParameters == null)
{
NpgsqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[hashKey] = spParameters;
cachedParameters = spParameters;
}

return CloneParameters(cachedParameters);
}

#endregion Parameter Discovery Functions

}

/// <summary>
/// This method assigns dataRow column values to an array of NpgsqlParameters
/// </summary>
/// <param name="commandParameters">Array of NpgsqlParameters to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
// Do nothing if we get no data
return;
}

int i = 0;
// Set the parameters values
foreach (NpgsqlParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}

/// <summary>
/// This method assigns an array of values to an array of NpgsqlParameters
/// </summary>
/// <param name="commandParameters">Array of NpgsqlParameters to be assigned values</param>
/// <param name="parameterValues">Array of objects holding the values to be assigned</param>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}

// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

// Iterate through the NpgsqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// If the current array value derives from IDbDataParameter, then assign its Value property
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}

/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
public static void CacheParameters(string cacheKey, params NpgsqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}

/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</returns>
public static NpgsqlParameter[] GetCachedParameters(string cacheKey)
{
NpgsqlParameter[] cachedParms = (NpgsqlParameter[])parmCache[cacheKey];

if (cachedParms == null)
return null;

NpgsqlParameter[] clonedParms = new NpgsqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (NpgsqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;
}

/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">NpgsqlCommand object</param>
/// <param name="conn">NpgsqlConnection object</param>
/// <param name="trans">NpgsqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">NpgsqlParameters to use in the command</param>
//private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, CommandType cmdType, string cmdText, NpgsqlParameter[] cmdParms) {

// if (conn.State != ConnectionState.Open)
// conn.Open();

// cmd.Connection = conn;
// cmd.CommandText = cmdText;

// if (trans != null)
// cmd.Transaction = trans;

// cmd.CommandType = cmdType;

// if (cmdParms != null) {
// foreach (NpgsqlParameter parm in cmdParms)
// cmd.Parameters.Add(parm);
// }
//}

private static void PrepareCommand(NpgsqlCommand command, NpgsqlConnection connection, NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{

connection.Open();
}
else
{

}

// Associate the connection with the command
command.Connection = connection;

// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;

// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}

// Set the command type
command.CommandType = commandType;

// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}

/// <summary>
/// This method is used to attach array of NpgsqlParameters to a NpgsqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">An array of NpgsqlParameters to be added to command</param>
private static void AttachParameters(NpgsqlCommand command, NpgsqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (NpgsqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
}
}using System;
using System.Collections.Generic;
using System.Text;
using EasyAccounts.DTO;
using Npgsql;
using EasyAccounts.DAL;
using System.Data;
using InventoryDTO;
using System.IO;
using System.Windows.Forms;

namespace InventoryDAL
{
public class GodownStockDAL
{
#region UserVariables
NpgsqlConnection con = new NpgsqlConnection(SqlHelper.SQLConnString);
NpgsqlTransaction trans;
int Prdcount = 0;

//Naveen
string storePath, FileName;
string StoreName = string.Empty;
string TempQuery = string.Empty;
string Tempbatchno = string.Empty;
string TempPrdID = string.Empty;
string VendorId = string.Empty;
string vendorName = string.Empty;
string DatePath = string.Empty;
string OfficeID = string.Empty;
string CusID = string.Empty;
#endregion

private string ManageQuote(string strMessage)
{
try
{
if (strMessage != null && strMessage != "")
{
strMessage = strMessage.Replace("'", "''");
}
}
catch (Exception)
{
return strMessage;
}
return strMessage;
}

public static string FormatDate(string strDate)
{
string Date = null;
string[] dat = null;
if (strDate != null)
{
if (strDate.Contains("/"))
{
dat = strDate.Split('/');
}
else if (strDate.Contains("-"))
{
dat = strDate.Split('-');
}
Date = dat[2] + "-" + dat[1] + "-" + dat[0];
}
return Date;
}

public string GenerateGSTId(string strtablename, string strcolname, int prefix, string strdate)
{
try
{
DateTime dt = Convert.ToDateTime(strdate);
string stryear = dt.Year.ToString();
string strFromdate = "01-APR-" + stryear;
if (Convert.ToDateTime(strdate) < Convert.ToDateTime(strFromdate))
{
strFromdate = Convert.ToDateTime(strFromdate).AddMonths(-12).ToString("dd-MMM-yyyy");
}
stryear = Convert.ToDateTime(strFromdate).Year.ToString();
string strToDate = Convert.ToDateTime(strFromdate).AddMonths(12).AddDays(-1).ToString("dd-MMM-yyyy");
string strId = SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT COALESCE(MAX(TO_NUMBER(SUBSTR(" + ManageQuote(strcolname) + "," + (prefix + 1) + " , LENGTH( " + ManageQuote(strcolname) + ")-(3+" + prefix + ")),'9999999'))+1,1) ||'/" + stryear.Substring(2) + "' FROM " + ManageQuote(strtablename) + " WHERE DDC_ORDERDATE BETWEEN '" + ManageQuote(strFromdate) + "' AND '" + ManageQuote(strToDate) + "'").ToString();
return strId;
}
catch
{
return null;
}
}

public List<InventoryDTO.StockTransferIssueDTO> GetToBranchDetails()
{
List<InventoryDTO.StockTransferIssueDTO> lstBranch = new List<InventoryDTO.StockTransferIssueDTO>();
try
{
//NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT DISTINCT VCHGODOWNNAME FROM TABGODOWN ORDER BY VCHGODOWNNAME");
NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT OFFICEID,OFFICENAME,status FROM TABOFFICES WHERE OFFICENAME NOT IN (SELECT NAME FROM TABBRANCH) AND TERRITORY IN(SELECT TERRITORY FROM TABBRANCH) AND STATUS='Y' ORDER BY OFFICENAME");
while (rdr.Read())
{
InventoryDTO.StockTransferIssueDTO objGodownName = new InventoryDTO.StockTransferIssueDTO();
objGodownName.ToGodownId = rdr["OFFICEID"].ToString();
objGodownName.ToGodown = rdr["OFFICENAME"].ToString();

lstBranch.Add(objGodownName);
}

rdr.Close();
return lstBranch;
}
catch
{
return null;
}
}

public DataSet GetProdTaxDetails(string ProdName)
{
DataSet dsProdTax = new DataSet();
dsProdTax = SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT A.CATEGORYTYPEID,A.CATEGORYTYPE,B.TAXRATE,CAST(B.TAXRATE||'% on '||B.CATEGORYTYPE AS VARCHAR(50)) AS TAXDETAILS FROM TABPRODUCT A JOIN TABTAXDETAILS B ON A.CATEGORYTYPEID=B.CATEGORYTYPEID WHERE PRDNAME='" + ManageQuote(ProdName) + "'", null);
return dsProdTax;
}

public int DuplicateName(string agentname, string category)
{
int cnt = -1;
try
{
cnt = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT count(*) FROM TABAGENTSETUP WHERE VCHNAME='" + ManageQoute(agentname) + "' AND vchcategory='" + ManageQoute(category) + "'"));
}
catch
{
}
return cnt;
}

public bool saveStockTransferIssue(List<StockTransferIssueDTO> lstItems, out string strNextId, out string Path)
{
strNextId = string.Empty;
string strddcid = string.Empty;
string strinvid = string.Empty;
string strJVNo = string.Empty;
Path = string.Empty;
TempQuery = string.Empty;

try
{
if (lstItems.Count > 0)
{
strddcid = "DDC" + GenerateGSTId("TABDIRECTDELIVERYCHALLAN", "DDC_DDCNO", 2, lstItems[0].TranDate);
strinvid = "INV" + GenerateGSTId("TABDIRECTDELIVERYCHALLAN", "INVOICENO", 2, lstItems[0].TranDate);

if (!string.IsNullOrEmpty(strddcid) && strddcid != null && strddcid.Length > 3)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

string Loginid = ManageQuote(lstItems[0].LoginId);
CusID = lstItems[0].ToGodownId;
// tabdirectdeliverychallan insert.
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABDIRECTDELIVERYCHALLAN(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_CUSNAME,TOTALAMOUNT,INVOICENO,DUEAMOUNT,OTHERCHARGES,GRDOTHERDISCOUNT,NUMAGENTPER,NUMCOMMAMT,VCHGODOWN,SALES_TYPE,DDC_VAT) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[0].ToGodownId) + "','" + ManageQuote(lstItems[0].TranDate) + "','" + ManageQuote(lstItems[0].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED','" + ManageQuote(lstItems[0].ToGodown) + "',cast(" + lstItems[0].TotAmount + " as numeric),'" + ManageQuote(strinvid) + "',cast(" + lstItems[0].TotAmount + " as numeric),0,0,0,0,'" + ManageQuote(lstItems[0].FromGodown) + "','STOCK ISSUED',cast(" + lstItems[0].VatAmount + " as numeric))", null);
NpgsqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT OFFICEID,OFFICENAME FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH)");

while (dr.Read())
{
vendorName = dr["officename"].ToString();
VendorId = dr["officeid"].ToString();
}
OfficeID = VendorId;

//naveen
//if ( VendorId=="" || VendorId=="OFF7"||VendorId==string.Empty)
//{
// string Name= SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT NAME FROM TABBRANCH").ToString();
// vendorName = Name == "DIAMONDPOINT" ? "HYDERABAD" : vendorName;
// OfficeID = VendorId = "OFF7";

//}

for (int k = 0; k < lstItems.Count; k++)
{

if (lstItems[k].Ptype == "V")
{
string warranty = string.Empty;
string Mfgdate = string.Empty;
string ExpDate = string.Empty;
string Mrp = string.Empty;
string SalePrice = string.Empty;
string Query1 = "INSERT INTO TABDIRECTDELIVERYCHALLANDETAILS(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_DDCNODETAILID,PRDID,PRDNAME,DDC_COSTPERITEM,DDC_AMOUNT,DDC_VAT,DDC_OTHERTAX,DDC_OTHERCHARGES,DDC_OTHERDISCOUNTS,DDC_TOTALAMOUNT,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_QUANTITY,INVOICENO,VATTYPE,VATDETAILS,VCHGODOWN,VCHBATCHNUMBER,PRDDISCOUNT,OTHERDISCOUNTS,TYPE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',1,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED'," + lstItems[k].Quant + ",'" + ManageQuote(strinvid) + "','INCLUDE','" + ManageQuote(lstItems[k].TaxDetails) + "','DIRECT','" + ManageQuote(lstItems[k].BatchNum) + "',0,0,'" + ManageQuote(lstItems[k].Ptype) + "')";
string[] str = lstItems[k].BatchNum.Split(',');
string str1 = "SELECT DSR_WARRANTY FROM TABDIRECTSTOCKRECEIPTDETAILS WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND DSR_DSRNO IN(SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHBATCHNUMBER IN('" + str[0] + "'))";
NpgsqlDataReader dr1 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, str1);

while (dr1.Read())
{
warranty = dr1["dsr_warranty"].ToString();
}

string QRY = "SELECT MFGDATE,EXPDATE,NUMMRP,NUMSALEPRICE FROM TABSTOCKMASTER WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER='" + str[0] + "'";

NpgsqlDataReader DR2 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, QRY);
while (DR2.Read())
{
Mfgdate = Convert.ToDateTime(DR2["MFGDATE"]).ToString("dd-MMM-yyyy");
ExpDate = Convert.ToDateTime(DR2["EXPDATE"]).ToString("dd-MMM-yyyy");
Mrp = DR2["NUMMRP"].ToString();
SalePrice = DR2["NUMSALEPRICE"].ToString();
}

TempQuery = TempQuery + "INSERT INTO TEMPTABDIRECTSTOCKRECEIPTDETAILS(DSR_ORDERDATE,PRDID,PRDNAME,DSR_INVOICENO,VCHBATCHNUMBER,DSR_COSTPERITEM,DSR_QUANTITY,DSR_AMOUNT,DSR_VAT,DSR_OTHERTAX,DSR_OTHERCHARGES,DSR_OTHERDISCOUNTS,VATTYPE,DSR_WARRANTY,DSR_TOTALAMOUNT,TYPE,VENID,VENDORNAME,FROMBRANCH,MFGDATE,EXPDATE,NUMSALEPRICE,NUMMRP,STATUS,DSR_LOGINDATE)VALUES('" + ManageQuote(lstItems[k].TranDate) + "','" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].BatchNum) + "',cast(" + lstItems[k].CostPerUnit + " as numeric)," + lstItems[k].Quant + ",cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,'INCLUDE','0',cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "','" + VendorId + "','" + vendorName + "','" + vendorName + "','" + Mfgdate + "','" + ExpDate + "','" + SalePrice + "','" + Mrp + "','Y',CURRENT_TIMESTAMP);";
TempPrdID = TempPrdID + "'" + ManageQuote(lstItems[k].ProdId) + "'" + ",";
StoreName = ManageQuote(lstItems[k].ToGodown);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, Query1, null);
}
else if (lstItems[k].Ptype == "Q")
{
string warranty = string.Empty;
string Query1 = "INSERT INTO TABDIRECTDELIVERYCHALLANDETAILS(DDC_DDCNO,CUSID,DDC_ORDERDATE,DDC_DDCNODETAILID,PRDID,PRDNAME,DDC_COSTPERITEM,DDC_AMOUNT,DDC_VAT,DDC_OTHERTAX,DDC_OTHERCHARGES,DDC_OTHERDISCOUNTS,DDC_TOTALAMOUNT,DDC_LOGINID,DDC_LOGINDATE,STATUS,DDC_QUANTITY,INVOICENO,VATTYPE,VATDETAILS,VCHGODOWN,PRDDISCOUNT,OTHERDISCOUNTS,TYPE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',1,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'DISPATCHED'," + lstItems[k].Quant + ",'" + ManageQuote(strinvid) + "','INCLUDE','" + ManageQuote(lstItems[k].TaxDetails) + "','DIRECT',0,0,'" + ManageQuote(lstItems[k].Ptype) + "')";
string[] str = lstItems[k].BatchNum.Split(',');
string str1 = "SELECT DSR_WARRANTY FROM TABDIRECTSTOCKRECEIPTDETAILS WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND DSR_DSRNO IN(SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHBATCHNUMBER IN('" + str[0] + "'))";
NpgsqlDataReader dr1 = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, str1);

while (dr1.Read())
{
warranty = dr1["dsr_warranty"].ToString();
}

TempQuery = TempQuery + "INSERT INTO TEMPTABDIRECTSTOCKRECEIPTDETAILS(DSR_ORDERDATE,PRDID,PRDNAME,DSR_INVOICENO,VCHBATCHNUMBER,DSR_COSTPERITEM,DSR_QUANTITY,DSR_AMOUNT,DSR_VAT,DSR_OTHERTAX,DSR_OTHERCHARGES,DSR_OTHERDISCOUNTS,VATTYPE,DSR_WARRANTY,DSR_TOTALAMOUNT,TYPE,VENID,VENDORNAME,FROMBRANCH,STATUS,DSR_LOGINDATE)VALUES('" + ManageQuote(lstItems[k].TranDate) + "','" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(lstItems[k].ProdName) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].BatchNum) + "',cast(" + lstItems[k].CostPerUnit + " as numeric)," + lstItems[k].Quant + ",cast(" + lstItems[k].indTotAmount + " as numeric),cast(" + lstItems[k].indVatAmount + " as numeric),0,0,0,'INCLUDE','" + warranty + "',cast(" + lstItems[k].TotAmount + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "','" + VendorId + "','" + vendorName + "','" + vendorName + "','Y',CURRENT_TIMESTAMP);";
TempPrdID = TempPrdID + "'" + ManageQuote(lstItems[k].ProdId) + "'" + ",";
StoreName = ManageQuote(lstItems[k].ToGodown);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, Query1, null);
}

if (lstItems[k].Ptype == "V")
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABSTOCKMASTER SET QUANTITY= (QUANTITY)-" + Convert.ToDouble(lstItems[k].Quant) + " WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND PRDNAME='" + ManageQuote(lstItems[k].ProdName) + "'and vchbatchnumber='" + lstItems[k].BatchNum + "'", null);

}
if (lstItems[k].Ptype == "Q")
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABSTOCKMASTER SET QUANTITY= (QUANTITY)-" + Convert.ToDouble(lstItems[k].Quant) + " WHERE PRDID='" + ManageQuote(lstItems[k].ProdId) + "' AND PRDNAME='" + ManageQuote(lstItems[k].ProdName) + "'", null);
}

string[] strSrlNmbrs = lstItems[k].BatchNum.Split(',');
foreach (string part in strSrlNmbrs)
{
string DSRNO = string.Empty;
//salebatchnumber insert
if (lstItems[k].Ptype == "V")
{
if (lstItems[k].RemainingQnty == 0)
{
DSRNO = SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS='N'").ToString(); // NAVEEN 12-3-15
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABPURCHASEBATCHNUMBER SET VCHSTATUS= 'Y' WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHPRNO='" + DSRNO + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS= 'N'", null); // NAVEEN 12-3-15
}
}
if (lstItems[k].Ptype == "Q")
{
DSRNO = SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT VCHPRNO FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHSTATUS='N'").ToString(); // NAVEEN 12-3-15
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE TABPURCHASEBATCHNUMBER SET VCHSTATUS= 'Y' WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHPRNO='" + DSRNO + "' AND VCHBATCHNUMBER = '" + ManageQuote(part) + "' AND VCHSTATUS= 'N'", null); // NAVEEN 12-3-15
}
decimal Hopurcost = 0;
Hopurcost = Convert.ToDecimal(SqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT HOPURCOST FROM TABPURCHASEBATCHNUMBER WHERE VCHPROID = '" + ManageQuote(lstItems[k].ProdId) + "'AND VCHBATCHNUMBER = '" + ManageQuote(part) + "'AND VCHPRNO='" + DSRNO + "'"));
//SqlHelper.ExecuteNonQuery(trans,CommandType.Text,"INSERT INTO TABSALEBATCHNUMBER(DCNO,VCHSUTID,DATDCDATE,DATDATE,VCHPROID,VCHBATCHNUMBER,LOGINID,LOGINDATE,VCHSTATUS,VCHGODOWN) VALUES ('" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'Y','" + ManageQuote(lstItems[k].ToGodown) + "')", null);
//cast(" + lstItems[k].CostPerUnit + " as numeric)
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSALEBATCHNUMBER(DCNO,VCHSUTID,DATDCDATE,DATDATE,VCHPROID,VCHBATCHNUMBER,LOGINID,LOGINDATE,VCHSTATUS,VCHGODOWN,NUMQUANTITY,TYPE,SALEPRICE,HOPURCOST,DSR_DSRNO) VALUES ('" + ManageQuote(strinvid) + "','" + ManageQuote(lstItems[k].ToGodownId) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,'" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP,'Y','" + ManageQuote(lstItems[k].ToGodown) + "',cast(" + lstItems[k].Quant + " as numeric),'" + ManageQuote(lstItems[k].Ptype) + "',cast(" + lstItems[k].CostPerUnit + " as numeric),'" + Hopurcost + "','" + DSRNO + "')", null);
Tempbatchno = Tempbatchno + "INSERT INTO TEMPTABPURCHASEBATCHNUMBER(VCHPROID,VCHDCNO,VCHBATCHNUMBER,DATDCDATE,LOGINDATE,TRANSFERSALEPRICE,VCHVENID,VENDORNAME,FROMBRANCH,HOPURCOST,VCHSTATUS,TYPE) VALUES('" + ManageQuote(lstItems[k].ProdId) + "','" + ManageQuote(strinvid) + "','" + ManageQuote(part) + "','" + ManageQuote(lstItems[k].TranDate) + "',CURRENT_DATE,cast(" + lstItems[k].CostPerUnit + " as numeric),'" + VendorId + "','" + vendorName + "','" + vendorName + "','" + Hopurcost + "','Y','" + ManageQuote(lstItems[k].Ptype) + "');";
}
}
//FilePath = DatePath;
strNextId = strinvid;
Prdcount = lstItems.Count;
CreateFolders(strinvid, Loginid, out Path);
lstItems.Clear();
trans.Commit();
}
}
return true;

}
catch (NpgsqlException ex)
{
string except = ex.Message.ToString();
trans.Rollback();
return false;
}
finally
{
con.Close();
}
}

public List<string> checkSerilnumbers(List<string> lstserialnumbers)
{
List<string> objlistSerialNumberDetails = new List<string>();
for (int i = 0; i < lstserialnumbers.Count; i++)
{
//InventoryDTO.StockTransferIssueDTO objserialnumbersDTO = new InventoryDTO.StockTransferIssueDTO();
string strStatus = Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT vchstatus from TABPURCHASEBATCHNUMBER where vchbatchnumber='" + ManageQuote(lstserialnumbers[i].ToString()) + "'"));
if (strStatus == "Y")
{
//InventoryDTO.DirectSalesInvoiceDTO objserialnumbersDTO = new InventoryDTO.DirectSalesInvoiceDTO();
string lstnum = lstserialnumbers[i].ToString();
objlistSerialNumberDetails.Add(lstnum);

}
}
return objlistSerialNumberDetails;
}

public bool savedatatable(DataTable dt)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

NpgsqlDataAdapter da = new NpgsqlDataAdapter();
da.Update(dt);
//foreach (DataRow item in dt.Rows)
//{
// //SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tabstudent values('" + item["stuname"].ToString() + "','" + item["sloc"].ToString() + "')", null);

//}

trans.Commit();
return true;
}

public DataTable ShowData()
{
try
{
return SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT stuname,sloc from tabstudent").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
}

public DataSet GetProductName()
{
try
{
return SqlHelper.ExecuteDataset(SqlHelper.SQLConnString, CommandType.Text, "SELECT distinct p.PRDID,p.PRDNAME FROM TABPRODUCT as p join tabpurchasebatchnumber as bno on p.prdid=bno.vchproid WHERE p.STATUS='ACTIVE' and bno.vchstatus='N' ORDER BY PRDNAME");
}
catch (Exception ex)
{
throw ex;
}
}

public void CreateFolders(string strinvid, string loginID, out string fPath)
{
string CompanyName = vendorName;

DateTime Datetime = DateTime.Now;
fPath = string.Empty;

String Year = DateTime.Now.Year.ToString();
String Month = DateTime.Now.ToString("MMMM");
String day = DateTime.Now.ToString("dd-MMMM-yyyy");
string path = Application.StartupPath;
string FileGeneratorPath = path + "\\" + "TextFiles" + "\\";
//string CompanyPath = FileGeneratorPath + StoreName + "\\";
string Yearpath = FileGeneratorPath + Year + "\\";
string Monthpath = Yearpath + Month + "\\";
DatePath = Monthpath + day + "\\";
storePath = DatePath + StoreName + "\\";

try
{
// Create Folder With "TextFileGenerator" if not Exist
//if (!Directory.Exists(FileGeneratorPath))
//{
// //TxtFileGeneratorDir = Directory.CreateDirectory("\\\\192.168.2.77\\E$\\" + CompanyName + "\\");
// TxtFileGeneratorDir = Directory.CreateDirectory(path + "\\" + CompanyName + "\\");
//}

// Create Folder With "CompanyName(Selected Company)" if not Exist
//if (!Directory.Exists(CompanyPath))
//{
// Directory.CreateDirectory(CompanyPath);
//}

// Create Folder With "Year(Current Year)" if not Exist
if (!Directory.Exists(Yearpath))
{
Directory.CreateDirectory(Yearpath);
}

// Create Folder With "Month(Current Month)" if not Exist
if (!Directory.Exists(Monthpath))
{
Directory.CreateDirectory(Monthpath); ;
}

// Create Folder With "Date(Current Date)" if not Exist
if (!Directory.Exists(DatePath))
{
Directory.CreateDirectory(DatePath);
}

WriteToLog(DatePath, StoreName, CompanyName, strinvid, loginID, out fPath);
}
catch (Exception ex)
{
throw ex;
}

}

private void WriteToLog(string storePath, string StoreName, string CompanyName, string strinvid, string LoginID, out string path)
{
try
{
TempPrdID = TempPrdID.TrimEnd(',');
TempQuery.TrimEnd(';');
string[] Query = TempQuery.Split(';');
Tempbatchno.TrimEnd(';');
string[] Batchnum = Tempbatchno.Split(';');
string[] Invno = strinvid.Split('/');
FileName = Invno[0] + "-" + Invno[1] + ".txt";
//FileName = StoreName.Substring(0, 2) + RecordId + ".txt";
//string FileName = "(" + RecordId + ")" + DateTime.Now.ToString("dd-MMMM-yy").ToUpper() + ".txt";
string filepath = storePath + FileName;
path = DatePath;

StreamWriter writer = File.CreateText(filepath);
writer.WriteLine();
writer.Close();

if (File.Exists(filepath))
{
FileStream errStream1 = new FileStream(filepath, FileMode.Append, FileAccess.Write);
StreamWriter Writer1 = new StreamWriter(errStream1);
Writer1.WriteLine("CREATE OR REPLACE FUNCTION ExecuteScript()");
Writer1.WriteLine(" RETURNS void AS"); Writer1.WriteLine("$BODY$");
Writer1.WriteLine("DECLARE");
Writer1.WriteLine("BEGIN");
Writer1.WriteLine("IF ('" + OfficeID + "'<>(SELECT OFFICEID FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH)) AND '" + CusID + "'=(SELECT OFFICEID FROM TABOFFICES WHERE OFFICENAME IN(SELECT NAME FROM TABBRANCH))) THEN");

Writer1.WriteLine("IF " + Prdcount + "=(SELECT COUNT(*) FROM TABPRODUCT WHERE PRDID IN(" + TempPrdID + "))THEN");
Writer1.WriteLine("IF 0=(SELECT COUNT(*)FROM TABDATAIMPORT WHERE DESCRIPTION='STOCK TRANSFERRED BY " + ManageQuote(LoginID) + " FROM " + ManageQuote(CompanyName) + " TO " + ManageQuote(StoreName) + " WITH:" + ManageQuote(strinvid) + "')THEN");
Writer1.WriteLine();
foreach (string str in Query)
{
if (str.Length > 0)
{
Writer1.WriteLine(str + ";");
Writer1.WriteLine();
}
}
foreach (string batch in Batchnum)
{
if (batch.Length > 0)
{
Writer1.WriteLine(batch + ";");
Writer1.WriteLine();
}
}
string QUERY = "INSERT INTO TABDATAIMPORT VALUES('STOCK TRANSFERRED BY " + ManageQuote(LoginID) + " FROM " + ManageQuote(CompanyName) + " TO " + ManageQuote(StoreName) + " WITH:" + ManageQuote(strinvid) + "',CURRENT_TIMESTAMP);";
//Writer1.WriteLine("INSERT INTO TABDATAIMPORT VALUES('STOCK RECEIVED FROM'"+CompanyName+"',CURRENT_TIMESTAMP);");

Writer1.WriteLine(QUERY); Writer1.WriteLine("ELSE"); Writer1.WriteLine("RAISE NOTICE 'SCRIPT ALREADY EXECUTED CONTACT EDP';");
Writer1.WriteLine("END IF;");
Writer1.WriteLine("ELSE");
Writer1.WriteLine("RAISE NOTICE 'Product Names Not Existed... Please Contact Admin';");
Writer1.WriteLine("END IF;");

Writer1.WriteLine("ELSE");
Writer1.WriteLine("RAISE NOTICE 'You Canot Purchase..,These Product(s) Can Purchase in " + ManageQuote(StoreName) + " Store Only... Please Contact Admin';");
Writer1.WriteLine("END IF;");
Writer1.WriteLine(); Writer1.WriteLine("RETURN;"); Writer1.WriteLine("END;");
Writer1.WriteLine("$BODY$"); Writer1.WriteLine("LANGUAGE 'plpgsql' VOLATILE;"); Writer1.WriteLine(); Writer1.WriteLine("SELECT ExecuteScript();");
Writer1.WriteLine();
Writer1.Close();
}

storePath = string.Empty;
FileName = string.Empty; ;
StoreName = string.Empty;
TempQuery = string.Empty;
Tempbatchno = string.Empty;
TempPrdID = string.Empty;
VendorId = string.Empty;
vendorName = string.Empty;
DatePath = string.Empty;
OfficeID = string.Empty;
CusID = string.Empty;
}

catch (Exception ex)
{
throw ex;
}
}

#region StockPoint To StockPoint
/// <summary>
/// This Method is used for Generating NextId of STP
/// </summary>
/// <param name="strtablename"></param>
/// <param name="strcolname"></param>
/// <param name="prefix"></param>
/// <param name="strdate"></param>
/// <returns></returns>
public string GenerateSTPId(string strtablename, string strcolname, int prefix, string strdate)
{
try
{
DateTime dt = Convert.ToDateTime(strdate);
string stryear = dt.Year.ToString();
string strFromdate = "01-APR-" + stryear;
if (Convert.ToDateTime(strdate) < Convert.ToDateTime(strFromdate))
{
strFromdate = Convert.ToDateTime(strFromdate).AddMonths(-12).ToString("dd-MMM-yyyy");
}
stryear = Convert.ToDateTime(strFromdate).Year.ToString();
string strToDate = Convert.ToDateTime(strFromdate).AddMonths(12).AddDays(-1).ToString("dd-MMM-yyyy");
string strId = SqlHelper.ExecuteScalar(SqlHelper.SQLConnString, CommandType.Text, "SELECT COALESCE(MAX(TO_NUMBER(SUBSTR(" + ManageQuote(strcolname) + "," + (prefix + 1) + " , LENGTH( " + ManageQuote(strcolname) + ")-(3+" + prefix + ")),'9999999'))+1,1) ||'/" + stryear.Substring(2) + "' FROM " + ManageQuote(strtablename) + " WHERE TRANSDATE BETWEEN '" + ManageQuote(strFromdate) + "' AND '" + ManageQuote(strToDate) + "'").ToString();
return strId;
}
catch
{
return null;
}
}
/// <summary>
/// This Method is used to Get the Product Names. Here passing the Branch Name is not required
/// </summary>
/// <param name="BranchName"></param>
/// <returns></returns>
public List<InventoryDTO.StockTransferIssueDTO> GetSTPProductName()
{
List<InventoryDTO.StockTransferIssueDTO> lstProdName = new List<InventoryDTO.StockTransferIssueDTO>();
try
{
NpgsqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.SQLConnString, CommandType.Text, "SELECT DISTINCT PRDNAME FROM TABPRODUCT WHERE STATUS='ACTIVE'");
while (rdr.Read())
{
InventoryDTO.StockTransferIssueDTO objProdName = new InventoryDTO.StockTransferIssueDTO();
objProdName.ProdName = rdr["PRDNAME"].ToString();
lstProdName.Add(objProdName);
}

rdr.Close();
return lstProdName;
}
catch
{
return null;
}
}

public bool saveStockPointToStockPoint(List<StockTransferIssueDTO> lstItems, out string strNextId)
{
strNextId = string.Empty;
string strddcid = string.Empty;

string strJVNo = string.Empty;
try
{
if (lstItems.Count > 0)
{
strddcid = "STP" + GenerateSTPId("TABSTOCKPOINT", "STP_NO", 2, lstItems[0].TranDate);

if (!string.IsNullOrEmpty(strddcid) && strddcid != null && strddcid.Length > 3)
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();

SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSTOCKPOINT(STP_NO ,VCHFROMOFFICE,VCHTOOFFICE,TRANSDATE ,TRANS_INVNO ,INV_TOT ,LOGINID,LOGINDATE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[0].FromGodown) + "','" + ManageQuote(lstItems[0].ToGodown) + "','" + ManageQuote(lstItems[0].TranDate) + "','" + ManageQuote(lstItems[0].ddc_id) + "',cast(" + lstItems[0].TotAmount + " as numeric),'" + ManageQuote(lstItems[0].LoginId) + "',CURRENT_TIMESTAMP)", null);

for (int k = 0; k < lstItems.Count; k++)
{
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO TABSTOCKPOINTDETAILS(STP_NO,VCHPRDNAME,NUMTRANSQTY,PRDAMT,LOGINID,LOGINDATE) VALUES ('" + ManageQuote(strddcid) + "','" + ManageQuote(lstItems[k].ProdName) + "',cast(" + lstItems[k].Quant + " as numeric),cast(" + lstItems[k].indTotAmount + " as numeric),'" + ManageQuote(lstItems[k].LoginId) + "',CURRENT_TIMESTAMP)", null);
}

strNextId = strddcid;
trans.Commit();
}
}

return true;

}
catch (NpgsqlException ex)
{
string except = ex.Message.ToString();
trans.Rollback();
return false;
}
finally
{
con.Close();
}
}

#endregion
}
}
---------------------------------------------------------------

My Helper Class

//===============================================================================
// This file is based on the Microsoft Data Access Application Block for .NET
// For more information please go to
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//===============================================================================

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using Npgsql;
using System.Text;
using System.ComponentModel;

namespace EasyAccounts.DAL
{
public abstract class SqlHelper
{

static string database = ConfigurationManager.AppSettings["Database"].ToString();
static string host = ConfigurationManager.AppSettings["Server"].ToString();
static string uname = ConfigurationManager.AppSettings["UserId"].ToString();
static string pwd = ConfigurationManager.AppSettings["Password"].ToString();
static string cmdtimeout = ConfigurationManager.AppSettings["cmdtimeout"].ToString();
static string timeout = ConfigurationManager.AppSettings["timeout"].ToString();
static string port = ConfigurationManager.AppSettings["port"].ToString();
//static string maxPoolSize = ConfigurationManager.AppSettings["MaxPoolSize"].ToString();

//static string database1 = ConfigurationManager.AppSettings["Database1"].ToString();
//static string host1 = ConfigurationManager.AppSettings["Server1"].ToString();
//static string uname1 = ConfigurationManager.AppSettings["UserId1"].ToString();
//static string pwd1 = ConfigurationManager.AppSettings["Password1"].ToString();

//Database connection strings
public static string SQLConnString = "UserId=" + uname + ";Password=" + pwd + ";Server=" + host + ";port=" + port + ";Database=" + database + ";CommandTimeout=" + cmdtimeout + ";Timeout=" + timeout + "";
// public static string conMpruser = "UserId=" + uname1 + ";Password=" + pwd1 + ";Server=" + host1 + ";Database=" + database1 + "";

// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(NpgsqlConnection connection, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">an existing sql transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(NpgsqlTransaction trans, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// NpgsqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>A NpgsqlDataReader containing the results</returns>
public static NpgsqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();
NpgsqlConnection conn = new NpgsqlConnection(connectionString);

// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}

/// <summary>
/// Create and prepare a NpgsqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection, on which to execute this command</param>
/// <param name="transaction">A valid NpgsqlTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of NpgsqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>NpgsqlDataReader containing the results of the command</returns>
public static NpgsqlDataReader ExecuteReader(NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
try
{
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Create a reader
NpgsqlDataReader dataReader;

dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

// Detach the NpgsqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (NpgsqlParameter commandParameter in cmd.Parameters)
{

if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}

if (canClear)
{
cmd.Parameters.Clear();
}

return dataReader;
}
catch
{
transaction.Connection.Close();
throw;
}
}

/// <summary>
/// Execute a NpgsqlCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{
NpgsqlCommand cmd = new NpgsqlCommand();

using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}

/// <summary>
/// Execute a NpgsqlCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(NpgsqlConnection connection, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters)
{

NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a 1x1 resultset) against the specified NpgsqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
//bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Execute the command & return the results
object retval = cmd.ExecuteScalar();

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

// Create & open a NpgsqlConnection, and dispose of it after we are done
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
connection.Open();

// Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the provided NpgsqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(connection, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the specified NpgsqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.CommandTimeout = 120;

PrepareCommand(cmd, connection, (NpgsqlTransaction)null, commandType, commandText, commandParameters);

// Create the DataAdapter & DataSet
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
da.FillSchema(ds, SchemaType.Source);

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();

//if (mustCloseConnection)
// connection.Close();

// Return the dataset
return ds;
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the specified NpgsqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset and takes no parameters) against the provided NpgsqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of NpgsqlParameters
return ExecuteDataset(transaction, commandType, commandText, (NpgsqlParameter[])null);
}

/// <summary>
/// Execute a NpgsqlCommand (that returns a resultset) against the specified NpgsqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new NpgsqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

// Create a command and prepare it for execution
NpgsqlCommand cmd = new NpgsqlCommand();

PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

// Create the DataAdapter & DataSet
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);

// Detach the NpgsqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();

// Return the dataset
return ds;
}
}

/// <summary>
/// Execute a stored procedure via a NpgsqlCommand (that returns a resultset) against the specified
/// NpgsqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">A valid NpgsqlTransaction</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(NpgsqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
NpgsqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);

// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

// Call the overload that takes an array of NpgsqlParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class SqlHelperParameterCache
{
#region private methods, variables, and constructors

//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelperParameterCache()"
private SqlHelperParameterCache() { }

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
/// Resolve at run time the appropriate set of NpgsqlParameters for a stored procedure
/// </summary>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
/// <returns>The parameter array discovered.</returns>
private static NpgsqlParameter[] DiscoverSpParameterSet(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

NpgsqlCommand cmd = new NpgsqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;

connection.Open();
NpgsqlCommandBuilder.DeriveParameters(cmd);
connection.Close();

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

NpgsqlParameter[] discoveredParameters = new NpgsqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

// Init the parameters with a DBNull value
foreach (NpgsqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}

/// <summary>
/// Deep copy of cached NpgsqlParameter array
/// </summary>
/// <param name="originalParameters"></param>
/// <returns></returns>
private static NpgsqlParameter[] CloneParameters(NpgsqlParameter[] originalParameters)
{
NpgsqlParameter[] clonedParameters = new NpgsqlParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (NpgsqlParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

#endregion private methods, variables, and constructors

#region caching functions

/// <summary>
/// Add parameter array to the cache
/// </summary>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters to be cached</param>
public static void CacheParameterSet(string connectionString, string commandText, params NpgsqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

string hashKey = connectionString + ":" + commandText;

paramCache[hashKey] = commandParameters;
}

/// <summary>
/// Retrieve a parameter array from the cache
/// </summary>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <returns>An array of SqlParamters</returns>
public static NpgsqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

string hashKey = connectionString + ":" + commandText;

NpgsqlParameter[] cachedParameters = paramCache[hashKey] as NpgsqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}

#endregion caching functions

#region Parameter Discovery Functions

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <returns>An array of NpgsqlParameters</returns>
public static NpgsqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">A valid connection string for a NpgsqlConnection</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
public static NpgsqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <returns>An array of NpgsqlParameters</returns>
internal static NpgsqlParameter[] GetSpParameterSet(NpgsqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
internal static NpgsqlParameter[] GetSpParameterSet(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
using (NpgsqlConnection clonedConnection = (NpgsqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
}
}

/// <summary>
/// Retrieves the set of NpgsqlParameters appropriate for the stored procedure
/// </summary>
/// <param name="connection">A valid NpgsqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of NpgsqlParameters</returns>
private static NpgsqlParameter[] GetSpParameterSetInternal(NpgsqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

NpgsqlParameter[] cachedParameters;

cachedParameters = paramCache[hashKey] as NpgsqlParameter[];
if (cachedParameters == null)
{
NpgsqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[hashKey] = spParameters;
cachedParameters = spParameters;
}

return CloneParameters(cachedParameters);
}

#endregion Parameter Discovery Functions

}

/// <summary>
/// This method assigns dataRow column values to an array of NpgsqlParameters
/// </summary>
/// <param name="commandParameters">Array of NpgsqlParameters to be assigned values</param>
/// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
// Do nothing if we get no data
return;
}

int i = 0;
// Set the parameters values
foreach (NpgsqlParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}

/// <summary>
/// This method assigns an array of values to an array of NpgsqlParameters
/// </summary>
/// <param name="commandParameters">Array of NpgsqlParameters to be assigned values</param>
/// <param name="parameterValues">Array of objects holding the values to be assigned</param>
private static void AssignParameterValues(NpgsqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}

// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

// Iterate through the NpgsqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// If the current array value derives from IDbDataParameter, then assign its Value property
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}

/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
public static void CacheParameters(string cacheKey, params NpgsqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}

/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached SqlParamters array</returns>
public static NpgsqlParameter[] GetCachedParameters(string cacheKey)
{
NpgsqlParameter[] cachedParms = (NpgsqlParameter[])parmCache[cacheKey];

if (cachedParms == null)
return null;

NpgsqlParameter[] clonedParms = new NpgsqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (NpgsqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;
}

/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">NpgsqlCommand object</param>
/// <param name="conn">NpgsqlConnection object</param>
/// <param name="trans">NpgsqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">NpgsqlParameters to use in the command</param>
//private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, CommandType cmdType, string cmdText, NpgsqlParameter[] cmdParms) {

// if (conn.State != ConnectionState.Open)
// conn.Open();

// cmd.Connection = conn;
// cmd.CommandText = cmdText;

// if (trans != null)
// cmd.Transaction = trans;

// cmd.CommandType = cmdType;

// if (cmdParms != null) {
// foreach (NpgsqlParameter parm in cmdParms)
// cmd.Parameters.Add(parm);
// }
//}

private static void PrepareCommand(NpgsqlCommand command, NpgsqlConnection connection, NpgsqlTransaction transaction, CommandType commandType, string commandText, NpgsqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{

connection.Open();
}
else
{

}

// Associate the connection with the command
command.Connection = connection;

// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;

// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}

// Set the command type
command.CommandType = commandType;

// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}

/// <summary>
/// This method is used to attach array of NpgsqlParameters to a NpgsqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">An array of NpgsqlParameters to be added to command</param>
private static void AttachParameters(NpgsqlCommand command, NpgsqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (NpgsqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
}
}

Dot Net

Tuesday, 20 February 2018

Saving Class