Handle Sql server and Foxpro database with .NET

• Download XML list - 0.2 KB
• Download source - 8.9 KB

Introduction

This article hopes to provide the reader with rapid application development for database handling. It works not only with the desktop applications but web applications too. This dll is written in the C# language, but you can use it with any of the other language after compiling it.

I am not submitting the dll file because I know it needs a little more modification (connection string, etc.) before it can be used. It can read the connection string in the text file but is not good with web applications, so you may have to make some changes in its Cnn class.

This article also helps those who are using SQL Server or .dbf (FoxPro) as a database. One of its classes, CopySqlAsDbf, has the ability to convert tables from SQL to dbf. To use its FoxPro facility means to use its Dbf and SqlDbfClass classes; you need to download the Microsoft ODBC driver, which is free. (This article assumes that you have that driver)

This ddl is basically made for Windows-based projects, but I used it for web projects too, when I have lots of work to finish within a limited amount of time.

One of the main advantages of this dll, is that it speeds up your development process and it is very easy to understand. It basically has four classes.

1. Cnn class
2. Dbf class
3. Sql class
4. SqlDbfClass class

As the names suggest, all the classes are made for the different purposes.

Let's go one class at a time:

Cnn class

It has two methods, named:

cnnStringValue //for sql server connection string.

    public string cnnStringValue()
    {
        try
        {
            TextReader tr = new StreamReader("connectionString.txt");
            cnnString=tr.ReadLine().ToString().Trim();
        }
        catch//(Exception ex)
        {
            //do nothing
            //throw new Exception(ex.Message);
        }
        return cnnString;
    }

Here I am reading the connectionstring from a text file, which is more convenient for software deployment because sometimes the client is using a proxy, or has different servers for their database, so we give them permission to set values from the text file. In the class Cnn, the variable cnnString has its own predefines values, so that if the file isn't found or any other error occurs then it returns the predefined value. This is because I do not handle the error by throwing an exception. You can do this if you need it, or think it is more convenient for you.

DBFcnnString //for dbf(foxpro)connection string. It simply returns dbfconnection string which is almost fixed. While creating, one thing you should have in your mind is the Application startup path. For example:

Cnn conn=new Cnn(ApplicationStartupPath.Trim());
// will call the following methods:
    public Cnn(string mvarSysProjPath)
    {
        //string mvarSysProjPath =Application.StartupPath;
        DBFString="Provider=MSDASQL/SQLServer ODBC;Driver={
            Microsoft Visual FoxPro Driver};" + "SourceType=DBF;SourceDB=" + 
            mvarSysProjPath.Trim() + ";InternetTimeout=300000;
            Transact Updates=True";
    }

And set the DBFString variables with the connectionString value. Now you can receive that value simply:

string strConnect=conn.DBFcnnString();

In my dll both methods are used for internal method calling. This means both are used by the other classes. You can use these functions according your requirements.

Dbf class

It has three methods, named:

1. ExecuteDbfQuery
2. ReturnDbfTableMaxid
3. ReturnDbfTableValue

In spite of these methods this class has one parameterized constructor and one property, both for the same task. You can choose any one of them while using the class. Now we will look at these methods one by one:

ExecuteDbfQuery

• ExecuteDbfQuery: We can use any methods of this class after setting its ApplicationPath property or by using parametrized constructor

public Dbf(string path)
{
    ApplicationStartupPath=path;
} 

While making the object of the Dbf class, you should pass the application startup path as parameter.

Dbf objDbf = new Dbf(Application.StartupPath);

public void ExecuteDbfQuery(string DbfQuery) method receives the DbfQuery as string. It can execute the Insert update and delete query in the FoxPro database. Just pass your insert query in DbfQuery like this:

objDbf.ExecuteDbfQuery(
    "insert into tablename(column1,column2,……..) 
    values('"+value1+"','"+value2+"',……..)");

ReturnDbfTableMaxid

• ReturnDbfTableMaxid: This is another useful function of this Class. It returns the maximum id or any auto increment field value. You should only pass the tablename and the columnName from which that value resides.

public int ReturnDbfTableMaxid(string tableName , string columnName)

ReturnDbfTableValue

public string ReturnDbfTableValue(string tableName, string columnName,
    string condition) 

This is also a very useful function of this class. It returns a single value of the column field based on the where condition. You should pass only the tablename, columnname (in which that value resides) and the where condition. There is a condition variable with the keyword where; this function generates the query and returns you that value.

Sql Class

It has three methods, named:

1. ExecuteSqlQuery
2. ReturnSqlTableMaxid
3. ReturnSqlTableValue

Similar to the Dbf class, the Sql class also has the same methods to deal with the SQL database. You can also add more methods according to your requirements. A brief introduction of these methods is as follows: you can use any aspect of this function if you place the text file having the connection string in the same folder where this dll resides, generally in the bin folder or you can manually set the cnnString variable in the Cnn class constructor like this:

public Cnn()
{ 
    cnnString="Server=servername/ipaddress;UID=username;
        PWD=password;Database=databasename";
}

And after set this parameter you can compile this dll and reference in your project. Methods are given below.

ExecuteSqlQuery

public void ExecuteSqlQuery(string sqlQuery)

This class simply accepts the SQL insert, update, delete query and execute them, it also throws an exception if it occurs, you can print it if you want. The method calling looks like this:

Sql objSql=new Sql();
objSql.ExecuteSqlQuery("update tablename set name='"+nameVariable+"'");

ReturnSqlTableMaxid

public int ReturnSqlTableMaxid(string tableName , string columnName)

This class returns the SQL table max auto genrated id. It only accepts the tablename and the columnname of that auto generated id.

The method calling looks like this:

Sql objSql=new Sql();
int maxid = objSql.ReturnSqlTableMaxid("tablename", "columnname");

ReturnSqlTableValue

public string ReturnSqlTableValue(string tableName, string columnName, 
    string condition)

This class returns a single sqltable column value based on the conditions. This method accepts the tablename, columnName, the where condition and returns the required value. The method calling looks like this:

Sql objSql=new Sql();
objSql.ReturnSqlTableValue("tablename", "columnname", 
    "where name='"+nameVariable+"'");

SqlDbfClass.cs file

Actually this has a SqlToDbfConvert class having only one method and one property, setApplicationPath. This property is to set the application startup path and the method CopySqlAsDbf to copy SQL table data into the dbf table, but it has some limitations.

public void CopySqlAsDbf(string SqlQuery, string DbfTableName)

This method accepts the SQL select query and dbf table name (only the name) into which you want to copy the data. It automatically creates the table and dumps the data.

Here I use the SqlQuery variable instead of the SQL table name because you can use the where condition here too, so you can got the filtered data into the dbf table.

Steps to use this Dll

1. Right click of the solution explorer, you will see the option add references. Choose "Add references" like this:

   

2. It will now seems like this:

   

3. Now you can use it as follows:
   C#

   using sqlDbfTransactions;

   if using C# you use the above code. You can use it in VB.NET in the same way using imports key word. Even if it is written in C#.

Conclusion

This dll helped me lot when I required fast development not only in the desktop but also in the web, and it is also very useful for those users who are working in the FoxPro database or use mix FoxPro and SQL Server as database.

Later I am adding more functions in this dll according to organizations requirement. You should do the same. I made many dynamic websites in a very short time using this dll. I hope you also enjoy it.