下列代码示例中展示了如何进行查询、更新以及存储过程调用。
using System;
using System.Data;
using PolarDB.PolarDBClient;
/*
* This class provides a simple way to perform DML operation in PolarDB
*
* @revision 1.0
*/
namespace PolarDBClientTest
{
class SAMPLE_TEST
{
static void Main(string[] args)
{
PolarDBConnection conn = new PolarDBConnection("Server=localhost;Port=5432;User Id=polaruser;Password=password;Database=sampledb");
try
{
conn.Open();
//Simple select statement using PolarDBCommand object
PolarDBCommand PolarDBSeletCommand = new PolarDBCommand("SELECT EMPNO,ENAME,JOB,MGR,HIREDATE FROM EMP",conn);
PolarDBDataReader SelectResult = PolarDBSeletCommand.ExecuteReader();
while (SelectResult.Read())
{
Console.WriteLine("Emp No" + " " + SelectResult.GetInt32(0));
Console.WriteLine("Emp Name" + " " + SelectResult.GetString(1));
if (SelectResult.IsDBNull(2) == false)
Console.WriteLine("Job" + " " + SelectResult.GetString(2));
else
Console.WriteLine("Job" + " null ");
if (SelectResult.IsDBNull(3) == false)
Console.WriteLine("Mgr" + " " + SelectResult.GetInt32(3));
else
Console.WriteLine("Mgr" + "null");
if (SelectResult.IsDBNull(4) == false)
Console.WriteLine("Hire Date" + " " + SelectResult.GetDateTime(4));
else
Console.WriteLine("Hire Date" + " null");
Console.WriteLine("---------------------------------");
}
//Insert statement using PolarDBCommand Object
SelectResult.Close();
PolarDBCommand PolarDBInsertCommand = new PolarDBCommand("INSERT INTO EMP(EMPNO,ENAME) VALUES((SELECT COUNT(EMPNO) FROM EMP),'JACKSON')",conn);
PolarDBInsertCommand.ExecuteScalar();
Console.WriteLine("Record inserted");
//Update using PolarDBCommand Object
PolarDBCommand PolarDBUpdateCommand = new PolarDBCommand("UPDATE EMP SET ENAME ='DOTNET' WHERE EMPNO < 100",conn);
PolarDBUpdateCommand.ExecuteNonQuery();
Console.WriteLine("Record has been updated");
PolarDBCommand PolarDBDeletCommand = new PolarDBCommand("DELETE FROM EMP WHERE EMPNO < 100",conn);
PolarDBDeletCommand.CommandType= CommandType.Text;
PolarDBDeletCommand.ExecuteScalar();
Console.WriteLine("Record deleted");
//procedure call example
try
{
PolarDBCommand callable_command = new PolarDBCommand("emp_query(:p_deptno,:p_empno,:p_ename,:p_job,:p_hiredate,:p_sal)", conn);
callable_command.CommandType = CommandType.StoredProcedure;
callable_command.Parameters.Add(new PolarDBParameter("p_deptno",PolarDBTypes.PolarDBDbType.Numeric,10,"p_deptno",ParameterDirection.Input,false ,2,2,System.Data.DataRowVersion.Current,20));
callable_command.Parameters.Add(new PolarDBParameter("p_empno", PolarDBTypes.PolarDBDbType.Numeric,10,"p_empno",ParameterDirection.InputOutput,false ,2,2,System.Data.DataRowVersion.Current,7369));
callable_command.Parameters.Add(new PolarDBParameter("p_ename", PolarDBTypes.PolarDBDbType.Varchar,10,"p_ename",ParameterDirection.InputOutput,false ,2,2,System.Data.DataRowVersion.Current,"SMITH"));
callable_command.Parameters.Add(new PolarDBParameter("p_job", PolarDBTypes.PolarDBDbType.Varchar,10,"p_job",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
callable_command.Parameters.Add(new PolarDBParameter("p_hiredate", PolarDBTypes.PolarDBDbType.Date,200,"p_hiredate",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
callable_command.Parameters.Add(new PolarDBParameter("p_sal", PolarDBTypes.PolarDBDbType.Numeric,200,"p_sal",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,null));
callable_command.Prepare();
callable_command.Parameters[0].Value = 20;
callable_command.Parameters[1].Value = 7369;
PolarDBDataReader result = callable_command.ExecuteReader();
int fc = result.FieldCount;
for(int i=0;i<fc;i++)
Console.WriteLine("RESULT["+i+"]="+ Convert.ToString(callable_command.Parameters[i].Value));
result.Close();
}
// 如果是.NET 1.1或者是.NET 2.0驱动,此处需要进行适当修改
catch(PolarDBException exp)
{
if(exp.ErrorCode.Equals("01403"))
Console.WriteLine("No data found");
else if(exp.ErrorCode.Equals("01422"))
Console.WriteLine("More than one rows were returned by the query");
else
Console.WriteLine("There was an error Calling the procedure. \nRoot Cause:\n");
Console.WriteLine(exp.Message.ToString());
}
//Prepared statement
string updateQuery = "update emp set ename = :Name where empno = :ID";
PolarDBCommand Prepared_command = new PolarDBCommand(updateQuery, conn);
Prepared_command.CommandType = CommandType.Text;
Prepared_command.Parameters.Add(new PolarDBParameter("ID", PolarDBTypes.PolarDBDbType.Integer));
Prepared_command.Parameters.Add(new PolarDBParameter("Name", PolarDBTypes.PolarDBDbType.Text));
Prepared_command.Prepare();
Prepared_command.Parameters[0].Value = 7369;
Prepared_command.Parameters[1].Value = "Mark";
Prepared_command.ExecuteNonQuery();
Console.WriteLine("Record Updated...");
}
catch(PolarDBException exp)
{
Console.WriteLine(exp.ToString() );
}
finally
{
conn.Close();
}
}
}
}