qileilove

View Code

    class Program
    {
        static void Main(string[] args)
        {
            Sql sql = new Sql();
            Stopwatch time1 = new Stopwatch();
            Stopwatch time2 = new Stopwatch();
            for (int j = 0; j < 10; j++)
            {
                time2.Start();
                for (int i = 0; i < 1000; i++)
                {
                    string sql1 = "select ID,[StuName],[ClassID] FROM [Student]";
                    string sql2 = " SELECT  ID,ClassName from Class";
                    List<string> item = new List<string>();
                    string bl="";
                    DataTable dt1 = sql.getData(sql1);
                    DataTable dt2 = sql.getData(sql2);
                    foreach (DataRow dtRow2 in dt2.Rows)
                    {
                        foreach (DataRow dtRow1 in dt1.Rows)
                        {
                            if (dtRow1["ClassID"].ToString() == dtRow2["ID"].ToString())
                            {
                                 bl+=dtRow1["StuName"].ToString()+",";
                            }   
                        }
                        item.Add(bl);
                        bl = "";
                    }
                }
                time2.Stop();
                Console.WriteLine(time2.Elapsed.ToString());

                time1.Start();
                for (int i = 0; i < 1000; i++)
                {
                    string sql3 = "SELECT C.ID, C.ClassName, dbo.f_getStuNamesByClassID(C.ID)as stuName FROM Class C";
                    DataTable dt = sql.getData(sql3);
                }
                time1.Stop();
                Console.WriteLine(time1.Elapsed.ToString());

                float index = (float)time1.Elapsed.Ticks / (float)time2.Elapsed.Ticks;
                Console.WriteLine("效率比" + index.ToString());
                Console.WriteLine("=============================");
            }

            Console.ReadLine();
        }
    }

View Code

    class Sql
    {
        public DataTable getData(string sql)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Test;User Id=sa;Password=1;";
            SqlCommand comm = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "ds");
            conn.Close();
            return ds.Tables[0];
        }
    }

View Code

--根據課程ID，返回選此課程的學生的名字，以逗號隔開
ALTER function [dbo].[f_getStuNamesByClassID] (@ID int)
RETURNS nvarchar(50)
begin
    declare @Result nvarchar(50);
    declare @stuName nvarchar(50);
    Set @Result='';

    declare cur cursor for
    (
        SELECT S.StuName FROM Class C
        LEFT JOIN Student S ON C.ID=S.ClassID
        WHERE C.ID=@ID
    )
    open cur;
    fetch next from cur into @stuName;
    while(@@fetch_status=0)
    begin
        set @Result=@Result+@stuName+',';
        fetch next from cur into @stuName;
    end;
--去除最后多余的一個逗號
    IF @Result <> '' 
        SET @Result=SUBSTRING(@Result, 1, LEN(@Result)-1);
    ELSE
        SET @Result=NULL;
    return @Result;
en