VS Connect to Access database--the connection string and the method of executing query statement (add and revise, user name check, according to the user to obtain password query)

Access data connection and statement execution operation, not difficult, long time will not be unfamiliar, every time to find information, simply their own collation, record down, when needed, direct view, improve efficiency. Also for beginners reference

1. Connection string

public static string strconn = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + directory.getcurrentdirectory () + "\\* *.mdb; ";

2, data statement execution (adding and removing difference, user name check, according to the user to obtain the password query)

Get all Datasets--Return all data

public static DataSet ExecuteQuery (string strsql)//strsql query statement to execute
{
using (OleDbConnection conn = new OleDbConnection (strconn))
{
Try
{
IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}
OleDbDataAdapter adapter = new OleDbDataAdapter (strSQL, strconn);
DataSet ds = new DataSet ();
Adapter. Fill (DS);
return DS;
}
catch (OleDbException OLE)
{
throw OLE;
}
Finally
{
Conn. Close ();
}
}

}

Execution of a query statement that has been changed or deleted-Returns the number of rows affected

public static int ExecuteNonQuery (string strsql)//strsql the query statement to execute
{
Try
{
using (OleDbConnection conn = new OleDbConnection (strconn))
{
IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}
OleDbCommand cmd = new OleDbCommand (strSQL, conn);
Executing transactions, transactions will control and maintain the consistency and integrity of each operation in the transaction
OleDbTransaction TS = conn. BeginTransaction ();
Cmd. Transaction = ts;
int iRet = cmd. ExecuteNonQuery ();
if (IRet > 0)
{
Ts.commit ();//If the return data is greater than 0, perform the operation
}
Else
{
Ts. Rollback ();//Not 0, rollback
}
return iRet;
}
}
catch (OleDbException OleDbException)
{
Throw OleDbException;
}
}

The query returns the first row of data---Check the password based on the user name (returns a piece of data based on the user name)
public static DataRow ExecuteRow (string strloginsql)//strsql query statement to execute
{
DataRow Row;
using (OleDbConnection conn = new OleDbConnection (strconn))//try linking the database
{
Try
{
IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}
OleDbDataAdapter adapter = new OleDbDataAdapter (Strloginsql, strconn);//Instantiate a data adapter
DataTable ds = new DataTable ();
Adapter. Fill (DS);//populate the adapter with data
if (ds. Rows.Count = = 0)
{
row = null;
}
Else
{
Row = ds. Rows[0];
}
}
catch (Exception ex)
{
return null;
}
Finally
{
Conn. Close ();
}
}
return row;
}

Query results, return the first column of the first row----for checking the weight (whether the registered user name already exists, whether the inserted data already exists)---return 0, 1

public static int ExecuteScalar (string strsql)//strsql the query statement to execute
{
Try
{
using (OleDbConnection conn = new OleDbConnection (strconn))
{
IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}
OleDbCommand cmd = new OleDbCommand (strSQL, conn);
Object obj = cmd. ExecuteScalar ();
if (obj! = null &&!) Convert.isdbnull (obj))
{
return 1;
}
Else
{
return 0;
}
}
}
catch (OleDbException OleDbException)
{
Throw OleDbException;
}
}

Mainly for this method to leave a backup, but also for beginners to refer to the user.

vs. connect to an Access database-the connection string and the method of executing the query (add and revise, user name check, password query according to the user)