command
/// <summary>
/// 获取存储过程的相关表的列名和isnuable
/// </summary>
/// <param name="spid">存储过程名称</param>
/// <returns>Hastable</returns>
public Hashtable SPDependonTable(Int32 spid)
{
try
{
//验证连接
if(conn!=null && conn.State!=ConnectionState.Open)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
string sqlstr="SELECT name, isnullable as allowNUll FROM dbo.syscolumns WHERE (id IN (SELECT DISTINCT id FROM dbo.sysobjects WHERE (id IN (SELECT DISTINCT dbo.sysdepends.depid FROM dbo.sysdepends WHERE (dbo.sysdepends.id ="+spid+")))))";
//获取相关表的isnullable
SqlDataAdapter comm= new SqlDataAdapter(sqlstr,conn );
DataSet depds=new DataSet();
comm.Fill(depds,"dbo.syscolumns");
Hashtable dependtbl=new Hashtable();
//建立Hashtble
for(int n=0;n<depds.Tables[0].Rows.Count;n++)
{
string keyname="@"+depds.Tables["dbo.syscolumns"].Rows[n]["name"];
Boolean isnullabled=Convert.ToBoolean( depds.Tables["dbo.syscolumns"].Rows[n]["allowNull"]);
dependtbl.Add(keyname,isnullabled);
}
return dependtbl;
}
catch(SqlException se)
{
throw(se);
}
catch(Exception e)
{
throw(e);
}
finally
{
if(conn.State ==ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// 获得指定表的所有字段对象
/// </summary>
/// <param name="TableName">表名</param>
/// <returns>System.Data.DataTable</returns>
public DataTable GetFields(String TableName)
{
try
{
DataSet myDataSet=new DataSet();
//验证连接
if(conn!=null && conn.State!=ConnectionState.Closed)
{
conn.Open();
}
else
{
conn= new SqlConnection(this.connectionString);
conn.Open();
}
SqlDataAdapter comm= new SqlDataAdapter("SELECT * from "+TableName,conn);
comm.FillSchema(myDataSet, SchemaType.Mapped,TableName);
return myDataSet.Tables[0];
}
catch(SqlException se)
{
throw(se);
}
}
/// <summary>
///私有: 获取数据类型(DbType)
/// </summary>
/// <param name="typename">数据类型名称</param>
/// <returns>DbType</returns>
private DbType getDbType(string typename)
{
//DbType t;
#region switch datatype
switch(typename)
{
//int64
case "bigint":
return DbType.Int64;
//break;
//int32
case "int":
return DbType.Int32;
//break;
//int16
case "smallint":
return DbType.Int16;
//break;
//byte
case "binary":
return DbType.Byte;
//break;
case "image":
return DbType.Byte;
//break;
case "varbinary":
return DbType.Byte;
//break;
case "tinyint":
return DbType.Byte;
//break;
//boolean
case "bit":
return DbType.Boolean;
//break;
//string
case "varchar":
return DbType.String;
//break;
case "text":
return DbType.String;
//break;
case "nvarchar":
return DbType.String;
//break;
case "ntext":
return DbType.String;
//break;
case "nchar":
return DbType.String;
//break;
case "char":
return DbType.String ;
//break;
//DateTime
case "datetime":
return DbType.DateTime;
//break;
case "smalldatetime":
return DbType.DateTime;
//break;
case "timestamp":
return DbType.DateTime;
//break;
//double
case "Float":
return DbType.Double;
//break;
//decimal
case "decimal":
return DbType.Decimal;
//break;
case "money":
return DbType.Decimal ;
//break;
case "smallmoney":
return DbType.Decimal;
//break;
default:
return DbType.Object;
//break;
}
#endregion
}
/// <summary>
/// 获取数据类型(SqlDbType)
/// </summary>
/// <param name="typename">数据类型名称</param>
/// <returns>SqlDbType</returns>
public SqlDbType getSqlDbType(string typename)
{
//SqlDbType t;
#region switch datatype
switch(typename)
{
//int64
case "bigint":
return SqlDbType.BigInt;
//break;
//int32
case "int":
return SqlDbType.Int;
//break;
//int16
case "smallint":
return SqlDbType.SmallInt;
//break;
//byte
case "binary":
return SqlDbType.Binary;
//break;
case "image":
return SqlDbType.Image;
//break;
case "varbinary":
return SqlDbType.VarBinary;
//break;
case "tinyint":
return SqlDbType.TinyInt;
//break;
//boolean
case "bit":
return SqlDbType.Bit;
//break;
//string
case "varchar":
return SqlDbType.VarChar;
//break;
case "text":
return SqlDbType.Text;
//break;
case "nvarchar":
return SqlDbType.NVarChar;
//break;
case "ntext":
return SqlDbType.NText ;
//break;
case "nchar":
return SqlDbType.NChar;
//break;
case "char":
return SqlDbType.Char;
//break;
//DateTime
case "datetime":
return SqlDbType.DateTime;
//break;
case "smalldatetime":
return SqlDbType.SmallDateTime;
//break;
case "timestamp":
return SqlDbType.Timestamp;
//break;
//double
case "Float":
return SqlDbType.Float;
//break;
//decimal
case "decimal":
return SqlDbType.Decimal;
//break;
case "money":
return SqlDbType.Money;
//break;
case "smallmoney":
return SqlDbType.SmallMoney;
//break;
default:
return SqlDbType.Variant;
//break;
}
#endregion
}
#endregion
}
}