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

namespace DataCollectionSystem
{
    public class SqlServerDBHelper
    {
        private static string _connString = string.Format("server=.;database=Data_Collection_DB;uid=sa;pwd=iamsa");
        /// <summary>
        /// 执行SQL语句，返回IdataReader对象
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns>IDataReader</returns>
        public SqlDataReader ExecReader(string sql)
        {
            SqlConnection oledbConn = new SqlConnection(_connString);
            SqlCommand command = new SqlCommand(sql, oledbConn);
            oledbConn.Open();
            return command.ExecuteReader();

        }

        /// <summary>
        /// 执行sql语句，返回执行结果
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回SQL语句执行结果1-成功；0-不成功</returns>
        public int ExecCommand(string sql)
        {

            int ret = 0;
            try
            {
                SqlConnection oledbConn = new SqlConnection(_connString);
                SqlCommand command = new SqlCommand(sql, oledbConn);
                oledbConn.Open();
                ret = command.ExecuteNonQuery();
                oledbConn.Close();
            }
            catch
            {
                return -1;
            }
            return ret;
        }

        /// <summary>
        /// 根据查询语句返回一个DataSet对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string sql)
        {
            DataSet ds = new DataSet();
            SqlConnection oledbConn = new SqlConnection(_connString);
            SqlCommand command = new SqlCommand(sql, oledbConn);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            oledbConn.Open();
            adapter.Fill(ds);
            return ds;
        }
    }
}