ADO .NET直接操作数据库-1,ORACLE;2,SQL Server;3,MySql。

1 篇文章 0 订阅
1 篇文章 0 订阅

ADO .NET直接操作数据库-1,ORACLE;2,SQL Server;3,MySql。
ADO .NET直接操作数据库,数据库类型分为三种,1,oralce,2,sql server,3,mysql。
操作功能分为:连接数据库,对表进行增删改查。

private static SqlConnection CreateConnectionSQL()
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["sqlserver1"];
            SqlConnection conn = new SqlConnection(connstr);
            return conn;
        }

        private static OracleConnection CreateConnectionORA()
        {
            string orcalestring = System.Configuration.ConfigurationManager.AppSettings["Oracle"];
            OracleConnection conn = new OracleConnection(orcalestring);
            return conn;
        }

        private static MySqlConnection CreateConnectionMySql()
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["Mysql"];
            MySqlConnection conn = new MySqlConnection(connstr);
            return conn;
        }
		/// <summary>
        /// 查询数据返回第一行第一列
        /// </summary>
        /// <param name="type">所用数据库类型Oracle或者 sql server</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static string DB_SELECT_ONE(string type, string sql)
        {
            string sql_return = string.Empty;
            if (type == "Oracle")
            {
                OracleConnection conn = CreateConnectionORA();
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                try
                {
                    conn.Open();
                    sql_return = cmd.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    sql_return = "数据单条出错!原因为:" + ex.Message.ToString().Replace("\n", "\\n").Trim();
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
            }
            else if (type == "Sql")
            {
                SqlConnection conn = CreateConnectionSQL();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                try
                {
                    conn.Open();
                    sql_return = cmd.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    sql_return = "数据单条出错!原因为:" + ex.Message.ToString();
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
            }
            else if (type == "MySql")
            {
                MySqlConnection conn = CreateConnectionMySql();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                try
                {
                    conn.Open();
                    sql_return = cmd.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    sql_return = "数据单条出错!原因为:" + ex.Message.ToString();
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
            }

            return sql_return;
        }
		/// <summary>
        /// 进行数据的查询返回datatable
        /// </summary>
        /// <param name="type">所用数据库类型Oracle或者 sql server</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataTable DB_SELECT_ALL(string type, string sql)
        {
            DataTable dt_return = new DataTable();
            if (type == "Oracle")
            {
                OracleConnection conn = CreateConnectionORA();
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                OracleDataAdapter dap = new OracleDataAdapter(cmd);
                try
                {
                    conn.Open();
                    dap.Fill(dt_return);
                }
                catch (Exception ex)
                {
                    dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
                    dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
                }
                finally
                {
                    dap.Dispose();
                    conn.Close();
                }
            }
            else if (type == "Sql")
            {
                SqlConnection conn = CreateConnectionSQL();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    dap.Fill(dt_return);
                }
                catch (Exception ex)
                {
                    dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
                    dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
                }
                finally
                {
                    dap.Dispose();
                    conn.Close();
                }
            }
            else if (type == "MySql")
            {
                MySqlConnection conn = CreateConnectionMySql();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.CommandTimeout = 36000;
                MySqlDataAdapter dap = new MySqlDataAdapter(cmd);
                try
                {
                    conn.Open();
                    dap.Fill(dt_return);
                }
                catch (Exception ex)
                {
                    dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
                    dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
                }
                finally
                {
                    dap.Dispose();
                    conn.Close();
                }
            }

            return dt_return;
        }

        /// <summary>
        /// 进行数据的删除及更新
        /// </summary>
        /// <param name="type">所用数据库类型Oracle或者Sql Server</param>
        /// <param name="sql">sql语句</param>
        /// <param name="rescount">返回更新行数</param>
        /// <param name="resmsg">返回信息</param>
        public static void DB_RUN(string type, string sql, out int rescount, out string resmsg)
        {
            rescount = -1;
            resmsg = string.Empty;
            if (type == "Oracle")
            {
                using (OracleConnection conn = CreateConnectionORA())
                {
                    conn.Open();
                    OracleTransaction transaction = conn.BeginTransaction();
                    try
                    {
                        using (OracleCommand cmd = new OracleCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = 36000;
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sql;
                            cmd.Transaction = transaction;
                            rescount = cmd.ExecuteNonQuery();
                            transaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback(); // 事务回滚
                        resmsg = "ErrCodeX:" + ex.Message.ToString();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            else if (type == "Sql")
            {
                using (SqlConnection conn = CreateConnectionSQL())
                {
                    conn.Open();
                    SqlTransaction transaction = conn.BeginTransaction();
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = 36000;
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sql;
                            cmd.Transaction = transaction;
                            rescount = cmd.ExecuteNonQuery();
                            transaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback(); // 事务回滚
                        resmsg = "ErrCodeX:" + ex.Message.ToString();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            else if (type == "MySql")
            {
                using (MySqlConnection conn = CreateConnectionMySql())
                {
                    conn.Open();
                    MySqlTransaction transaction = conn.BeginTransaction();
                    try
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = 36000;
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sql;
                            cmd.Transaction = transaction;
                            rescount = cmd.ExecuteNonQuery();
                            transaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback(); // 事务回滚
                        resmsg = "ErrCodeX:" + ex.Message.ToString();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

需引用的相关命名空间

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值