数据库的不常见操作

创建数据表

SQL
Create Table Student
( Sno Char(9) PRIMARY KEY,
Sname Char(20) UNIQUE,
Ssex Char(2),
Sage SMALLINT,
Sdept Char(20)
);

C#

        /// <summary>
        /// 创建表格
        /// </summary>
        /// <param name="table"></param>
        public void CreateTable(DBTable table)
        {
            string strSQL = "CREATE TABLE [" + table.TableName + "](";

            int nIndex = 1;

            foreach (DBTable.TableField field in table.Fields)
            {
                if(field.Type == FieldType.DBIdentity)
                    strSQL += "[" + field.FieldName + "] counter";
                else if (field.Type == FieldType.DBLong)
                    strSQL += "[" + field.FieldName + "] LONG";
                else if(field.Type == FieldType.DBSingle)
                    strSQL += "[" + field.FieldName + "] SINGLE";
                else if(field.Type == FieldType.DBDateTime)
                    strSQL += "[" + field.FieldName + "] DATETIME";
                else if(field.Type == FieldType.DBString)
                    strSQL += "[" + field.FieldName + "] TEXT(" + field.FieldSize + ")";
                else if(field.Type == FieldType.DBDouble)
                    strSQL += "[" + field.FieldName + "] DOUBLE";
                else if(field.Type == FieldType.DBBinary)
                    strSQL += "[" + field.FieldName + "] LONGBINARY";

                if (field.PrimaryKey)
                {
                    string strFieldIndex = table.TableName + "_idx" + field.FieldName;
                    strSQL += " constraint " + strFieldIndex + "  primary key";
                }
                else
                {
                    if(field.NotNULL)
                        strSQL += " NOT NULL";
                }

                if (nIndex < table.Fields.Count)
                    strSQL += ",";

                nIndex++;
            }
            strSQL += ")";

            ExecuteNonQuery(strSQL);
        }

获取数据库中所有的表

包含架构信息的 System.Data.DataTable

/// <summary
/// 获取数据库表名列表
/// </summary>
/// <returns></returns>
public List<string> GetTableNameList()
{
     List<string> arrTable = new List<string>();
     DataTable dt = m_Conn.GetSchema("Tables");
     foreach (DataRow row in dt.Rows)
     {
           if (row[3].ToString() == "TABLE")
           arrTable.Add(row[2].ToString());
     }
     return arrTable;  
}

查询字段是否存在

大小写不敏感
SQL SELECT TOP 1 * From Table1 查询某个表中的首行数据
c#

public bool IsFieldExisted(string strTableName, string strFieldName)
{
    string strSQL = "SELECT TOP 1 * FROM [" + strTableName + "]";

    List<string> arrField = new List<string>();
    DbDataReader reader = ExecuteReader(strSQL);

    for (int i = 0; i < reader.FieldCount; i++)
    {
        if (reader.GetName(i) == strFieldName)
        {
            reader.Close();
            return true;
        }
    }
    reader.Close();
    return false;
}

新增表中字段

SQL Alter table 表名称 Add 列名称 列的数据类型

删除表中的列

SQL Alter table 表名称 Drop 列名称
修改某表删除某列

删除表中的索引

SQL Drop Index table_name.index_name

删除表

Drop Table 表名称

删除DataBase

Drop DATABASE 数据库名称