如果ConnectionStringSettings不能用则在“引用”中添加System.Configuration
using System.Configuration;
using System.Data.OleDb;
public void AddCol()
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["WindowsApplication1.Properties.Settings.ConnectionString"];
string ConnString = settings.ConnectionString;
string s = "ALTER TABLE MessageScout add Channel varchar(1) not null default 1";
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(s, Conn);
Conn.Open();
try
{
Cmd.ExecuteNonQuery();
}
catch
{
}
finally
{
Conn.Close();
}
}
public void AddCheck()
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["WindowsApplication1.Properties.Settings.ConnectionString"];
string ConnString = settings.ConnectionString;
string s = "ALTER TABLE MessageScout DROP CONSTRAINT primarykey";//primarykey为Access的默认主键名
OleDbConnection Conn = new OleDbConnection(ConnString);
OleDbCommand Cmd = new OleDbCommand(s, Conn);
Conn.Open();
try
{
Cmd.ExecuteNonQuery();
}
catch{}
//SQL数据库中会自动将原来的记录中的新添加的字段会添加默认值Access则需要SQL语句处理
try
{
Cmd.CommandText = "UPDATE MessageScout SET Channel ='1' WHERE Channel is null";
Cmd.ExecuteNonQuery();
}
catch
{ }
try
{
Cmd.CommandText = "ALTER TABLE MessageScout ADD CONSTRAINT primarykey PRIMARY KEY CLUSTERED(ScoutID,Channel)";
Cmd.ExecuteNonQuery();
}
catch
{ }
Conn.Close();
}
Access的SQL语句与SQLServer的SQL语句基本相同。字段的默认值default在Access中不能用SQL语句直接实现,需通过ADO方式实现。