OracleHelper与SqlServerHelper

浏览数：63 / 时间：2015年06月12日

1、OracleHelper

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Oracle.DataAccess.Client;

namespace OracleDBDemo
{
    public class OracleHelper
    {

        private static string oracleConnectionStr = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ToString();


        public static DataTable ExecuteDataTable(string sql,params OracleParameter[] paramList)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStr))
            {
                conn.Open();

                using (OracleCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;

                    command.Parameters.AddRange(paramList);

                    DataTable dt = new DataTable();

                    OracleDataAdapter adapter = new OracleDataAdapter(command);

                    adapter.Fill(dt);

                    return dt;
                }
            }
        }

        public static int ExecuteNonQuery(string sql, params OracleParameter[] paramList)
        { 
            using(OracleConnection conn = new OracleConnection(oracleConnectionStr))
            {
                conn.Open();
                using (OracleCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);

                    return command.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params OracleParameter[] paramList)
        {
            using (OracleConnection conn = new OracleConnection(oracleConnectionStr))
            {
                conn.Open();
                using (OracleCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);

                    return command.ExecuteScalar();
                }
            }
        }
    }
}

2、SqlServerHelper

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Common;

namespace OracleDBDemo
{
    public class SqlServerHelper
    {

        private static string sqlConnenctionStr = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ToString();

       
        public static DataTable ExecuteDataTable(string sql,params SqlParameter[] paramList)
        {
            using(SqlConnection conn = new SqlConnection(sqlConnenctionStr))
            {
                 conn.Open();
             
                 using(SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);

                    DataTable dt = new DataTable();

                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                     
                    adapter.Fill(dt);

                     return dt;
                }
            }
        }

        public static int ExecuteNonQuery(string sql, params SqlParameter[] paramList)
        {

            using (SqlConnection conn = new SqlConnection(sqlConnenctionStr))
            {
                conn.Open();

                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);

                    return command.ExecuteNonQuery();
                }
            }
            
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] paramList)
        {
            using (SqlConnection conn = new SqlConnection(sqlConnenctionStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;

                    command.Parameters.AddRange(paramList);

                    object obj = command.ExecuteScalar();

                    return obj;

                }
            }
        }


    }
}

3、web.config

<connectionStrings>

    <add name="SqlServerConnectionString" connectionString="Data Source=.\sql2005;Initial Catalog=Credit;Integrated Security=False;User ID=sa;Password=123456;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False" providerName="System.Data.SqlClient"/>
        
    <add name="OracleConnectionString" connectionString="DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=WebSite)));PASSWORD=a123456;PERSIST SECURITY INFO=True;USER ID=WebSiteDataDeveloper" providerName="Oracle.DataAccess.Client"/>

</connectionStrings>