| public CreateParameter ( ) : Npgsql.NpgsqlParameter | ||
| return | Npgsql.NpgsqlParameter | |
public int SetData(string procedureName, IList<SQLProcedureParameterVO> ParameterList)
{
int succCount = 0;
Exception ex = null;
bool querySucc = true;
//저장프로시저와 커맨드 객체 연결
NpgsqlCommand nCMD = new NpgsqlCommand(procedureName, this.NpgConn);
nCMD.CommandType = CommandType.StoredProcedure;
foreach (SQLProcedureParameterVO SQLProcedureParameterVO in ParameterList)
{
var parameter = nCMD.CreateParameter();
parameter.ParameterName = SQLProcedureParameterVO.parameterName;
parameter.DbType = SQLProcedureParameterVO.DBType;
parameter.Value = SQLProcedureParameterVO.value;
nCMD.Parameters.Add(parameter);
}
using (nCMD)
{
using (NpgsqlTransaction tran = this.NpgConn.BeginTransaction(IsolationLevel.ReadCommitted))
{
nCMD.Transaction = tran;
try
{
lock (SetDataThreadSafe)
{
succCount += nCMD.ExecuteNonQuery();
}
}
catch (Exception e)
{
querySucc = false;
ex = e;
}
if (querySucc)
{
tran.Commit();
return succCount;
}
else
{
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
}//End of using(tran)
}//End of Using(cmd)
}
public string UserInformation(string json)
{
string status = String.Empty;
try
{
using (NpgsqlConnection con = new NpgsqlConnection(_connectionString))
{
con.Open();
var command = new Npgsql.NpgsqlCommand("_bt_getuserdetailsbyid", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "input";
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json;
parameter.Value = json;
command.Parameters.Add(parameter);
NpgsqlDataReader reader = command.ExecuteReader();
string resultset = string.Empty;
while (reader.Read())
{
if (reader[0] != null)
{
resultset = reader[0].ToString();
}
}
status = "ok";
status = resultset;
}
}
catch (Exception ex)
{
status = ex.Message;
}
return(status);
}
public void SetParameterValueNull()
{
NpgsqlCommand cmd = new NpgsqlCommand("insert into tablef(field_bytea) values (:val)", TheConnection);
NpgsqlParameter param = cmd.CreateParameter();
param.ParameterName="val";
param.NpgsqlDbType = NpgsqlDbType.Bytea;
param.Value = DBNull.Value;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd = new NpgsqlCommand("select field_bytea from tablef where field_serial = (select max(field_serial) from tablef)", TheConnection);
Object result = cmd.ExecuteScalar();
Assert.AreEqual(DBNull.Value, result);
}
public void ParameterExplicitType2DbTypeObjectWithPrepare2()
{
new NpgsqlCommand("create temp table test ( tc date )", TheConnection).ExecuteNonQuery();
const string query = @"select * from test where tc=:param or tc=:param2";
NpgsqlCommand command = new NpgsqlCommand(query, TheConnection);
IDbDataParameter sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "2008-1-1";
sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param2";
sqlParam.Value = DateTime.Now;
sqlParam.DbType = DbType.Date;
command.Parameters.Add(sqlParam);
command.Prepare();
command.ExecuteScalar();
}
public void ParameterExplicitType2DbTypeObject()
{
const string query = @"create temp table test ( tc date ); select * from test where tc=:param";
NpgsqlCommand command = new NpgsqlCommand(query, TheConnection);
IDbDataParameter sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "2008-1-1";
sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
command.ExecuteScalar();
}
public DataTable GetData(string procedureName,IList<SQLProcedureParameterVO> ParameterList)
{
//저장프로시저와 커맨드 객체 연결
NpgsqlCommand nCMD = new NpgsqlCommand(procedureName, this.NpgConn);
nCMD.CommandType = CommandType.StoredProcedure;
foreach (SQLProcedureParameterVO SQLProcedureParameterVO in ParameterList)
{
var parameter = nCMD.CreateParameter();
parameter.ParameterName = SQLProcedureParameterVO.parameterName;
parameter.DbType = SQLProcedureParameterVO.DBType;
parameter.Value = SQLProcedureParameterVO.value;
nCMD.Parameters.Add(parameter);
}
//커맨드객체와 데이터어댑터 연결 및 데이터테이블 Get
DataTable dt = new DataTable();
try
{
using (NpgsqlDataAdapter nda = new NpgsqlDataAdapter(nCMD))
{
nda.Fill(dt);
}
}
catch (Exception)
{
throw;
}
return dt;
}
public void TestPreparedStatementParameterCastIsNotAdded()
{
// Test by Waldemar Bergstreiser
var cmd = new NpgsqlCommand("select field_int4 from data where :p0 is null or field_int4 = :p0 ", Conn);
var paramP0 = cmd.CreateParameter();
paramP0.ParameterName = "p0";
paramP0.DbType = DbType.Int32;
cmd.Parameters.Add(paramP0);
cmd.Prepare(); // This cause a runtime exception // Tested with PostgreSQL 8.3 //
}
public void ParameterExplicitType2DbTypeObjectWithPrepare2()
{
using (var command = new NpgsqlCommand(@"SELECT * FROM data WHERE field_date=:param or field_date=:param2", Conn))
{
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "2008-1-1";
sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param2";
sqlParam.Value = DateTime.Now;
sqlParam.DbType = DbType.Date;
command.Parameters.Add(sqlParam);
command.Prepare();
command.ExecuteScalar();
}
}
public void SetParameterValueNull()
{
var cmd = new NpgsqlCommand("insert into data(field_bytea) values (:val)", Conn);
var param = cmd.CreateParameter();
param.ParameterName = "val";
param.NpgsqlDbType = NpgsqlDbType.Bytea;
param.Value = DBNull.Value;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd = new NpgsqlCommand("select field_bytea from data where field_serial = (select max(field_serial) from data)", Conn);
var result = cmd.ExecuteScalar();
Assert.AreEqual(DBNull.Value, result);
}
public List<SolutionDatabase> getSolutions(int assignmentID)
{
conn.Open();
List<SolutionDatabase> solutions = new List<SolutionDatabase>();
NpgsqlCommand command = new NpgsqlCommand("getSolution", conn);
command.CommandType = CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "AssnId";
parameter.DbType = System.Data.DbType.Int32;
parameter.Value = assignmentID;
command.Parameters.Add(parameter);
NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
string check = dr[2].ToString();
Database d = JsonConvert.DeserializeObject<Database>(dr[2].ToString());
solutions.Add(new SolutionDatabase(d, (int)dr[1], (int)dr[0]));
}
conn.Close();
return solutions;
}
public int addSubmission(string json,int assnId, int studentId)
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("addSubmission", conn);
command.CommandType = CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "AssignmentId";
parameter.DbType = System.Data.DbType.Int32;
parameter.Value = assnId;
command.Parameters.Add(parameter);
var parameter2 = command.CreateParameter();
parameter2.ParameterName = "StudentId";
parameter2.DbType = System.Data.DbType.Int32;
parameter2.Value = studentId;
command.Parameters.Add(parameter2);
var parameter3 = command.CreateParameter();
parameter3.ParameterName = "JSonString";
parameter3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
parameter3.Value = json;
command.Parameters.Add(parameter3);
command.ExecuteNonQuery();
conn.Close();
return Grade(json, assnId);
}
public void addSolutions(string json, int assnId, int percent)
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("addSolution", conn);
command.CommandType = CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "percentage";
parameter.DbType = System.Data.DbType.Int32;
parameter.Value = percent;
command.Parameters.Add(parameter);
var parameter2 = command.CreateParameter();
parameter2.ParameterName = "jsonString";
parameter2.DbType = System.Data.DbType.AnsiString;
parameter2.Value = json;
command.Parameters.Add(parameter2);
var parameter3 = command.CreateParameter();
parameter3.ParameterName = "AssnId";
parameter3.DbType = System.Data.DbType.Int32;
parameter3.Value = assnId;
command.Parameters.Add(parameter3);
command.ExecuteNonQuery();
conn.Close();
}
public void TestTsQueryIsContainedIn()
{
var query = @"select 'cat'::tsquery <@ :param::tsquery";
using (var command = new NpgsqlCommand(query, Conn))
{
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "cat & rat";
//sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
var dr = command.ExecuteReader();
int i = 0;
var result = false;
while (dr.Read())
{
result = Convert.ToBoolean(dr[0]);
i++;
}
Assert.AreEqual(1, i);
Assert.IsTrue(result);
}
}
public void TestFullTextSearchOr()
{
BuildFullTextSearchTableTest();
var query = @"select *
from posts
where search_vector @@ to_tsquery('english', :param)
order by ts_rank_cd(search_vector, to_tsquery('english', :param)) desc";
using (var command = new NpgsqlCommand(query, Conn))
{
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "postgres | mysql";
//sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
var dr = command.ExecuteReader();
int i = 0;
while (dr.Read())
i++;
Assert.AreEqual(5, i);
}
}
public void TestPreparedStatementParameterCastIsNotAdded()
{
// Test by Waldemar Bergstreiser
new NpgsqlCommand("create table testpreparedstatementparametercast ( C1 int );", TheConnection).ExecuteNonQuery();
IDbCommand cmd = new NpgsqlCommand("select C1 from testpreparedstatementparametercast where :p0 is null or C1 = :p0 ", TheConnection);
IDbDataParameter paramP0 = cmd.CreateParameter();
paramP0.ParameterName = "p0";
paramP0.DbType = DbType.Int32;
cmd.Parameters.Add(paramP0);
cmd.Prepare(); // This cause a runtime exception // Tested with PostgreSQL 8.3 //
}
private void TestXmlParameter_Internal(bool prepare)
{
using (var conn = OpenConnection())
using (var command = new NpgsqlCommand("select @PrecisionXML", conn))
{
var sXML = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> <strings type=\"array\"> <string> this is a test with ' single quote </string></strings>";
var parameter = command.CreateParameter();
parameter.DbType = DbType.Xml; // To make it work we need to use DbType.String; and then CAST it in the sSQL: cast(@PrecisionXML as xml)
parameter.ParameterName = "@PrecisionXML";
parameter.Value = sXML;
command.Parameters.Add(parameter);
if (prepare)
command.Prepare();
command.ExecuteScalar();
}
}
public void addGrade(int index, SolutionDatabase s)
{
conn.Open();
NpgsqlCommand commandsubmission = new NpgsqlCommand("Select s.submissionid from submissions s order by s.submissionid desc limit 1 ",conn);
int submissionId = (int)commandsubmission.ExecuteScalar();
conn.Close();
conn.Open();
NpgsqlCommand command = new NpgsqlCommand("addGrade", conn);
command.CommandType = CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.ParameterName = "Graded_Against_SolutionId";
parameter.DbType = System.Data.DbType.Int32;
parameter.Value = s.solId;
command.Parameters.Add(parameter);
var parameter2 = command.CreateParameter();
parameter2.ParameterName = "SubmissionId";
parameter2.DbType = System.Data.DbType.Int32;
parameter2.Value = submissionId;
command.Parameters.Add(parameter2);
var parameter3 = command.CreateParameter();
parameter3.ParameterName = "Grade";
parameter3.DbType = System.Data.DbType.Int32;
parameter3.Value = s.grade;
command.Parameters.Add(parameter3);
var parameter4 = command.CreateParameter();
parameter4.ParameterName = "GradeComments";
parameter4.DbType = System.Data.DbType.AnsiString;
parameter4.Value = string.Empty;
command.Parameters.Add(parameter4);
command.ExecuteNonQuery();
conn.Close();
}
public void CharParameterValueSupport()
{
const String query = @"create temp table test ( tc char(1) );
insert into test values(' ');
select * from test where tc=:charparam";
var command = new NpgsqlCommand(query, Conn);
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "charparam";
// Exception Can't cast System.Char into any valid DbType.
sqlParam.Value = ' ';
command.Parameters.Add(sqlParam);
var res = (String) command.ExecuteScalar();
Assert.AreEqual(" ", res);
}
public void ParameterExplicitType2DbTypeObjectWithPrepare()
{
new NpgsqlCommand("create temp table test ( tc date )", TheConnection).ExecuteNonQuery();
var command = new NpgsqlCommand(@"select * from test where tc=:param", TheConnection);
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.Value = "2008-1-1";
sqlParam.DbType = DbType.Object;
command.Parameters.Add(sqlParam);
command.Prepare();
command.ExecuteScalar();
}
public void ParameterExplicitType2DbTypeObjectIntTypeFirst()
{
using (var command = new NpgsqlCommand(@"SELECT * FROM data WHERE field_int4=:param", Conn))
{
var sqlParam = command.CreateParameter();
sqlParam.ParameterName = "param";
sqlParam.DbType = DbType.Object;
sqlParam.Value = 1;
command.Parameters.Add(sqlParam);
command.ExecuteScalar();
}
}
/// <summary>
/// Adds the parameters to a PostgreSQL command.
/// </summary>
/// <param name="commandText">The PostgreSQL query to execute.</param>
/// <param name="parameters">Parameters to pass to the PostgreSQL query.</param>
private static void AddParameters(NpgsqlCommand command, Dictionary<string, object> parameters)
{
if (parameters == null)
{
return;
}
foreach (var param in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = param.Key;
parameter.Value = param.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
public void OutputParameterWithoutName()
{
ExecuteNonQuery(@"INSERT INTO data (field_int4) VALUES (4)");
ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION funcC() returns int8 as 'select count(*) from data;' language 'sql'");
var command = new NpgsqlCommand("funcC", Conn);
command.CommandType = CommandType.StoredProcedure;
var p = command.CreateParameter();
p.Direction = ParameterDirection.Output;
p.Value = -1;
command.Parameters.Add(p);
command.ExecuteNonQuery();
Assert.AreEqual(1, command.Parameters[0].Value);
}
public void OutputParameterWithoutName()
{
NpgsqlCommand command = new NpgsqlCommand("funcC", TheConnection);
command.CommandType = CommandType.StoredProcedure;
NpgsqlParameter p = command.CreateParameter();
p.Direction = ParameterDirection.Output;
p.Value = -1;
command.Parameters.Add(p);
command.ExecuteNonQuery();
Assert.AreEqual(6, command.Parameters[0].Value);
}