.net连接oracle有三种方式,我采用的是使用微软自带的system.data.oracleclient
电脑上装了oracle11g client(x64)、和pl/sql,在安装的过程中也遇到了许多问题:
1、oracle安装,环境不满足最低要求
百度然后修改了两个.ora文件就好了,加上了win8的坏境
2、oracle安装,path过长无法自动配置环境变量
于是在path中删掉了一些没用的东西(比如已经卸载的软件的配置)
然后重启电脑!!!就好了,一定要重启!!!
3、pl/sql一直无法登陆
发现pl/sql装的是32位的,但是也有说不一定要统一,但我确实遇到了这个问题(大概是我菜)
然后就是开始连接数据库啦!
1、在引用中添加system.data.oracleclient;(直接右键添加引用就可以找到)
2、写上using System.Data.OracleClient;
public void ConnectDB()
{
OracleConnection conn = new OracleConnection("data source=**服务命名**;User Id=**用户名**;Password=**密码**;");
try
{
conn.Open();
string sql = "select * from atest_patient_info";
OracleDataAdapter res = new OracleDataAdapter(sql, conn);
DataTable dt = new DataTable();
res.Fill(dt);
string a = null;
for (int x = 0; x < dt.Rows.Count; x++)
{
a += dt.Rows[x][1].ToString();
a += "\n";
}
MessageBox.Show(a);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conn.Close();
}
}
然后是用于查找和删、改、增的函数(删、改、查用的是同一个函数)
private OracleConnection conn = new OracleConnection("data source=**服务命名**;User Id=**用户名**;Password=**密码**;");
public DataTable getPatientInfo(string sql)//查找
{
DataTable table = new DataTable();
try
{
conn.Open();
OracleDataAdapter res = new OracleDataAdapter(sql, conn);
res.Fill(table);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
conn.Close();
}
return table;
}
public bool updataTables(string sql1, string sql2)//增、改、删
{
try
{
conn.Open();
OracleCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = sql1;
cmd1.ExecuteNonQuery();
OracleCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = sql2;
cmd2.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
finally
{
conn.Close();
}
return true;
}
然后是调用函数的地方啦!
1、增加
public void NewPatient()
{
string[] a = { IDText.Text, NameText.Text, SexBox.Text, AgeText.Text, NativeText.Text, ComplainsText.Text,
PreComplaintText.Text, PreIllnessText.Text, PersonalHisText.Text, AdmittedTimeText.Text,RecordTimeText.Text};
string sql1 = string.Format("insert into atest_patient_info values('{0}','{1}','{2}','{3}','{4}')",
a[0], a[1], a[2], a[3], a[4]);
string sql2 = string.Format("insert into atest_admission_rec values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
a[0], a[5], a[6], a[7], a[8], a[9], a[10]);
bool check = db.updataTables(sql1, sql2);
if(check) treeView1.Nodes.Add(IDText.Text, NameText.Text);
}
2、更新数据
public void RefreshPatient()
{
string[] a = { IDText.Text, NameText.Text, SexBox.Text, AgeText.Text, NativeText.Text, ComplainsText.Text,
PreComplaintText.Text, PreIllnessText.Text, PersonalHisText.Text, AdmittedTimeText.Text,RecordTimeText.Text};
string sql1 = string.Format("update atest_patient_info set name='{0}',sex='{1}',age='{2}',native='{3}' where pid='{4}'",
a[1],a[2],a[3],a[4],a[0]);
string sql2 = string.Format("update atest_admission_rec set complains='{0}',present='{1}',previous='{2}',personal='{3}'," +
"intime='{4}',rectime='{5}' where pid='{6}'",a[5],a[6],a[7],a[8],a[9],a[10],a[0]);
bool check = db.updataTables(sql1, sql2);
}
3、删除数据
public void DeletePatient()
{
//delete data from database
//**admission_rec has a foreign key references patient_info**
//1、delete data from table admission_rec
//2、delete data from table patient_info
string pid = IDText.Text;
string sql1 = string.Format("delete from atest_admission_rec where pid='{0}'", pid);
string sql2 = string.Format("delete from atest_patient_info where pid='{0}'", pid);
bool check = db.updataTables(sql1, sql2);
//delete from tree
//if the sql is completed
if(check) treeView1.SelectedNode.Remove();
}
4、查找数据(返回数据存在datatable中)
public void SetBoxValue()
{
string sql = "select a.pid,a.name,a.sex,a.age,a.native,b.intime,b.rectime,b.complains,b.present,b.previous,b.personal from atest_patient_info a,atest_admission_rec b where a.pid=b.pid and a.pid='"+treeView1.SelectedNode.Name+"'";
DataTable tb = db.getPatientInfo(sql);
IDText.Text = tb.Rows[0][0].ToString();
NameText.Text = tb.Rows[0][1].ToString();
SexBox.Text = tb.Rows[0][2].ToString();
AgeText.Text = tb.Rows[0][3].ToString();
NativeText.Text = tb.Rows[0][4].ToString();
AdmittedTimeText.Text = tb.Rows[0][5].ToString();
RecordTimeText.Text = tb.Rows[0][6].ToString();
ComplainsText.Text = tb.Rows[0][7].ToString();
PreComplaintText.Text = tb.Rows[0][8].ToString();
PreIllnessText.Text = tb.Rows[0][9].ToString();
PersonalHisText.Text = tb.Rows[0][10].ToString();
}
最后,感谢大神们!
参考链接:
连接oracle数据库:https://www.cnblogs.com/mq0036/p/3678148.html
读取数据:https://www.cnblogs.com/jiangxin/p/5546288.html
增删改:https://blog.csdn.net/wulex/article/details/81007248