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 人气:267
分享到:
blog comments powered by Disqus

相关文章

  • 支持多类型数据库的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

  • 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:

  • 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&quo

  • 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

  • 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

  • Asp.Net 通用数据操作类 (附通用数据基类)第1/2页 2014-03-14

    以前经常用php的数据操作类,这次的asp.net数据操作类,是个方法 文章内容为本站编辑,创作.你可以任意转载.发布.使用但请务必以明文标注文章原始出处及本声明 http://www.opent.cn 作者:浪淘沙 此贴的方法会持续更新, 此文件要引用与数据操作的基类 using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using Sy

  • c#实现的操作oracle通用类 2014-04-28

    这篇文章主要介绍了c#实现的操作oracle通用类,用来操作oracle数据库十分的方便,需要的朋友可以参考下 修改整理的一个通用类,用来操作oracle数据库 十分的方便,支持直接操作sql语句和Hash表操作.现在修补MIS我都用这个类,节约了大 量的代码与工作良!在老孙的指点下,偶将操作oracle,sql server ,access三种数据库的通用类集成在一起写了个数据抽象工厂,同时支持三种数据库无缝切换...以后整理出来. using System; using System.Dat

  • java interview questions 1 Study Notes 2010-07-17

    1, JSP's built-in objects and their role A: JSP total of the following nine kinds of basic built-in component (ASP-6 with the corresponding internal components): request the client request, this request will be included from the GET / POST request pa

  • 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 PROC

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

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

CodeWeblog.com 版权所有 闽ICP备15018612号

processed in 0.036 (s). 11 q(s)