用SqlServer 連接服務器實現,c#Ado.Net 編程實現。
連接服務器的建立及一些語句如下:
代碼
--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;
數據庫模型建立:
代碼
/// <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
}
數據庫訪問類代碼實現:
代碼
/// <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 成員方法
}
注意的一點:
D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\
此處,Access存放的目錄,必須給添加Everyone用戶的讀寫權限。負責只能查詢,不能修改。
點擊下載源碼