public List <string> ExecuteDataAdapter(string storeName, NpgsqlParameter[] sqlParams)
{
List <string> res = null;
try
{
using (NpgsqlTransaction t = connection.BeginTransaction())
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
DataSet result = new DataSet();
cmd.CommandText = DBConstant.BuildStoreName(storeName);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
da.Fill(result);
t.Commit();
cmd.Connection.Close();
if (result.Tables != null && result.Tables[0] != null && result.Tables[0].Rows != null && result.Tables[0].Rows.Count > 0)
{
res = new List <string>();
foreach (DataRow row in result.Tables[0].Rows)
{
res.Add(row[0].ToString());
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
public int ExecuteScalar(string storeName, NpgsqlParameter[] sqlParams)
{
int result = 0;
try
{
using (NpgsqlTransaction t = connection.BeginTransaction())
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
cmd.CommandText = DBConstant.BuildStoreName(storeName);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
result = (int)cmd.ExecuteScalar();
t.Commit();
return(result);
}
}
catch (NpgsqlException ex)
{
if (ex.Code == "23505")
{
return(-23505);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(result);
}
public List <T> ExecuteDataAdapter <T>(string storeName, NpgsqlParameter[] sqlParams) where T : new()
{
try
{
using (NpgsqlTransaction t = connection.BeginTransaction())
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
DataSet result = new DataSet();
cmd.CommandText = DBConstant.BuildStoreName(storeName);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
da.Fill(result);
t.Commit();
cmd.Connection.Close();
if (result.Tables != null && result.Tables[0] != null && result.Tables[0].Rows != null && result.Tables[0].Rows.Count > 0)
{
return(castObjectHelper.ConvertTo <T>(result.Tables[0]));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(null);
}
public bool ExcuteNoneQuery(string storeName, NpgsqlParameter[] sqlParams)
{
bool result = false;
try
{
using (NpgsqlTransaction t = connection.BeginTransaction())
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
cmd.CommandText = DBConstant.BuildStoreName(storeName);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Clear();
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
int i = cmd.ExecuteNonQuery();
result = i > 0;
t.Commit();
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(result);
}