timliao0308

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace tiffToPdf
{
    class sqlTest
    {
        static void Main(string[] args)
        {
            string sqlcmd = "SELECT top 4* FROM [bsns].[Subscription]";
            string connString = "Password=mmasuser;Persist Security Info=True;User ID=usermmas;Initial Catalog=BSNSTW;Data Source=192.168.31.147,1803";
            sqlTest st = new sqlTest();
            st.sqlQuery(sqlcmd, connString);

            //System.Console.WriteLine(st.sqlQuery(sqlcmd, connString).ToString());
            Console.ReadLine();
        }


        public void sqlQuery(string sqlText, string connString)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connString))
                {
                    ArrayList al = new ArrayList();
                    SqlCommand command = new SqlCommand(sqlText, sqlConnection);
                    SqlDataReader dr;
                    
                    sqlConnection.Open();
                    dr = command.ExecuteReader();

                    //System.Console.Write(dr.

                    while (dr.Read())
                    {
                        for (int i = 0; i < dr.VisibleFieldCount; i++)
                        {
                            System.Console.Write(dr[i].ToString()+" ");
                        }
                        System.Console.Write("\n");
                    }


                    sqlConnection.Close();
                    sqlConnection.Dispose();


                    //return reader;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException("ConnectionString", ex.Message);
            }
        }

        public int execNonQuery(string sqlText, string connStr)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand sqlCommand = new SqlCommand(sqlText, connection);
                try
                {
                    connection.Open();
                    int s = sqlCommand.ExecuteNonQuery();
                    //sqlCommand.CommandText
                    return s;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    sqlCommand.Dispose();
                }
            }
        }
    }
}

重點在於sqlConnection要先open

然後再reader接資料，取值後

再close sqlConnection，然後dispose!!