创建数据表
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 数据库名称