Cassandra with C#

1. Using Microsoft Visual Studio, Create New Project for Console Application   

2. Configure the project 

3. My target Framework using NET 5.0 

4. Add Client Driver for Cassandra, click Tools menu ->NuGet Package Manager-> Manage NuGet Packages for Solution, then type CassandraCSharpDriver

so CassandraCSharpDriver package get installed

5. Create program 

using System;
using System.Collections;
using System.Collections.Generic;
using Cassandra;
using Cassandra.Mapping;

namespace Cassandra_con
{

    public class KeyspacesMain
    {
        public string Keyspace_name { get; set; }
        public bool Durable_writes { get; set; }
        public SortedDictionary<string string=""> Replication { get; set; }
    }

    class Program
    {
       const string MyDC = "datacenter1";
       const string MyIP = "localhost";
       const int MyPortNo = 9042;
       const string MyUID = "username";
       const string MyPass = "password";
       
        static void Main(string[] args)
        {
            Console.WriteLine("Hello Cassandra!");

            var cluster = Cluster.Builder()
                .AddContactPoints(MyIP)
                .WithPort(MyPortNo)
//              .WithLoadBalancingPolicy(new DCAwareRoundRobinPolicy(MyDC))
//              .WithAuthProvider(new PlainTextAuthProvider(MyUID, MyPass))
                .Build();

            var session = cluster.Connect();
            Console.WriteLine("Connected to cluster: " + cluster.Metadata.ClusterName);

            IMapper mapper = new Mapper(session);
            IEnumerable<keyspacesmain> datax = mapper.Fetch<keyspacesmain>("SELECT keyspace_name, durable_writes, replication FROM system_schema.keyspaces");
            Console.WriteLine("Keyspace Name      Durable Writes  Replication Factor"); 

            foreach (var row in datax)
            {
                Console.Write("{0}", row.Keyspace_name.PadRight(19));
                Console.Write("{0}     ", row.Durable_writes);
                

SortedDictionary<string string=""> obj = (SortedDictionary<string string="">)row.Replication;
                

                IDictionaryEnumerator dictEnum = obj.GetEnumerator();
                int spaces = 12;
                while (dictEnum.MoveNext())
                {
                    Console.WriteLine("Key= ".PadLeft(spaces) + dictEnum.Key + ", Value= " + dictEnum.Value);
                    spaces += 28;
                }
            }
        }
     }
 }

5. Output

Hello Cassandra!

Connected to cluster: Test Cluster

Keyspace Name      Durable Writes  Replication Factor

system_auth          True                     Key= class, Value= org.apache.cassandra.locator.SimpleStrategy

                                                          Key= replication_factor, Value= 1

system_schema     True                    Key= class, Value= org.apache.cassandra.locator.LocalStrategy

system_distributed True                    Key= class, Value= org.apache.cassandra.locator.SimpleStrategy

                                                          Key= replication_factor, Value= 3

system                    True                    Key= class, Value= org.apache.cassandra.locator.LocalStrategy

system_traces        True                     Key= class, Value= org.apache.cassandra.locator.SimpleStrategy

                                                          Key= replication_factor, Value= 2

So simple!

Create a Control to act like a Button

System.Windows.Forms.UserControl provides an empty control that can be used to create other controls. We might consider to inherit the class to create a control with custom appearance, feature and behaviour.
I then create a control that functionally same as standard button, but my control could not be be assign to AcceptButton and CancelButton property of a form. To allows the control to act like a button on a form, just implement the IButtonControl interface to the control.

public class FerBtn : System.Windows.Forms.UserControl, System.Windows.Forms.IButtonControl
{
}

After visit this, I copy and paste the DialogResult property, the NotifyDefault and PerformClick methods, and add a definition for IsDefault.

private bool mIsDefault;
[Browsable(false)]
public bool IsDefault 
{
  get { return this.mIsDefault; }
  set { this.mIsDefault = value; }
}

Now this control could be assign to AcceptButton and CancelButton property of a form.

Linq to NHibernate

My previous codes show the use of NHibernate and Fluent NHibernate as OR/M with .NET framework. Next, I would refer to Fluent NHibernate and use Linq.

1. Download NHibernate.Linq-2.1.2-GA-Bin to get NHibernate.Linq.dll.
2. Add reference to NHibernate.Linq.dll in this file that we work with, and in the beginning add using NHibernate.Linq;
3. At line 43, add this:

  var x = (from p in session.Linq<Personal>() where p.Member== "Y" select p).Take(5);
  Console.WriteLine("Output #1:");
  foreach (var a in x)
    Console.WriteLine("id= {0}, ket= {1}", a.Id, a.Sex);

  var r = from p in session.Linq<Personal>()
          group p by p.Sex into g
          select new { sex = g.Key, count = g.Count() };
  Console.WriteLine("Output #2:");
  foreach (var a in r)
    Console.WriteLine("Sex = {0}, Count= {1}", a.sex, a.count);

