注:OracleConnection和OracleCommand已被标注为[弃用的],可以使用System.Data.OleDb.OleDbConnection代替OracleCOnnection,使用System.Data.OleDb.OleDbCommand代替OracleCommand,并在连接字符串中的ConnectionString属性里增加"Provider=OraOLEDB.Oracle;"。
首先是在Oracle数据库中创建表:
Create Table Student( S_ID VARCHAR2(40) default sys_guid() primary key, STUDENT_ID CHAR(12), STUDENT_Name VARCHAR2(20), STUDENT_AGE VARCHAR2(10), STUDENT_SEX VARCHAR2(10) )
接着在配置文件中加入Oracle连接字符串:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings>
<!--使用OleDbConnection时用该连接字符串-->
<!--<add name="DeviceDBConnection" connectionString="Provider=OraOLEDB.Oracle;User ID=JPVDS;Password=JPVDS_2015;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=16.130.2.14)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myorcl)))"
providerName="System.Data.OracleClient"/>--> <add name="DefaultDBConnection" connectionString="User ID=JPVDS;Password=JPVDS_2015;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=16.130.2.14)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myorcl)))" providerName="System.Data.OracleClient"/> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> </configuration>
然后是读取配置文件,连接数据库,进行增删改查操作,本人比较懒,直接贴代码了。
public class Program { //读取数据库连接字符串 string OracleStr = ConfigurationManager.ConnectionStrings["DefaultDBConnection"].ToString(); OracleConnection conn; /// <summary> /// 连接数据库 /// </summary> /// <param name="oracleStr">数据库连接字符串</param> /// <returns></returns> public OracleConnection ConnectionDB(string oracleStr) { OracleConnection conn = null; try { conn = new OracleConnection(OracleStr); //打开数据库 conn.Open(); } catch (Exception ex) { } return conn; } /// <summary> /// 插入操作 /// </summary> /// <param name="student">需要插入的对象</param> public void Insert(Student student) { string insertSqlStr = "insert into Student(Student_Id, Student_Name, Student_Age, Student_Sex)values(:Student_Id,:Student_Name,:Student_Age,:Student_Sex)"; try { OracleCommand cmd = new OracleCommand(insertSqlStr, conn); cmd.Parameters.AddWithValue("Student_Id", student.student_Id); cmd.Parameters.AddWithValue("Student_Name", student.student_Name); cmd.Parameters.AddWithValue("Student_Age", student.student_Age); cmd.Parameters.AddWithValue("Student_Sex", student.student_Sex); cmd.ExecuteNonQuery(); Console.WriteLine(string.Format("添加{0}成功!", student.student_Name)); } catch(Exception ex) { Console.WriteLine(string.Format("添加{0}失败!", student.student_Name)); } } /// <summary> /// 查询操作,查询全部内容 /// </summary> /// <returns>返回学生列表</returns> public List<Student> Query() { string querySqlStr = "select * from student"; List<Student> studentList = new List<Student>(); try { OracleCommand cmd = new OracleCommand(querySqlStr, conn); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { Student student = new Student(dr.GetValue(1).ToString(), dr.GetValue(2).ToString(), dr.GetValue(3).ToString(), dr.GetValue(4).ToString()); studentList.Add(student); } } } catch (Exception ex) { } return studentList; } /// <summary> /// 操作 /// </summary> public void operation() { conn = ConnectionDB(OracleStr); Student student1 = new Student("130202031004", "123", "22", "男"); Student student2 = new Student("130202031020", "香蕉", "24", "男"); Insert(student1); Insert(student2); List<Student> studentList = Query(); foreach(var student in studentList) { Console.WriteLine("~~~~~~~~~~~~~~~~"); Console.WriteLine(string.Format("学号:{0}",student.student_Id)); Console.WriteLine(string.Format("姓名:{0}",student.student_Name)); Console.WriteLine(string.Format("年龄:{0}",student.student_Age)); Console.WriteLine(string.Format("性别:{0}",student.student_Sex)); } conn.Close(); } static void Main(string[] args) { Program program = new Program(); program.operation(); } }
Student学生类
/// <summary> /// 学生类 /// </summary> public class Student { /// <summary> /// ID /// </summary> public string id { get; set; } /// <summary> /// 学生学号 /// </summary> public string student_Id { get; set; } /// <summary> /// 学生姓名 /// </summary> public string student_Name { get; set; } /// <summary> /// 学生年龄 /// </summary> public string student_Age { get; set; } /// <summary> /// 学生性别 /// </summary> public string student_Sex { get; set; } public Student() { } public Student(string s_Id,string s_Name,string s_Age,string s_Sex) { this.student_Id = s_Id; this.student_Name = s_Name; this.student_Age = s_Age; this.student_Sex = s_Sex; } }