All geek questions in one place

Any solution or workaround for serializing SQLParameter to WCF?

I initially defined a set of SQLParameters in my MessageContract to create a simple execution of the stored procedure through the WCF service. Apparently the SQLParameter type is not serializable, so I need some tips on how to proceed here.

Is there any way I can use SQLParameter as part of my WCF contract, or do I need to do something else, like creating a custom class with the same properties as SQLParameter and then creating SQLParameters elsewhere in my code?

UPDATE:
For a further context as to why this situation arises, initially the Windows form client directly contacted the database to retrieve DataSet data for reporting purposes using regular ADO.NET objects. Now the client wants the shared web service to process all reports. This is the best I can think of to handle this without much change.

+3
source share
4 answers

I was relatively unhappy with the accepted answer that:

, , . , . ServiceContracts WCF. , .

, -; , , , , sql, .

WCF SQL-.

, , - db.

Craig H SerializableSqlParam .

SerializableSqlParam :

  • SqlParameter.
  • UTF-16, SQL- .
  • AssemblyQualifiedName, .
  • SqlParameter.

:

SerializedSqlParam sp = new SerializedSqlParam(new SqlParameter("@id", 1));

//or through typecasting:

SqlParameter parameter = new SqlParameter("@id", 1);
SerializedSqlParam sp = (SerializedSqlParam) parameter;

:

SqlParameter parameter = sp.GetSqlParameter();

//or through typecasting

SqlParameter parameter = (SqlParameter) sp;

. , , /; , , . , !

SerializedSqlParam.cs

[DataContract]
public class SerializedSqlParam
{
    [Browsable(false)]
    [DataMember]
    public string CompareInfo { get; set; } 

    [RefreshProperties(RefreshProperties.All)]
    [DataMember]
    public string Direction { get; set; }

    [DataMember]
    public bool IsNullable { get; set; }

    [Browsable(false)]
    [DataMember]
    public int LocaleId { get; set; }

    [Browsable(false)]
    [EditorBrowsable(EditorBrowsableState.Advanced)]
    [DataMember]
    public int Offset { get; set; }

    [DataMember]
    public string ParameterName { get; set; }

    [DefaultValue(0)]
    [DataMember]
    public byte Precision { get; set; }

    [DefaultValue(0)]
    [DataMember]
    public byte Scale { get; set; }

    [DataMember]
    public int Size { get; set; }

    [DataMember]
    public string SourceColumn { get; set; }

    [DataMember]
    public bool SourceColumnNullMapping { get; set; }

    [DataMember]
    public string SourceVersion { get; set; }

    [DataMember]
    public string SqlDbType { get; set; }

    [DataMember]
    public string TypeName { get; set; }

    [DataMember]
    public string UdtTypeName { get; set; }

    [DataMember]
    public string Value { get; set; }

    [DataMember]
    public string ValueType { get; protected set; }

    [DataMember]
    public string XmlSchemaCollectionDatabase { get; set; }
    [DataMember]
    public string XmlSchemaCollectionName { get; set; }
    [DataMember]
    public string XmlSchemaCollectionOwningSchema { get; set; }

    public SerializedSqlParam(SqlParameter p)
    {
        this.CopyProperties(p);
        this.SerializeParameterValue(p);
    }

    public static explicit operator SerializedSqlParam(SqlParameter p)
    {
        return new SerializedSqlParam(p);
    }

    public static explicit operator SqlParameter(SerializedSqlParam p)
    {
        return p.GetSqlParameter(p);
    }

    public SqlParameter GetSqlParameter()
    {
        return this.GetSqlParameter(this);
    }

    public SqlParameter GetSqlParameter(SerializedSqlParam serialized)
    {
        SqlParameter p = new SqlParameter();

        p.ParameterName = serialized.ParameterName;
        p.Precision = serialized.Precision;
        p.Scale = serialized.Scale;
        p.Size = serialized.Size;
        p.IsNullable = serialized.IsNullable;
        p.LocaleId = serialized.LocaleId;
        p.Offset = serialized.Offset;
        p.SourceColumn = serialized.SourceColumn;
        p.SourceColumnNullMapping = serialized.SourceColumnNullMapping;

        p.XmlSchemaCollectionDatabase = serialized.XmlSchemaCollectionDatabase;
        p.XmlSchemaCollectionName = serialized.XmlSchemaCollectionName;
        p.XmlSchemaCollectionOwningSchema = serialized.XmlSchemaCollectionOwningSchema;

        p.TypeName = serialized.TypeName;
        p.UdtTypeName = serialized.UdtTypeName;

        p.Direction = (ParameterDirection)Enum.Parse(typeof(ParameterDirection), serialized.Direction);
        p.CompareInfo = (SqlCompareOptions)Enum.Parse(typeof(SqlCompareOptions), serialized.CompareInfo);
        p.SourceVersion = (DataRowVersion)Enum.Parse(typeof(DataRowVersion), serialized.SourceVersion);

        p.Value = this.DeserializeObject(serialized.Value, Type.GetType(serialized.ValueType));

        return p;
    }

    private void SerializeParameterValue(SqlParameter p)
    {
        if (p.Value.GetType().IsSerializable)
        {
            this.ValueType = this.GetTypeAssemblyQualifiedName(p.Value);
            this.Value = this.SerializeObject(p.Value);
        }
        else
        {
            throw new SerializationException("Cannot serialize the parameter value object. Recast that object into a primitive or class that can be serialized.");
        }
    }

