Displaying data from sql

MagnvmMagnvm USMember ✭✭✭

Hi Forum
may I ask how to display sql data directly into ListView? I know its a bad practice and it is supposed to be through webservice but in this scenario I have to pull directly from sql and put it to ListView

Tagged:

Answers

  • TobyKTobyK GBMember ✭✭✭

    How are you connecting to sql from Xam.Forms?

  • ChrisColeTechChrisColeTech USMember ✭✭✭

    if you're using entity framework, this is pretty straight forward:

    XAML:

    <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
                 xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
                 xmlns:local="clr-namespace:XamlSamples;assembly=XamlSamples"
                 x:Class="XamlSamples.ListViewDemoPage"
                 Title="ListView Demo Page">
    
        <ListView x:Name="listView"/>
    
    </ContentPage>
    

    C# (in your code behind):

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    using Xamarin.Forms;
    using Xamarin.Forms.Xaml;
    
    namespace XamlSamples
    {
        public partial class ListViewDemoPage : ContentPage
        {
            public ObservableCollection<Employee> Items { get; set; }
    
            public ListViewDemoPage()
            {
                InitializeComponent();
            }
    
            protected override async void OnAppearing()
            {
                base.OnAppearing();
                // this is where you make the database call
                using (ProjectContext context = new ProjectContext())
                {
                    //create a new ObservableCollection of employees
                    //store the ObservableCollection in the Items property
                    Items = new ObservableCollection<Employee>(context.Employees);
                }
    
                //assign items to the list view's ItemSource property
                listView.ItemsSource = Items;
            }
        }
    }
    

    taken from:
    https://docs.microsoft.com/en-us/xamarin/xamarin-forms/user-interface/listview/data-and-databinding

    working examples:
    https://developer.xamarin.com/samples/xamarin-forms/UserInterface/ListView/SwitchEntryTwoBinding/
    https://developer.xamarin.com/samples/xamarin-forms/XamlSamples/

  • MagnvmMagnvm USMember ✭✭✭

    @ChrisColeTech Thanks for a very clear explanation I give it a try

  • MagnvmMagnvm USMember ✭✭✭

    @ChrisColeTech Is ProjectContext in using (ProjectContext context = new ProjectContext()) a new class file? because when I try to type ProjectContext in my project it gives me a hint that I must be referring to assembly so I clicked that suggestion and it automatically generates a new file called ProjectContext.cs with internal class ProjectContent... should I put my database connection inside internal class ProjectContext?

  • MagnvmMagnvm USMember ✭✭✭

    @ChrisColeTech how come I have an error that says
    'ProjectContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'
    because Im on that part of your code that says using (ProjectContext context = new ProjectContext())
    and that was the error I got

  • MagnvmMagnvm USMember ✭✭✭

    @ChrisColeTech can you check my code

    ``public ObservableCollection Items { get; set; }
    public List tempdata;
    public Contactpage ()
    {
    InitializeComponent ();
    BindCustomerRecords();
    Contacts.ItemsSource = tempdata;
    }
    private void BindCustomerRecords()
    {
    DataSet ds = new DataSet();
    SqlConnectionStringBuilder dbConnect = new SqlConnectionStringBuilder();
    dbConnect.DataSource = "mydbdummy.database.windows.net";
    dbConnect.InitialCatalog = "bdbname";
    dbConnect.UserID = "dbadmin123455667";
    dbConnect.Password = "nothingpassword";
    SqlDataAdapter adapter = null;

            using (SqlConnection ConnectedtoDatabase = new SqlConnection(dbConnect.ConnectionString))
            {
                try
                {
                    ConnectedtoDatabase.Open();
                    var str = "SELECT * FROM Table.Cust";
                    var com = new SqlCommand(str, ConnectedtoDatabase);
                    SqlDataReader reader = com.ExecuteReader();
                    Items = new ObservableCollection<User>(reader.CustomerName);
    
                }
                catch
                {
                    Label label = new Label
                    {
                        Text = "Fail on Connecting",
                        FontSize = 16
                    };
                    Content = label;
                }
            }``
    

    Sorry my english is not that good

  • Nadjib_BaitNadjib_Bait DZMember ✭✭✭✭
    DataSet ds = new DataSet();
    SqlConnectionStringBuilder dbConnect = new SqlConnectionStringBuilder();
    dbConnect.DataSource = "mydbdummy.database.windows.net";
    dbConnect.InitialCatalog = "bdbname";
    dbConnect.UserID = "dbadmin123455667";
    dbConnect.Password = "nothingpassword";
    SqlDataAdapter adapter = null;
    

    You are doing it very very wrong... You are putting the database password into your app? It's the same as putting it into a public Twitter post...

    You need to have a web server, your app will communicate with the web server, which will communicate with the database and return the needed records.

    https://devlinduldulao.pro/how-to-create-a-simple-web-service-for-your-xamarin-application/
    https://docs.microsoft.com/en-us/aspnet/core/mobile/native-mobile-backend?view=aspnetcore-2.1

  • MagnvmMagnvm USMember ✭✭✭

    @nadjib I know its wrong and bad practice I already raised to my boss but he insists doing like that anyway so thats it thats why I got in this way of pull data from sql anyway thanks for the references(links)

  • ChrisColeTechChrisColeTech USMember ✭✭✭
    edited June 2018

    @Magnvm said:
    @ChrisColeTech Is ProjectContext in using (ProjectContext context = new ProjectContext()) a new class file? because when I try to type ProjectContext in my project it gives me a hint that I must be referring to assembly so I clicked that suggestion and it automatically generates a new file called ProjectContext.cs with internal class ProjectContent... should I put my database connection inside internal class ProjectContext?

    yes, project context would be a new class.

    it appears you're using ado.net to connect, so your way to access data is a little different than the examples provided....and I agree with @nadjib - you shouldn't be hard coding your password.

    If you insist to do things the wrong way, this is what you should be doing:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    using Xamarin.Forms;
    using Xamarin.Forms.Xaml;
    
    namespace XamlSamples
    {
        public partial class ListViewDemoPage : ContentPage
        {
            public ObservableCollection<User> Items { get; set; }
            public List<User> tempdata;
    
            public Contactpage ()
            {
                InitializeComponent ();
            }
    
    
            protected override async void OnAppearing()
            {
                base.OnAppearing();
                BindCustomerRecords();
                Contacts.ItemsSource = tempdata; 
            }
    
    
            private void BindCustomerRecords()
            {
                DataSet ds = new DataSet();
                SqlConnectionStringBuilder dbConnect = new SqlConnectionStringBuilder();
                dbConnect.DataSource = "mydbdummy.database.windows.net";
                dbConnect.InitialCatalog = "bdbname";
                dbConnect.UserID = "dbadmin123455667";
                dbConnect.Password = "nothingpassword";
    
                SqlDataAdapter adapter = null;
    
                using (SqlConnection ConnectedtoDatabase = new SqlConnection(dbConnect.ConnectionString))
                {
                    try
                    {
                        ConnectedtoDatabase.Open();
                        var str = "SELECT * FROM Table.Cust";
                        var com = new SqlCommand(str, ConnectedtoDatabase);
                        SqlDataReader reader = com.ExecuteReader();
                        Items = new ObservableCollection<User>(reader.CustomerName);
    
                    }
                    catch
                    {
                        Label label = new Label
                        {
                            Text = "Fail on Connecting",
                            FontSize = 16
                        };
                        Content = label;
                    }
                }
            }
    
        }
    }
    

    I would advise you to override the OnAppearing method and do your work there instead of in the constructor. I've ran into null reference exceptions when I do work in the constructor. I prefer overriding the protected override async void OnAppearing() method like above.

    I feel like I can't help much because you're using ado.net

    here is an example of using ado.net to access data:
    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples#adonet-data-provider-examples

    hope this helps,

    good luck

  • Nadjib_BaitNadjib_Bait DZMember ✭✭✭✭

    @ChrisColeTech said:

    @Magnvm said:
    @ChrisColeTech Is ProjectContext in using (ProjectContext context = new ProjectContext()) a new class file? because when I try to type ProjectContext in my project it gives me a hint that I must be referring to assembly so I clicked that suggestion and it automatically generates a new file called ProjectContext.cs with internal class ProjectContent... should I put my database connection inside internal class ProjectContext?

    yes, project context would be a new class.

    it appears you're using ado.net to connect, so your way to access data is a little different than the examples provided....and I agree with @nadjib - you shouldn't be hard coding your password.

    If you insist to do things the wrong way, this is what you should be doing:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    using Xamarin.Forms;
    using Xamarin.Forms.Xaml;

    namespace XamlSamples
    {
    public partial class ListViewDemoPage : ContentPage
    {
    public ObservableCollection Items { get; set; }
    public List tempdata;

    public Contactpage ()
    {
    InitializeComponent ();
    }


    protected override async void OnAppearing()
    {
    base.OnAppearing();
    BindCustomerRecords();
    Contacts.ItemsSource = tempdata;
    }


    private void BindCustomerRecords()
    {
    DataSet ds = new DataSet();
    SqlConnectionStringBuilder dbConnect = new SqlConnectionStringBuilder();
    dbConnect.DataSource = "mydbdummy.database.windows.net";
    dbConnect.InitialCatalog = "bdbname";
    dbConnect.UserID = "dbadmin123455667";
    dbConnect.Password = "nothingpassword";

    SqlDataAdapter adapter = null;

    using (SqlConnection ConnectedtoDatabase = new SqlConnection(dbConnect.ConnectionString))
    {
    try
    {
    ConnectedtoDatabase.Open();
    var str = "SELECT * FROM Table.Cust";
    var com = new SqlCommand(str, ConnectedtoDatabase);
    SqlDataReader reader = com.ExecuteReader();
    Items = new ObservableCollection(reader.CustomerName);

    }
    catch
    {
    Label label = new Label
    {
    Text = "Fail on Connecting",
    FontSize = 16
    };
    Content = label;
    }
    }
    }

    }
    }

    I would advise you to override the OnAppearing method and do your work there instead of in the constructor. I've ran into null reference exceptions when I do work in the constructor. I prefer overriding the protected override async void OnAppearing() method like above.

    I feel like I can't help much because you're using ado.net

    here is an example of using ado.net to access data:
    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples#adonet-data-provider-examples

    hope this helps,

    good luck

    Good luck to his boss too, when a script kiddie grabs the database password and delete/leaks/misuse the data. Anyway thank you for your answer and your help.

  • MagnvmMagnvm USMember ✭✭✭

    @najib and @ChrisColeTech thanks to you both for the help, I know its a bad practice putting password within I tried telling my boss but he insists so I didnt disagree more because my job in on the line if I disagree on him and insists the right way

  • JamesLaveryJamesLavery GBBeta, University ✭✭✭✭✭
    If you have to do it this way then at least don't store the connection information (server, username, password) in variables. Store them in an encrypted file or secure storage and retrieve them at runtime.
Sign In or Register to comment.