SQL Server資料庫操作類(C#)

        博文介紹的SQL Server資料庫操作類是C#語言的，可實作對SQL Server資料庫的增删改查詢等操作。并且該操作類可實作對圖檔的存儲，博文的最後附上如何将Image圖檔轉換為byte[]數組類型，以及如何将byte[]數組類型轉換為Image圖檔。

       博文中會貼出該類的下載下傳位址，可以直接下載下傳使用。

1. DataBaseOp類

下載下傳：點選打開連結

http://download.csdn.net/detail/softimite_zifeng/9688497

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

//C#的SQL Server操作類
public class DataBaseOp
{
    private string SqlConnectionString;    //資料庫連接配接

    /// <summary>
    /// 構造函數
    /// 初始化連接配接資料庫參數
    /// </summary>
    public DataBaseOp()
    {
        SqlConnectionString = "Data Source=.;Initial Catalog=DataBaseName;User ID=sa;pwd=123456;Connection Lifetime=0;max pool size=200";
    }

    /// <summary>
    /// 構造函數
    /// 初始化連接配接資料庫參數
    /// </summary>
    /// <param name="ConSqlServer">連接配接對象</param>
    public DataBaseOp(string ConSqlServer)
    {
        SqlConnectionString = ConSqlServer;
    }

    /// <summary>
    /// 打開資料庫連接配接
    /// </summary>
    /// <param name="cn">連接配接</param>
    public void Open(SqlConnection cn)
    {
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
    }

    /// <summary>
    /// 關閉資料庫連接配接
    /// </summary>
    /// <param name="cn">連接配接</param>
    public void Close(SqlConnection cn)
    {
        if (cn != null)
        {
            if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
            cn.Dispose();
        }
    }

    /// <summary>
    /// 查詢
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>是否存在</returns>
    public bool ChaXun(string strSql)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            Open(cn);
            cmd = new SqlCommand(strSql, cn);
            return cmd.ExecuteReader().Read();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            cmd.Dispose();
            Close(cn);
        }
    }

    /// <summary>
    /// 查詢
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>第一行第一列結果</returns>
    public string ChaXun2(string strSql)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            Open(cn);
            cmd = new SqlCommand(strSql, cn);
            return cmd.ExecuteScalar().ToString().Trim();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            cmd.Dispose();
            Close(cn);
        }
    }

    /// <summary>
    /// 查詢（SqlDataReader）
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>查詢結果</returns>
    public SqlDataReader GetDR(string strSql)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            Open(cn);
            cmd = new SqlCommand(strSql, cn);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            cmd.Dispose();
        }
    }

    /// <summary>
    /// 查詢（DataSet）
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>查詢結果</returns>
    public DataSet GetDS(string strSql)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlDataAdapter sda = new SqlDataAdapter();
        try
        {
            Open(cn);
            sda = new SqlDataAdapter(strSql, cn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            sda.Dispose();
            Close(cn);
        }
    }

    /// <summary>
    /// 查詢（DataSet）
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <param name="tableName">指定DataSet["tableName"]表</param>
    /// <returns>查詢結果</returns>
    public DataSet GetDS(string strSql, string tableName)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlDataAdapter sda = new SqlDataAdapter();
        try
        {
            Open(cn);
            sda = new SqlDataAdapter(strSql, cn);
            DataSet ds = new DataSet();
            sda.Fill(ds, tableName);
            return ds;
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            sda.Dispose();
            Close(cn);
        }
    }

    /// <summary>
    /// 查詢（DataTable）
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>查詢結果</returns>
    public DataTable GetDT(string strSql)
    {
        return GetDS(strSql).Tables[0];
    }

    /// <summary>
    /// 查詢（DataView）
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>查詢結果</returns>
    public DataView GetDV(string strSql)
    {
        return GetDS(strSql).Tables[0].DefaultView;
    }

    /// <summary>
    /// 增删改，無圖檔
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <returns>影響的行數</returns>
    public int RunSql(string strSql)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            Open(cn);
            cmd = new SqlCommand(strSql, cn);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            cmd.Dispose();
            Close(cn);
        }
    }

    /// <summary>
    /// 增改，有圖檔
    /// </summary>
    /// <param name="strSql">SQL語句</param>
    /// <param name="picbyte">圖檔的二進制資料</param>
    /// <returns>影響的行數</returns>
    public int RunSql(string strSql, byte[] picbyte)
    {
        SqlConnection cn = new SqlConnection(SqlConnectionString);
        SqlCommand cmd = new SqlCommand();
        try
        {
            Open(cn);
            cmd = new SqlCommand(strSql, cn);
            cmd.Parameters.Add("@Image", SqlDbType.Image);
            cmd.Parameters["@Image"].Value = picbyte;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            cmd.Dispose();
            Close(cn);
        }
    }
}
           

2. Image圖檔與byte[]數組的互相轉換

using System.Drawing;
using System.IO;
using System.Drawing.Imaging;

// 将Image轉換為二進制序列
public static byte[] ImageToBytes(Image image)
{
    MemoryStream ms = new MemoryStream();
    image.Save(ms, ImageFormat.Jpeg);
    byte[] bytes = new Byte[ms.Length];
    ms.Position = 0;
    ms.Read(bytes, 0, bytes.Length);
    ms.Close();
    return bytes;
}

// 将二進制序列轉換為Image
public static Image BytesToImage(byte[] bytes)
{
    try
    {
        using (Stream fStream = new MemoryStream(bytes.Length))
        {
            BinaryWriter bw = new BinaryWriter(fStream);
            bw.Write(bytes);
            bw.Flush();
            Bitmap bitMap = new Bitmap(fStream);
            bw.Close(); 
            fStream.Close();
            Image image = Image.FromHbitmap(bitMap.GetHbitmap());
            return image;
        }
    }
    catch (IOException ex)
    {
        throw new Exception("讀取圖檔失敗：" + ex.Message);

    }
}
           

       博文中會貼出該類的下載下傳位址，可以直接下載下傳使用。