C# NpgsqlCommand.CreateParameter方法代码示例

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

        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 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 //
        }

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