ASP.NET使用C#数据库类连接Oracle数据库(包括增删改查各种操作)

在写之前,感谢一下这篇文章,就是因为看了这篇文章是怎么连接mysql的才学会的怎么连Oracle数据库

https://blog.csdn.net/qq_35560429/article/details/80316664

 

建立一个网站项目(或者用已有的),在网站文件下建立一个文件夹,新建一个db.cs文件。

db.cs文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OracleClient;
using System.Data;

namespace WebApplication2.appcode
{
    public class db
    {
        public static OracleConnection CreatConnetion()
        {
          
                OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
                return conn;
         
           
        }
        public static DataTable select(String sql)
        {

            OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
            conn.Open();

            OracleCommand cmd = new OracleCommand(sql, conn);

            OracleDataAdapter oda = new OracleDataAdapter(cmd);

            DataTable dt = new DataTable();

            oda.Fill(dt);

            conn.Close();

            cmd.Dispose();
            return dt;

        }
        
        //下面这个函数的意思就是delete from table where atribute = value;
        //这里的tble、attribute和value是三个参数,自己看着输吧,注意这个value是字符串,在数据库里是varchar类型,如果删除int的话,自己改一下代码就好了!
        //算了,我自己改:1.把最后一个String value的String 改成int value
        //                2.把函数第三行的['" + value + "'"]改成[" + value]就欧克啦
        public static int delete(String table,String attribute,String value)
        {
            try
            {
                OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
                conn.Open();
                String sql = "delete from " + table + " where " + attribute + " ='" + value + "'";
                OracleCommand cmd = new OracleCommand(sql, conn);

                OracleDataAdapter oda = new OracleDataAdapter(cmd);

                DataTable dt = new DataTable();

                oda.Fill(dt);

                conn.Close();

                cmd.Dispose();
                return 0;//返回0就是删除成功!

            }
            catch (Exception ex)  
            {

                //返回1就是删除失败!
                return 1;
            }

            

        }
        //下面这个函数的意思就是update table set atribute_alter = new_value where atribute = value;
        //有点复杂哈,但是这样功能才齐全嘛。
        //具体的不多说啦,主要是修改varchar类型的,所以多用varchar,改成int参照delete函数好了。(其实可以写两个,一个改varchar,一个改int我赶脚数据库里这两种类型够用了
        public static int update(String table, String attribute_alter, String new_value,String attribute,String value)
        {
            try
            {
                OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
                conn.Open();
                String sql = "update " + table + " set " + attribute_alter + " ='" + new_value + "'where "+attribute+" = '"+value+"'";
                OracleCommand cmd = new OracleCommand(sql, conn);

                OracleDataAdapter oda = new OracleDataAdapter(cmd);

                DataTable dt = new DataTable();

                oda.Fill(dt);

                conn.Close();

                cmd.Dispose();
                return 0;//返回0就是修改成功!

            }
            catch (Exception ex)
            {

                //返回1就是修改失败!
                return 1;
            }



        }


        //由于插入比较复杂涉及到多个值甚至多个不同类型的值,这里写了改两列数据的,需要用时在这里修改
        //其实更建议直接用上面的select()因为这个小括号里可以直接输入语句,自然也可输入insert语句了比较灵活
        public static int insert(String table,String [] attributes,String []values )
        {

            OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
            conn.Open();
            String sql = "insert into " + table + " (" + attributes[0] + "," + attributes[1] + ") values(" + values[0] + "," + values[1] + ")";
            OracleCommand cmd = new OracleCommand(sql, conn);

            OracleDataAdapter oda = new OracleDataAdapter(cmd);

            DataTable dt = new DataTable();

            oda.Fill(dt);

            conn.Close();

            cmd.Dispose();
            return 0;

        }

        //为了灵活起见下面直接给一个输入语句的通用方法
        public static int sql(String sql)
        {

            OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=system;Password=a123456A");
            conn.Open();

            OracleCommand cmd = new OracleCommand(sql, conn);

            OracleDataAdapter oda = new OracleDataAdapter(cmd);

            DataTable dt = new DataTable();

            oda.Fill(dt);

            conn.Close();

            cmd.Dispose();
            return 0;

        }




    }
 
}

login.aspx文件

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="login.aspx.cs" Inherits="WebApplication2.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="用户名"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Label ID="Label2" runat="server" Text="密码"></asp:Label>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="登录" OnClick="Button1_Click" />
        <asp:Button ID="Button2" runat="server" Text="删除" OnClick="Button2_Click" />
        <asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="修改" />
        <asp:Button ID="Button4" runat="server" OnClick="Button4_Click" Text="增加" />
    </div>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1">
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="select" TypeName="WebApplication2.appcode.db">
            <SelectParameters>
                <asp:Parameter DefaultValue="select * from student" Name="sql" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>
    </form>
</body>
</html>

login.aspx.cs文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using WebApplication2.appcode;
using System.Data;

namespace WebApplication2
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                 db db = new db();
                 String sql = "select * from student where sno = '"+TextBox1.Text+"'";
                 DataTable dt = db.select(sql);
                 if (dt.Rows.Count == 1)
                 {
                     Response.Write(@"<script>alert('登陆成功');</script>");
                 }else if (dt.Rows.Count == 0)
                 {
                     Response.Write(@"<script>alert('登陆失败!你输入的用户名不存在!');</script>");
                 }
              GridView1.DataBind();
       

            }
            catch (Exception ex)
            {
                Response.Write(ex);
            }
            
          
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            try
            {
                db db = new db();
                int isDelete = db.delete("student","sno",TextBox1.Text);
                if ( isDelete == 1)
                {
                    Response.Write(@"<script>alert('删除失败');</script>");
                }
                else if (isDelete == 0)
                {
                    Response.Write(@"<script>alert('删除成功!');</script>");
                }
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex);
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            try
            {
                db db = new db();
                int isUpdate = db.update("student", "sno", TextBox2.Text,"sno",TextBox1.Text);
                if (isUpdate == 1)
                {
                    Response.Write(@"<script>alert('修改失败');</script>");
                }
                else if (isUpdate == 0)
                {
                    Response.Write(@"<script>alert('修改成功!');</script>");
                }
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex);
            }
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            try
            {
                db db = new db();
                String []attributes = {"sno","sname","tc"};
                String[] values = { TextBox1.Text, TextBox2.Text,"60" };
                int isInsert = db.insert("student", attributes,values);
                if (isInsert == 1)
                {
                    Response.Write(@"<script>alert('添加失败!');</script>");
                }
                else if (isInsert == 0)
                {
                    Response.Write(@"<script>alert('添加成功!');</script>");
                }
                GridView1.DataBind();

            }
            catch (Exception ex)
            {
                Response.Write(ex);
            }
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页