下例代码示例中展示了如何进行查询、更新以及存储过程调用。
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 |
POLARDB集群的连接地址，如何查看连接地址请参见查看连接地址。
|
| Port |
1521 |
POLARDB集群的端口，默认为1521。 |
| User Id |
polaruser |
POLARDB集群的用户名。 |
| Password |
password |
POLARDB集群用户名对应的密码。 |
| Database |
sampledb |
需要连接的数据库名称。 |
评论