SqlHelper

using system;
using system.collections.generic;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.linq;
using system.reflection;
using system.text;

namespace mz.models.dal
{

    //可更改为mysql

    public class sqlhelper
    {
        #region fields

        private string connectstring = configurationmanager.connectionstrings["connstr"].tostring();

        #endregion

        #region constructors

        public sqlhelper()
        {

        }

        public sqlhelper(string connectstring)
        {
            this.connectstring = connectstring;
        }

        #endregion

        #region property connectionstring

        public string connectionstring
        {
            get { return this.connectstring; }
        }

        #endregion

        #region method executenonquery

        public int executenonquery(string commandtext)
        {
            return this.executenonquery(commandtext, commandtype.text, null);
        }

        public int executenonquery(string commandtext, sqlparameter[] paras)
        {
            return this.executenonquery(commandtext, commandtype.text, paras);
        }

        public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            int result = 0;
            using (sqlconnection connection = new sqlconnection(this.connectstring))
            {
                connection.open();
                result = this.executenonquery(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return result;
        }

        public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            int result = command.executenonquery();
            command.dispose();

            return result;
        }

        public int executenonquery(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            int result = command.executenonquery();
            command.dispose();

            return result;
        }

        #endregion

        #region method executescalar

        public int executescalar(string commandtext)
        {
            return this.executescalar(commandtext, commandtype.text, null);
        }

        public int executescalar(string commandtext, sqlparameter[] paras)
        {
            return this.executescalar(commandtext, commandtype.text, paras);
        }

        public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            int result = 0;
            using (sqlconnection connection = new sqlconnection(this.connectstring))
            {
                connection.open();
                result = executescalar(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return result;
        }

        public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            object result = command.executescalar();
            command.dispose();

            if (result == null || result + "" == "")
                return 0;

            return convert.toint32(result);
        }

        public int executescalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            object result = command.executescalar();
            command.dispose();

            if (result == null)
                return 0;

            return (int)result;
        }

        #endregion

        #region method executeobjectscalar

        public object executeobjectscalar(string commandtext)
        {
            return this.executeobjectscalar(commandtext, commandtype.text, null);
        }

        public object executeobjectscalar(string commandtext, sqlparameter[] paras)
        {
            return this.executeobjectscalar(commandtext, commandtype.text, paras);
        }

        public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            object result;
            using (sqlconnection connection = new sqlconnection(this.connectstring))
            {
                connection.open();
                result = executeobjectscalar(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return result;
        }

        public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            object result = command.executescalar();
            command.dispose();

            if (result == null || result + "" == "")
                return null;

            return result;
        }

        public object executeobjectscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            object result = command.executescalar();
            command.dispose();

            if (result == null)
                return null;

