C# .NET - Connect client WPF application, WCF and SQL SERVER

Asked By Dom Afonso on 28-Jun-12 05:45 AM
Earn up to 20 extra points for answering this tough question.
I need a c# code to connect a remote wpf application to SQL Server. Basically I wish to use sql server service like WCF in other to retrieve data from internet.
[)ia6l0 iii replied to Dom Afonso on 28-Jun-12 12:30 PM
In order to access a SQL Server database from WPF application, create a SQLConnection object, and point it to the database that you need to use. 
SqlConnection con = new SqlConnection("Data Source=servername;initial catalog=databasename, userid=u; pwd=p;");
And continue your code as usual. 

If you want to connect to a WCF service, right-click on the project and choose "Add Service reference" and browse for the WCF service. And then add a service reference. Then  you would start coding as if you are working with a normal class. 

Hope this helps.
Jitendra Faye replied to Dom Afonso on 29-Jun-12 12:35 AM
For this you need to simply use connection string for that remote sql server ,

means you have to set connection string attribute like this-


use connectionstring tag like this-

<connectionStrings>
  <add
    name="conname"
    connectionString="Data Source=RemoteserverName or IP address;Initial
    Catalog=DataBaseName;Persist Security Info=True;User
    ID=userName;Password=password"
    providerName="System.Data.SqlClient"
  />
</connectionStrings>


bharti odedra replied to Dom Afonso on 29-Jun-12 07:39 AM

 Simple Steps to Connect SQL Server using WCF from SilverLight

1: Create the Service and Data Service Contract

[DataContract]
    public class clsCustomer
    {
      private string _strCustomer;
      private string _strCustomerCode;
 
      [DataMember]
      public string Customer
      {
        get { return _strCustomer; }
        set { _strCustomer = value; }
      }
 
      [DataMember]
      public string CustomerCode
      {
        get { return _strCustomerCode; }
        set { _strCustomerCode = value; }
      }
    }

2: Code the WCF Service

public class ServiceCustomer : IServiceCustomer
    {
      public clsCustomer getCustomer(int intCustomer)
      {
        SqlConnection objConnection = new SqlConnection();
        DataSet ObjDataset = new DataSet();
        SqlDataAdapter objAdapater = new SqlDataAdapter();
        SqlCommand objCommand = new SqlCommand
         ("Select * from Customer where CustomerId=" + intCustomer.ToString());
        objConnection.ConnectionString = 
         System.Configuration.ConfigurationManager.ConnectionStrings
         ["ConnStr"].ToString();
        objConnection.Open();
        objCommand.Connection = objConnection;
        objAdapater.SelectCommand = objCommand;
        objAdapater.Fill(ObjDataset);
        clsCustomer objCustomer = new clsCustomer();
        objCustomer.CustomerCode = ObjDataset.Tables[0].Rows[0][0].ToString();
        objCustomer.Customer = ObjDataset.Tables[0].Rows[0][1].ToString();
        objConnection.Close();
        return objCustomer;
      }
    }

3: Copy the CrossDomain.xml and ClientAccessPolicy.XML File

<?xml version="1.0"?>
    <!DOCTYPE cross-domain-policy SYSTEM 
      "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
    <cross-domain-policy>
    <allow-http-request-headers-from domain="*" headers="*"/>
   </cross-domain-policy>

4: Change the WCF Bindings to ‘basicHttpBinding’

<endpoint address="" binding="basicHttpBinding" 
      contract="WCFDatabaseService.IServiceCustomer">

5: Add Service Reference

 right click the Silverlight project and select add service reference.

6: Define the Grid for Customer Name and Customer Code

<Grid x:Name="LayoutRoot" Background="White">
      <Grid.ColumnDefinitions>
          <ColumnDefinition></ColumnDefinition>
          <ColumnDefinition></ColumnDefinition>
        </Grid.ColumnDefinitions>
      <Grid.RowDefinitions>
          <RowDefinition Height="20"></RowDefinition>
          <RowDefinition Height="20"></RowDefinition>
        </Grid.RowDefinitions>
        <TextBlock x:Name="LblCustomerCode" Grid.Column="0" 
         Grid.Row="0" Text="Customer Code"></TextBlock>
        <TextBlock x:Name="TxtCustomerCode" Grid.Column="1" 
         Grid.Row="0" Text="{Binding Path=CustomerCode}"></TextBlock>
        <TextBlock x:Name="LblCustomerName" Grid.Column="0" 
         Grid.Row="1" Text="Customer Name"></TextBlock>
        <TextBlock x:Name="TxtCustomerName" Grid.Column="1" 
         Grid.Row="1" Text="{Binding Path=Customer}"></TextBlock>
    </Grid>

7: Bind the WCF Service with the GRID

public partial class Page : UserControl
{
    public Page()
    {
      InitializeComponent();
      ServiceCustomerClient obj = new ServiceCustomerClient();
      obj.getCustomerCompleted += new EventHandler<getCustomerCompletedEventArgs>
                 (DisplayResults);
      obj.getCustomerAsync(1);
    }
    void DisplayResults(object sender, getCustomerCompletedEventArgs e)
    {
      LayoutRoot.DataContext = e.Result;
    }
}
bharti odedra replied to Dom Afonso on 29-Jun-12 07:59 AM
try this 
SqlConnection con = new SqlConnection("Data Source=servername;initial catalog=databasename, userid=username; pwd=password;");