访问远程Access数据库,使用SqlServer 2005的连接服务器实现
2010-04-23 21:45 c#在路上 阅读(1671) 评论(0) 编辑 收藏 举报用SqlServer 连接服务器实现,c#Ado.net 编程实现。
连接服务器的建立及一些语句如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--Create a linked server.
EXEC sp_addlinkedserver
@server = 'txtsrv1',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\test.mdb'
GO
--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv1, FALSE, sa, NULL
GO
--List the tables in the linked server.
EXEC sp_tables_ex txtsrv1
GO
--Query one of the tables: file1#txt
--using a four-part name.
SELECT *
FROM txtsrv1...[department]
SELECT *
FROM txtsrv1...[testtable]
SELECT *
FROM OPENQUERY(txtsrv1, 'SELECT * from testtable') as queyr1;
--Insert data
INSERT OPENQUERY(txtsrv1,'SELECT col1,col2 FROM testtable') values('REMOTTING1','REMOTTIN1')
insert into txtsrv1...testtable values ('remottin2','remottig2')
insert into txtsrv1...testtable values ( '123', '456')
--update data
UPDATE OPENQUERY (txtsrv1, 'SELECT col2 FROM testtable where col1=id') SET col2 = 'ADifferentName',id='33'
update txtsrv1...testtable set col1='456' where col2='33'
--delete data
DELETE OPENQUERY (txtsrv1, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
delete from txtsrv1...testtable where col1='33'
--Delete link server login user
EXEC sp_droplinkedsrvlogin 'txtsrv1', 'sa'
--Dlete link server
EXEC sp_dropserver 'txtsrv1'
--Query all link server
EXEC sp_helpserver;
EXEC sp_addlinkedserver
@server = 'txtsrv1',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\test.mdb'
GO
--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv1, FALSE, sa, NULL
GO
--List the tables in the linked server.
EXEC sp_tables_ex txtsrv1
GO
--Query one of the tables: file1#txt
--using a four-part name.
SELECT *
FROM txtsrv1...[department]
SELECT *
FROM txtsrv1...[testtable]
SELECT *
FROM OPENQUERY(txtsrv1, 'SELECT * from testtable') as queyr1;
--Insert data
INSERT OPENQUERY(txtsrv1,'SELECT col1,col2 FROM testtable') values('REMOTTING1','REMOTTIN1')
insert into txtsrv1...testtable values ('remottin2','remottig2')
insert into txtsrv1...testtable values ( '123', '456')
--update data
UPDATE OPENQUERY (txtsrv1, 'SELECT col2 FROM testtable where col1=id') SET col2 = 'ADifferentName',id='33'
update txtsrv1...testtable set col1='456' where col2='33'
--delete data
DELETE OPENQUERY (txtsrv1, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
delete from txtsrv1...testtable where col1='33'
--Delete link server login user
EXEC sp_droplinkedsrvlogin 'txtsrv1', 'sa'
--Dlete link server
EXEC sp_dropserver 'txtsrv1'
--Query all link server
EXEC sp_helpserver;
数据库模型建立:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/// <summary>
/// 实体类testtable 。(属性说明自动提取数据库字段的描述信息)
/// </summary>
[Serializable]
public class testtable
{
public testtable()
{}
#region Model
private string _col1;
private string _col2;
/// <summary>
///
/// </summary>
public string col1
{
set{ _col1=value;}
get{return _col1;}
}
/// <summary>
///
/// </summary>
public string col2
{
set{ _col2=value;}
get{return _col2;}
}
#endregion Model
}
/// 实体类testtable 。(属性说明自动提取数据库字段的描述信息)
/// </summary>
[Serializable]
public class testtable
{
public testtable()
{}
#region Model
private string _col1;
private string _col2;
/// <summary>
///
/// </summary>
public string col1
{
set{ _col1=value;}
get{return _col1;}
}
/// <summary>
///
/// </summary>
public string col2
{
set{ _col2=value;}
get{return _col2;}
}
#endregion Model
}
数据库访问类代码实现:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/// <summary>
/// 数据访问类testtable。
/// </summary>
public class RemottesttableDAL
{
public RemottesttableDAL()
{ }
#region 成员方法
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(testtable model)
{
//insert into txtsrv1...testtable values ('remottin2','remottig2')
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into txtsrv1...testtable ");
strSql.Append("values (");
strSql.Append(string.Format(" '{0}', '{1}'",model.col1,model.col2));
strSql.Append(")");
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(testtable model)
{
//update txtsrv1...testtable set col1='456' where col2='33'
StringBuilder strSql = new StringBuilder();
strSql.Append("update txtsrv1...testtable ");
strSql.Append(string.Format("set col1='{0}' ",model.col1));
strSql.Append("where ");
strSql.Append(string.Format("col2='{0}'",model.col2));
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 删除一条数据
/// </summary>
public void Delete(string id)
{
//delete from txtsrv1...testtable where col1='33'
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from txtsrv1...testtable ");
strSql.Append(string.Format("where col1 ='{0}'", id));
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public testtable GetModel(string id)
{
//SELECT * FROM txtsrv1...[testtable] where col1='11'
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from txtsrv1...testtable ");
strSql.Append(string.Format("where col1 ='{0}'",id));
testtable model = new testtable();
DataSet ds = DbHelperSQL.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
model.col1 = ds.Tables[0].Rows[0]["col1"].ToString();
model.col2 = ds.Tables[0].Rows[0]["col2"].ToString();
return model;
}
else
{
return null;
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List<testtable> GetList(string strWhere)
{
//SELECT * FROM txtsrv1...[testtable]
List<testtable> listTable = new List<testtable>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from txtsrv1...testtable ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
DataSet set = DbHelperSQL.Query(strSql.ToString());
int rowCount = set.Tables[0].Rows.Count;
DataTable setTable = set.Tables[0];
if (rowCount > 0)
{
for (int i = 0; i < rowCount; i++)
{
testtable table = new testtable();
table.col1 = setTable.Rows[i]["col1"].ToString();
table.col2 = setTable.Rows[i]["col2"].ToString();
listTable.Add(table);
}
}
return listTable;
}
#endregion 成员方法
}
/// 数据访问类testtable。
/// </summary>
public class RemottesttableDAL
{
public RemottesttableDAL()
{ }
#region 成员方法
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(testtable model)
{
//insert into txtsrv1...testtable values ('remottin2','remottig2')
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into txtsrv1...testtable ");
strSql.Append("values (");
strSql.Append(string.Format(" '{0}', '{1}'",model.col1,model.col2));
strSql.Append(")");
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(testtable model)
{
//update txtsrv1...testtable set col1='456' where col2='33'
StringBuilder strSql = new StringBuilder();
strSql.Append("update txtsrv1...testtable ");
strSql.Append(string.Format("set col1='{0}' ",model.col1));
strSql.Append("where ");
strSql.Append(string.Format("col2='{0}'",model.col2));
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 删除一条数据
/// </summary>
public void Delete(string id)
{
//delete from txtsrv1...testtable where col1='33'
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from txtsrv1...testtable ");
strSql.Append(string.Format("where col1 ='{0}'", id));
DbHelperSQL.ExecuteSql(strSql.ToString());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public testtable GetModel(string id)
{
//SELECT * FROM txtsrv1...[testtable] where col1='11'
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from txtsrv1...testtable ");
strSql.Append(string.Format("where col1 ='{0}'",id));
testtable model = new testtable();
DataSet ds = DbHelperSQL.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
model.col1 = ds.Tables[0].Rows[0]["col1"].ToString();
model.col2 = ds.Tables[0].Rows[0]["col2"].ToString();
return model;
}
else
{
return null;
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List<testtable> GetList(string strWhere)
{
//SELECT * FROM txtsrv1...[testtable]
List<testtable> listTable = new List<testtable>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from txtsrv1...testtable ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
DataSet set = DbHelperSQL.Query(strSql.ToString());
int rowCount = set.Tables[0].Rows.Count;
DataTable setTable = set.Tables[0];
if (rowCount > 0)
{
for (int i = 0; i < rowCount; i++)
{
testtable table = new testtable();
table.col1 = setTable.Rows[i]["col1"].ToString();
table.col2 = setTable.Rows[i]["col2"].ToString();
listTable.Add(table);
}
}
return listTable;
}
#endregion 成员方法
}
注意的一点:
D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\
此处,Access存放的目录,必须给添加Everyone用户的读写权限。负责只能查询,不能修改。
源代码:
/Files/csharponworking/LinkServerAccess.rar