C#_ 数据库操作类

数据库连接字符串：

(Copy Code)

string connectionString= System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString;

执行单条增删改语句,返回影响的记录数(Copy Code)

/// <summary>
/// 执行Sql语句，返回影响的记录数
/// 单条增，删，改语句
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SqlString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(SqlString, connection))
        {
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (SqlException E)
            {
                throw new Exception(E.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }
}

 

执行多条增，删，改Sql语句,实现数据库事务(Copy Code)

/// <summary>
/// 执行多条增，删，改Sql语句，实现数据库事务。
/// 多条增，删，改语句，如果失败将撤销操作（回滚）
/// </summary>
/// <param name="SqlStringList">多条Sql语句</param>  
public static void ExecuteSqlTran(ArrayList SqlStringList)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        SqlTransaction tx = conn.BeginTransaction();
        cmd.Transaction = tx;
        try
        {
            for (int n = 0; n < SqlStringList.Count; n++)
            {
                string strSql = SqlStringList[n].ToString();
                if (strSql.Trim().Length > 1)
                {
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();
                }
            }
            tx.Commit();
        }
        catch (System.Data.SqlClient.SqlException E)
        {
            tx.Rollback();
            throw new Exception(E.Message);
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
    }
}

 

执行带一个SqlParameter参数的增,删,改Sql语句(Copy Code)

/// <summary>
/// 执行带一个SqlParameter参数的的Sql语句。
/// 示例：int i=DbHelperSql.ExecuteSql("insert into operater values('1','2','3',@content)","4");
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SqlString, string content)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(SqlString, connection);
        SqlParameter myParameter = new SqlParameter("@content",SqlDbType.NText);
        myParameter.Value = content;
        cmd.Parameters.Add(myParameter);
        try
        {
            connection.Open();
            int rows = cmd.ExecuteNonQuery();
            return rows;
        }
        catch (SqlException E)
        {
            throw new Exception(E.Message);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }
}

 

向数据库里插入Image的二进制数据(Copy Code)

/// <summary>
/// 向数据库里插入Image的二进制数据
/// </summary>
/// <param name="strSql">Sql语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSql, byte[] fs)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(strSql, connection);
        SqlParameter myParameter = new SqlParameter("@image",SqlDbType.Image);
        myParameter.Value = fs;
        cmd.Parameters.Add(myParameter);
        try
        {
            connection.Open();
            int rows = cmd.ExecuteNonQuery();
            return rows;
        }
        catch (SqlException E)
        {
            throw new Exception(E.Message);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }
}

 

执行一条SQl语句，返回第一行第一列（object）(Copy Code)

/// <summary>
/// 执行一条计算查询结果语句，返回第一行第一列（object）。
/// </summary>
/// <param name="SqlString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SqlString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(SqlString, connection))
        {
            try
            {
                connection.Open();
                object obj = cmd.ExecuteScalar();
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    return null;
                }
                else
                {
                    return obj;
                }
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }
}

 

执行查询语句,返回SqlDataReader(Copy Code)

/// <summary>
/// 执行查询语句，返回SqlDataReader,使用完成后SqlDataReader.Close()
/// </summary>
/// <param name="strSql">查询语句</param>
/// <returns>返回SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSql)
{
    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(strSql, connection);
    try
    {
        connection.Open();
        SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
       // myReader.Close();需在在使用完毕后Close();
        return myReader;
    }
    catch (SqlException e)
    {
        throw new Exception(e.Message);
    }
}

 

执行断开式查询,返回DataSet(Copy Code)

/// <summary>
/// 执行断开式查询，返回DataSet
/// </summary>
/// <param name="SqlString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSet(string SqlString)
{
    SqlConnection connection = new SqlConnection(connectionString);
    DataSet ds = new DataSet();
    try
    {
        SqlDataAdapter command = new SqlDataAdapter(SqlString, connection);
        command.Fill(ds, "table1");
    }
    catch (SqlException ex)
    {
        throw new Exception(ex.Message);
    }
    return ds;
}

 

执行查询语句,返回DataTable(Copy Code)

/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
/// <param name="SqlString"></param>
/// <param name="TableName"></param>
/// <param name="ct"></param>
/// <returns></returns>
public static DataTable GetDataTable(string SqlString)
{
    SqlConnection connection = new SqlConnection(connectionString);
    DataTable dt = new DataTable();
    try
    {
        SqlDataAdapter command = new SqlDataAdapter(SqlString, connection);
        command.Fill(dt);
    }
    catch (SqlException ex)
    {
        throw new Exception(ex.Message);
    }
    return dt;
}

 

根据参数传来的存储过程名和存储过程中用到的参数，执行增、删、改操作(Copy Code)

/// <summary>
/// 根据参数传来的存储过程名和存储过程中用到的参数，执行增、删、改操作
/// </summary>
/// <param name="strProc">存储过程名</param>
/// <param name="pars">存储过程中用到的参数数组</param>
public void ExecuteProcNonQuery(string strProc, SqlParameter[] pars)
{
    SqlCommand cmd = new SqlCommand(strProc, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    if (pars != null) //如果存储过程中用到的参数数组不为空
    {
        foreach (SqlParameter par in pars)  //则遍历数组
        {
            cmd.Parameters.Add(par);   //将所有的参数添加到SqlCommand对象的参数属性中
        }
    }
    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        conn.Close();
    }
}

 

根据参数传来的存储过程名和存储过程中用到的参数,执行断开式查询操作(Copy Code)

/// <summary>
/// 根据参数传来的存储过程名和存储过程中用到的参数，执行断开式查询操作
/// </summary>
/// <param name="strProc">存储过程名</param>
/// <param name="pars">存储过程中用到的参数</param>
/// <returns>返回DataTable类型的查询结果</returns>
public DataTable ExecuteProcQuery(string strProc, SqlParameter[] pars)
{
    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter(strProc, conn);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    if (pars != null) //如果存储过程中用到的参数数组不为空
    {
        foreach (SqlParameter par in pars)  //则遍历数组
        {
            da.SelectCommand.Parameters.Add(par);   //将所有的参数添加到SqlDataAdapter对象的参数属性中
        }
    }
    try
    {
        da.Fill(dt);
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    return dt;
}