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

namespace WindowsApplication1
{
    class DataBase
    {
        public SqlConnection connection;
        /// <summary>
        /// 打开数据库
        /// </summary>
        private void open()
        {
            string connstring = "Data Source=(local);Initial Catalog=school;User ID=sa";
            connection = new SqlConnection(connstring);
            connection.Open();
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        private void close()
        {
            connection.Dispose();
            connection.Close();
            connection = null;
        }

        /// <summary>
        /// 输入SQL命令，得到DataReader对象
        /// </summary>
        public SqlDataReader GetDataReader(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring, connection);
            SqlDataReader Dr = mycom.ExecuteReader();
            return Dr;
        }
        /// <summary>
        /// 输入SQL命令，得到Dataset
        /// </summary>
        public DataSet GetDataSet(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring, connection);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = mycom;

            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            close();
            return dataset;
        }
        /// <summary>
        /// 执行非查询命令SQL命令
        /// </summary>
        public int ExecuteSQL(string sqlstring)
        {
            int count = -1;
            open();
            try
            {
                SqlCommand cmd = new SqlCommand(sqlstring, connection);
                count = cmd.ExecuteNonQuery();
            }
            catch
            {
                count = -1;
            }
            finally
            {
                close();
            }
            return count;
        }
        /// <summary>
        /// 输入SQL命令，检查数据表中是否有该数据信息
        /// </summary>
        public int GetdataRow(string sqlstring)
        {
            int CountRow = 0;
            open();
            SqlCommand mycom = new SqlCommand(sqlstring, connection);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = mycom;
            DataSet ds = new DataSet();
            da.Fill(ds);

            ds.CaseSensitive = false;
            CountRow = ds.Tables[0].Rows.Count;//取行集合中的元素的总数
            close();
            return CountRow;
        }
        /// <summary>
        /// 输入SQL命令，得到DataTable对象
        /// </summary>
        public DataTable GetDataTable(string sqlstring)
        {
            DataSet ds = GetDataSet(sqlstring);
            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            return dt;

        }

        /// <summary>
        /// 获取单个值
        /// </summary>
        public object GetScalar(string sqlstring)
        {
            open();
            SqlCommand mycom = new SqlCommand(sqlstring, connection);
            object result = mycom.ExecuteScalar();
            close();
            return result;
        }
        /// <summary>
        /// 对整体数据集实施批量更新；一般用于列表这样的对象
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="sql">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool doUpdate(DataSet ds, String sql, String tableName)
        {
            bool flag = false;
            open();
            //强制资源清理;清理非托管资源，不受GC控制的资源。Using结束后会隐式的调用Disposable方法。
            using (SqlDataAdapter da = new SqlDataAdapter(sql, connection))
            {
                //数据库表一定要有主键列 否则此处通不过
                SqlCommandBuilder builder = new SqlCommandBuilder(da);
                try
                {
                    lock (this)
                    {
                        da.Update(ds, tableName);
                        flag = true;
                    }
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
            close();
            return flag;
        }
        /// <summary>
        /// 查询某张表的某列属性的数据，并形成列表
        /// </summary>
        /// <param name="sqlstring">查询SQL字串</param>
        /// <param name="m">第m列的属性，整数类型</param>
        /// <returns></returns>
        public ArrayList GetListArray(string sqlstring,int m)
        {
            ArrayList array = new ArrayList();//创建ArrayList对象 
            SqlDataReader dr = GetDataReader(sqlstring);
            while (dr.Read())//遍历所有结果集 
            {
                array.Add(dr.GetValue(m));//取到结果集索引的第0列的值并添加到ArrayList对象中 
            }
            return array;//返回ArrayList对象 
        }
        
    }
}
