C#-Win From开发-数据库连接

C#-数据库操作

连接方式，我们使用 Oracle.ManagedDataAccess.dll 插件连接数据库。

在vs中找到参考，然后引入Oracle.ManagedDataAccess.dll

数据库操代码如此

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AlarmExportToExcel
{
    public class DataSouce
    {
        /**
          导出Excel
            
            */

        public void DataToExcel(DataGridView m_DataView)
        {
            SaveFileDialog saveFile = new SaveFileDialog();
            saveFile.Title = "保存EXECL文件";
            saveFile.Filter = "EXECL文件(*.csv) |*.csv |所有文件(*.*) |*.*";
            saveFile.FilterIndex = 1;
            if (saveFile.ShowDialog() == DialogResult.OK)
            {
                string FileName = saveFile.FileName;
                if (File.Exists(FileName))
                    File.Delete(FileName);
                FileStream objFileStream;
                StreamWriter objStreamWriter;
                string strLine = "";
                objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
                objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
                for (int i = 0; i < m_DataView.Columns.Count; i++)
                {
                    if (m_DataView.Columns[i].Visible == true)
                    {
                        strLine = strLine + m_DataView.Columns[i].HeaderText.ToString() + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";

                for (int i = 0; i < m_DataView.Rows.Count; i++)
                {
                    if (m_DataView.Columns[0].Visible == true)
                    {
                        if (m_DataView.Rows[i].Cells[0].Value == null)
                            strLine = strLine + " " + Convert.ToChar(9);
                        else
                            strLine = strLine + m_DataView.Rows[i].Cells[0].Value.ToString() + Convert.ToChar(9);
                    }
                    for (int j = 1; j < m_DataView.Columns.Count; j++)
                    {
                        if (m_DataView.Columns[j].Visible == true)
                        {
                            if (m_DataView.Rows[i].Cells[j].Value == null)
                                strLine = strLine + " " + Convert.ToChar(9);
                            else
                            {
                                string rowstr = "";
                                rowstr = m_DataView.Rows[i].Cells[j].Value.ToString();
                                if (rowstr.IndexOf("\r\n") > 0)
                                    rowstr = rowstr.Replace("\r\n", " ");
                                if (rowstr.IndexOf("\t") > 0)
                                    rowstr = rowstr.Replace("\t", " ");
                                strLine = strLine + rowstr + Convert.ToChar(9);
                            }
                        }
                    }
                    objStreamWriter.WriteLine(strLine);
                    strLine = "";
                }
                objStreamWriter.Close();
                objFileStream.Close();
                MessageBox.Show("保存EXCEL成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }










        /**
           执行查询
           dbName ： 数据库名称
         startTime：  开始时间
           endTime：  结束时间
              week： 周
               day： 天
           lineNme: 名称
          deviceId: 名称
        */
        //public static DataTable executeQuery(string dbName, string startTime, string endTime, RadioButton week, RadioButton day, string lineNme, string deviceId)
       
        public string connecting(string url)
        {
            using (OracleConnection conn = new OracleConnection())
            {
                conn.ConnectionString = url;
                try
                {
                    conn.Open();
                    return "连接成功";
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return "连接异常";
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }

                }

            }


        }


        //测试连接
        public string testConnecting(string source)
        {

            switch (source)
            {
                case "1":
                    return "1" + connecting("Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = 数据库名称))); USER ID = 用户名; PASSWORD = 密码;";);
                case "2":
                    return "2" + connecting("Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = 数据库名称))); USER ID = 用户名; PASSWORD = 密码;";);
                case "3":
                    return "3" + connecting("Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = 数据库名称))); USER ID = 用户名; PASSWORD = 密码;";);
                default:
                    return "数据库选择异常，请选择对应数据库！";

            }

        }

        //定义一个 查询sql 返回 DataTable  方法
        /**
          sql ：执行的sql 语句
          conn ： 数据库连接对象
        */
        public static DataTable executeSqlForDatable(string sql, OracleConnection conn)
        {

            OracleCommand cmd = new OracleCommand(sql, conn);

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet set = new DataSet();
            try
            {

                adapter.Fill(set);

            }
            catch (Exception)
            {
                return null;
            }

            return set.Tables[0];
        }


        /**
         sql执行方法，这里相当于数据层
             数据库操作
          执行查询
          dbName ： 数据库名称
        startTime：  开始时间
          endTime：  结束时间
             week： 周
              day： 天
          lineNme: 名称
         deviceId: id 
       */
        public static DataTable executeQuery(string dbName, string startTime, string endTime, RadioButton week, RadioButton day, RadioButton all_Alarm, RadioButton all_throwAndPut, string deviceId, string deviceNme)
           
        {
            deviceId = deviceId.Trim();
            deviceNme = deviceNme.Trim();
            //天查询的sql
            string queryDaySql="";
            //周查询的sql
            string queryWeekSql = "";
            DataTable result = null;

            using (OracleConnection conn = new OracleConnection())
            {
                if (dbName.Equals("1"))
                {
                    //天查询的sql
                    queryDaySql = "";
                    // 周查询的对象
                    queryWeekSql = " ";

                    conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = 数据库名称))); USER ID = 用户名; PASSWORD = 密码;";
                }
                else if (dbName.Equals("1"))
                {
                    conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = 数据库名称))); USER ID = 用户名; PASSWORD = 密码;";
                    //天查询的sql
                    queryDaySql = " ";
                    // 周查询的对象
                    queryWeekSql = "";

                }
                else
                {
                    //天查询的sql
                    queryDaySql = "";
                    // 周查询的对象
                    queryWeekSql = " ";

                    conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = DB名称))); USER ID = 用户名; PASSWORD = 密码;";
                }
                conn.Open();
                try
                {
                    if (day.Checked)
                    {
                        result = executeSqlForDatable(queryDaySql, conn);
                    } else if (all_Alarm.Checked) {
                        string queryAllAlarmSql = " ";

                        result = executeSqlForDatable(queryAllAlarmSql, conn);

                    } else if (all_throwAndPut.Checked) {
                        string queryAllThrowAndPutSql = " ";

                        result = executeSqlForDatable(queryAllThrowAndPutSql, conn);
                    }

                    else
                    {

                        result = executeSqlForDatable(queryWeekSql, conn);

                    }
                    return result;
                }
                catch (OracleException e)
                {
                    MessageBox.Show(e.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    throw e;
                    
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();

                    }
                }
            }


        }

    }
}