欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlHelper

程序员文章站 2022-08-04 12:14:11
using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System. ......

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
    }
}