准备工作
一、准备好需要连接的数据库
CREATE TABLE StuClass(
Class_Id int primary key not null,
Class_Name varchar2(50)
)
CREATE TABLE Students(
Stu_Id int primary key not null,
Stu_Name varchar(20),
--Stu_Sex varchar(4) check(Stu_Sex="男" or Stu_Sex="女"),
Stu_Sex varchar(4),
Stu_Birthday date null,
Class_Id numeric(10),
constraint fk_column foreign key(Class_Id) --外键
references StuClass(Class_Id)
on delete cascade
)
INSERT INTO StuClass (Class_Id,Class_Name) VALUES(101,'高一(1)班')
INSERT INTO StuClass (Class_Id,Class_Name) VALUES(102,'高一(2)班')
INSERT INTO Students (Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(1,'孙七','男',to_date ( '2008-07-02' , 'YYYY-MM-DD HH24:MI:SS'),102)
INSERT INTO Students (Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(2,'张三','女',TO_DATE('2019-07-02 15:31:34','YYYY-MM-DD HH24:MI:SS'),101)
INSERT INTO Students (Stu_Id,Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(3,'赵六','男',to_date ( '1998-08-03 15:31:34' , 'YYYY-MM-DD HH24:MI:SS'),102)
二、引用Oracle.ManagedDataAccess的dll包,这里使用NuGet安装
搜索Oracle.ManagedDataAccess,点击右侧得安装
下载完成之后看一下依赖中是否有dll
包安装完成之后咱们就可以来写代码啦
新建一个DataBases基础类,用来访问数据库
class DataBases
{
OracleConnection conn;
public DataBases()//初始化数据库连接字符串
{
//数据源字符串其中localhost是你oracle数据库的机器ip,port是你oracle数据库的端口号,IRON是你的用户名
string connString = (@"DATA SOURCE= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)));PASSWORD=IRON123;USER ID=IRON");
conn = new OracleConnection(connString);
}
public OracleDataReader getShow(string sql)
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rd;
}
/// <summary>
/// 查询操作 方法跟ADO.NET的操作基本一样
/// </summary>
/// <param name="sql">要执行的sql脚本</param>
/// <returns></returns>
public DataSet Queues(string sql)
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter dap = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
dap.Fill(ds);
dap.Dispose();
//int sdr = cmd.ExecuteNonQuery();
conn.Close();
return ds;
}
/// <summary>
/// 添加方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int Inserts(string sql)
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
}
基本类完成后咱们就可以通过这个类来访问到数据库啦,接着写个测试,操作一下数据库
class Program
{
static DataBases db = new DataBases();
static void Main(string[] args)
{
try
{
while (true)
{
Console.WriteLine("输入操作:1/添加,2/查询,c/重新输入,e/退出");
string caozuo = Console.ReadLine();
switch (caozuo)
{
case "1":
Console.WriteLine("添加,按c查重新选择操作");
string strAdd = Console.ReadLine();
if (strAdd=="c")
{
break;
}
else
{
Console.Write("编号:"); int Stu_Id = int.Parse(Console.ReadLine());
Console.Write("姓名:"); string Stu_Name = Console.ReadLine();
Console.Write("性别:"); string Stu_Sex = Console.ReadLine();
Console.Write("生日:"); DateTime Stu_Birthday = Convert.ToDateTime(Console.ReadLine());
Console.Write("班级编号:"); int Class_Id = int.Parse(Console.ReadLine());
string sqladd=string.Format(@"INSERT INTO Students (Stu_Id,Stu_Name,Stu_Sex,Stu_Birthday,Class_Id)
VALUES("+ Stu_Id + ",'"+ Stu_Name + "','"+ Stu_Sex + "',TO_DATE('" + Stu_Birthday + "','YYYY-MM-DD HH24:MI:SS')," + Class_Id + ")");
int i = db.Inserts(sqladd);
if (i > 0)
{
Console.WriteLine("添加成功!");
}
else
{
Console.WriteLine("添加失败!");
}
break;
}
case "2":
string sql = "";
Console.WriteLine("按1查询全部,按2地址查询,按c查重新选择操作");
string qu = Console.ReadLine();
if (qu == "1")
{
sql = string.Format("select * from Students");
}
else if (qu == "2")
{
Console.WriteLine("请输入地址:");
string str = Console.ReadLine();
sql = "select * from t_curriculum t join UserInfo u on t.stuid=u.userid where address like '%" + str + "%'";
}
else if (qu=="c")
{
break;
}
DataTable dt = db.Queues(sql).Tables[0];
foreach (DataRow item in dt.Rows)
{
string strResult = item["stu_id"] + "\t" + item["stu_name"] + "\t" + item["stu_sex"] + "\t" + item["stu_birthday"] + "\t" + item["class_id"];
Console.WriteLine(strResult);
}
break;
case "c":
Console.Clear();
continue;
case "e":
return;
default:
Console.WriteLine("请输入正确的操作!");
Console.Clear();
break;
}
}
}
catch (Exception e)
{
Console.WriteLine("错误:" + e.Message);
throw;
}
}
}