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