示例ConsoleApp查询SQLServer数据库

访问数据库的类

using System;
using System.Collections;
using System.Data.SqlClient;


namespace SQLCMD
{
    public partial class SqlCMD
    {
        SqlConnection connector = null;
        SqlCommand command = new SqlCommand();
        ArrayList alResult = new ArrayList();

        //连接指定的数据库，初始化Command对象的Connection属性
        public bool Connect(string datasource, string database, string uid, string password)
        {
            string strConnection = string.Format("data source={0}; database={1}; uid={2}; password={3}",
                                                                                        datasource, database, uid, password);
            connector = new SqlConnection(strConnection); //give connector object
            connector.Open();
            command.Connection = connector;
            return (connector.State == System.Data.ConnectionState.Open);
        }

        //用于执行常规的SQL语句查询，返回ArrayList
        private ArrayList ExecuteCommand(string strCommand)
        {
            if (connector == null)
                throw new Exception("Connector object is not initialized!");

            System.Collections.ArrayList al = new System.Collections.ArrayList();
            command.CommandText = strCommand;
            command.Connection = connector;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                string strtemp="";
                for (int i = 0; i < reader.FieldCount; i++)
                    strtemp += string.Format("{0}  ", reader[i]);
                 al.Add(strtemp);
            }
            reader.Close();
            al.Add("");//追加一个空行
            return al;
        }

        //分解命令，以ArrayList的形式返回数据
        public ArrayList CommandDispatch(string strCommand)
        {
            alResult.Clear();

            string strcmd = strCommand.ToUpper();
            string[] cmd = strcmd.Split(' ');
            switch (cmd[0])    //command
            {
                case "SHOW"://show database
                    if (cmd.Length < 2)
                    {
                        alResult.Add("the SHOW command:");
                        alResult.Add("DATABASE    TABLE[COLUMN]    VERSION");
                        return alResult;
                    }
                    switch (cmd[1])    //command target
                    {
                        case "DATABASE":
                            strcmd = "select name from master.dbo.sysdatabases";
                            alResult = this.ExecuteCommand(strcmd);
                            break;
                        case "VERSION":
                            alResult.Add("Version 1.0 @copyright by xuminghui. ");
                            alResult.Add("All Rights Reserved!  2013-4-25");
                            alResult.Add("");
                            return alResult;
                        case "TABLE":
                            if(cmd.Length ==2)
                            {
                                strcmd = "select name from sysobjects where xtype='u'";
                                alResult = this.ExecuteCommand(strcmd);
                                return alResult;                            
                            }
                            else if    (cmd[2] =="COLUMN")//cmd.length>2    3.......//SHOW tableName COLUMN
                            {
                                    strcmd = "select name from syscolumns where id=( ";
                                    strcmd+="select max(id) from sysobjects where xtype='u' and name ='{0}' )";
                                string strtemp = string.Format(strcmd,cmd[3]);
                                    alResult = this.ExecuteCommand(strtemp);
                                    alResult.Add(strtemp);
                                    return alResult;
                            }
                            else
                            {
                                alResult.Add("SHOW TABLE COLUMN  tableName is supported only.");
                                return alResult;
                            }
                        default:
                            alResult.Add("command " + cmd[1] + " is not regnized,the SHOW command:");
                            alResult.Add(string.Format("\tDATABASE    TABLE[COLUMN]    VERSION"));
                            alResult.Add("");
                            return alResult;
                    }
                    return alResult;
                case "USE"://use Northwind;
                    if (cmd.Length < 2)
                    {
                        alResult.Add("you must give the database name:");
                        alResult.Add("Example: USE NorthWind");
                    }
                    else
                    {
                        command.Connection = null;
                        connector.ChangeDatabase(cmd[1]);
                        alResult.Add("Database Changed To: " + cmd[1]);
                    }
                    alResult.Add("");
                    return alResult;
                case "CLS":
                    Console.Clear();
                    return alResult;
                case "":
                    return alResult;
                default: //
                    alResult = this.ExecuteCommand(strcmd);
                    return alResult;
            }
        }
    }

}

应用上述的类实现类SQLCMD的命令行工具，提供SHOW DATABASE, SHOW TABLE, SHOW TABLE COLUMN, CLS, EXIT等支持，支持存储过程及增删改查，可以进一步完善。其实SQLSERVER 提供了专门管理其Instance的类库，这里只是个SQL查询的demo而已。

 

using System;

using System.Collections;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLCMD
{
    partial class SqlCMD
    {
 
        public static void Main(string[] args)
        {
            string version = "\tThanks for use SqlCMD by xumh@lypower 2013.04.25\n";

            SqlCMD sql = new SqlCMD();
            ArrayList al = new ArrayList();

            if (args.Length < 4)
            {
                Console.WriteLine(version);
                Console.WriteLine("usage:\n\tSQLCMD ip database username password");
                Console.WriteLine("\tSQLCMD 192.168.0.xxx master  sa PASSWORD");
                return;
            }
            try
            {
                sql.Connect(args[0], args[1], args[2], args[3]);
            }
            catch(System.Exception e)
            {
                Console.WriteLine(e.Message);
                return;
            }
            
            Console.WriteLine("Connected successfully!\n");
            Console.WriteLine("\t\tExit to quit\tShow\tUse Database\tSelect Update ...");
            //get user command and give the answer
            Console.Write(sql.connector.Database + ">");
            string strCommand = Console.ReadLine();
            while (strCommand.ToUpper() != "EXIT")    //exit to quit
            {
                try
                {
                    al =  sql.CommandDispatch(strCommand);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                foreach (var item in al)
                {
                    Console.WriteLine(item);
                }
                //let's go
                Console.Write(sql.connector.Database +">");
                strCommand = Console.ReadLine();
            }
            Console.WriteLine(version);
        }
    }
}