//带参数的插入1 public static int test_insertCmdwithParams() { XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; string sql = "insert into tp(pid,pname) values(?,?)"; cmd.CommandText = sql; XGTransaction trans = conn.BeginTransaction(); //为连接创建显示事务,此时该连接事务处于非自动提交状态 cmd.Transaction = trans; //指定申明命令事务环境 //参数赋值 cmd.Parameters.Add("pid", XGDbType.Int).Value = 2; cmd.Parameters.Add("pname", XGDbType.VarChar).Value = "开发部"; cmd.ExecuteNonQuery(); //重复执行参数赋值前,需清除前次参数 cmd.Parameters.Clear(); //采用AddWithValue方法 object t_int = 3; object t_str = "测试部"; cmd.Parameters.AddWithValue("pid", t_int); cmd.Parameters.AddWithValue("pname", t_str); //采用AddRange方法 cmd.Parameters.Clear(); XGParameters[] t_Arr = new XGParameters[2]; t_Arr[0] = new XGParameters("pid", XGDbType.Int); t_Arr[0].Value = 4; t_Arr[1] = new XGParameters("pname", XGDbType.VarChar); t_Arr[1].Value = "产品部"; cmd.Parameters.AddRange(t_Arr); cmd.ExecuteNonQuery(); trans.Commit();//提交命令事务 //free cmd and disconnect cmd.Dispose(); conn.Close(); return(0); } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); conn.Close(); return(-1); } }
//存储过程/函数执行后提取其结果集 public static int test_execRefCursor() { XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; XGDataReader t_Reader = new XGDataReader(); XGParameters t_Param = new XGParameters("ARG", XGDbType.RefCursor); XGRefCursor t_Cur = new XGRefCursor(); t_Param.Direction = System.Data.ParameterDirection.Output; string results = null; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; //创建存储过程及相关表 cmd.CommandText = "SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME='T_REFCURSOR';"; if (Convert.ToInt16(cmd.ExecuteScalar()) != 0) { cmd.CommandText = "DROP TABLE T_REFCURSOR CASCADE ;"; cmd.ExecuteNonQuery(); } cmd.CommandText = "CREATE TABLE T_REFCURSOR(COL INTEGER,COL2 VARCHAR);"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO T_REFCURSOR VALUES(1,'xugu');"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE OR REPLACE PROCEDURE T_PROC_REF(ARG OUT SYS_REFCURSOR)AS BEGIN OPEN ARG FOR SELECT * FROM T_REFCURSOR; END;";//OUT TYPE cmd.ExecuteNonQuery(); //执行存储过程 cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "T_PROC_REF"; cmd.Parameters.Clear(); cmd.Parameters.Add(t_Param); cmd.ExecuteNonQuery(); t_Cur = ((XGRefCursor)t_Param.Value); t_Reader = t_Cur.GetDataReader(); if (t_Reader.Read()) { string val = "RefCursor结果集展示: \n"; val += Convert.ToInt32(t_Reader.GetValue(0)).ToString() + '|'; val += Convert.ToString(t_Reader.GetValue(1)); Console.WriteLine(val); } } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); conn.Close(); return(-1); } return(0); }
//函数执行 public static int test_execprocedure_fun() { XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; cmd.CommandText = "select count(*) from user_tables where table_name='T_PACK_FUNC1'";//T_pack_func1 if (Convert.ToInt32(cmd.ExecuteScalar()) == 1) { cmd.CommandText = "drop table T_PACK_FUNC1 cascade"; cmd.ExecuteScalar(); } cmd.CommandText = "create table T_pack_func1(c1 int ,c2 double,c3 datetime,c4 numeric(32,8),c5 varchar)"; cmd.ExecuteNonQuery(); cmd.CommandText = " insert into T_pack_func1 values(1,null,'2017-07-10 15:01:35',33493.23423,'this is the func1 values 1')"; cmd.ExecuteNonQuery(); cmd.CommandText = " insert into T_pack_func1 values( 2,23423.23,null,98763.2333,'here is the func1 the No2 value and so we need ')"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_func1 values( 3,972.332,'2017-07-09 19:45:22',null,'so we get the third 3 value')"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_func1 values( 4, 243.2342,'2017-07-04 20:35:18',3454.32,null)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_func1 values( 5,843.23,'2008-09-01 12:25:38',205.23,'')"; cmd.ExecuteNonQuery(); // cmd.CommandText = "select count(*) from user_tables where table_name='T_PACK_PAN1'";//T_pack_pan1 if (Convert.ToInt32(cmd.ExecuteScalar()) == 1) { cmd.CommandText = "drop table T_PACK_PAN1 cascade"; cmd.ExecuteScalar(); } cmd.CommandText = "create table T_pack_pan1(c1 bigint,c2 char(50),c3 date,c4 int, c5 float)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_pan1 values(123,'sdishosho hereess ','2017-07-01',null,324.56)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_pan1 values(456,'some get out the sdishosho hereess ',null,234,45.324) "; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_pan1 values(789,null,'2017-07-03',34,23445.23) "; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into T_pack_pan1 values(678,'the 4fourth in ','2017-07-04',44,null)"; cmd.ExecuteNonQuery(); // HEAD cmd.CommandText = "select count(*) from user_PACKAGES where PACK_name='PACK_NAME1'";//PACK_NAME1 if (Convert.ToInt32(cmd.ExecuteScalar()) == 1) { // cmd.CommandText = "drop table T_PACK_PAN1 cascade"; // cmd.ExecuteScalar(); cmd.CommandText = "alter package pack_name1 recompile"; cmd.ExecuteScalar(); } else { string sql_pack_head = "create or replace package pack_name1 is "; sql_pack_head += " function pa_func1(aa in int,"; sql_pack_head += " bb in out double,"; sql_pack_head += " cc out datetime,"; sql_pack_head += " dd out numeric,"; sql_pack_head += " ee out varchar)"; sql_pack_head += " return datetime;"; sql_pack_head += " procedure proc_pan1"; sql_pack_head += "(aa in bigint,bb in out char(50), cc out date, dd out int, ee out float);"; sql_pack_head += " end; "; cmd.CommandText = sql_pack_head; cmd.ExecuteNonQuery(); // cmd.ExecuteScalar(); //body string sql_pack_body = "create or replace package body pack_name1 is"; sql_pack_body += " function pa_func1("; sql_pack_body += " aa in int, bb in out double, cc out datetime, dd out numeric, ee out varchar )"; sql_pack_body += " return datetime as TMP_DT DATETIME; begin"; sql_pack_body += " select c2 ,c3,c4,c5 into bb,cc,dd,ee from T_pack_func1 where c1=aa;"; sql_pack_body += " TMP_DT:=cc;"; //sql_pack_body += ""; sql_pack_body += " return TMP_DT;end;"; sql_pack_body += " procedure proc_pan1("; sql_pack_body += " aa in bigint, bb in out char(50), cc out date, dd out int, ee out float)"; sql_pack_body += " as begin "; sql_pack_body += " select c2 ,c3,c4,c5 into bb,cc,dd,ee from T_pack_pan1 where c1=aa;"; sql_pack_body += " end; "; sql_pack_body += " end; "; cmd.CommandText = sql_pack_body; cmd.ExecuteNonQuery(); } //==func ==================== cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "PACK_NAME1.PA_FUNC1"; XGParameters aa = new XGParameters("AA", XGDbType.Int); aa.Direction = ParameterDirection.Input; aa.Value = 3;//2 3 4 5 cmd.Parameters.Add(aa); XGParameters bb = new XGParameters("BB", XGDbType.Double); bb.Direction = ParameterDirection.InputOutput; bb.Value = 1.0; cmd.Parameters.Add(bb); XGParameters cc = new XGParameters("CC", XGDbType.DateTime); cc.Direction = ParameterDirection.Output; cmd.Parameters.Add(cc); XGParameters dd = new XGParameters("DD", XGDbType.Numeric); dd.Direction = ParameterDirection.Output; cmd.Parameters.Add(dd); XGParameters ee = new XGParameters("ee", XGDbType.VarChar, 200); ee.Direction = ParameterDirection.Output; cmd.Parameters.Add(ee); XGParameters ff = new XGParameters("ff", XGDbType.DateTime); ff.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(ff); // fun ================ cmd.ExecuteNonQuery(); Console.WriteLine("存储函数参数和返回值输出:"); Console.WriteLine("aa=" + aa.Value.ToString()); Console.WriteLine("bb=" + bb.Value.ToString()); Console.WriteLine("cc=" + cc.Value.ToString()); Console.WriteLine("dd=" + dd.Value.ToString()); Console.WriteLine("ee=" + ee.Value.ToString()); Console.WriteLine("ff=" + ff.Value.ToString()); //===================================================== //free cmd and disconnect cmd.Dispose(); conn.Close(); return(0); } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); conn.Close(); return(-1); } }
//带输出型参数的存储过程执行 public static int test_execprocedure_OutParam() { XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; cmd.CommandText = "select count(*) from user_tables where table_name='TEST_CSH_PROC'"; if (Convert.ToInt32(cmd.ExecuteScalar()) == 1) { cmd.CommandText = "drop table TEST_CSH_PROC cascade"; cmd.ExecuteScalar(); } cmd.CommandText = "CREATE TABLE TEST_CSH_PROC(ID INT,SS VARCHAR(20),KK DATE,SP NUMERIC(12,5),PP FLOAT,TT FLOAT)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),321.34,123.123,321.123)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),111.23,231.12,322)"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),234.123,986.234,9322)"; cmd.ExecuteNonQuery(); string sql_str = "CREATE OR REPLACE PROCEDURE P1(IN_ID IN OUT INT,OSS OUT VARCHAR,OKK OUT DATE,OSP OUT NUMERIC,OPP OUT FLOAT,OTT OUT FLOAT)"; sql_str += "AS TEMP_ID INT; BEGIN"; sql_str += " select count(*),ss,kk,sum(sp),sum(pp),sum(tt) into temp_id,oss,okk,osp,opp,ott from TEST_CSH_PROC where id=IN_ID group by ss,kk;"; sql_str += " IN_ID:=TEMP_ID; end;"; cmd.CommandText = sql_str; cmd.ExecuteNonQuery(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "P1"; XGParameters par_id = new XGParameters("IN_ID", XGDbType.Int); par_id.Direction = ParameterDirection.InputOutput; par_id.Value = 1; cmd.Parameters.Add(par_id); XGParameters par_oss = new XGParameters("OSS", XGDbType.VarChar, 500); par_oss.Direction = ParameterDirection.Output; cmd.Parameters.Add(par_oss); XGParameters par_okk = new XGParameters("OKK", XGDbType.DateTime); par_okk.Direction = ParameterDirection.Output; cmd.Parameters.Add(par_okk); XGParameters par_osp = new XGParameters("OSP", XGDbType.Numeric); par_osp.Direction = ParameterDirection.Output; cmd.Parameters.Add(par_osp); XGParameters par_opp = new XGParameters("OPP", XGDbType.Double); par_opp.Direction = ParameterDirection.Output; cmd.Parameters.Add(par_opp); XGParameters par_ott = new XGParameters("OTT", XGDbType.Double); par_ott.Direction = ParameterDirection.Output; cmd.Parameters.Add(par_ott); cmd.ExecuteNonQuery(); Console.WriteLine("输出型参数返回值:"); Console.WriteLine("in_id=" + par_id.Value.ToString()); Console.WriteLine("oss=" + par_oss.Value.ToString()); Console.WriteLine("okk=" + par_okk.Value.ToString()); Console.WriteLine("osp=" + par_osp.Value.ToString()); Console.WriteLine("opp=" + par_opp.Value.ToString()); Console.WriteLine("ott=" + par_ott.Value.ToString()); cmd.Dispose(); conn.Dispose(); return(0); } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); conn.Close(); return(-1); } }
//带参数的插入2 public static int test_insertCmdwithParams2() { XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; string sql = "insert into ta(pid,name) values(?,?)"; cmd.CommandText = sql; XGParameters pidParam = new XGParameters("pid", XGDbType.Int); pidParam.Direction = ParameterDirection.Input; pidParam.Value = 2; cmd.Parameters.Add(pidParam); string c1string = "李四"; XGParameters nammeParam = new XGParameters("name", XGDbType.VarChar); nammeParam.Direction = ParameterDirection.Input; nammeParam.Value = c1string; cmd.Parameters.Add(nammeParam); cmd.ExecuteNonQuery(); //insert方法插入参数 cmd.Parameters.Clear(); cmd.Parameters.Insert(0, 2); //根据索引位置插入参数 cmd.Parameters.Insert(1, "王五"); cmd.ExecuteNonQuery(); //重复执行参数赋值前,需清除前次参数 cmd.Parameters.Clear(); cmd.Parameters.Add("pid", XGDbType.Int).Value = 3; cmd.Parameters.Add("name", XGDbType.VarChar).Value = "赵七"; cmd.Parameters.Add("remove", XGDbType.VarChar).Value = "待删除"; //使用Remove方法删除指定位置的参数 cmd.Parameters.Remove(2); //cmd.Parameters.Remove("remove"); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Parameters.Add("pid", XGDbType.Int).Value = 3; cmd.Parameters.Add("name", XGDbType.VarChar).Value = "钱八"; cmd.ExecuteNonQuery(); //free cmd and disconnect cmd.Dispose(); conn.Close(); return(0); } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); conn.Close(); return(-1); } }
public static int test_Cmd5() { string sql_str = "CREATE TABLE TEST_DT(DT1 DATETIME,DT2 DATETIME,DT3 DATETIME)"; XGConnection conn = new XGConnection(); conn.ConnectionString = conn_xg; try { conn.Open(); XGCommand cmd = new XGCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME='TEST_DT'"; if (Convert.ToInt32(cmd.ExecuteScalar()) > 0) { cmd.CommandText = "DROP TABLE TEST_DT"; cmd.ExecuteNonQuery(); } cmd.CommandText = sql_str; cmd.ExecuteNonQuery();//创建测试基础表结构 cmd.CommandText = "INSERT INTO TEST_DT VALUES(?,?,?)"; DateTime[] dt1 = new DateTime[array_num]; DateTime[] dt2 = new DateTime[array_num]; DateTime[] dt3 = new DateTime[array_num]; dt1 = ret_dt_val(array_num); dt2 = ret_dt_val(array_num); dt3 = ret_dt_val(array_num); object[] objs = new object[3]; objs[0] = dt1; objs[1] = dt2; objs[2] = dt3; //非批处理,时间类型传入参数为时间类型 DateTime bt = DateTime.Now; for (int rec = 0; rec < rec_num; rec++) { cmd.Parameters.Clear(); cmd.Parameters.Add("?", XGDbType.DateTime).Value = ((DateTime[])objs[0])[rec]; cmd.Parameters.Add("?", XGDbType.DateTime).Value = ((DateTime[])objs[1])[rec]; cmd.Parameters.Add("?", XGDbType.DateTime).Value = ((DateTime[])objs[2])[rec]; cmd.ExecuteNonQuery(); } DateTime ed = DateTime.Now; TimeSpan ts = ed - bt; Console.WriteLine("非批处理插入3列datetime类型表传入时间类型参数" + rec_num + "条记录耗时:" + ts.ToString()); string[] dt_str1 = new string[array_num]; string[] dt_str2 = new string[array_num]; string[] dt_str3 = new string[array_num]; dt_str1 = ret_dt_str(array_num); dt_str2 = ret_dt_str(array_num); dt_str3 = ret_dt_str(array_num); object[] objs2 = new object[3]; objs2[0] = dt_str1; objs2[1] = dt_str2; objs2[2] = dt_str3; bt = DateTime.Now; for (int rec = 0; rec < rec_num; rec++) { cmd.Parameters.Clear(); cmd.Parameters.Add("?", XGDbType.VarChar).Value = ((string[])objs2[0])[rec]; cmd.Parameters.Add("?", XGDbType.VarChar).Value = ((string[])objs2[1])[rec]; cmd.Parameters.Add("?", XGDbType.VarChar).Value = ((string[])objs2[2])[rec]; cmd.ExecuteNonQuery(); } ed = DateTime.Now; ts = ed - bt; Console.WriteLine("非批处理插入3列datetime类型表传入字符串类型参数" + rec_num + "条记录耗时:" + ts.ToString()); cmd.Parameters.Clear(); cmd.CommandText = "SELECT COUNT(*) FROM TEST_DT WHERE 1=1"; if (Convert.ToInt32(cmd.ExecuteScalar()) != rec_num * 2) { Console.WriteLine("插入时间类型测试后,数据条数异常"); } //批处理 cmd.CommandText = "INSERT INTO TEST_DT VALUES(?,?,?)"; cmd.ArrayBindCount = rec_num; for (int col_no = 0; col_no < 3; col_no++) { XGParameters par = new XGParameters("?", XGDbType.VarChar); par.Direction = ParameterDirection.Input; par.Value = objs2[col_no]; cmd.Parameters.Add(par); } bt = DateTime.Now; cmd.ExecuteNonQuery(); ed = DateTime.Now; ts = ed - bt; Console.WriteLine("批处理插入3列datetime类型表传入字符串类型参数" + rec_num + "条记录耗时:" + ts.ToString()); cmd.Parameters.Clear(); cmd.ArrayBindCount = rec_num; for (int col_no = 0; col_no < 3; col_no++) { XGParameters par = new XGParameters("?", XGDbType.DateTime); par.Direction = ParameterDirection.Input; par.Value = objs[col_no]; cmd.Parameters.Add(par); } bt = DateTime.Now; cmd.ExecuteNonQuery(); ed = DateTime.Now; ts = ed - bt; Console.WriteLine("批处理插入3列datetime类型表传入时间类型参数" + rec_num + "条记录耗时:" + ts.ToString()); cmd.Dispose(); } catch (Exception Err) { Console.WriteLine("Error :"); Console.WriteLine("\t{0}", Err.ToString()); return(-1); } finally { conn.Close(); Console.WriteLine("测试 关闭连接后 连接当前状态" + conn.State.ToString()); } return(0); }