how to manage easy connection with m.s access on vb.net.

give me suggestion.

 

You can use Oledb Connection.

 

public abstract class OLEHelper
{

public OLEHelper()
{

}

public static bool TableExists(string connString, string tablename)
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
DataTable schemaTable;// = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });

DataRow[] dr = schemaTable.Select("TABLE_NAME='" + tablename + "'");
if (dr.Length > 0)
{
return true;
}
}
return false;
}


public static int ExecuteNonQuery(string connString, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();

using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;

}
}



public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{

OleDbCommand cmd = new OleDbCommand();

using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}


public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{

OleDbCommand cmd = new OleDbCommand();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}



public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}



public static OleDbDataReader ExecuteReader(string connString, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);

// we use a try/catch here because if the method throws an exception we want to

// close the connection throw code, because no datareader will exist, hence the

// commandBehaviour.CloseConnection will not work

try
{
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// cmd.Parameters.Clear();

return rdr;
}
catch
{
conn.Close();
throw;
}
}



public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);

// we use a try/catch here because if the method throws an exception we want to

// close the connection throw code, because no datareader will exist, hence the

// commandBehaviour.CloseConnection will not work

try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// cmd.Parameters.Clear();

return rdr;
}
catch
{
conn.Close();
throw;
}
}



public static object ExecuteScalar(string connString, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();

using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}



public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();

using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}




public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{

OleDbCommand cmd = new OleDbCommand();

PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}



private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
{

if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}

}

 

thanks for sharing infomation........