using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

namespace UsersDAL

{

    public  class Class1

    {

        //返回受影响的行数

       // static string constr = "server=.;database=OneDb;uid=sa;pwd=123123";

        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        //public  int ExecuteNnonQuery(string sql)

        //{

        //    //string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

        //    using(SqlConnection conn=new SqlConnection(constr))

        //    {

        //        conn.Open();

        //            using(SqlCommand cmd=conn.CreateCommand())

        //            {

        //                cmd.CommandText = sql;

        //                return cmd.ExecuteNonQuery();

        //            }

        //    }

        //}

        public int ExecuteNnonQuery(string sql,params SqlParameter[] parameters)

        {

            //string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(constr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    //foreach(SqlParameter parame in parameters)

                    //{

                    //    cmd.Parameters.Add(parame);

                    //}

                    cmd.Parameters.AddRange(parameters);

                    return cmd.ExecuteNonQuery();

                }

            }

        }

        /// <summary>

        /// 返回首行首列

        /// </summary>

        /// <param name="sql"></param>

        /// <returns></returns>

        public object ExecuteScalar(string sql,params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(constr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(parameters);

                  return  cmd.ExecuteScalar();

                }

            }

        }

        /// <summary>

        /// 返回一个表

        /// </summary>

        /// <param name="sql"></param>

        /// <returns></returns>

        //public DataTable ExecuteDataTable(string sql,SqlParameter[] parameters)

        //{

        //    using (SqlConnection conn = new SqlConnection(constr))

        //    {

        //        conn.Open();

        //        using (SqlCommand cmd = conn.CreateCommand())

        //        {

        //            cmd.CommandText = sql;

        //            cmd.Parameters.AddRange(parameters);

        //            SqlDataAdapter sda = new SqlDataAdapter(cmd);

        //            DataSet ds = new DataSet();

        //            sda.Fill(ds);

        //            return ds.Tables[0];

        //        }

        //    }

        //}

        //使用了长度可变参数

        public DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)

        {

            using (SqlConnection conn = new SqlConnection(constr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(parameters);

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);

                    DataSet ds = new DataSet();

                    sda.Fill(ds);

                    return ds.Tables[];

                }

            }

        }   

    }

}