sqlhelper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AutoW.Framework.Core.Utils.SqlHelpers
{
public class SqlHelper
{
///
///执行增删改
///
///
///
///
public static int ExecuteNoneQuery(string constr, string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
var str= cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
return str;
}

        }

    }    

    /// <summary>
    /// 返回datatable
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pms"></param>
    /// <returns></returns>
    public static DataTable ExecuteDataTable(string constr, string sql, params SqlParameter[] pms)
    {
        DataTable dt = new DataTable();
        SqlConnection conn = new SqlConnection(constr);

        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
        {
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(dt);
        }
        return dt;
    }

    /// <summary>  
    /// 一次性把DataTable中的数据插入
    /// </summary>  
    /// <param name="source">DataTable数据源</param>  
    /// <returns>true - 成功，false - 失败</returns>  
    public static bool AddDataTableToDB(DataTable source, string constr, string Table)
    {
        SqlTransaction tran = null;//声明一个事务对象  
        try
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();//打开链接  
                using (tran = conn.BeginTransaction())
                {
                    using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                    {
                        copy.DestinationTableName = Table;  //指定服务器上目标表的名称
                        copy.WriteToServer(source);                      //执行把DataTable中的数据写入DB  
                        tran.Commit();                                      //提交事务  
                        return true;                                        //返回True 执行成功！  
                    }
                }
            }
        }
        catch (Exception ex)
        {
            if (null != tran)
                tran.Rollback(); 
            return false;//返回False 执行失败！  
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>  
    public static int ExecuteSqlTran(List<String> SQLStringList, string constr)
    {
        using (SqlConnection connection = new SqlConnection(constr))
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            SqlTransaction tx = connection.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                int count = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n];

                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        count += cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                return count;
            }
            catch
            {
                tx.Rollback();
                return 0;
            }
            finally
            {
                cmd.Dispose();
                connection.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 返回单个值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pms"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string constr, string sql, params SqlParameter[] pms)
    {
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();
                var str = cmd.ExecuteScalar();
                con.Close();
                con.Dispose();
                return str;
            }
        }
    }


    /// <summary>
    /// 返回DataSet
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataSet ExecuteDataSet(string constr, string sql,List<string> table)
    {

        DataSet dt = new DataSet();
        SqlConnection conn = new SqlConnection(constr);
        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
        {
            foreach (var item in table)
            {
                adapter.TableMappings.Add(item);                
            }
            DataSet ds = new DataSet();
            adapter.Fill(ds);
        }
        return dt;
    }

    /// <summary>
    /// 返回执行多个sql
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pms"></param>
    /// <returns></returns>
    #region 事务一次执行多个sql，将sql语句按照数组传递进去
    public static object ExecuteScores(string constr, string[] strStudentNoScore, params SqlParameter[] pms)
    {  
            using (SqlConnection sqlcon = new SqlConnection(constr))
            {
                sqlcon.Open();//打开链接  
                SqlTransaction myTrans = null;//声明一个事务对象
                //sql命令对象
                SqlCommand mycmd = new SqlCommand();
                //声明命令对象的连接数据库
                mycmd.Connection = sqlcon;
                //开启事务
                myTrans = sqlcon.BeginTransaction();
                //设置将在命令对象中执行的事务
                mycmd.Transaction = myTrans;
            try
            {
                //遍历字符串数组中的sql语句
                for (int i = 0; i < strStudentNoScore.Length; i++)
                {
                    mycmd.CommandText = strStudentNoScore[i];
                    int r = mycmd.ExecuteNonQuery();
                }
                //事务提交
                myTrans.Commit();
                return true;
            }
            catch (Exception)
            {
                //执行错误，事务回滚
                if (myTrans != null)
                    myTrans.Rollback();
                return false;
            }
            finally
            {
                //关闭连接                
                if (sqlcon != null)
                    sqlcon.Close();
                sqlcon.Dispose();
            }
        } 
    }
    #endregion


    #region  存储过程
    /// <summary>
    /// 执行带参数的存储过程，返回DataSet类型
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public static DataSet RunProceduresByParameter(string connectionString, string storedProcName, IDataParameter[] parameters)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            DataSet dataSet = new DataSet();
            connection.Open();
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
            sqlDA.Fill(dataSet);
            connection.Close();
            connection.Dispose();
            return dataSet;
        }
    }

    /// <summary>  
    /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)  
    /// </summary>  
    /// <param name="connection">数据库连接</param>  
    /// <param name="storedProcName">存储过程名</param>  
    /// <param name="parameters">存储过程参数</param>  
    /// <returns>SqlCommand</returns>  
    private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        SqlCommand command = new SqlCommand(storedProcName, connection);
        command.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter parameter in parameters)
        {
            command.Parameters.Add(parameter);
        }
        return command;
    }

    /// <summary>  
    /// 一次性把DataTable中的数据插入并且返回
    /// </summary>  
    /// <param name="source">DataTable数据源</param>  
    /// <returns>true - 成功，false - 失败</returns>  
    public static DataSet API(DataTable source, string constr, params string[] param)
    {
        try
        {
            SqlConnection cn = new SqlConnection(constr);//connertionString链接数据库字符串
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = @"" + param[0];
            SqlParameter p = cmd.Parameters.AddWithValue("@User", source);

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    public static DataSet RunProcedures(string connectionString, string storedProcName, IDataParameter[] parameters)
    {

        try
        {
            DataSet ds = new DataSet();
            SqlConnection sqlconn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlconn;
            cmd.CommandText = storedProcName;
            cmd.CommandType = CommandType.StoredProcedure;
            parameters[3].Direction = ParameterDirection.Output;
            parameters[4].Direction = ParameterDirection.Output;
            foreach (var item in parameters)
            {
                cmd.Parameters.Add(item);
            }

            sqlconn.Open();
            // 执行存储过程并返回影响的行数
            cmd.ExecuteNonQuery().ToString();
            sqlconn.Close();
            string str = parameters[3].Value.ToString();
            int i = int.Parse(parameters[4].Value.ToString());
            ds.Tables[0].Rows.Add("@return", str);
            ds.Tables[0].Rows.Add("@catchflag", i);
            return ds;
        }
        catch (Exception ex)
        {

            throw;
        }

    }


    #endregion
}

}