When the DBHelper asp.net programming

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class SQLHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        public SQLHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }

        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            if (conn == null)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        ///   Is executed without parameters to add to an SQL statement or stored procedure
        /// </summary>
        /// <param name="cmdText"> Additions to the SQL statement or stored procedure  </param>
        /// <param name="ct"> Command type  </param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType ct)
        {
            int res;
            try
            {
                cmd = new SqlCommand(cmdText, GetConn());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }

        /// <summary>
        ///   Execute the parameterized additions to SQL statements or stored procedures
        /// </summary>
        /// <param name="cmdText"> Additions to the SQL statement or stored procedure  </param>
        /// <param name="ct"> Command type  </param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn()))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }

        /// <summary>
        ///   Executing a query the SQL statement or stored procedure
        /// </summary>
        /// <param name="cmdText"> Query the SQL statement or stored procedure  </param>
        /// <param name="ct"> Command type  </param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdText, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        /// <summary>
        ///   Execute the parameterized query SQL statement or stored procedure
        /// </summary>
        /// <param name="cmdText"> Query the SQL statement or stored procedure  </param>
        /// <param name="paras"> Parameters collection  </param>
        /// <param name="ct"> Command type  </param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
    }
}
分类:DotNet 时间:2010-03-07 人气:240
分享到:
blog comments powered by Disqus

相关文章

  • SQL Server 2005 RTM 安装错误 :The SQL Server System Configuration Checker cannot be executed due to 2013-10-14

    昨天在安装SQL 2005 RTM的时候碰到了这样一个错误: Microsoft SQL Server 2005 Setup ------------------------------ The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine XXXX Error:2147749896 (0x80041008). For help, click:

  • Prompted in Navicate be entered using SQL parameters. 2011-03-01

    select [$ Enter A] Input Reference: select if(sum(gold_coin) is null, 0, sum(gold_coin)) as gold_coin_total from gather_34.credit_log_96 where account = [$ Enter account number ] and `time` between [$ Enter the last time ] and [$ Enter this time ]; A

  • JSP to connect SQL Server 2000 system configuration details 2010-07-14

    To SUN official site (http://java.sun.com) to download J2SDK installation file: j2sdk-1_4_2_04-windows-i586-p.exe, After the download is installed J2SDK; installed after setting the environment variable: My computer --- properties --- Advanced --- Environ

  • oracle stored procedure parameters in the output (like java in the system.out.println () this function) 2010-05-13

    Storage parameters of the process output (like java in the system.out.println () this function) dbms_output.put_line (ln_sql);

  • 支持多类型数据库的c#数据库模型示例 2014-02-13

    本文为大家提供一个c#数据库访问模型,支持多类型数据库,简单抽取数据库访问函数,大家参考使用吧 DataAccess.cs using System; using System.Collections.Generic; using System.Text; namespace DynamicFramework { public abstract class DataAccess : MarshalByRefObject { protected System.Data.Common.DbConn

  • asp.net 数据库连接类代码(SQL) 2015-05-04

    asp.net数据库连接类(SQL) 代码,需要的朋友可以参考下。 public class SqlOperation { #region 属性 /// <summary> /// 保存在Web.config中的连接字符串 /// </summary> protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].C

  • C # to connect SqlServer and execute sql 2010-08-02

    SQLserver2008 create a stored procedure, the query attempts (sql statement) created, the system will be stored in the specified database sp "programmability" -> "Stored Procedures" to create a stored procedure searchDate CREATE

  • mysql database web programming mysqlhelper 2010-03-07

    using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; using MySql.Data.Types; using MySql.Data.MySqlClient; // The source download from www.51aspx.com(51as

  • C# Oracle数据库操作类实例详解 2014-01-23

    这篇文章主要介绍了C# Oracle数据库操作类实例,进行数据库操作时很有实用价值,需要的朋友可以参考下 本文所述为C#实现的Oracle数据库操作类,可执行超多常用的Oracle数据库操作,包含了基础数据库连接、关闭连接、输出记录集、执行Sql语句,返回带分页功能的dataset 、取表里字段的类型和长度等,同时还有哈稀表自动插入数据库等高级任务。需要特别指出的是:在执行SQL语句,返回 DataReader之前一定要先用.read()打开,然后才能读到数据,再用hashTable对数据库进行

  • Asp.Net 数据操作类(附通用数据基类) 2014-02-27

    数据操作类代码,方便在asp.net操作数据库 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.Html

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.187 (s). 12 q(s)