Wcf Service using Ado.net to connect with Sql Server

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi All,

I have created on WCf Service. Now I just want to add concepts of Ado.net to connect with Sql Server.

Please help me on this.

Thanks in Advance.

Posted 3-Jul-11 20:23pm

Archana Parate

Updated 3-Jul-11 20:28pm

Ramalinga Koushik

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2011-07-03T20:35:00

Solution 1

You could start with
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx[^]
http://www.web-enable.com/technology/summary/ado.asp[^]

Posted 3-Jul-11 20:35pm

Abhinav S

Comments

Archana Parate 4-Jul-11 2:54am

Dear..

I know Ado.net but I want use it in WCF Service classes.. Like
[OperationContract]
public DataSet GetSPDataSet(string procedureName, SqlCommand command)
{}

When I pass the Parameter SqlCommand , Service does not work.

Same for Dataset,SqlDataReader, etc..

So Please help me on this...

[no name] 4-Jul-11 3:13am

The service connects to the database, the client just sends and receives serialized information and knows nothing about the database. You don't want the client to send the sql command and you cannot use parameters that cannot be serialized.

Archana Parate 4-Jul-11 4:20am

Ok.. It's fine..

Actually I just want to do all transaction like Add,Edit,Update and Delete and Bind GridView through WCF Service.. I'll just call the function of Wcf Service and all transaction will be done through WCF Service. So I just create function like

[OperationContract]
public void FillDropDown(System.Web.UI.WebControls.DropDownList MyDropDown, DataView MyDV)
{
try
{
MyDropDown.DataSource = MyDV;
MyDropDown.DataTextField = MyDV.Table.Columns[1].ColumnName.ToString();
MyDropDown.DataValueField = MyDV.Table.Columns[0].ColumnName.ToString();
MyDropDown.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}

So when I pass Dataview parameter in function , Service does not work..

I am not getting how to solve this problem..

Please reply ASAP with Example

[no name] 4-Jul-11 4:45am

The dataview cannot be serialized.

Ruard · Answer 2 · 2011-07-03T22:46:00

Solution 2

In order to send non-serializable objects you need to create a datacontract. Read these articles for more information:

http://www.wcftutorial.net/Data-Contract.aspx[^]
http://msdn.microsoft.com/en-us/library/ms733127.aspx[^]

Posted 3-Jul-11 22:46pm

Ruard

Member 8328362 · Answer 3 · 2013-05-22T20:28:00

Solution 3

HI ,

I have one answer to this question.

Instead of passing dropdownlist to the service ,you can get the dataset from service and bind it to dropdownlist at client end right?

Posted 22-May-13 20:28pm

Member 8328362

Ashad25 · Answer 4 · 2013-07-21T00:44:00

Hi First you have to define the Method in interface like this

C#

[OperationContract]
DataSet GetEmployees();

This method will return dataset.

Then go to Web.config file and declare your connection

C#

<connectionstrings>
    <add name="FAST" connectionstring="Server=localhost;User id=Ashad-PC\Ashad;Password=;Initial Catalog=FAST_PROD;Integrated Security=SSPI" />
  </connectionstrings>

Then give the implementation for this method in your class

public DataSet GetEmployees()
        {
            SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["FAST"].ConnectionString);
            SqlCommand cmd = new SqlCommand("select * from Employee", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

Create a client application,create the proxy of your wcf service and bind the dataset to dropdownlist of gridview or whatever you want.