Oracle9 表结构:CREATE TABLE "NILE3"."PRODUCTS"
(
"BOOKID" NUMBER NOT NULL,
"SUBJECTID" NUMBER NOT NULL,
"BOOKTITLE" VARCHAR2(50 byte),
"AUTHOR" VARCHAR2(50 byte),
"PRICE" NUMBER(22, 7),
"RETAIL" NUMBER(22, 7),
"ISBN" VARCHAR2(50 byte),
"QUANTITYONHAND" NUMBER,
"SPECIALITEM" CHAR(1 byte) NOT NULL,
CONSTRAINT "ISBN_PRODUCTS" UNIQUE("ISBN")
);
存储过程的代码:create or replace procedure sp_products
(
v_bookid number,
v_booktitle varchar2,
v_price number)
as v_subjectid number;
v_author varchar2(50);
v_retail number;
v_isbn varchar2(50);
begin
v_subjectid := v_bookid + 1000;
v_author := 'Author1';
v_retail := v_price - 300;
v_isbn := 'ISBN-' || v_bookid;
insert into nile3.products
(
bookid,
subjectid,
booktitle,
author,
price,
retail,
isbn,
quantityonhand,
specialitem
)
values
(
v_bookid,
v_subjectid,
v_booktitle,
v_author,
v_price,
v_retail,
v_isbn,
100,
1
);
commit;end sp_products;程序代码:
try
{
OracleConnection cnn = new OracleConnection();
cnn.ConnectionString = "Persist Security Info=False;User ID=system;Password=manager;Data Source=ora9";
cnn.Open();
OracleCommand cmd = new OracleCommand();
OracleParameter[] para= new OracleParameter[3];
para[0] = new OracleParameter("v_bookid",OracleType.Number);
para[1] = new OracleParameter("v_booktitle",OracleType.VarChar,50);
para[2] = new OracleParameter("v_price",OracleType.Number); //赋值
para[0].Value = txtBookID.Text;
para[1].Value = "'" + txtBookTitle.Text + "'";
para[2].Value = txtPrice.Text; cmd.Parameters.Add(para[0]);
cmd.Parameters.Add(para[1]);
cmd.Parameters.Add(para[2]); //cmd.CommandText = "exec sys.sp_products (10012,'C# Test',50.00)";
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_products";
cmd.ExecuteNonQuery(); //this row is always wrong, why?
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}cmd.ExecuteNonQuery() 执行总出错,为什么???
(
"BOOKID" NUMBER NOT NULL,
"SUBJECTID" NUMBER NOT NULL,
"BOOKTITLE" VARCHAR2(50 byte),
"AUTHOR" VARCHAR2(50 byte),
"PRICE" NUMBER(22, 7),
"RETAIL" NUMBER(22, 7),
"ISBN" VARCHAR2(50 byte),
"QUANTITYONHAND" NUMBER,
"SPECIALITEM" CHAR(1 byte) NOT NULL,
CONSTRAINT "ISBN_PRODUCTS" UNIQUE("ISBN")
);
存储过程的代码:create or replace procedure sp_products
(
v_bookid number,
v_booktitle varchar2,
v_price number)
as v_subjectid number;
v_author varchar2(50);
v_retail number;
v_isbn varchar2(50);
begin
v_subjectid := v_bookid + 1000;
v_author := 'Author1';
v_retail := v_price - 300;
v_isbn := 'ISBN-' || v_bookid;
insert into nile3.products
(
bookid,
subjectid,
booktitle,
author,
price,
retail,
isbn,
quantityonhand,
specialitem
)
values
(
v_bookid,
v_subjectid,
v_booktitle,
v_author,
v_price,
v_retail,
v_isbn,
100,
1
);
commit;end sp_products;程序代码:
try
{
OracleConnection cnn = new OracleConnection();
cnn.ConnectionString = "Persist Security Info=False;User ID=system;Password=manager;Data Source=ora9";
cnn.Open();
OracleCommand cmd = new OracleCommand();
OracleParameter[] para= new OracleParameter[3];
para[0] = new OracleParameter("v_bookid",OracleType.Number);
para[1] = new OracleParameter("v_booktitle",OracleType.VarChar,50);
para[2] = new OracleParameter("v_price",OracleType.Number); //赋值
para[0].Value = txtBookID.Text;
para[1].Value = "'" + txtBookTitle.Text + "'";
para[2].Value = txtPrice.Text; cmd.Parameters.Add(para[0]);
cmd.Parameters.Add(para[1]);
cmd.Parameters.Add(para[2]); //cmd.CommandText = "exec sys.sp_products (10012,'C# Test',50.00)";
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_products";
cmd.ExecuteNonQuery(); //this row is always wrong, why?
}
catch(Exception exc)
{
MessageBox.Show(exc.Message);
}cmd.ExecuteNonQuery() 执行总出错,为什么???
{
OracleCommand command = new OracleCommand( "sp_products", cnn );
command .Parameters.Add(new OracleParameter("v_bookid",OracleType.Number)); command .Parameters.Add(new OracleParameter("v_booktitle",OracleType.VarChar,50));
command.Parameters.Add( new OracleParameter("v_price",OracleType.Number));
command.CommandType = CommandType.StoredProcedure;
return command;
}
{
OracleConnection cnn = new OracleConnection();
cnn.ConnectionString = "Persist Security Info=False;User ID=system;Password=manager;Data Source=ora9";
cnn.Open();
OracleDataAdaper dscommand = new OracleDataAdapter();
dscommand.InsertCommand = this.Command();
dscommand.InsertCommand.Parameter["v_bookid"].value = i;
dscommand.InsertCommand.Parameter["v_booktitle"].value = j;
dscommand.InsertCommand.Parameter["v_price"].value = m;
dscommand.update( ...);}
maotin(liu):我按照你的代码试了,但是:return cmd 有错误OracleCommand cmd = new OracleCommand( "sp_products", cnn );
cmd.Parameters.Add(new OracleParameter("v_bookid",OracleType.Number));
cmd.Parameters.Add(new OracleParameter("v_booktitle",OracleType.VarChar,50));
cmd.Parameters.Add( new OracleParameter("v_price",OracleType.Number));//我自己的赋值
cmd.Parameters["v_bookid"].Value = txtBookID.Text;
cmd.Parameters["v_booktitle"].Value = "'" + txtBookTitle.Text + "'";
cmd.Parameters["v_price"].Value = txtPrice.Text;cmd.CommandType = CommandType.StoredProcedure;
return cmd;
maotin(liu):另外,那个update(...), ...应该写什么?
maotin(liu)错误是:
E:\Work\TestOraclePro\frmMain.cs(220): 由于“TestOraclePro.frmMain.btnInsert_Click(object, System.EventArgs)”返回 void,返回关键字后面不得有对象表达式
private OracleCommnad Command()
{
if( command == null )
{
command = new OracleCommand( "sp_products", cnn );
command .Parameters.Add(new OracleParameter("v_bookid",OracleType.Number)); command .Parameters.Add(new OracleParameter("v_booktitle",OracleType.VarChar,50));
command.Parameters.Add( new OracleParameter("v_price",OracleType.Number));
command.CommandType = CommandType.StoredProcedure;
}
return command;
}