            return result;
        }

        #endregion

        #region method executestringscalar

        public string executestringscalar(string commandtext)
        {
            return this.executestringscalar(commandtext, commandtype.text, null);
        }

        public string executestringscalar(string commandtext, sqlparameter[] paras)
        {
            return this.executestringscalar(commandtext, commandtype.text, paras);
        }

        public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            string result = "";
            using (sqlconnection connection = new sqlconnection(this.connectstring))
            {
                connection.open();
                result = executestringscalar(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return result;
        }

        public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            object result = command.executescalar();
            command.dispose();

            if (result == null)
                return "";

            return result.tostring();
        }

        public string executestringscalar(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            object result = command.executescalar();
            command.dispose();

            if (result == null)
                return "";

            return result.tostring();
        }

        #endregion

        #region method executedatatable

        public datatable executedatatable(string commandtext)
        {
            return this.executedatatable(commandtext, commandtype.text, null);
        }

        public datatable executedatatable(string commandtext, sqlparameter[] paras)
        {
            return this.executedatatable(commandtext, commandtype.text, paras);
        }

        public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            datatable dt = null;
            using (sqlconnection connection = new sqlconnection(this.connectstring))
            {
                connection.open();
                dt = this.executedatatable(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return dt;
        }

        public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            datatable dt = new datatable();
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            sqldataadapter adapter = new sqldataadapter(command);
            adapter.fill(dt);
            command.dispose();
            adapter.dispose();

            return dt;
        }

        public datatable executedatatable(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            datatable dt = new datatable();
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            sqldataadapter adapter = new sqldataadapter(command);
            adapter.fill(dt);
            command.dispose();
            adapter.dispose();

            return dt;
        }

        #endregion

        #region method executedataset

        public dataset executedataset(string commandtext)
        {
            return this.executedataset(commandtext, commandtype.text, null);
        }

        public dataset executedataset(string commandtext, sqlparameter[] paras)
        {
            return this.executedataset(commandtext, commandtype.text, paras);
        }

        public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            dataset ds = null;
            using (sqlconnection connection = new sqlconnection(connectstring))
            {
                connection.open();
                ds = this.executedataset(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return ds;
        }

        public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            dataset ds = new dataset();
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            sqldataadapter adapter = new sqldataadapter(command);
            adapter.fill(ds);
            command.dispose();
            adapter.dispose();

            return ds;
        }

        public dataset executedataset(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            dataset ds = new dataset();
            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            sqldataadapter adapter = new sqldataadapter(command);
            adapter.fill(ds);
            command.dispose();
            adapter.dispose();

            return ds;
        }

        #endregion

        #region method executeentity
        private static t executedatareader<t>(sqldatareader dr)
        {
            t obj = default(t);
            type type = typeof(t);
            propertyinfo[] propertyinfos = type.getproperties();
            int columncount = dr.fieldcount;
            obj = activator.createinstance<t>();
            foreach (propertyinfo propertyinfo in propertyinfos)
            {
                string propertyname = propertyinfo.name;
                for (int i = 0; i < columncount; i++)
                {
                    string columnname = dr.getname(i);
                    if (string.compare(propertyname, columnname, true) == 0)
                    {
                        object value = dr.getvalue(i);
                        if (value != null && value != dbnull.value)
                        {
                            propertyinfo.setvalue(obj, value, null);
                        }
                        break;
                    }
                }
            }
            return obj;
        }

        public t executeentity<t>(string commandtext)
        {
            return this.executeentity<t>(commandtext, commandtype.text, null);
        }

        public t executeentity<t>(string commandtext, sqlparameter[] paras)
        {
            return this.executeentity<t>(commandtext, commandtype.text, paras);
        }

        public t executeentity<t>(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            t obj = default(t);
            using (sqlconnection connection = new sqlconnection(connectstring))
            {
                connection.open();
                obj = this.executeentity<t>(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return obj;
        }

        public t executeentity<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            t obj = default(t);
            using (sqlcommand cmd = new sqlcommand(commandtext, connection))
            {
                cmd.commandtype = commandtype;
                if(paras!=null)
                cmd.parameters.addrange(paras);
                connection.close();
                connection.open();
                using (sqldatareader dr = cmd.executereader(commandbehavior.closeconnection))
                {
                    while (dr.read())
                    {
                        obj = executedatareader<t>(dr);
                        break;
                    }
                }
            }
            return obj;
        }
        #endregion

        #region method executelist
        public list<t> executelist<t>(string commandtext)
        {
            return this.executelist<t>(commandtext, commandtype.text, null);
        }

        public list<t> executelist<t>(string commandtext, sqlparameter[] paras)
        {
            return this.executelist<t>(commandtext, commandtype.text, paras);
        }

        public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras)
        {
            list<t> list = new list<t>();
            using (sqlconnection connection = new sqlconnection(connectstring))
            {
                connection.open();
                list = this.executelist<t>(commandtext, commandtype, paras, connection);
                connection.close();
            }

            return list;

        }

        public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            list<t> list = new list<t>();

            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, connection);
            using (sqldatareader dr = command.executereader(commandbehavior.closeconnection))
            {
                while (dr.read())
                {
                    t obj = executedatareader<t>(dr);
                    list.add(obj);
                }
            }
            command.dispose();

            return list;
        }

        public list<t> executelist<t>(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            list<t> list = new list<t>();

            sqlcommand command = this.createcommandhelper(commandtext, commandtype, paras, trans);
            using (sqldatareader dr = command.executereader(commandbehavior.closeconnection))
            {
                while (dr.read())
                {
                    t obj = executedatareader<t>(dr);
                    list.add(obj);
                }
            }
            command.dispose();

            return list;
        }
        #endregion

        #region method insertbatch

        public int insertbatch(string commandtext, datatable data, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = this.createcommandhelper(commandtext, commandtype.text, paras, trans);
            sqldataadapter adapter = new sqldataadapter();
            adapter.insertcommand = command;
            int result = adapter.update(data);

            adapter.dispose();
            command.dispose();

            return result;
        }

        #endregion

        #region private method createcommandhelper

        private sqlcommand createcommandhelper(string commandtext, commandtype commandtype, sqlparameter[] paras, sqlconnection connection)
        {
            sqlcommand command = new sqlcommand();
            command.commandtext = commandtext;
            command.commandtype = commandtype;
            command.connection = connection;

            if (paras != null && paras.length > 0)
            {
                foreach (sqlparameter p in paras)
                {
                    /*update 修改无法使用 parameterdirection.output 来输出值的bug*/
                    //sqlparameter paranew = new sqlparameter();
                    if (p != null)
                    {
                        // check for derived output value with no value assigned
                        if ((p.direction == parameterdirection.inputoutput ||
                            p.direction == parameterdirection.input) &&
                            (p.value == null))
                        {
                            p.value = dbnull.value;
                        }
                        /*
                        paranew.parametername = p.parametername;
                        paranew.sqldbtype = p.sqldbtype;
                        paranew.dbtype = p.dbtype;
                        paranew.sourcecolumn = p.sourcecolumn;
                        paranew.value = p.value;
                         */

                        command.parameters.add(p);
                    }

                }
            }

            return command;
        }

        private sqlcommand createcommandhelper(string commandtext, commandtype commandtype, sqlparameter[] paras, sqltransaction trans)
        {
            sqlcommand command = new sqlcommand();
            command.commandtext = commandtext;
            command.commandtype = commandtype;
            command.connection = trans.connection;
            command.transaction = trans;

            if (paras != null && paras.length > 0)
            {
                foreach (sqlparameter p in paras)
                {
                    sqlparameter paranew = new sqlparameter();
                    if (p != null)
                    {
                        // check for derived output value with no value assigned
                        if ((p.direction == parameterdirection.inputoutput ||
                            p.direction == parameterdirection.input) &&
                            (p.value == null))
                        {
                            p.value = dbnull.value;
                        }

                        paranew.parametername = p.parametername;
                        paranew.sqldbtype = p.sqldbtype;
                        paranew.dbtype = p.dbtype;
                        paranew.sourcecolumn = p.sourcecolumn;
                        paranew.value = p.value;
                    }
                    command.parameters.add(paranew);
                }
            }

            return command;
        }

        #endregion
    }
}