access|访问|数据|数据库
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;
}
}
}