通過EFCore呼叫GBase8s資料庫儲存過程

前置閱讀文章

《EFCore使用ADO.NET連線GBase8s資料庫示例》

《C#連線GBase8s資料庫在windows環境下使用步驟》

建立解決方案Demo工程，並引入相應的EFCore包

 

我們首先建立名字為 test的資料庫，並建立使用到的資料表和儲存過程。

示例1： 有兩個入參和兩個返回值的儲存過程

CREATE   TABLE  test:company (

id INTEGER ,

branck VARCHAR ( 100 ),

address VARCHAR ( 100 ),

city VARCHAR ( 100 ),

phone VARCHAR ( 100 )

)

 

create   procedure  testselect (id int ,city varchar ( 100 )) returning   varchar ( 100 ), varchar ( 100 )

begin

define  v_in int ;

define  v_in2 VARCHAR ( 100 ) ;

     let  v_in = id+ 1 ;

     let  v_in2 = "Hello GBase8s" ;

insert   into  company values  (id, '1' , '2' ,city, '3' ) ;

return   'value: '  || v_in,v_in2 ;

end  

end   procedure ;

 

 

 

 

控制檯輸出結果，與預期一致，兩個返回值

 

資料也insert成功。

 

示例程式碼：

 

            GbsConnection conn = new GbsConnection(builder.ConnectionString);

            conn.Open();

            GbsCommand cmd = conn.CreateCommand();

            cmd.CommandText = "testselect";

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

 

            cmd.Parameters.Add(new GbsParameter("@id", SqlDbType.VarChar));

            cmd.Parameters["@id"].Value = 44;

            cmd.Parameters.Add(new GbsParameter("@city", SqlDbType.Int));

            cmd.Parameters["@city"].Value = "SH";

            cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;

 

            cmd.ExecuteNonQuery();

            GbsDataReader dr = (GbsDataReader)cmd.ExecuteReader();

            while (dr.Read())

            {

                for (int i = 0; i < dr.FieldCount; i++)

                {

                    Console.WriteLine(dr[i]);

                }

            }

 

示例2： 有一個出參的儲存過程。

create   procedure  call_out_param() returning   varchar ( 100 )

begin  

define  v_in int ;

define  v_rc varchar ( 100 ) ;

let  v_in=- 1 ;

call  out_param(p_out = v_in) returning  v_rc ;

let  v_rc = v_rc || ', value :'  || v_in ;

return  v_rc ;

end  

end   procedure ;

 

create   procedure  out_param ( out  p_out int ) returning   varchar ( 100 )

begin

  define  v_in int ;

  let  v_in = 1 ;

  let  p_out = v_in+ 1 ;

  return   'value:' || v_in ;

end

end   procedure ;

 

 

執行結果

 

與儲存過程執行結果一致。

 

示例程式碼

 GbsConnection conn = new GbsConnection(builder.ConnectionString);

            conn.Open();

            GbsCommand cmd = conn.CreateCommand();

            cmd.CommandText = "call_out_param";

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

 

            cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;

 

            cmd.ExecuteNonQuery();

            GbsDataReader dr = (GbsDataReader)cmd.ExecuteReader();

            while (dr.Read())

            {

                for (int i = 0; i < dr.FieldCount; i++)

                {

                    Console.WriteLine(dr[i]);

                }

            }

 

 

 

示例3： 有兩個出參的儲存過程。

create   procedure  call_p4() returning   varchar ( 20 ), varchar ( 20 )

begin

  define  v1 int ;

  define  v2 row (col1 int ,col2 varchar ( 20 )) ;

  define  rc varchar ( 20 ) ;

  let  v1 = 100 ;

let  v2 = row ( 200 , "testReturnValue" ) ;

call  p4(v1,v2) ;

return  v2.col1,v2.col2 ;

end

end   procedure ;

 

 

create   procedure  p4(v1 int , out  v2 row (col1 int , col2 varchar ( 20 )))

begin

let  v1 = 1111 ;

let  v2 = row ( 2222 , "P4_Return" ) ;

end

end   procedure ;

兩個出參的儲存過程，兩個出參使用的row方式。

 

 

 

能看出來，這是兩個返回結果值。儲存過程執行結果，也是兩個返回值。

 

示例程式碼

 GbsConnection conn = new GbsConnection(builder.ConnectionString);

            conn.Open();

            GbsCommand cmd = conn.CreateCommand();

            cmd.CommandText = "call_p4";

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

 

            cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;

 

            cmd.ExecuteNonQuery();

            GbsDataReader dr = (GbsDataReader)cmd.ExecuteReader();

            while (dr.Read())

            {

                for (int i = 0; i <= dr.FieldCount; i++)

                {

                    Console.WriteLine(dr[i]);

                }

            }