    private void CopyProperties(SqlParameter p)
    {
        this.ParameterName = p.ParameterName;
        this.Precision = p.Precision;
        this.Scale = p.Scale;
        this.Size = p.Size;
        this.IsNullable = p.IsNullable;
        this.LocaleId = p.LocaleId;
        this.Offset = p.Offset;
        this.SourceColumn = p.SourceColumn;
        this.SourceColumnNullMapping = p.SourceColumnNullMapping;

        this.XmlSchemaCollectionDatabase = p.XmlSchemaCollectionDatabase;
        this.XmlSchemaCollectionName = p.XmlSchemaCollectionName;
        this.XmlSchemaCollectionOwningSchema = p.XmlSchemaCollectionOwningSchema;

        this.TypeName = p.TypeName;
        this.UdtTypeName = p.UdtTypeName;

        this.Direction = p.Direction.ToString();
        this.CompareInfo = p.CompareInfo.ToString();
        this.SourceVersion = p.SourceVersion.ToString();

        try
        {
            this.SqlDbType = p.SqlDbType.ToString();
        }
        catch
        {
            this.SqlDbType = null;
        }
    }

    private string SerializeObject(object value)
    {
        if (value == null) return null;

        XmlSerializer serializer = new XmlSerializer(value.GetType());
        XmlWriterSettings settings = new XmlWriterSettings();

        settings.Encoding = new UnicodeEncoding(false, false);
        settings.Indent = false;
        settings.OmitXmlDeclaration = false;

        using (StringWriter textWriter = new StringWriter())
        {
            using (XmlWriter xmlWriter = XmlWriter.Create(textWriter, settings))
            {
                serializer.Serialize(xmlWriter, value);
            }
            return textWriter.ToString();
        }
    }

    private object DeserializeObject(string xml, Type type)
    {
        if (string.IsNullOrEmpty(xml)) return null;

        XmlSerializer serializer = new XmlSerializer(type);

        XmlReaderSettings settings = new XmlReaderSettings();
        using (StringReader textReader = new StringReader(xml))
        {
            using (XmlReader xmlReader = XmlReader.Create(textReader, settings))
            {
                return Convert.ChangeType(serializer.Deserialize(xmlReader), type);
            }
        }
    }

    private string GetTypeAssemblyQualifiedName(object obj)
    {
        return obj.GetType().AssemblyQualifiedName.ToString();
    }
}
+3

- - , " WCF", Data Data Services .

; SqlParameter WCF; . , IMO WCF - WCF, - i.e.

[OperationContract]
Customer[] FindCustomers(string id, string name, string location, ...);

.

0

, . , , , " ". () , DataSet, .

, , . , . ServiceContracts WCF. , .

This works better with automatic unit tests and good coverage code. This will provide the level of confidence needed to do something so radical.

0
source

I just created a simple serialization wrapper for sqlparameter

#region

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;

#endregion

[Serializable]
public class SQLParamSerializationWrapper
{
    #region Constants and Fields

    private SqlParameter param;

    #endregion

    #region Constructors and Destructors

    public SQLParamSerializationWrapper()
    {
        //paramless constructor for serialization
        this.param = new SqlParameter();
    }

    public SQLParamSerializationWrapper(SqlParameter param)
    {
        this.SQLParam = param;
    }

    #endregion

    #region Properties

    public DbType DbType
    {
        get
        {
            return this.SQLParam.DbType;
        }
        set
        {
            this.SQLParam.DbType = value;
        }
    }

    public ParameterDirection Direction
    {
        get
        {
            return this.SQLParam.Direction;
        }
        set
        {
            this.SQLParam.Direction = value;
        }
    }

    public string ParameterName
    {
        get
        {
            return this.SQLParam.ParameterName;
        }
        set
        {
            this.SQLParam.ParameterName = value;
        }
    }

    [XmlIgnore]
    public SqlParameter SQLParam
    {
        get
        {
            return this.param;
        }
        set
        {
            this.param = value;
        }
    }

    public int Size
    {
        get
        {
            return this.SQLParam.Size;
        }
        set
        {
            this.SQLParam.Size = value;
        }
    }

    public object Value
    {
        get
        {
            return this.SQLParam.Value;
        }
        set
        {
            this.SQLParam.Value = value;
        }
    }

    #endregion
}

Then you can use it as follows

Serialize (im using parameter list): -

List<SQLParamSerializationWrapper> procParams = new List<SQLParamSerializationWrapper>();
            SqlParameter startdate = new SqlParameter("dateStart", new DateTime(2011, 9, 5));
            SqlParameter enddate = new SqlParameter("dateEnd", new DateTime(2011, 9, 6));

            SQLParamSerializationWrapper startDateWrapper = new SQLParamSerializationWrapper(startdate);
            SQLParamSerializationWrapper endDateWrapper = new SQLParamSerializationWrapper(enddate);

            procParams.Add(startDateWrapper);
            procParams.Add(endDateWrapper);

            string paramsAsXML = "";

            using (var sw = new StringWriter())
            {
                using (var xw = XmlWriter.Create(sw))
                {
                    XmlSerializer xs = new XmlSerializer(procParams.GetType());
                    xs.Serialize(xw, procParams);
                }
                paramsAsXML = sw.ToString();
            }

Deserialize: -

var procParams = new List<SqlParameter>();

StringReader sr = new StringReader(parm.Value);
                        // Create an instance of the XmlSerializer specifying type.
                        XmlSerializer deserializer = new XmlSerializer(typeof(List<SQLParamSerializationWrapper>));

                        List<SQLParamSerializationWrapper> sqlParamWrapper = (List<SQLParamSerializationWrapper>)deserializer.Deserialize(sr);

                        foreach (var param in sqlParamWrapper)
                        {
                            procParams.Add(param.SQLParam);
                        }
0
source

Source: https://habr.com/ru/post/1705721/

More articles:


All Articles