ACCESS数据库访问组件(二)ACCESS_Table.cs
using System;
namespace XLang.VideoOnline.Framework.Database.Access { /// <summary> /// Summary description for ACCESS_DataTable. /// </summary> public class DataTable:System.Data.DataTable { private string _tableName;
private string _primaryKey;
public string Name { get { return _tableName; } set { _tableName=value; } }
public string PrimaryKey { get { return _primaryKey; } set { _primaryKey=value; } }
public DataTable() { }
public DataTable(string tableName) { _tableName=tableName; }
public string CreateCommand(Database.Access.DataRow[] rows) { string temp="CREATE TABLE "+_tableName+"("; for(int i=0;i<rows.GetLength(0);i++) { temp+="["+rows[i].Name+"] "+rows[i].Type+(rows[i].IsNull? ",":" not null,"); } temp+="CONSTRAINT [Index1] PRIMARY KEY (["+rows[0].Name+"]))"; _primaryKey=rows[0].Name; return temp; }
public string InsertCommand(Database.Access.DataRow[] rows) { string temp=""; temp="INSERT INTO "+_tableName+" ( "; for(int i=0;i<rows.GetLength(0);i++) { temp+="["+rows[i].Name+"] "+","; } temp=temp.Substring(0,temp.Length-1); temp+=") VALUES("; for(int i=0;i<rows.GetLength(0);i++) { if(rows[i].Type.Equals("boolean")) temp+=((rows[i].Value.ToString().ToUpper().Equals("YES"))? "yes":"no")+","; else if(rows[i].Type.StartsWith("string")||rows[i].Type.Equals("memo")|| rows[i].Type.Equals("text")||rows[i].Type.StartsWith("text")) temp+="'"+rows[i].Value+"',"; else if(rows[i].Type.Equals("date")) temp+="#"+rows[i].Value+"#,"; else temp+=rows[i].Value+","; } temp=temp.Substring(0,temp.Length-1); temp+=")"; return temp; }
public string DeleteCommand() { return "DROP TABLE "+_tableName; }
public string DeleteCommand(string conditions) { return "DELETE * FROM "+_tableName+" WHERE "+conditions; }
public string UpdateCommand(Database.Access.DataRow[] rows,string conditions) { string temp="UPDATE "+_tableName+" SET "; temp+=ProsessDifferentDataType(rows); temp+=" WHERE "+conditions; return temp; }
public string SelectCommand() { return "SELECT * FROM "+_tableName; }
public string SelectCommand(string conditions) { return "SELECT * FROM "+_tableName +" WHERE "+conditions; }
public string SelectCommand(Database.Access.DataRow[] rows,string conditions) { string temp="SELECT "; for(int i=0;i<rows.GetLength(0);i++) { temp+="["+rows[i].Name+"],"; } temp=temp.Substring(0,temp.Length-1); temp+=" FROM "+_tableName+" WHERE "+conditions; return temp; }
public string JoinCommand(Database.Access.DataRow[] rows,string condition,params string [] tableNames) { string temp="SELECT "; for(int i=0;i<rows.GetLength(0);i++) { temp+="["+rows[i].Name+"],"; } temp=temp.Substring(0,temp.Length-1); // temp+=" FROM "+_tableName+" WHERE "+conditions; temp+=" FROM "; foreach(string table in tableNames) temp+=table+","; temp=temp.Substring(0,temp.Length-1);
if(condition!=null) temp+=" WHERE "+condition;
return temp; }
private string ProsessDifferentDataType(Database.Access.DataRow[] rows) { string temp=""; for(int i=0;i<rows.GetLength(0);i++) { if(rows[i].Type.Equals("boolean")) temp+="["+rows[i].Name+"] ="+ ((rows[i].Value.ToString().ToUpper().Equals("YES"))? "yes":"no") +","; else if(rows[i].Type.StartsWith("string")||rows[i].Type.Equals("memo")|| rows[i].Type.Equals("text")||rows[i].Type.StartsWith("text")) temp+="["+rows[i].Name+"] ='"+rows[i].Value+"',"; else if(rows[i].Type.Equals("date")) temp+="["+rows[i].Name+"] =#"+rows[i].Value+"#,"; else temp+="["+rows[i].Name+"] ="+rows[i].Value+",";
} temp=temp.Substring(0,temp.Length-1); return temp; } } }

|