ADO.NET处理数据库

既然选择用ADO.NET处理数据库，想要获取关于数据库的信息，都可以用sql语句通过SqlCommand获取。

比如以下函数功能：

1. 获取指定数据库的所有table名称。

public List<string> GetAllTables()
        {
            List<string> list = new List<string>();
            if (conn.State==System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            DataTable dt = conn.GetSchema("Tables");
            foreach (DataRow row in dt.Rows)
            {
                list.Add(row[2].ToString());
            }
            list.Sort();
            return list;
        }

2.获取指定Table的所有列。List<string> Columns = new List<string>();

public void GetColumnFromTable(string tableName)
        {
            Columns.Clear();
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd=new SqlCommand("Select a.Name,b.name,a.length FROM SysColumns a left join systypes b on a.xusertype=b.xusertype Where id=Object_Id('" + tableName + "')", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {                
                Columns.Add(reader[0].ToString());
                Types.Add(GetSQLDBType(reader[1].ToString()));
                Widths.Add(Convert.ToInt32(reader[2].ToString()));
            }
            reader.Close();
        }

3. 给指定Table添加字段。示例添加VPNCode字段，类型为nchar(50)

public void AddColumnToTable(string tableName)
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand("alter table " + tableName+ " add VPNCode nchar(50)", conn);
            int n = cmd.ExecuteNonQuery();
        }

4.判断指定Table是否含有某一字段。示例判断是否含有VPNCode字段

public bool ExistVPNCodeColumn(string tableName)
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand("Select a.Name,b.name,a.length FROM SysColumns a left join systypes b on a.xusertype=b.xusertype Where id=Object_Id('" + tableName + "')", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (reader[0].ToString().IndexOf("VPNCode") >= 0)
                {
                    reader.Close();
                    return true;
                }
            }
            reader.Close();
            return false;
        }