思路:field表在oracle中. 写一过程.根据表中的字段名和类型,动态建表,然后用C#调用过程,来返回一个值判断是否建表成功:field 表字段名 类型 长度
id number 8
name varchar2 30
...写过程:
create or replace procedure proc_name(p_out out number) as
v_field varchar2(20);
v_type varchar2(20);
v_len number(8);
....;
strsql varchar(800);
cursor c is select * from field;
begin
strsql:='create table table_name ( ';
open c;
loop
fetch c into v_field,v_type,v_len,........;
exit when c%notfound;
strsql:=strsql||v_field||' '||v_type||'('||v_len||')'||',';
--根据field表中参数的不同写不同的strsql;
end loop;
strsql:=substr(strsql,1,length(strsql)-1)||')' <---去掉最后一个逗号.加一个括号
execute immediate strsql ;
p_out:=0;
exception
when others then
p_out:=-1;
end;
id number 8
name varchar2 30
...写过程:
create or replace procedure proc_name(p_out out number) as
v_field varchar2(20);
v_type varchar2(20);
v_len number(8);
....;
strsql varchar(800);
cursor c is select * from field;
begin
strsql:='create table table_name ( ';
open c;
loop
fetch c into v_field,v_type,v_len,........;
exit when c%notfound;
strsql:=strsql||v_field||' '||v_type||'('||v_len||')'||',';
--根据field表中参数的不同写不同的strsql;
end loop;
strsql:=substr(strsql,1,length(strsql)-1)||')' <---去掉最后一个逗号.加一个括号
execute immediate strsql ;
p_out:=0;
exception
when others then
p_out:=-1;
end;
using System.OleDb;private OleDbCommand cmdOra=new OleDbCommand();#region 连接oracle数据库
public OleDbConnection ConnectDB()
{
OleDbConnection conn=new OleDbConnection();
conn.ConnectionString=connectora;
conn.Open();
return conn;
}
#endregion
public int Exec_AddOrder(string v_user_login_name,string v_item,string v_uom ,string v_qty,string v_need_by_date,string v_flag,string proc_name)
{
int i;
cmdOra.Parameters.Clear();
cmdOra.CommandText=proc_name;
cmdOra.CommandType=CommandType.StoredProcedure;
cmdOra.Connection=new clsPublic().ConnectDB(); cmdOra.Parameters.Add("p_user_login_name",OleDbType.VarChar);
cmdOra.Parameters.Add("p_item",OleDbType.VarChar);
cmdOra.Parameters.Add("p_uom",OleDbType.VarChar);
cmdOra.Parameters.Add("p_qty",OleDbType.VarChar);
cmdOra.Parameters.Add("p_need_by_date",OleDbType.VarChar);
cmdOra.Parameters.Add("p_flag",OleDbType.VarChar);
cmdOra.Parameters.Add("p_out",OleDbType.Integer); cmdOra.Parameters["p_user_login_name"].Value=v_user_login_name;
cmdOra.Parameters["p_item"].Value=v_item;
cmdOra.Parameters["p_uom"].Value=v_uom;
cmdOra.Parameters["p_qty"].Value=v_qty;
cmdOra.Parameters["p_need_by_date"].Value=v_need_by_date;
cmdOra.Parameters["p_flag"].Value=v_flag; cmdOra.Parameters["p_user_login_name"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_item"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_uom"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_qty"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_need_by_date"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_flag"].Direction=ParameterDirection.Input;
cmdOra.Parameters["p_out"].Direction=ParameterDirection.ReturnValue;
try
{
cmdOra.ExecuteNonQuery();
i=(int)cmdOra.Parameters["p_out"].Value;
}
catch
{
i=-8;
}
finally
{
if(cmdOra.Connection.State==ConnectionState.Open)
{
cmdOra.Connection.Close();
cmdOra.Connection.Dispose();
cmdOra.Parameters.Clear();
cmdOra.Dispose();
}
}
return i;
}
2. 有表之后,select,insert,update,delete... 都可以自由进行