WCF from Data Access Layer

Question

I have a problem i am struggling to create a WCF Service Application form this Data Access Layer :

public class DataAccess
{
    private SqlConnection connection = new SqlConnection("Data Source=LAPI;Initial Catalog=PrimierData;Integrated Security=True");
    private SqlDataReader dataReader;
    private SqlCommand command;
    private SqlTransaction transaction = null;
    private SqlParameter[] parameters = null;


    #region Conenction
    public void Open()
    {
        if (connection.State != ConnectionState.Open)
            connection.Open();
    }

    public void Close()
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }

    #endregion




    #region Reader
    /// <summary>
    /// Executes the reader. For MultiRow Search
    /// </summary>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="commandText">The command text.</param>
    /// <returns></returns>
    public SqlDataReader ExecuteReader(CommandType commandType, string commandText,SqlParameter[] readerparams)
    {
        Open();
        command = new SqlCommand(commandText, connection);
        command.CommandType = commandType;
        if (readerparams != null)
        {
            command.Parameters.AddRange(readerparams); 
        }
       this.dataReader = command.ExecuteReader();
        command.Parameters.Clear();
       // Close();
        return this.dataReader;
    }

   #endregion

    #region Execute
    /// <summary>
    /// Executes the non query. For Insert, Update and Delete
    /// </summary>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="commandText">The command text.</param>
    /// <param name="parameters">The parameters.</param>
    /// <returns></returns>
    public int ExecuteNonQuery(CommandType commandType, string commandText,SqlParameter[] nonparams)
    {
        Open();
        command = new SqlCommand(commandText, connection);
        command.CommandType = commandType;
        command.Parameters.AddRange(nonparams);
        int returnValue = command.ExecuteNonQuery();
        command.Parameters.Clear();
        Close();
        return returnValue;
    }

  #endregion

}

I would like to use WCF but i get a Error

Failed to add a service. Service metadata may not be accessible. Make sure your service is running and exposing metadata.

And i tried to code it I am failing hard. The code works that i am using but when creating the WCF i am a complte noob.

[ServiceContract]
public interface IService1
{


   // TODO: Add your service operations here
    [OperationContract]
    void Open();

    [OperationContract]
    void Close();


    [OperationContract]
    SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] readerparams);

    [OperationContract]
    int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] nonparams);
}

and my Service.svc

public class Service1 : IService1
{
    private SqlConnection connection = new SqlConnection("Data Source=LAPI;Initial Catalog=PrimierData;Integrated Security=True");
    private SqlDataReader dataReader;
    private SqlCommand command;
    private SqlTransaction transaction = null;
    private SqlParameter[] parameters = null;


    [OperationContract]
    public void Open()
    {
        if (connection.State != ConnectionState.Open)
            connection.Open();
    }

    [OperationContract]
    public void Close()
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }

    [OperationContract]
    public int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] nonparams)
    {
        Open();
        command = new SqlCommand(commandText, connection);
        command.CommandType = commandType;
        command.Parameters.AddRange(nonparams);
        int returnValue = command.ExecuteNonQuery();
        command.Parameters.Clear();
        Close();
        return returnValue;
    }
    [OperationContract]
    public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] readerparams)
    {
        Open();
        command = new SqlCommand(commandText, connection);
        command.CommandType = commandType;
        if (readerparams != null)
        {
            command.Parameters.AddRange(readerparams);
        }
        this.dataReader = command.ExecuteReader();
        command.Parameters.Clear();
        // Close();
        return this.dataReader;
    }

I don't see any WCF Service code...If this code works you better show what doesn't work... — rene, Aug 16 '12 at 08:15
Are you trying to add WCF service reference to your DAL? You have to be more specific so we can help you. — Piotr Justyna, Aug 16 '12 at 08:23
Yes,am trying to reference to a DAL sorry for being so non-specific. My first post. I will add the error code now. — J03Fr0st, Aug 16 '12 at 08:47
Take a look at WCF Data Services - it looks like you're trying to reinvent the wheel here.... — Tim, Aug 16 '12 at 09:32

score 0 · Answer 1 · answered Aug 16 '12 at 09:05

SqlDataReader cannot be XML serialized. Client application in a service based application, should not know anything about the database and related operations. I would recommend create classes and objects which will be used by the service to send and receive data.

score 0 · Accepted Answer · answered Aug 22 '12 at 05:19

Ok so did this myself oand it worked for me. On the Client Side DAL

public SQLArray[] SQLtoArray(SqlParameter[] parama)
    {
        if (parama != null)
        {
            SqlParameter[] param = parama;
            int lenght = param.Count();
            SQLArray[] unner = new SQLArray[lenght];

            for (int i = 0; i < lenght; i++)
            {
                unner[i] = new SQLArray();
                unner[i].ParamaterName = param[i].ParameterName;
                unner[i].Paramatertype = param[i].SqlDbType;
                unner[i].ParamaterDirection = param[i].Direction;
                unner[i].ParamaterValue = param[i].Value.ToString();
            }
            return unner;
        }
        return null;
    }

