using System;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
namespace DataMap
{
/// <summary>
/// SqlDataHelper 的摘要说明。
/// </summary>
public class SqlDataHelper:IDataHelper
{
string _ConnectionString="user id=sa;password=;initial catalog=hrs;data source=(local);Connect Timeout=30";
string _DatabaseName="northwind";
public string ConnnectionString
{
get
{
return _ConnectionString;
}
set
{
_ConnectionString=value;
}
}
public System.Collections.ArrayList FillDataObject(System.Type type,SqlDataReader dr)
{
System.Collections.ArrayList al=new System.Collections.ArrayList();
if(type==null)
return al;
while(dr.Read())
{
object obj=System.Activator.CreateInstance(type);
System.Reflection.PropertyInfo[] pi=type.GetProperties();
for(int i=0;i<pi.Length;i++)
{
DataAtAttribute[] daProp=(DataAtAttribute[])pi[i].GetCustomAttributes(typeof(DataAtAttribute),true);
if(daProp.Length>0)
{
if(dr[daProp[0].DataField]!=DBNull.Value)
try
{
pi[i].SetValue(obj,dr[daProp[0].DataField],null);
}
catch(IndexOutOfRangeException e)
{
}
}
}
al.Add(obj);
}
dr.Close();
return al;
}
public System.Collections.ArrayList FillDataObject(System.Type type)
{
DataAtAttribute[] daClass=(DataAtAttribute[])type.GetCustomAttributes(typeof(DataAtAttribute),true);
if(daClass.Length==0)
return new System.Collections.ArrayList();
string tableName=daClass[0].DataField;
string sql="SELECT * FROM "+tableName;
SqlDataReader dr=getDataReader(sql);
return FillDataObject(type,dr);
}
private SqlDataReader getDataReader(string sql)
{
SqlConnection cn=new SqlConnection(_ConnectionString);
SqlCommand cmd=new SqlCommand(sql,cn);
cn.Open();
SqlDataReader dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public System.Collections.SortedList getMetaData()
{
System.Collections.SortedList slTable;
slTable=new System.Collections.SortedList();
SqlConnection cn=new SqlConnection(_ConnectionString);
cn.Open();
SqlCommand cmd=new SqlCommand("sp_help",cn);
cmd.CommandType=CommandType.StoredProcedure;
SqlDataReader dr=cmd.ExecuteReader();
while(dr.Read())
{
if(dr["object_type"].ToString().Trim()=="user table")
{
slTable.Add(dr["name"].ToString(),null);
}
}
dr.Close();
for(int i=0;i<slTable.Keys.Count;i++)
{
System.Collections.SortedList slColumn;
slColumn=new System.Collections.SortedList();
string table=slTable.GetKey(i).ToString();
cmd.CommandType=CommandType.Text;
cmd.CommandText="SELECT o.name table_name,c.name column_name,t.name type FROM sysobjects o,syscolumns c,systypes t WHERE o.[id]=c.[id] AND c.xtype=t.xusertype AND o.name='"+table+"'";
dr=cmd.ExecuteReader();
while(dr.Read())
{
slColumn.Add(dr["column_name"].ToString(),dr["type"].ToString());
}
dr.Close();
slTable[table]=slColumn;
}
cn.Close();
return slTable;
}
public System.Type getTypeByDbType(string dbType)
{
switch(dbType)
{
case "bigint":
return typeof(System.Int64);
case "int":
return typeof(System.Int32);
case "bit":
return typeof(System.Boolean);
case "char":
case "nchar":
case "ntext":
case "varchar":
case "nvarchar":
case "text":
return typeof(System.String);
case "datetime":
case "smalldatetime":
return typeof(System.DateTime);
case "decimal":
case "numeric":
return typeof(System.Decimal);
case "float":
case "real":
return typeof(System.Single);
case "smallint":
case "tinyint":
return typeof(System.Int16);
case "image":
case "money":
case "smallmoney":
case "sql_variant":
case "sysname":
case "timestamp":
case "uniqueidentifier":
case "varbinary":
return typeof(System.Object);
default:
return typeof(System.Object);
}
}
}
}