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);

    }
}
           

       博文中会贴出该类的下载地址，可以直接下载使用。