次のサンプルコードは、ストアドプロシージャを照会、更新、および呼び出す方法を示しています。
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=1521;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();
}
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();
}
}
}
}
ここで、コード文字列 Server=localhost;Port=1521;User Id=polaruser;Password=password;Database=sampledb
はデータベースへの接続に使用される接続文字列です。
接続文字列は、
Server
、
Port
、
User Id
、
Password
、および
Database
など、次の表で説明するパラメーターから構成されます。
パラメーター |
例 |
説明 |
Server |
localhost |
ApsaraDB for POLARDB クラスターのエンドポイント。 エンドポイントをクエリする方法については、「エンドポイントの表示」をご参照ください。
|
Port |
1521 |
ApsaraDB for POLARDB クラスターのポート。 デフォルト値:1521。 |
User Id |
polaruser |
ApsaraDB for POLARDB クラスターに接続するためのユーザー名。 |
Password |
password |
ユーザー名のパスワード。 |
Database |
sampledb |
接続するデータベースの名前。 |