用C#l连接MySQL和Oracle数据库－在公司最近十天的工作内容总结（一）

1.MySQL,Oracle数据库的连接
using System;
using System.Data;
using System.Data.OracleClient;
using MySQLDriverCS;
using System.Windows.Forms;

namespace MSG
{
 /// <summary>
 /// pub_Class 的摘要说明。
 /// </summary>
 public class Pub_Class
 {
  public Pub_Class()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   // 
  }

  //Oracle 数据库
  public static OracleConnection OraConn=
                   new OracleConnection("data source=zldfdb;user id=tliadmin;password=tliadmin12345");
  //建立与Oracle数据库的连接

  public static void OraDB_Open()
  {
   OraConn.Open(); //打开数据库
  }

  public void OraDB_Insert(string sqlstr)
  {
   OracleCommand cm = new OracleCommand(sqlstr,OraConn); //执行插入操作
   cm.ExecuteNonQuery();
  }

  public void OraDB_Delete(string sqlstr)
  {
   OracleCommand cm = new OracleCommand(sqlstr,OraConn); //执行删除操作
   cm.ExecuteNonQuery();
  }
 
  public void OraDB_Update(string sqlstr)
  {
   OracleCommand cm = new OracleCommand(sqlstr,OraConn); //执行更新操作
   cm.ExecuteNonQuery();
  }

  
  public  DataSet OraDB_Select(string sqlstr,string tablename)
  {
   OracleDataAdapter da = new OracleDataAdapter(sqlstr,OraConn); //执行选择操作
   DataSet ds = new DataSet();
   da.Fill(ds,tablename);
   return ds;
  }

  

  //MySQL数据库
  public static MySQLConnection MySQLConn=new MySQLConnection(new MySQLConnectionString(
                                      "10.100.100.2","im","tliadmin","tliadmin12345",3306).AsString);
  //MySQL数据库的连接语句  
  
  public static void MySQLDB_Open()
  {
   MySQLConn.Open();//打开数据库
  }

  public static void MySQLDB_Insert(string sqlstr)
  {
   MySQLCommand cm=new MySQLCommand(sqlstr,MySQLConn);//执行插入操作
   cm.ExecuteNonQuery();
  }

  
  public void MySQLDB_Delete(string sqlstr)
  {
   MySQLCommand cm=new MySQLCommand(sqlstr,MySQLConn);//执行删除操作
   cm.ExecuteNonQuery();
  }
 
  public void MySQLDB_Update(string sqlstr)
  {
   MySQLCommand cm=new MySQLCommand(sqlstr,MySQLConn);//执行更新操作
   cm.ExecuteNonQuery();
  }
  
  public  DataSet MySQLDB_Select(string sqlstr,string tablename) 
  {
   MySQLDataAdapter da = new MySQLDataAdapter(sqlstr,MySQLConn);//执行选择操作
   DataSet ds = new DataSet();
   da.Fill(ds,tablename);
   return ds;
  }
  
 }
}
连接MySQL数据库时,使用到了MySQLDriverCS提供程序,下载目录http://sourceforge.net/projects/mysqldrivercs/
在ASP中与MySQL数据库的连接可以参考http://www.easte.cn/post/18.html

对于与Oracle数据库的连接用到System.Data.OracleClient.dll动态库(在没有安装Oracle Client的情况下).

