SqlHelper 数据库操作类2

 

using System;
using System.Data;
using System.Data.SqlClient;

namespace lsb.DBUtility
{
    
/// <summary>
    
/// SQL Server 数据库操作类
    
/// </summary>
    public class SqlHelper
    {
        
private static string connectionString = "Data Source=.;Initial Catalog=FBMS;Integrated Security=True";

        
/// <summary>
        
/// 执行查询, 返回单个值
        
/// </summary>
        public static object ExecuteScalar(string sql)
        {
            
using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    
try
                    {
                        conn.Open();
                        
return cmd.ExecuteScalar();
                    }
                    
catch
                    {
                        
throw;
                    }
                }
            }
        }

        
/// <summary>
        
/// 执行带参数查询, 返回单个值
        
/// </summary>
        public static object ExecuteScalar(string sql, SqlParameter[] cmdParms)
        {
            
using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
using (SqlCommand cmd = new SqlCommand())
                {
                    
try
                    {
                        PrepareCommand(cmd, conn, 
null, sql, cmdParms);
                        
object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        
return obj;
                    }
                    
catch
                    {
                        
throw;
                    }
                }
            }
        }

        
/// <summary>
        
/// 执行查询, 返回多个值
        
/// </summary>
        public static SqlDataReader ExecuteReader(string sql)
        {
            
using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    
try
                    {
                        conn.Open();
                        
return cmd.ExecuteReader();
                    }
                    
catch
                    {
                        
throw;
                    }
                }
            }
        }

        
/// <summary>
        
/// 执行带参数查询, 返回多个值
        
/// </summary>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] cmdParms)
        {
            
using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
using (SqlCommand cmd = new SqlCommand())
                {
                    
try
                    {
                        PrepareCommand(cmd, conn, 
null, sql, cmdParms);
                        SqlDataReader sdr 
= cmd.ExecuteReader();
                        cmd.Parameters.Clear();
                        
return sdr;
                    }
                    
catch
                    {
                        
throw;
                    }
                }
            }
        }

        
/// <summary>
        
/// 执行非查询, 返回影响行数
        
/// </summary>
        public static int ExceuteNonQuery(string sql, params SqlParameter[] cmdParms)
        {
            
using (SqlConnection conn = new SqlConnection(connectionString))
            {
                
using (SqlCommand cmd = new SqlCommand())
                {
                    
try
                    {
                        PrepareCommand(cmd, conn, 
null, sql, cmdParms);
                        
int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        
return rows;
                    }
                    
catch
                    {
                        
throw;
                    }
                }
            }
        }

        
/// <summary>
        
/// 
        
/// </summary>
        
/// <param name="cmd"></param>
        
/// <param name="conn"></param>
        
/// <param name="trans"></param>
        
/// <param name="cmdText"></param>
        
/// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            
if ((conn.State == ConnectionState.Broken) || (conn.State == ConnectionState.Closed))
            {
                conn.Open();
            }

            cmd.Connection 
= conn;
            cmd.CommandText 
= cmdText;

            
if (trans != null)
                cmd.Transaction 
= trans;

            cmd.CommandType 
= CommandType.Text;

            
if (cmdParms != null)
            {
                
foreach (SqlParameter parm in cmdParms)
                {
                    
if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && (parm.Value == null))
                    {
                        parm.Value 
= DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}

posted @ 2009-03-02 09:28 强悍的抽屉 阅读(...) 评论(...) 编辑 收藏