(oracle+vs2010+asp.net)创建table,package,数据操作

1.创建表Tfactory

CREATE TABLE EOLANDA.TFACTORY
(
  FACTORYID    NUMBER,
  FACTORYNO    VARCHAR2(100 BYTE)               NOT NULL,
  FACTORYNAME  VARCHAR2(100 BYTE)               NOT NULL,
  COMMENTS     VARCHAR2(200 BYTE),
  FLAG         NUMBER                           DEFAULT 1
)
2.添加Primary Key

ALTER TABLE EOLANDA.TFACTORY ADD (
  PRIMARY KEY
(FACTORYID));

使factoryid自动增长：
3.添加Sequence

CREATE SEQUENCE EOLANDA.SEQ_TFACTORY
  START WITH 1
  NOMAXVALUE

  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

4.添加trigger

CREATE OR REPLACE TRIGGER EOLANDA.tri_tfactory
before insert ON EOLANDA.TFACTORY for each row
begin
select seq_tfactory.nextval into :new.factoryid from dual;
end;

5.新增package spec

CREATE OR REPLACE package EOLANDA.pkg_factory is
type cur is ref cursor;
errorexception exception;
procedure InsertFactory(v_factoryno varchar2,
  v_factoryname varchar2,
  v_comments varchar2);
  end pkg_factory;
6.新增package body

CREATE OR REPLACE package body EOLANDA.pkg_factory is
procedure InsertFactory(v_factoryno varchar2,
  v_factoryname varchar2,
  v_comments varchar2)is
  begin
    insert into tfactory
    (factoryno,
    factoryname,
    comments)
    values
    (v_factoryno,
    v_factoryname,
    v_comments);
    commit;
     end  InsertFactory;
end pkg_factory;

vs2010创建web application

1.编辑web.config

添加ConnectionString

<configuration>
  <connectionStrings>    
    <add name="ConnectionString"
            connectionString="Data Source=orcl;User ID=eolanda;Password=123456;Unicode=True" />
  </connectionStrings>

</configuration>

2.创建Factory.aspx

 <table>
        <tr>
            <td>
                工厂编号：
            </td>
            <td>
                <asp:TextBox ID="txtFactoryNo" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                工厂名称：
            </td>
            <td>
                <asp:TextBox ID="txtFactoryName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                备注：
            </td>
            <td>
                <asp:TextBox ID="txtComments" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td colspan="2" style="text-align:right;">
                <asp:Button ID="btnSubmit" runat="server" Text="Button" 
                    onclick="btnSubmit_Click" />
            </td>
        </tr>
    </table>

3.InsertFactory()

 public int InsertFactory()
        {
            string ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
 
            OracleCommand cmd = new OracleCommand();
            OracleParameter[] ps = new OracleParameter[]
            {
                new OracleParameter("v_factoryno",OracleType.VarChar){Value=txtFactoryNo.Text},
                new OracleParameter("v_factoryname",OracleType.VarChar){Value=txtFactoryName.Text},
                new OracleParameter("v_comments",OracleType.VarChar){Value=txtComments.Text},
            };
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "pkg_factory.InsertFactory";
                foreach (OracleParameter parm in ps)
                {
                    cmd.Parameters.Add(parm);
                }
                int val = cmd.ExecuteNonQuery();
                conn.Close();
                cmd.Parameters.Clear();
                return val;
            }
        }

4.Click事件新增一条记录。