jeudi 30 avril 2015

Database locked issue while Inserting in same table the Array of more than 1000 records by multiple client

I am facing the big issue. I have created the service stack web services in C# DotNet and Database is SQLite (v4.0.30319). My System hang / Database locked issue, some time SQlite database file also corrupted while inserting/ updating the database table by number of clients (more than one connection). All are update the same table same time. Whle One is updating other try to connect than the system hang. I have tried all the ways but not able to resolve this issue.

How can I force that One One client can update the table at one time other clients can readonly mode.

My code as below

public IDbConnection Db { get; private set; }

OrmLiteConnectionFactory factory = new OrmLiteConnectionFactory("Data Source= C:/Database/db.sqlite;Integrated Security=True;User Instance=True", ServiceStack.OrmLite.Sqlite.SqliteOrmLiteDialectProvider.Instance);

    public bool CreateAnother2(LTHR record)
    {
        try
        {
            using (Db = factory.OpenDbConnection())
            {
                using (var trans = Db.BeginTransaction()  
                {                         
                        // If no error insert data
                        var id = (int)Db.Insert(record, selectIdentity: true);
                        trans.Commit();
                        Db.Close();  
                        return true;                       
                }
            }
        }
        catch (Exception ex)
        {
            if (Db.State == ConnectionState.Open)
            {
                Db.Close();  
            } 
        }
        finally
        {
            if (Db.State == ConnectionState.Open)
            {
                Db.Close();  
            }
        }
    }

Aucun commentaire:

Enregistrer un commentaire