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); }