none
Loop to get Columns and Values RRS feed

  • Question

  • Hi,

    I would like to ask how can I loop through result from SQL Server to list in a console the columns and values?

    Thanks,

    Jassim

    Sunday, July 24, 2016 4:35 PM

Answers

  • Hi Jassim,

    just set your connection string and your table name in this application and go for it:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
          string connectionString = "Your connectionstring";
          string tableName = "YourTableName";
          var table = LoadTable(connectionString, tableName);
    
          Console.WriteLine(GetColumnsLine(table));
    
          foreach (DataRow row in table.Rows)
          {
            Console.WriteLine(GetRowLine(row));
          }
    
          Console.ReadLine();
        }
    
        private static string GetRowLine(DataRow row)
        {
          return string.Join("\t |", row.ItemArray);
        }
    
        private static string GetColumnsLine(DataTable table)
        {
          return string.Join("\t |", table.Columns
             .Cast<DataColumn>()
             .Select(c => c.ColumnName)
             );
        }
    
        private static DataTable LoadTable(string connectionString, string tableName)
        {
          var table = new DataTable();
          using (var conn = new SqlConnection(connectionString))
          {
            using (var command = conn.CreateCommand())
            {
              // Don't let the user specify the tableName,
              // else this String-concatenation can lead to SQL-Injection. :-)
              command.CommandText = "Select * FROM " + tableName;
              conn.Open();
              table.Load(command.ExecuteReader());
            }
          }
          return table;
        }
      }
    }
    


    Thomas Claudius Huber

    My latest Pluralsight-courses:
    WPF and MVVM: Test Driven Development of ViewModels
    WPF and MVVM: Advanced Model Treatment
    XAML Layout in Depth
    Windows Store Apps - Data Binding in Depth

    twitter: @thomasclaudiush
    homepage: www.thomasclaudiushuber.com

    Sunday, July 24, 2016 5:49 PM
  • Hi Jassim Rahma,

    We could also use entity framework to achieve it. like this:

    #models

    public partial class Blog 
    { 
        public Blog() 
        { 
            Posts = new HashSet<Post>(); 
        } 
     
        public int BlogId { get; set; } 
     
        [StringLength(200)] 
        public string Name { get; set; } 
     
        [StringLength(200)] 
        public string Url { get; set; } 
     
        public virtual ICollection<Post> Posts { get; set; } 
    }


    #Dbcontext

    public partial class BloggingContext : DbContext 
        { 
            public BloggingContext() 
                : base("name=BloggingContext") 
            { 
            } 
     
            public virtual DbSet<Blog> Blogs { get; set; } 
            public virtual DbSet<Post> Posts { get; set; } 
     
            protected override void OnModelCreating(DbModelBuilder modelBuilder) 
            { 
            } 
        }


    #usage:

    using (var db = new BloggingContext()) 
            { 
                // Create and save a new Blog 
                Console.Write("Enter a name for a new Blog: "); 
                var name = Console.ReadLine(); 
     
                var blog = new Blog { Name = name }; 
                db.Blogs.Add(blog); 
                db.SaveChanges(); 
     
                // Display all Blogs from the database 
                var query = from b in db.Blogs 
                            orderby b.Name 
                            select b; 
     
                Console.WriteLine("All blogs in the database:"); 
                foreach (var item in query) 
                { 
                    Console.WriteLine(item.Name); 
                } 
     
                Console.WriteLine("Press any key to exit..."); 
                Console.ReadKey(); 
            } 
    

    For more information about entity framework code first approach, please refer to:

    https://msdn.microsoft.com/en-sg/data/jj200620

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 26, 2016 3:22 AM
    Moderator

All replies

  • Hi Jassim,

    just set your connection string and your table name in this application and go for it:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
          string connectionString = "Your connectionstring";
          string tableName = "YourTableName";
          var table = LoadTable(connectionString, tableName);
    
          Console.WriteLine(GetColumnsLine(table));
    
          foreach (DataRow row in table.Rows)
          {
            Console.WriteLine(GetRowLine(row));
          }
    
          Console.ReadLine();
        }
    
        private static string GetRowLine(DataRow row)
        {
          return string.Join("\t |", row.ItemArray);
        }
    
        private static string GetColumnsLine(DataTable table)
        {
          return string.Join("\t |", table.Columns
             .Cast<DataColumn>()
             .Select(c => c.ColumnName)
             );
        }
    
        private static DataTable LoadTable(string connectionString, string tableName)
        {
          var table = new DataTable();
          using (var conn = new SqlConnection(connectionString))
          {
            using (var command = conn.CreateCommand())
            {
              // Don't let the user specify the tableName,
              // else this String-concatenation can lead to SQL-Injection. :-)
              command.CommandText = "Select * FROM " + tableName;
              conn.Open();
              table.Load(command.ExecuteReader());
            }
          }
          return table;
        }
      }
    }
    


    Thomas Claudius Huber

    My latest Pluralsight-courses:
    WPF and MVVM: Test Driven Development of ViewModels
    WPF and MVVM: Advanced Model Treatment
    XAML Layout in Depth
    Windows Store Apps - Data Binding in Depth

    twitter: @thomasclaudiush
    homepage: www.thomasclaudiushuber.com

    Sunday, July 24, 2016 5:49 PM
  • Hi Jassim Rahma,

    We could also use entity framework to achieve it. like this:

    #models

    public partial class Blog 
    { 
        public Blog() 
        { 
            Posts = new HashSet<Post>(); 
        } 
     
        public int BlogId { get; set; } 
     
        [StringLength(200)] 
        public string Name { get; set; } 
     
        [StringLength(200)] 
        public string Url { get; set; } 
     
        public virtual ICollection<Post> Posts { get; set; } 
    }


    #Dbcontext

    public partial class BloggingContext : DbContext 
        { 
            public BloggingContext() 
                : base("name=BloggingContext") 
            { 
            } 
     
            public virtual DbSet<Blog> Blogs { get; set; } 
            public virtual DbSet<Post> Posts { get; set; } 
     
            protected override void OnModelCreating(DbModelBuilder modelBuilder) 
            { 
            } 
        }


    #usage:

    using (var db = new BloggingContext()) 
            { 
                // Create and save a new Blog 
                Console.Write("Enter a name for a new Blog: "); 
                var name = Console.ReadLine(); 
     
                var blog = new Blog { Name = name }; 
                db.Blogs.Add(blog); 
                db.SaveChanges(); 
     
                // Display all Blogs from the database 
                var query = from b in db.Blogs 
                            orderby b.Name 
                            select b; 
     
                Console.WriteLine("All blogs in the database:"); 
                foreach (var item in query) 
                { 
                    Console.WriteLine(item.Name); 
                } 
     
                Console.WriteLine("Press any key to exit..."); 
                Console.ReadKey(); 
            } 
    

    For more information about entity framework code first approach, please refer to:

    https://msdn.microsoft.com/en-sg/data/jj200620

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 26, 2016 3:22 AM
    Moderator