And on the Server Side

Interface

[ServiceContract]
[ServiceKnownType(typeof(SqlParameter[]))]
[ServiceKnownType(typeof(object))]
[ServiceKnownType(typeof(SqlDbType))]
[ServiceKnownType(typeof(ParameterDirection))]
[ServiceKnownType(typeof(SqlDateTime))]
public interface IServerService
{
    [OperationContract]
    DataSet ExecuteDataSet(CommandType commandType, string commandText, SQLArray[] dsparams);

    [OperationContract]
    int ExecuteNonQuery(CommandType commandType, string commandText, SQLArray[] nonparams);


}
[DataContract]
[KnownType(typeof(SqlParameter[]))] 
[KnownType(typeof(object))]
[KnownType(typeof(SqlDbType))]
[KnownType(typeof(ParameterDirection))]
[KnownType(typeof(SqlDateTime))]
public class SQLArray
{
  //  private SqlParameter[] array;
    private string paramaterName;
    private string paramaterValue;
    private ParameterDirection paramaterDirection;
    private SqlDbType paramatertype;


   [DataMember]

    public string ParamaterName
    {
        get { return paramaterName; }
        set { paramaterName = value; }
    }
    [DataMember]
    public string ParamaterValue
    {
        get { return paramaterValue; }
        set { paramaterValue = value; }
    }
    [DataMember]
    public ParameterDirection ParamaterDirection
    {
        get { return paramaterDirection; }
        set { paramaterDirection = value; }
    }
    [DataMember]
    public SqlDbType Paramatertype
    {
        get { return paramatertype; }
        set { paramatertype = value; }
    }
}

CSV

[Serializable]
public class ServerService : IServerService
{
    #region Class Members

  // private ServerDataAccess dataAccess;
    SqlConnection connection = new SqlConnection("Data Source=LAPI;Initial Catalog=PrimierData;Integrated Security=True");
   // SqlDataReader dataReader;
    SqlCommand command;
   // SqlTransaction transaction = null;
   // SqlParameter[] parameters = null;

    #endregion

    #region Constructor

   public ServerService()
    {


    }
    public SqlParameter[] ArrayToSQL(SQLArray[] parama)
    {
       // bool outahere = false;
        int count = 0;
        foreach (SQLArray array in parama)
            count++;
        SqlParameter[] unner = new SqlParameter[count];
        for (int i = 0; i < count; i++)
        {
            unner[i] = new SqlParameter();
            unner[i].ParameterName = parama[i].ParamaterName;
            unner[i].SqlDbType = parama[i].Paramatertype;
            unner[i].Direction = parama[i].ParamaterDirection;
            unner[i].Value = parama[i].ParamaterValue;
        }
        return unner;
    }
    public SQLArray[] SQLtoArray(SqlParameter[] parama)
    {
        int count = 0;
        foreach (SqlParameter parameter in parama)
            count++;
        SQLArray[] unner = new SQLArray[count];

        for (int i = 0; i < parama.Count(); i++)
        {
            unner[i] = new SQLArray();
            unner[i].ParamaterName = parama[i].ParameterName;
            unner[i].Paramatertype = parama[i].SqlDbType;
            unner[i].ParamaterDirection = parama[i].Direction;
            unner[i].ParamaterValue = parama[i].Value.ToString();
        }
        return unner;
    }
    #endregion

    public void Open()
    {
        if (connection.State != ConnectionState.Open)
            connection.Open();
    }

    public void Close()
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }

    #region Methods
    /// <summary>
    /// Executes the non query. For Insert, Update and Delete
    /// </summary>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="commandText">The command text.</param>
    /// <param name="parameters">The parameters.</param>
    /// <returns></returns>
    public int ExecuteNonQuery(CommandType commandType, string commandText, SQLArray[] nonparams)
    {
       Open();
        command = new SqlCommand(commandText, connection);
        command.CommandType = commandType;
        command.Parameters.AddRange(ArrayToSQL(nonparams));
        int returnValue = command.ExecuteNonQuery();
        command.Parameters.Clear();
        Close();
        return returnValue;
    }
    public DataSet ExecuteDataSet(CommandType commandType, string commandText, SQLArray[] dsparams)
    {

            Open();
            command = new SqlCommand(commandText, connection);
            command.CommandType = commandType;
            if (dsparams != null)
            {
                command.Parameters.AddRange(ArrayToSQL(dsparams));
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            command.Parameters.Clear();
            Close();

            return dataSet;



    }

WCF from Data Access Layer

2 Answers2