OracleHelper与SqlServerHelper

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=DBName)));PASSWORD=a123456;PERSIST SECURITY INFO=True;USER ID=system" providerName="Oracle.DataAccess.Client"/>

</connectionStrings>

4、数据测试

using System;

using System.Collections;

using System.Configuration;

using System.Data;

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 Oracle.DataAccess.Client;

namespace OracleDBDemo

{

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            OracleExecuteNonQuery();

        }

        #region SqlServer

        private void SqlServerExecuteDataTable()

        {

            string sql = "select * from Account";

            DataTable dt = SqlServerHelper.ExecuteDataTable(sql);

        }

        private void SqlServerExecuteNonQuery()

        {

            string sql = "insert into Account(AccountName,createtime) values(@name,@time)";

            SqlServerHelper.ExecuteNonQuery(sql, new SqlParameter("@name", "你好屌！！！"), new SqlParameter("@time", DateTime.Now));

        }

        private void SqlServerExecuteScalar()

        {

            string sql = "select max(id) from account where AccountName=@name";

            object obj = SqlServerHelper.ExecuteScalar(sql, new SqlParameter("@name", "ff"));

            Response.Write(obj.ToString());

        }

        #endregion

        #region Oracle

        private void OracleExecuteDataTable()

        {

            string sql = "select * from AccountInfo";

            DataTable dt = OracleHelper.ExecuteDataTable(sql);

        }

        private void OracleExecuteNonQuery()

        {

            string sql = "insert into AccountInfo(id,AccountInfoName) values(:id,:name)";

            OracleHelper.ExecuteNonQuery(sql,  new OracleParameter(":id", ),new OracleParameter(":name", "你好屌！！！"));

        }

        private void OracleExecuteScalar()

        {

            string sql = "select max(id) from account where AccountName=@name";

            object obj = SqlServerHelper.ExecuteScalar(sql, new SqlParameter("@name", "ff"));

            Response.Write(obj.ToString());

        }

        #endregion

    }

}

备注：

/*
 * 使用参数化 DbCommand 的一个缺点是需要参数的代码将仅适用于支持相同语法的提供程序。
 * OleDb、SqlClient 和 Oracle 提供程序全部使用不同的语法。
 * 例如：
 * SqlClient 参数语法需要使用“@”作为参数占位符；
 * OleDb 参数语法需要使用问号“?”作为参数占位符；
 * Oracle 参数语法需要使用“:”作为参数占位符。
 */