4. In this sample, I add a new table and make mapping to database.

public class Personal
    {
        public virtual string Id { get; private set; }
        public virtual string Member { get; set; }
        public virtual string Sex { get; set; }
    }

public class PersonalMap : ClassMap<Personal>
{
  public PersonalMap()
  {
    Table("employees");
    Id(x => x.Id).Column("pk");
    Map(x => x.Member, "member");
    Map(x => x.Sex, "sex");
  }
}

Below the output, and it's time for me to sleep....good morning..

LINQ to MySQL with DbLinq

Language-Integrated Query (LINQ), allows .NET programs to connect to databases. It is an Object-Relational mapping tool, with some similarities to Hibernate or LLBLGen. LINQ is type-safe, queries get compiled into MSIL on the fly, and your C# WHERE clauses are translated into SQL and sent to SQL server for execution. In short, it makes design of data access layers safer and faster.

Today I have work with LINQ to MySQL, and arrived at DbLinq.
DbLinq is the LINQ provider that allows to use common databases with an API close to Linq to SQL. DbLinq is a reimplementation of System.Data.Linq.dll for use with SQL servers. It supports MySQL, Oracle, PostgreSQL, SQLite, Ingres, Firebird, and SQL Server.

This code use DbLinq 0.20.1 (released 2010-04-16).
1. Download and extract DbLinq to c:\DbLinq-0.20.1
2. Copy run_myMetal.bat to runme.bat, and modify it to
DbMetal.exe /conn:"server=localhost;user id=myname;password=a; port=3306; database=mydb" /provider=MySql /code:FerDBLinq.cs /language:C#
4. Execute runme.bat so we get FerDBLinq.cs. Copy the file into your project folder then use Add Existing items from Solution Explorer.

5. Add reference to DbLinq.dll, DbLinq.MySql.dll
6. Open FerDBLing.cs file, add these in the beginning:

using DbLinq.MySql;
using DbLinq.Data.Linq;
using System.Data.Linq.Mapping;
using MySql.Data.MySqlClient;

Here code snippets that follows:

public partial class myDBS: MySqlDataContext
{
    #region Extensibility Method Declarations
  partial void OnCreated();
    #endregion
    public myDBS(string conStr) : base(new MySqlConnection(conStr)) { }
    public myDBS(MySqlConnection conn): base(conn){}

