Winform操作Access

有時(shí)候做數(shù)據(jù)的中轉(zhuǎn)，SQLServer和Oracle這些大型數(shù)據(jù)庫(kù)有點(diǎn)殺雞用牛刀，而且會(huì)增加維護(hù)成本，這時(shí)可以使用Access數(shù)據(jù)庫(kù)，尤其是處理Winform的時(shí)候。下面簡(jiǎn)單說(shuō)一下 Access的數(shù)據(jù)訪問(wèn)類(lèi)的使用方法，該類(lèi)為靜態(tài)方法，如果是多線程程序，可能會(huì)造成數(shù)據(jù)庫(kù)連接之

有時(shí)候做數(shù)據(jù)的中轉(zhuǎn)，SQLServer和Oracle這些大型數(shù)據(jù)庫(kù)有點(diǎn)“殺雞用牛刀”，而且會(huì)增加維護(hù)成本，這時(shí)可以使用“Access”數(shù)據(jù)庫(kù)，尤其是處理“Winform”的時(shí)候。下面簡(jiǎn)單說(shuō)一下
Access的數(shù)據(jù)訪問(wèn)類(lèi)的使用方法，該類(lèi)為靜態(tài)方法，如果是多線程程序，可能會(huì)造成“數(shù)據(jù)庫(kù)”連接之間的競(jìng)爭(zhēng)。比如一個(gè)線程打開(kāi)了數(shù)據(jù)庫(kù)連接，還沒(méi)有處理完，另一個(gè)線程就要關(guān)閉，這時(shí)就不能使用這個(gè)類(lèi)了。

1、類(lèi)如下。
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OleDb;

namespace Model
{
///

/// DataAccess 的摘要說(shuō)明 C#操作Access實(shí)例解析
///

public class DataAccess
{
protected static OleDbConnection conn = new OleDbConnection();
protected static OleDbCommand comm = new OleDbCommand();
public DataAccess()
{
//init C#操作Access實(shí)例解析
}
private static void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
conn.ConnectionString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + ConfigurationManager.AppSettings["myconn"];
//web.config文件里設(shè)定。
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception e)
{ throw new Exception(e.Message); }

}

}//打開(kāi)數(shù)據(jù)庫(kù) C#操作Access實(shí)例解析

private static void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
}//關(guān)閉數(shù)據(jù)庫(kù) C#操作Access實(shí)例解析

public static void excuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{ closeConnection(); }
}//執(zhí)行sql語(yǔ)句 C#操作Access實(shí)例解析

public static OleDbDataReader dataReader(string sqlstr)
{
OleDbDataReader dr = null;
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;

dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
dr.Close();
closeConnection();
}
catch { }
}
return dr;
}
//返回指定sql語(yǔ)句的OleDbDataReader對(duì)象，使用時(shí)請(qǐng)注意關(guān)閉這個(gè)對(duì)象。
public static void dataReader(string sqlstr, ref OleDbDataReader dr)
{
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
if (dr != null && !dr.IsClosed)
dr.Close();
} //C#操作Access實(shí)例解析
catch
{
}
finally
{
closeConnection();
}
}
}
//返回指定sql語(yǔ)句的OleDbDataReader對(duì)象,使用時(shí)請(qǐng)注意關(guān)閉

public static DataSet dataSet(string sqlstr)
{
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);

}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return ds;
}//返回指定sql語(yǔ)句的dataset C#操作Access實(shí)例解析

public static void dataSet(string sqlstr, ref DataSet ds)
{
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}//返回指定sql語(yǔ)句的dataset C#操作Access實(shí)例解析

public static DataTable dataTable(string sqlstr)
{
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dt;
}//返回指定sql語(yǔ)句的datatable
public static void dataTable(string sqlstr, ref DataTable dt)
{
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}//返回指定sql語(yǔ)句的datatable C#操作Access實(shí)例解析

public static DataView dataView(string sqlstr)
{
OleDbDataAdapter da = new OleDbDataAdapter();
DataView dv = new DataView();
DataSet ds = new DataSet();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
dv = ds.Tables[0].DefaultView;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dv;
}
//返回指定sql語(yǔ)句的dataview C#操作Access實(shí)例解析

}

}

2、配置文件中連接字符串

3、查詢(xún)及判斷數(shù)據(jù)存在
string IsExistSQL = " select * from ETLSettings where ETLName='" + name + "'";

if (DataAccess.dataTable(IsExistSQL).Rows.Count == 0)
{}

4、創(chuàng)建表
string ETLCreateSql = "Create TABLE " + name +
" ( DANo VARCHAR NOT NULL, DATime DATETIME NOT NULL, LogTime DATETIME NOT NULL, MeterType VARCHAR NOT NULL, MeterNo VARCHAR NOT NULL, Qty Decimal(18,6) NOT NULL )";

DataAccess.excuteSql(ETLCreateSql);

5、增加及刪除記錄
string ETLSql = " insert into ETLSettings values ('" + name + "','" + name + "'," + "1,0)";

ETLSql = " delete from ETLSettings where ETLName='" + name + "'";

6、刪除表
drop table test

7、Access里插入時(shí)間需要“#XXXXXXXX#”這樣。

DataAccess.excuteSql("insert into " + etlname + " (DANo,DATime,LogTime,MeterType,MeterNo,Qty,Status) values ('"+model.DANo+"',#"+model.DATime+"#,#"+model.LogTime+"#,'"+model.MeterType+"','"+model.MeterNo+"',"+model.Qty+",0)");

8、Access的連接數(shù)

Access是允許同時(shí)有255個(gè)打開(kāi)的連接，注意是打開(kāi)，打開(kāi)并不表示就一定在執(zhí)行查詢(xún)。如果要執(zhí)行查詢(xún)，那是另外的事，和理論支持“255個(gè)并發(fā)連接”不沖突。

Access的連接是串行執(zhí)行，沒(méi)有并行執(zhí)行模式。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com