Programmer:Create BCE.Data.DBSetting with Database Info

From AutoCount Resource Center

Introduction

This example uses DevExpress components. 
When create a form Setting for user to maintain which Database to link/integrate,
often we do not know where to obtain the account book setting in AutoCount Accounting.

This example shows how to get Database Info that is maintained and stored in AutoCount Accounting,
by using the class of BCE.AutoCount.DatabaseManagement.
Download Sample Project (Visual Studio 2015)

Sample to illustrate how to get Database Info that is stored in Database Management File.




Obtain Database Info via DatabaseManager

References of AutoCount Accounting 1.8

BCE.AutoCount.dll
BCE.AutoCount.CommonAccounting.dll
BCE.AutoCount.MainEntry.dll
BCE.Utils.dll
BCE.Utils.UI.dll



1. Create a class to store Selected Database Info
[Optional, you may direct save the value to file or database without this class] 

internal class SelectedDatabaseInfo
{
    public string CompanyName { get; set; }
    public string Remark { get; set; }
    public string DBName { get; set; }
    public string SvrName { get; set; }
}



2. Create a DevExpress Lookup Edit control in the form class 

private DevExpress.XtraEditors.LookUpEdit lueDatabase = new DevExpress.XtraEditors.LookUpEdit();

//Object mydbInfo is optional, if the setting of database info is directly write to file or database
private SelectedDatabaseInfo mydbInfo;



3. Add a method that will Load Database Info into the LookupEdit.

You may use other control to get user selection. Below example is using LookupEdit of DevExpress control.
Highlighted lines are the key to obtain Database Info that has been maintained in AutoCount Accounting 1.8.


There are two posibilities:-
  1. Get Database Info after user has login, such as when develop an inter-billing system.
  2. Get Database Info before user login, such as a standalone program that needs to access database info.

Obtain Database Info after user login

This method is only possible after user has login into AutoCount Accounting, else the database info table will be empty. 

internal void InitiateDefaultDatabaseInfoLookup(System.Windows.Forms.Control parent)
{
    //Get the database info that has been attached in AutoCount Accounting
    DataTable dtDBInfo = 
        BCE.AutoCount.DatabaseManagement.Default.DatabaseManager
        .DatabaseInfoTable.DefaultView.ToTable(
            false, new string[] { "CompanyName", "Remark", "DatabaseName", "ServerName" });

    //Set the property of LookupEdit control
    lueDatabase.Properties.DataSource = dtDBInfo;
    lueDatabase.Properties.ValueMember = "DatabaseName";
    lueDatabase.Properties.DisplayMember = "CompanyName";
    lueDatabase.Properties.NullText = "";
    lueDatabase.Properties.PopupWidth = 500;
    lueDatabase.Location = new System.Drawing.Point(10, 10);
    lueDatabase.Size = new System.Drawing.Size(200, lueDatabase.Size.Height);

    //Add LookupEdit Control to Form or Panel, depends on the parent control
    parent.Controls.Add(lueDatabase);
}

Obtain Database Info before user login

To get the database info table before user login, LoadDatabaseManagementFile(...) method needs to be called. 

private void InitiateDatabaseInfoLookup(System.Windows.Forms.Control parent)
{
    //Set the path to where the Database Info file is. (dmf: Database Management File)
    string appDataPath = System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
    string defaultDBInfoFile = System.IO.Path.Combine(appDataPath + "\\AutoCount\\Accounting", "A2006.dmf");

    //Get the database info that has been maintained in AutoCount Accounting
    BCE.AutoCount.DatabaseManagement dbMgmt = new BCE.AutoCount.DatabaseManagement();

    //Load Database Info from Database Management File at designated path
    dbMgmt.LoadDatabaseManagementFile(defaultDBInfoFile, false);
    DataTable dtDBInfo = dbMgmt.DatabaseManager.DatabaseInfoTable.DefaultView.ToTable(
            false, new string[] { "CompanyName", "Remark", "DatabaseName", "ServerName" });

    //Set the property of LookupEdit control
    lueDatabase.Properties.DataSource = dtDBInfo;
    lueDatabase.Properties.ValueMember = "DatabaseName";
    lueDatabase.Properties.DisplayMember = "CompanyName";
    lueDatabase.Properties.NullText = "";
    lueDatabase.Properties.PopupWidth = 500;
    lueDatabase.Location = new System.Drawing.Point(10, 10);
    lueDatabase.Size = new System.Drawing.Size(200, lueDatabase.Size.Height);

    //Add LookupEdit Control to Form or Panel, depends on the parent control
    parent.Controls.Add(lueDatabase);
}



4. On Form closing or when perform saving, store the database info.

Sample code here stores in an object of SelectedDatabaseInfo.
private void OnFormClosingOrOnButtonClickSave()
{
    DataRowView row = lueDatabase.GetSelectedDataRow() as DataRowView;
    if (row != null)
    {
        mydbInfo = new SelectedDatabaseInfo();
        mydbInfo.CompanyName = row["CompanyName"].ToString();
        mydbInfo.Remark = row["Remark"].ToString();
        mydbInfo.DBName = row["DatabaseName"].ToString();
        mydbInfo.SvrName = row["ServerName"].ToString();

        //Refer to number 5
        SaveSetting(mydbInfo);
    }
}



5. Write your algorithm to save the selected Database Info. 

internal void SaveSetting(SelectedDatabaseInfo dbInfo)
{
    if (dbInfo == null)
    {
        //throw or log error.
    }
    //Write saving code here
}



6. Write some code to load the selected Database Info. 

internal SelectedDatabaseInfo LoadSetting()
{
    SelectedDatabaseInfo dbInfo = new SelectedDatabaseInfo();

    //Write loading code here, and assign value to dbInfo.

    return dbInfo;
}



7. Get BCE.Data.DBSetting with the selected database info.

The return value of DBSetting is null, if Database Info to connect to database server is invalid.
internal BCE.Data.DBSetting GetConnectionToAutoCount(SelectedDatabaseInfo dbInfo)
{
    if (dbInfo == null)
    {
        //throw error;
        //or log error and return null
    }

    return new BCE.Data.DBSetting(BCE.Data.DBServerType.SQL2000, dbInfo.SvrName, dbInfo.DBName);
}



Go to menu

Go to top
Resources For AutoCount Software Developers


Retrieved from "https://wiki.autocountsoft.com/wiki/Programmer:Create_BCE.Data.DBSetting_with_Database_Info?oldid=9041"