sqlhelper.cs

基于Web的图片管理功能模块,使用SQL SERVER和VS2005

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

public class SQLHelper
{
    // 定义连接字符串
    private static string connStr= System.Configuration.ConfigurationManager.AppSettings["connStr"];

    private SqlConnection conn = null;

    private SqlCommand cmd = null;

    private SqlDataAdapter adapter = null;

    private DataSet dataset = null;

    public SQLHelper()
    {
        conn = new SqlConnection(connStr);
        cmd = new SqlCommand();
        cmd.Connection = conn;
    }

    // 打开数据库连接
    private void openConnect()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }

    // 关闭数据库连接
    private void closeConnect()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }

    // 验证节点是否为目录
    public bool CheckDir(string strID)
    {
        bool dt;

        string tempCmd = "SELECT isDir FROM Images WHERE ImgID=" + strID;
        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;
            dt = Boolean.Parse(cmd.ExecuteScalar().ToString());

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }

        return dt;
    }

    // 获取所有节点
    public DataTable GetPictureDetails()
    {
        DataTable dt;

        string tempCmd = "SELECT * FROM Images";

        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;

            adapter = new SqlDataAdapter(cmd);
            dataset = new DataSet();
            adapter.Fill(dataset);

            dt = dataset.Tables[0];

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }

        return dt;
    }

    // 验证子节点
    public bool CheckChild(string strID)
    {
        bool dt;

        string tempCmd = "SELECT COUNT(*) FROM Images WHERE ParentID=" + strID;
        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;
            dt = Int32.Parse(cmd.ExecuteScalar().ToString()) > 0 ? true : false;

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }

        return dt;
    }

    // 删除节点
    public void DeleteItem(string strID)
    {
        string tempCmd = "DELETE Images WHERE ImgID = " + strID;
        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;
            cmd.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }
    }

    // 添加节点
    public void AddItem(string name,
                               string url,
                               string IsDir,
                               string ParentID)
    {

        string tempCmd = "INSERT INTO Images (ImgName, ImgUrl, isDir, ParentID, CreateDate) VALUES "
            + "('" + name + "', '" + url + "', '" + IsDir + "', " + ParentID + ", GetDate())";
        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;
            cmd.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }
    }

    // 更新节点
    public void UpdateItem(string strID,
                                  string name)
    {
        string tempCmd = "UPDATE Images SET ImgName = '" + name + "' WHERE ImgID = " + strID;
        try
        {
            this.openConnect();
            cmd.CommandText = tempCmd;
            cmd.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            this.closeConnect();
        }
    }
}