Category Archives: Work With Data

How to use common folder paths in C#


Reading files from application path may be one of key requirement of any application. Actually we can place many place you love to have, on a installation machine there can be restriction as well.

Like wise in a Windows system, installer may want to put apps and other files to Programfiles. This folder intended to work with executable,configuration files etc. You can’t programmatically create new folder or files in Programfiles folder, which may require some admin rights. So where we kept those documents and other data ?

It is wise to store such files on Application data folder or even on your Document folder. For reports and other stuffs Document folder is enough.

Access Special Folders

We can use the Enironment.SpecialFolder to access the App data and Documents and also can fetch path to those folders using GetFolderPath method of Environment class.

datapath = Environment.GetFolderPath(Environment.SpecialFolder
.ApplicationData);
reportPath = Environment.GetFolderPath(Environment.
SpecialFolder.MyDocuments);

the first line will get the location of C:\Users\manoj\AppData\Roaming and the second will show the Document folder path .

Access application path

How to access application/executable path in C#. This can be done using Application.ExecutablePath and the GetFolderPath method

 Path.GetDirectoryName(Application.ExecutablePath);

List all SQL Instances on a local machine in C#


This is just another SQL C# articles which let you learn how to make a list of names of SQL instance available on your Microsoft SQL Server using C# code.

This is made possible using Registry entries.

     private IEnumerable<string> ListLocalSqlInstances(RegistryKey hive)
        {
            const string keyName = @"Software\Microsoft\Microsoft SQL Server";
            const string valueName = "InstalledInstances";
            const string defaultName = "MSSQLSERVER";

            using (var key = hive.OpenSubKey(keyName, false))
            {
                if (key == null) return Enumerable.Empty<string>();

                var value = key.GetValue(valueName) as string[];
                if (value == null) return Enumerable.Empty<string>();

                for (int index = 0; index < value.Length; index++)
                {
                    if (string.Equals(value[index], defaultName, StringComparison.OrdinalIgnoreCase))
                    {
                        value[index] = ".";
                    }
                    else
                    {
                        value[index] = @".\" + value[index];
                    }
                }

                return value;
            }
        }


 public IEnumerable<string> ListLocalSqlInstances()
        {
            if (Environment.Is64BitOperatingSystem)
            {
                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }

                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }
            }
            else
            {
                foreach (string item in ListLocalSqlInstances(Registry.LocalMachine))
                {
                    yield return item;
                }
            }
        }

How to use the function

The function has two part, one with argument which dig for registry element another will extract the information.

 txt_instance.ItemsSource = SqlHelper.ListLocalSqlInstances();

The a above line will serve the list of string ( instance names ) and serve as itemsource for a combo box control.

How to Check for a database on SQL Server using C#


This is just another SQL C# articles which let you learn how to check for a database existence on SQL Server.

All you have to do query names in sys.databases table and make sure it is available to use. You can create a function for the purpose.

public static bool IsDBExist(string server, string db)
        {
            List<string> list = new List<string>();

            // Open connection to the database
            string conString = null; ;
            
                conString = "server=" + server + ";uid=sa;";
           
            bool has = false;
            try
            {
                using (SqlConnection con = new SqlConnection(conString))
                {
                    if (con.State == ConnectionState.Open) con.Close();
                    con.Open();


                    // Set up a command with the given query and associate
                    // this with the current connection.
                    using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases where name='" + db + "'", con))
                    {
                        using (IDataReader dr = cmd.ExecuteReader())
                        {

                            while (dr.Read())
                            {
                                list.Add(dr[0].ToString());
                            }
                            if (list.Count > 0) has = true;
                        }
                    }
                    con.Close();
                }
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message.ToString());
            }
            return has;

        }

Sorting CollectionView using Orderby in C#


We already learned Filtering, lambad experssion on collection view, and the last one you may want to know how to get a sorted list from a collection view.

AsEnumerable () function makes it easier to sort collection view. In our example we had a StockView class which hold information for Stock of an inventory.

StockView

class StockView{
  public string Batch{ get; set; }
        public int Qty{ get; set; }
        public string Product { get; set; }
  public DateTime Expiry{ get; set; }
}

Then we had a ListCollection view which hold information of all baches of available products. All I wan is to sort out the list. Following code serve the need

 IOrderedEnumerable<StockView> sorted = null;
  sorted = collectionView.SourceCollection.Cast<StockView>()
.AsEnumerable().OrderBy(z => z.Expiry);

As from the last article on lambada experession we learned how to use them. Here we use lambda fuction sort the collection by

  • Using the underlying sourceobject of the collection to get original data
  • Then we Casting the cource to StockView, which is our ViewClass
  • Invoking AsEnumerable() function to access OrderBy functionality
  • Finally created lambda expression for sorting the list

What Next ? Try yourself , sorting Inventory by ProductWise and BatchWise

Create aggregate function using lambda expressions on collectionView in C#


Hope you need this article for updating your skills. As we seen in the last article, we learned about collection view and how to create filters using them. If you miss just take a look at it. To I will help you learn som lambda expression

Lambda experession ?

Lambda expressions permit the creation of anonymous functions with a very concise syntax. I don’t want explain what lambda expression is , I will show you how to use it. For learn more about lambda please visit Richard Carr’s lackwaspBlog ,he had a detailed guide to lambda.

The following is an example of lambda expression.

(params) => expression

let’s jump into the regular salesinformation example. The sales Collection used to store all sales information such as Customer,Date,Amount,tax,discount etc. It may contain many of them, that is why we used collection or List.

We are going to use Agrgregate feature of collectionview object to create lambda function, for find total sale amount ,tax collected etc

 var total_saleAmount = Salescolletionview.Cast<SalesView>()
.Aggregate<SalesView, double>(0, (totalSale, s) => totalSales += s.Amount);

The SaleCollectionView class Hold information about Sales which is in the type of ViewClass. So first up all we can cast the view to SalesView and then invoke Aggregate. The Aggregate functionality of C#.Net let us create anonymous function using lambda expression.

At first we had initialise the output variable and access the Amount property of viewclass and then add it up.

In the similar manner you can operate on other properties of view class.

 var total_tax = Salescolletionview.Cast<SalesView>().
Aggregate<SalesView, double>(0, (tax, s) => tax+= s.Tax);

ListCollectionView for sorting,filtering data collection


When we wanted to perform filtering, sorting of data over a binded data collection, collection view might be helpful. If the source collection implements INotifyCollectionChanged interface, the changes raised by the CollectionChanged event are reflected to the views.

ListcollectionView Represents the collection view for collections that implement IList as Mictosoft Doc says.

Create a collection view

In order to use ListCollectionView you have to use ObjectModel

using System.Collections.ObjectModel;

Then you can use any list to create ListCollectionView, in this example I used my observable collection of SalesOrder.

ListCollectionView sorderview = null;
collectionview = new ListCollectionView(salesOrderCollection);

Model View Class

class SalesOrderView
    {   public DateTime Date { get; set; }
        public string Customer{ get; set; }        
        public string Amount{ get; set; }
    }

Create a Custom filter

Let’s play with filter, first up all we need to create some custom filter for searching SalesOrders received from customers on a specific date

collectionview.Filter = (e1) =>
                {
                    SaleOrderView sorder = e1 as SaleOrderView ;
                    if (((Convert.ToDateTime(sorder.Date) >= dtp_from.SelectedDate && 
Convert.ToDateTime(sorder.Date) <= dtp_to.SelectedDate)) )
                        return true;
                    return false;
                };

That is it, now you can simply bind the collection view with DataGrid or Listcontrol. For me it is a Grid

 mydatagrid.ItemsSource = collectionview;