C#连接SMO及数据库操作（一）

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接本地数据库
            Microsoft.SqlServer.Management.Common.ServerConnection conn = new ServerConnection("localhost", "sa", "pass");
            //连接远程数据库
            //Microsoft.SqlServer.Management.Common.ServerConnection conn = new ServerConnection("tcp:192.168.18.10,pass", "sa", "pass");
            Server s = new Server(conn);
            Console.WriteLine("DataBaseCount:" + s.Databases.Count);

            //创建数据库
            Database db1 = new Database(s, "NewDataBaseName");
            //db1.Create();
            Console.WriteLine("DataBaseCount:" + s.Databases.Count);

            //创建表
            Database db = s.Databases["NewDataBaseName"];//引用数据库
            Table tb = new Table(db, "NewTableName");//表名
            Column c = new Column(tb, "CustomerId");//字段CustomerId
            c.Identity = true;
            c.IdentitySeed = 1;
            c.DataType = DataType.Int;
            c.Nullable = false;
            tb.Columns.Add(c);
            c = new Column(tb, "CustomerName");//字段CustomerName
            c.DataType = DataType.VarChar(20);
            c.Nullable = true;
            tb.Columns.Add(c);
            //tb.Create();//创建表
            
            //创建储存过程
            StoredProcedure sp = new StoredProcedure(db, "NewProcedure");
            StoredProcedureParameter spp1 = new StoredProcedureParameter(sp, "@addId", DataType.Int);
            sp.TextMode = false;
            sp.Parameters.Add(spp1);
            sp.TextBody = "select * from newtableName where CustomerId=@addId";
            //sp.Create();

            //执行储存过程
            db.ExecuteNonQuery("exec NewProcedure addId=1");

            //删除数据库
            Database db2 = s.Databases["NewDataBaseName"];
            //db2.Drop();
            Console.WriteLine("DataBaseCount:" + s.Databases.Count);

            //删除表
            Table tb1 = db.Tables["NewTableName"];
            //tb1.Drop();

            //删除储存过程
            StoredProcedure sp2 = db.StoredProcedures["NewProcedure"];
            //sp2.Drop();
        }
    }
}

 

其中要引用三个文件：

1.Microsoft.SqlServer.ConnectionInfo.dll

2.Microsoft.SqlServer.Smo.dll

3.Microsoft.SqlServer.WmiEnum.dll

可以从C:\Program Files\Microsoft SQL Server\90\sdk\Assemblies 这个文件夹里找到，如果你安装了sql的话。

还有SMO操作表的增删改查操作，待续中。。。。