示例#1
0
        public async Task CreateTwoStoredProceduresOneReturnsMoreRowsAsync()
        {
            StringBuilder complexSelectQuery = new StringBuilder();

            complexSelectQuery.AppendLine("DECLARE @TestDate TABLE	(Column1 int)");
            complexSelectQuery.AppendLine("INSERT INTO @TestDate (Column1)");
            complexSelectQuery.AppendLine("VALUES");
            complexSelectQuery.AppendLine("(1),");
            complexSelectQuery.AppendLine("(2)");
            complexSelectQuery.AppendLine("SELECT Column1 FROM @TestDate");


            string createSchemaSql = _sqlQueryBuilder.BuildCreateSchemaQuery();

            string sqlQueryCreateFirstStoredProcedure =
                _sqlQueryBuilder.BuildCreateStoredProcedureQuery(StoredProcedureTesterTestsConsts
                                                                 .TestUnoptimisedStoredProcedureName, string.Empty, _sqlQueryBuilder.BuildSelectQuery("1", "Column1"));

            string sqlQueryCreateSecondStoredProcedure =
                _sqlQueryBuilder.BuildCreateStoredProcedureQuery(StoredProcedureTesterTestsConsts
                                                                 .TestOptimisedStoredProcedureName, string.Empty, _sqlQueryBuilder.BuildComplexSelectQuery(complexSelectQuery));

            using (SqlConnection connection = new SqlConnection(StoredProcedureTesterTestsConsts.ConnectionString))
            {
                SqlCommand createUnoptimisedStoredProcedure = new SqlCommand(sqlQueryCreateFirstStoredProcedure, connection);
                SqlCommand createOptimisedStoredProcedure   = new SqlCommand(sqlQueryCreateSecondStoredProcedure, connection);
                SqlCommand createSchema = new SqlCommand(createSchemaSql, connection);
                await connection.OpenAsync();

                await createSchema.ExecuteNonQueryAsync();

                await createUnoptimisedStoredProcedure.ExecuteNonQueryAsync();

                await createOptimisedStoredProcedure.ExecuteNonQueryAsync();
            }
        }