与Oracle,MySQL数据库的连接代码
private void Button1_Click(object sender, System.EventArgs e)
  {
   
   Pub_Class.OraConn.Open();
   //string strsql="insert into xxxt_xxfs(nbbm,xxid,sjhm,xxnr,bzxx,djsj,dlzh) values(1,1,'13787104050','test','bzxx',to_date('2005-01-01 12:12:12','yyyy-mm-dd hh:mi:ss'),'1001')";
   string strsql="insert into xxxt_xxfs(nbbm,xxid,sjhm,xxnr) values(N_xxxt_xxfs.NextVal,1,'13787104050','test')";
   Pub_Class.OraDB_Insert(strsql);
   Pub_Class.OraConn.Close();

  }

  private void Button2_Click(object sender, System.EventArgs e)
  {
   Pub_Class.OraConn.Open();
   string strsql="update xxxt_xxfs set xxid='2' where nbbm=1";
   Pub_Class.OraDB_Update(strsql);
   Pub_Class.OraConn.Close();
  }

  private void Button3_Click(object sender, System.EventArgs e)
  {
   Pub_Class.OraConn.Open();
   string strsql="delete from xxxt_xxfs where xxid=1";
   Pub_Class.OraDB_Delete(strsql);
   Pub_Class.OraConn.Close();
  }

  private void Button4_Click(object sender, System.EventArgs e)
  {
   string strsql="select * from xxxt_xxfs";
   Pub_Class DBSelet = new Pub_Class();
            DataSet ds = DBSelet.OraDB_Select(strsql,"xxxt_xxfs");
   DataGrid1.DataSource=ds.Tables[0].DefaultView;
   DataGrid1.DataBind();
  }

  private void Button5_Click(object sender, System.EventArgs e)
  {
   Pub_Class.MySQLConn.Open();
   string strsql="insert into api_mt_88(SM_ID,mobiles,content) values (1,'13875907966','test')";
   Pub_Class.MySQLDB_Insert(strsql);
   Pub_Class.MySQLConn.Close();
  }

  private void Button6_Click(object sender, System.EventArgs e)
  {
   Pub_Class.MySQLConn.Open();
   string strsql="update api_rpt_88 set rpt_desc='test' where auto_sn=3";
   Pub_Class.MySQLDB_Update(strsql);
   Pub_Class.MySQLConn.Close();
  }

  private void Button8_Click(object sender, System.EventArgs e)
  {
   Pub_Class.MySQLConn.Open();
   string strsql="delete from api_rpt_88 where auto_sn=1";
   Pub_Class.MySQLDB_Delete(strsql);
   Pub_Class.MySQLConn.Close();  
  }

  private void Button7_Click(object sender, System.EventArgs e)
  {
   string strsql="select * from api_rpt_88";
   Pub_Class DBSelet = new Pub_Class();
   DataSet ds = DBSelet.MySQLDB_Select(strsql,"api_rpt_88");
   DataGrid2.DataSource=ds.Tables[0].DefaultView;
   DataGrid2.DataBind();
  }

在项目中还实现了一个功能就是将Oracle的数据记录读取后写道MySQL数据库,使用到了如下的代码:
private void timer1_Tick(object sender, System.EventArgs e)      //定时器1启动后往MySQl数据库插入记录
  {
   try
   {
    string xxid,sjhm,xxnr;
    string strsql="select xxid,sjhm,xxnr from xxxt_xxfs";

    Pub_Class DBSelet = new Pub_Class();
    DataSet ds = DBSelet.OraDB_Select(strsql,"xxxt_xxfs");
   
    foreach (DataRow Row in ds.Tables["xxxt_xxfs"].Rows)
    {
     xxid = Row[0].ToString();
     sjhm = Row[1].ToString();
     xxnr = Row[2].ToString(); 

     string strsql1="insert into api_mt_88(SM_ID,mobiles,content) values ("+xxid+",'"+sjhm+"','"+xxnr+"')";   //注意这里字符串的值的引用的语法
     Pub_Class.MySQLDB_Insert(strsql1);
    }

    ds.Reset();
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.ToString());
    timer1.Enabled=false;
    timer2.Enabled=true;
   }   
  }

  private void timer2_Tick(object sender, System.EventArgs e)//当发现网络故障后,关闭定时器1,启动定时器2进行网络的侦测
  {
   Pub_Class.OraConn.Close();
   Pub_Class.MySQLConn.Close();
   if (CheckNetwork(3306,"10.100.100.2"))//测试于MySQL数据库连接的网络
   {
    if (CheckNetwork(1521,"10.100.100.3"))//测试于Oracle数据库连接的网络    
    { 
     try
     {
      Pub_Class.OraConn.Open(); 
      //MessageBox.Show("Oracle " + Pub_Class.OraConn.State.ToString());
      Pub_Class.MySQLConn.Open();
      //MessageBox.Show("MySQL " + Pub_Class.MySQLConn.State.ToString());
     }
     catch(Exception exc)
     {
      MessageBox.Show(exc.ToString());
     }   

     if ((Pub_Class.OraConn.State.ToString()=="Open") & (Pub_Class.MySQLConn.State.ToString()=="Open"))             //获取连接的状态信息,但此信息不一定准确
     {
      timer1.Enabled = true;
      timer2.Enabled = false;
         //MessageBox.Show("OK");
     }
    }
   }   
   //MessageBox.Show("tt");
  }

2.timer和System.Timer类对象
  timer控件的使用方法：
 在Windows Form应用程序中可以加入timer控件，timer控件的Interval指示Elapsed时间的发生的时间间隔
 双击，可以编辑timer的Tick事件发生时执行的代码。