Halaman

Tampilkan postingan dengan label MySQL. Tampilkan semua postingan
Tampilkan postingan dengan label MySQL. Tampilkan semua postingan

Minggu, 26 September 2010

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?

Jumat, 24 September 2010

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");
}
}
}

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


The code does not use xml configuration files at all!

Kamis, 23 September 2010

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; }
}
}
  1. 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>
  1. 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>
  1. 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!

Sabtu, 15 Mei 2010

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?

Selasa, 11 Mei 2010

Backup and restore all database between MySQL instances

I have two instances of MySQL, one using 5.0.27 and the other 5.5.3. My requirement is to transfer all database to a new one. I try to backup all database and restore using My SQL Administration Tools. All database successfully restored (imported) to the new instance, but got problem with privilege that reports Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

Then I look at the backup script but could not found command to create/insert user privilege on mysql database. Probably I missed something, and glad to hear from you. I switch the way, and try following steps:

1. Do backup:
shell5027>mysqldump -uroot -ppass --all-database > C:\temp\sqldataku.sql

Then opened the backup script and found commands to create-insert users privileges on mysql database, something like these:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
DROP TABLE IF EXISTS `db`;
CREATE TABLE `db` ( .... )...;
INSERT INTO `db` VALUES ('%','cms','fercms','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),....);

INSERT INTO `user` VALUES ('localhost','root','*7775476747C6AD73358FA54DAED7828A72014B4E','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('%','fercms','*778F407DE7C65307389FA34AAED7828A72014B4E','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0), ...);

mysql.user table contains user list (with passwords) and their global privileges, mysql.db handles users' database-level privileges.

2. Do restore:
shell553>mysql -uroot -ppass < C:\temp\sqldataku.sql

3. Do flush privilege:
shell553>mysql -uroot -ppass
mysql> flush privileges;
mysql>\q

Test the database using myprogram or tools but come with error message: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

4. Do upgrade, by run this command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\

Looking for 'mysql.exe' as: shell553\mysql.exe
Looking for 'mysqlcheck.exe' as: shell553\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
drupal64.access OK
drupal64.actions OK
fkm.phpbb_acl_groups OK
fkm.phpbb_acl_options OK
Running 'mysql_fix_privilege_tables'...
ERROR 1547 (HY000) at line 167: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 168: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 181: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (cp850). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (cp850_general_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
FATAL ERROR: Upgrade failed

Once again I run the command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\
...
...
Running 'mysql_fix_privilege_tables'...
OK

I did the command again, and reported that "This installation of MySQL is already upgraded to 5.5.3-m3, use --force if you still need to run mysql_upgrade".

If these solve my requirement above (I have to see in future), than I suspect that first try using MySQL Administration Tools should work as well.

Senin, 10 Mei 2010

SELECT command denied for table 'proc'

By using MySQL Administrator Tool or phpMyAdmin, we could administer users. For example create Esterina as a new user then grant all available privilege to her. One of the privilige granted was run by this command.

GRANT CREATE ROUTINE ON mydb.* TO esterina@ 'localhost';

The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).

On her desk, she open TOAD, create some table then create a procedure with this command:

DROP PROCEDURE IF EXISTS mydb.'tester';
CREATE PROCEDURE mydb.'tester'(IN stat INT)
BEGIN
set @STATUS=stat;
END;

The procedure created successfully, but she could not see the procedure on TOAD GUI. She will receive an error message:

MySQL Database Error
SELECT command denied to user esterina@ 'localhost'
for table 'proc';

For her to be able to see her procedure, she must contact her boss, then with super user privilege the adminstrator run this:

GRANT SELECT ON mysql.proc TO esterina@ 'localhost';

Esterina back to her workbench, with tons of plan!