    public DbLinq.Data.Linq.Table<BUkUBeSaR> BUkUBeSaR
    {
      get { return this.GetTable<BUkUBeSaR>();}
    }

5. Creata a function for testing purpose.

private void linqing()
{
  using (MySqlConnection conn = new MySqlConnection(GconString))
  {
    try
    {
      conn.Open();
      myDBS db = new myDBS(conn);

      var q = (from p in db.PerSoNaL where p.member=="Y";
      Console.WriteLine("Output #1:");
      foreach (var a in q)
        Console.WriteLine("id= {0}, ket= {1}", a.PK, a.sex);
 
      var r = from p in db.PerSoNaL group p by p.sex into g
              select new { sex = g.Key, Count = g.Count() };
      Console.WriteLine("Output #2:");
      ObjectDumper.Write(r);

      var t = db.PerSoNaL.GroupBy(p => p.sex);
      var u = t.Select(g => new {g.Key, Count = g.Count()});
      Console.WriteLine("Output #3:");
      foreach (var a in u)
          Console.WriteLine("sex={0}, count={1}", a.Key, a.Count);

      int cnt = (from p in db.PerSoNaL group p by p.sex).Count();
      Console.WriteLine("Output #4:\nCount={0}", cnt);

      var s=db.PerSoNaL.GroupBy(c=> c.sex).ToList().AsQueryable();
      Console.WriteLine("Output #5:");
      foreach (var a in s)
         Console.WriteLine("sex "+a.Key+ ", count=" +a.Count());
      }
      catch (Exception ex)
      {
         MessageBox.Show(ex.Message);
       }
    }
}

The output:
Dblinq works for me, but I could not resolve about the count(), for Male(L) and Female(P) always return 1. I've read issue 157 and 158, but still locked. Do you have any idea?

Fluent NHibernate with MySQL

NHibernate is an Object Relational Mapping framework, which (as ORM states) maps between relational data and objects. It defines its mappings in an XML format called HBM, each class has a corresponding HBM XML file that maps it to a particular structure in the database.

Fluent NHibernate offers an alternative to NHibernate's standard XML mapping files. Rather than writing XML documents (.hbm.xml files), Fluent NHibernate lets you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability and more concise code. Fluent, XML-less, compile safe, automated, convention-based mappings for NHibernate.

Fluent NHibernate is external to the NHibernate Core, but is fully compatible with NHibernate version 2.1, and is experimentally compatible with NHibernate trunk.

Why replace HBM.XML? While the separation of code and XML is nice, it can lead to several undesirable situations.

• Due to XML not being evaluated by the compiler, you can rename properties in your classes that aren't updated in your mappings.
• XML is verbose; NHibernate has gradually reduced the mandatory XML elements, but you still can't escape the verbosity of XML.
• Repetitive mappings - NHibernate HBM mappings can become quite verbose if you find yourself specifying the same rules over again. For example if you need to ensure all string properties mustn't be nullable and should have a length of 1000, and all ints must have a default value of -1.

To counter these issues Fluent NHibernate does by moving mappings into actual code, so they're compiled along with the rest of your application; rename refactorings will alter your mappings just like they should, and the compiler will fail on any typos. As for the repetition, Fluent NHibernate has a conventional configuration system, where you can specify patterns for overriding naming conventions and many other things; you set how things should be named once, then Fluent NHibernate does the rest.

My post before, shows an example of using NHibernate. Below we modify the previous code to work with Fluent NHibernate.

1. Download fluentnhibernate-1.1.zip then extract.
2. Create a console application using Visual Studio and named as FluentNHibernateSample.
3. Add reference to FluentNHibernate.dll, NHibernate.ByteCode.Castle.dll, NHibernate.dll. In this sample we work with MySQL, so add MySql.Data.dll and remember to set Copy Local property to true.
4. Here the modified version of my previous code:

using System;
using System.Collections.Generic;
using System.Collections;
using MySql.Data.MySqlClient;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using System.Linq;
using FluentNHibernate.Mapping;

namespace FluentNHibernateSample
{
  class Program
  {
    static void Main(string[] args)
    {
       FluentConfiguration config = Fluently.Configure()
          .Database(MySQLConfiguration.Standard
          .ConnectionString(c => c
             .Server("localhost")
             .Database("mydb")
             .Username("myname")
             .Password("mypass")));
       ISessionFactory factory = config.Mappings(m =>
          m.FluentMappings.AddFromAssemblyOf<Coba>())
          .BuildSessionFactory();

       var session = factory.OpenSession();
       System.Collections.IList myRows = session.CreateCriteria(typeof(Coba)).List();
       foreach (Coba item in myRows)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       IQuery query = session.CreateQuery("from Coba order by ket desc");
       IList<Coba> dta = query.List<Coba>();
       foreach (Coba item in dta)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       query = session.CreateQuery("FROM Coba WHERE kode=:kode");
       query.SetString("kode", "19");
       foreach (Coba item in query.List<Coba>())
         Console.WriteLine("\nResult:\nID:{0} with Ket:{1}", item.Id, item.Ket);
       session.close();
       Console.ReadKey();
     }
   }
   public class Coba
   {
     public virtual string Id{ get; private set; }
     public virtual string Ket {get; set;}
   }

   public class CobaMap : ClassMap<Coba>
   {
     public CobaMap()
     {
        Table("masjob");
        Id(x => x.Id).Column("kode");
        Map(x => x.Ket, "ket");
     }
  }
}

5. Run. We get the same result with my previous code.

The code does not use xml configuration files at all!

NHibernate MySQL

1. Download NHibernate.
2. Create a new C# console application "NHibernateTest", and create a folder "sharelib" that contains library from first step.
3. Make reference to NHibernate.dll, MySql.Data.dll, NHibernate.ByteCode.Castle.dll
4. Type this into Program.cs.
   

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using MySql.Data.MySqlClient;
using NHibernate;
using NHibernate.Cfg;
namespace NHibernateTest
{
  class Program
  {
    static void Main(string[] args)
    {
      Configuration config = new Configuration();
      config.AddAssembly("NHibernateTest");
      config.Configure();

      ISessionFactory factory = config.BuildSessionFactory();
      try
      {
        ISession session = factory.OpenSession();
        IList myRows = session.CreateCriteria(typeof(NHibernateTest.Coba)).List();
        foreach (NHibernateTest.Coba item in myRows)
          Console.WriteLine("Id:{0} - Ket:{1}", item.Id,item.Ket);

        IQuery query = session.CreateQuery("from Coba order by ket desc");
        IList<coba> dta = query.List<coba>();
        foreach (NHibernateTest.Coba item in dta)
          Console.WriteLine("Id:{0} - Ket:{1}",item.Id,item.Ket);

        query = session.CreateQuery("FROM Coba WHERE kode=:kode");
        query.SetString("kode", "19");
        foreach (NHibernateTest.Coba item in query.List<coba>())
          Console.WriteLine("\nResult:\nID:{0} with Ket:{1}",item.Id,item.Ket);
        session.Close();
      }
      catch (Exception ex)
      {
         Console.Out.WriteLine("Error: " + ex.Message.ToString());
      }
      Console.In.Read();
    }
  }

  public class Coba()
  {
    public Coba() {}
    public virtual string Id { get; private set; }
    public virtual string Ket { get; set; }
  }
}

5. Create an XML Mapping File by right-click on the project, add a new item, an named the XML as "NHibernateTest.hbm.xml". Right-click on the file, select "Properties" and change the "Build Action" to "Embedded Resource". Here the contents.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="NHibernateTest.Coba, NHibernateTest" table="masjob" lazy="false">
    <id name="Id" column="kode" type="string">
      <generator class="native"></generator>
    </id>
    <property name="Ket" column="ket" />
  </class>
</hibernate-mapping>

6. Create Configuration File so NHibernate knows the database by right-click on the project, select "New Item...", select "Application configuration File", then type the following to "App.config".

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration"
    type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
  </configSections>
  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="connection.provider">
        NHibernate.Connection.DriverConnectionProvider</property>
      <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver
      </property>
      <property name="connection.connection_string">
        Server=localhost;Database=myDB;User ID=myname;Password=mypass;
      </property>
      <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
      <property name="show_sql">false</property>
      <property name="proxyfactory.factory_class">
        NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
    </session-factory>
  </hibernate-configuration>
</configuration>

7. Assume we already have masjob table on myDB with this structure:

CREATE TABLE masjob (
`kode` int(11) NOT NULL AUTO_INCREMENT,
`ket` varchar(20) NOT NULL,
PRIMARY KEY (`kode`));

We sould have this output. Have a try!

Could not create the driver from NHibernate.Driver.MySqlDataDriver

Here a piece of #C code, I use to create session for NHibernate:

1. Configuration config = new Configuration();
2. config.Configure();
3. ISessionFactory factory = config.BuildSessionFactory();
4.......

Line 3 would raise error something like "Could not create the driver from NHibernate.Driver.MySqlDataDriver"

I pointed to Mysql.Data.dll file in References folder on the Solution Exploreror, than change Copy Global property from false to true. The code could work to open a session.

A generic error occurred in GDI+ and The process cannot access the file...

I have a database that stores data on a server and use directory as a placeholder for .jpg files. In this application, I store images in folders instead of using BLOB data type, so there is a correspondence between each record with image files, and also there are references to several fields with their corresponding image file.

When viewing the data in the DataGridView, images should be shown in Picture Box, using this function.

private void NextPrevRow()
{
  DataRowView drv = (DataRowView)bindSrc0.Current;
  if (drv == null) return;
  string fname = myGlobVar.UNC + "\\f"+drv["pk"].ToString().Trim()+".jpg";
  if (System.IO.File.Exists(fname))
  {
    try
    {
      fotobox.Image = System.Drawing.Image.FromFile(fname);
    }
    catch { }
  }
  else
    fotobox.Image = null;
}

If a user wants to link an image with a record, he chose the image file first and then clicking the save button.
The following function will save the bitmap image that is displayed in the Picture Box as an image file in the directory of the server.

private void SaveImageToFile()
{
  DataRowView drv = (DataRowView)bindSrc0.Current;
  if (fotobox.Image != null)
  {
    string fname = myGlobVar.UNC + "\\f" + drv["pk"].ToString().Trim() + ".jpg";
    try
    {
      if (File.Exists(fname))
        File.Delete(fname);
      fotobox.Image.Save(fname, ImageFormat.Jpeg);
    }
    catch (Exception ex)
    {
      MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
  }
}

Make changing of the image, will raise an error The process cannot access the file '\\10.11.12.13\\f128.jpg' because it is being used by another process.
It occurs when use fotobox.Image = System.Drawing.Image.FromFile(fname); line number 10 of NextPrevRow()function, and these commands in SaveImageToFile() function:

if (File.Exists(fname))
File.Delete(fname);
fotobox.Image.Save(fname, ImageFormat.Jpeg);

If I remark commands to delete files, and leave Save command only in SaveImageToFile() function, an error thrown: A generic error occurred in GDI+.

I modify the commands, error messages about file locking does not exist anymore.

private void NextPrevRow()
{
  DataRowView drv = (DataRowView)bindSrc0.Current;
  if (drv == null) return;
  string fname = myGlobVar.UNC + "\\f"+drv["pk"].ToString().Trim()+".jpg";
  if (System.IO.File.Exists(fname))
  {
    try
    {
      FileStream fs = File.OpenRead(fname);
      byte[] data = new byte[fs.Length];
      fs.Read(data, 0, data.Length);
      fs.Close();

      MemoryStream ms = new MemoryStream(data);
      Bitmap bmp = new Bitmap(ms);
      fotobox.Image = bmp;
      ms.Close();
    }
    catch { }
  }
  else
    fotobox.Image = null;
}


private void SaveImageToFile()
{
  DataRowView drv = (DataRowView)bindSrc0.Current;
  if (fotobox.Image != null)
  {
     string fname = myGlobVar.UNC + "\\f" + drv["pk"].ToString().Trim() + ".jpg";
     try
     {
       // if (File.Exists(fname))
          // File.Delete(fname);
       fotobox.Image.Save(fname, ImageFormat.Jpeg);
     }
     catch (Exception ex)
     {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
  }
}

Specify .NET Framework Target for a Project

We specify a specific .NET Framework target while creating a new project or change it later. For a new project, i.e after a New Project dialog box appear (by clicking Project on the File menu, New Project), we select the .NET Framework version we want from a drop down list.

To change a .NET Framework version:
1. Locate the Project Designer, by right click on a project in the Solution Explorer, then click Properties.

2a. For C#, on the Application tab, locate Target Framework and select .NET Framework version.

2b. For VB, click Compile tab on the Project Designer then click Advanced Compile Options.. from drop down list to choose .NET Framework version.

My list show only one record after doing edit-close form repeatedly

I was coding a form with a datagridview for single table. All Insert, Update, and Delete operations work well and satisfy me. But I saw a strange behaviour, when I did these:

1. Open the form, Edit some data, then click Save button..(data updated successfully!)
2. Close the form.
3. Do step 1-2 repeatedly.

After n times opening-editing-closing the form, the datagridview only show one record???
My list show only one record after doing edit-close form repeatedly.

public partial class frmCoa : mybasefrm.frmMainGrdView
{
  private MySqlDataAdapter gda;
  private DataTable gdt;
  private DBOPS.SqlHelper;

  private void frmCoa_Load(object sender, EventArgs e)
  {
     gHelper = new DBOPS.SqlHelper();
     gda = gHelper.getDataAdapter("SELECT * FROM akun");
     MySqlCommandBuilder gcmdBldr = new MySqlCommandBuilder(gda);
     gdt = new DataTable();
     gda.Fill(gdt);
     bindSrc0.DataSource = gdt;
     bindNav0.BindingSource = bindSrc0;
     dgv.DataSource = bindSrc0;
     ...do binding
  }

  private void btnSave_Click(object sender, EventArgs e)
  {
     try
     {
        Validate();
        bindSrc0.EndEdit();
        gda.Update(gdt);
        startingEdit(false);
        base.buttonNormal();
     }
     catch (System.Exception ex)
     {
        MessageBox.Show("Error: " + ex.Message);
        return;
     }
     dgv.Enabled = true;
  }
}

.

The form has a Query button, then without closing the application, I did some query to display all or some data, all query work as expected.

Don't know why this happened, and my guess is related to connection and data adapter (Fill method). I disable pooling connection in my connection string, and this seems quite resolve my problem. Any idea?

Unlock DataGridView properties in Inherited Form

Form inheritance improve our coding time. But I'm facing a problem while inherit a form that contain DataGridView control. I could not modify the DataGridView properties. Eventhough change the modifier property to protected or public. The DataGridView control still locked in designer.

Here a brief step to work with:
1. I add a new class file for example FerDataGrdView.cs in my library objects (mylib.dll).
2. Type this code:
using System.Windows.Forms;
using System.ComponentModel;
namespace Ferryptk
{
[Designer(typeof( System.Windows.Forms.Design.ControlDesigner))]
public class FerDataGrdView : DataGridView { }
}

3. Build library (mylib.dll).
4. Add reference to mylib.dll
5. Drag the control (FerDataGrdView) in ancestor form.
6. Do form inheritance.
7. Now, I could modify the properties of DataGridView control.

O.. happy day..