lyfz_repos/铂金版源码/LYFZ.Network/TCPNetworkServer/NetworkSQLHelper.cs

using HPSocketCS.Extended;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace LYFZ.Network.TCPNetworkServer
{
    public class NetworkSQLHelper
    {
        public NetworkSQLHelper()
        {

        }

        /// <summary>
        /// 服务器端接收客户端发送的SQL数据库语句执行请求并处理返回结果
        /// </summary>
        /// <param name="hp_Server">网络连接对象</param>
        /// <param name="header">协议头</param>
        /// <param name="connId">连接ID</param>
        /// <param name="bytes">收到的数据</param>
        public static bool SQLHelperDataRequestProcessed(HPSocketCS.TcpPackServer hp_Server,TcpHeadInfo header, IntPtr connId, byte[] bytes)
        {
            bool ret = false;
            TransferRequestCommand Command = (TransferRequestCommand)Convert.ToInt32(header.RequestCommand);
            SQLHelperReturnData returnData;
            try
            {
                SQLHelperRequestData requestData = (SQLHelperRequestData)DataSetSerializerDeserialize.ObjectDeserializeDecompress(bytes);

                TCP_ClientConnectInfo cInfo = new TCP_ClientConnectInfo(connId.ToInt32(), header);

                try
                {

                    // LYFZ.BLL.BLL_FileLogs.WriteMainLogs(String.Format("接收到命令请求{0},参数{1}", Command.ToString(), recprotocol.Content));

                    if (Command == TransferRequestCommand.SQLHelperData)
                    {
                        returnData = SQLHelperRequestProcessed(cInfo, requestData);//new SQLHelperReturnData("准备执行SQL请求指令...");
                    }
                    else {
                        returnData = new SQLHelperReturnData("非法SQL请求指令，服务器拒绝执行...");
                    }


                }
                catch (Exception ex)
                {
                    returnData = new SQLHelperReturnData("处理SQL请求指令时出错："+ex.Message);
                }

                // LYFZ.BLL.BLL_FileLogs.WriteMainLogs("服务器端返回信息：" + returnProtocol.Content.Trim());

                if (returnData == null)
                {
                    returnData = new SQLHelperReturnData("SQL请求指令失败，没有返回有效的结果");
                }
                returnData.ServerStartTimeStamp = LYFZ.Network.TCPNetworkServer.TCP_NetworkServer.ServerStartTimeStamp; 
                byte[] sendBytes = DataSetSerializerDeserialize.ObjectSerializerCompressionRetBytes(returnData);
                ret = hp_Server.AutoUnpackingAndSend(connId, sendBytes, header.TransportID, DataType.SQLHelper, Command);
                System.GC.Collect();
            }
            catch (Exception ex)
            {
                try
                {
                    returnData = new SQLHelperReturnData("服务器处理客户端SQL请求时出错:" + ex.Message);
                    returnData.ServerStartTimeStamp = LYFZ.Network.TCPNetworkServer.TCP_NetworkServer.ServerStartTimeStamp; 
                    LYFZ.BLL.BLL_FileLogs.WriteMainLogs(returnData.ReturnMessage);
                    byte[] sendBytes = DataSetSerializerDeserialize.ObjectSerializerCompressionRetBytes(returnData);
                    ret = hp_Server.AutoUnpackingAndSend(connId, sendBytes, header.TransportID, DataType.SQLHelper, Command);
                }
                catch
                {

                }
            }
            return ret;
        }

        /// <summary>
        /// 处理SQL命令请求 并返回请求结果
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData SQLHelperRequestProcessed(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData=null;
            SQLHelperRequestType rType = (SQLHelperRequestType)requestData.RequestType;
            switch (rType)
            {
                case SQLHelperRequestType.QueryReturnDataSet:
                    returnData = QueryReturnDataSet(cInfo,requestData);
                    break;

                case SQLHelperRequestType.ExecuteScalar:
                    returnData = ExecuteScalar(cInfo, requestData);
                    break;

                case SQLHelperRequestType.ExecuteNonQuery:
                    returnData = ExecuteNonQuery(cInfo, requestData);
                    break;
                case SQLHelperRequestType.RunProcedure:
                    returnData = RunProcedure(cInfo, requestData);
                    break;
                case SQLHelperRequestType.ExecuteSqlTran:
                    returnData = ExecuteSqlTran(cInfo, requestData);
                    break;
                case SQLHelperRequestType.ExistsSqlConn:
                    returnData = ExistsSqlConn(cInfo, requestData);
                    break;
            }
            return returnData;
        }
        /// <summary>
        /// 检查数据库连接 是否能正常连接数据
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData ExistsSqlConn(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
            SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (!string.IsNullOrEmpty(sqlCommand.DataConnString))
            {
                object retBl = false;
                returnData = new SQLHelperReturnData();
                try
                {
                    retBl = LYFZ.Helper.SQLHelper.ExistsSqlConn(sqlCommand.DataConnString);
                    returnData.ReturnCode = 0;
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }

                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(retBl);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，DataConnString 命令不能为空。");
            }

            return returnData;
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData QueryReturnDataSet(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
            SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (!string.IsNullOrEmpty(sqlCommand.SqlCommandText))
            {
                DataSet ds = null;
                returnData = new SQLHelperReturnData();
                try
                {
                    if (!string.IsNullOrEmpty(sqlCommand.DataConnString))
                    {
                        //SqlParameter[] cmdParms
                        ds = LYFZ.Helper.SQLHelper.Query(sqlCommand.SqlCommandText, sqlCommand.DataConnString, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.CommandTimeout > 0 && sqlCommand.SerSqlParameterList.Count > 0)
                    {
                        ds = LYFZ.Helper.SQLHelper.Query(sqlCommand.SqlCommandText, sqlCommand.CommandTimeout, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.SerSqlParameterList.Count > 0)
                    {
                        ds = LYFZ.Helper.SQLHelper.Query(sqlCommand.SqlCommandText, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.CommandTimeout > 0)
                    {
                        ds = LYFZ.Helper.SQLHelper.Query(sqlCommand.SqlCommandText, sqlCommand.CommandTimeout);
                    }
                    else
                    {
                        ds = LYFZ.Helper.SQLHelper.Query(sqlCommand.SqlCommandText);
                    }
                    returnData.ReturnCode = 0;
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }

                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(ds);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，SqlCommandText 命令不能为空。");
            }

            return returnData;
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData ExecuteScalar(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
            SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (!string.IsNullOrEmpty(sqlCommand.SqlCommandText))
            {
                object mObj = null;
                returnData = new SQLHelperReturnData();
                try
                {
                    if (!string.IsNullOrEmpty(sqlCommand.DataConnString))
                    {
                        mObj = LYFZ.Helper.SQLHelper.GetSingle(sqlCommand.SqlCommandText, sqlCommand.DataConnString);
                    }
                    else if (sqlCommand.SerSqlParameterList.Count > 0)
                    {
                        mObj = LYFZ.Helper.SQLHelper.GetSingle(sqlCommand.SqlCommandText, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.CommandTimeout > 0)
                    {
                        mObj = LYFZ.Helper.SQLHelper.GetSingle(sqlCommand.SqlCommandText, sqlCommand.CommandTimeout);
                    }
                    else
                    {
                        mObj = LYFZ.Helper.SQLHelper.GetSingle(sqlCommand.SqlCommandText);
                    }
                    returnData.ReturnCode = 0;
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }
                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(mObj);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，SqlCommandText 命令不能为空。");
            }

            return returnData;
        }

        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData ExecuteNonQuery(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
            SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (!string.IsNullOrEmpty(sqlCommand.SqlCommandText))
            {
                object mObj = null;
                returnData = new SQLHelperReturnData();
                try
                {
                    if (!string.IsNullOrEmpty(sqlCommand.DataConnString))
                    {
                        mObj = LYFZ.Helper.SQLHelper.ExecuteSql(sqlCommand.SqlCommandText, sqlCommand.DataConnString, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.CommandTimeout > 0 && sqlCommand.SerSqlParameterList.Count > 0)
                    {
                        mObj = LYFZ.Helper.SQLHelper.ExecuteSql(sqlCommand.SqlCommandText, sqlCommand.CommandTimeout, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.SerSqlParameterList.Count > 0)
                    {
                        mObj = LYFZ.Helper.SQLHelper.ExecuteSql(sqlCommand.SqlCommandText, sqlCommand.ToSqlParameters());
                    }
                    else if (sqlCommand.CommandTimeout > 0)
                    {
                        mObj = LYFZ.Helper.SQLHelper.ExecuteSqlByTime(sqlCommand.SqlCommandText, sqlCommand.CommandTimeout);
                    }
                    else
                    {
                        mObj = LYFZ.Helper.SQLHelper.ExecuteSql(sqlCommand.SqlCommandText);
                    }

                    returnData.ReturnCode = 0;
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }
                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(mObj);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，SqlCommandText 命令不能为空。");
            }

            return returnData;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData RunProcedure(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
            SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (!string.IsNullOrEmpty(sqlCommand.SqlCommandText))
            {
                DataSet ds = null;
                returnData = new SQLHelperReturnData();
                try
                {
                    System.Data.SqlClient.SqlParameter[] mySqlParameters = sqlCommand.ToSqlParameters();
                     if (sqlCommand.CommandTimeout > 0)
                    {
                        ds = LYFZ.Helper.SQLHelper.RunProcedure(sqlCommand.SqlCommandText, mySqlParameters, sqlCommand.AdditionalInfo, sqlCommand.CommandTimeout);
                    }
                    else
                    {
                        ds = LYFZ.Helper.SQLHelper.RunProcedure(sqlCommand.SqlCommandText, mySqlParameters, sqlCommand.AdditionalInfo);
                    }
                     foreach (System.Data.SqlClient.SqlParameter par in mySqlParameters)
                     {
                         if (par.Direction != ParameterDirection.Input)
                         {
                             returnData.SerOutputSqlParameterList.Add(new SerSqlParameter(par));
                         }
                     }
                    returnData.ReturnCode = 0;
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }

                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(ds);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，SqlCommandText 命令不能为空。");
            }

            return returnData;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="cInfo"></param>
        /// <param name="requestData"></param>
        /// <returns></returns>
        static SQLHelperReturnData ExecuteSqlTran(TCP_ClientConnectInfo cInfo, SQLHelperRequestData requestData)
        {
            SQLHelperReturnData returnData = null;
           // SerSqlCommandItme sqlCommand = requestData.GetSerSqlCommand;
            if (requestData.SerSqlCommandList.Count>0)
            {
                object obj = null;
                returnData = new SQLHelperReturnData();
                try
                {
                    if (requestData.SerSqlCommandList.Count > 0)
                    {
                        obj = LYFZ.Helper.SQLHelper.ExecuteSqlTran(requestData.SerSqlCommandList);
                        returnData.ReturnCode = 0;
                    }
                    else {
                        returnData.ReturnMessage = "SQL指令请求的事务语句不能为空.";
                    }
                }
                catch (SqlException ex)
                {
                    returnData.ReturnMessage = "SQL指令请求失败：" + ex.Message;
                }

                returnData.DataByte = HPSocketCS.Extended.DataSetSerializerDeserialize.ObjectSerializerRetBytes(obj);
            }
            else
            {
                returnData = new SQLHelperReturnData("SQL请求指令失败，事务 SqlCommandText 命令不能为空。");
            }

            return returnData;
        }
    }
}